SQLite faster than MySQL?

Question

I want to configure a teampeak 3 server. I can choose between SQLite and MySQL as a database. I usually try not to use SQLite in production. But, on the other hand, it is a teampeak server. Alright, just let me google this ... I found this:

2. Speed
   SQLite3 is much faster than the MySQL database. This is because the file database is always faster than the unix socket. When I asked to edit the channel, it took about 0.5-1 seconds in the MySQL database (127.0.0.1) and almost instantly (0.1 s) on SQLite 3. [...]

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

I do not want to start a discussion of SQLite and MySQL. I just want to ask: Is his argument even valid? I can’t imagine that it’s true what he is saying. But, unfortunately, I am not an expert enough to answer this question myself.

Perhaps TeamSpeak dev has some significant differences in their db architecture between SQLite and MySQL, which explains the huge difference in speed (I can't imagine this).

Answer 1

At first access time will appear faster in SQLite

Access time for SQLite will display faster in the first instance, but this is due to the small number of users on the Internet. SQLite uses a very simplified access algorithm, quickly, but does not handle concurrency.

As the database begins to grow, and the amount of concurrent access begins to suffer. The way servers handle multiple requests is completely different and more complex and optimized for high concurrency. For example, SQLite locks the entire table if an update occurs and orders orders.

RDBMS does a lot of extra work that makes them more scalable.

MySQL, for example, will create QUEUE access even with one user, partially block tables, instead of allowing only one time execution by the user and other rather complicated tasks, to make sure that the database is still available for any other simultaneous access.

This will lead to a slow connection of one user, but will pay off in the future when 100 users are online, in which case the simple "LOCK ALL ONE TABLE AND FULL SINGLE REQUEST EACH TIME" SQLite procedure will start the server.

SQLite is designed for simplicity and stand-alone database applications.

If you expect to have 10 simultaneous writes to the database at the same time, SQLite may work well, but you do not want 100 user applications to write and read data to the database continuously using SQLite. It was not designed for such a scenario, and it will destroy resources.

Given your TeamSpeak script, you'll probably be fine with SQLite, even for some companies this is fine, some websites need databases that will only be read if new content is added.

For this use, SQLite is a cheap, easy-to-implement, self-contained, ideal solution that does the job.

Answer 2

I personally have not tested SQLite and MySQL, but it’s easy to find examples on the Internet that say the opposite ( for example ), you ask a question that is not so religious: is this argument valid?

Firstly, the essence of the argument is somewhat obvious. The Unix socket will be used to communicate with the database server. The "file database" seems to refer to the fact that communication is via a compiled interface. In SQLite terminology, this is server-less . Most databases store data in files, so the term “file database” is a little misleading.

Database performance includes several factors, such as:

• Communication request with the database.
• Compilation speed (the ability to store precompiled queries here is a plus).
• Processing speed.
• Ability to handle complex processing.
• Compiler optimization and execution algorithm algorithms.
• Transferring the results back to the application.

The presence of a compiled-in interface affects the first and last of them. There is nothing that would prevent the server database from surpassing the rest. However, database servers typically represent millions of lines of code — much more than SQLite. Many of them support additional functionality. Some of them support improved optimizations and better algorithms.

As with most performance issues, the answer is to test the systems themselves for your data in your environment. The lack of a server is not an automatic performance improvement. Having a server does not make the database “better.” These are different applications designed for different optimization points.

Answer 3

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

Using fine-grained locking (as MySQL and most other database servers do) is much more complicated and slower if there is only one database user, but required if you want to allow more concurreny.