MySQL common configuration and Performance stress test: the MySQL series

First, MySQL common configuration

All of the following configuration parameters are based on a server with 32G of memory

1. Open a separate table space

innodb_file_per_table = 1

2, the MySQL service allows the maximum number of simultaneous sessions, the default is 151, often appear too many connections error, you need to increase this value

max_connections = 8000

3. The number of connections that the operating system can hold in the listening queue

back_log = 300

4, the maximum number of errors per client connection, when the number of times exceeded, the MySQL server will prohibit the connection request of this host until the MySQL server restarts or the flush hosts command to clear the information about this host

max_connect_errors = 1000

5. The number of tables opened by all threads

open_files_limit = 10240

6, each connection transfer data size, maximum 1G, must be a multiple of 1024, generally set to the maximum value of the Blob

max_allowed_packet = 32M

7. Maximum connection time for the request

wait_timeout = 10

8. The sort buffer is used to process the sort caused by the same order by and the group by queue

sort_buffer_size = 16M

9. Full table scan without index, minimum value of buffer used

join_buffer_size = 16M

10. Query buffer size

query_cache_size = 128M

11. Specify a buffer size that can be used by a single query, which defaults to 1M

query_cache_limit = 4M

12. Set the default transaction isolation level

transaction_isolation = REPEATABLE-READ

13. The heap size used by threads, this value limits the recursive depth of stored procedures that can be processed in memory and the complexity of SQL statements, and this capacity is reserved for each connection

thread_stack = 512K

14. Turn on the binary logging function

log_bin

15. Binary Log format: line-based

binlog_format = row

16, InnoDB use a buffer pool to save the index and raw data, you can set this variable to the server physical memory size of 80%

innodb_buffer_pool_size = 6G

17. The number of IO threads used to synchronize IO operations

innodb_file_io_threads = 4

18, the number of allowable threads within the InnoDB core, the recommended setting is twice times the number of CPUs plus the number of disks

innodb_thread_concurrency = 16

19. The size of the buffer used to buffer the log data

innodb_log_buffer_size = 16M

20. The size of each log file in the log group

innodb_log_file_size = 512M

21. The total number of files in the log group

innodb_log_files_in_group = 3

22. InnoDB the transaction waits for InnoDB row lock before the SQL statement is rolled back

innodb_lock_wait_timeout = 120

23, slow query record threshold length, default 10 seconds

long_query_time = 2

24, the query will not use the index is also recorded

log-queries-not-using-indexes

MY.CNF Example:

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksymbolic-links=0innodb_file_per_table = 1innodb_buffer_pool_size = 6442450944  #内存不足6G会报错innodb_file_io_threads = 4innodb_thread_concurrency = 16innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_lock_wait_timeout = 120log_bin = /var/lib/mysql/mariadb-binbinlog_format = rowslow_query_loglong_query_time = 2log-queries-not-using-indexestransaction_isolation = REPEATABLE-READquery_cache_size = 128Mquery_cache_limit = 4Mmax_connections = 8000back_log = 300max_connect_errors = 1000open_files_limit = 10240max_allowed_packet = 32Mwait_timeout = 10sort_buffer_size = 16Mjoin_buffer_size = 16Mthread_stack = 512K

Second, MySQL performance stress test common test tools:

• Mysqlslap
• Sysbench
• Tpcc-mysql
• MySQL Benchmark Suite
• MySQL Super-smack
• Mybench

Mysqlslap Tools Introduction

? Mysqlslap comes from the MARIADB package, the test process generates a MYSQLSLAP schema by default, generates a test table T1, queries and inserts test data, mysqlslap the library is automatically generated, and if it already exists, it is deleted first. Use--only-print to print the actual test process, leaving no traces in the database after the entire test is complete.

Common options:

