Laszlo Elteto <[EMAIL PROTECTED]> wrote: > I've started to use SQLite and it works fine - except for performance. The > application gets requests (possibly from many users) and does a few > transactions on the database. (eg. create a context for the request; later > it may be updated and when the user releases the record is deleted.) > > I tried all three sync methods and with FULL, NORMAL and OFF. For 100 > transactions (request / check / release) I see 800 open/close (in 1200msec), > 5800 lock/unlock (22 msec) and 600 (FULL - 17,000 msec), 400 (NORMAL - > 12,000 msec) or 0 (OFF) file flush operations. The latter is really slow but > I understand it. (Have to wait for the actual disk operation to complete). > > For this particular application it would NOT be a problem to lose like 2-5 > seconds of transactions. I wonder if it is possible to tell SQLite to "hold > off" the transactions, ACCUMMULATE them until a certain time (or if cache > memory is exhausted - which is not yet the case as we have a modest > database), then make a BIG COMMIT (ie. all previous transactions committed > or none). That way it's still transactional (ie. no currupted database - I > really don't want to use sync = OFF) but the I/O performance wouldnt slow > down serving requests. >
It takes at least two complete rotations of the disk platter to do an atomic and durable commit. On a 7200 RPM disk, that means 60 transactions per second is your speed of light. Your question boils down to this: Can you speed up transactions by dropping the durable property - the D in ACID. Yes you can. Actually, most client/server database engines already do this for you without telling you. Very few client/server databases are really ACID - they are usually on ACI when confronted with a power failure. There is no simple pragma setting or anything like that to drop durability from SQLite simply because there is no server process hanging around to make sure that transactions get committed atomically in the background. You have to do the background commits yourself. There are various ways to do this. One approach would be to write your changes to one or more TEMP tables. Writes to TEMP tables are always done with synchronous=OFF since TEMP tables do not need to survive a power loss. So writes to TEMP tables are fast. Then have your application periodically transfer the information in TEMP tables over to the main database. A second approach would be to overload the OS drivers on the backend of SQLite to support asynchronous I/O. Mozilla does this in Firefox in order to boost performance on NFS. There is well-commented sample code showing how to do this in the SQLite source file "test_async.c". That sample code does not combine multiple transactions, but you could probably tweak it to make that happen. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
