Mysql is a versatile database server. As many other open source projects, it comes in various flavors. Each flavor has its own peculiarity: let’s do an overlook and take them for a run to validate (or invalidate) their claims on perfomance.
After Oracle buying Sun Microsystems, Mysql became part of the Oracle’s product porfolio, with its -paid- enterprise edition, while keeping a free Community edition (also called GA for Generally Available). After that, the original authors decided to fork the project into MariaDB (citing reasons varying from licensing terms to arquitectual goals), and kept concentrating efforts on it, leaving the original project to the responsibility of the new owner. Previous to the separation of MariaDB, existed the PerconaDB fork of Mysql. Percona is a long standing third party supplier of paid support for Mysql. They take their job seriously, so they decided to maintain their own version. They promise better read and write performance with their own, InnoDB-based XtraDB storage engine. Lets put those claims to test.
I will be addressing performance differences between versions, using the following testing methodology:
- sysbench’s OLTP tests
- virtual machines with 2 cores and 3 GB of RAM
- The testing dataset has 1 Million rows.
I added two versions of MariaDB (5.5 and 10.1, which in spite the difference in version number, are next to each other). The test is run for 60 seconds.
The environment can be replicated using this gitlab project (and Vagrant). You can find instructions on the README.md file
The benchmark will be run three times and only the last test results will be taken. This is done to avoid cold start differences on performance. All tests are run with InnoDB table engine, otherwise noted.
Metrics
To simplify I will be taking only the following metrics from the benchmark result:
- Operations per second
- transactions
- r/w requests
- other
- Total queries in 60 seconds:
- read (any SELECT query)
- write (any UPDATE or INSERT query)
- other (queries that affect the engine or the session, such as setting a variable or changing an environment parameter)
- Response time during test:
- min (minimum)
- avg (average)
- max (maximum)
- 95% (95% of the queries response time)
There are more metrics available, I invite you to check out the project and run the tests locally on your machine (be sure to have enough ram free)
Configuration
Global (for all instances)
max_connections=100 #we will not be using more than that
query_cache_size=0 #avoid interference with real read performance
skip_name_resolve=1 # avoid resolving hostnames during connection
tmp_table_size=1024M #temp table size has implications on performance for queries with big datasets that require a temporal table to be resolved
max_heap_table_size=1024M #idem previous value
join_buffer_size=128M #this optimizes queries that join tables in columns that don’t have an index
sort_buffer_size=2M #sort buffer for sorting non-indexed columns
read_rnd_buffer_size=2M #recommended value for this machine size
InnoDB/XtraDB
innodb_buffer_pool_size=2G #maximum buffer pool size for the test machines
innodb_log_file_size=512MB #recommended value for this machine size
innodb_file_per_table=1 #create a file on disk for each table
innodb_flush_log_at_trx_commit=2 #the usual mode we deploy, less ACID, more performance
innodb_log_buffer_size=2MB #recommended for this machine size
innodb_thread_concurrency=0 #let InnoDB infer the best number of threads
innodb_flush_method=O_DIRECT #avoid interference from OS disk buffers
TokuDB
tokudb_cache_size=2GB #similar to innodb_buffer_pool_size
tokudb_commit_sync=0 #similar to innodb’s log_at_trx_commit=2
tokudb_fsync_log_period=1000 #setting the fsync period to 1 sec (similar to innodb’s default)
The competitors
Mysql community edition
The official mysql version
- Pros: Officialy mantained.
- Cons: Mysql historically had been putting put emphasis on performance over ACID compliance. Since the Oracle acquisition this trend was reversed, putting compliance over performance (which can be good or bad, depending on your use case)
MariaDB
Fork of the historical developers, made after the Oracle’s takeover mysql.
- Pros:
- Original developers are still working on it, probably with a more deep knowledge of the internals that the people at Oracle
- Commercial support available from mariadb.com
- Cons:
- Since the Oracle takeover of mysql, it is a fork of Percona Server, so we shouldn’t see much difference on test results.
Percona Server
- Pros:
- Support for SSD as a second level read and write cache (called “FlashCache”) built in
- Relatively cheap commercial support as subscription available.
- Some features available commercially on Mysql are available for free (PAM authentication, Audit Log, Threadpool).
- Cons:
- Some extensions and improvements needs you to tie up your project to the Percona fork.
TokuDB
Originally a fork by Tokutek, a company focused on a special index technology called “Fractal indexes” is now owned by Percona, thus, the technology was included on Percona Server as an alternative storage engine. The original promise was consistent write performance across the entire application lifecycle.
- Pros: If your use case matches the need of constant writes rate, may be an interesting option
- Cons: Highly experimental.
Test results
Operations per second
| Ops/s | mariadb 10.1 | mariadb 5.5 | mysql 5.7 | percona 5.6-XtraDB | percona-TokuDB |
| transactions | 586.74 | 630.04 | 396.24 | 238.45 | 209.04 |
| r/w requests | 10561.25 | 11340.75 | 7133.42 | 4292.17 | 3762.75 |
| other | 1173.47 | 1260.08 | 792.55 | 476.91 | 418.08 |
| Queries | mariadb 10.1 | mariadb 5.5 | mysql 5.7 | percona 5.6-XtraDB | percona-TokuDB |
| read | 492926 | 529284 | 332976 | 201068 | 175658 |
| write | 140836 | 151224 | 95128 | 57448 | 50188 |
| other | 70418 | 75612 | 47564 | 28724 | 25094 |
| total | 704180 | 756120 | 475668 | 287240 | 250940 |
| Response time | mariadb 10.1 | mariadb 5.5 | mysql 5.7 | percona 5.6-XtraDB | percona-TokuDB |
| min | 2.07 | 1.81 | 4.94 | 11.28 | 13.59 |
| avg | 20.44 | 19.03 | 30.27 | 50.3 | 57.38 |
| max | 341.08 | 245.85 | 374.18 | 719.64 | 929.43 |
| 95% | 39.76 | 33.06 | 63.7 | 78.45 | 85 |
Conclusions
Our most important metric should be queries and response time, since that’s what affects the client software. And I have to say I am totally stunned by MariaDB! It is incredible the level of performance that it achieves compared to Oracle’s Mysql, and on the same hardware! I tested it over and over, looking for an error on my side, but I found nothing. I just could not believe it. Almost twice the performance over the Percona, which is clearly lagging behind, as I said earlier, they where the commonly accepted performance kings.
TokuDB deserves a new round of testing, oriented to write performance. Since their promise is “constant and consistent write performance over time, with no degradation” the OLTP testing is not well suited for this table engine, but I decided to include it anyway, just in case.
Before taking a decision on which flavor is better for your project, remember to do your own testing. There is the possibility that for your workload type, other flavor of mysql performs better than MariaDB.
