Scaling heavy database usage with mysql Master Slave configuration

Number of Read Replica influence on Heavy database usage overview

Results Overview

Here I’m experimenting a way to scale a web site that generates an heavy workload on the database.
The idea is to separate the reads from the insert/update sql statements in order to dispatch the reads (select) statements on serveral servers.
I’m going to use a mysql Master / Slave configuration (Read replica).

In order to fully understand the way it will work, to eliminate a maximum of PHP execution impact and to facilitate the test, I decided to use micro PHP benchs made up of simple select and insert statements.

I used the 2 very simple PHP scripts:

The database contains one table POST. I used the following script to create it:

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`post`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`post` (
  `idpost` INT NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR(50) NOT NULL ,
  `date_creation` DATETIME NULL ,
  PRIMARY KEY (`idpost`) )

ENGINE = InnoDB;

Mysql Master Slave Configuration

To setup the master slave configuration, I used the following commands:
On master:

$ sudo vi /etc/mysql/my.cnf
# Replication Block
log-bin = /var/lib/mysql/master-bin.log
log-bin-index = /var/lib/mysql/master-bin.index.log
binlog-do-db    =       mydb
server-id       =       1

innodb_flush_log_at_trx_commit=1
sync_binlog=1

$ sudo /etc/init.d/mysql restart

$ mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'wdmydb'@'%'
mysql> FLUSH PRIVILEGES;
mysql> use mydb
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      107 | mydb         |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

On slave:

$ sudo vi /etc/mysql/my.cnf
[mysqld]
# Replication Settings
read-only
server-id=2
#master-connect-retry=60 => error unknown variable in mysql 5.5
replicate-do-db=mydb

$ sudo /etc/init.d/mysql restart

$ mysql -u root -p
mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.223', MASTER_USER='wdmydb', MASTER_PASSWORD='[PASSWORD]';
mysql> START SLAVE;

The size of the innodb buffer was setup to 64M (to force the use of IO on disks)
innodb_buffer_size=64M

I did first a series of tests on one mysql server (VM mysql 2Vcpu, 2GB RAM see Configuration)

Insert and Select on the same server, 1 front, no replication

Database size: 300MB

Test #30, Select test

