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 (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.
Good night,
Do you use any tool for ajust the parameters on my.cnf? as caches size, max queries, etcc…
I’m using a master with seven slaves replication. each slave has 10Gb Ram and 4 vcpu, but i’m not sure if I have my.conf right.
Good toturial,
Regards.