views:

6027

answers:

13

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?

+12  A: 

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.

Joel Coehoorn
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.
Andrew Burns
Give it time: you'll have two developer access the same field at the same time and it'll choke.
Joel Coehoorn
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.
Andrew Burns
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.
Pop Catalin
This should be the accepted answer.
Justice
@Justice. This answer has no supporting evidence of how scalable SQLite is. The answer by nobody is much better.
GateKiller
A: 

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

GateKiller
+17  A: 

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.

Lasse V. Karlsen
While the ideal use case for SQLite might be a single user, it doesn't mean it can't be used in a multi user environment.
Sam
Perhaps I should've been clearer. I'll edit my answer.
Lasse V. Karlsen
SQLite 3 supports reading when other users are writing to it.
Alix Axel
It does? That's not been my experience, and the documentation doesn't seem to support that claim (http://www.sqlite.org/lockingv3.html). Quote: "An EXCLUSIVE lock is needed in order to write to the database file. Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock."
Lasse V. Karlsen
+1  A: 

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.

Seun Osewa
+127  A: 

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.

Kyle Cronin
Thanks for the response. I've accepted your answer because you have real world experience of sqlite vs another db system :)
GateKiller
Great work to come back after 3 months and update your answer. big +1.
Mat Nadrofsky
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.
Henning
I agree with Mat's comment. A big +1 for returning to update the post after almost 3 months with new and useful information. If I could, I'd give you another point. Such a commitment should get rewarded!
Adam Asham
Great stackoverflow citizen. Greaat research. Great follow-up
Pita.O
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.
porneL
@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.
Kyle Cronin
great followup poster!
djangofan
+1 for coming back
Sachin
You know, you could have just +1up'ed Mat's +1 - rather than +1up-ing all these +1up'ers.
Xeoncross
+1  A: 

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.

peregrine
s/uses it/writes to it . sqlite doesn't lock on read.
Gregg Lind
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.
Berk D. Demir
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.
porneL
A: 

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

Paul Lefebvre
Looks interesting. Might be something I'll use in the future.
GateKiller
I dont think any site warrants spending $299 for "REAL SQL Server" when most sites dont get enough traffic to even begin reaching SQLLite's limits.
djangofan
+1  A: 

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.

jle
and use transactions. That's crucial for SQLite.
porneL
+8  A: 

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.

Sam
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.
djangofan
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).
jamieb
+1  A: 

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

djangofan
It's not about requiring "formal transactions", it's about how much overhead is involved in creating and destroying transactions. SQLite happens to have a relatively high amount of overhead, and because transactions are opened and closed for each statement (unless they're managed manually at a higher level), it adds up quickly. For the record, MSSQL also does these "implicit transactions" if you don't manage them yourself.
Mark
Based on my own testing, wrapping several operations in a single transaction can lead to dramatic speed improvements for SQLite. However, the asynchronous nature of web programming doesn't lend itself very well to this model. For example, if user A and user B send update requests via the website at the same time, it would be quite difficult to combine both requests into a single transaction. You are better off using a more powerful DB that was designed with concurrency in mind.
Kurt
+2  A: 

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

Ice
In a low volume website, you'd be quite right. However, there comes a point when the webserver needs more than 1 connection to the database.
GateKiller
Thx GateKiller, but please specify "low volume website".
Ice
+2  A: 

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.

MPelletier
A: 

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.

Satya Prakash