Shared cache won't help as my problem is the file flush operations at each COMMIT, not the reading part. My original test was done in a single-threaded program and it clearly shows the timing issue is with FileFlushBuffers.
-----Original Message----- From: Kees Nuyt [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 19, 2006 12:08 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] delayed (batch) transactions On Tue, 19 Dec 2006 13:52:19 -0500, you 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. > >Anybody has done that already? If yes, where can I find such modified >SQLite source? >I saw that all file I/O related calls are in two files: pager.c and >vdbeaux.c so they are already well isolated and relatively easy to >understand. > >Any help for such "delayed" transaction method in SQLite? >(I tried to find any relevant message in the email archives but came up >empty.) > >Thanks, > >Laszlo Elteto >CISSP, System Architect >SafeNet, Inc. I wonder if shared cache would help you? http://www.sqlite.org/sharedcache.html In general, in a high concurrency environment sqlite might not be a suitable solution. http://www.sqlite.org/whentouse.html -- ( Kees Nuyt ) c[_] ---------------------------------------------------------------------------- - 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] -----------------------------------------------------------------------------
