mysql 性能参数调优详解

1 优化连接池

连接池运行机制
MySQL连接器中的连接池，用以提高数据库密集型应用程序的性能和可扩展性，默认启用。MySQL连接器负责管理连接池中的多个连接，自动创建、打开、关闭和破坏连接，多个连接的创建，可满足多客户端的频繁连接，连接的重复使用获得最佳性能。
MySQL连接器
每三分钟运行一次后台作业，并从池中删除闲置（未使用）超过三分钟的连接。池清理释放客户端和服务器端的资源。这是因为在客户端每个连接都使用一个Socket，而在服务器端每个连接都使用一个Socket和一个线程。

max_connections，MySQL最大并发连接数，默认值是151，最大连接数上限是16384；

经验：实际连接数是最大连接数的 85% 较为合适。 设置 max_used_connections 方法

• 查询数据库目前设置的最大并发连接数是多少

SHOW VARIABLES LIKE ‘max_connections’;

• 查询数据库目前实际连接的并发数是多少
  SHOW STATUS LIKE ‘max_used_connections’;

• 在MySQL配置文件 /etc/my.cnf 中设置 max_connections=3000，表示修改最大连接数为3000。

注意：需要重启 MySQL 才能生效。 – MySQL为每个连接创建缓冲区，所以不应该盲目上调最大连接数。

如果最大连接数达到了上面设置的 3000，会消耗大约 800M 内存。

其他连接池设置：
开启连接池： Pooling=true，默认开启
复用时重置连接状态： ConnectionReset=True
保持连接设置： CacheServerProperties=True
连接超时回收(秒)： ConnectionLifeTime=300
支持的最大连接数量： Max Pool Size=100
保持最小的连接数量： Min Pool Size=10

2. 优化请求堆栈

back_log，存放执行请求的堆栈大小，默认值是50。

• 该值设置为最大并发连接数的 20%～30% 较为合适。
  设置 back_log 方法：
• 在MySQL配置文件 /etc/my.cnf 中，设置 back_log=600
• 修改后需要重启 MySQL 才能生效。

back_log 在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
也就是说，如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
将会报：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL |
login | NULL 的待连接进程时. back_log值不能超过TCP/IP连接的侦听队列的大小。
若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令：cat
/proc/sys/net/ipv4/tcp_max_syn_backlog，目前系统为1024。
对于Linux系统推荐设置为大于512的整数。 修改系统内核参数，可以编辑/etc/sysctl.conf去调整它。
如：net.ipv4.tcp_max_syn_backlog = 2048，改完后执行sysctl -p 让修改立即生效。 查看mysql
当前系统默认back_log值，命令：
show variables like ‘back_log’;

3. 修改连接超时时间

wait-timeout，超时时间，单位是秒，连接默认超时为8小时，连接长期不用不销毁，比较浪费资源。
查看

SHOW VARIABLES LIKE 'wait_timeout%';

经验：设置超时时间为 10 分钟 wait-timeout=600

4. 优化内存缓冲池

缓冲池运行机制

• 在MySQL5.5之前，广泛使用的和默认的存储引擎是MyISAM。MyISAM使用操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存，所以非常需要有足够的InnoDB buffer pool空间。
• 缓冲区分为 热数据区 / 冷数据区，两者空间占比约为 7/3，每区中的数据集依使用频率按顺序依次排列。当一个新的查询结果出现后，首先考虑存放到冷数据区，当冷数据区的结果集使用达到一定频率，会被改存到热数据区，使用频率最好的数据集会被存放到热区的首位，当然也有热区转到冷区的状况。

InnoDB 缓冲池不仅仅是一个缓存，MySQL InnoDB buffer pool 包含四部分：
1. 数据缓存，InnoDB 数据页面；
2. 索引缓存，索引数据；
3. 缓冲数据，脏页（在内存中修改尚未写入到磁盘的数据）；
4. 内部结构，如自适应哈希索引，行锁等。

innodb_buffer_pool_instances 内存缓冲池。

• buffer_pool 把需要缓冲的数据 hash 到不同的缓冲池中，这样可以并行的内存读写。通过减少争用不同线程对缓存页面进行读写的争用，将缓冲池划分为多个单独的实例可以提高并发性。

