一、缓冲池
缓冲池是主内存中的一个区域，InnoDB在访问表和索引数据时将其缓存。缓冲池允许直接从内存访问经常使用的数据，从而加快处理速度。在专用服务器上，高达80%的物理内存通常分配给缓冲池。
为了提高大容量读取操作的效率，缓冲池被划分为可能容纳多行的页面。为了提高缓存管理的效率，缓冲池被实现为页面的链接列表；很少使用的数据会使用最不常用（LRU）算法的变体从缓存中过时。
了解如何利用缓冲池将频繁访问的数据保存在内存中是MySQL调优的一个重要方面。
二、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系统上可能是一个问题。
初始化缓冲池的时间大致与其大小成正比。在具有大型缓冲池的实例上，初始化时间可能很长。要缩短初始化周期，可以在服务器关闭时保存缓冲池状态，并在服务器启动时恢复。
当增加或减少缓冲池大小时，该操作将分块执行。区块大小由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变量报告在线缓冲池大小调整操作的状态。
innodb_buffer_pool_chunk_size 默认是128M
innodb_buffer_pool_instances默认是8（如果innodb_buffer_pool_size < 1GB，则是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 |
+-------------------------------------+----------------+
四、优化
把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 |
+-------------------------------------+----------------+
- 这些参数也支持在线调整，可考虑在业务低峰时调整。
五、查看优化配置是否合适
- 查询缓存命中率
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的值。
- 缓存数据页占比
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的值。
- 如何判断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
若该值不超过系统可用内存，说明还好（理论）
网友评论