MySQL5.7 在线调整Innodb_Buffer_Pool_size不用重启mysql进程

最新推荐文章于 2025-07-30 15:08:32 发布
最新推荐文章于 2025-07-30 15:08:32 发布
阅读量9.1k 收藏 4
点赞数 3
分类专栏： mysql 文章标签： Innodb_Buffer_Pool_size mysql5.7 mysql配置文件修改
本文介绍MySQL 5.7中InnoDB缓存池大小的动态调整方法及其应用场景，包括调整过程中的注意事项及监控方式。

在之前的版本，调整Innodb_Buffer_Pool_size大小必须重启mysql进程才可以生效，如今在MySQL5.7里，可以直接动态设置，方便了很多。

这个功能应用的场景：
一、机器增加内存，DBA粗心大意忘记调大Innodb_Buffer_Pool_size了
二、工作交接，新来的DBA发现前任DBA设置的Innodb_Buffer_Pool_size不合理

需要注意的地方，在调整Buffer_Pool期间，用户的请求将会阻塞，直到调整完毕，所以请勿在白天调整，在凌晨3-4点低峰期调整。

调整时，内部把数据页移动到一个新的位置，单位是块。如果想增加移动的速度，需要调整innodb_buffer_pool_chunk_size参数的大小，默认是128M。

例（把BP 128M增大为384M）：

mysql> SELECT @@innodb_buffer_pool_size;

+---------------------------+

| @@innodb_buffer_pool_size |

+---------------------------+

|                134217728 |

+---------------------------+

1 row in set (0.00 sec)

mysql> SELECT @@innodb_buffer_pool_chunk_size;

+---------------------------------+

| @@innodb_buffer_pool_chunk_size |

+---------------------------------+

|                      134217728 |

+---------------------------------+

1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @@innodb_buffer_pool_size;

+---------------------------+

| @@innodb_buffer_pool_size |

+---------------------------+

|                402653184 |

+---------------------------+

1 row in set (0.00 sec)


innodb_buffer_pool_chunk_size的大小，计算公式是innodb_buffer_pool_size / innodb_buffer_pool_instances

比如现在初始化innodb_buffer_pool_size为2G，innodb_buffer_pool_instances实例为4，innodb_buffer_pool_chunk_size设置为1G，那么会自动把innodb_buffer_pool_chunk_size 1G调整为512M，例：
./mysqld --innodb_buffer_pool_size=2147483648 --innodb_buffer_pool_instances=4 
--innodb_buffer_pool_chunk_size=1073741824;

mysql> SELECT @@innodb_buffer_pool_size;

+---------------------------+

| @@innodb_buffer_pool_size |

+---------------------------+

|                2147483648 |

+---------------------------+

1 row in set (0.00 sec)

 

mysql> SELECT @@innodb_buffer_pool_instances;

+--------------------------------+

| @@innodb_buffer_pool_instances |

+--------------------------------+

|                              4 |

+--------------------------------+

1 row in set (0.00 sec)

 

# Chunk size was set to 1GB (1073741824 bytes) on startup but was

# truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_chunk_size;

+---------------------------------+

| @@innodb_buffer_pool_chunk_size |

+---------------------------------+

|                      536870912 |

+---------------------------------+

1 row in set (0.00 sec)


监控Buffer Pool调整进程

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

+----------------------------------+----------------------------------+

| Variable_name                    | Value                            |

+----------------------------------+----------------------------------+

| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |

+----------------------------------+----------------------------------+

1 row in set (0.00 sec)


查看错误日志：
（增大）

[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)

[Note] InnoDB: disabled adaptive hash index.

