MySQL之Innodb_buffer_pool_size设置方式

MySQL之Innodb_buffer_pool_size设置方式

目录

Innodb_buffer_pool_size设置方式

缓冲池相关参数说明

合理的设置缓存池相关参数

设置innodb_buffer_pool_size参数

Innodb_buffer_pool_size设置方式

缓冲池是用于存储InnoDB表,索引和其他辅助缓冲区的缓存数据的内存区域。缓冲池的大小对于系统性能很重要。更大的缓冲池可以减少磁盘I/O来多次访问同一表数据。在专用数据库服务器上,可以将缓冲池大小设置为计算机物理内存大小的80%

缓冲池相关参数说明

1)系统变量参数

Innodb_page_size

InnoDB页面大小(默认为16KB)。页面中包含许多值,页面大小使它们可以轻松转换为字节。

Innodb_buffer_pool_chunk_size

innodb_buffer_pool_chunk_size 定义InnoDB缓冲池大小调整操作的块大小。默认128M。最大值可设置innodb_buffer_pool_size / innodb_buffer_pool_instances

innodb_buffer_pool_instances

InnoDB 缓冲池划分为的区域数。

Innodb_buffer_pool_pages_data

数页在 InnoDB 缓冲池中包含的数据。该数字包括 脏页和干净页。使用压缩表时,报告的 Innodb_buffer_pool_pages_data 值可能大于 Innodb_buffer_pool_pages_total (错误#59550)。

Innodb_buffer_pool_pages_total

InnoDB 缓冲池 的总大小(以页为单位)。使用压缩表时,报告的 Innodb_buffer_pool_pages_data 值可能大于 Innodb_buffer_pool_pages_total (错误#59550)

2)运行状态变量

Innodb_buffer_pool_pages_flushed

从缓冲池刷新页面的请求数 。

Innodb_buffer_pool_read_requests

表示从内存中读取逻辑的请求数。

Innodb_buffer_pool_reads

InnoDB 不能从缓冲池满足的逻辑读取的数量,必须直接从磁盘读取。

Innodb_buffer_pool_wait_free

通常,对InnoDB缓冲池的写入是在后台进行的。当InnoDB需要读取或创建一个页面而没有可用的干净页面时,InnoDB会首先刷新一些脏页面并等待该操作完成。此计数器统计这些等待的实例。如果innodb_ buffer_pool_size设置正确,这个值应该很小。

合理的设置缓存池相关参数

1、innodb_buffer_pool_size 默认设置系统内存百分之80%,后按如下规则配合实际情况调整

mysql> show global status like 'Innodb_buffer_pool_pages_data'; +-------------------------------+---------+ | Variable_name | Value | +-------------------------------+---------+ | Innodb_buffer_pool_pages_data | 1894874 | +-------------------------------+---------+ 1 row in set (0.00 sec) mysql> show global status like 'Innodb_buffer_pool_pages_total'; +--------------------------------+---------+ | Variable_name | Value | +--------------------------------+---------+ | Innodb_buffer_pool_pages_total | 1965960 | +--------------------------------+---------+ 1 row in set (0.00 sec) # 计算是否应该添加内存 使用率 = Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100% 当结果 > 95% 则增加 innodb_buffer_pool_size 当结果 < 95% 则减少 innodb_buffer_pool_size, 可适当较少,当然独享业务机器多了也没啥问题

2、innodb_buffer_pool_instances 默认设置为8,最小1,最大64

对于缓冲池在数千兆字节范围内的系统,通过减少争用不同线程读写缓存页面的争用,将缓冲池划分为多个单独的实例可以提高并发性。此功能通常用于缓冲池大小在数GB范围内的系统。使用innodb_buffer_pool_instances 配置选项配置了多个缓冲池实例 ,您也可以调整该innodb_buffer_pool_size值。

当InnoDB缓冲池很大时,可以通过从内存中检索来满足许多数据请求。您可能会遇到多个线程试图立即访问缓冲池的瓶颈。您可以启用多个缓冲池以最小化此争用。使用散列函数,将存储在缓冲池中或从缓冲池中读取的每个页面随机分配给其中一个缓冲池。每个缓冲池管理自己的空闲列表,刷新列表,LRU和连接到缓冲池的所有其他数据结构,并受其自己的缓冲池互斥量保护。

要启用多个缓冲池实例,请将innodb_buffer_pool_instances配置选项设置为 大于1(默认)的值,最大为64(最大)。仅当您将innodb_buffer_pool_size大小设置为1GB或更大时,此选项才生效 。您指定的总大小将分配给所有缓冲池。为了获得最佳效率,指定的组合 innodb_buffer_pool_instances 和innodb_buffer_pool_size,使得每个缓冲池实例是至少为1GB。

总结:

# cat /etc/my.cnf [mysqld] ...... innodb_buffer_pool_size = 系统内存%80 innodb_buffer_pool_instances = 大于8的情况下,每个缓冲池实例至少1GB ......

Reference:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.htm

https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

设置innodb_buffer_pool_size参数

用于缓存索引和数据的内存大小,这个当然是越多越好, 数据读写在内存中非常快, 减少了对磁盘的读写。

当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。

然而内存还有操作系统或数据库其他进程使用, 根据经验,推荐设置innodb-buffer-pool-size为服务器总可用内存的75%。 若设置不当, 内存使用可能浪费或者使用过多。

对于繁忙的服务器, buffer pool 将划分为多个实例以提高系统并发性, 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响, 当然影响较小。

1.Innodb_buffer_pool_pages_data: Innodb buffer pool缓存池中包含数据的页的数目,包括脏页。单位是page。

show global status like 'Innodb_buffer_pool_pages_data';

2.Innodb_buffer_pool_pages_total: innodb buffer pool的页总数目。单位是page。

show global status like 'Innodb_buffer_pool_pages_total';

3.show global status like 'Innodb_page_size'; 查看@@innodb_buffer_pool_size大小,单位字节

SELECT @@innodb_buffer_pool_size/1024/1024/1024; #字节转为G

4.在线调整InnoDB缓冲池大小,如果不设置,默认为128M

set global innodb_buffer_pool_size = 4227858432; ##单位字节

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

当结果 > 95% 则增加 innodb_buffer_pool_size, 建议使用物理内存的 75%

当结果 < 95% 则减少 innodb_buffer_pool_size, 建议设置大小为: Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

以上为个人经验,希望能给大家一个参考,也希望大家多多支持易知道(ezd.cc)。

推荐阅读

    mac怎么安装mysql| macbook怎么下载mysql

    mac怎么安装mysql| macbook怎么下载mysql,标签,学习python 不得不学习的就是数据库,那么在开始学习数据库之前,那么今天我们先说下怎么安装m

    MySQL更新更新页面1/4。

    MySQL更新更新页面1/4。,,插入和替换 插入和替换语句的功能是将新数据插入表中,这两个语句的语法相似,它们之间的主要区别是如何处理重复的

    mysql数据库启动失败

    mysql数据库启动失败,报错,非正常,1、原因 公司服务器故障,非正常停机导致数据库启动失败。 报错信息 [ [email protected] dmp]# /etc/ini

    mysql长整型是什么

    mysql长整型是什么,整型,数据类型,语法,用户,填充,版本,MySQL长整型是“BIGINT”,是MySQL中最常用的数据类型之一,其可以用来存储较大的整数值,与

    深入理解MySQL分区

    深入理解MySQL分区,查询,数据,列表,索引,操作,按键,MySQL数据库是一个开源的关系型数据库管理系统。在一些大型数据环境中,为了更好地管理数据、

    mysql 如何查询

    mysql 如何查询,查询,数据,语句,条件,选取,排序,MySQL是一款常用的关系型数据库管理系统,被广泛应用于各种网站和应用开发。在MySQL中查询数据是

    MySQL中怎么实现分页操作

    MySQL中怎么实现分页操作,数据,显示,偏移量,分页,查询,初始,一、 背景什么是分页,就是查询时候数据量太大,一次性返回所有查询结果既耗费网络资源

    mysql怎么连接数据库

    mysql怎么连接数据库,服务,启动,数据库,命令,登录,步骤,mysql连接数据库的方法:1、通过计算机管理方式或通过命令行方式执行“net start mysql”

    mysql 查询拼接

    mysql 查询拼接,函数,字段,字符串,查询,分隔符,连接,MySQL 查询拼接在使用 MySQL 进行查询时,有时需要将多个字段或多张表中的字段进行拼接,这时

    怎么启动 mysql

    怎么启动 mysql,启动,服务器,输入,命令提示符,终端,命令,MySQL是一种广泛使用的关系型数据库管理系统。它是一种可扩展性强、性能卓越、跨平台

    mysql 如何卸载

    mysql 如何卸载,卸载,服务,命令,软件包,数据,安装,MySQL是一种广泛使用的数据库管理系统,用于管理数据的存储、检索和更新。在某些情况下,您需要

    mysql视图是什么

    mysql视图是什么,视图,查询,数据,替换,年龄,引用,mysql视图是一个虚拟表,其内容由查询定义;视图包含系列带有名称的列和行数据,而行和列数据来自定