• --auto-generate-sql,-a automatically generates test tables and data representing the SQL scripts generated by the Mysqlslap tool to test concurrency pressure
• --auto-generate-sql-load-type=type the type of the test statement. Represents whether the environment to be tested is a read or write operation or a mixture of the two. Values include: Read,key,write,update and mixed (default)
• --auto-generate-sql-add-auto-increment represents the automatic addition of the Auto_increment column to the generated table, starting with the 5.1.18 Release support
• --number-char-cols=n,-x N Auto-generated test table contains the number of character type columns, default 1
• --number-int-cols=n,-y N auto-generated test table contains how many number types of columns, default 1
• --number-of-queries=n total number of test queries (number of concurrent customers × number of queries per customer)
• --QUERY=NAME,-Q performs tests using custom scripts, such as the ability to invoke custom stored procedures or SQL statements to perform tests
• --create-schema represents the name of a custom test library, and the schema in the test schema,mysql is the database
• --commint=n how many DML to submit once
• --compress,-c if both the server and the client support compression, the information is compressed
• --concurrency=n,-c N represents concurrency, that is, how many clients execute a select at the same time, multiple values can be specified, and a comma or--delimiter parameter specifies the value as a delimiter
• --engine=engine_name,-e engine_name represents the engine to be tested, can have multiple, separated by separators
• --iterations=n,-I N the number of iterations of the test execution, which represents how many times to run the tests in different concurrency environments
• --only-print only test statements are printed and not actually executed
• --detach=n break the re-connection after executing N statements
• --debug-info,-t print memory and CPU related information

Test Example:

1) Single Thread test

[[email protected] ~]# mysqlslap -a -uroot -pEnter password: Benchmark        Average number of seconds to run all queries: 0.004 seconds        Minimum number of seconds to run all queries: 0.004 seconds        Maximum number of seconds to run all queries: 0.004 seconds        Number of clients running queries: 1        Average number of queries per client: 0

2) Multithreaded testing, using –concurrency to simulate concurrent connections

[[email protected] ~]# mysqlslap -uroot -p -a -c 500Enter password: Benchmark        Average number of seconds to run all queries: 3.384 seconds        Minimum number of seconds to run all queries: 3.384 seconds        Maximum number of seconds to run all queries: 3.384 seconds        Number of clients running queries: 500        Average number of queries per client: 0

3) Compare the performance of different storage engines at the same time

[[email protected] ~]# mysqlslap-uroot-p-a--concurrency=500--number-of-queries---iterations=5--engine= Myisam,innodb--debug-infoenter Password:benchmark Running for engine MyISAM Average number of seconds to R Un all queries:0.192 seconds Minimum number of seconds to run all queries:0.187 seconds Maximum number of Seconds to run all queries:0.202 seconds number of clients running queries:500 Average number of queries P        Er client:2benchmark Running for engine InnoDB Average number of seconds-run all queries:0.355 seconds Minimum number of seconds to run all queries:0.350 seconds Maximum number of seconds to run all queries:0. 364 seconds number of clients running queries:500 Average number of queries per Client:2user time 0.33, Sy Stem time 0.58Maximum resident set size 22892, Integral resident set size 0non-physical pagefaults 46012, physical Pagefau LTS 0, Swaps 0Blocks in 0 ouT 0, Messages in 0 out 0, signals 0Voluntary context switches 31896, involuntary context switches 0 

4) Perform a test, 500 and 1000 concurrently, and perform 5,000 total queries

[[email protected] ~]# mysqlslap-uroot-p-a--concurrency=500,1000--number-of-queries  Password:benchmark Average number of seconds to run all queries:3.378 seconds Minimum number of seconds to Run all queries:3.378 seconds Maximum number of seconds to run all queries:3.378 seconds number of client s running queries:500 Average number of queries per Client:10benchmark Average number of seconds to run Al L queries:3.101 seconds Minimum number of seconds to run all queries:3.101 seconds Maximum number of Secon DS to run all queries:3.101 seconds number of clients running queries:1000 Average number of queries per C Lient:5user time 0.84, System time 0.64Maximum resident set size 83068, Integral resident set size 0non-physical Pagefaul TS 139977, physical pagefaults 0, Swaps 0Blocks in 0 out 0, Messages in 0 out 0, signals 0Voluntary context switches 31524 , involuntary context SwitcheS 3 

5) Iterative Testing

[[email protected] ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 5000 --iterations=5 --debug-infoEnter password: Benchmark        Average number of seconds to run all queries: 3.307 seconds        Minimum number of seconds to run all queries: 3.184 seconds        Maximum number of seconds to run all queries: 3.421 seconds        Number of clients running queries: 500        Average number of queries per client: 10User time 2.18, System time 1.58Maximum resident set size 74872, Integral resident set size 0Non-physical pagefaults 327732, Physical pagefaults 0, Swaps 0Blocks in 0 out 0, Messages in 0 out 0, Signals 0Voluntary context switches 73904, Involuntary context switches 3

MySQL common configuration and Performance stress test: the MySQL series