• MySQL 5.7、MySQL 8.0 下 innodb_buffer_pool_instances 默认为 1，若 MySQL 存在高并发和高负载访问，设置为 1 则会造成大量线程对 buffer_pool 的单实例互斥锁竞争，这样会消耗一定量的性能的。

• innodb_buffer_pool_instances 建议设置为 cpu核心数。
  innodb_buffer_pool_chunk_size，缓冲池每块大小，默认128M。

• pool_chunk_size 一般不做改动，使用默认值就可以。
  innodb_buffer_pool_size，缓冲池的承载总量。

• innodb_buffer_pool_size 可以缓存索引和行数据，值越大、IO读写就越少；

设置规则：innodb_buffer_pool_size = （innodb_buffer_pool_chunk_size * {N}块 ）*innodb_buffer_pool_instances

如果单纯的做数据库服务，该参数可以设置到电脑物理内存的80%；
为了更好的配合 pool_instance，pool_size 需要设置为 pool_instance 和 pool_chunk_size 的整数倍，这样可以被 pool_instance 整除，为每个 buffer pool 实例平均分配内存。如果设置的值不是倍数，MySQL会自动将 pool_size 调整为 pool_chunk_size 的倍数。

5. 优化并发线程数

innodb_thread_concurrency，代表并发线程数。
默认是0，表示没有设置线程数量的上限。不是分配给 MySQL 的线程越多越好，线程多反而会损耗cpu性能，导致速度变慢。

经验：并发线程数应该设置为 cpu 核心数的两倍。

注意：这个变量特定于Solaris 8和更早的系统，MySQL 5.7.2中删除了这个变量。

设置 innodb_thread_concurrency 方法：

在MySQL配置文件 /etc/my.cnf 中，设置 innodb_thread_concurrency=8。

– 查看cpu型号
cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
– 查看cpu核心数
cat /proc/cpuinfo | grep “cores”|uniq

6. 优化线程池

客户端发起连接到 MySQL Server 后，MySQL Server监听进程监听到新的请求，然后 Sever 会为其分配一个新的 thread去处理此请求。
从建立连接之开始，CPU要给它划分一定的 thread stack，然后进行用户身份认证，建立上下文信息，最后请求完成，关闭连接，同时释放资源。
在高并发的情况下，这个过程将给系统带来巨大的压力，不能保证性能。MySQL服务器的线程数需要在一个合理的范围之内，这样才能保证MySQL服务器健康平稳地运行。

6.1 查看线程池的状态：

mysql> show variables like ‘thread%’;
±-------------------±--------------------------+
| Variable_name | Value |
±-------------------±--------------------------+
| thread_cache_size | 64 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
±-------------------±--------------------------+

thread_cache_size

thread_cache_size，Threads_cached 中存放的最大连接线程数。

• 在短连接的应用中，Threads_cached 的功效非常明显，因为在应用中数据库的连接和创建是非常频繁的。如果不使用 Threads_cached，那么消耗的资源是非常频繁的。
• 在长连接中虽然带来的改善没有短连接的那么明显，但是好处是显而易见的。但并不是越大越好，大了反而浪费资源，这个的确定一般认为和物理内存有一定关系。
• Mysql默认值为9。
  设置 thread_cache_size 方法：
• 参考下面额对照表，根据物理内存设置对应的 thread_cache_size 数值：

1G —> 8
2G —> 16
3G —> 32
3G —> 64

6.2 在 mysql 命令行中设置：

mysql> set global thread_cache_size=64；

thread_concurrency

• thread_concurrency 应设为 CPU核数的2倍。
  比如有一个双核的CPU，那么thread_concurrency的应该为4。这个变量是针对Solaris系统的，如果设置这个变量的话，mysqld就会调用thr_setconcurrency()。这个函数使应用程序给同一时间运行的线程系统提供期望的线程数目。但是在5.7以后就已经抛弃了。
  设置 thread_concurrency 方法：
  – 在 mysql 命令行中设置：

mysql> set global thread_concurrency=4；

thread_handling

运用 Thread_Cache 处理连接的方式，从 5.1.19 添加的新特性，有两个值可选 no-threads、one-thread-per-connection。

