Ms Sql Vs Mysql Performance Benchmarks

Benchmark databases in Docker: MySQL, PostgreSQL, SQL Server

Plus MariaDB, Percona and TimescaleDB

Why should you practice database benchmark?

I was a long-term SQL Server user. Just it was non my conclusion to use SQL Server. I was young and had to heed older colleagues. If they said, SQL Server is the best, and so it was.

Then I was a long-term MySQL user. But it was not my decision to use MySQL. I was older, but my other colleagues were much more experienced. If they said, MySQL is the best, then it was.

I never searched for any other opinions, because at that time, I was learning. How can I debate with my much more than experienced colleagues?

Then I found MariaDB. And so I institute Percona. And I asked my much more experienced colleagues something similar: "Hey, what are they? Why are they?". The respond was almost ever something like: "MySQL is the best, I know MySQL. Do you suppose to use something else? Do you want to pause things?"

And of course, I didn't desire to break things.

But curiosity is my 2d proper noun, and so I decided to find out, which database should suits the all-time for our awarding. And I hit the perfect time, because Docker was arriving to the scene, then it looked like, it would be piece of cake to starting time my piece of work.

So, to reply the question "Why should yous do database benchmark?" is to say: "Because it turns YOU into that much more experienced colleague".

How I did my database benchmark?

To criterion a database can be a tricky thing. Just expect effectually the internet. In that location are dissimilar benchmarks, measuring different things, sometimes parameters, that do non tell you anything meaningful.

From my perspective, I run into database as something, that exactly matches its name. A base for a data. Nothing more than. And then no awarding logic in database. Database should hold the data and make two master operations as quick equally possible: read and write.

I run into read equally something not changing the database and write as something changing the database. For me, delete and update are both subset of write.

Also, when I read from database, I tend to brand my selects as unproblematic as possible. I practise non use joins on joins on joins on joins. I prefer reading multiple different tables as quick as possible, and then process the data outside the database. Merely I utilize max, avg, min and sum a lot. I employ elementary things a lot and complicated things equally little as possible.

From my perspective, my ideal database should be calmly bustling all the time, serving reads and writes as quickly, every bit possible. Nothing more than.

When I decided to make my benchmark I was hunting three things:

Which database has the fastest writes
Which database has the fastest reads
Which database has the smallest memory and CPU usage

Preparation

As I did my benchmark two years ago in 2019 (resulted into switching to PostgreSQL), information technology will be wise to repeat it now, in 2021.

At first, we demand to run all databases, we want to criterion. We can run all the major databases in Docker with commands (and a picayune flake more information) from this commodity, merely you observe all those commands below.

I added some popular forks from all three major engine families.

Postgres engine family: PostgreSQL, TimescaleDB

MySQL engine family unit: MySQL, MariaDB, Percona

SQL Server engine family: SQL Server

Here are the Docker commands to run every database we demand to test.

PostgreSQL

                docker run --proper name postgres -eastward POSTGRES_PASSWORD=password -p 5433:5432 -v postgres_data:/var/lib/postgresql/data -d postgres:alpine

TimescaleDB

                docker run --proper noun timescale -due east POSTGRES_PASSWORD=password -p 5434:5432 -v timescale_data:/var/lib/postgresql/data -d timescale/timescaledb:latest-pg12

MySQL

                docker run --name mysql -east MYSQL_ROOT_PASSWORD=password -p 3306:3306 -v mysql_data:/var/lib/mysql -d mysql:latest

MariaDB

                docker run --proper noun mariadb -east MYSQL_ROOT_PASSWORD=password -p 3307:3306 -v mariadb_data:/var/lib/mysql -d mariadb:latest

Percona

                docker run --proper name percona -eastward MYSQL_ROOT_PASSWORD=password -p 3308:3306 -v percona_data:/var/lib/mysql -d percona:ps-8

SQL Server

                docker run --proper noun sqlserver -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=passw0rd.' -p 1433:1433 -v sqlserver_data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-latest

Unfortunately, thank to Microsoft's EULA for SQL Server 2019, it is not possible to nowadays criterion for SQL Server, but I can tell y'all, it is a shame. You have to do your benchmarks yourself.

