[EMAIL PROTECTED] wrote: > I'm trying to insert some 1,000 rows a second, with about 200-300 bytes per > row--it's overview data from an NNTP server. Also, FWIW, this is on OS X and > I've bumped the page size up to 4096 since that's the memory page size (I > think), and the rows were exactly the wrong size for the default--pages could > only hold one row, so I was using about 4x as much disk space as I needed. > > Is 200K/s unreasonable for SQLite on a modern-ish (1GHz G4 powerbook) > machine? When I sample the app I see that it's spending most of its time on > the filesystem--unlink, read, open, write. I was hoping there would be an > easy way to turn off journaling so I could see how much of the disk access > was from that.. >
See http://www.sqlite.org/speed.html. In the second test, SQLite does over 25000 inserts/sec. Granted, those inserts are about 4 times smaller than yours, but there are 25 times more of them, so you would think that would balance out. After all 25000 inserts, an analysis of the table T2 shows this:
Percentage of total database.......... 99.86% Number of entries..................... 25000 Bytes of storage consumed............. 2247680 Bytes of payload...................... 1792651 79.8%
Bytes of data......................... 1692651 75.3%
Bytes of key.......................... 100000 4.4%
Average payload per entry............. 71 Average unused bytes per entry........ 3 Average fanout........................ 11.39 Maximum payload per entry............. 86 Entries that use overflow............. 0 0.000%
Total pages used...................... 2195 Primary pages used.................... 2195 Overflow pages used................... 0 Unused bytes on primary pages......... 99628 4.4%
Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 99628 4.4%
The SQL script used to implement the second speed test can be found at http://www.sqlite.org/test2.sql.gz. Why not download it and run it against your copy of the sqlite shell. Like this:
zcat test2.sql.gz | time ./sqlite speed.db
The above reports 0.78 seconds (wallclock time) on my system, for more than 32000 inserts per second or over 2MB per second. What are you getting?
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
