MySQL series 15 MySQL common configuration and performance stress test

1. Common MySQL configuration

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

1. Open an independent tablespace

innodb_file_per_table = 1

2. The upper limit of the number of simultaneous sessions allowed by the MySQL service is 151 by default. If the error message "Too Many Connections" appears frequently, you need to increase this value.

max_connections = 8000

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

back_log = 300

4. The maximum number of errors allowed for each client connection. When this number is exceeded, the MYSQL server will prohibit the connection request of this host until the MYSQL server is restarted or the relevant information of this host is cleared through the flush hosts command

max_connect_errors = 1000

5. The number of tables opened by all threads

open_files_limit = 10240

6. The maximum data size transmitted by each connection is 1G, which must be a multiple of 1024. It is generally set to the value of the largest BLOB.

max_allowed_packet = 32M

7. Maximum connection time for requests

wait_timeout = 10

8. The sort buffer is used to handle sorting caused by ORDER BY and GROUP BY queues

sort_buffer_size = 16M

9. Minimum buffer size for full table scan without index

join_buffer_size = 16M

10. Query buffer size

query_cache_size = 128M

11. Specify the buffer size that can be used by a single query. The default is 1M

query_cache_limit = 4M

12. Set the default transaction isolation level

transaction_isolation = REPEATABLE-READ

13. The heap size used by the thread. This value limits the recursive depth of the stored procedure and the complexity of the SQL statement that can be processed in the memory. This amount of memory is reserved for each connection.

thread_stack = 512K

14. Enable binary logging

log_bin

15. Binary log format: row-based

binlog_format = row

16. InnoDB uses a buffer pool to store indexes and raw data. You can set this variable to 80% of the server's physical memory size.

innodb_buffer_pool_size = 6G

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

innodb_file_io_threads = 4

18. The recommended setting for the number of threads allowed within the Innodb core is twice the number of CPUs plus the number of disks.

innodb_thread_concurrency = 16

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

innodb_log_buffer_size = 16M

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

innodb_log_file_size = 512M

21. Total number of files in the log group

innodb_log_files_in_group = 3

22. The time that an InnoDB transaction waits for an InnoDB row lock before a SQL statement is rolled back

innodb_lock_wait_timeout = 120

23. The threshold duration of slow query records is 10 seconds by default

long_query_time = 2

24. Record queries that do not use indexes

log-queries-not-using-indexes

my.cnf example:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_file_per_table = 1
innodb_buffer_pool_size = 6442450944 #If the memory is insufficient, an error will be reported innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 120
log_bin = /var/lib/mysql/mariadb-bin
binlog_format = row
slow_query_log
long_query_time = 2
log-queries-not-using-indexes
transaction_isolation = REPEATABLE-READ
query_cache_size = 128M
query_cache_limit = 4M
max_connections = 8000
back_log = 300
max_connect_errors = 1000
open_files_limit = 10240
max_allowed_packet = 32M
wait_timeout = 10
sort_buffer_size = 16M
join_buffer_size = 16M
thread_stack = 512K

2. MySQL performance stress test

Common testing tools:

• mysqlslap
• Sysbench
• tpcc-mysql
• MySQL Benchmark Suite
• MySQL super-smack
• MyBench

mysqlslap tool introduction

​mysqlslap comes from the mariadb package. During the test, a mysqlslap schema is generated by default, a test table t1 is generated, test data is queried and inserted, and the mysqlslap library is automatically generated. If it already exists, it is deleted first. Use --only-print to print the actual test process, and no trace will be left in the database after the entire test is completed.

Common options:

• --auto-generate-sql, -a Automatically generate test tables and data, indicating that the SQL script generated by the mysqlslap tool is used to test concurrent pressure
• --auto-generate-sql-load-type=type Type of test statement. Indicates whether the environment to be tested is a read operation, a write operation, or a mixture of the two. The values ​​include: read, key, write, update and mixed (default)
• --auto-generate-sql-add-auto-increment means automatically adding auto_increment columns to the generated tables. This function is supported since version 5.1.18.
• --number-char-cols=N, -x N The number of character columns in the automatically generated test table, default 1
• --number-int-cols=N, -y N The number of numeric columns in the automatically generated test table, default 1
• --number-of-queries=N Total number of test queries (number of concurrent clients × number of queries per client)
• --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 represents the custom test library name, the test schema, and the schema in MySQL is the database
• --commint=N Commit after how many DMLs
• --compress, -C Compress messages if both server and client support compression
• --concurrency=N, -c N indicates the concurrency, that is, how many clients are simulated to execute select at the same time; multiple values ​​can be specified, with commas or the values ​​specified by the --delimiter parameter as delimiters
• --engine=engine_name, -e engine_name represents the engine to be tested. There can be multiple engines, separated by delimiters.
• --iterations=N, -i N The number of iterations of the test execution, which represents how many times to run the test in different concurrent environments.
• --only-print only print test statements without actually executing them
• --detach=N Disconnect and reconnect after executing N statements
• --debug-info, -T Print memory and CPU related information

Test example:

1) Single-threaded test

[root@centos7 ~]# mysqlslap -a -uroot -p
Enter 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) Multi-threaded testing, use –concurrency to simulate concurrent connections

[root@centos7 ~]# mysqlslap -uroot -p -a -c 500
Enter 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) Test the performance of different storage engines at the same time for comparison

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info
Enter password: 
Benchmark
        Running for engine myisam
        Average number of seconds to run 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 per client: 2

Benchmark
        Running for engine innodb
        Average number of seconds to 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: 2


User time 0.33, System time 0.58
Maximum resident set size 22892, Integral resident set size 0
Non-physical pagefaults 46012, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31896, Involuntary context switches 0

4) Run a test with 500 and 1000 concurrent queries, and 5000 total queries

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500,1000 --number-of-queries 5000 --debug-info
Enter 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 clients running queries: 500
        Average number of queries per client: 10

Benchmark
        Average number of seconds to run all queries: 3.101 seconds
        Minimum number of seconds to run all queries: 3.101 seconds
        Maximum number of seconds to run all queries: 3.101 seconds
        Number of clients running queries: 1000
        Average number of queries per client: 5


User time 0.84, System time 0.64
Maximum resident set size 83068, Integral resident set size 0
Non-physical pagefaults 139977, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31524, Involuntary context switches 3

5) Iterative testing

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 5000 --iterations=5 --debug-info
Enter 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: 10


User time 2.18, System time 1.58
Maximum resident set size 74872, Integral resident set size 0
Non-physical pagefaults 327732, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 73904, Involuntary context switches 3

The above is the detailed content of MySQL series 15: Common MySQL configuration and performance stress testing. For more information about common MySQL configuration and performance stress testing, please pay attention to other related articles on 123WORDPRESS.COM!