How Scalable is SQLite?

Question

I recently read this Question about SQLite vs MySQL and the answer pointed out that SQLite doesn't scale well and the official website sort-of confirms this, however.

How scalable is SQLite and what are its upper most limits?

Answer 1

Sqlite is a desktop or in-process database. SQL Server, MySQL, Oracle, and their brethren are servers.

Desktop databases are by their nature not a good choices for any application that needs to support concurrent access to the data store. This includes pretty much every web site ever created.

Answer 2

Thanks for the comments. I still don't see any strong arguments though, mainly theories.

Answer 3

Sqlite is scalable in terms of single-user, I have multi-gigabyte database that performs very well and I haven't had much problems with it.

But it is single-user, so it depends on what kind of scaling you're talking about.

In response to comments. Note that there is nothing that prevents using an Sqlite database in a multi-user environment, but every transaction (in effect, every SQL statement that modifies the database) takes a lock on the file, which will prevent other users from accessing the database at all.

So if you have lots of modifications done to the database, you're essentially going to hit scaling problems very quick. If, on the other hand, you have lots of read access compared to write access, it might not be so bad.

But Sqlite will of course function in a multi-user environment, but it won't perform well.

Answer 4

I use MySQL for my forum and most of the time when I run "show processlist" there's only one thread accessing the database at any given time. I wonder how SQLite will behave in such a situation. Where concurrency is low but load is really high.

Answer 5

Yesterday I released a small site to track your rep that used a shared SQLite database for all visitors. Unfortunately, even with the modest load that it put on my host it ran quite slowly. This is because the entire database was locked every time someone viewed the page because it contained updates/inserts. I soon switched to MySQL and while I haven't had much time to test it out, it seems much more scaleable than SQLite. I just remember slow page loads and occasionally getting a database locked error when trying to execute queries from the shell in sqlite. That said, I am running another site from SQLite just fine. The difference is that the site is static (i.e. I'm the only one that can change the database) and so it works just fine for concurrent reads. Moral of the story: only use SQLite for websites where updates to the database happen rarely (less often than every page loaded).

edit: I just realized that I may not have been fair to SQLite - I didn't index any columns in the SQLite database when I was serving it from a web page. This partially caused the slowdown I was experiencing. However, the observation of database-locking stands - if you have particularly onerous updates, SQLite performance won't match MySQL or Postgres.

another edit: Since I posted this almost 3 months ago I've had the opportunity to closely examine the scalability of SQLite, and with a few tricks it can be quite scalable. As I mentioned in my first edit, database indexes dramatically reduce query time, but this is more of a general observation about databases than it is about SQLite. However, there is another trick you can use to speed up SQLite: transactions. Whenever you have to do multiple database writes, put them inside a transaction. Instead of writing to (and locking) the file each and every time a write query is issued, the write will only happen once when the transaction completes.

The site that I mention I released in the first paragraph has been switched back to SQLite, and it's running quite smoothly once I tuned my code in a few places.

Answer 6

Think of it this way. SQL Lite will be locked every time someone uses it. So if your serving up a web page or a application that has multiple concurrent users only one could use your app at a time with SQLLite. So right there is a scaling issue. If its a one person application say a Music Library where you hold hundreds of titles, ratings, information, usage, playing, play time then SQL Lite will scale beautifully holding thousands if not millions of records(Hard drive willing)

MySQL on the other hand works well for servers apps where people all over will be using it concurrently. It doesn't lock and it is quite large in size. So for your music library MySql would be over kill as only one person would see it, UNLESS this is a shared music library where thousands add or update it. Then MYSQL would be the one to use.

So in theory MySQL scales better then Sqllite cause it can handle mutiple users, but is overkill for a single user app.

Answer 7

It might be worth checking out REAL SQL Server, which is a database server built on SQLite.

Answer 8

SQLite's website (the part that you referenced) indicates that it can be used for a variety of multi-user situations.

I would say that it can handle quite a bit. In my experience it has always been very fast. Of course, you need to index your tables and when coding against it, you need to make sure you use parameritized queries and the like. Basically the same stuff you would do with any database to improve performance.

Answer 9

Have you read this SQLite docs - http://www.sqlite.org/whentouse.html ?

SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

Answer 10

In reply to GateKiller (above), if your doing updates/inserts without using SQL Transaction statements, then SQLLite will "appear" to be slow. If you send SQL in transactions, the speed will be way way faster. Most SQL servers dont require that kind of thing but SQLlite, Firebird, and JavaDB are some that do require formal transactions, which is why they are free...

Please read this: http://www.sqlite.org/lang_transaction.html

Answer 11

Hi there,

i think that a (in numbers 1) webserver serving hunderts of clients appears on the backend with a single connection to the database, isn't it?

So there is no concurrent access in the database an therefore we can say that the database is working in 'single user mode'. It makes no sense to diskuss multi-user access in such a circumstance and so SQLite works as well as any other serverbased database.

Please help me if i am totaly wrong.

Peace

Ice

Answer 12

SQLite scaling will highly depend on the data used, and their format. I've had some tough experience with extra long tables (GPS records, one record per second). Experience showed that SQLite would slow down in stages, partly due to constant rebalancing of the growing binary trees holding the indexes (and with time-stamped indexes, you just know that tree is going to get rebalanced a lot, yet it is vital to your searches). So in the end at about 1GB (very ballpark, I know), queries become sluggish in my case. Your mileage will vary.

One thing to remember, despite all the bragging, SQLite is NOT made for data warehousing. There are various uses not recommended for SQLite. The fine people behind SQLite say it themselves:

Another way to look at SQLite is this: SQLite is not designed to replace Oracle. It is designed to replace fopen().

And this leads to the main argument (not quantitative, sorry, but qualitative), SQLite is not for all uses, whereas MySQL can cover many varied uses, even if not ideally. For example, you could have MySQL store Firefox cookies, but you'd need that service running all the time. On the other hand, you could have a transactional website running on SQLite (as many people do) instead of MySQL, but expect a lot of downtime.

Answer 13

I have found Kyle Cronin's example useful. Actually for similar reason I want to use Sqlite. I thought to develop a small app for my blog for tracking the current visitors. but this last comment has confused me. I am thinking of using MySQL for write when new visitors come and Sqlite for showing online visitors to my visitors. So I will update sqlite after each few seconds.