High performance pressure test for MySQL

According to the business needs, the MySQL performance and stress test should be tested.

Here I say the test method and the test procedure and the problem of the test error

I guess a lot of people like me. To test MySQL performance pressure, the internet to find a lot of information there are many, but very messy, so here I make a summary, take their own real server test. and want to test how much data can be simulated! The method is only described here.

MySQL begins with version 5.1.4 with a stress test tool mysqlslap, which simulates multiple concurrent client access

MySQL to perform the test.

[Root@localhost smacks]# Mysql-v

MySQL Ver 14.14 distrib 5.1.69, for Redhat-linux-gnu (x86_64) using ReadLine 5.1

Mysqlslap--delimiter= "--create=" Create TABLE A (b int); INSERT into a VALUES (n) "--query=" select * from a "--concurr Ency=50--iterations=200-uadmin-p123456

–auto-generate-sql,-
a automatically generates the types of test tables and data
–auto-generate-sql-load-type=type
test statements. Values include: Read,key,write,update and mixed (default).
–number-char-cols=n, x-n
automatically generated test table contains the number of characters in the column, default 1
–number-int-cols=n, y-n
automatically generated test table contains how many numeric types of columns, Default 1
–number-of-queries=n
total number of test queries (number of concurrent customers x per customer query)
–query=name,-q
Use custom scripts to perform tests. For example, you can invoke a custom stored procedure or SQL statement to perform a test.
–create-schema
Test schema,mysql schema is the database
–commint=n
How many DML after the submission
of –compress,-C
if both the server and client support are compressed, the compression information passes
–concurrency=n,-c-N
concurrency, which is how many clients execute the select at the same time. Multiple values can be specified, with a comma or a value specified by the –delimiter parameter as the delimiter
–engine=engine_name,-e engine_name to
create the storage engine used by the test table to specify multiple
- Iterations=n, i n the
number of iterations that the test executes
–detach=n
disconnect the –debug-info after executing N statements
-T
print memory and CPU information
– Only-print
only print test statements without actually executing

Automatically generate a test statement without you writing the SQL to test

For example, the result of this sentence is (test the time when concurrency is 50, and 100 for a total of 1000 visits)

The maximum, minimum, and average values are listed here. Are all the same values because only one test has been performed

Mysqlslap-a--concurrency=50,100--number-of-queries 1000--iterations=5--engine=myisam,innodb--debug-info-uadmin- p123456

Test the MyISAM and InnoDB engines separately

Benchmark Running for Engine MyISAM Average # of seconds to run all queries:0.638 seconds M Inimum number of seconds to run all queries:0.565 seconds Maximum number of seconds to run all queries:0.924 sec Onds number of clients running QUERIES:50 Average number of queries per client:20 Benchmark Runn ing for engine MyISAM Average number of seconds to run all queries:0.691 seconds Minimum number of second s to run all queries:0.686 seconds Maximum number of seconds to run all queries:0.696 seconds number of
        Clients running queries:100 Average number of queries per client:10 Benchmark running for engine InnoDB  Average number of seconds to run all queries:13.189 seconds Minimum number of seconds to run all queries: 12.886 seconds Maximum number of seconds to run all queries:13.337 seconds number of clients running que Ries:50 Average Number of queries per client:20 Benchmark Running for engine InnoDB Average # of seconds to run all queries:12.449 seconds Minimum number of seconds to run all queries:12.299 seconds Maximum number of SE Conds to run all queries:12.592 seconds number of clients running queries:100 Average number of queries Per Client:10

The tool cannot set its own number of data, so I got a super smack tool test later.

Going to download vegan.net/tony/supersmack/super-smack-1.3.tar.gz online

Tar zxvf super-smack-1.3.tar.gz

CD super-smack-1.3

./configure--prefix=/usr/local/super-smack-1.3--with-mysql=/usr/local/mysql/--with-mysql-lib=/usr/local/mysql/ lib/mysql/--with-mysql-include=/usr/local/mysql/include/mysql/