Comparing Query Performance Across DBMSs

Question

I have a list of queries that I want to compare the execution time of across 3 different DBMSs: SQLite, MariaDB and postgreSQL.

I have been looking into command line tools for each system to get the query execution time.

• For SQLite I used the .timer ON command to show the real/user/sys time after each query
• For MariaDB I set the profiling and called "show profiling" to get the duration of each query
• For PostgreSQL I executed the query with "analyze" to show the full query plan including execution time.

My problem is that I wasn't able to find documentation for these tools that specified what the time actually is: CPU vs actual time etc, and some of the values are very different. For example, a join query that I tried with MariaDB took 33 mins but with postgreSQL it only took a few seconds.

Does anyone have insight into how these metrics are actually measuring query time, and if they can accurately be compared with each other? Alternatively, is there a better tool I should be using for this task? Any help is appreciated.

Answer 1

I think that you shouldn't rely on any of that. The only real measure is how long the query actually took, when measured from the client side. That also avoids overhead during query execution that you get if you for example use EXPLAIN (ANALYZE) in PostgreSQL.

So I would do it like this:

• measure the current time on the client

• send a query to the server

• wait until execution is complete and the client has got the result

• measure the time again and calculate the difference

Answer 2

For MariaDB/MySQL, PROFILE is about as useless as tools can get. Almost always, almost all of the time is lumped into a single entry, such as "Sending data", and even that is a misnomer.

The Query Cache should be OFF, else you get bogus results.

A "cold" system, followed by a single run of a query leads to a virtually useless timing. The first run is likely to be 10 times as slow as the second run -- just because of caching of disk blocks.

mysqlslap and some percona tools are good at running the same query again and again, optionally from separate connections. (In my opinion, this is not very useful either.)

As for comparing products, the following can complicate things:

• In general every product will run simple queries about as fast. This because all the simple stuff has been optimized. by everyone.
• What about multiple CPUs and parallelism -- either between connections or within a single connection. MySQL has essentially no parallism within a single connection; it does have good parallelism between connections. If your benchmark tests only one thread, MySQL will be at a disadvantage. But if your application is single-threaded, then this is a point to note.
• HDD vs SSD -- SSD is faster, hence can hide some of the inefficiency of a product.
• Settings -- poor settings can lead to more I/O than a well-tuned product. I/O, especially with huge tables and HDD, is the main component of query time.
• Network latency -- If you are using a cloud, that introduces several milliseconds per query; for simple queries, this is the bulk of the 'total' time (as discussed by Laurenz). That can be mitigated by having multiple threads. But which is more important? Latency (how fast one query finishes) or Throughput (how many times a query can be run per second by multiple threads).
• Performance cliff. Old versions of MySQL would tank if you had "too many" connections contending for resources. Current versions can get to about 100 before throughput stalls and latency goes through the roof -- all due to having to share the cpus/IO/network. Very few production systems have 100 active threads at the same time, even if they are running thousands of queries per second!
• The best benchmark is getting a dump of the queries from a live system, then replaying them as fast as possible against a snapshot of the database taken at the beginning of the dump.