On Mon, 21 Nov 2005, Shane Baker wrote:
>I'm sure I must be doing something wrong. This is my first attempt at
>working with SQLite.
We'll see...
>
>I have a simple table, with 7 columns. There are 6 integers and a BLOB,
>with the primary key being on an integer. When I try to run inserts (one
>insert per transacion - I know this is not optimal, but it represents my
>application's usage), I am only getting about 7 inserts per second, on
>average.
>
>My first suspect was the BLOB and the fact that I was binding this
>parameter using SQLITE_TRANSIENT (using sqlite3_bind_blob). I removed the
>BLOB from the schema altogether, leaving just 6 integers, and I still have
>the same performance.
The performance problem is the synchronous IO bottleneck of doing only a
single insert per transaction.
>
>For reference, I am getting around 10,000 queries per second when I lookup
>a row based on the primary key column.
>
>All performance measurements I've seen posted by others suggest between
>200 and 300 inserts per second with one insert per transaction.
Probably not, unless this is to a FLASH device, for example. The
Bottleneck in hard disk IO is the rotational and head movement latencies
to write data to the platters. Assuming no head movement, a 7200 rpm disk
will only allow the same sector to be rewritten 1/7200 times a minute,
which is 120 times a second. Add in that many different sectors need to be
updated synchronously, and throughput drops dramatically.
A quick test indicates that I can almost double the performance on
Linux/ext3 by having "data=journal" option set in the mount flags. This is
because head movement is reduced significantly. A test that previously
took ~500 seconds (13785 inserts without transactions) took 280 seconds
with "data=journal". For reference, the same data inserted with a single
transaction took ~1.2 seconds!
>
>I haven't run a profiler yet but hope to do this tomorrow. Does anyone
>have any ideas as to what I might be doing wrong, or where I should look?
If you can change your model to insert more than 1 row per transaction,
you should see a significant performance increase. You'll see roughly N
times the performance for small N.
If this is not an option, look at your storage and how you can reduce
latency. FLASH devices have low latency, being solid state, and some RAID
controllers have battery backed buffers, and so may have lower latency.
>
>Thanks in advance.
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