$ ab -c 10 -n 10000  -p post-data2 -T "application/x-www-form-urlencoded" http://tu-web-01/benchphp/select41.php
Requests per second:    609.07 [#/sec] (mean)

the post-data2 file contains just one line

Title=%2534%25&Date_Creation=1964-12-31 16:00:00

The percentage of usage CPU on the mysql server was 9%

Test #31, Insert Test

$ ab  -c 10 -n 100000 -p post-data1 -T "application/x-www-form-urlencoded" http://tu-web-01/benchphp/insert41.php
Requests per second:    1908.23 [#/sec] (mean)

The percentage of usage CPU on the mysql server was 43%

Test #32, Select Test, Insert & Select at the same time

$ ab -c 10 -n 10000  -p post-data2 -T "application/x-www-form-urlencoded" http://tu-web-01/benchphp/select41.php
Requests per second:    68.56 [#/sec] (mean)

Test #33, Insert Test, Insert & Select at the same time

$ ab  -c 10 -n 100000 -p post-data1 -T "application/x-www-form-urlencoded" http://tu-web-01/benchphp/insert41.php
Requests per second:    458.24 [#/sec]

The percentage of usage CPU on the mysql server was 100%
iostat result :

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          94.00    0.00    6.00    0.00    0.00    0.00

Insert on the mysql master, Select on slave server, 1 Front

Test #34, Impact of the replication on the server

First, I would like to know what decrease of performance I can expect when I insert on the master.

$ ab  -c 10 -n 100000 -p post-data1 -T "application/x-www-form-urlencoded" http://tu-web-01/benchphp/insert41.php
Requests per second:    925.07 [#/sec] (mean)

We have a result of 925 req/sec vs 1908.23 req/sec, so a decrease of 51% due to the replication on the slave

iostat results on mysql servers are :
On the mysql master server :

  • during the 1 min : %CPU = 59%, IO read 280K/s, write 24 MB/s
  • then after 1 min : %CPU = 56%, IO read 0K/s, write 24MB/s

On the mysql slave server:

  • The CPU is at 13% and the iops at : read 106 KB/s, write 8MB/s

 

Test #35, Read Replica, Select Test, Insert & Select at the same time

$ ab -c 10 -n 1000  -p post-data2 -T "application/x-www-form-urlencoded" http://tu-web-01/benchphp/select41.php
Requests per second:    69.32 [#/sec] (mean)

Test #36, Read Replica, Insert Test, Insert & Select at the same time

$ ab  -c 10 -n 100000 -p post-data1 -T "application/x-www-form-urlencoded" http://tu-web-01/benchphp/insert41.php
Requests per second:    794.92 [#/sec] (mean

iostat results on mysql servers are :
On the mysql master server :

  • %CPU = 50%, IO read 0K/s, write 20MB/s

On the mysql slave server :

  • %CPU = 100%, IO read 0K/s, write 7MB/s

This result shows that the CPU on the master is now at 50% (vs 100% without replica).

Test #37, Read Replica, 2 read replica, Impact of the replication on insert performance

First, I would like to know what decrease of performance I can expect when I insert on the master when I have 2 slaves

$  ab  -c 10 -n 100000 -p post-data1 -T "application/x-www-form-urlencoded" http://tu-web-01/benchphp/insert41.php
Requests per second:    743.93 [#/sec] (mean)

We have a result of 743 req/sec vs 1908.23 req/sec without replica and 925 req/sec for 1 replica , so a decrease of 61% compared to “No replica” and 19,6% compared to “1 replica”

iostat results on mysql servers are :
On the mysql master server :

  • %CPU = 68%, IO read 0K/s, write 20MB/s

On the mysql slave 1 :

  • %CPU = 29%, IO read 88K/s, write 7.3MB/s

On the mysql slave 2 :

  • %CPU = 28%, IO read 88K/s, write 7.8MB/s

Test #38, Read Replica, 2 read replica, Select Test, Insert & Select at the same time

In this test, I should dispatch the reads between 2 fronts
Rem:
I had to do twice the test because the first time I run the test, the 3 sql servers were on the first vmware host so the CPU of the Host was at 100%. I moved the second slave on a second vmware esxi to avoid saturing the CPU of the host

on first slave :

$ ab -c 10 -n 9620  -p post-data2 -T "application/x-www-form-urlencoded" http://tu-web-01/benchphp/select41.php
Requests per second:    60.03 [#/sec] (mean)

on second slave :

$ ab -c 10 -n 9620  -p post-data2 -T "application/x-www-form-urlencoded" http://tu-web-02/benchphp/select41-2.php
Requests per second:    70.17 [#/sec] (mean)

Rem:
The 2 results differ a little because the 2 vmware esxi are not loaded in an equivalent way (on ade-esxi-01, I have a front, the mysql master and the first mysql slave ; on ade-esxi-02, I have a front and the second mysql slave).
I was run out of servers, sorry. I decided that it was not a big deal, and took the sum of the 2 tests as, if I used a load balancer, the 2 select work loads were dispatched between the 2 servers

The sum of the 2 tests is : 130.02 req/sec

Test #39, Read Replica, 2 read replica, Insert Test, Insert & Select at the same time

$ ab  -c 10 -n 100000 -p post-data1 -T "application/x-www-form-urlencoded" http://tu-lb-01/benchphp/insert41.php
Requests per second:    635.59 [#/sec] (mean)

iostat results on mysql servers are :
On the mysql master server :

  • %CPU = 62%, IO read 0K/s, write 18.3MB/s

On the mysql slave 1 :

  • %CPU = 91%, IO read 88K/s, write 6.9MB/s

On the mysql slave 2 :

  • %CPU = 94%, IO read 88K/s, write 5.6MB/s

Rem:
The difference in IO between the 2 slaves is normal. The RAID system on the first slave is faster than the RAID system on the second slave.

Conclusion

I drew up 2 graphs that show the influence of the number of Read Replicas on the performance.
The first one displays the throughput in req/sec for the different tests and 0, 1 and 2 Read Replicas. The second displays the difference of performance in percentage.

Number of Read Replica influence on Heavy database usage

Number of Read Replica influence on Heavy database usage

Number of Read Replica influence on Heavy database usage (percentage diff)

Number of Read Replica influence on Heavy database usage (percentage diff)

These graphs show clearly the following symptoms:

  • The “pure” insert performance is sharply decreased when you set up read replicas. By a factor of roughly 50% for the first replica, and by 20% between the first and the second replica. If I have time, I will test with 3 replicas to check how the decrease is between 2 and 3 replicas.
  • However, you should notice that the Insert performance is also sharply increased by a 70% when you add one replica if heavy concurrent selects occur at the same time. It is normal as the master replica is relieved from managing select and inserts, only inserts (in this workload) are managed. This is a special case of this test, I guess that we result shouldn’t be the same for updates because the server had to select records for updating.
  • On the other hand, The select performance is improved (almost linearly) when you add read replicas (at least, you need 2 replicas to increase the performance as we don’t read in this configuration on the master), so the increase for “Selects” is roughly 100% between 1 and 2 replicas. Again, if I have time, I will test with 3 replicas to check how the increase is between 2 and 3 replicas.
Facebooktwittergoogle_plusredditpinterestlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *