mysql5.7 性能优化配置 innodb_buffer_pool_size

一、缓冲池

​​​​​14.5.1 Buffer Pool

缓冲池是主内存中的一个区域，InnoDB在访问表和索引数据时将其缓存。缓冲池允许直接从内存访问经常使用的数据，从而加快处理速度。在专用服务器上，高达80%的物理内存通常分配给缓冲池。

为了提高大容量读取操作的效率，缓冲池被划分为可能容纳多行的页面。为了提高缓存管理的效率，缓冲池被实现为页面的链接列表；很少使用的数据会使用最不常用（LRU）算法的变体从缓存中过时。

了解如何利用缓冲池将频繁访问的数据保存在内存中是MySQL调优的一个重要方面。

二、innodb_buffer_pool_size

14.8.3.1 Configuring InnoDB Buffer Pool Size

innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

innodb_buffer_pool_size默认是128M，

缓冲池的大小（字节），InnoDB缓存表和索引数据的内存区域。默认值为134217728字节（128MB）。最大值取决于CPU架构；32位系统的最大值为4294967295（232-1），64位系统的最大值为18446744073709551615（264-1）。在32位系统上，CPU体系结构和操作系统的实际最大大小可能低于规定的最大大小。当缓冲池的大小大于1GB时，将innodb_buffer_pool_instances设置为大于1的值可以提高繁忙服务器上的可伸缩性。

更大的缓冲池需要更少的磁盘I/O来多次访问相同的表数据。在专用数据库服务器上，可以将缓冲池大小设置为机器物理内存大小的80%。在配置缓冲池大小时，请注意以下潜在问题，并准备在必要时缩小缓冲池的大小。

对物理内存的竞争可能会导致操作系统中的分页。

InnoDB为缓冲区和控制结构保留额外的内存，因此总分配空间比指定的缓冲池大小大约大10%。

缓冲池的地址空间必须是连续的，这在具有在特定地址加载DLL的Windows系统上可能是一个问题。

初始化缓冲池的时间大致与其大小成正比。在具有大型缓冲池的实例上，初始化时间可能很长。要缩短初始化周期，可以在服务器关闭时保存缓冲池状态，并在服务器启动时恢复。参见第14.8.3.6节“保存和恢复缓冲池状态”。

当增加或减少缓冲池大小时，该操作将分块执行。区块大小由innodb_buffer_pool_chunk_size变量定义，该变量的默认值为128 MB。

缓冲池大小必须始终等于或是innodb_buffer_pool_chunk_sizeinnodb_buffer_pool_instances的倍数。如果将缓冲池大小更改为不等于innodb_buffer_pool_chunk_sizeinnodb_buffer_pool_instances的值或其倍数，缓冲池大小将自动调整为等于或其倍数的值。

innodb_buffer_pool_size可以动态设置，这允许您在不重新启动服务器的情况下调整缓冲池的大小。Innodb_buffer_pool_resize_status变量报告在线缓冲池大小调整操作的状态。有关更多信息，请参阅第14.8.3.1节“配置InnoDB缓冲池大小”。

innodb_buffer_pool_chunk_size 默认是128M

innodb_buffer_pool_instances默认是8（如果innodb_buffer_pool_size < 1GB，则是1）

14.8.3.2 Configuring Multiple Buffer Pool Instances

2.1查看现有配置

mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+

2.2简单优化

把innodb_buffer_pool_size设置为1G。

个人建议innodb_buffer_pool_size设置为系统内存的50%。

最好设置为：innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

否则，innodb_buffer_pool_size自动调整可能是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的两倍。

my.cnf

# innodb缓冲池大小
innodb_buffer_pool_size=1G

# innodb缓冲池块大小
innodb_buffer_pool_chunk_size=128M

# innodb缓冲池实例数
innodb_buffer_pool_instances=8

重启数据库

调整后：

mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 1073741824 |
+-------------------------------------+----------------+

这些参数也支持在线调整，可考虑在业务低峰时调整。

Configuring InnoDB Buffer Pool Size Online

2.3配置是否合适

5.1.3 Server Option, System Variable, and Status Variable Reference

2.3.1查询缓存命中率：

mysql> show status like 'Innodb_buffer_pool_read%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 20294922 |
| Innodb_buffer_pool_read_ahead_evicted | 1240192 |
| Innodb_buffer_pool_read_requests | 299216558100 |
| Innodb_buffer_pool_reads | 1167281260 |
+---------------------------------------+--------------+

Innodb_buffer_pool_read_requests：逻辑读取请求的数量。
Innodb_buffer_pool_reads：InnoDB无法从缓冲池满足的逻辑读取数，必须直接从磁盘读取。
percent = innodb_buffer_pool_read_requests / (innodb_buffer_pool_reads + innodb_buffer_pool_read_requests) * 100%
上述的 percent>=99%，则表示当前的buffer pool满足当前的需求。否则需要考虑增加 innodb_buffer_pool_size的值。

