SQLite faster than MySQL?

Question

I want to set up a teamspeak 3 server. I can choose between SQLite and MySQL as database. Well I usually tend to "do not use SQLite in production". But on the other hand, it's a teamspeak server. Well okay, just let me google this... I found this:

2. Speed
   SQLite3 is much faster than MySQL database. It's because file database is always faster than unix socket. When I requested edit of channel it took about 0.5-1 sec on MySQL database (127.0.0.1) and almost instantly (0.1 sec) on SQLite 3. [...]

http://forum.teamspeak.com/showthread.php/77126-SQLite-vs-MySQL-Answer-is-here

I don't want to start a SQLite vs MySQL debate. I just want to ask: Is his argument even valid? I can't imagine it's true what he says. But unfortunately I'm not expert enough to answer this question myself.

Maybe TeamSpeak dev's have some major differences in their db architecture between SQLite and MySQL which explains a huge difference in speed (I can't imagine this).

Solution 1

At First Access Time will Appear Faster in SQLite

The access time for SQLite will appear faster at first instance, but this is with a small number of users online. SQLite uses a very simplistic access algorithm, its fast but does not handle concurrency.

As the database starts to grow, and the amount of simultaneous access it will start to suffer. The way servers handle multiple requests is completely different and way more complex and optimized for high concurrency. For example, SQLite will lock the whole table if an update is going on, and queue the orders.

RDBMS's Makes a lot of extra work that make them more Scalable

MySQL for example, even with a single user will create an access QUEUE, lock tables partially instead of allowing only single user-per time executions, and other pretty complex tasks in order to make sure the database is still accessible for any other simultaneous access.

This will make a single user connection slower, but pays off in the future, when 100's of users are online, and in this case, the simple "LOCK THE WHOLE TABLE AND EXECUTE A SINGLE QUERY EACH TIME" procedure of SQLite will hog the server.

SQLite is made for simplicity and Self Contained Database Applications.

If you are expecting to have 10 simultaneous access writing at the database at a time SQLite may perform well, but you won't want an 100 user application that constant writes and reads data to the database using SQLite. It wasn't designed for such scenario, and it will trash resources.

Considering your TeamSpeak scenario you are likely to be ok with SQLite, even for some business it is OK, some websites need databases that will be read only unless when adding new content.

For this kind of uses SQLite is a cheap, easy to implement, self contained, perfect solution that will get the job done.

Solution 2

The relevant difference is that SQLite uses a much simpler locking algorithm (a simple global database lock).

Using fine-grained locking (as MySQL and most other DB servers do) is much more complex, and slower if there is only a single database user, but required if you want to allow more concurrency.

Solution 3

I have not personally tested SQLite vs MySQL, but it is easy to find examples on the web that say the opposite (for instance). You do ask a question that is not quite so religious: is that argument valid?

First, the essence of the argument is somewhat specious. A Unix socket would be used to communicate to a database server. A "file database" seems to refer to the fact that communication is through a compiled-in interface. In the terminology of SQLite, it is server-less. Most databases store data in files, so the terminology "file database" is a little misleading.

Performance of a database involves multiple factors, such as:

• Communication of query to the database.
• Speed of compilation (ability to store pre-compiled queries is a plus here).
• Speed of processing.
• Ability to handle complex processing.
• Compiler optimizations and execution engine algorithms.
• Communication of results back to the application.

Having the interface be compiled-in affects the first and last of these. There is nothing that prevents a server-less database from excelling at the rest. However, database servers are typically millions of lines of code -- much larger than SQLite. A lot of this supports extra functionality. Some of it supports improved optimizations and better algorithms.

As with most performance questions, the answer is to test the systems yourself on your data in your environment. Being server-less is not an automatic performance gain. Having a server doesn't make a database "better". They are different applications designed for different optimization points.

Solution 4

In short:

• For Local application databses, single user applications, and little simple projects keeping small data SQLite is winner.
• For Network database applications, multiuser and concurrency, load balancing and growing data managements, security and roll based authentications, big projects and widely used services you should choose MySql.

---

In your question I do not know much about teamspeak servers and what kind of data it actually needs to keep in its database but if it just needs a local DBMS and not needs to proccess lots of concurrency and managements SQLite will be my choice.

View more solutions

Comments

[gunslingor]

I can say with reasonable certainty that SQLite is slower at DROPPING tables. I believe the reason is that MySQL stores each table in discrete files, whereas SQLite stores the entire DB in one file... requiring search and destroy rather than just deleting a file. It took 1.5 minutes to drop my tables and 1 sec to delete the SQLite DB.

[Jamie Lindsey]

Please remember the whole "do not use sqlite in production" only refers to applications that need many concurrent read/writes, sqlite is definitely a production quality RDBMS. And anyone who says otherwise is simply a liar. Using sqlite can be much better than other RDBMS 's depending on the number of expected concurrent requests. I have used sqlite for many years for small web applications that only require simple logins etc. And I have and do still use mysql on larger (many users posting many updates) applications and it is simply obvious how much faster sqlite is than mysql.

[Mike Sherrill 'Cat Recall']

SQLite's own site says, " SQLite does not compete with client/server databases. SQLite competes with fopen()."

[ranieri]

Can you provide some sources on this?

[José Algarra]

Check for "When Not To Use SQLite" in the link: digitalocean.com/community/tutorials/…

[ASBai]

SQLite supports WAL, and if you enable it, there will be no db-level exlusive locks. If you think that WAL (binlog) still has a limit of one writer at a time, then other products like MySQL have this limitation too.