This may be a red herring, but how do you generate the 'random' characters for the test? Could entropy exhaustion affecting the results?
Just a thought... *** Doug Fajardo -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Tuesday, June 16, 2009 12:29 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down On Tue, Jun 16, 2009 at 1:59 PM, Jens Páll Hafsteinsson<j...@lsretail.com> wrote: > Ok, but you might have to run this a bit longer (or more iterations). I ran the code a bit longer, and yes, I do notice an increase. Here are my results after 610 iterations... I have provided the numbers for 10 iterations every hundred (my numbers are lower this time than the earlier posted 8 seconds or so per iteration because of a different way of generating random strings). 1. 3 wallclock secs ( 0.87 usr + 0.24 sys = 1.11 CPU) 2. 2 wallclock secs ( 0.88 usr + 0.26 sys = 1.14 CPU) 3. 3 wallclock secs ( 0.89 usr + 0.26 sys = 1.15 CPU) 4. 2 wallclock secs ( 0.88 usr + 0.27 sys = 1.15 CPU) 5. 3 wallclock secs ( 0.88 usr + 0.26 sys = 1.14 CPU) 6. 2 wallclock secs ( 0.88 usr + 0.27 sys = 1.15 CPU) 7. 3 wallclock secs ( 0.89 usr + 0.27 sys = 1.16 CPU) 8. 2 wallclock secs ( 0.88 usr + 0.26 sys = 1.14 CPU) 9. 3 wallclock secs ( 0.89 usr + 0.27 sys = 1.16 CPU) 10. 2 wallclock secs ( 0.88 usr + 0.27 sys = 1.15 CPU) .. 200. 4 wallclock secs ( 0.89 usr + 0.30 sys = 1.19 CPU) 201. 4 wallclock secs ( 0.89 usr + 0.29 sys = 1.18 CPU) 202. 4 wallclock secs ( 0.89 usr + 0.30 sys = 1.19 CPU) 203. 3 wallclock secs ( 0.88 usr + 0.31 sys = 1.19 CPU) 204. 4 wallclock secs ( 0.89 usr + 0.30 sys = 1.19 CPU) 205. 4 wallclock secs ( 0.88 usr + 0.31 sys = 1.19 CPU) 206. 4 wallclock secs ( 0.90 usr + 0.30 sys = 1.20 CPU) 207. 4 wallclock secs ( 0.89 usr + 0.29 sys = 1.18 CPU) 208. 4 wallclock secs ( 0.88 usr + 0.30 sys = 1.18 CPU) 209. 4 wallclock secs ( 0.89 usr + 0.30 sys = 1.19 CPU) 210. 3 wallclock secs ( 0.89 usr + 0.30 sys = 1.19 CPU) .. 300. 5 wallclock secs ( 0.89 usr + 0.32 sys = 1.21 CPU) 301. 4 wallclock secs ( 0.90 usr + 0.33 sys = 1.23 CPU) 302. 5 wallclock secs ( 0.89 usr + 0.34 sys = 1.23 CPU) 303. 6 wallclock secs ( 0.89 usr + 0.33 sys = 1.22 CPU) 304. 5 wallclock secs ( 0.89 usr + 0.32 sys = 1.21 CPU) 305. 6 wallclock secs ( 0.90 usr + 0.33 sys = 1.23 CPU) 306. 5 wallclock secs ( 0.89 usr + 0.33 sys = 1.22 CPU) 307. 5 wallclock secs ( 0.89 usr + 0.31 sys = 1.20 CPU) 308. 8 wallclock secs ( 0.89 usr + 0.32 sys = 1.21 CPU) 309. 13 wallclock secs ( 0.90 usr + 0.35 sys = 1.25 CPU) 310. 6 wallclock secs ( 0.90 usr + 0.33 sys = 1.23 CPU) .. 400. 5 wallclock secs ( 0.89 usr + 0.33 sys = 1.22 CPU) 401. 5 wallclock secs ( 0.89 usr + 0.32 sys = 1.21 CPU) 402. 5 wallclock secs ( 0.89 usr + 0.33 sys = 1.22 CPU) 403. 5 wallclock secs ( 0.89 usr + 0.33 sys = 1.22 CPU) 404. 5 wallclock secs ( 0.89 usr + 0.32 sys = 1.21 CPU) 405. 6 wallclock secs ( 0.89 usr + 0.34 sys = 1.23 CPU) 406. 5 wallclock secs ( 0.89 usr + 0.33 sys = 1.22 CPU) 407. 5 wallclock secs ( 0.89 usr + 0.33 sys = 1.22 CPU) 408. 5 wallclock secs ( 0.89 usr + 0.33 sys = 1.22 CPU) 409. 5 wallclock secs ( 0.89 usr + 0.33 sys = 1.22 CPU) 410. 5 wallclock secs ( 0.89 usr + 0.33 sys = 1.22 CPU) .. 500. 5 wallclock secs ( 0.89 usr + 0.34 sys = 1.23 CPU) 501. 6 wallclock secs ( 0.89 usr + 0.34 sys = 1.23 CPU) 502. 6 wallclock secs ( 0.89 usr + 0.34 sys = 1.23 CPU) 503. 6 wallclock secs ( 0.90 usr + 0.36 sys = 1.26 CPU) 504. 6 wallclock secs ( 0.88 usr + 0.37 sys = 1.25 CPU) 505. 11 wallclock secs ( 0.89 usr + 0.36 sys = 1.25 CPU) 506. 9 wallclock secs ( 0.89 usr + 0.34 sys = 1.23 CPU) 507. 5 wallclock secs ( 0.89 usr + 0.34 sys = 1.23 CPU) 508. 6 wallclock secs ( 0.89 usr + 0.35 sys = 1.24 CPU) 509. 6 wallclock secs ( 0.89 usr + 0.34 sys = 1.23 CPU) 510. 6 wallclock secs ( 0.88 usr + 0.34 sys = 1.22 CPU) .. 600. 6 wallclock secs ( 0.89 usr + 0.36 sys = 1.25 CPU) 601. 7 wallclock secs ( 0.89 usr + 0.35 sys = 1.24 CPU) 602. 6 wallclock secs ( 0.88 usr + 0.35 sys = 1.23 CPU) 603. 7 wallclock secs ( 0.89 usr + 0.35 sys = 1.24 CPU) 604. 6 wallclock secs ( 0.89 usr + 0.35 sys = 1.24 CPU) 605. 6 wallclock secs ( 0.89 usr + 0.35 sys = 1.24 CPU) 606. 7 wallclock secs ( 0.89 usr + 0.35 sys = 1.24 CPU) 607. 6 wallclock secs ( 0.88 usr + 0.35 sys = 1.23 CPU) 608. 7 wallclock secs ( 0.89 usr + 0.35 sys = 1.24 CPU) 609. 6 wallclock secs ( 0.89 usr + 0.36 sys = 1.25 CPU) 610. 7 wallclock secs ( 0.89 usr + 0.35 sys = 1.24 CPU) > My first 10 runs give the following results (in seconds): > > 1 15,681 > 2 16,010 > 3 16,093 > 4 16,168 > 5 16,057 > 6 16,585 > 7 16,114 > 8 16,596 > 9 16,115 > 10 16,270 > > Jumping around a bit but it seems linear (but it's not). I'm not saying that > 3.6.13 behaves the stame as 3.6.14 but this only becomes really apparent when > run over an extended period of time. 3.6.15 looks promising though in this > respect but looking at the first 10 runs of that version does definitely not > say the whole story (at least in my case): > > 1 17,874 > 2 21,753 > 3 23,5 > 4 26,591 > 5 27,925 > 6 29,682 > 7 29,807 > 8 31,944 > 9 32,422 > 10 34,144 > > Increasing like crazy (but seems to level off later at 40 seconds). > > JP > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor > Sent: 16. júní 2009 16:55 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Database inserts gradually slowing down > > On Tue, Jun 16, 2009 at 10:57 AM, Jens Páll > Hafsteinsson<j...@lsretail.com> wrote: >> The key factor here is not the total time taken to perform these operations >> but the fact that the time is *increasing* for each run. I am looking for >> consistency in that I need to be able to let the application perform these >> steps in constant time over a long period of time (months). >> > > > Using SQLite 3.6.13, Perl 5.8.8, on a Mac OS X Leopard Macbook with 4 > GB RAM and 320 GB 7200 RPM Seagate drive, I get > > [11:27 AM] ~/Projects/sqlite_test$perl db_slows.pl > 1. the code took: 8 wallclock secs ( 6.83 usr + 0.30 sys = 7.13 CPU) > 2. the code took: 9 wallclock secs ( 6.78 usr + 0.34 sys = 7.12 CPU) > 3. the code took: 8 wallclock secs ( 6.80 usr + 0.34 sys = 7.14 CPU) > 4. the code took: 8 wallclock secs ( 6.78 usr + 0.34 sys = 7.12 CPU) > 5. the code took: 8 wallclock secs ( 6.78 usr + 0.33 sys = 7.11 CPU) > 6. the code took: 8 wallclock secs ( 6.81 usr + 0.33 sys = 7.14 CPU) > 7. the code took: 8 wallclock secs ( 6.80 usr + 0.34 sys = 7.14 CPU) > 8. the code took: 9 wallclock secs ( 6.80 usr + 0.35 sys = 7.15 CPU) > 9. the code took: 8 wallclock secs ( 6.79 usr + 0.34 sys = 7.13 CPU) > 10. the code took: 8 wallclock secs ( 6.90 usr + 0.34 sys = 7.24 CPU) > > Pretty linear performance. As stated in the OP, I > 1. Insert 1000 records (an integer and a 100 random char string; > 2. Commit; > 3. Repeat 1 one hundred times; > 4. DELETE all records; > 5. Repeat 1-4 x 10. > > The table is > > CREATE TABLE foo (id INTEGER PRIMARY KEY, num INTEGER, desc TEXT); > > There are no indexes. > > > man/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Carbon Model http://carbonmodel.org/ Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/ Science Commons Fellow, Geospatial Data http://sciencecommons.org Nelson Institute, UW-Madison http://www.nelson.wisc.edu/ ----------------------------------------------------------------------- collaborate, communicate, compete ======================================================================= _______________________________________________ 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
