Mysql High Performance stress testing (Summary For A Long Time)

Mysql performance and stress tests are required based on business needs. here I will talk about the testing method and the testing process, as well as the problems with errors in the testing, it is estimated that many people want to test the performance pressure of mysql like me. I have found a lot of resources online.

Mysql performance and stress tests are required based on business needs. here I will talk about the testing method and the testing process, as well as the problems with errors in the testing, it is estimated that many people want to test the performance pressure of mysql like me. I have found a lot of resources online.

Mysql performance and stress tests are required based on business needs.

Here I will talk about the test method and process, as well as the problems with errors in the test.

It is estimated that many people want to test the performance pressure of mysql like me. I have found a lot of information on the Internet, but it is messy. So here I will make a summary and test it with my own Real Server. You can simulate how much data you want to test! Here we will only introduce the method.

MySQL starts from version 5.1.4 with a stress testing tool mysqlslap, which simulates multiple concurrent Client Access
Mysql to perform the test.

[Root @ localhost smacks] # mysql-Vmysql 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 (23) "-- query =" SELECT * FROM a "-- concurrency = 50 -- iterations = 200-uadmin-p123456-auto-generate-sql, -a automatically generates the test table and data-auto-generate-SQL-load-type = type test statement type. Optional values: read, key, write, update, and mixed (default ). -Number-char-cols = N,-x N: The number of columns in the test table that are automatically generated. The default value is 1-number-int-cols = N, -y N: the number of numeric columns in the automatically generated test table. The default value is 1-number-of-queries = N total number of test queries (number of concurrent customers × number of queries per customer) -query = name,-q: Use a custom script to execute the test. For example, you can call a custom stored procedure or SQL statement to execute the test. -Create-schema: the schema in MySQL, that is, the number of DML entries in database-commint = N, is submitted once-compress.-C if both the server and client support compression, then the compressed information is transmitted-concurrency = N,-c N concurrency, that is, to simulate how many clients execute select simultaneously. You can specify multiple values. Use a comma or the value specified by the-delimiter parameter as the delimiter-engine = engine_name, and-e engine_name to create the storage engine used by the test table, you can specify multiple-iterations = N,-I N test iterations-detach = N to execute N statements and then disconnect the reconnection-debug-info, -T print memory and CPU information-only-print: only test statements are printed instead of actually executed.

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

Test the myisam and innodb engines respectively.

BenchmarkRunning for engine myisamAverage number of seconds to run all queries: 0.638 secondsMinimum number of seconds to run all queries: 0.565 secondsMaximum number of seconds to run all queries: 0.924 secondsNumber of clients running queries: 50 Average number of queries per client: 20 BenchmarkRunning for engine myisamAverage number of seconds to run all queries: 0.691 secondsMinimum number of seconds to run all queries: 0.686 secondsMaximum number of seconds to run all queries: 0.696 secondsNumber of clients running queries: 100 Average number of queries per client: 10 BenchmarkRunning for engine innodbAverage number of seconds to run all queries: 13.189 secondsMinimum number of seconds to run all queries: 12.886 secondsMaximum number of seconds to run all queries: 13.337 secondsNumber of clients running queries: 50 Average number of queries per client: 20 BenchmarkRunning for engine innodbAverage number of seconds to run all queries: 12.449 secondsMinimum number of seconds to run all queries: 12.299 secondsMaximum number of seconds to run all queries: 12.592 secondsNumber of clients running queries: 100 Average number of queries per client: 10

This tool cannot specify the amount of data, so I later tested it with the super smack tool.

Download vegan.net/tony/supersmack/super-smack-1.3.tar.gz from the Internet.

Tar zxvf super-smack-1.3.tar.gzcd 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/

Make reports an error after compilation.

Important: -- with-mysql compilation configuration: libmysqlclient under the/usr/lib64 directory should be compiled. put so.16 in the/usr/lib directory. Otherwise, the following error occurs: configure: error: cocould not find libmysqlclient in '/usr/lib/mysql/usr/local/mysql/libconfigure: error: cocould not find mysql. h in '/usr/include/mysql/usr/local/mysql/include' because of my mysql it is self-compiled, so I am operating cd/usr/include; ln-s/usr/local/mysql/include/mysqlcd/usr/lib like this; ln-s/usr/local/mysql/lib/mysql

You also need to modify the configuration file.

Dictionary. h: In member function 'void Unique_dictionary: set_template (const char *) ': dictionary. h: 93: 28: error: 'strlen' was not declared in this scopesuper-smack.cc: In function 'void set_parse_file (char *) ': super-smack.cc: 65: 55: warning: deprecated conversion from string constant to 'Char * 'super-smack. cc: In function 'void set_db_type (const char *) ': super-smack.cc: 72: 48: warning: deprecated conversion from string constant to 'Char *' super-smack. cc: In function 'void print_engines () ': super-smack.cc: 126: 55: error: 'strlen' was not declared in this scopemake [2]: * ** [super-smack.o] Error 1 make [1]: *** [all-recursive] Error 1 make: *** [all-recursive-am] Error 2

You must modify the corresponding configuration file.

Modify the super-smack-1.3/src/query file before compilation. cc row 193rd: <int len = 0, num_recs = 0; changed to:> long len = 0; int num_recs = 0; row 199,200 <int str_len = (* I ). first. length (); <if (unsigned) p + str_len + 3 * sizeof (int) <(unsigned) p_end) changed to:> long str_len = (* I ). first. length ();> if (long) p + str_len + 3 * sizeof (int) <(long) p_end) 219th rows <len = (unsigned) p-(unsigned) buf; change to:> len = (long) p-(long) buf;

I have read the relevant tutorials for a long time!

Makemake install

Introduce the corresponding parameters:

Start with the default password file

Vi select-key.smack