Testing the performance of forks Mysql on real data

Introduction

An upgrade of the web server system has started, on which the website of the online store has been spinning since 2007 on the mysql 5.1 + perl + apache + nginx samopisny engine.

As usual, with an increase in attendance, everything began to rest on the database. Began to choose a new database that is compatible with the current one. Chose from Mysql 5.5, Mysql 5.6, MariaDB 10, Percona Sever 5.6.

After a long study of benchmarks, it became clear that you need to test performance on real data. Firstly, in most cases InnoDB and XtraDB were compared, secondly, they were testing mostly mad loads on monster servers, I was interested in the indicators on a narrow section of the graph, where usually nothing was clear.

Preparation for testing

1. Create a virtual machine, I chose a simple configuration with 4 cores and 4GB of RAM, Debian 7.6;
2. We put nginx and apache, the configuration is taken out of the box without cache and tuning;
3. We put siege - a utility for load testing, there is a standard repository;
4. We climb to the site statistics and select the most popular pages in order to test the work of the site fully. I selected the first 50 pages of hits over the last month, save these URLs in urls.txt, Yandex.Metrica can upload this data to csv;
5. Register in / etc / hosts virtual ip for the required domain;
6. We connect repositories of Mysql, Maria, Percona
   ')
   

     deb http://mirror.timeweb.ru/mariadb/repo/10.0/debian wheezy main
    deb-src http://mirror.timeweb.ru/mariadb/repo/10.0/debian wheezy main
    deb http://repo.flops.ru/debian/ wheezy free
   
    deb http://repo.percona.com/apt wheezy main
    deb-src http://repo.percona.com/apt wheezy main
   
    deb http://repo.mysql.com/apt/debian/ wheezy mysql-5.6
    deb-src http://repo.mysql.com/apt/debian/ wheezy mysql-5.6
   

   
   
   Mysql 5.5 is in the standard repository;
   
7. We transfer the dump of the old database, create another 2 dumps with the substitution of the inventory
   

    sed -e 's / myisam / InnoDB / gi' dump_myisam.sql> dump_innodb.sql 
    sed -e 's / myisam / XtraDB / gi' dump_myisam.sql> dump_xtradb.sql
   

   
   

Self testing

We put in turn the databases and fill in the dumps we created there, we are testing using siege. When installing a new database, we completely demolish the previous packages and in order not to bathe with the conversion, delete the / var / lib / mysql directory, then fill in the new dump.

I chose the following parameters for siege: / usr / bin / siege -b -c 20 -r 50 -f urls.txt -v

• -b turns on benchmark mode, and does not make arbitrary pauses between requests, this option can be not used at your discretion;
• -c 20 sets the number of simultaneous requests. I chose 20, which roughly corresponds to the peak load on the combat configuration in my case;
• -r 50 sets the number of requests from each stream, it makes sense to equal the number of URLs for testing in order to run through all;
• -f urls.txt - sets the file with the tested URL of the site.

As a result, siege makes 1000 requests to the site in 20 threads.

Trusted database options

Disk Size: 300MB (in MyIsam)
Read / Write: 98% / 2%

The main table has 40000 records, about 200 columns (it stores goods and their properties), additional parameters join it: manufacturers, exchange rates, groups, discounts and other special signs, units of measure, collections ... About 10 joins.

my.cnf

 key_buffer = 512M
 join_buffer_size = 158M
 max_allowed_packet = 16M
 thread_stack = 192K
 thread_cache_size = 8
 myisam-recover = BACKUP
 max_connections = 100
 table_cache = 500
 query_cache_limit = 16M
 query_cache_size = 2G

 innodb_log_file_size = 50M
 innodb_buffer_pool_size = 512M
 innodb_log_buffer_size = 8M
 innodb_file_per_table = 1
 innodb_open_files = 2548
 innodb_io_capacity = 400
 innodb_flush_method = O_DIRECT

Test results

Column Description

• ETime - Total Test Time (1000 requests) in seconds
• RTime - Average response time in seconds
• TRate - The average number of requests per second
• Conc - Maximum number of customers maintained at the same time
• OK - The number of successfully processed requests (status 200 OK)
• LA - Maximum Load Average during testing
• MEM - Total occupied memory on the virtual machine

Base / Store	ETime	RTime	TRate	Conc	Ok	LA	Mem
Mysql 5.1 mysam	252.98	3.95	3.83	15.15	969	6.98	1055
Mysql 5.5 myisam	500.19	7.48	1.80	13.50	902	14.67	1265
Mysql 5.6 MyISAM	289.89	5.55	3.43	19.01	994	6.59	1000
Percona 5.6 MyISAM	510.71	8.42	1.80	15.15	919	11.76	1657
MariaDB 10.0 MyISAM	351.74	6.10	2.74	16.71	964	10.23	889
MariaDB 10.0 Aria page = 8k Trans	904.76	12.07	0.74	8.89	666	27.86	1545
MariaDB 10.0 Aria page = 1k NonTrans	781.43	10.47	0.94	9.89	738	27.76	1465
Mysql 5.1 InnoDB	368.21	5.91	2.55	15.08	940	7.23	1241
Mysql 5.6 InnoDB	229.92	4.54	4.35	19.76	1000	6.66	1414
MariaDB InnoDB	223.35	4.38	4.48	19.61	1000	6.24	1813
Percona 5.6 XtraDB	223.78	4.42	4.47	19.75	1000	5.09	1176
MariaDB 10.0 XtraDB	222.55	4.38	4.49	19.66	1000	6.39	1176

Charts

From the test data I choose between Maria DB and Percona Server on XtraDB. I bow to Percona. In addition to a slight gain in performance, there are also extended statistics on queries to tables and a good set of utilities.

PS Testing took about 5 hours, most of the time it took the transfer of data from the combat vehicles and the installation / demolition of packages. In general, my hoster (flops.ru) allows you to clone a virtual machine, it takes about 1 minute and transfer IP between virtual machines. If it were not for the old software on the combat virtual machine, it would be possible to clone it and test it (the funds are debited daily for consumption). It would save me a lot of time.

PPS I think the next step is to arrange testing in combat mode, make a virtual clone, set up a test slice and transfer the IP address from the combat one day.