文章目录
参考文档
优化方式
硬件优化 --> 系统优化 -->mysql配置优化 --> SCHEMA优化 --> sql优化 --> 其他解决方案
mysql配置分析
常见瓶颈
90%系统瓶颈都在IO上，所以提高IOPS尤为总要，如果 iowait过高，加内存，减小数据读取量
如果CPU很高，或者查询时间很长，大概率是索引不当
如果系统发生swap，必定是内存分配不当
所以优化，总是会围绕着提高对内存的使用率+减少IO，比如内存缓存+索引，还有其他方式吗? NO
确认方式
slow log + global status + engine status + processlist + pt工具
mysql配置优化
慢查询日志
在mysql服务器中，数据表都是保存在磁盘上的（innodb、myisam组织表的形式不同，所以文件结构也就不同）。索引为服务器提供了一种在表中查找特定数据行的方法，而不用搜索整个表。当必须要搜索整个表时，就称为表扫描。通常来说，您可能只希望获得表中数据的一个子集，因此全表扫描会浪费大量的磁盘 I/O，因此也就会浪费大量时间。当必须对数据进行连接时，这个问题就更加复杂了，因为必须要对连接两端的多行数据进行比较。
当然，表扫描并不总是会带来问题；有时读取整个表反而会比从中挑选出一部分数据更加有效（服务器进程中查询优化器用来作出这些决定）。如果索引的使用效率很低，或者根本就不能使用索引，则会减慢查询速度，而且随着服务器上的负载和表大小的增加，这个问题会变得更加显著。执行时间超过给定时间范围的查询就称为慢速查询。
在my.cnf中开启慢日志
long_query_time = 1
slow-query-log = on
slow-query-log-file = /data/mysql/slow-query.log
log-queries-not-using-indexes
也可以在会话中设置慢查询相关参数，但重启服务就没了，一般用于调试参数，最后固化到 my.cnf 配置文件
下面设置会把超过 1s 的慢查询日志给记录下来
SET global slow_query_log= 1;
SET global long_query_time = 1;
SET global slow_query_log_file = '/var/lib/mysql/query-slow.log';
查看是否开启
mysql> show variables like '%slow_query%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/query-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主－从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小，另mysql有自带的命令 mysqldumpslow 可进行查询，也可以使用pt工具进行分析 pt-query-digest
例下列命令可以查出访问次数最多的20个sql语句
mysqldumpslow -s c -t 20 slow-query.log
连接数
经常会遇见”MySQL: ERROR 1040: Too manyconnections”的情况，一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 3000 |
+-----------------+-------+
1 row in set (0.00 sec)
这台MySQL服务器最大连接数是3000，然后查询一下服务器响应的最大连接数
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 1444 |
+----------------------+-------+
1 row in set (0.00 sec)
MySQL服务器过去的最大连接数是1444，没有达到服务器连接数上限3000，不会出现1040错误，最大连接数占上限连接数的85％左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了
比较理想的设置是：Max_used_connections / max_connections * 100% ≈ 85%
还有两个比较重要参数
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
+--------------------+-------+
1 row in set (0.00 sec)
wait_timeout指的是mysqld 终止所有空闲时间超过 28800 秒（8小时）的连接。在 LAMP 应用程序中，连接数据库的时间通常就是 Web 服务器处理请求所花费的时间。有时候，如果负载过重，连接会挂起，并且会占用连接表空间。如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点并不可取
max_connect_errors 是一个安全的方法。如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到 FLUSH HOSTS 之后才能运行。默认情况下，10 次失败就足以导致锁定了。将这个值修改为 100 会给服务器足够的时间来从问题中恢复。如果重试 100 次都无法建立连接，那么使用再高的值也不会有太多帮助，可能它根本就无法连接。
key_buffer_size
key_buffer_size 是对MyISAM表性能影响最大的一个参数
mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)
分配了8MB 内存给 key_buffer_size，我们再看一下 key_buffer_size 的使用情况
mysql> show global status like 'key_read%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_read_requests | 0 |
| Key_reads | 0 |
+-------------------+-------+
2 rows in set (0.00 sec)
Key_reads 代表命中磁盘的请求个数， Key_read_requests 是总数
计算索引未命中缓存的概率：key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%
如果 key_cache_miss_rate 在 0.01% 以下的话，key_buffer_size 分配的过多，可以适当减少，如果每 1000 个请求中命中磁盘的数目超过 1 个（>0.1%），就应该考虑增大关键字缓冲区了。
MySQL服务器还提供了key_blocks_*参数
mysql> show global status like 'key_blocks_u%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_blocks_unused | 6698 |
| Key_blocks_used | 0 |
+-------------------+-------+
2 rows in set (0.01 sec)
Key_blocks_unused 表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数，增加key_buffer_size 和 过度索引 都可能会把缓存占满了
比较理想的设置：Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
临时表
临时表可以在更高级的查询中使用，其中数据在进一步进行处理（例如 GROUP BY 字句）之前，都必须先保存到临时表中；理想情况下，在内存中创建临时表。但是如果临时表变得太大，就需要写入磁盘中。
每次创建临时表，Created_tmp_tables 增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables 也增加, Created_tmp_files 表示MySQL服务创建的临时文件文件数，比较理想的配置是：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
mysql> show global status like 'created_tmp%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Created_tmp_disk_tables | 4772 |
| Created_tmp_files | 15231 |
| Created_tmp_tables | 807649 |
+-------------------------+--------+
3 rows in set (0.01 sec)
MySQL服务器对临时表的配置
mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size | 16777216 |
+---------------------+----------+
2 rows in set (0.03 sec)
这里参数值为16MB，说明只有 16MB 以下的临时表才能全部放内存，超过的就会用到硬盘临时表
每次使用临时表都会增大 Created_tmp_tables；基于磁盘的表也会增大 Created_tmp_disk_tables。对于这个比率，并没有什么严格的规则，因为这依赖于所涉及的查询。长时间观察 Created_tmp_disk_tables 会显示所创建的磁盘表的比率，您可以确定设置的效率。tmp_table_size 和 max_heap_table_size 都可以控制临时表的最大大小，因此请确保在 my.cnf 中对这两个值都进行了设置
Open Table情况
Open_tables 表示打开表的数量 Opened_tables 表示打开过的表数量，如果 Opened_tables 数量过大，说明配置中 table_open_cache 值可能太小。
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 13920 |
| Opened_tables | 14166 |
+---------------+-------+
2 rows in set (0.00 sec)
table_open_cache 默认为 MAX( (open_files_limit - 10 - max_connections) / 2,400)，上限为 524288，同时也要注意操作系统文件描述符的限制（该服务器为104854），不能超过系统 ulimit -n table_open_cache 官方文档
table_definition_cache 默认为 MIN(400 + table_open_cache / 2, 2000)，上限为 524288 table_definition_cache 官方文档
mysql> show variables like 'table\_%_cache';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| table_definition_cache | 16384 |
| table_open_cache | 16384 |
+------------------------+-------+
2 rows in set (0.01 sec)
比较合适的值为：Open_tables / Opened_tables * 100% 在 85% ~ 95% 之间
线程使用情况
与表的缓存类似，对于线程来说也有一个缓存。 mysqld 在接收连接时会根据需要生成线程。在一个连接变化很快的繁忙服务器上，对线程进行缓存便于以后使用可以加快最初的连接
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 4 |
| Threads_connected | 1090 |
| Threads_created | 1458 |
| Threads_running | 88 |
+-------------------+-------+
4 rows in set (0.00 sec)
如果我们在MySQL服务器配置文件中设置了 thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户 而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中 thread_cache_size 值，配置默认 8 + (max_connections / 100)，上限为100。 thread_cache_size 官方文档
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 100 |
+-------------------+-------+
1 row in set (0.00 sec)
查询缓存(query cache)
mysql8官方已经废弃这个功能
很多 LAMP 应用程序都严重依赖于数据库，但却会反复执行相同的查询。每次执行查询时，数据库都必须要执行相同的工作 —— 对查询进行分析，确定如何执行查询，从磁盘中加载信息，然后将结果返回给客户机。MySQL 有一个特性称为查询缓存，它将（后面会用到的）查询结果保存在内存中。在很多情况下，这会极大地提高性能。不过，问题是查询缓存在默认情况下是禁用的
# mysql 8 没有此项: Empty set (0.00 sec)
mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 22756 |
| Qcache_free_memory | 76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached | 13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks | 111212 |
+-------------------------+-----------+
MySQL查询缓存变量解释：
Qcache_free_blocks：缓存中相邻内存块的个数，数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理。
Qcache_free_memory：缓存中的空闲内存。
Qcache_hits：每次查询在缓存中命中时就增大
Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes： 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。
Qcache_total_blocks：缓存中块的数量。
我们再查询一下服务器关于query_cache的配置
# mysql 8 没有此项: Empty set (0.00 sec)
mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 203423744 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
各字段的解释：
query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小
query_cache_size：查询缓存大小
query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。
query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
查询缓存碎片率 = 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_inserts) / Qcache_hits * 100%
示例服务器 查询缓存碎片率 ＝ 20.46％，查询缓存利用率 ＝ 62.26％，查询缓存命中率 ＝ 1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。
作为一条规则，如果 FLUSH QUERY CACHE 占用了很长时间，那就说明缓存太大了
排序使用情况
mysql> show global status like 'sort%';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| Sort_merge_passes | 13575 |
| Sort_range | 0 |
| Sort_rows | 20446556 |
| Sort_scan | 82431 |
+-------------------+----------+
4 rows in set (0.00 sec)
Sort_merge_passes 包括两步。MySQL 首先会尝试在内存中做排序，使用的内存大小由系统变量Sort_buffer_size 决定，如果它的大小不够把所有的记录都读到内存中，MySQL 就会把每次在内存中排序的结果存到临时文件中，等MySQL 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 Sort_merge_passes。实际上，MySQL会用另一个临时文件来存再次排序的结果，所以通常会看到 Sort_merge_passes增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加 Sort_buffer_size 会减少Sort_merge_passes 和 创建临时文件的次数，但盲目的增加Sort_buffer_size 并不一定能提高速度
文件打开数(open_files)
比较合适的设置：Open_files / open_files_limit * 100% <= 75％
mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 90 |
+---------------+-------+
1 row in set (0.00 sec)
show variables like 'open_files_limit';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| open_files_limit | 1048576 |
+------------------+---------+
表锁情况
Table_locks_immediate 表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数，一般在业务表都用 InnoDB 引擎（支持行锁），因此这项不太适合优化。
mysql> show global status like 'table_locks%';
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Table_locks_immediate | 719852 |
| Table_locks_waited | 0 |
+-----------------------+--------+
2 rows in set (0.00 sec)
如果 Table_locks_immediate / Table_locks_waited >5000，最好采用InnoDB引擎，因为 InnoDB 是行锁而MyISAM是表锁，对于高并发写入的应用 InnoDB 效果会好些。示例中的服务 器 Table_locks_immediate / Table_locks_waited ＝ 235，MyISAM 就足够了
表扫描情况
表扫描率 ＝ Handler_read_rnd_next / Com_select
如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB，默认128 KB
mysql> show global status like 'handler_read%';
+-----------------------+------------+
| Variable_name | Value |
+-----------------------+------------+
| Handler_read_first | 385719 |
| Handler_read_key | 26662032 |
| Handler_read_last | 1605 |
| Handler_read_next | 202033741 |
| Handler_read_prev | 47010 |
| Handler_read_rnd | 453972 |
| Handler_read_rnd_next | 2838187313 |
+-----------------------+------------+
7 rows in set (0.22 sec)
mysql> show global status like 'Com_select';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Com_select | 2340543 |
+---------------+---------+
1 row in set (0.01 sec)
mysql> show variables like 'read_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.01 sec)
table_definition_cache
表定义信息缓存是从MySQL5.1.3 版本才开始引入的一个新的缓存区，用来存放表定义信息。当我们的MySQL 中使用了较多的表的时候，此缓存无疑会提高对表定义信息的访问效率。MySQL 提供了table_definition_cache 参数给我们设置可以缓存的表的数量。在MySQL5.1.25 之前的版本中，默认值为128，从MySQL5.1.25 版本开始，则将默认值调整为256 了，最大设置值为524288。注意，这里设置的是可以缓存的表定义信息的数目，而不是内存空间的大小。
mysql> show global variables like '%definition%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| schema_definition_cache | 256 |
| stored_program_definition_cache | 256 |
| table_definition_cache | 2000 |
| tablespace_definition_cache | 256 |
+---------------------------------+-------+
4 rows in set (0.00 sec)
内存的组成
innodb存储引擎可以分为三部分：内存、进程、数据文件
innodb的内存的作用大致如下
- 缓存磁盘上的数据，方便快速的读取；
- 对磁盘文件的数据进行修改之前在这里缓存；
- 应用所作的日志的缓存；
- 内存结构自身的管理结构
Innodb buffer pool size
mysql8.0 性能优化配置 innodb_buffer_pool_size
用于缓存数据、索引、锁、插入缓冲、数据字典等，不超过物理内存的80%。总而言之，innodb_buffer_pool_size对InnoDB表来说非常重要。
缓冲池是最大块的内存部分，主要用来各种数据的缓冲。innodb将数据文件按页（16K）读取到缓冲池，然后按最少使用（LRU)算法来保留缓存数据；
数据文件修改时，先修改缓存池中的页（即脏页），然后按一定平率将脏页刷新到文件。
innodb_buffer_pool_size = n* innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances，n 为整数倍。
例如： innodb_buffer_pool_chunk_size(128M) * innodb_buffer_pool_instances(16) = 2G，那 innodb_buffer_pool_size 可以为 2，4，6，8, 16G 等, 要注意物理机的可用内存，一般小于80%，下面示例我设置为物理机的一半 16G。
mysql> SELECT
-> @@innodb_buffer_pool_size/1024/1024 as innodb_buffer_pool_size_MB ,
-> @@innodb_buffer_pool_instances,
-> @@innodb_buffer_pool_chunk_size/1024/1024 as innodb_buffer_pool_chunk_size_MB;
+----------------------------+--------------------------------+----------------------------------+
| innodb_buffer_pool_size_MB | @@innodb_buffer_pool_instances | innodb_buffer_pool_chunk_size_MB |
+----------------------------+--------------------------------+----------------------------------+
| 128.00000000 | 1 | 128.00000000 |
+----------------------------+--------------------------------+----------------------------------+
1 row in set (0.00 sec)
优化示例：
# innodb缓冲池大小
innodb_buffer_pool_size=16G
# innodb缓冲池块大小
innodb_buffer_pool_chunk_size=128M
# innodb缓冲池实例数，默认为 8 (或 1 当 innodb_buffer_pool_size < 1GB)
innodb_buffer_pool_instances=16
更多参数解读：
mysql> show variables like 'innodb_%_size';
+----------------------------------+------------+
| Variable_name | Value |
+----------------------------------+------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_ddl_buffer_size | 1048576 |
| innodb_doublewrite_batch_size | 0 |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_log_buffer_size | 16777216 |
| innodb_log_file_size | 50331648 |
| innodb_log_write_ahead_size | 8192 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_page_size | 16384 |
| innodb_purge_batch_size | 300 |
| innodb_sort_buffer_size | 1048576 |
| innodb_sync_array_size | 1 |
+----------------------------------+------------+
18 rows in set (0.00 sec)
按照数据页的类型:
- 有索引页
- 数据页
- undo页
- 插入缓冲
- 自适应哈希索引
- InnoDB存储的锁信息、数据字典信息等
通过 show engine innodb status 可以查看缓冲池的具体信息
mysql> show engine innodb status;
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 19028108
Buffer pool size 8192
Free buffers 1024
Database pages 7085
Old database pages 2595
Modified db pages 14
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 984942, not young 54321088
3.63 youngs/s, 997.00 non-youngs/s
Pages read 1661655, created 58929, written 361616
5.33 reads/s, 0.22 creates/s, 11.36 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 336 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7085, unzip_LRU len: 0
I/O sum[1380]:cur[3], unzip sum[0]:cur[0]
单位是buffer frame，每个buffer frame为16K，通过计算可以查看buffer pool的使用情况:
1、Buffer pool size 8192×16×1024 = 134217728 B = 131072 KB = 128 MB
2、Free buffers表示当前空闲的缓冲页
3、Database pages表示已经使用的缓冲页
4、Modified db pages 表示脏页的数量
5、Old database pages表示LRU列表中old sublist中的数据块数量
对上面的innodb buffer pool 细看会发现，buffer pool的数据类型又可以分为：page cache、hash index、undo、insert buffer、explicit locks
Log Buffer
日志缓冲池（功能跟oracle redo log buffer基本相似），将redo log（重做日志信息） 放入这个缓冲区，然后按一定频率将其刷新到重做日志文件。该值一般不需要设置很大，因为一般情况下每一秒钟就会将重做日志缓冲刷新到日志文件，只需要保证每秒产生的事务量在这个缓冲大小之内即可。
mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)
内存计算
key_buffer_size
+ tmp_table_size
+ innodb_buffer_pool_size
+ innodb_log_buffer_size
+ max_connections *(
read_buffer_size
+ read_rnd_buffer_size
+ sort_buffer_size
+ join_buffer_size
+ binlog_cache_size
+ thread_stack
+ tmp_table_size
+ bulk_insert_buffer_size
)
优化主方向：
- 降
innodb_buffer_pool_size，定期观察，结合业务实际使用情况 - 降
连接数，通过设置wait_timeout，和interactive_timeout，及时释放空闲连接
sql 查询各值明细和最大占用内存
SELECT
@@key_buffer_size / ( 1024 * 1024 ),
@@tmp_table_size / ( 1024 * 1024 ),
@@innodb_buffer_pool_size / ( 1024 * 1024 ),
@@innodb_log_buffer_size / ( 1024 * 1024 ),
@@max_connections / ( 1024 * 1024 ),
@@sort_buffer_size / ( 1024 * 1024 ),
@@read_buffer_size / ( 1024 * 1024 ),
@@read_rnd_buffer_size / ( 1024 * 1024 ),
@@join_buffer_size / ( 1024 * 1024 ),
@@thread_stack / ( 1024 * 1024 ),
@@binlog_cache_size / ( 1024 * 1024 ),
@@bulk_insert_buffer_size / ( 1024 * 1024 );
各值明细
| key_buffer_size_MB | tmp_table_size_MB | innodb_buffer_pool_size_MB | innodb_log_buffer_size_MB | max_connections | sort_buffer_size_MB | read_buffer_size_MB | read_rnd_buffer_size_MB | join_buffer_size_MB | thread_stack_MB | binlog_cache_size_MB | bulk_insert_buffer_size_MB |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 8 | 16 | 128 | 16 | 3000 | 0.25 | 0.125 | 0.25 | 0.25 | 1 | 0.0313 | 8 |
最大占用内存
SELECT(@@key_buffer_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@max_connections * ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack + @@bulk_insert_buffer_size ) ) / (1024*1024*1024) AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
| 29.1863 |
+---------------+
SQL 汇总
-- 慢查询
show variables like '%slow_query%';
show variables like 'long_query_time';
-- SET global long_query_time = 1;
-- SET global slow_query_log= 1;
-- SET global slow_query_log_file = '/var/lib/mysql/query-slow.log';
-- status 状态
show global status where Variable_name in ('Threads_connected', 'Threads_created','Threads_running');
SELECT variable_name,variable_value FROM `performance_schema`.global_status WHERE variable_name like 'Thread%';
-- 要求 max_connections > Max_used_connections, 理想是 used/max = 85%
show global status like 'Thread%';
show variables like 'max_connections';
show global status like 'Max_used_connections';
show global status like 'table_locks%';
-- VARIABLES 参数
show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
SELECT variable_name,variable_value/1024/1024 FROM `performance_schema`.global_variables
WHERE variable_name in ('key_buffer_size','tmp_table_size','innodb_buffer_pool_size',
'innodb_log_buffer_size','max_connections'
);
-- 更改要在 my.cnf innodb_buffer_pool_size = n * innodb_buffer_pool_instances(16) * innodb_buffer_pool_chunk_size(128M)= n*2G,
show variables where Variable_name in ('innodb_buffer_pool_size', 'innodb_buffer_pool_instances', 'innodb_buffer_pool_chunk_size');
-- 连接
show variables like '%connections';
-- 默认 thread_cache_size= 8+(max_connections/100)，限制100内
show variables like 'thread_cache_size';
-- set global thread_cache_size = 100;
-- 默认 28800/3600 = 8h，终止所有空闲时间超过 8h 的连接
show variables like 'wait_timeout';
show variables like 'max_connect_errors';
-- set global max_connect_errors=100;
-- 关键字缓冲区 8388608/1024/1024 =8M, key_buffer_size是对MyISAM表性能影响最大的一个参数
show variables like 'key_buffer_size';
-- Key_reads 代表命中磁盘的请求个数， Key_read_requests 是总数, Key_reads/Key_read_requests 在 0.0001～0.001 -> key_buffer_size 过多，比值越小
show global status like 'key_read%';
-- Key_blocks_unused 未使用的缓存簇(blocks)数，理想 used/(used+unused) = 80%
show global status like 'key_blocks_u%';
-- max_heap_table_size 以下的存内存，超过就存硬盘
show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
-- 临时表变得太大，就需要写入磁盘中，理想 Created_tmp_disk_tables/Created_tmp_tables <= 25%， 15934/841982*100% = 1.89% (好)
show global status like 'created_tmp%';
-- Open_tables / Opened_tables = 0.85～0.95，
show global status like 'open%tables%';
-- table_open_cache 根据 max_connections * 表数目，不超过参数 open_files_limit 设置的值 1048576，同时也要注意操作系统文件描述符的限制，不能超过系统 ulimit -n (104854)
show variables where Variable_name in ('table_open_cache','open_files_limit');
-- set global table_open_cache=16384;
-- set global table_definition_cache=16384;
-- 尝试内存做排序
show global status like 'sort%';
show variables like 'sort_buffer_size';
-- Open_files / open_files_limit * 100% <= 75％
show global status like 'open_files';
show variables like 'open_files_limit';
-- 表锁情况
show global status like 'table_locks%';
-- 表扫描率 ＝ Handler_read_rnd_next / Com_select 如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加 read_buffer_size 值会有一些好处，但最好不要超过8MB
show global status like 'handler_read%';
show global status like 'Com_select';
-- 默认 131072/1024 = 128kb
show variables like 'read_buffer_size';
-- SELECT 604800/24/3600 =7 day
show variables like 'binlog_expire_logs_seconds';
-- QPS 每秒查询率 (150364482-150364357)/5 = 25
show global status like 'Queries%'; SELECT sleep(5); show global status like 'Queries%';
-- TPS 每秒事务处理数 TPS = ( Handler_commit + Handler_rollback + Com_commit + Com_rollback + Com_rollback_to_savepoint + Handler_savepoint_rollback) / 单位时间 t
show global status like 'com%';
-- 内存最大总值
SELECT(@@key_buffer_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size
+ @@max_connections * ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
+ @@join_buffer_size + @@binlog_cache_size + @@thread_stack + @@bulk_insert_buffer_size )
)/( 1024 * 1024 * 1024 ) AS MAX_MEMORY_GB;
-- 内存各值
SELECT
@@key_buffer_size / ( 1024 * 1024 ),
@@tmp_table_size / ( 1024 * 1024 ),
@@innodb_buffer_pool_size / ( 1024 * 1024 ),
@@innodb_log_buffer_size / ( 1024 * 1024 ),
@@max_connections,
@@sort_buffer_size / ( 1024 * 1024 ),
@@read_buffer_size / ( 1024 * 1024 ),
@@read_rnd_buffer_size / ( 1024 * 1024 ),
@@join_buffer_size / ( 1024 * 1024 ),
@@thread_stack / ( 1024 * 1024 ),
@@binlog_cache_size / ( 1024 * 1024 ),
@@bulk_insert_buffer_size / ( 1024 * 1024 );