How Scalable is SQLite? [closed]

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?

Alfaro

Answer 1

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.

^{* the site is no longer available}

Karelian

MySQL's "classic" database engine, MyISAM, has the same problems concerning concurrent read/write operations as SQLite. In fact, it locks every single row it touches in a write operation, making it impossible to scale write-intensive applications. Still, it served many web applications just fine. – Dordogne

Could you rewrite beginning of your answer then? Judging performance of DB without appropriate indexes is completely unfair. Also transactions change performance and scalability of SQLite a lot. – Fribble

@porneL: True, but SQLite without indexes was an order of magnitude slower than MySQL without indexes, and I also included a bit about transactions in my second edit. I still think that the progression of the answer makes some sense - it shows my initial naive use of SQLite and how relatively bad the performance was. I expect that those new to the platform will encounter similar problems, and I would hope that they can identify with the first paragraph, then read the following edits and realize that there are ways of speeding up SQLite to have acceptable performance. – Karelian

No mention of splitting up info on read performance vs. write performance? SQLite outperforms MySQL on read performance, doesn't it? I mean, especially if you use an index? And it performs even faster if you mount the database file on a volume with the noatime property, or if you run the "chattr -R +A {database dir}" command on the database directory. – Suannesuarez

Another valid point to make is the current trend of shared hosting. Many shared hosting servers I've worked with have had customers who really hammer MySQL servers, making MySQL not a great choice anymore unless you have heavy writes. On the otherhand, for low to medium size reads or writes, SQLite performance may outperform MySQL (especially if indices are properly used and the noatime property is turned off on the database directory) but only on these particular kinds of overloaded shared hosting plans. – Suannesuarez

Can you please share with us approximately how many hits per second your site is getting? – Niblick

@Niblick I took the site down a while ago, so I don't quite remember. It wasn't a lot - on the order of hundreds of users per day at the peak, but no more. However, if you want to do mainly reads instead of writes, SQLite can scale well beyond that. – Karelian

This is just about the first problem everyone working with SQLite runs into: sqlite.org/faq.html#q19 . It is also the problem with asking questions like these: only really the experts can give a fair answer. – Rhyme

I also had concurrency problems with updates on a website until I started wrapping batch updates/inserts in transactions. (even if there are only a couple of them) Observedly that causes much better locking performance. – Mistiemistime

There are also write-ahead-logging (WAL) available in newer SQLite versions which may take away some of the pain of read/write cycles. Things change. – Walke

@KyleCronin How do you roll up updates to the db schema and reference data when you deploy a new version of your site? – Hartle

@KonstantinTarkus When it comes to deploying schema updates, SQLite isn't really different than other databases (like MySQL and Postgres), and the techniques used to update them will likely work for SQLite as well. – Karelian

@KonstantinTarkus For example, whenever possible I try and make sure that my schema updates are backwards-compatible with production code. I then update the schema in production, then push the code changes that depend on the new schema. – Karelian

Off topic: I really wanted to upvote this answer, but the current count of 420 makes this the perfect answer needing no more validation. ;) – Skeens

Modern SQLite is quite scalable. If you use transactions, you can make upwards of 10,000 changes per second. Concurrency is the only issue with SQLite. However, that can be overcome by writing a custom TCP/IP server with a SQLite backend (i.e. a custom API). Your application connects to that TCP/IP server and sends JSON terminated by a newline. The server process extracts the JSON, talks to the DB, and returns the response as JSON terminated by a newline, periodically flushing transactions (e.g. every 3 seconds). A naïve server like that easily handles ~4,000 reqs/sec on a single host. – Hawthorne

Before someone points out what I describe looks like a mini-MySQL or Postgres, it certainly does...up to a point. A custom TCP/IP server, however, allows for caching information from the database in RAM that will be accessed regularly. The key is that all DB changes go through the custom server. A SQLite-backed custom server which presents a focused API can readily outperform the equivalent MySQL/Postgres-backed app while still getting all of the benefits of a database backend in the process. Unlike formal DB applications, SQLite is portable and now you know how to make it scalable too. – Hawthorne

Answer 2

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.

Walke

SQLite 3 supports reading when other users are writing to it. – Virgenvirgie

Note that the above comments are out of date, with the new(er) WAL system, writes and reads can be performed at the same time, increasing scalability. – Walke

