提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

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

mysql 服务器 缓存 数据
2023-04-18 12:55:34 时间

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缓冲池的大小和如果必要增加服务器内存。

相关文章