Previous Section  < Day Day Up >  Next Section

3.3 Benchmarking Tools

In this chapter we'll introduce three useful benchmarking tools:

  • The MySQL Benchmark Suite, which is useful for making comparisons between different database engines or different installations of one database engine. It isn't meant to benchmark your site-specific data or needs.

  • MySQL super-smack, a stress-testing tool.

  • MyBench, a tool developed in Perl by one of the authors. It is another stress-testing tool that is easier to customize and extend than super-smack.

The benchmark tools presented in this chapter may not run under Windows due to the lack of a Perl interpreter or binaries compiled for Windows. Because versions of Perl for Windows are readily (and freely) available from ActiveState, there's a good chance MyBench may work. However, neither of the authors use Windows, and we have not tried to confirm this.

However, these tools do run on Linux and most Unix-like platforms and can be used to test remote servers. So you might run them on Linux or Solaris to remotely benchmark a Windows 2000 server running MySQL.


3.3.1 The MySQL Benchmark Suite

The MySQL distribution comes with a rather comprehensive set of generic tests that have been bundled together so you can run them as a group and examine the results. The tests will do little to help you figure out whether a configuration change will speed up your application. But they're very helpful when used as a high-level benchmark, meaning they provide a good overall indication of how well one server performs relative to another.

You can also run the tests individually if you'd like compare a subset of the results from several servers. If you're mainly interested in UPDATE speed, run one of the UPDATE-intensive tests a few times on each server.

The benchmark suite can be used to test non-MySQL servers as well. According to the README, PostgreSQL, Solid, and mSQL have been tested. This may be helpful if you're trying to choose between MySQL and PostgreSQL. All the benchmark code is relatively generic Perl using the DBI and Benchmark modules. If needed, you can add support for nearly any database server that has a DBI driver (Oracle, Sybase, Informix, DB2, etc.). If you do so, be sure to look at the bench-init.pl for any global options you may need to add or change.

By running the benchmarks against several different servers, you'll get an idea of how much faster one server is than another. The tests are largely CPU-bound, but there are portions of the test that demand a lot of disk I/O (for short times). You'll likely find that the 2.4-GHz CPU doesn't necessarily make MySQL run twice as fast as the 1.2-GHz CPU.

The benchmark suite will not help you test the benefits of multi-CPU machines because the benchmark process is completely serialized. It executes one query after another, so MySQL will not benefit from the addition of a second CPU. To test that, you'll need to use MySQL super-smack or a home-grown solution. Both are covered in the following sections.

To run the tests, use the run-all-tests script located in the sql-bench directory. Be sure to read the README in that directory. It provides a complete list of the command-line options you can use.

$ cd sql-bench

sql-bench$ ./run-all-tests --server=mysql --user=root --log --fast

Test finished. You can find the result in:

output/RUN-mysql_fast-Linux_2.4.18_686_smp_i686

The benchmarks may take quite a while to run, depending on your hardware and configuration. On a dual 933-MHz Pentium 3, it took over an hour to execute the tests using MySQL 4.0.13. While it's running, however, you can watch the progress. The --log flag causes results from each test to be logged in a subdirectory named output. Each file contains a series of timings for the various operations in each benchmark test. Here's a small sampling, slightly reformatted for printing:

sql-bench/output$ tail -5 select-mysql_fast-Linux_2.4.18_686_smp_i686

Time for count_distinct_group_on_key (1000:6000):

  34 wallclock secs ( 0.20 usr  0.08 sys +  0.00 cusr  0.00 csys =  0.28 CPU)

Time for count_distinct_group_on_key_parts (1000:100000):

  34 wallclock secs ( 0.57 usr  0.27 sys +  0.00 cusr  0.00 csys =  0.84 CPU)

Time for count_distinct_group (1000:100000):

  34 wallclock secs ( 0.59 usr  0.20 sys +  0.00 cusr  0.00 csys =  0.79 CPU)

Time for count_distinct_big (100:1000000):

  8 wallclock secs ( 4.22 usr  2.20 sys +  0.00 cusr  0.00 csys =  6.42 CPU)

Total time:

  868 wallclock secs (33.24 usr  9.55 sys +  0.00 cusr  0.00 csys = 42.79 CPU)

As you can see, the count_distinct_group_on_key (1000:6000) test took 34 "wallclock" seconds to execute. That's the total amount of time the client took to run the test. The other values (usr, sys, cursr, csys) that added up to 0.28 seconds constitute the overhead for this test. That's how much of the time was spent running the benchmark client code rather than waiting for the MySQL server's response. This means that the figure we care about—how much time was tied up by things outside the client's control—totalled 33.72 seconds.