• no-threads ：服务器使用一个线程
• one-thread-per-connection ：服务器为每个客户端请求使用一个线程

thread_stack

每个连接被创建的时候，mysql分配给它的内存。这个值一般认为默认就可以应用于大部分场景了，除非必要非则不要动它。上面表示是256kb。

6.2 查看线程使用情况：

mysql> show global status like ‘Thread%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| Threads_cached | 41 |
| Threads_connected | 53 |
| Threads_created | 541 |
| Threads_running | 4 |
±------------------±------+

Threads_cached

MySQL里面为了提高客户端请求创建连接过程的性能，提供了一个连接池也就是 Thread_cache 池（大小是thread_cache_size），将空闲的连接线程放在连接池中，而不是立即销毁。
这样的好处就是，当又有一个新的请求的时候，mysql不会立即去创建连接 线程，而是先去 Thread_Cache 中去查找空闲的连接线程，如果存在则直接使用，不存在才创建新的连接线程。Thread_cache 值表示已经被线程缓存池缓存的线程个数。

Threads_connected

当前处于连接状态的线程个数，等于 show processlist。

Threads_created

Threads_created 表示创建过的线程数，如果发现 Threads_created 值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中 thread_cache_size 值。

Threads_running

处于激活状态的线程的个数，这个一般都是远小于Threads_connected的。

7.优化日志

日志运行机制
MySQL在运行时，会有各种不同日志的记录，大量的各种类型的日志产生，会对资源的开销产生严重的影响，必要的时候我们选择性的开启。
但在生产环境时，有些日志并不是必须，以下列出MySQL各种日志信息：

• 错误日志：启动、关闭、运行时 产生的异常记录，建议开启，设置 log_error
  查询日志：客户端连接和执行的脚本，建议关闭，设置 general_log
• 慢查询日志：记录超时的查询，记录不适用索引的查询等，建议关闭，设置 slow_query_log
• 二进制日志：用于数据同步复制，需发送的数据日志，多用于集群，如需开启，设置 log_bin
• 中继日志：用于数据同步复制时，接收到的数据日志，多用于集群，如需开启，设置 relay_log

8. 锁优化

8.1. innodb 锁优化

Innodb 存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM 的表级锁定的。

尽可能让所有的数据检索都通过索引来完成，从而避免Innodb 因为无法通过索引键加锁而升级为表级锁定；
合理设计索引，让Innodb 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query 的执行；
尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL 因为实现事务隔离级别所带来的附加成本；
减少 innodb 死锁产生概率的建议：
类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

9.2. MyISAM 锁优化

在MyISAM里读写操作是串行的，但当对同一个表进行查询和插入操作时，为了降低锁竞争的频率，根据concurrent_insert的设置，MyISAM是可以并行处理查询和插入的

缩短锁定时间

• 尽两减少大的复杂Query，将复杂Query 分拆成几个小的Query 分布进行；

• 尽可能的建立足够高效的索引，让数据检索更迅速；

• 尽量让MyISAM 存储引擎的表只存放必要的信息，控制字段类型；

• 利用合适的机会优化MyISAM 表数据文件；

• max_write_lock_count：
  缺省情况下，写操作的优先级要高于读操作的优先级，即便是先发送的读请求，后发送的写请求，此时也会优先处理写请求，然后再处理读请求。这就造成一 个问题：一旦我发出若干个写请求，就会堵塞所有的读请求，直到写请求全都处理完，才有机会处理读请求。此时可以考虑使用 max_write_lock_count：
  max_write_lock_count=1
  有了这样的设置，当系统处理一个写操作后，就会暂停写操作，给读操作执行的机会。
  low-priority-updates：
  我们还可以更干脆点，直接降低写操作的优先级，给读操作更高的优先级。
  low-priority-updates=1
  综合来看，concurrent_insert=2是绝对推荐的，至于max_write_lock_count=1和low-priority- updates=1，则视情况而定，如果可以降低写操作的优先级，则使用low-priority-updates=1，否则使用 max_write_lock_count=1。
  set-variable = max_allowed_packet=1M
  set-variable = net_buffer_length=2K