SQLite on a production server? [closed]

Solution 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

Solution 2:

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.

Solution 3:

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.