MySQL Troubleshooting

Special Testing Tools

When you test a problem that has more than one solution, such as a performance issue or the design of your SQL application, you need to test how each suits your needs. This section offers a quick overview of tools that can help in such testing.

Benchmarking Tools

Benchmarking tools test an application’s speed. MySQL benchmarking tools usually test MySQL installations, which is not the same as testing an application, but they can still be useful for testing a particular set of options. If a benchmarking tool allows you to use custom queries written specially for your application, you also can run tests on your own dataset.

The most popular benchmarks for MySQL are sysbench and mysqlslap. The following subsections describe them.

mysqlslap

mysqlslap is a load emulation client that comes with the MySQL distribution. It makes it easy to test concurrent loads on similar queries. Run it with an SQL script, either from a file or specified as an argument:

$ mysqlslap  --socket=/tmp/mysql51.sock --user=root --delimiter=";" \
--create-schema=mstest --create="CREATE TABLE mstest(id INT NOT NULL \
AUTO_INCREMENT PRIMARY KEY, f1 VARCHAR(255)) ENGINE=InnoDB" --query="INSERT INTO \
mstest(f1) VALUES(MD5(RAND())); SELECT f1 FROM mstest;" --concurrency=10 \
--iterations=1000 Benchmark Average number of seconds to run all queries: 0.039 seconds Minimum number of seconds to run all queries: 0.025 seconds Maximum number of seconds to run all queries: 0.173 seconds Number of clients ...