Sqlite on a production server?

Question

I have known about Sqlite for a long time and I know it is blazing fast, but I have never tried it in a production server because I was never able to find a solid estimate on how much traffic it could handle before failing. Does anyone have any numbers or an article on this?

Answer 1

Unfortunately, I haven't got any figures for you about load capabilities, but a few comments about some of the performance-restricting factors:

• The speed of SQLite is affected by the speed of the disk that it is on and whether there are lots of inserts/updates going on (i.e. write access). The write-lock is limited by the disk spin speed

• Transactions are started by default, but you get better performance if you start and commit the transaction. I have had very fast mass inserts when handling the transaction programmatically

• If you are generally only reading data then you get good performance in my experience. So, SQLite can be used as a caching system to store database server reads, particulary remote ones or complex queries.

• It uses less resources than a database server, so this can affect site performance as freeing more resources for the Web server and application code

• If you require a number of concurrent writes to be possible, then a database server (e.g. MySQL, Postgres) may well serve you better

As Devrim stated, the SQLite site states around 100k users/day should be fine. A Trac system requires writes, so performance would probably be slower in that case

Answer 2

Sqlite is great for embedding in applications, and that is what it is designed for, but it most certainly isn't "blazingly fast". I use it for several of my own applications, purely for the convenience of only having two files that can be copied to another machine to give a fully working application. Tests against MySQL, using the same structure, indexes, etc., shows Sqlite to be considerably slower, even for for small databases. I would expect the performance difference to grow as the database size grows, although I can't say for certain as I've only used it with databases of less than 100MB.

Answer 3

I think sqlite is only faster than a text/xml file (you may be surprised if you tried it). And it doesn't support concurrency, if u want to create a site for intranet where people register their work hours or use trac ticketing it may serve well. Other than that it should be avoided and replaced by mysql or couchdb.

sqlite website says 100k users/day should be fine but i highly doubt it, since a simple trac project gets stuck a lot with 10 ppl office use.

Answer 4

Sqlite isn't a traditional client/server DB application. It's essentially a library that's embedded within another application. It's designed for single-user desktop applications. You absolutely do not want to try to use it as some sort of standalone MySQL/PostgreSQL/MS-SQL replacement in a multiuser enviroment because the entire DB is locked on write. You'll be dealing with contention issues on even a light load which will destroy performance.

Answer 5

I have some points to add to these good answers.

The current version of SQLite has WAL (Write-Ahead Logging) so that reading and writing can proceed concurrently. So the traditional single writer limitation mentioned in the previous answers no longer exists. I haven't seen WAL in production yet so I cannot comment how well it scales.

Using WAL or not, if your SQLite database is read only (or is batch updated) and it fits in RAM (your OS has enough spare RAM to keep it in buffers) it can scale very well on a production web application. I personally was very sceptic about its performance, scalability and robustness but now after nine months in production it has proven to run even the most complex parts of the system very well.