MYSQL单表数据量过大查询过慢配置优化innodb_buffer_pool_size 实践笔记

cookily

发布于 2021-12-07 15:19:51

8440

发布于 2021-12-07 15:19:51

文章被收录于专栏：cookilycookily

MYSQL单表数据量过大查询过慢配置优化innodb_buffer_pool_size 实践笔记

0.问题场景

有张表，里面有300多万数据，使用select count(1) from table 查询的时候要好几分钟，查过资料后添加了innodb_buffer_pool_size参数，然后就1秒就查出来了。

innodb_buffer_pool_size=4G #一般设为内存的50%

注意

独立服务器

在一个独立的只使用InnoDB引擎的MySQL服务器中，根据经验，推荐设置innodb-buffer-pool-size为服务器总可用内存的80%。为什么不是90%或者100%呢？因为其它的东西也需要内存：

每个查询至少需要几K的内存(有时候是几M)
有各种其它内部的MySQL结构和缓存
InnoDB有一些结构是不用缓冲池的内存的(字典缓存，文件系统，锁系统和页哈希表等)
也有一些MySQL文件是在OS缓存里的(binary日志，relay日志,innodb事务日志等)
此处，你也必须为操作系统留出些内存

共享服务器

如果你的MySQL服务器与其它应用共享资源，那么上面80%的经验就不那么适用了。在这样的环境下，设置一个对的数字有点难度。首先让我们来统计一下InnoDB表的实际占用大小。执行如下查询：

SELECT engine,
 count(*) as TABLES,
 concat(round(sum(table_rows)/1000000,2),'M') rows,
 concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
 concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
 concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
 round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES
WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema')
GROUP BY engine
ORDER BY sum(data_length+index_length) DESC LIMIT 10;

这会给出一个参考，让你知道如果你想缓存整个数据集应该为InnoDB缓冲池设置多少内存合适。不过大多数情况你不需要那样做，你只需要缓存你经常使用的数据集。设置好之后，我们来看看如何检查InnoDB缓冲池大小是否设置足够。在终端中，执行如下命令：

$ mysqladmin ext -ri1 | grep Innodb_buffer_pool_reads
| Innodb_buffer_pool_reads                 | 1832098003     |
| Innodb_buffer_pool_reads                 | 595            |
| Innodb_buffer_pool_reads                 | 915            |
| Innodb_buffer_pool_reads                 | 734            |
| Innodb_buffer_pool_reads                 | 622            |
| Innodb_buffer_pool_reads                 | 710            |
| Innodb_buffer_pool_reads                 | 664            |
| Innodb_buffer_pool_reads                 | 987            |
| Innodb_buffer_pool_reads                 | 1287           |
| Innodb_buffer_pool_reads                 | 967            |
| Innodb_buffer_pool_reads                 | 1181           |
| Innodb_buffer_pool_reads                 | 949            |

你所看到的是从硬盘读取数据到缓冲池的次数(每秒)。上面的数据已经相当高了(幸运的是，这个服务器的IO设备能处理每秒4000的IO操作)，如果这个是OLTP系统，我建议提高innodb缓冲池的大小和如果必要增加服务器内存。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2021-05-25 ，如有侵权请联系 cloudcommunity@tencent.com 删除

缓存