It's also worth noting that you can run the tests individually if you need to. Rather than rerun the entire suite, you may decide to focus on the insert test. By doing so, you see a bit more detail than was in the summarized files left in the output directory:

sql-bench$ ./test-insert

Testing server 'MySQL 4.0.13 log' at 2003-05-18 11:02:39



Testing the speed of inserting data into 1 table and do some selects on it.

The tests are done with a table that has 100000 rows.



Generating random keys

Creating tables

Inserting 100000 rows in order

Inserting 100000 rows in reverse order

Inserting 100000 rows in random order

Time for insert (300000):

  42 wallclock secs ( 7.91 usr  5.03 sys +  0.00 cusr  0.00 csys = 12.94 CPU)

Testing insert of duplicates

Time for insert_duplicates (100000):

  16 wallclock secs ( 2.28 usr  1.89 sys +  0.00 cusr  0.00 csys =  4.17 CPU)

3.3.2 MySQL super-smack

Developed by Sasha Pachev, a former MySQL AB employee, super-smack is a stress-testing tool that can talk to both MySQL and PostgreSQL. The super-smack tool really deserves wider recognition, because it's very powerful. Using a simple configuration file syntax, you can define a series of tests (a query barrel) to run against your server along with the data and tables needed to support the tests. When running the tests, you control how many concurrent clients will be simulated (one per thread) and how many iterations of each test the clients will execute using command-line arguments.

Because the tool simulates many simultaneous users, it works very well for testing multi-CPU servers. And even on single CPU machines, it allows you to generate more realistic test scenarios as well as perform stress tests.

A typical test with super-smack involves creating one or more large tables and populating them with various data, chosen from an input file or generated on the fly. It then proceeds to beat on the created tables using a series of queries that are defined by the user via a configuration file. The values used in the queries are selected from an external file in either random or sequential order.

As you'll see, using MySQL super-smack requires more work than using the supplied benchmarks. While it will take some time to get super-smack set up and running the first time, you'll benefit from having much greater control over the tests. With a little practice, you can create custom tailored benchmarks in very little time.

You'll first need to download and build super-smack before you can begin testing; it doesn't come with MySQL. As of this writing, the current release is available from http://jeremy.zawodny.com/mysql/super-smack/. It uses GNU autoconf, so the installation process is relatively simple as long as your build tools are reasonably current.

/tmp$ tar -zxf super-smack-1.1.tar.gz

/tmp$ cd super-smack-1.1

/tmp/super-smack-1.1$ ./configure --with-mysql

... lots of configure output ...

/tmp/super-smack-1.1$ make

... lots of compilation output ...

/tmp/super-smack-1.1$ sudo make install

Be sure to read the MANUAL and TUTORIAL files included in the distribution. They cover topics that we may not—especially if you're using a newer version.

To get started with super-smack, let's look at the example benchmarks it includes. In /usr/share/smacks, you'll find a small collection of smack files:

/usr/share/smacks$ ls -l

total 8

-rw-r--r--    1 jzawodn  jzawodn      3211 Feb  2  2004 select-key.smack

-rw-r--r--    1 jzawodn  jzawodn      3547 Feb  2  2004 update-select.smack

These files contain the commands necessary to populate a table and execute a bunch of queries against it. Before diving into the configuration file, let's give it a quick run. We'll ask it to simulate 30 concurrent users, each running 10,000 iterations of the test queries.

/usr/share/smacks$ super-smack update-select.smack 30 10000

Error running query select count(*) from http_auth:Table 'test.http_auth' doesn't exist

Creating table 'http_auth'

Loading data from file '/var/smack-data/words.dat' into table 'http_auth'

Table http_auth is now ready for the test

Query Barrel Report for client smacker

connect: max=49ms  min=0ms avg= 14ms from 30 clients 

Query_type      num_queries     max_time        min_time        q_per_s

select_index    300000          10              0               2726.41

update_index    300000          5               0               2726.41

The test requires a table named http_auth to operate. Since the table didn't exist, the test used the data in /var/smack-data/words.dat to populate the table. Then super-smack ran the tests and produced results.

