MySQL 的 innodb_buffer_pool_size 参数配置指南

innodb_buffer_pool_size是MySQL InnoDB存储引擎最重要的参数之一，它直接影响数据库的性能。这个参数决定了InnoDB缓存表数据和索引数据的内存大小。

基本配置原则

1. 一般建议：将innodb_buffer_pool_size设置为服务器物理内存的50%-80%
2. 小型系统：如果是小型系统或MySQL与其他服务共享服务器，可以设置为25%-50%
3. 专用数据库服务器：如果服务器专门用于MySQL，可以设置为物理内存的80%左右

具体配置方法

在my.cnf或my.ini文件中设置：

[mysqld]
innodb_buffer_pool_size = 8G  # 例如设置为8GB

注意事项

1. 避免过大：不要将此值设置得太大，否则可能导致操作系统开始使用交换空间，严重降低性能
2. 在线调整：MySQL 5.7及以上版本支持动态调整此参数，无需重启服务器：

   SET GLOBAL innodb_buffer_pool_size = 8589934592;  # 设置为8GB (以字节为单位)
   

3. 多个缓冲池实例：对于大内存服务器，建议配置多个缓冲池实例以提高并发性能：

   innodb_buffer_pool_instances = 8  # 例如设置为8个实例
   

4. 监控使用情况：通过以下命令监控缓冲池的使用情况：

   SHOW ENGINE INNODB STATUS\G
   SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;
   

5. 预热功能：考虑使用InnoDB缓冲池预热功能，特别是对于频繁重启的服务器：

   innodb_buffer_pool_dump_at_shutdown = 1
   innodb_buffer_pool_load_at_startup = 1
   

最佳的设置取决于您的工作负载特性、服务器配置和其他运行在同一系统上的应用程序。建议在调整后监控系统性能，并根据实际情况进行微调。

MariaDB

针对 MairaDB，修改配置文件路径为： nano /etc/mysql/mariadb.conf.d/50-server.cnf

针对我们的服务器，我们的的这个配置被修改成了 24G。

查询 Pool 大小：

SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;

INNODB_BUFFER_POOL_STATS

上面的配置参数为生产服务器上的真实参数。

引擎状态查询

SHOW ENGINE INNODB STATUS

上面的命令能够查询当前引擎的使用状态：

=====================================
2025-03-13 18:34:22 0x75a2fc5606c0 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 42 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 10 srv_active, 0 srv_shutdown, 196110 srv_idle
srv_master_thread log flush and writes: 196115
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 1235652
Purge done for trx's n:o < 1235652 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (0x75a940405880), not started
mysql tables in use 1, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x75a940404d80), ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
MariaDB thread id 5221894, OS thread handle 129343170938560, query id 399272277 ns564012.ip-54-39-157.net 54.39.157.60 src Sending data
SELECT count(*) FROM `release` WHERE repo_id=? AND sha1<>?
Trx read view will not see trx with id >= 1235652, sees < 1235652
---TRANSACTION (0x75a940401680), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync): 0
74899 OS file reads, 93031 OS file writes, 90571 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.36 writes/s, 0.36 fsyncs/s
---
LOG
---
Log sequence number 2828162553
Log flushed up to   2828162553
Pages flushed up to 2765805507
Last checkpoint at  2765805507
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 25803358208
Dictionary memory allocated 155238072
Buffer pool size   1557632
Free buffers       1486272
Database pages     70609
Old database pages 26044
Modified db pages  2601
Percent of dirty pages(LRU & free pages): 0.167
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 47083, not young 511720
0.00 youngs/s, 0.00 non-youngs/s
Pages read 73858, created 4808, written 3023
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 70609, unzip_LRU len: 1504
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
1 read views open inside InnoDB
state: sleeping
----------------------------
END OF INNODB MONITOR OUTPUT
============================

数据库的参数，通常可以用查询来了解。

MySQL的innodb_buffer_pool_size参数优化配置指南 - #2 by hex - 数据库 - iSharkFly