Re: [sqlite] Performance problem with 3.2.7
Thank you very much. I am happy to hear that the performance I am seeing is in line with what others have observed. I am running this on Windows XP. On Tue, 22 Nov 2005, Akira Higuchi wrote: > Hi, > > On Mon, 21 Nov 2005 10:56:41 -0500 (EST) > Shane Baker <[EMAIL PROTECTED]> wrote: > > > I just need to figure out why my performance is about 30x slower than what > > others are reporting when using the library in similar ways. > > Are you using sqlite on windows or MacOS X? > > As I tested, sqlite performs 10 write transactions per sec on > windows and MacOS X. On Linux, sqlite performs up to 300 write > transactions per sec if HDD cache is enabled. However, when HDD cache > is disabled (/sbin/hdparm -W0 /dev/hda etc.), it drops to 10 > transactions per sec. > > To ensure ACID compliance, sqlite seems to be using FlushFileBuffers() on > windows, F_FULLFSYNC fcntl() on MacOS X, and fsync() on other unixes. > FlushFileBuffers and F_FULLFSYNC fcntl() flush HDD write-back > cache, and they are very slow. However, many implementations of fsync() > don't flush HDD cache (they flush OScache only). It's very fast, but > dangerous (not ACID compliant) if HDD cache has no battery backup. > (So i'm using sqlite on Linux with HDD cache off.) >
Re: [sqlite] Performance problem with 3.2.7
Hi, On Mon, 21 Nov 2005 10:56:41 -0500 (EST) Shane Baker <[EMAIL PROTECTED]> wrote: > I just need to figure out why my performance is about 30x slower than what > others are reporting when using the library in similar ways. Are you using sqlite on windows or MacOS X? As I tested, sqlite performs 10 write transactions per sec on windows and MacOS X. On Linux, sqlite performs up to 300 write transactions per sec if HDD cache is enabled. However, when HDD cache is disabled (/sbin/hdparm -W0 /dev/hda etc.), it drops to 10 transactions per sec. To ensure ACID compliance, sqlite seems to be using FlushFileBuffers() on windows, F_FULLFSYNC fcntl() on MacOS X, and fsync() on other unixes. FlushFileBuffers and F_FULLFSYNC fcntl() flush HDD write-back cache, and they are very slow. However, many implementations of fsync() don't flush HDD cache (they flush OScache only). It's very fast, but dangerous (not ACID compliant) if HDD cache has no battery backup. (So i'm using sqlite on Linux with HDD cache off.)
Re: [sqlite] Performance problem with 3.2.7
Thank you very much for the feedback. I understand your point, hardware takes a deterministic amount of time. I have been basing my assumptions on these sources: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations (See "Transactions and performance") http://blog.amber.org/2004/11/28/sqlite-insertion-performance/ There was one other, but I can't find it. For the time being, I don't think that inserts are going to happen very frequently in my application and I can probably roll updates into transactions. Thanks again. On Mon, 21 Nov 2005, Christian Smith wrote: > 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 > / \ >
Re: [sqlite] Performance problem with 3.2.7
No, as I mentioned in my original message, I am not wrapping them. I don't want to test an unrealistic scenario for my application. In my application, there are multiple sources that will be inserting into the database and pooling the information for a bulk insert won't work. I understand that I will get better performance by inserting all of my rows inside a transaction. What I don't understand is why, when NOT using a transaction, I get about 7 inserts per second compared to others who are reporting between 200 and 300. I am working with reasonable hardware. I just need to figure out why my performance is about 30x slower than what others are reporting when using the library in similar ways. On Mon, 21 Nov 2005, Chris Schirlinger wrote: > Are you wrapping the transactions in between Begin/End Transactions? > > BEGIN TRANSACTION; > INSERT INTO table (foo) VALUES (bar); > INSERT INTO table (foo) VALUES (par); > INSERT INTO table (foo) VALUES (tar); > INSERT INTO table (foo) VALUES (far); > .. > INSERT INTO table (foo) VALUES (car); > INSERT INTO table (foo) VALUES (jar); > INSERT INTO table (foo) VALUES (mar); > COMMIT TRANSACTION; > > Check out this document for more info > http://www.sqlite.org/lang_transaction.html > > > I'm sure I must be doing something wrong. This is my first attempt at > > working with SQLite. >
Re: [sqlite] Performance problem with 3.2.7
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 / \
Re: [sqlite] Performance problem with 3.2.7
Are you wrapping the transactions in between Begin/End Transactions? BEGIN TRANSACTION; INSERT INTO table (foo) VALUES (bar); INSERT INTO table (foo) VALUES (par); INSERT INTO table (foo) VALUES (tar); INSERT INTO table (foo) VALUES (far); .. INSERT INTO table (foo) VALUES (car); INSERT INTO table (foo) VALUES (jar); INSERT INTO table (foo) VALUES (mar); COMMIT TRANSACTION; Check out this document for more info http://www.sqlite.org/lang_transaction.html > I'm sure I must be doing something wrong. This is my first attempt at > working with SQLite.
