(go) Stress test the database with the MySQL self-mysqlslap tool

http://aolens.blog.51cto.com/7021142/1901557-------Stress Testing the database with the 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

12345678910111213

[[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 Maximum number of seconds to run all queries: 0.002 seconds Number of clients running queries: 1 Average number of queries per 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

1234567

[[email protected]_aolens_01  /home/aolens " # mysqlslap --user=root --password =password --auto-generate-sql --concurrency=100 --number-of-queries=1000 benchmark average number of seconds to run all queries:  0.316 seconds minimum number of seconds to  Run all queries: 0.316 seconds maximum number of  seconds to run all queries: 0.316 seconds number of clients running queries: 100 average  Number of queries per client: 10

3. Use your own test library and test statements

1234567

[[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 secondsMaximum number of seconds to run all queries: 4.255 secondsNumber of clients running queries: 10Average number of queries 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

123456789101112131415161718192021

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 inset(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

12345678

MariaDB [(none)]> setglobal server_audit_file_rotate_size=1024*1024*1024;Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> setglobal server_audit_events=‘query,table‘;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> setglobal server_audit_file_rotate_now=on;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> setglobal 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:

1234567891011121314151617181920212223

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 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; \......"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

(go) Stress test the database with the MySQL self-mysqlslap tool