MySQL 参数

theme: smartblue highlight: androidstudio

connection 相关

max_connections

show VARIABLES LIKE 'max_connections'

代表数据库同时允许的最大连接数

连接由两种常见状态：sleep / query

• sleep：连接处于闲置状态
• query：代表连接正处于处理任务的状态

sleep + query 连接的总量不能超过 max_connections 的值，否则会出现 ERROR 1040：Too many connections

Max_used_connections

mysql 历史运行过程中最大连接数的数量及节点

建议：开始可以最大连接数设置大一点，跟踪观察Max_used_connections，最后上浮百分之20-50

back_log

show variables like 'back_log';

如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：

unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.

查看 MySQL 线程服务信息

show STATUS like 'Threads%'

• Threads_connected 代表当前由多少连接（sleep + query）
• Threads_created 代表历史总共创建过多少个数据库连接
• Threads_running 代表有几个连接正处于工作状态，也是目前的并发数
• Threads_cached 代表共缓存过多少连接，如果我们在 MySQL 服务器配置文件中设置了 thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来，以响应下一个客户而不是销毁（前提是缓存数未达上限）

wait_timeout 和 interactive_timeout

show VARIABLES like 'wait_timeout' show VARIABLES like 'interactive_timeout'

两个参数都是超过一段时间后，数据库连接自动关闭，单位秒

interactive_timeout 针对交互式连接，wait_timeout 针对非交互式连接

通过 mysql 客户端连接数据库是交互式连接，通过 jdbc 连接数据库是非交互式连接

show processlist;

查询当前数据库连接详情

• Command：连接状态
• Time：这个状态持续的时间，单位是秒
• State：当前 sql 语句的状态
• show processlist 史上最全参数详解及解决方案

cache 相关

• 使用缓存查询，MySQL 将查询结果放在缓冲区中
• 今后对同样的 select 语句（区分大小写），将直接从缓冲区读取结果
• MySQL缓存深入理解（全网最深、最全、最实用）
• [玩转MySQL之四]MySQL缓存机制
• 5.7 默认禁用 Qc

%query_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             | 599040  |      --查询缓存的大小，QC 存储最小单位是 1024byte，如果不设置 1024 的整数倍，那么会设置成最接近当前值等于 1024 的倍数
| query_cache_type             | ON      |      --是否支持查询缓存
| query_cache_wlock_invalidate | OFF     |      --控制当有写锁加在表上的时候，是否先让该表相关的 Query Cache失效
+------------------------------+---------+
6 rows in set (0.02 sec)

Qcache%

SHOW STATUS LIKE 'Qcache%'; 缓存状态查看

• Qcache_free_memory： QueryCache 中剩余的内存大小，可以通过该参数较为准确的观察当前系统中 QueryCache 是否足够

• Qcache_lowmem_prunes： 多少条 Query 因为内存不足而被清除出 QueryCache，通过 Qcache_lowmem_prunes 和 Qcache_free_memory 相互结合，能够更清楚的了解到 QueryCache 的内存是否真的足够，是否非常频繁的出现因为内存不足而有 Query 被换出。这个数字需要长时间来看，如果这个数字不断增长，就表示可能碎片化非常严重或内存不够

• Qcache_total_blocks： 总块数，每个 sql 缓存都以 block 为单位

• Qcache_free_blocks： 在查询缓存中的闲置块，如果该值比较大，则说明 Query Cache 中的内存碎片可能比较多。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个较大的空闲内存块。（查询缓存碎片率：Qcache_free_blocks / Qcache_total_blocks * 100%，超过 20% 可以进行清理）

• block 默认是 4kb（query_cache_min_res_unit），设置值大对大数据有好处，但是如果查询大多是小数据，就容易内存碎片和浪费

• Qcache_hits： 缓存命中次数

• Qcache_inserts： 缓存被插入的次数，也就是查询没有命中的次数。

• Qcache_queries_in_cache： 当前缓存中缓存的查询数量

• Qcache_not_cached： 未进入查询缓存的 sql 条数，有一些会导致查询结果不会被缓存：

  1. 其一，由于 query_cache_type 的设置；
  2. 其二，查询不是 SELECT 语句；
  3. 其三，使用了 now() 之类的函数，导致查询语句一直在变化。
  4. 超过 query_cache_limit 的值
  5. 未查询到数据行

