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.
