AWS RDS benchmark — MariaDB vs MySQL
Benchmarking MariaDB vs MySQL on Amazon RDS default configuration.
Before migrate my database (31GB dump.sql file) I decided to do a little benchmark to define the RDS solution I’ll choose (RDS MariaDB or RDS MySQL). If they costs the same, make sense to get the solution with better performance, right?
So I create 2 RDS's with the same size (db-m3-large) and the last version of available engines, MariaDB 10.1.14 and MySQL 5.7.16, both on Availability Zone sa-east-1 (São Paulo).
Storage Type: General Purpose (SSD)
IOPS: disabled
Storage: 100 GB
Multi AZ: No
Automated Backups: Disabled
Then I get an EC2 (t2.large) at same region on VPC to import the database file to RDS's.
Before start the import I have to create a parameter group to modify the parameter log_bin_trust_function_creators = 1. This will allow import of database functions, procedures and triggers as explained above:
How do I enable functions, procedures, and triggers for my Amazon RDS MySQL instance?
Even changing the log_bin_trust_function_creators to 1 I still received the error:
Access denied; you need (at least one of) the SUPER privilege(s) for this operationSo I used the command above to generate new file changing the "DEFINER" on dump file.
perl -pe 's/\sDEFINER=`[^`]+`@`[^`]+`//' < dump.sql > dump.fixed.sqlAfter file import problem solved, I started to measure the import time on RDS servers.
The first import on MariaDB:
start: 02:16:13
end: 04:40:51
time: 02:24:38
The second import on MariaDB:
start: 10:11:08
end: 12:37:10
time: 02:26:02
The first import on MySQL
start: 02:16:12
end: 05:15:01
time: 02:58:49
The second import on MySQL
start: 10:11:07
end: 13:14:55
time: 03:03:48
As you can see the restore time was faster with MariaDB them that the MySQL. I took some screenshots that show it graphically.
I always reboot the servers before after test to make sure they was at the initial state, with no cache that could interfere the process.
After restored the database I decide to import an example file that we use to daily feed the system that generate ours campaigns. The file has 365262 lines and is composed with:
msisdn|var1|var2|var3
which is stored in many-to-many tables.
The first import on MariaDB:
start: 18:25:29
end: 19:47:06
time: 01:21:37
inserts per second: 75
The first import on MySQL
start: 18:25:30
end: 19:44:41
time: 01:19:11
inserts per second: 76,88
So, MySQL ended 2 minutes and 26 seconds before the MariaDB. Above some graphics during the import.
After that I changed both RDS instances to "Provisioned IOPS" setting the value to 1000 and repeated the import process.
All results were very similar to the previous process, so decide not to repeat the graphics to not make the post very extensive. The actual gain was approximately 5% compared to SSD disks without IO provisioned.
But before start the next process (export de data) I want to see the differences between some common query operations like simple count(1) and see the results.
SELECT COUNT(1) FROM messages; // almost 13M records.
SELECT COUNT(1) FROM message_broadcasts; // almost 33M records.
These results made me very intrigued! Why the MariaDB was so faster than MySQL? If you have the answer please let me know in comments.
But I moved on and started benchmark of my daily data export (complex SQL query with 135 lines of joins, sub queries and selects) to statisticians who use the data to generate reports and extract informations about the campaigns.
The MySQL runs the process in 00:26:07 and the second time in 00:24:09. I always reboot the servers before start the test.
The MariaDB servers wasn't able to complete the task, always stopping the query after 9 or 10 minutes of execution. I tried a lot of configuration and nothing make the query runs.
After changing parameters to:
innodb_buffer_pool_size = {DBInstanceClassMemory*5/8} or
innodb_buffer_pool_size = {DBInstanceClassMemory*3/4} or
innodb_buffer_pool_size = {DBInstanceClassMemory*2/5}
I still get the same result:
Tue Jan 10 11:17:05 UTC 2017
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
Tue Jan 10 11:26:36 UTC 2017
After changing parameters to:
innodb_buffer_pool_size = {DBInstanceClassMemory*1/2}
Tue Jan 10 11:37:42 UTC 2017
ERROR 5 (HY000) at line 1: Out of memory (Needed 6406848 bytes)
Tue Jan 10 11:47:50 UTC 2017
After changing a lot of parameters trying to make the query run I decided stop trying to use MariaDB and decided to go with MySQL.
I contacted the amazon support team but despite all the efforts made we have not yet come to a real conclusion of what could be causing this problem.
The last status was that the cardinality of the banks was different, both of which were restored using the same file (dump.sql) generated from my current server which is MariaDB.
Maybe I'll start the hole process again some day…
Links:
