CODEWITHSUNDEEP
performancetestingsqlite
FrankDrebin
FrankDrebin

Reputation: 31

Sqlite3 insert performance test

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:

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

Platform

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

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

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

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

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?

Upvotes: 3

Views: 4746

Answers (1)

Sebastian Hojas
Sebastian Hojas

Reputation: 4210

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.

Upvotes: 2

Related Questions