Is it possible to create to export records on the fly to sqlite from any rdbms like sql server or oracle etc? – Mobilize

Answer 3

SQLite drives the sqlite.org web site and others that have lots of traffic. They suggest that if you have less than 100k hits per day, SQLite should work fine. And that was written before they delivered the "Writeahead Logging" feature.

If you want to speed things up with SQLite, do the following:

• upgrade to SQLite 3.7.x
• Enable write-ahead logging
• Run the following pragma: "PRAGMA cache_size = Number-of-pages;" The default size (Number-of-pages) is 2000 pages, but if you raise that number, then you will raise the amount of data that is running straight out of memory.

You may want to take a look at my video on YouTube called "Improve SQLite Performance With Writeahead Logging" which shows how to use write-ahead logging and demonstrates a 5x speed improvement for writes.

Impregnate

Answer 4

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 write access to the data store. This includes at some level most web sites ever created. If you even have to log in for anything, you probably need write access to the DB.

Dominik

I would disagree with the 'This includes pretty much every web site ever created.' comment. If the website are high load you are correct. Trac for instance uses SQLite by default and performs very nicely out of the box for small teams. – Conceal

Give it time: you'll have two developer access the same field at the same time and it'll choke. – Dominik

What do you define as choke? from your response I am guessing you do not have much experience with SQLite. SQLite will lock the entire file on operations so you might experience a delay, but it is near impossible to have it 'choke' in the situation you proposed. – Conceal

Andrew, because SQL Lite works well for small teams, doesn't make it scalable, to scalable the requirement is well to scale, meaning it should perform well with large teams. To my knowledge SQL Lite is not scalable to large teams/concurrent database operations that exceed a fairly low threshold. – Luciennelucier

@Justice. This answer has no supporting evidence of how scalable SQLite is. The answer by nobody is much better. – Alfaro

I think you mean "concurrent /write/ access to the database store", don't you? As well, I think you mean to say "file-based" database, not "desktop" database, correct? See, SQLite performs extremely well on reads, although yes it can be much slower than MySQL on writes. – Suannesuarez

This distinction between server software and desktop software is an arbitrary one. The only difference is whether they accept remote connections, not any performance metrics. Worse yet is to describe the difference as 'in-process'. For example, Postgresql is also a single process database like sqlite, but it has many of the features of mysql. – Rhyme

Answer 5

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.

Redmer

I agree VERY much with this. 99% of websites could be handled fine with SQLLite if you wanted to. But, 99% of web traffic goes to the largest 1% of websites, on the other hand. – Perseus

The metric of "100k hits/day" is utter rubbish. A "hit" is typically defined as a HTTP GET and a website with a bunch of sliced-up images might get 40+ "hits" per pageview--none of it touching the DB. Even if the docs are making the mistake of hit==pageview, it's still misleading. SQLite locks the entire DB on a write. While it may valiantly serve 100k pageviews of people just browsing records, it's going to fall apart in a write-intensive application (e-commerce, messageboard, etc). – Disengage

Answer 6

SQLite scalability 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 (instead of SQLite), 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.

Jez

You can get around with the issue of having very large indexed tables by sharding your data, e.g. one table per day / week. SQLite even allows you to split the tables into distinct database files and then use ATTACH DATABASE to create a virtual database connection with all the tables (hard limited to 62 databases however). – Virgenvirgie

Answer 7

Think of it this way. SQL Lite will be locked every time someone uses it (SQLite doesn't lock on reading). 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.

Aylward

s/uses it/writes to it . sqlite doesn't lock on read. – Loon

well, your answer can be misinterpreted easily. SQLite locks on write requests only. We're using SQLite for with more than 50GB of medical data in relational form and serving hundreds of simultaneous web clients for browsing and query. Its read performance is never worse than a recent MySQL. – Nason

MySQL's MyISAM isn't much better for concurrent access than SQLite. MySQL uses table-level locks a lot, and won't do concurrent writes except in few cases where layout of MyISAM is optimal. Unless you go for InnoDB (which has its own problems like never-shrinking datafile), you might not be much better off with MySQL. – Fribble

Answer 8

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.

Sigfried

Thx GateKiller, but please specify "low volume website". – Sigfried

Answer 9

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.

Elisa

and use transactions. That's crucial for SQLite. – Fribble