Hmmm ... Is there a way to replace step 5 (delete everything from the table) with a System-Level rm / unlink and a sqlite CREATE TABLE ?
-- kjh On 06/16/2009 10:06 AM, Jim Wilcoxson wrote: > You are doing transactions here, which is a very different thing from > normal disk I/O. Your CPU is idle because it is waiting for the disk. > Your disk is idle because it is waiting for the platters to rotate > around again. The best case you can achieve on a 7200RPM disk is 120 > transactions (commits) per second. > > In practice, you will see much lower TPS rates, and they will > typically be 50% fractions. For 1 sync per transaction, the max rate > is 120 TPS. For 2 syncs per transaction, the max rate is 60 TPS. For > 3 syncs per transaction, the max rate is 30 TPS. SQLite always does > at least 2 syncs per transaction, and sometimes 3, depending on the > options you use, so a transaction rate of 30 TPS is reasonable. > > The only way you can speed this up is to get a disk that rotates > faster, ie, a 10K or 15K rpm drive will do faster transactions, but > even so, you are still limited to 250 TPS with a 15K drive. Or, get a > battery-backup caching controller that will lie to your OS and tell it > that the data is on the media, when really it is only stored in the > controller's memory. This allows the controller to combine write > requests to increase the transaction rate. > > Jim > > On 6/16/09, Jens Páll Hafsteinsson <j...@lsretail.com> wrote: >> Yes, I'm perfectly aware of this and hence I would expect the disk to be >> sweating like hell running this test while the CPU is relatively relaxed >> (given that sqlite is disk bound in this case and not CPU bound). >> >> But this is not happening; neither the disk nor the CPU are practically >> doing anything, which is a bit strange. It's as if both the disk and the CPU >> are waiting for each other or that sqlite is playing 'nice' behind my back >> and giving up the CPU when it shouldn't. >> >> JP >> >> -----Original Message----- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita >> Sent: 16. júní 2009 14:06 >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Database inserts gradually slowing down >> >> Remember the implications of Moore's law and how much time has passed. >> >> CPU speed is much faster than memory speed. >> Memory speed is much faster than disk access. >> >> This is why hardware folks play all sorts of tricks with pipelines, caches, >> interleaving, and parallelism. >> >> For a single process that interacts with the HDD, the HDD will be the bottle >> neck and the CPU will spend lots of time waiting for the rest of the machine >> to catch up. Even if you have a RAID system, the CPU is still much faster >> than the bus the hard drives are on. >> >> >> On Tue, Jun 16, 2009 at 9:52 AM, Jens Páll Hafsteinsson >> <j...@lsretail.com>wrote: >> >>> In step 5 I execute "delete from t1" without any where clause. >>> >>> I haven't monitored the disk space used (does sqlite use temporary files >>> beside the database file?) but the database file itself has been fixed in >>> size at around 12MB (12.461.056 bytes) the whole time. >>> >>> The load on the disk is minimal (sustained 1MB/s) and the CPU load is >>> about >>> 6%, which is a bit surprising since I thought I would be putting a huge >>> load >>> on the computer running a loop like this. I'm not at all happy to see >>> these >>> low load numbers given how the test is programmed (it should practically >>> own >>> the machine). The database should utilize the computer much better than >>> this. >>> >>> I've been running the test now for about 10 minutes using 3.6.15 and it >>> 'seems' to be behaving as before, slowly increasing in execution time. I >>> want to run this a bit longer to be absolutely sure and will let you know. >>> >>> JP >>> >>> -----Original Message----- >>> From: sqlite-users-boun...@sqlite.org [mailto: >>> sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov >>> Sent: 16. júní 2009 12:15 >>> To: General Discussion of SQLite Database >>> Subject: Re: [sqlite] Database inserts gradually slowing down >>> >>> How do you do step 5? Like "delete from table" or "delete from table >>> where ..."? Do you see any degrade in disk space used by database >>> along with slowness? >>> >>> Pavel >>> >>> On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll >>> Hafsteinsson<j...@lsretail.com> wrote: >>>> Hi >>>> >>>> I've been running some tests against sqlite and have found that inserts >>> are gradually slowing down. Since I'm new to sqlite I might be doing >>> something wrong and thought I'd ask here if anyone has seen this before or >>> know what might be causing this. >>>> The test I'm running creates a database containing a single table (int, >>> int, varchar(100)) along with an index (not unique) on the first field. I >>> then perform the following operations (all records have unique data in the >>> first field): >>>> >>>> 1. start a transaction >>>> >>>> 2. insert 1000 records >>>> >>>> 3. commit >>>> >>>> 4. repeat steps 1-3 100 times >>>> >>>> 5. delete everything from the table >>>> >>>> 6. Start again at step 1 >>>> >>>> The time taken to execute steps 1-4 increases gradually from taking >>> around 16 seconds the first time to just over 28 seconds after about 2400 >>> iterations. To me, this is alarming since this time increase seems not to >>> be >>> asymptotic (not sure though, there is a slight curve on the graph and I >>> haven't done any curve fitting) and I fear that I will end up with an >>> unusable slow database after some time of use. Even if I shut down the >>> test >>> application and start again (on the same database and without re-creating >>> the table), it just continues running as before it was stopped, that is, >>> taking 28 seconds (and increasing) if I stop it after 2400 iterations. >>>> At first I ran this for shorter periods without an index and think I saw >>> the same behavior, but I have to verify that to be sure. >>>> I'm using sqlite version 3.6.14 on Windows XP, compiled using Visual >>> Studio 2008. >>>> If anyone is interested I can e-mail the collected data and the test >>> program I'm using. >>>> Any pointers appreciated. >>>> >>>> Thanks, >>>> JP >>>> >>>> --------------------------------------------------------------------- >>>> "What you take away is more important than what you add." Chuck Moore >>>> >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> >> -- >> VerifEye Technologies Inc. >> 905-948-0015x245 >> 7100 Warden Ave, Unit 3 >> Markham ON, L3R 8B5 >> Canada >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
