As of MySQL 5.1.4, there is a supported benchmarking tool called mysqlslap. Not sure where they got the name from, is there going to be a mysqltickle next?

create database mysqlslap
use mysqlslap
use schema.sql

mysqlslap –skip-create-schema

–skip-create-schema
mysqlslap: No user supplied data to insert or –auto-generate-sql specified!

trashes schema

I’ll be using the MySQL Sakila Sample Database.

[arabx@web1 sql]$ mysqlslap -P3307 -hlocalhost.localdomain -uroot –auto-generate-sql Seconds to load data: 0.08929
Number of clients loading data: 1
Number of inserts per client: 0
Seconds to run all queries: 0.23113
Number of clients running queries: 1
Number of queries per client: 1000

mysql -P3307 -hweb1.onegreendog.com -usakila -psakila sakila

mysqlslap -P3307 -hlocalhost.localdomain -uroot –create=schema.sql
mysqlslap: No user supplied data to insert or –auto-generate-sql specified!
[arabx@web1 sql]$ mysql -P3307 -hlocalhost.localdomain -uroot mysqlslap

Syntax

$ mysqlslap -?
mysqlslap  Ver 0.1 Distrib 5.1.4-alpha, for pc-linux-gnu (i686)
Copyright (C) 2005 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL        license

Run a query multiple times against the server

Usage: mysqlslap [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf
The following groups are read: mysqlslap client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit
--no-defaults           Don't read default options from any options file
--defaults-file=#       Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
  -a, --auto-generate-sql
                      Generate SQL where not supplied by file or command line.
  -C, --compress      Use compression in server/client protocol.
  -l, --concurrency-load=#
                      Number of clients to use when loading data.
  -c, --concurrency=# Number of clients to simulate for query to run.
  --create=name       File or string to use for create.
  -d, --data=name     File or string with INSERT to use for populating data.
  -#, --debug[=name]  Output debug log. Often this is 'd:t:o,filename'.
  -F, --delimiter=name
                      Delimiter to use in SQL statements supplied in file or
                      command line.
  -D, --drop-schema   Drop schema if it exists prior to running and after
                      running.
  -e, --engine=name   Storage engine to use for creating the table.
  -?, --help          Display this help and exit.
  -h, --host=name     Connect to host.
  -i, --iterations=#  Number of iterations.
  -x, --number-char-cols=#
                      Number of INT columns to create table with if specifying
                      --sql-generate-sql.
  -y, --number-int-cols=#
                      Number of VARCHAR columns to create table with if
                      specifying --sql-generate-sql.
  -n, --number-rows=# Number of rows to insert when loading data.
  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -P, --port=#        Port number to use for connection.
  --protocol=name     The protocol of connection (tcp,socket,pipe,memory).
  -A, --skip-create-schema
                      Don't create a schema, use existing schema.
  -L, --skip-data-load
                      Don't load any data, use existing data set.
  -Q, --skip-query    Don't run any queries.
  -s, --silent        Run program in silent mode - no output.
  -q, --query=name    Query to run or file containing query to run.
  -S, --socket=name   Socket file to use for connection.
  -u, --user=name     User for login if not current user.
  -v, --verbose       More verbose output; You can use this multiple times to
                      get even more verbose output.
  -V, --version       Output version information and exit.