Mysqlslap for mysql load testing

Mysqlslap is a Load Emulation Client. mysqlslap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server. mysqlslap is available as of MySQL 5.1.4.

mysqlslap runs in three stages:

1. Create schema, table, and optionally any stored programs or data you want to using for the test. This stage uses a single client connection.

2. Run the load test. This stage can use many client connections.

3. Clean up (disconnect, drop table if specified). This stage uses a single client connection.

Please find expalnations for mysqlslap options below.

# /usr/local/mysql/bin/mysqlslap -uroot -p –create-schema=DBName –query=file.txt –concurrency=100 –iterations=1 –delimiter=”;”

mysqlslap usage

To apply auto generated queries,
# mysqlslap -uroot -p –auto-generate-sql

To use multiple simultaneous client connections,
# mysqlslap -uroot -p –auto-generate-sql –concurrency=100

To run the above command multiple times, iteration option is used,
# mysqlslap -uroot -p –auto-generate-sql –concurrency=100 –iterations=5

To specify the number of queries each client executes,
# mysqlslap -uroot -p –auto-generate-sql –concurrency=100 –number-of-queries=1000

The default behavior of mysqlslap when using the –auto-generate-sql switch is to create a two-column table with one integer column and one character column. To adjust these settings to include more integer and/or character columns, use the –number-char-cols and –number-int-cols switches.
# mysqlslap -uroot -p –auto-generate-sql –concurrency=100 –number-of-queries=1000 –number-char-cols=4 –number-int-cols=7

To specify the storage engine –engine option is used,
# mysqlslap -uroot -p –auto-generate-sql –concurrency=100 –number-of-queries=1000 –number-char-cols=4 –number-int-cols=7 –engine=innodb

We can also apply custom queries to mysqlslap either from a text file or directly from command line using –query option. In order to use an existing database use the –create-schema option. This doesnt create a schema but uses an existing schema.
# mysqlslap -uroot -p –create-schema=Cherry_1H_B-01 –query=cherry_1h.txt –concurrency=100 –iterations=5 –delimiter=”;”