Use Benchmarking to Improve Performance

Last Updated on: March 13, 2011

One of the best ways to check the performance of your MySQL application is to benchmark it regularly. Put the application under as much load as you can to simulate the worst possible reasonable load and check the performance.

Several functions within MySQL can help you to test your databases. One of the most useful of these is the Benchmark() function, which tests the time taken to perform an expression a defined number of times. The function calculates the time on the client, not the server, so the test should be run several times and the average taken.

{code type=php}
mysql> Benchmark(1000000, SELECT CURRENTDATE());
0
1 row in set (7.15 sec)
{/code}

MySQL also comes complete with a Benchmark suite that will tell a user what operations your SQL implementation performs well or badly. You can find this in the sql-bench folder of your MySQL source distribution. This benchmark will only run on a single thread so will only measure the minimum time for the operations to perform. The benchmark scripts are coded in Perl and that must be installed to run them.

To execute the benchmark tests, navigate to the sql-bench directory and run the run-all-tests script.

{code type=php}
shell> cd sql-bench
shell> perl run-all-tests –server=myserver
{/code}

Another useful script is crash-me, which attempts to determine the features that a database supports by running queries against it. The crash-me script can find out:

• Supported data types
• Number of supported indexes
• Supported functions
• Maximum query size
• Maximum size of a VARCHAR column

For the maximum benefit, run these scripts on a regular basis and use the results to improve the performance of your database. When you find one bottleneck, replace that piece of code with dummy code that executes fast to find the next bottleneck. Once all the bottlenecks are identified, look to rewrite the code segment to improve performance.