Benchmarking MySQL Server

Before proceeding to production on a complex database server, it is always necessary to benchmark your server. There are different tools available for this purpose like that of Todd.

There is a tool in MySQL itself. Though primitive, it is interesting to try it out. The MySQL Benchmark Tool, sql-bench, is a perl program, which can be found in the folder sql-bench under the MySQL program folder.

Installation of Perl DBD/DBI for MySQL

On Windows, you should do the following to install the MySQL DBD module and DBI with ActiveState Perl.  Get ActiveState Perl 5.6 or later from

http://www.activestate.com/Products/ActivePerl/

and install it. Open a command window.  Type ppm at the command prompt. The Perl Package Manager starts as follows: 

perl.jpg

 

Here you may add new packages. Add the package DBD-MySQL, the MySQL Driver for Perl Database Interface (DBI). Then add the package DBI also. 

Start the MySQL Benchmark Tool 

At sql-bench directory of MySQL, run the following command: 

Sql-bench:\> perl run-all-tests – -host {host name} – -user root – -password {password}

See below one of the sample screens it produces on Innodb stress test:

Testing server ‘MySQL 5.0.36sp1 enterprise gpl nt log/’ at 2007-0627 14:15:44

Innotest2: MySQL/InnoDB stress test in Perl for FOREIGN keys

This is a randomized stress test for concurrent inserts, updates, deletes, commits and rollbacks with foreign keys with the ON DELETE … clause. The test will generatealso a lot of deadlocks, duplicate key errors, and other SQL errors.You should run innotest2, innotest2a, and innotest2b concurrently.The thing to watch is that the server does not crash or does not print to the .err log anything. Currently, due to a buglet in MySQL, warnings about MySQL lock reservations can appear in the .err log. The test will run very long, even several hours. You can kill the perl processes running this test at any time and do CHECK TABLE on tables innotest2a, b, c, d in the ‘test’ database. 

 

 

 

 

 

 

 

 

 

 

 

 

 

Advertisements

One thought on “Benchmarking MySQL Server

  1. Igor D.

    I cannot find complete sql-bench directory in any version of MySQL noinstall package, and I’ve searched through and through the http://WWW... If you know where I can download one please tell me. Thanks for the nice tutorials!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s