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