By Cal Mitchell
This article will compare the speed and cost-efficiency of MySQL, MariaDB, and AWS Aurora for use as an operations-critical transactional database.
To summarize, MariaDB was the fastest in my tests, followed by MySQL, the Aurora. However, Aurora offers a lot of extra features that might sway your opinion.
I hope these benchmarks can help you decide what RDBMS to use for a MySQL compatible workload as might be required by a busy WordPress site or web application.
Let’s give a brief overview of the databases in this test. Feel free to skip ahead if you just want to see the results.
MySQL has been around for a long time and powers a non-trivial amount of the internet. Because of WordPress’ dependency on a MySQL compatible database, MySQL will be with us for a long time to come.
However, not everyone is happy with MySQL’s recent trajectory. It was initially an open-source project. Then it was purchased by Sun Microsystems and then acquired by Oracle. Some people accuse Oracle of letting MySQL’s development slow to a crawl to encourage the adoption of enterprise options, but we won’t get into that here.
Regardless of how you feel about MySQL’s recent development, many people want to take things in a different direction. Enter MariaDB.
The founder of MySQL, Michael Monty Widenius, was one of those unhappy people. He decided to fork MySQL, and in 2012, the MariaDB foundation was born. According to an excerpt from a book on MariaDB, this foundation was created to “avoid any company acquisition like what had happened in the past for MySQL.”
MariaDB allows clients requiring a MySQL database access to additional (and in our opinion, improved) storage engines and a more open development community. In general, we are fans of the MariaDB initiative.
MariaDB isn’t the only MySQL compatible database in town, especially for AWS users. Amazon has developed a cloud-native database called Aurora, which takes advantage of lightning-fast and ultra-reliable networking in their data centres.
The standardisation of internal APIs and incredibly robust physical infrastructure available to public cloud development teams has allowed them to build products that push the boundaries of what was considered possible for distributed systems. Amazon’s Aurora is one example of these new data system products.
AWS RDS allows anyone to deploy a well-tuned, managed database in the cloud. The engineers at AWS have done an excellent job optimising each instance type for the amount of RAM and CPU cores provided. This pre-configuration by a team of experts allows us to compare the relative performance of the database engines instead of our ability to tune a given database system.
The first part of this comparison will involve spinning up each database on a db.t4g.medium RDS instance, and running a test with HammerDB, a popular database benchmarking tool. Then, we will explore smaller and larger instance sizes to see which database scales best.
All databases in this part of the comparison were run on a db.t4g.medium instance with 2 ARM CPU cores and 4 GB of RAM, with default at rest encryption and no performance insights or enhanced monitoring.
Here you can read about the workload that HammerDB simulates on this page of their official documentation.
Here are some screenshots of HammerDB as we configured it to build the test schema, run the benchmarks, and log the results.
This screenshot shows how we configured HammerDB to build a schema and pre-insert some data.
We switched to the “Timed Driver Script” mode to run a timed test and checked the “Use All Warehouses” box. Everything else was left on default.
Here we create 11 virtual users to query the database.
We started the transaction counter, and off we went!
Right off the bat, MySQL went right to ~50,000 transactions per minute and stayed there for the test duration. The CPU utilisation report on the AWS dashboard shows that it used about 90% of available CPU cycles – this indicates that the workload is CPU bound, not memory bound.
The official HammerDB Github profile recommends using the MySQL driver for benchmarking MariaDB, even though a specific MariaDB driver exists. So, we will leave everything the same for MariaDB except for a new hostname and credentials.
MariaDB immediately shows that at this instance size, it is faster.
One thing that surprised me was that it just continued to climb. In the middle of the test, it was routinely getting close to 80,000 transactions per minute!
As you can see, it also had ~90% CPU utilisation, which indicates that it was CPU bound as well.
We were very excited to see what Aurora could do in this test. Amazon makes a lot of claims about how fast it is. For example, the Aurora FAQ page references claims about being five times faster than MySQL.
This benchmark, however, shows that it was roughly as fast as MySQL in this particular workload.
Aurora seemed to show slightly lower CPU utilisation than the others.
We also checked the amount of I/Os that Aurora used during the test, as many people on the internet complain about I/O’s being unexpectedly expensive. Our test peaked at 300 IOPS per second. Here are a few links; make your own decision about the validity of those complaints.
Just for kicks, we went to the AWS cost calculator and did some back-of-napkin math to figure out what this Aurora instance would cost us to run.
Backtracks seem to be akin to rollbacks. Since we never make mistakes, we tried to enter zero, but apparently, AWS doesn’t believe us
So we left that number at the default of 100.
In the end, the forecasted bill for this unit running in AWS EU-WEST-2 was around $100 per month.
MySQL: 16,855 orders processed per minute, 50,945 transactions per minute
MariaDB: 23,347 orders processed per minute, 76,866 transactions per minute
Aurora: 15,781 orders processed per minute, 47,517 transactions per minute
HammerDB reported two central figures for us: The number of orders that our OLTP system was able to process per minute, as well as how many database transactions it took to get there.
If you plan to run an RDS instance with 4 GB of RAM, and your workload is similar to the one run by this benchmark, then MariaDB is your best choice. It processed 38% more orders than MySQL and 48% more orders than Aurora.
I found this result surprising! I thought Aurora would come out on top performance-wise, but it was roughly on par with MySQL for this specific workload. The lack of a performance benefit is especially noticeable when you consider cost. MySQL performed 7% better at 65%of the price. MariaDB performed 48% better at 65% of the price.
Let’s try to put a figure on the cost-effectiveness of each database. To do this, we will divide transactions processed per minute by the per-minute cost of each database, and the result will be the cost to process each transaction. Not a perfect measure of cost, to be sure, but it is something.
First, let’s get the cost of each database. Aurora will probably cost about $100 per month, while the other two cost $65.86 per month. There are about 43,200 billable minutes per month (60 minutes * 24 hours * 30 days).
Now let’s divide the transactions processed per minute by the cost per minute.
According to this metric, for this workload, MariaDB is 38% more cost-effective at processing orders than MySQL and 225% more cost-effective at processing orders than Aurora. Meanwhile, MySQL is 62% more cost-effective at processing orders than Aurora.
MariaDB was the best performer for our particular workload, and it seemed CPU bound (as opposed to RAM bound). AWS’ RDS t4g instances come with 2 CPU cores up until t4g.xlarge. It might be possible to get the same performance with a smaller instance size. Let’s investigate.
Here we tested MariaDB on a db.t4g.micro instance. This instance size also has 2 CPU cores but only has 1 GB of RAM. As you can see, due to the lower amount of RAM (and possibly RDS tuning differences that are not apparent to us), the transactions per minute are lower, and the CPU doesn’t seem to be fully utilised (it is only hitting 65% – 70% utilisation).
Let’s try one size up – an db.t4g.small instance, which also has 2 CPU cores but has 2 GB of RAM.
To my surprise, the transactions per minute are slightly higher than the MariaDB t4g.medium instance! This probably means that the way that AWS’ engineers have tuned the t4g.small’s database engine fits the workload of this test particularly well. A possible future follow up would be finding the exact changes made to MariaDB’s configuration to see how they make a difference.
It seems like 2 GB of RAM can fully utilise the 2 CPU cores for this workload, as it is back up to 90% or higher.
For comparison’s sake, let’s also run MySQL on a db.t4g.small instance to see if it can also match its bigger sibling’s performance.
Yup, just like MariaDB, MySQL runs great on a db.t4g.small instance, with 90% CPU utilisation or higher.
This workload seems to be CPU bound, and AWS offers EC2 instances that are CPU optimised. We are mature adults here, and we totally don’t want just to see how fast we can make things go. So, for the sake of cost-effectiveness, let’s see what you should do with about 100$ a month.
Amazon offers a c6g.xlarge ec2 instance that has 4 CPU cores and 8GB of RAM. This machine costs $.136 per hour. If you purchase that, along with 100GB of gp2 storage per month, you’re looking at about $110 per month. Close enough.
We fired up the ec2 instance and used the following commands to install MariaDB, open it up to the world, and create a user which HammerDB will use to connect.
# Open the MariaDB config file
sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
# Configure the following setting in the file
bind-address = 0.0.0.0
# Connect to MariaDB with whatever client you like
CREATE USER hammer@'%' IDENTIFIED BY ‘whatever_password’;
Notice that we didn’t do any DB configuration other than opening up the database’s ports.
We can see by using the top command that we are fully using 2 of the CPU cores.
Here we run the test without any tuning whatsoever. As you can see, the transactions per minute are roughly on par with a db.t4g.small instance! That means we have 2X as many CPU cores and 8X as much RAM, but we see similar benchmark results. This shows the importance of database tuning.
Running the top command during the test shows that we are not fully utilising our CPU resources.
After some basic tuning
I am not a professional DBA, but have worked with databases in the past. So, let’s do some basic MariaDB tuning:
# Set Linux's swap value to 0 in the shell
sysctl -w vm.swappiness=0
# Edit MariaDB config file again
sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
# Change key buffer size and innodb buffer pool size
key_buffer_size = 10M
# Restart DB for changes to take effect
sudo systemctl restart mariadb
And let’s rerun it!
Woah, that made a huge difference! We see at least double the performance level of the untuned database. And that was with just a few un-educated config changes.
I’m not going to do a specific cost analysis of this instance, but you can if you want!
For this specific MySQL compatible workload, MariaDB is faster than MySQL is faster than Aurora, and the t4g.small instance is the best “bang for your buck”.
MariaDB and MySQL on a t4g.small instance: $39.58 per month, X 43200 = $0.0009162 per minute.
And so, at their most efficient, MariaDB seems to be able to process about 61% more orders per dollar than MySQL, and 417% more orders per dollar than Aurora.
These results surprise me so much that it makes me question my methods – why would Aurora perform worse than MariaDB? Maybe the system prioritises something over speed, such as reliability, fault tolerance, or scalability. And would these performance differences hold up in other workloads? These are great questions for future articles.