• Query Cache 利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%
• Query Cache 利用率在25%以下的话，说明 query_cache_size 设置的过大，可适当减小；
• Query Cache 利用率在 80% 以上，而且 Qcache_lowmem_prunes > 50 的话，说明 query_cache_size 可能有点小，或者就是内存碎片太多。

• Query Cache 命中率 = Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%
• 若命中率在 50-70%的范围之内，则表明 Query Cache 的缓存效率较高。
• 如果命中率明显小于 50%，那么建议禁用（将 query_cache_type 设置为 0(OFF)）或按需使用（将 query_cache_type 设置为2(DEMAND)）Query Cache，节省的内存可以用作 InnoDB 的缓冲池。

buffer 相关

  虽然说 MySQL 的数据是存储在磁盘里的，但是也不能每次都从磁盘里面读取数据，这样性能是极差的。   要想提升查询性能，加个缓存就行了嘛。所以，当数据从磁盘中取出后，缓存内存中，下次查询同样的数据的时候，直接从内存中读取。   为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

sort_buffer_size

• sort_buffer_size是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。
• 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。官方文档推荐范围为256KB~2MB。

innodb_buffer_size

专用于 innodb 存储引擎的读写 IO 出现的

show GLOBAL variables like '%Innodb_buffer_pool%'; innodb_buffer_pool 相关的参数配置 show GLOBAL STATUS like '%Innodb_buffer_pool_pages%'; innodb_buffer_pool 相关的统计信息，例如：已使用的缓存页数量、全部缓存页的数量 show GLOBAL STATUS like '%Innodb_page_size%'; Innodb 每页的长度

result = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100% 页面使用率

• result > 95% 考虑增大 innodb_buffer_pool_size，建议使用物理内存的 75%
• result < 95% 考虑减小 innodb_buffer_pool_size，建议设置为 Innodb_buffer_pool_pages_data * Innodb_buffer_pool_size * 1.05 / (1024 * 1024 * 1024) G

线程相关

innodb_thread_concurrency

默认是 0 ，表示不限制并发线程数量

使用以下指南来帮助找到和维护一个合适的设置（来自官网）:

• 如果一个工作负载的并发用户线程数一直很低且不影响性能，则将 innodb_thread_concurrency 设置为 0 (无限制)。
• 如果您的工作负载一直很重，或者偶尔出现峰值，请设置一个 innodb_thread_concurrency 值并进行调整，直到找到提供最佳性能的线程数。例如，假设您的系统通常有 40 到 50 个用户，但是这个数字会周期性地增加到 60、70 或更多。通过测试，您会发现性能在 80 个并发用户的限制下基本保持稳定。在此例中，将 innodb_thread_concurrency 设置为80。
• 如果您不想为用户线程使用超过一定数量的虚拟 cpu (例如 20 个虚拟cpu)，请将innodb_thread_concurrency 设置为这个数字(或可能更低，这取决于性能测试)。如果您的目标是将MySQL 与其他应用程序隔离，请考虑将进程专门绑定到虚拟 cpu。但是，请注意，如果进程不是始终处于繁忙状态，排他性绑定可能会导致非最佳的硬件使用。在这种情况下，可以将进程绑定到虚拟 cpu，但允许其他应用程序使用部分或全部虚拟 cpu。InnoDBmysqldmysqldmysqld
• 请注意
  • 从操作系统的角度来看，使用资源管理解决方案来管理如何在应用程序之间共享 CPU 时间可能比绑定进程更好。例如，您可以在其他关键进程未运行时将 90% 的虚拟 CPU 时间分配给给定应用程序，并在其他关键进程正在运行时将该值调回 40%。mysqld

• 在某些情况下，innodb_thread_concurrency 的最佳设置可以小于虚拟 cpu 的数量。
• innodb_thread_concurrency 值过高会导致性能下降，因为系统内部和资源的争用会增加。
• 定期监控和分析您的系统。工作负载、用户数量或计算环境的变化可能需要调整innodb_thread_concurrency 设置。