After the "Query Barrel Report" line, you can see the performance stats from this benchmark run. (A query barrel, as you'll see later, is a set of queries run by super-smack in each iteration.) The first line provides connection stats, which list the maximum, minimum, and average connection times for each of the 30 clients—that is, how long the client waited for the server when establishing a connection.[2]

[2] The super-smack tool uses persistent connections. Each client connects once and remains connected for the duration of the test run. You can't use super-smack to simulate nonpersistent connections.

The remaining lines provide statistics for each type of test defined in the smack file. For each, you see the number of times the query was executed (this should always match what you specified on the command line), the maximum time the query took, the minimum time the query took, and the number of queries executed per second.

Running with different values (fewer clients), you'll see the performance was actually higher: 3,306 queries/sec versus 2,726 queries/sec.

/usr/share/smacks$ super-smack update-select.smack 5 10000

Query Barrel Report for client smacker

connect: max=2ms  min=1ms avg= 1ms from 5 clients 

Query_type      num_queries     max_time        min_time        q_per_s

select_index    50000           1               0               3306.66

update_index    50000           1               0               3306.66

That's likely because we ran the super-smack client on the same machine as MySQL, so the two were competing for CPU time. In real-world testing, you'd probably have the client and server separated, and you'd want to run the same benchmark several times to rule out any anomalies.

3.3.2.1 Preparing test data

Using the words.dat data as input works in the http_auth benchmark, but when testing your applications, you'll need to supply your own data. There is no one-size-fits-all answer for how to generate your test data. You have to determine what data to create or extract for use in the tests. Once you've done that and loaded the data into MySQL, you need to extract the relevant values into a file that super-smack can read during testing.

For example, if you're testing an online product catalog in which items will be selected based on their product ID, you'll need a list of product IDs to use during testing. For a comprehensive test, use all the product IDs. If you have millions of products, it may be sufficient to test a subset of them.

In either case, first get a list of the product IDs into a text file that you can then drop into /var/smack-data/ to use during the testing. The easiest way to do that to use MySQL's SELECT ... INTO OUTFILE construct:

SELECT id INTO OUTFILE "/tmp/product.dat" FROM product

That produces a file containing one product ID per line—perfect for use with super-smack. If your test requires multiple columns of data, you can produce a file of quoted comma-separated values:

SELECT id, type INTO OUTFILE "/tmp/product.dat"

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY "\n"

FROM product

super-smack allows you to specify a field delimiter to be used for input files, as you'll see. Also be sure to copy your file to /var/smack-data/.

3.3.2.2 Configuration

Having installed and tested super-smack, let's spend some time dissecting one of the standard smack files. Along the way, we'll consider how you might adapt the file to your own testing needs.

The file presented here is a bit different from the one contained in the super-smack distribution. The functionality is the same, but the comments and formatting have been adjusted.


The smack file looks like a stripped-down scripting language that's loosely based on C or Perl. Each smack file defines several objects that are used in the main block of the file: clients, tables, dictionaries, and queries.

client "admin"

{

  user "root";

  host "localhost";

  db "test";

  pass "";

  socket "/var/lib/mysql/mysql.sock";

}

The first section defines an admin client using the root account on localhost's server and assumes there's no password on the account.[3] If you plan to run super-smack on a remote client, be sure to update the settings appropriately. The socket should be left empty (or removed) in that case. If you're running MySQL on a nonstandard port, specify that in the client section(s):

[3] If you don't specify a password, super-smack does not prompt you for one. We point this out only because many other MySQL tools prompt you.

  port "3307";

Next, define the table and data used for the tests:

table "http_auth"

{

  client "admin";



  create "create table http_auth

    (username char(25) not null primary key,

     pass char(25),

     uid integer not null,

     gid integer not null

    )";



  min_rows "90000";

  data_file "words.dat";

  gen_data_file "gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d";

}

There's a lot going on here. First, we specify that the table will be created and populated using the admin user options specified previously. Then we provide a CREATE TABLE specification. If the table doesn't already exist, super-smack creates it. We also specify a minimum number of rows. If the table exists but doesn't have sufficient rows, super-smack will drop and recreate the table. Then, if needed, it will load the data from the words.dat file, which is expected to live in /var/smack-data. Finally, if that file doesn't exist, super-smack uses gen-data (which comes with super-smack) to create 90,000 rows of random data.

The gen-data command isn't documented, but as you can see, it requires a number of rows (-n) and a printf-style format string (-f). Sample output for our command looks like:

$ gen-data -n 5 -f %12-12s%n,%25-25s,%n,%d

pajgyycklwiv1,qbnvqtcewpwvxpobgpcgwppkw,1,763719779

epqjynjbrpew2,mhvcdpmifuefqdmjblodvlset,2,344858293

fbntssvvmwck3,cfydxkranoqfiuvyhqvtprmpx,3,2125632375

fcwtayvakrxr4,ldaprgacrwsbujrnlxxsxqwse,4,1513050921

jnaixvfvktpf5,htihaukugfiurnnmxnysypsnr,5,1872952907

super-smack loads the output into the table using the LOAD DATA command.

In real-life testing, you probably won't be using super-smack to populate your tables. Instead, you can simply use a copy of your real data.

Next we have a dictionary definition:

dictionary "word"

{

  type "rand";

  source_type "file";

  source "words.dat";

  delim ",";

  file_size_equiv "45000";

}

A dictionary is simply a source for words that will later be used when constructing queries. It's a simple mechanism that gives you control over which values are used in queries and how they are used.

The dictionary type can be one of the following:


rand

Values are selected randomly from the list.


seq

Values are used sequentially.


unique

Generate unique values using the same method as gen-data.

The source_type may be one of the following:


file

A file read from disk.


list

A user-supplied list of words, comma-separated.


template

The format to use when type is unique. For example, "jzawodn_%07d" generates values composed of jzawodn_ and a seven-digit number.

The source is either a filename (assumed to be in the /var/smack-data directory) or a comma-separated list of quoted values ("one","two","three") when using a source_type of list.

If you use a delimited file, the delim option tells super-smack which character separates the input fields in your source file. The file_size_equiv option is helpful when you have a very large dictionary. Rather than use every word, super-smack divides the file size by this number. The result is then used to skip records in the input.

For example, if your file is 100 KB in size and you specify a file_size_equiv of 10,240, super-smack divides the two and knows to use only one tenth of the input. It will test using every tenth value in the source file.

Next are two query definitions, one for a series of SELECT queries followed by an UPDATE query generator:

query "select_by_username"

{

  query "select * from http_auth where username = '$word'";

  type "select_index";

  has_result_set "y";

  parsed "y";

}

query "update_by_username"

{

  query "update http_auth set pass='$word' where username = '$word'";

  type "update_index";

  has_result_set "n";

  parsed "y";

}

The queries are relatively simple. If you'd like to substitute a word from the dictionary in the query, simply use the $word placeholder and be sure to set parsed to y; otherwise super-smack uses your query as is.

The type is simply a tag or name for this set of queries. It is reported by name in the final statistics. The has_result_set option tells super-smack whether the query returns data.

Next, one more client is defined:

client "smacker"

{

  user "test";

  pass "";

  host "localhost";

  db "test";

  socket "/var/lib/mysql/mysql.sock";

  query_barrel "1 select_by_username 1 update_by_username";

}

Unlike the previous client, this one has a query_barrel associated with it. The query barrel defines the order and number of queries the client will run during each iteration. In this case, we've instructed it to execute one select_by_username query followed by one update_by_username query. You can adjust the numbers to suit your particular testing needs, of course.

Finally, we get to the main section of the smack file. It controls the actual flow.

main

{

  smacker.init( );

  smacker.set_num_rounds($2);

  smacker.create_threads($1);

  smacker.connect( );

  smacker.unload_query_barrel( );

  smacker.collect_threads( );

  smacker.disconnect( );

}

One of the first things to notice is that command-line arguments are available in shell-style numbered variables ($1, $2, etc.). So if you'd like to reverse the order of arguments on the command line, you can do so.

The code's flow is straightforward. It begins by initializing the smacker client. Then we set the number of rounds and create the necessary threads. Each thread then connects to the server and unloads its barrel of queries, keeping statistics along the way. The collect_threads function causes the main thread to wait for the others to complete. The clients then disconnect, and the statistics are reported.

When you look at the setup piece by piece, it's easy to digest. The same framework works for a wide variety of testing. The main section rarely changes. And, for very simple tests (such as a single query), there's no need to define multiple users. If you are creating a benchmark to simulate a relatively complex application that requires various username and password combinations to access all the necessary data, you'll need to also define them in your smack file.

3.3.3 MyBench: A Home-Grown Solution

MySQL super-smack is a great tool, but it's not terribly extensible unless you want to dive into the C++ code. When you need custom logic that's not easy to express in super-smack's configuration, it's probably time to turn to your favorite scripting language.

When Jeremy encountered this problem in back in 2001, he developed a very simple Perl-based system called MyBench. It handles the details of spawning clients, gathering and computing statistics, and so on. The downside is that it's quite a bit heavier on the client side. You really shouldn't run the benchmark client on the same machine as MySQL.

You can download the code from http://jeremy.zawodny.com/mysql/mybench/. To use it you'll need DBI, DBD::mysql, and Time::HiRes installed. The MyBench.pm module contains the common logic. Creating a simple benchmark is a matter of adding your logic to the supplied bench_example script.

As we did with super-smack, let's look through the bench_example script to understand how it works. The first few lines simply import the required modules and set up some simple command-line option handling. It requires two command-line arguments. The -n argument specifies the number of clients to simulate (children to fork), and -r sets the number of iterations each client will run. The optional -h argument can specify a hostname.

#!/usr/bin/perl -w



use strict;

use MyBench;

use Getopt::Std;

use Time::HiRes qw(gettimeofday tv_interval);

use DBI;



my %opt;

Getopt::Std::getopt('n:r:h:', \%opt);



my $num_kids  = $opt{n} || 10;

my $num_runs  = $opt{r} || 100;

my $db        = "test";

my $user      = "test";

my $pass      = "";

my $port      = 3306;

my $host      = $opt{h} || "192.168.0.1";

my $dsn       = "DBI:mysql:$db:$host;port=$port";

Of course, you can hardcode the values if you'd like, or you can make the script more generic by parameterizing the connection information (db, user, pass, port, host).

With the setup out of the way, the script sets up a callback function. It will be called by the code to set up an initial connection and run the tests.

my $callback = sub

{

    my $id  = shift;

    my $dbh = DBI->connect($dsn, $user, $pass, { RaiseError => 1 });

    my $sth = $dbh->prepare("SELECT * FROM mytable WHERE ID = ?");



    my $cnt = 0;

    my @times = ( );



    ## wait for the parent to HUP me

    local $SIG{HUP} = sub { };

    sleep 600;



    while ($cnt < $num_runs)

    {

        my $v = int(rand(100_000));

        ## time the query

        my $t0 = [gettimeofday];

        $sth->execute($v);

        my $t1 = tv_interval($t0, [gettimeofday]);

        push @times, $t1;

        $sth->finish( );

        $cnt++;

    }



    ## cleanup

    $dbh->disconnect( );

    my @r = ($id, scalar(@times), min(@times), max(@times), avg(@times), 

                         tot(@times));

    return @r;

};

The callback first establishes a connection to the server and prepares the query that will be executed. Next, it sets a few variables and then sets a dummy signal handler. It then sleeps, waiting for a SIGHUP. After the parent has started all the children, it signals them to start using SIGHUP.

After the signal has been handled, the main loop starts. In each iteration, it selects a random value to test, starts a timer, executes the query, and stops the timer. The resulting time is pushed to the @times list for later use. We finish the statement to dispose of any returned data and increment the loop counter.

After the loop completes, we disconnect from the server and return the time information back to the caller:

my @results = MyBench::fork_and_work($num_kids, $callback);

MyBench::compute_results('test', @results);



exit;



_ _END_ _

The fork_and_work( ) subroutine from the MyBench package is what gets everything rolling. The results are then passed to compute_results( ) and printed. The first argument passed is simply a name that will appear in the output to identify the results.

Here's a simple run, using a SELECT 1 query with 10 clients for 100,000 iterations:

$ ./bench_select_1 -n 10 -r 100000

forking: ++++++++++

sleeping for 2 seconds while kids get ready

waiting: ----------

test: 1000000 7.5e-05 0.65045 0.000561082981999975 561.082981999975 17822.6756483597

  clients : 10

  queries : 1000000

  fastest : 7.5e-05

  slowest : 0.65045

  average : 0.000561082981999975

  serial  : 561.082981999975

  q/sec   : 17822.6756483597

The first three lines are merely status updates so you can tell that the test is doing something while it runs. The test: line produces all the statistics on a single line, suitable for processing in another script or pasting into a spreadsheet. They're followed by human readable output.

There you can see how many clients were used, the total number of queries executed, and the response times (in seconds) of fastest and slowest queries as well as the average. The serial value explains approximately how many seconds the queries would have taken if executed serially. Finally, the q/sec number tells us how many queries per second (on average) the server handled during the test.

Because the code times only the query and not the work done by the Perl script, you can add arbitrarily complex logic to the main loop. Rather than generate a random number, maybe you need to read a value from a file or from another database table. Perhaps you need to run a few special queries every 785th iteration, to simulate the behavior of your real application. Doing so with MyBench would be easy; using super-smack would be more of a challenge.

    Previous Section  < Day Day Up >  Next Section