2.3.2缓存数据页占比：

mysql> show status like 'Innodb_buffer_pool_pages%';
+----------------------------------+----------+
| Variable_name | Value |
+----------------------------------+----------+
| Innodb_buffer_pool_pages_data | 7003 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 19906085 |
| Innodb_buffer_pool_pages_free | 1021 |
| Innodb_buffer_pool_pages_misc | 167 |
| Innodb_buffer_pool_pages_total | 8191 |
+----------------------------------+----------+

innodb_buffer_pool_pages_data：InnoDB缓冲池中包含数据的页数。这个数字包括脏页和干净页。（使用压缩表时，报告的Innodb_buffer_pool_pages_数据值可能大于）
percent = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
上述的 percent>=95% 则表示当前的innodb_buffer_pool_size满足当前的需求。否则可以考虑增加 innodb_buffer_pool_size的值。

2.4如何判断MySQL使用内存会不会过高

可能还有有一些担心，所有参数设置完毕后MySQL的占用会过高导致内存溢出，那么我们可以算一下他会不会太高。
通过下面的SQL语句：
SELECT ((@@key_buffer_size+@@innodb_buffer_pool_size+@@innodb_log_buffer_size)/1024/1024)+((@@read_rnd_buffer_size+@@read_buffer_size+@@myisam_sort_buffer_size+@@sort_buffer_size+@@join_buffer_size)/1024/1024*@@max_connections);
最终单位为MB
若该值不超过系统可用内存，说明还好（理论）

2.5其他命令

mysql> show status like 'Innodb_buffer_pool%';
+---------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 220313 7:31:02 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 6999 |
| Innodb_buffer_pool_bytes_data | 114671616 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 19905034 |
| Innodb_buffer_pool_pages_free | 1024 |
| Innodb_buffer_pool_pages_misc | 168 |
| Innodb_buffer_pool_pages_total | 8191 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 20294410 |
| Innodb_buffer_pool_read_ahead_evicted | 1240164 |
| Innodb_buffer_pool_read_requests | 299111990637 |
| Innodb_buffer_pool_reads | 1167212424 |
| Innodb_buffer_pool_wait_free | 1193110 |
| Innodb_buffer_pool_write_requests | 156029072 |
+---------------------------------------+--------------------------------------------------+

mysql> show engine innodb status \G
mysql> SHOW GLOBAL STATUS \G 太多了。

三、其他待优化：

join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M

mysql> show variables like '%buffer_size%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
+-------------------------+----------+

四、参考：

Mysql优化之innodb_buffer_pool_size篇

MySQL参数 之 innodb_buffer_pool_size

MySQL中innodb_buffer_pool_size的配置

MySQL基准测试innodb_buffer_pool_size对性能影响

五、文档：

Chapter 8 Optimization

8.1 Optimization Overview

8.2 Optimizing SQL Statements

8.3 Optimization and Indexes

8.4 Optimizing Database Structure

8.5 Optimizing for InnoDB Tables

8.6 Optimizing for MyISAM Tables

8.7 Optimizing for MEMORY Tables

8.8 Understanding the Query Execution Plan

8.9 Controlling the Query Optimizer

8.10 Buffering and Caching

8.11 Optimizing Locking Operations

8.12 Optimizing the MySQL Server

    8.12.4.1 How MySQL Uses Memory

8.13 Measuring Performance (Benchmarking)

8.14 Examining Server Thread (Process) Information

8.5 Optimizing for InnoDB Tables

8.5.1 Optimizing Storage Layout for InnoDB Tables

8.5.2 Optimizing InnoDB Transaction Management

8.5.3 Optimizing InnoDB Read-Only Transactions

8.5.4 Optimizing InnoDB Redo Logging

8.5.5 Bulk Data Loading for InnoDB Tables

8.5.6 Optimizing InnoDB Queries

8.5.7 Optimizing InnoDB DDL Operations

8.5.8 Optimizing InnoDB Disk I/O

8.5.9 Optimizing InnoDB Configuration Variables

8.5.10 Optimizing InnoDB for Systems with Many Tables

14.8.3 InnoDB Buffer Pool Configuration

14.8.3.1 Configuring InnoDB Buffer Pool Size

14.8.3.2 Configuring Multiple Buffer Pool Instances

14.8.3.3 Making the Buffer Pool Scan Resistant

14.8.3.4 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)

14.8.3.5 Configuring Buffer Pool Flushing

14.8.3.6 Saving and Restoring the Buffer Pool State