On Sat, 9 Apr 2005, Al Danial wrote: >On Apr 9, 2005 12:43 AM, Andy Lutomirski <[EMAIL PROTECTED]> wrote: >> Al Danial wrote: >> > The attached C program measures insert performance for populating >> > a table with an integer and three random floating point values with >> > user defined transaction size. Usage is: >> > >> > ./sqlite_insert <number of rows> <transaction size> >> >> All of these are on Gentoo, Athlon 64 3200+, running 64 bit. >> >> Writing to /tmp, which is ext3 (acl,user_xattr,usrquota) over RAID0 (two >> slave drives on different channels): >> >> $ ./sqlitetest 100000 50000 >> 100000 inserts to /tmp/a.db in 0.531 s = 188446.34 inserts/s >> $ ./sqlitetest 2000000 50000 >> 2000000 inserts to /tmp/a.db in 11.546 s = 173223.61 inserts/s > >That's impressive. Clearly a well-implemented RAID0 configuration >and fast disks make a huge difference. This will be the direction I'll >take.
My machine, 2x Athlon XP 1700+, 512MB RAM, U160 SCSI (3x10K disks): [EMAIL PROTECTED] tmp]$ ./sqlite_insert 100000 50000 100000 inserts to ./a.db in 2.495 s = 40081.96 inserts/s [EMAIL PROTECTED] tmp]$ ./sqlite_insert 2000000 50000 2000000 inserts to ./a.db in 47.068 s = 42491.71 inserts/s While I have a big fat SCSI IO system, vmstat during the run shows only shows about 2MB/s ever going to the HD, well within the capabilities of any SCSI or IDE HD (Laptop even!) [EMAIL PROTECTED] csmith]$ vmstat 1 procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 3 0 93296 116156 30424 229604 0 0 9 14 62 31 96 3 1 0 3 0 93296 114308 30484 231548 0 0 0 2167 333 1194 99 1 0 0 7 0 93296 112488 30512 233488 0 0 0 2064 320 1092 99 1 0 0 4 0 93296 110716 30540 235432 0 0 0 2072 314 1060 99 1 0 0 4 0 93296 108844 30568 237504 0 0 128 2068 319 1069 99 1 0 0 4 0 93296 107380 30596 239444 0 0 0 2076 383 1252 98 2 0 0 2 2 93296 103352 30640 241392 0 0 0 2101 321 1111 96 4 0 0 > >Thanks to everyone who posted performance numbers and machine >setup info. Some results were counterintuitive (I'd have guessed >SCSI drives would come out on top) but many variables are at work >so I won't try to draw too many conclusions. The test given is clearly CPU bound. All the big numbers are from people with big CPUs, with equally big RAM performance as well, probably. >Also thanks to the suggestions to study the pragma's. I did try >SYNCHRONOUS=off but that didn't seem to have an effect; I'll >study the docs to make sure I've got it right. -- Al For CPU bound work, it won't. You appear to be maxing out what the CPU and memory subsystem can handle. Tweaking IO will not help. Using ":memory:" gave about a 10% speedup, so that looks like the maximum amount of speedup you could possibly hope for. Christian -- /"\ \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \