MySQL vs MySQLSlap

In preparation for some more intense testing on the MySQL 5.1 Cluster, I started up a plain vanilla MySQL 5.1 database with MyISAM and INNODB enabled.

I am going to test out various configurations of the cluster ie. 2 data node, 4 data node, multiple SQL/API nodes using a new MySQL 5.1 load emulation tool called mysqlslap.

I read some decent entries on the net about other people who have used it as well, here and here.
You can capture your own sql either dumping the sql from your app, using tcpdump to capture the sql on the wire, or setting the general query log (requires restart) to capture all queries or patching mysql so the slow query log can go below the 1 sec threshold.

Anyway on with the show. This was not really a tuning exercise, I just used the sample medium my.cnf located in /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/support-files/my-medium.cnf, which frankly is probably too small.

Here are results for the plain standalone MySQL database on EC2. I have included the full help of mysqlslap as the MySQL documentation is missing detail about the auto-generate-sql options.

Have Fun

Paul



mysqlslap --help
mysqlslap Ver 0.9 Distrib 5.1.20-beta, 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 /etc/mysql/my.cnf ~/.my.cnf /usr/local/mysql/etc/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
-?, --help Display this help and exit.
-a, --auto-generate-sql
Generate SQL where not supplied by file or command line.
--auto-generate-sql-add-autoincrement
Add autoincrement to auto-generated tables.
--auto-generate-sql-execute-number=#
Set this number to generate a set number of queries to
run.

--auto-generate-sql-guid-primary
Add GUID based primary keys to auto-generated tables.
--auto-generate-sql-load-type=name
Load types are mixed, update, write, key, or read.
Default is mixed

--auto-generate-sql-secondary-indexes=#
Number of secondary indexes to add auto-generated tables.
--auto-generate-sql-unique-query-number=#
Number of unique queries auto tests
--auto-generate-sql-unique-write-number=#
Number of unique queries for
auto-generate-sql-write-number
--auto-generate-sql-write-number=#
Number of rows to insert to used in read and write loads
(default is 100).

-C, --compress Use compression in server/client protocol.
-c, --concurrency=name
Number of clients to simulate for query to run.
--create=name File or string to use create tables.
--create-schema=name
Schema to run tests in.
--csv[=name] Generate CSV output to named file or to stdout if no file
is named.
-#, --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.
-e, --engine=name Storage engine to use for creating the table.
-h, --host=name Connect to host.
-i, --iterations=# Number of times to run the tests.
-x, --number-char-cols=name
Number of VARCHAR columns to create table with if
specifying --auto-generate-sql
-y, --number-int-cols=name
Number of INT columns to create table with if specifying
--auto-generate-sql.
--number-of-queries=#
Limit each client to this number of queries (this is not
exact).
--only-print This causes mysqlslap to not connect to the databases,
but instead print out what it would have done instead.
-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.
--post-query=name Query to run or file containing query to run after
executing.
--pre-query=name Query to run or file containing query to run before
executing.
--preserve-schema Preserve the schema from the mysqlslap run, this happens
unless --auto-generate-sql or --create are used.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-q, --query=name Query to run or file containing query to run.
-s, --silent Run program in silent mode - no output.
-S, --socket=name Socket file to use for connection.
--ssl Enable SSL for connection (automatically enabled with
other flags). Disable with --skip-ssl.
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
-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.

Running tests, progressively increasing the number of queries

mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv \
--engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=100 --user=root \
--password

cat mysqlslap.csv

blackhole,mixed,0.021,0.009,0.069,1,100
blackhole,mixed,0.030,0.017,0.080,25,4
blackhole,mixed,0.044,0.025,0.087,50,2
blackhole,mixed,0.080,0.043,0.108,100,1
myisam,mixed,0.017,0.011,0.072,1,100
myisam,mixed,0.053,0.022,0.087,25,4
myisam,mixed,0.062,0.031,0.096,50,2
myisam,mixed,0.101,0.046,0.117,100,1
innodb,mixed,0.023,0.022,0.029,1,100
innodb,mixed,0.083,0.077,0.090,25,4
innodb,mixed,0.098,0.078,0.158,50,2
innodb,mixed,0.229,0.167,0.265,100,1


mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q1000.csv \
--engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 --user=root \
--password

cat /tmp/mysqlslap_q1000.csv

blackhole,mixed,0.161,0.089,0.209,1,1000
blackhole,mixed,0.167,0.154,0.215,25,40
blackhole,mixed,0.193,0.163,0.233,50,20
blackhole,mixed,0.249,0.183,0.284,100,10
myisam,mixed,0.215,0.172,0.237,1,1000
myisam,mixed,0.234,0.185,0.252,25,40
myisam,mixed,0.280,0.250,0.339,50,20
myisam,mixed,0.300,0.270,0.359,100,10
innodb,mixed,0.350,0.236,0.674,1,1000
innodb,mixed,0.425,0.375,0.655,25,40
innodb,mixed,0.424,0.355,0.499,50,20
innodb,mixed,0.640,0.587,0.668,100,10

mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q10000.csv \
--engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10000 --user=root \
--password

cat /tmp/mysqlslap_q10000.csv

blackhole,mixed,1.678,1.591,1.777,1,10000
blackhole,mixed,1.673,1.608,1.737,25,400
blackhole,mixed,1.739,1.675,1.899,50,200
blackhole,mixed,1.971,1.707,2.424,100,100
myisam,mixed,2.145,2.098,2.232,1,10000
myisam,mixed,2.185,2.056,2.464,25,400
myisam,mixed,2.179,2.060,2.319,50,200
myisam,mixed,2.370,2.151,3.008,100,100
innodb,mixed,3.541,3.077,3.989,1,10000
innodb,mixed,4.005,3.513,4.793,25,400
innodb,mixed,4.329,4.080,5.172,50,200
innodb,mixed,6.438,6.212,6.614,100,100


2 thoughts on “MySQL vs MySQLSlap

Comments are closed.