牛下载:绿色软件官方软件免费下载基地!
所在位置:首页 > 新闻资讯 > INNODB_BUFFER_POOL_SIZE:设置最佳内存值

INNODB_BUFFER_POOL_SIZE:设置最佳内存值

发布时间:2020-07-14 20:26:40来源:阅读:

什么是INNODB BUFFER POOL



计算机使用它们的大部分内存来提升对经常访问的数据的性能。这就是我们所知的缓存,是系统的一个非常重要的组成部分,因为访问硬盘的数据可能会慢到100到100000倍,这取决你访问的数据量。
MyISAM是使用操作系统的文件系统缓存来缓存那些经常被查询的数据。然而InnoDB使用的是一种非常不同的方法。
不依赖操作系统的缓存,InnoDB自己在InnoDB Buffer Pool处理缓存。经过这篇文章你会学到它是如何工作的,为什么以那种方式来实施是一个不错的想法。

InnoDB缓冲池不仅仅是一个缓存



InnoDB缓冲池实际上用于多个目的,它用来:
* 数据缓存 – 这绝对是它的最重要的目的
* 索引缓存 – 这使用是的同一个缓冲池
* 缓冲 – 更改的数据(通常称为脏数据)在被刷新到硬盘之前先存放到缓冲
* 存储内部结构 – 一些结构如自适应哈希索引或者行锁也都存储在InnoDB缓冲池
下面是一个经典的把innodb-buffer-pool-size设置为62G的InnoDB缓冲池页的分布情况:
INNODB_BUFFER_POOL_SIZE:设置最佳内存值
正如你所看到的,Buffer Pool大多是用于普通的InnoDB页面,但大约10%用作其它目的。
这张表的单位是InnoDB页。单个页面大小实际上是16K,所以你可以乘以16,384来得到以字节为单位更直观的使用情况。

InnoDB缓冲池的大小



那么innodb-buffer-pool-size的大小应该设置为什么呢?下面我们就开始谈到这个。

独立服务器

在一个独立的只使用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缓冲池的大小和如果必要增加服务器内存。

更改InnoDB缓冲池



最后,介绍如何更改innodb-buffer-pool-size。
如果你运行的是MySQL 5.7,那么非常幸运,你可以在线更改这个变量,只需要以root身份执行如下查询:

mysql> SET GLOBAL innodb_buffer_pool_size=size_in_bytes;

这还没完,你仍然需要更改my.cnf文件,不过至少你不需要重启服务器让它生效。从mysql的错误日志中我们可以看到它生效的过程:

[Note] InnoDB: Resizing buffer pool from 134217728 to 21474836480. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 159 chunks (1302369 blocks) were added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: Completed to resize buffer pool from 134217728 to 21474836480.
[Note] InnoDB: Re-enabled adaptive hash index.

在更早的mysql版本就需要重启了,所以:
1. 在my.cnf中设置一个innodb_buffer_pool_size合适的值
2.重启mysql服务器

反对
收藏
  • 热门资讯
  • 最新资讯
  • 应用排行榜
  • 游戏排行榜