Ah, of course. Thanks for the explanation Jim. JP
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Wilcoxson Sent: 16. júní 2009 15:07 To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down 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 > -- Software first. Software lasts! _______________________________________________ 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
