Getting familiar with any product means either jumping in the deep end and hoping the weight of new knowledge is not enough to sink you or taking incremental steps walking from the shallow end towards the deep end (of knowledge in depth).
So as I proceed towards a robust test of MySQL Cluster on EC2, I thought it would be useful to see how MySQL holds up against a mysqlslap load simulation as a standard alone database and in a MySQL replication Master->Slave setup.
You can see my results for the standard databases in this post.
So the next step was to setup a standard master-slave replication. I have done this before, but it is always useful to keep the documentation handy. You will find the outline and how to setup MySQL replication from MySQL’s website.
Setting up MySQL replication on EC2 was as simple as firing up two instances of the same AMI with MySQL 5.1 installed and making the necessary changes to the master and slave my.cnf files.
Whilst you could have foregone the process of rebuilding the slave database from a master backup, it is always good practice to run though the steps. You could foregone this step as the database is actually the same on both instances as it is the same AMI.
Once the replication was configured and running I run the same tests as I ran the other day using mysqlslap.
Comments:
- The slave database never had any trouble even when the master was struggling until I increased the size of the auto-generated table significantly which suggests that the network bandwidth became the bottleneck rather than the IO or CPU bandwidth.
- MySQL started with the medium my.cnf sample and modified max_connections couldn’t handle more than 750 concurrent sessions without mysql complaining on a lack of resources and causing mysqlslap to die.
- At 500 concurrent sessions, the MySQL EC2 node was tapped out. Load spiked to 180 at one point.
As I mentioned this was the first step and using a simulated auto-generated workload. I am reviewing the new sample database called Sakila, provided by MySQL.
As I have time I am planning on building this series into a full blown performance test of MySQL on EC2. Essentially I am hoping that this work will allow people to understand exactly how MySQL scales on EC2 so they can resource their expected workload with confidence or alternatively know how to use mysqlslap to produce their own performance and scaling benchmarks.
Next up will be another run of MySQL 5.1 NDB cluster vs mysqlslap.
Have Fun
Paul
Previous Articles:
http://blog.dbadojo.com/2007/08/mysql-vs-mysqlslap.html
Here is the screen dump of my session
cat /etc/hosts
127.0.0.1 localhost
# Mysql replication
10.255.71.159 mysql-master
10.255.83.177 mysql-slave
Much sure my.cnf is set appropriately and restart MySQL:
service mysql.server restart
Shutting down MySQL.. [ OK ]
Starting MySQL [ OK ]
Setup MySQL replication:
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Create the replication user and grant replication privileges:
mysql> create user myslave IDENTIFIED BY '53cr37';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE
-> ON *.* TO 'myslave'@'mysql-slave'
-> IDENTIFIED BY '53cr37';
Query OK, 0 rows affected (0.00 sec)
Lock the tables during the backup to get a consistent backup:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
After the backup is complete unlock the tables again:
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
Grab the master status info
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 488
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
Backup the master database using mysqldump
mysqldump --all-databases --lock-all-tables -u root -p >dbdump.db
Enter password:
Copy the files to slave:
scp dbdump.db mysql-slave:/mnt
dbdump.db 100% 392KB 391.8KB/s 00:00
Set the Master setting on the slave:
CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_PORT=3306,
MASTER_USER='myslave',
MASTER_PASSWORD='53cr37',
MASTER_LOG_POS=488;
mysql>
mysql> CHANGE MASTER TO
-> MASTER_HOST='mysql-master',
-> MASTER_PORT=3306,
-> MASTER_USER='myslave',
-> MASTER_PASSWORD='53cr37',
-> MASTER_LOG_POS=488;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-master
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 488
Relay_Log_File: domU-12-31-35-00-52-43-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 488
Relay_Log_Space: 423
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
Running mysqlslap load simulation tests:
mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=100 \
--user=root --password
cat /tmp/mysqlslap.csv
blackhole,mixed,0.024,0.012,0.072,1,100
blackhole,mixed,0.039,0.020,0.083,25,4
blackhole,mixed,0.072,0.030,0.091,50,2
blackhole,mixed,0.094,0.051,0.115,100,1
myisam,mixed,0.026,0.012,0.075,1,100
myisam,mixed,0.057,0.026,0.091,25,4
myisam,mixed,0.061,0.033,0.124,50,2
myisam,mixed,0.109,0.052,0.119,100,1
mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q1000.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 \
--user=root --password
cat /tmp/mysqlslap_q1000.csv
blackhole,mixed,0.192,0.165,0.231,1,1000
blackhole,mixed,0.227,0.179,0.295,25,40
blackhole,mixed,0.253,0.188,0.312,50,20
blackhole,mixed,0.283,0.203,0.345,100,10
myisam,mixed,0.255,0.194,0.318,1,1000
myisam,mixed,0.282,0.205,0.354,25,40
myisam,mixed,0.302,0.275,0.362,50,20
myisam,mixed,0.357,0.306,0.403,100,10
mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q10000.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10000 --user=root \
--password
cat /tmp/mysqlslap_q10000.csv
blackhole,mixed,2.145,2.051,2.244,1,10000
blackhole,mixed,2.210,2.107,2.275,25,400
blackhole,mixed,2.128,1.953,2.240,50,200
blackhole,mixed,2.420,2.085,3.031,100,100
myisam,mixed,2.651,2.618,2.751,1,10000
myisam,mixed,2.710,2.637,2.821,25,400
myisam,mixed,2.698,2.637,2.788,50,200
myisam,mixed,2.901,2.599,3.741,100,100
mysqlslap --concurrency=1,25,50,100,150 --iterations=10 --number-int-cols=10 \
--number-char-cols=5 --auto-generate-sql --csv=/tmp/mysqlslap_bigtab_q10000.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10000 --user=root \
--password
cat /tmp/mysqlslap_bigtab_q10000.csv
blackhole,mixed,2.958,2.877,3.054,1,10000
blackhole,mixed,2.993,2.946,3.219,25,400
blackhole,mixed,2.982,2.897,3.088,50,200
blackhole,mixed,3.247,2.902,3.788,100,100
blackhole,mixed,3.262,2.966,4.075,150,66
myisam,mixed,3.540,3.442,3.628,1,10000
myisam,mixed,3.577,3.516,3.652,25,400
myisam,mixed,3.608,3.537,3.661,50,200
myisam,mixed,3.647,3.508,3.720,100,100
myisam,mixed,4.298,3.608,5.188,150,66
Setting the max_connections setting higher so I can test more than 150
concurrent sessions:
mysql> set global max_connections = 1024;
Query OK, 0 rows affected (0.00 sec)
mysqlslap --concurrency=1,25,50,100,250,500,1000 --iterations=10 --number-int-cols=10 --number-char-cols=5 \
--auto-generate-sql --csv=/tmp/mysqlslap_1000c_bigtab_q10000.csv --engine=blackhole,myisam \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
--number-of-queries=10000 --user=root --password
Sample of the top from the master with mysqlslap running:
top - 08:08:23 up 1:10, 2 users, load average: 161.43, 59.07, 26.07
Tasks: 2056 total, 100 running, 1949 sleeping, 0 stopped, 7 zombie
Cpu(s): 62.8% us, 34.6% sy, 0.0% ni, 0.0% id, 1.6% wa, 0.0% hi, 1.0% si
Mem: 1740944k total, 1548008k used, 192936k free, 20684k buffers
Swap: 917496k total, 0k used, 917496k free, 1323268k cached
mysqlslap --concurrency=1,25,50,100,250,500,1000 --iterations=10 --number-int-cols=10 --number-char-cols=5 \
> --auto-generate-sql --csv=/tmp/mysqlslap_1000c_bigtab_q10000.csv --engine=blackhole,myisam \
> --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
> --number-of-queries=10000 --user=root --password
Enter password:
mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1624178779,294458676,629436982,550287458,1828995128,517409934,1943137703,190133822,450874686,1121980228,'YhItGF0yXybWhdqQxRQHvltIGuCfwJzTs9M0OEyv4PHl274sQW9M7ugNokudvtyn2CnJQmEMBO76N3yqZzCZtkwaSrBGc93XDjqfLwtOK0WkwKaLJeu5MrfiaiyAju','ZwdNRyKKMYClG85iMHKGMaztHPR7SA11yWPPKddj9pKQqH8CQLKgvKZ4d0C52570cOHw1uXAkjSQ1TeJqy0lAPqoQ2lKzsLR8WG9PnC1NYRG1xbrvbdy1ReJLNfC8s','gGFwIgbMhyu0EwxinsHGiAbKLoct1klhs0d2Y4O53J5972run13L3eiPRAISuv1eoWhKaxQTGNfFIMA5F5QJj0k2A2WwxNRlt8yS5GpEfSTxjXuycD9CDtEGnd4d0X','EufCEdS3j0O4p7ckx4x7G2sNAg0FH0mXmIc0nwwWoKaeJ2E873FFx0e8YWNpN1C9JgA6T6SHI3vdx1lw4tC2liAH5PySQ2dejfcCd5Lv0HOxA2uFnyHYYJhdcf52Zb','YGE9KkEifErw31qx8dnH1KSax4DvzmWXvcY7KmhssOowIWlQzZc0l4Qi9fT8HrX4S4CoilYbACNSi0mhPMZARzlcWEcg6zkQ3o6eafDcrS4QSq7u5Z4YYh0vwSbuRB') ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)
tail -f /tmp/mysqlslap_1000c_bigtab_q10000.csv
blackhole,mixed,2.717,2.641,2.830,1,10000
blackhole,mixed,2.730,2.644,2.839,25,400
blackhole,mixed,2.668,2.482,2.747,50,200
blackhole,mixed,2.955,2.723,3.481,100,100
blackhole,mixed,2.969,2.853,3.288,250,40
blackhole,mixed,3.849,3.061,4.874,500,20
blackhole,mixed,5.514,3.760,8.081,1000,10
myisam,mixed,3.099,2.937,3.160,1,10000
myisam,mixed,3.142,3.055,3.263,25,400
myisam,mixed,3.217,3.141,3.268,50,200
myisam,mixed,3.217,3.110,3.319,100,100
myisam,mixed,4.256,3.319,5.561,250,40
myisam,mixed,4.306,3.781,7.740,500,20
Checking the table is ok after that failure
[root@domU-12-31-35-00-40-51 data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 49254
Server version: 5.1.20-beta-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqlslap |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysqlslap
Database changed
mysql> show tables;
+---------------------+
| Tables_in_mysqlslap |
+---------------------+
| t1 |
+---------------------+
1 row in set (0.00 sec)
mysql> check table t1 quick;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| mysqlslap.t1 | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.05 sec)
mysqlslap --concurrency=500,750 --iterations=10 --number-int-cols=10 \
--number-char-cols=5 --auto-generate-sql \
--csv=/tmp/mysqlslap_750c_bigtab_q10000.csv --engine=blackhole,myisam \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
--number-of-queries=10000 --user=root --password
Enter password:
mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1624178779,294458676,629436982,550287458,1828995128,517409934,...
ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)
cat /tmp/mysqlslap_750c_bigtab_q10000.csv
blackhole,mixed,3.682,3.053,4.811,500,20
blackhole,mixed,6.265,3.496,10.161,750,13
myisam,mixed,5.009,3.662,7.803,500,20
mysqlslap --concurrency=500,750 --iterations=10 --number-int-cols=10 \
--number-char-cols=5 --auto-generate-sql \
--csv=/tmp/mysqlslap_write_750c_bigtab_q10000.csv --engine=blackhole,myisam \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write \
--number-of-queries=10000 --user=root --password
Enter password:
mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1413946291,2008175571,806084542,1096535128,1877357308,2039695798,
1522423739,1247119446,677182057,1658524254,
'4uoCZg37QkwQxLB0Jl7CkWw6qGxpFQbJLFYmvRlDBig13jtESsJCEFA4Nz...
ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)
mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1338459946,2107889310,393787969,1623810840,1403227682,...
ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)
Killed
tail -f /tmp/mysqlslap_write_750c_bigtab_q10000.csv
blackhole,write,3.736,2.935,5.308,500,20
blackhole,write,5.025,3.172,7.826,750,13
myisam,write,4.769,3.630,6.532,500,20
DBA Dojo 