World: r3wp

Janko: I just tested again two concurrent processes writing into 
one table.

This time I used the approach connecting and closing for each statement, 
as the link you gave suggested..
I still got locking.

I understood the text in link that if you get a lock, you wait for 
a while and try again.. and that by this method even it scales to 
quite big number of concurr processes compared to mysql for example

trying again is already implemented in sqlite.r in the function do-step.
I expanded the wait time with a random duration up to one second.
still not good enough

And now additionaly I connected and disconnected according the link 
.
Again I encountered locks

aha ... hm.. I havent tried it myself yet

how many times do you retry?

a few 10k times

what disconnection and connecting? :)

sorry, that was the number of statements until a lockk occurs

aha .. does it at least signidicantly reduce the occurence of the 
lock or it's the same as no retry?

do-step seems to retry until not busy

yes, it reduces, but they happen still

He tried only 100 times (by looking at the example he gave) .. and 
I don't know what he means by 500 >>> in line with mysql 

and others- at 500+ simultaneous users you start to get about a 10% 
drop 
because of lock overhead. <<< 


Most probably not 500 processes writing all the time ...  without 
delays

my mistake, I forgot to set the number of retries during connect.
I will test again

no difference, sooner than later I get a lock, even with 100 retries 
after less than 1000 statements

hm.. too bad

how are you testing it.. how many writes/sec .. processes?

sqlab, use a proxy process that does the priorization. Send requests 
from client to proxy-process and this one handles 1 connection to 
the database.

IMO you are much flexibler anyway.

You can add things like priority, read-only support etc.

Maybe my test settings are different from your  requirements.

During testing I let two concurrent processes insert a record with 
three fields, one ip address, a timestamp and one text field with 
variable length from 1k to 8k., and this i do with full speed

I get up to 200 inserts with just one process, with two concurrent 
process this slows down to 30 to 60 per second

I'm pretty sure a proxy process can handle 200req/s at the frontend 
and backend. So if 200req/s is the maximum you can get from one process, 
adding more processes just devides this down. But it won't scale 
up.

Robert, that could be a solution.

Unfortunately I observed a crosstalk of events with many ip connections, 

means one process is awakened  without data available, if I send 
data additionaly on the same server via ip

Don't understand the problem. Is it realted to my suggestion or your 
current try?

If I have some processes serving incoming events and sending their 
data to a central storage process, the central process sometimes 
seems to get an event, but not the data with that event.
When the data really arrives, I do not get the event.
Maybe he got the first event meant for an other connection

As I know, that there should be data available, I read again after 
timeout

Event = TCP/IP event?

yes

Hm... normaly there should be event/data

normally there is, but not always. 

Most socket connections are static, they stay connected for hours 
or longer.

Reading thru SQLite changelog found this - http://sqlite.org/rtree.html
... not sure of its practical usability, but some of you might find 
it interesting :-)

I love the sqlite motto  :-)   

Small. Fast. Reliable. Choose any three.

rebol.com motto - Web 3.0 starts here. Smarter, faster, better.

only that fast (at sqlite) is still a little problematic to me

SQLIte is fast for simple to middle local stuff. I have few obstacles 
with it 


1) it stores everything in one file. You can't use simplicity of 
file-system for simple back-up purposes. Attaching DBs (max 10 precompiled 
value) is not an option, as then transactions are not atomic

2) it is not secure - can't be secured easily, because encryption 
is not part of the package

3) serverless (both advantage = no install, but also disadvantage). 
It provides locking. They claim multiple instances of app can access 
one file, but I did not find more info on that. Dunno how granular 
locking you can do. You have to create server front-end yourself 
...

I use it for very simple task just so it takes case for locking of 
messages to bots that can come from multiple processes.. but at my 
small/cheap VPS that I use for bots update/insert/delete takes multiple 
seconds which is very bad.. all rebol writes/deletes which it does 
with normal files return imediately on same VPS and yesterday I tried 
rebDB and it was also much much faster for insert/update/delete (almost 
no noticable delay) for the same amount of data (300 lines) as sqlite.. 
funny thing is that sqlite delays the same at these operations if 
there is 300 or 5000 rows in table

(I tried rebDB on same VPS - localy where comp. is fast I notice 
no delay at sqlite either)

and this is no problem with rebol binding as the delays are the same 
if I go to sqlite3 shell

Have you tried put BEGIN transaction and END transaction outer commands? 
Because if not, then it commits each time. The difference is going 
to be ... well, drastic ...

yes, I know for that .. that would come into play if I would be doing 
10 inserts for example ,.. with begin commit it would take just 1/10 
of time as without , but I am doing a simple sql scentence here. 
and I tried begin/commit also with this.

ah, single query?

yes, and nothing big.. 5 simple rows

show me the table structure, show me the query :-)

I talked about this in detail a while ago.. now I optimised the problem 
so it's not a biggie any more but I am still mad that all rebol files 
can do changes in moments and when I need to update the sqlite page 
halts for noricable time

any query I try :) .. I talked about this to you already :) .. look 
a little up at 14-Apr

Janko - I did small test for you. With no indices the speed was: 
0:00:00.516 and I used LIKE expressions, which need to do searches 
in terms of field values .... 

REBOL []

print "Starting test for Janko ..."

do %sqlite.r

attempt [delete %janko.db]

connect/direct/create %janko.db

sql {
CREATE TABLE [domains] (
[id] INTEGER  NOT NULL PRIMARY KEY,
[domain] VARCHAR  NOT NULL,
[user] VARCHAR  NOT NULL,
[processed] DATETIME  NULL,
[ok_count] INT NOT NULL DEFAULT 0,
[fail_count] INT NOT NULL DEFAULT 0,
[error] TEXT NULL
)

}

sql "BEGIN"

for i 1 1000 1 [

 sql reduce ["insert into domains values(?,?,?,?,?,?,?)" i i i i i 
 i i]
]

sql "COMMIT"

start: now/time/precise


sql {update domains set user = 'u3' where domain like '%1%' and user 
like '%1%'}

print now/time/precise - start

disconnect %janko.db

halt

hm... mucho interesante :)

I will try this on my local computers and then on that VPS.. and 
report you back :)

btw - what is VPS?

virtual private server ... like you can buy here .. www.linode.com 
or sliceshare.com