MySQL 之配置优化
sunysh00 2020-06-01
1、连接请求的变量
1). max_connections MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当 然这建立在机器能支撑的情况下，因为如果连接数越多， MySQL会为每个连接提供连接缓冲区，就会开 销越多的内存，所以要适当调整该值，不能盲目提高设值。 数值过小会经常出现ERROR 1040: Too many connections错误，可以通过 mysql> show status like ‘connections‘; 通配符查看当前状态的连接数量(试 图连接到MySQL(不管是否连接成功)的连接数)，以定夺该值的大小。
mysql> show variables like ‘max_connections‘; # 查看最大连接数 +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.01 sec) mysql> show status like ‘max_used_connections‘; # 响应的连接数 +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 1 | +----------------------+-------+ 1 row in set (0.00 sec)
max_used_connections / max_connections * 100% （理想值≈ 85%）? 如果max_used_connections跟 max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。
#修改my.cnf配置文件 [ ~]# vim /etc/my.cnf .................... max_connections = 1024 #重启服务，并登录数据库查看 mysql> show variables like ‘max_connections‘; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1024 | +-----------------+-------+ 1 row in set (0.00 sec)
2).back_log MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，它就会起作用。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一 连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。 back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如 果期望在一个短时间内有很多连接，你需要增加它。 当观察你主机进程列表有许多的待连接进程时，就要加大back_log 的值了或加大max_connections的值。
mysql> show variables like ‘back_log‘; #查看back_log +---------------+-------+ | Variable_name | Value | +---------------+-------+ | back_log | 254 | +---------------+-------+ 1 row in set (0.01 sec) [ ~]# vim /etc/my.cnf .................... max_connections = 1024 back_log = 10 #重启服务并查看 mysql> show variables like ‘back_log‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | back_log | 10 | # 可以看到已经设置为10 +---------------+-------+ 1 row in set (0.00 sec)
3).wait_timeout和interactive_timeout wait_timeout 指的是MySQL在关闭一个非交互的连接之前所要等待 的秒数 interactive_time -- 指的是mysql在关闭一个交互的连接之前所要等待的秒数，比如我们在终端上 进入mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时 间就会自动断开。默认数值是28800，可调优为7200。
对性能的影响：
wait_timeout：
（1）如果设置 大小，那么连接关闭的很快，从而使一些持久的连接不起作用
（2）如果设置太大，容易造成连接打开 时间过长，在show processlist时，能看到太多的sleep状态的连接，从而造成too many connections错误
（3）一般希望wait_timeout尽可能地低 interactive_timeout的设置将要对你的web application没有多大 的影响 查看wait_timeout和interactive_timeout
[ ~]# vim /etc/my.cnf .................. wait_timeout = 100 interactive_timeout = 100 #重启服务并查看 mysql> show variables like ‘%interactive_timeout%‘; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | interactive_timeout | 100 | +---------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like ‘%wait_tmeout%‘; # 设置完毕后，当过100秒后进行使用会出现下方的字样 ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 3 Current database: *** NONE ***
2、缓冲区变量
全局缓冲：
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。 通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。
一共有6个索引读取请求，有3个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：? key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =50%? key_buffer_size只对MyISAM表起作用。 即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值 created_tmp_disk_tables得知详情。
[ ~]# vim /etc/my.cnf ....................... key_buffer_size = 268435456 # 两个选一个 key_buffer_size = 256M #重启并查看 mysql> show status like ‘key_read%‘; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Key_read_requests | 6 | | Key_reads | 3 | +-------------------+-------+ 2 rows in set (0.01 sec)
query_cache_size(查询缓存简称QC) 使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样 的SELECT语句（区分大小写），将直接从缓冲区中读取结果。 一个SQL查询如果以select开头，那么 MySQL服务器将尝试对其使用查询缓存。
注：两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两个SQL 将使用不同的一个CACHE。
mysql> show variables like ‘%query_cache%‘; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 6 rows in set (0.00 sec) #可以看出query_cache_type为off表示不缓存任何查询 #各字段解释： query_cache_limit：超过此大小的查 询将不缓存 query_cache_min_res_unit：缓存块的最小大小?（query_cache_min_res_unit的配置是一柄”双刃 剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪 费。 ） query_cache_size：查询缓存大小?(注：QC存储的最小单位是1024?byte，所以如果你设定了一个不是1024 的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。) query_cache_type：缓存类型，决 定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字
query_cache_type三个参数的含义:
query_cache_type=0（OFF）关闭
query_cache_type=1（ON）缓存所有结果，除非select语句使用SQL_NO_CACHE禁用查询缓存
query_cache_type=2(DEMAND)，只缓存select语句中通过SQL_CACHE指定需要缓存的查询
如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。 如果设置为1，将会缓存所有的结果，除 非你的select语句使用SQL_NO_CACHE禁用了查询缓存。 如果设置为2，则只缓存在select语句中通过 SQL_CACHE指定需要缓存的查询。 query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操 作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。?
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks 100%? 如果查询缓存碎片率超过20%，可以用 FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的 话。? 查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size 100%? 查询缓存利用 率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且 Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。 查询缓存命中率 = Qcache_hits/(Qcache_hits +Qcache_inserts) * 100% 。
Query Cache 的限制 ：
a) 所有子查询中的外部查询SQL 不能被Cache；
b) 在Procedure，Function 以及Trigger 中 的Query 不能被Cache；
c) 包含其他很多每次执行可能得到不一样结果的函数的Query不能被Cache。 鉴于上面 的这些限制，在使用Query Cache 的过程中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进 入Query Cache，仅仅让某些Query的查询结果被Cache。[ ~]# vim /etc/my.cnf .................... query_cache_size = 256M query_cache_type = 1 mysql> show variables like ‘%query_cache%‘; +------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 268435456 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+-----------+ 6 rows in set (0.00 sec)max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止 暴力破解密码的情况,当超过指定次数，MYSQL服务器将禁止host的连接请求，直到mysql服务器重启或 通过flush?hosts命令清空此host的相关信息。max_connect_errors的值与性能并无太大关系。
[ ~]# vim /etc/my.cnf .............. max_connect_errors=20 mysql> show variables like ‘%max_connect_error%‘; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 20 | +--------------------+-------+ 1 row in set (0.00 sec)
sort_buffer_size 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。 Sort_Buffer_Size?是一个connection级参数，在每个connection（session）第一次需要使用这个 buffer的时候，一次性分配设置的内存。 Sort_Buffer_Size?并不是越大越好，由于是connection级的参 数，过大的设置+高并发可能会耗尽系统内存资源。
[ ~]# vim /etc/my.cnf ................ sort_buffer_size = 2M mysql> show variables like ‘%sort_buffer_size%‘; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_sort_buffer_size | 1048576 | | myisam_sort_buffer_size | 8388608 | | sort_buffer_size | 2097152 | +-------------------------+---------+ 3 rows in set (0.00 sec)
max_allowed_packet?=?32M MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更 新会受 max_allowed_packet 参数限制，导致写入或者更新失败。最大值是1GB，必须设置1024的倍数。
join_buffer_size?=?2M??? 用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是 每个连接独享。
thread_cache_size?=?300??? 服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开 连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线 程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创 建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较?Connections?和?Threads_created?状态 的变量，可以看到这个变量的作用。设置规则如下：1GB?内存配置为8，2GB配置为16，3GB配置为32， 4GB或更高内存，可配置更大。服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前 提是缓存数未达上限)
Threads_cached?:代表当前此时此刻线程缓存中有多少空闲线程。 Threads_connected?:代表当前已建立连接的 数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。 Threads_created?:代表从最近一 次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程， 这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。 Threads_running?:代表当前激活的（非睡 眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
3、配置InnoDB的几个变量
innodb_buffer_pool_size 对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对 于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数 据库服务器，最大可以把该值设置成物理内存的80%。根据MySQL手册，对于2G内存的机器，推荐值是 1G（50%）。?如果你的数据量不大，并且不会暴增，那么无需把?innodb_buffer_pool_size?设置的太大 了。
[ ~]# vim /etc/my.cnf innodb_buffer_pool_size = 2048M mysql> show variables like ‘innodb_buffer_pool_size‘; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 2147483648 | +-------------------------+------------+ 1 row in set (0.00 sec)
innodb_flush_log_at_trx_commit 主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时 间点，取值分别为0、1、2三个。
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中 的数据写入日志文件并flush磁盘一次；
1，则在每秒钟或是每次事物的提交都会引起日志文件写入、 flush磁盘的操作，确保了事务的ACID；
2，每次事务提交引起写入日志文件的动作，但每秒钟完 成一次flush磁盘操作。 实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录 只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操 作合并成一个事务，这样可以大幅提高速度。 根据MySQL手册，在允许丢失最近部分事务的危险的前提 下，可以把该值设为0或2。
innodb_thread_concurrency?=?0??? 此参数用来设置innodb线程的并发数量，默认值为0表示不限制，若 要设置则与服务器的CPU核数相同或是cpu的核数的2倍，建议用默认设置，一般为8.
innodb_log_buffer_size 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能， 对于较大的事务，可以增大缓存大小。
innodb_log_file_size = 50M 此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.
innodb_log_files_in_group = 3 为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
read_buffer_size?=?1M?? MySql 读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区， MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太 慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分 配内存也是每个连接独享。
read_rnd_buffer_size?=?16M??? MySql 的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如， 按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁 盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该 缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。 注：顺序读是指根据索引的叶节点数据就 能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而 辅助索引和主键所在的数据段不同，因此访问方式是随机的。
bulk_insert_buffer_size?=?64M??? 批量插入数据缓存大小，可以有效提高插入效率，默认为8M
binary log log-bin=/usr/local/mysql/data/mysql-bin binlog_cache_size = 2M //为每个session 分配的内 存，在事务过程中用来存储二进制日志的缓存,提高记录bin-log的效率。没有什么大事务，dml也不是很 频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议 是--1M，后者建议是：即 2--4M max_binlog_cache_size = 8M //表示的是binlog 能够使用的最大cache 内存大小 max_binlog_size = 512M //指定binlog日志文件的大小，如果当前的日志大小达到 max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默 认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删 除。 expire_logs_days = 7 //定义了mysql清除过期日志的时间。 二进制日志自动删除的天数。默认值为 0,表示“没有自动删除”。 mysqladmin flush-logs 也可以重新开始新的binary log
log_queries_not_using_indexes 开启这个选项真实地记录了返回所有行的查询。
[ ~]#mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 -iterations=1 --create-schema=‘test1‘ --query=‘select * from test1.tb1‘ -engine=innodb --number-of-queries=2000 -uroot -p123456 –verbose 显示结果： Benchmark Running for engine innodb Average number of seconds to run all queries: 13.837 seconds Minimum number of seconds to run all queries: 13.837 seconds Maximum number of seconds to run all queries: 13.837 seconds Number of clients running queries: 10 Average number of queries per client: 200 #优化之后执行mysqlslap工具进行测试 [ ~]#mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 -iterations=1 --create-schema=‘test1‘ --query=‘select * from test1.tb1‘ -engine=innodb --number-of-queries=2000 -uroot -p123456 –verbose 显示结果： Benchmark Running for engine innodb Average number of seconds to run all queries: 4.199 seconds Minimum number of seconds to run all queries: 4.199 seconds Maximum number of seconds to run all queries: 4.199 seconds Number of clients running queries: 10 Average number of queries per client: 200
相关优化参数总结： [mysqld] slow_query_log = 1 slow_query_log_file = /usr/local/mysql/data/slow-query.log long_query_time = 1 log-queries-not-using-indexes max_connections = 1024 back_log = 128 wait_timeout = 60 interactive_timeout = 7200 key_buffer_size=256M query_cache_size = 256M query_cache_type=1 query_cache_limit=50M max_connect_errors=20 sort_buffer_size = 2M max_allowed_packet=32M join_buffer_size=2M thread_cache_size=200 innodb_buffer_pool_size = 2048M innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size=32M innodb_log_file_size=128M innodb_log_files_in_group=3 log-bin=mysql-bin binlog_cache_size=2M max_binlog_cache_size=8M max_binlog_size=512M expire_logs_days=7 read_buffer_size=1M read_rnd_buffer_size=16M bulk_insert_buffer_size=64M log-error = /usr/local/mysql/data/mysqld.err
