I DO need Durability, so I don't want to drop that. In fact, I need and want normal transactional updates - just not immediately flushed to disk. I've looked at the source and know there is no simple compile option for what I want. My question was more like: Anybody already done this? (So I don't have to do the work myself IF it's already been done.)
Laszlo Elteto SafeNet, Inc. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 19, 2006 11:15 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] delayed (batch) transactions 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] ---------------------------------------------------------------------------- - The information contained in this electronic mail transmission may be privileged and confidential, and therefore, protected from disclosure. If you have received this communication in error, please notify us immediately by replying to this message and deleting it from your computer without copying or disclosing it. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