[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was 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 4294967296.

[Note] InnoDB: re-enabled adaptive hash index.


（减少）

[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)

[Note] InnoDB: disabled adaptive hash index.

[Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.

[Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate 0 pages. (253952/253952)

[Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.

[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was freed.

[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 4294967296 to 134217728.

[Note] InnoDB: re-enabled adaptive hash index.

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htm

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL http://www.linuxidc.com/Linux/2014-05/102351.htm

Ubuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htm

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htm

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htm

MySQL-5.5.38通用二进制安装 http://www.linuxidc.com/Linux/2014-07/104509.htm

调整MySQL InnoDB缓冲池大小的方法（无需重启MySQL进程）
BjmmVue的博客
09-26 857
在MySQL数据库中，InnoDB是一种常用的存储引擎，它使用缓冲池（Buffer Pool）来提高读取和写入性能。通过调整InnoDB缓冲池的大小，我们可以优化系统的性能。至此，您已经成功地在线调整了MySQL InnoDB缓冲池的大小，而无需重启MySQL进程。请注意，在高负载环境中进行缓冲池大小的调整可能会对数据库性能产生一定影响。替换为您希望设置的新的缓冲池大小。在调整InnoDB缓冲池大小之前，我们需要获取当前的缓冲池大小。希望本文对您有所帮助！确保新的缓冲池大小与您在第2步中设置的值相匹配。
MySQL参数 之 innodb_buffer_pool_size
心有猛虎 细嗅蔷薇
03-26 1万+
Innodb_buffer_pool_size 《深入浅出MySQL》一文中这样描述Innodb_buffer_pool_size： 该参数定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同，MyISAM 的 key_buffer_size只缓存索引键， 而 innodb_buffer_pool_size 却是同时为数据块和索引块做...
解决：Mysql exceeds the lock table size 不指定
xiao4816的博客
04-12 1002
故障现象 view plainprint? mysql> delete FROM `tablesname` WHERE datetime ERROR 1206 (HY000): The total number of locks exceeds the lock table size   解决步骤 1、锁表写操作 view plainprint? mysql> lock ta
数据库参数调优：innodb_buffer_pool_size 与 thread_cache_size 的配置技巧
最新发布
2503_92604243的博客
07-30 621
因此，在配置时，需要根据 max_connections 的大小来合理设置 thread_cache_size，一般建议 thread_cache_size 为 max_connections 的 10% - 20%。如果没有，则创建新的线程。因此，合理设置 thread_cache_size 的大小，能够减少线程创建和销毁的开销，提升数据库的并发处理能力。如果数据库的连接请求非常频繁，那么需要设置较大的 thread_cache_size，以确保有足够的缓存线程来应对新的连接请求，减少线程创建的次数。
MySQL管理之道-笔记-MySQL5.7-在线调整innodb_buffer_pool_size不用重启mysql进程
weixin_30695195的博客
07-04 312
在线调整innodb_buffer_pool_size不用重启mysql进程MySQL5.7以前，调整innodb_buffer_pool_size需要重启mysql进程才可以生效。建议业务低峰时间执行 1 当前大小128M root@localhost:mysql3306.sock [(none)]>show variables like '%innodb_buffer_pool_...
Mysql 参数 innodb_buffer_pool_size
热门推荐
02-26 2万+
以下考虑主要为 Innodb 引擎, key_buffer_size 不考虑。对于实例级别或线程级别参数设置,暂不考虑。【 innodb_buffer_pool_size 】 用于缓存 索引 和 数据的内存大小, 这个当然是越多越好, 数据读写在内存中非常快, 减少了对磁盘的读写。 当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。然而内存还有操作系统或数据库其他进程使用, 一般设置 b...
【MySQL】MySQL优化-innodb_buffer_pool_size
Ankie_的博客
04-07 1927
首先需要有MySQL的root权限账号，也就是information_schema数据库访问权限；可以通过语句查看Innodb的系统变量参数；可以通过语句查看innodb的运行状态变量；指代的一个内存区域，用来缓存InnoDB存储引擎的表中的数据和索引数据，用来提高对InnoDB存储引擎表中的数据查询和访问速度；该参数以字节(byte)为单位，我自己笔记本的MySQL中的默认是8388608byte，也就是8MB。
Mysql_解决The total number of locks exceeds the lock table size错误
weixin_33826609的博客
03-30 291
在操作mysql数据库表时出现以下错误。 网上google搜索相关问题，发现一位外国牛人这么解释： If you're running an operation on a large number of rows within a table that uses the InnoDB storage engine, you might see this error: ERRO...
mysql的innodb_buffer_pool_size
yao_libao的博客
09-02 598
用于缓存索引和数据的内存大小，数据读写在内存中非常快， 减少了对磁盘的读写。 当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。 参数说明 Innodb_buffer_pool_pages_data缓存池中包含数据的页的数目，包括脏页。单位是page。 e:show global status like 'Innodb_buffer_pool_pages_data'; Innodb_buffer_pool_pages_total的页总数目。单位是page。 eg：show glob.
mysql innodbuffer修改_mysql修改innodb_buffer_pool_size无效
weixin_39943750的博客
01-18 376
mysql在启动的时候总是报2015-06-04 14:44:21 12771 [Note] InnoDB: Initializing buffer pool, size = 128.0MInnoDB: mmap(137363456 bytes) failed; errno 122015-06-04 14:44:21 12771 [ERROR] InnoDB: Cannot allocate me...
mysql5.7 性能优化配置 innodb_buffer_pool_size
05-15 1万+
mysql5.7 性能优化配置 innodb_buffer_pool_size
Mysql查询报错：The total number of locks exceeds the lock table size
weixin_63678046的博客
08-29 1906
innodb_buffer_pool_size默认大小为8388608(即8M ∗ \ast∗ 1024 ∗ \ast∗ 1024),最大值取决于CPU的架构，在32-bit的平台上，最大值为2^32-1, 在64-bit的平台上最大值为2^64-1。这是由于innodb_buffer_pool_size过小导致的。缓冲池大小必须始终等于或是innodb_buffer_pool_chunk_size *innodb_buffer_pool_instances的倍数，若不符合条件，则缓冲池大小将自动调整。
mysql数据库管理-innodb_buffer_pool_size参数
oradbm的博客
10-24 3523
innodb_buffer_pool_size是整个MySQL服务器最重要的变量。 1. 为什么需要innodb buffer pool？ 在MySQL5.5之前，广泛使用的和默认的存储引擎是MyISAM。MyISAM使用操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存。所以非常需要有足够的InnoDB buffer pool空间。 2. MySQL I...
Mysql innodb_buffer_pool_size参数
hbhe0316的专栏
11-27 535
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.htmlhttps://www.cndba.cn/hbhe0316/article/22632 https://www.cndba.cn/hbhe0316/article/22632 https://www.cndba.cn/hbhe0316/article...
MySQL参数优化之innodb_buffer_pool_size
不积跬步，无以至千里；不积小流，无以成江海。
02-16 1817
MySQL参数优化之innodb_buffer_pool_size
在线修改 innodb_buffer_pool_size
04-20
<think>好的，我现在需要帮助用户解决关于MySQL插入性能优化以及如何在线调整innodb_buffer_pool_size的问题。用户之前已经询问了插入465,617条数据耗时85秒是否正常，并得到了相关的优化建议。现在用户进一步希望了解如何在线修改innodb_buffer_pool_size以优化性能。 首先，我需要回顾之前的对话和用户提供的引用内容。用户给出的引用[1]到[4]都涉及innodb_buffer_pool_size的配置和优化，这些信息需要整合到当前的回答中。特别是用户提到在线动态调整这个参数，所以需要确认MySQL版本是否支持，以及具体操作步骤。 接下来，用户的问题可以分为两个部分：1）插入性能是否正常；2）如何在线调整innodb_buffer_pool_size。对于第一部分，之前的回答已经指出当前速度中等偏低，但用户可能希望更详细的基准分析。这里需要结合引用中的信息，比如缓冲池大小对性能的影响，以及如何通过公式计算合适的值。 关于在线调整，根据MySQL的文档，从5.7版本开始支持动态调整innodb_buffer_pool_size，但需要注意调整的步骤和可能的影响。需要确保用户了解调整时可能需要的内存分配时间，以及如何监控调整后的效果。引用中提到缓冲池实例数(innodb_buffer_pool_instances)的设置，需要结合缓冲池大小来配置，比如每个实例至少1GB，所以如果用户调整了pool_size，可能需要相应调整实例数。 同时，用户提到的性能公式（Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100）需要解释，让用户知道如何评估当前缓冲池是否足够，是否需要调整大小。此外，引用中提到的服务器内存占用建议（50%-75%）也需要强调，避免用户设置过大导致内存不足，影响其他服务。 在回答结构上，应该先确认当前性能状态，然后详细说明如何在线调整参数，包括具体步骤、注意事项和验证方法。同时，结合用户的实际应用场景，比如数据抽取时的单线程大事务，可能需要更大的缓冲池，但也要考虑服务器上其他服务的内存需求，如Redis和Kettle，因此需要平衡设置。 另外，用户可能希望在不重启数据库的情况下进行调整，所以需要明确说明动态调整的可行性，以及可能的限制，比如调整时可能会有短暂的性能波动，或者需要分阶段调整以避免内存不足的问题。同时，引用中的案例提到将pool_size设置为16G，但要根据服务器总内存来调整，比如64G的服务器，设置16G可能还有提升空间，但需留出足够内存给其他服务。 最后，需要生成相关问题，帮助用户进一步了解其他优化方法，比如缓冲池实例的配置、监控工具的使用，以及不同存储引擎的影响。这些问题应该与用户之前的提问和当前主题相关，提供连贯的学习路径。</think>#### MySQL插入性能评估与`innodb_buffer_pool_size`动态调整指南 --- ### 一、插入性能基准分析 **当前性能**：插入$465,617$条数据耗时$85$秒（约$5,480$条/秒），在以下场景中属于**中等偏低**水平： - **理想场景**：批量插入+优化配置可达$10,000-50,000$条/秒 - **瓶颈可能**：事务提交频率、索引维护、缓冲池命中率[^1] --- ### 二、`innodb_buffer_pool_size`在线调整方法 #### 1. **动态调整条件**（MySQL 5.7+） ```sql -- 查看当前缓冲池配置 SHOW VARIABLES LIKE 'innodb_buffer_pool%'; -- 在线调整缓冲池大小（需SUPER权限） SET GLOBAL innodb_buffer_pool_size = 16106127360; -- 15GB（单位：字节） ``` **关键限制**： - 调整耗时与内存分配速度相关（每GB约需$2-5$秒） - 需保证`innodb_buffer_pool_chunk_size`是总大小的公约数[^2] #### 2. **配置优化建议** | 服务器内存 | 推荐比例 | 计算公式（参考[^3]） | |------------|----------|----------------------| | ≤32GB | 50-75% | `256MB + 256 * log2(RAM_GB)` | | >32GB | 70-80% | 直接按比例分配 | **示例**：64GB内存服务器运行其他服务（Redis、Kettle）： ```ini innodb_buffer_pool_size = 16G # 64GB的25%（留出其他服务内存） innodb_buffer_pool_instances = 8 # 每个实例2GB（需≥1GB/实例）[^4] ``` #### 3. **性能验证指标** ```sql -- 计算缓冲池命中率（目标：>99%） SELECT (1 - (Variable_value / (Variable_value + (SELECT Variable_value FROM information_schema.global_status WHERE Variable_name = 'Innodb_buffer_pool_read_requests')))) * 100 AS hit_rate FROM information_schema.global_status WHERE Variable_name = 'Innodb_buffer_pool_reads'; ``` **优化效果**：命中率若低于$95\%$，需增大缓冲池[^3] --- ### 三、插入性能综合优化方案 #### 1. **缓冲池协同优化** | 操作步骤 | 具体实现 | |---------------------------|--------------------------------------------------------------------------| | 1. 临时禁用双写缓冲 | `SET GLOBAL innodb_doublewrite = 0;`（仅限数据导入场景） | | 2. 调整日志刷新策略 | `SET GLOBAL innodb_flush_log_at_trx_commit = 2;` | | 3. 批量插入事务控制 | 每$1,000-10,000$条提交一次事务 | #### 2. **硬件与配置联动优化** ```ini # my.cnf配置文件优化（需重启） innodb_io_capacity = 2000 # SSD建议值 innodb_thread_concurrency = 0 # 自动调整线程数 innodb_write_io_threads = 16 # 提升写入并行度 ``` --- ### 四、在线调整风险与规避 1. **内存碎片风险** - 调整后执行`ALTER TABLE ... ENGINE=InnoDB`重建表 2. **服务中断风险** - 选择业务低峰期操作 3. **性能抖动规避** - 分阶段调整（例如：8GB → 12GB → 16GB） --- ### 五、性能优化效果对比 | 优化措施 | 插入速度提升 | 适用场景 | |-------------------------|--------------|------------------------------| | 缓冲池从8GB调至16GB | 20-30% | 频繁数据更新的OLTP系统 | | 批量插入+事务合并 | 50-70% | 数据迁移或ETL作业 | | 禁用二级索引+文件导入 | 80-90% | 初始化数据装载 | --- ### 相关问题 1. 如何通过`SHOW ENGINE INNODB STATUS`分析缓冲池利用率？ 2. `innodb_buffer_pool_instances`配置是否会影响并发写入性能？ 3. 使用`LOAD DATA INFILE`时如何配置`bulk_insert_buffer_size`？
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值