一、mysql常用配置
以下所有配置参数以32g内存的服务器为基
1、打开独立的表空间
innodb_file_per_table = 1
2、mysql服务所允许的同时会话数的上限，默认为151，经常出现too many connections的错误提示，则需要增大此值
max_connections = 8000
3、操作系统在监听队列中所能保持的连接数
back_log = 300
4、每个客户端连接最大的错误允许数量，当超过该次数，mysql服务器将禁止此主机的连接请求，直到mysql服务器重启或通过flush hosts命令清空此主机的相关信息
max_connect_errors = 1000
5、所有线程所打开表的数量
open_files_limit = 10240
6、每个连接传输数据大小，最大1g，须是1024的倍数，一般设为最大的blob的值
max_allowed_packet = 32m
7、请求的最大连接时间
wait_timeout = 10
8、排序缓冲被用来处理类似order by以及group by队列所引起的排序
sort_buffer_size = 16m
9、不带索引的全表扫描，使用的buffer的最小值
join_buffer_size = 16m
10、查询缓冲大小
query_cache_size = 128m
11、指定单个查询能够使用的缓冲区大小，默认为1m
query_cache_limit = 4m
12、设定默认的事务隔离级别
transaction_isolation = repeatable-read
13、 线程使用的堆大小，此值限制内存中能处理的存储过程的递归深度和sql语句复杂性，此容量的内存在每次连接时被预留
thread_stack = 512k
14、开启二进制日志功能
log_bin
15、二进制日志格式：基于行
binlog_format = row
16、innodb使用一个缓冲池来保存索引和原始数据, 可设置这个变量到服务器物理内存大小的80%
innodb_buffer_pool_size = 6g
17、用来同步io操作的io线程的数量
innodb_file_io_threads = 4
18、在innodb核心内的允许线程数量，建议的设置是cpu数量加上磁盘数量的两倍
innodb_thread_concurrency = 16
19、用来缓冲日志数据的缓冲区的大小
innodb_log_buffer_size = 16m
20、在日志组中每个日志文件的大小
innodb_log_file_size = 512m
21、在日志组中的文件总数
innodb_log_files_in_group = 3
22、sql语句在被回滚前,innodb事务等待innodb行锁的时间
innodb_lock_wait_timeout = 120
23、慢查询记录的阈值时长，默认10秒
long_query_time = 2
24、将没有使用索引的查询也记录下来
log-queries-not-using-indexes
my.cnf示例：
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 innodb_file_per_table = 1 innodb_buffer_pool_size = 6442450944 #内存不足6g会报错 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
二、mysql的性能压力测试
常见测试工具：
- mysqlslap
- sysbench
- tpcc-mysql
- mysql benchmark suite
- mysql super-smack
- mybench
mysqlslap工具介绍
mysqlslap来自于mariadb包，测试的过程默认生成一个mysqlslap的schema,生成测试表t1，查询和插入测试数据，mysqlslap库自动生成，如果已经存在则先删除。用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。
常用选项：
- --auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的sql脚本来测试并发压力
- --auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)
- --auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持
- --number-char-cols=n, -x n 自动生成的测试表中包含多少个字符类型的列，默认1
- --number-int-cols=n, -y n 自动生成的测试表中包含多少个数字类型的列，默认1
- --number-of-queries=n 总的测试查询次数(并发客户数×每客户查询次数)
- --query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的存储过程或者sql语句来执行测试
- --create-schema 代表自定义的测试库名称，测试的schema，mysql中schema也就是database
- --commint=n 多少条dml后提交一次
- --compress, -c 如服务器和客户端都支持压缩，则压缩信息
- --concurrency=n, -c n 表示并发量，即模拟多少个客户端同时执行select；可指定多个值，以逗号或者--delimiter参数指定值做为分隔符
- --engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开
- --iterations=n, -i n 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
- --only-print 只打印测试语句而不实际执行
- --detach=n 执行n条语句后断开重连
- --debug-info, -t 打印内存和cpu的相关信息
测试示例：
1）单线程测试
[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）多线程测试，使用–concurrency来模拟并发连接
[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）同时测试不同的存储引擎的性能进行对比
[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）执行一次测试，分别500和1000个并发，执行5000次总查询
[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）迭代测试
[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
以上就是mysql系列之十五 mysql常用配置和性能压力测试的详细内容，更多关于mysql常用配置和性能压力测试的资料请关注其它相关文章！