First comparison

By at present, you should have all six databases running. All 6 in their default states. No fine tuning.

We can do our first comparing. We tin compare database image size, initial memory usage in Docker and initial CPU usage.

Past these results, it looks similar PostgreSQL is the winner, and SQL Server is the loser. Only nosotros didn't brand any read and/or write benchmarks yet.

Write benchmarks

To do write benchmarks, I developed a uncomplicated Go program (github repository at the end of commodity). This programme creates one table called benchmark_data with 6 columns. You need to create database benchmark manually using create database benchmark.

This program inserts 10 000 rows into this table, one-past-one. No batch inserts, just unproblematic inserts.

Benchmark volition exist done on Macbook Pro 2019, running Docker desktop, all unnecessary applications are shut down.

This first part of benchmark will measure time, it will have those inserts to complete. From my perspective, time versus some operation is the but reasonable measurement one tin exercise. In the stop you always want to know, how long does information technology accept, or how much was done in specified time.

In this benchmark, I did 5 sequent insert batches, each of x 000 rows. Conspicuously, PostgreSQL is the winner and Percona is the loser.

Let's add comparison in terms of total duration and average duration.

PostgreSQL is about twice as fast as Percona, in case of inserts. What strikes me here, is the deviation betwixt MariaDB and MySQL, because they are both from i engine family. Looks similar folks behind MariaDB did some magic.

A simple conclusion: Postgres engine family is about twice as fast equally MySQL engine family, except MariaDB.

Read benchmarks

This read benchmark was done this way: 2 000 loops with two reads in every loop: boilerplate and sum of data column. Again, I did everything 5 times.

Beneath are the results. PostgreSQL is the winner, Percona is the loser. MariaDB second worse.

Comparing total duration and boilerplate duration, just to accept a better picture. Postgres engine family is about twice as fast every bit MySQL engine family.

Conclusion

Clearly the winner here is PostgreSQL. Smallest size, smallest CPU and retention usage, fastest write speed and fastest read speed.

2d identify goes to TimescaleDB. TimescaleDB is a member of the aforementioned engine family. Thanks to data from this reddit post, TimescaleDB should perform better on bigger time-series based information, which was not purpose of this benchmark.

Third place goes to MariaDB, because of that fast reads.

Fourth is MySQL and last is Percona, to my surprise (explained below).

And something about SQL Server, whose results are not presented here (see caption above): I have to say, they did some good piece of work in Microsoft, considering SQL Server would have been placed third.

Here is a link for a tool I used for benchmarking.

Differences between 2019 and 2021

When I did those tests two years ago, PostgreSQL was the winner, Percona second, MariaDB third, MySQL fourth and SQL Server terminal. I tested their latest versions in 2019 and today I tested, of grade, different latest versions. I did not test TimescaleDB in 2019.

Looks like they did some very bad work at Percona, running in Docker. And they are withal doing the best work at PostgreSQL, running in Docker.

Those ii years ago, we did a switch from MySQL to PostgreSQL and looks similar nosotros did the right thing — for our application of use.

Those ii years ago, we were thinking (and testing) about MariaDB and Percona. Skillful decision, we did not switch to Percona.

Bonus information

Some of you may argue, that ane can fine-tune database of his/her selection to make it perform better. And you will be absolutely correct. Just carry in listen, y'all can fine-melody every database. So, if you lot fine-tune MySQL to catch PostgreSQL, you can surely fine-tune PostgreSQL.

Speaking about this, PostgreSQL has a very nice tool for fine-tuning the database. I don't know of any like tool for MySQL family unit or for SQL Server. If you know, delight let me know in comments.

PostgreSQL only benchmark

Reading comments, I made an boosted criterion, this fourth dimension comparing running PostgreSQL in Docker, versus running PostgreSQL natively on all three major systems. On MacOS, using unlike PGTune settings.

hopkinsoccred.blogspot.com

Source: https://itnext.io/benchmark-databases-in-docker-mysql-postgresql-sql-server-7b129368eed7