Stress test the database with MySQL self-mysqlslap tool

Mysqlslap is a tool that comes with MySQL and does not need to be installed separately:

Parameters:

-concurrency represents the number of concurrent, multiple can be separated by commas, concurrency=10,50,100, the number of concurrent connection threads are 10, 50, 100 concurrency respectively.

--engines represents the engine to be tested, can have multiple, separated by delimiters.

--iterations represents how many times to run these tests.

The--auto-generate-sql represents a SQL script generated by the system itself.

Whether the--auto-generate-sql-load-type represents a read or write or a mixture of the two (read,write,update,mixed)

--number-of-queries represents the total number of queries to run. The number of queries that each client runs can be calculated using the total number of queries/concurrency.

The--debug-info represents the additional output of CPU and memory related information.

--number-int-cols: Number of int fields to create test table

--auto-generate-sql-add-autoincrement: Represents the automatic addition of auto_increment columns to the generated table, starting with version 5.1.18

--number-char-cols the number of char fields that created the test table.

The schema of the--create-schema test Schema,mysql is database.

--query uses a custom script to perform tests, such as a custom stored procedure or SQL statement that can be invoked to perform the test.

--only-print If you just want to print and see what the SQL statement is, you can use this option.

1, simple usage

[Email protected]_aolens_01/home/aolens]# mysqlslap--user=root--password=password--auto-generate-sql Benchmark Average number of seconds to run all queries:0.002 seconds Minimum number of seconds to run all queries:0.002 seconds Ma Ximum number of seconds to run all queries:0.002 seconds number of clients running queries:1 Average number of queries P ER client:0

The meanings of the results:

• Average Number of ... Average number of seconds to run all statements

• Minimum Number of ... Minimum number of seconds to run all statements

• Maximum Number of ... Maximum number of seconds to run all statements

• Number of clients ... Number of clients

• Average number of queries per client average number of running queries

2, adding concurrency

[Email protected]_aolens_01/home/aolens]# mysqlslap--user=root--password=password--auto-generate-sql-- concurrency=100--number-of-queries=1000benchmarkaverage number of seconds to run all queries:0.316 secondsminimum Numbe  R of seconds to run all queries:0.316 secondsmaximum number of seconds to run all queries:0.316 secondsnumber of clients Running queries:100average number of queries per Client:10

3. Use your own test library and test statements

[Email protected]_aolens_01/home/aolens]# mysqlslap--user=root--password=password--concurrency=10-- number-of-queries=100--create-schema=wordpress--query= "select * from Wordpress.wp_posts;" Benchmarkaverage number of seconds to run all queries:4.255 secondsminimum number of seconds to run all queries:4.255 se Condsmaximum number of seconds to run all queries:4.255 Secondsnumber of clients running queries:10average number of que Ries per client:10

4, combined with the actual, the site home page requested database connection to do stress testing

Database mariadb 10.0.14

First, install the audit plugin for the database and enable

mariadb [(none)]> show variables like  '%audit% '-> ;+---------------------- ---------+-----------------------+| variable_name | value |+------------------------------ -+-----------------------+| server_audit_events | | |  server_audit_excl_users | | |  server_audit_file_path | server_audit.log | |  server_audit_file_rotate_now | off | |  server_audit_file_rotate_size | 1000000 | |  server_audit_file_rotations | 9 | |  server_audit_incl_users | | |  server_audit_logging | off | |  server_audit_mode | 0 | |  server_audit_output_type | file | |  server_audit_syslog_facility | log_user | |  server_audit_syslog_ident | mysql-server_auditing | |  server_audit_syslog_info | | |  server_audit_syslog_priority | log_info |+-------------------------------+-----------------------+14 rows in set  (0.00  SEC)

Found already installed, no installed mariadb [(none)]> install PLUGIN server_audit SONAME ' server_audit.so ';

command line to start the audit function:

Command line enable audit, fail after reboot

MariaDB [(None)]> set global server_audit_file_rotate_size=1024*1024*1024; Query OK, 0 rows affected (0.01 sec) MariaDB [(None)]> set global server_audit_events= ' query,table '; Query OK, 0 rows Affected (0.00 sec) MariaDB [(None)]> set global server_audit_file_rotate_now=on; Query OK, 0 rows Affected (0.00 sec) MariaDB [(None)]> set global server_audit_logging= ' on '; Query OK, 0 rows Affected (0.00 sec)

Refresh the homepage to see what SQL operations are in the audit log, and to test these sql:

mysqlslap --user=root --password=password --concurrency=20 --number-of-queries=1000 - -create-schema=wordpress --query= " select option_name, option_value from wp_ options where autoload =  ' yes ';  select option_value from wp_options  WHERE option_name =  ' A3_lz_google_api_key '  limit 1; select option_ value from wp_options where option_name =  ' a3_lz_google_api_key_enable '   limit 1; select option_value from wp_options where option_name =  ' _transient_timeout_a3_lz_google_api_key_status '  limit 1; select option_value from  wp_options WHERE option_name =  ' _transient_a3_lz_google_api_key_status '  limit  1; SELECT option_value FROM wp_options WHERE option_name =  ' Wordpress_api_key '  LIMIT&NBsp;1; select option_value from wp_options where option_name =  ' Onp_ License_clipboard-images '  limit 1; select autoload from wp_options where  option_name =  ' onp_license_clipboard-images ';  select option_value from wp_ options where option_name =  ' Onp_version_check_clipboard-images '  LIMIT 1;  select option_value from wp_options where option_name =  ' Ossdl_https '   limit 1; select option_value from wp_options where option_name =  ' Uninstall_plugins '  limit 1; select option_value from wp_options where  option_name =  ' a3_lazy_load_just_installed '  limit 1; select option_value  FROM wp_options WHERE option_name =  ' Akismet_comment_nonce '  LIMIT 1;  select option_value from wp_options where option_name =  ' Preload_cache_counter '  LIMIT  1; SELECT option_value FROM wp_options WHERE option_name =  ' Rewrite_rules '  limit 1;&nbsp, ... "benchmarkaverage number of seconds to  Run all queries: 40.931 secondsminimum number of seconds to run  all queries: 40.931 secondsmaximum number of seconds to run  all queries: 40.931 secondsNumber of clients running queries:  20average number of queries per client: 50

This article is from the "Aolens blocks until those" blog, be sure to keep this source http://aolens.blog.51cto.com/7021142/1901557

Stress test the database with MySQL self-mysqlslap tool