MySQL Stress test

1, use Mysqlslap for stress testing

Mysqlslap--defaults-file=/etc/my.cnf--concurrency=200--iterations=1--number-int-cols=1--auto-generate-sql-- Auto-generate-sql-load-type=write--engine=myisam,innodb--number-of-queries=200-s/home/mysql/mysql.sock-- Debug-info-uroot-p123

Let's take a look at some of the more important parameters:

--defaults-file, configuration file storage location
--create-schema, the Schema,mysql schema in the test is the database.
--concurrency, number of concurrent
--engines, test engine, can have multiple, separated by separators.
--iterations, number of iterations of the experiment
--socket,socket, file location
--debug-info, print memory and CPU information
--only-print, only test statements are printed and not actually executed

--auto-generate-sql, automatically generate test SQL
--auto-generate-sql-load-type, test the type of SQL. Type has mixed,update,write,key,read.
--number-of-queries, total number of SQL executed
--number-int-cols, the number of int columns in the table
--number-char-cols, number of char columns in the table
--query=name, use a custom script to perform tests, such as a custom stored procedure or SQL statement that can be called to perform the test.

To specify a test for the database:
--create-schema, specifying the database name
--query, specifying the SQL statement to navigate to a file that contains SQL

For example:

[Email protected] ~]#/usr/local/mysql/bin/mysqlslap--defaults-file=/etc/my.cnf--concurrency=50--iterations=1-- Create-schema=test--query=/root/test.sql-s/tmp/mysql.sock-uroot-p123

A small problem encountered during the concurrency test for MySQL, record it.

Concurrent access test with MYSQLSLAP, error on 1024 thread:

Bin/mysqlslap:error when connecting to server:1135 Can ' t ' Create a new thread (errno 11); If you aren't out of available memory, you can consult the manual for a possible os-dependent bug

The number of open files for the Linux system has been modified. Of course still error. ulimit-a command to view current system limitations

MAX User Processes = 1024

Use the Ulimit-u 10000 command to modify the current session limit and then restart MySQL for problem resolution. If you want to make this value permanent, you can configure it in/etc/profile.