0

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.

3
  • Agreed with Laurenz's answer, there's too many variables in each test case. The best test is multiple real world tests from a client to the database. If you're just generally trying to find the fastest database system, all modern RDBMS are close enough to each other for most use cases. SQLite is probably a minor exception since it's design purpose is for lighter weight databases usually, so it lacks some features of a full fledged RDBMS and may be measurably slower consistently. Also by general word of mouth, PostgreSQL might be slightly faster than MySQL variants like MariaDB.
    – J.D.
    Mar 15 '21 at 16:55
  • @J.D. - what makes you say that PostgreSQL would be slightly faster, especially since we have no queries to work with? My understanding is that MySQL can be better in a high-read/low-write scenario - websites which is where it "made its bones"... and PostgreSQL was better on complex analytical type queries. Of course, this says nothing about which server is superior - I would go for PostgreSQL in a heartbeat in any scenario - stability, SQL standard compliance and functionality - it's streets ahead of MySQL... Mar 30 '21 at 16:18
  • @Vérace Honestly I'm just going by hearsay on that one, as I've read other articles and StackExchange posts where MySQL is a little lacking in how it was architected compared to the big 3 (SQL Server, Oracle, and PostgreSQL) such that there are somewhat common use cases that perform noticeably worse in MySQL. But I could definitely be wrong, which is why I qualified what I said with saying "general word of mouth".
    – J.D.
    Mar 30 '21 at 18:01
2

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

5
  • This is a good techinque for a single query on MySQL/MariaDB, too.
    – Rick James
    Mar 15 '21 at 18:19
  • Could you suggest commands to use for this? I'm just querying local databases from the command line for testing purposes Mar 15 '21 at 20:00
  • I would write a shell script or a little program. Mar 16 '21 at 0:12
  • Thank you! I was wondering if you had any idea why a join query I created would be significantly faster in postgreSQL than in MySQL/MariaDB or SQLite (on the magnitude of seconds vs minutes)? This is the only thing I'm still troubled on in my comparison. Mar 16 '21 at 23:14
  • To figure that out, you have to investigate the execution plans. That is a different problem. Mar 17 '21 at 6:44
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.