MySQL系列之十五 MySQL常用配置和性能压力测试

一、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