Sqlite3 insert performance test

3
votes

In environments of hign concurrency SQLite block table with lose data. I'm looking the way for improve the performance of SQLite with high concurrency without lose data for insert query. My intention is know the limit of concurrent users for insert in way that a site work (in this case 1 insert for request) with a “high concurrency”. For to make this test case more simple, users will send data for to save in database

After looking how improve the performance and use advice other users:

  • sqlite.org
  • sqlite.org/faq.html#q19
  • stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite
  • stackoverflow.com/questions/54998/how-scalable-is-sqlite

I decided to make a little test in a amazon small instance.

Platform

  • Linux version 2.6.35.14-106.53.amzn1.i686 ([email protected]) (gcc version 4.4.5 20110214 (Red Hat 4.4.5-6) (GCC) ) #1 SMP Fri Jan 6 16:20:23 UTC 2012
  • Sqlite 3.6.20
  • Lighttpd 1.4.29
  • Php 5.3.10

test.php with insert sql:

PRAGMA synchronous = OFF; BEGIN TRANSACTION; INSERT INTO test (data1,date) VALUES ('".$_POST['data1']."',date() ); END TRANSACTION;

Use Apache HTTP server benchmarking tool.

Database is a file in file system not in ram.

CONCLUSION: Test 1: ab -n 10000 -c 50 -k xxxx.xxx/test.php?data1=fc82bf71a0d5f8a3c2dc868ebe0d6eaa

  • Time taken for tests: 63.637 seconds
  • Complete requests: 10000
  • Failed requests: 0
  • Sqlite inserted rows: 10050
  • Average: 159.52 insert/sg

Test2: ab -n 10000 -c 100 -k xxxx.xxx/test.php?data1=fc82bf71a0d5f8a3c2dc868ebe0d6eaa

  • Time taken for tests: 64.221 seconds
  • Complete requests: 10000
  • Failed requests: 0
  • Sqlite inserted rows: 10100
  • Average: 157.26 insert/sg

Test3: ab -n 10000 -c 150 -k xxxx.xxx/test.php?data1=fc82bf71a0d5f8a3c2dc868ebe0d6eaa

  • Time taken for tests: 33.338 seconds
  • Complete requests: 10000
  • Failed requests: 7095
  • (Connect: 0, Receive: 0, Length: 7095, Exceptions: 0)
  • SQLITE: 2905 inserted rows
  • Average: LOST DATA!!

TEST4: ab -n 10000 -c 200 -k xxxx.xxx/test.php?data1=fc82bf71a0d5f8a3c2dc868ebe0d6eaa

  • Time taken for tests: 33.705 seconds
  • Complete requests: 10000
  • Failed requests: 7049
  • (Connect: 0, Receive: 0, Length: 7049, Exceptions: 0)
  • SQLITE: 2918 inserted rows
  • Average: LOST DATA!!

In this specific environment we can use SQLite up to 100 concurrent users with average of 157.26 insert/sg. You take into account this result is only for insert data.

From my ignorance, is possible prevent lose data? is possible to improve this performance?

1
performancetestingsqlite
I love SQLite, but you are really not playing to its strengths... Why not log to a file and then periodically import that've to SQLite - Eli
We are using sqlite3 on production with about 20 users for web based Rails application(not all of them are online at the same time). So far we haven't heard of complaint yet. Now we are working on a web application with about 60 users and we are wondering if we need to use other database such as postgres or mysql. You post helps to understand the limit of the sqlite3 and 100 concurrent users look good to our application. Did you try to fire up the insert from multiple pc or with larger data set inserted? Sqlite3 is very simple to use and we love it. - user938363
is there c between c=100 & 150 tested without lost data? - user938363
You dont even include your php test codes, may we can improve it - a55

1 Answers

2
votes

I think that Sqlite3 should not be used in your case. Yes, sqlite3 is one of the embedded databases which handles concurrency pretty well, but to scale well and to improve the performance I would suggest to simply use a server/client database.

Let's look at your last test where you are firing 10000 request in about 30 seconds. If they are distributed equally, Sqlite3 would not be allowed to need more than 3ms per transaction (keeping in mind that sqlite3 only allows one write process at a time). Let's look at the sqlite documentation. (http://www.sqlite.org/faq.html)

We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds.

To summarise: Just the locking of the database needs a few milliseconds, not speaking about the update itself.

However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.

You have several possibilities:

  • Change the way your application works. It is very unlikely that you will need that much concurrency.
  • If you still insist that you need the concurrency and a good and solid performance, switch to a client/server database.
  • Accept that some of the request are failing when your load is peaking.