MySQL大数据量插入数据参数优化

痛定思痛。 2024-03-30 12:14 95阅读 0赞

关于一些配置项优化  
\[client\]  
port = 3306  
socket = /var/lib/mysql/mysql.sock

\[mysql.server\]  
pid-file = /var/lib/mysql/localhost.pid

\[mysqld\]  
port = 3306  
socket =/var/lib/mysql/mysql.sock

\# 网络传输中一次消息传输量的最大值。系统默认值 为1MB，最大值是1GB，必须设置1024的倍数。  
\# 因为后续我们在代码中是使用prepareStatement.addBatch()，单次传输的值会比较大，所以这个参数必须加大。  
max\_allowed\_packet = 32M  
\# 缓冲池大小，只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。  
innodb\_buffer\_pool\_size = 5120M  
innodb\_buffer\_pool\_instances=3  
innodb\_additional\_mem\_pool\_size = 1024M  
innodb\_log\_files\_in\_group = 3  
innodb\_lock\_wait\_timeout = 120  
\# 刷新日志频率  
open\_files\_limit = 10240  
back\_log = 600  
innodb\_log\_file\_size=110M  
\# 连接数  
max\_connections = 3000  
max\_connect\_errors = 6000  
\# 多线程  
thread\_cache\_size = 300  
thread\_concurrency = 16  
thread\_stack = 192KB  
innodb\_write\_io\_threads = 32  
innodb\_purge\_threads=1  
\# 刷新脏页频率  
innodb\_max\_dirty\_pages\_pct=90  
wait\_timeout=300

详细的参数配置

1、mysql配置优化

2、 如何在MySQL中分配innodb\_buffer\_pool\_size

详细如下说明:

1、mysql配置优化

#### 一、全局配置 ####

（1）**max\_connections**  
**最大连接数。默认值是151，最多2000**。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量。但是如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值。  
查看最大连接数

mysql> SHOW VARIABLES LIKE 'max_connections';

查看响应的连接数

mysql> SHOW STATUS LIKE 'max%connections';

**max\_used\_connections / max\_connections \* 100% （理想值≈85%）**   
**如果max\_used\_connections跟max\_connections相同 那么就是max\_connections设置过低或者超过服务器负载上限了**，**低于10%则设置过大。**  
**（2）back\_log**  
**MySQL能暂存的连接数量，默认值是80，最多512，可设置为128**。如果MySQL的连接数据达到max\_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，***该堆栈的数量即back\_log***。如果等待连接的数量超过back\_log，将不被授予连接资源。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。**类似于线程池**  
**（3）key\_buffer\_size**  
索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。  
通过检查状态值Key\_read\_requests和Key\_reads，可以知道key\_buffer\_size设置是否合理。

mysql> SHOW STATUS LIKE 'key_read%';
    +-------------------+----------+
    | Variable_name     | Value    |
    +-------------------+----------+
    | Key_read_requests | 90585564 |
    | Key_reads         | 97031    |
    +-------------------+----------+

计算索引未命中缓存的概率：  
key\_cache\_miss\_rate = Key\_reads / Key\_read\_requests \* 100%，设置在1/1000左右较好  
key\_buffer\_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。  
默认配置数值是8388608(8M)，主机有4GB内存，可改为268435456(256M)  
（**4）query\_cache\_size**  
使用查询缓存(query cache)，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。  
**最佳选项是将其从一开始就停用，设为0**（现在MySQL 5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如Redis或Memcached）。  
通过检查状态值qcache\_\*，可以知道query\_cache\_size设置是否合理

mysql> SHOW STATUS LIKE 'qcache%';
    +-------------------------+----------+
    | Variable_name           | Value    |
    +-------------------------+----------+
    | Qcache_free_blocks      | 1        |
    | Qcache_free_memory      | 1031360  |
    | Qcache_hits             | 0        |
    | Qcache_inserts          | 0        |
    | Qcache_lowmem_prunes    | 0        |
    | Qcache_not_cached       | 10302865 |
    | Qcache_queries_in_cache | 0        |
    | Qcache_total_blocks     | 1        |
    +-------------------------+----------+

查询缓存碎片率 = Qcache\_free\_blocks / Qcache\_total\_blocks \* 100%  
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query\_cache\_min\_res\_unit，如果你的查询都是小数据量的话。  
查询缓存利用率 = (query\_cache\_size – Qcache\_free\_memory) / query\_cache\_size \* 100%  
查询缓存利用率在25%以下的话说明query\_cache\_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache\_lowmem\_prunes > 50的话说明query\_cache\_size可能有点小，要不就是碎片太多。  
查询缓存命中率 = (Qcache\_hits – Qcache\_inserts) / Qcache\_hits \* 100%  
如果Qcache\_lowmem\_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache\_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache\_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL\_NO\_CACHE可以明确表示不使用查询缓冲。  
与查询缓冲有关的参数还有query\_cache\_type、query\_cache\_limit、query\_cache\_min\_res\_unit。  
query\_cache\_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。  
query\_cache\_limit指定单个查询能够使用的缓冲区大小，缺省为1M。  
query\_cache\_min\_res\_unit指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache\_free\_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query\_cache\_min\_res\_unit。  
（5**）read\_buffer\_size**  
是MySQL读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，read\_buffer\_size变量控制这一缓冲区的大小，如果对表的顺序扫描非常频繁，并你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。  
**默认数值是131072(128K)，可改为16773120(16M)**  
（**6）read\_rnd\_buffer\_size**  
随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。  
默认数值是262144(256K)，可改为16777208(16M)  
（**7）sort\_buffer\_size**  
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。  
默认数值是10485760(1M)，可改为16777208(16M)  
**（8）join\_buffer\_size**  
联合查询操作所能使用的缓冲区大小  
read\_buffer\_size，read\_rnd\_buffer\_size，sort\_buffer\_size，join\_buffer\_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M\*100  
**（9）table\_open\_cache**  
表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。  
通过检查峰值时间的状态值Open\_tables和Opened\_tables，可以决定是否需要增加table\_cache的值。

mysql> SHOW STATUS LIKE 'open%tables';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | Open_tables   | 2000  |
    | Opened_tables | 0     |
    +---------------+-------+

如果open\_tables等于table\_cache，并且opened\_tables在不断增长，那么就需要增加table\_cache的值了。注意，不能盲目地把table\_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。  
1G内存机器，推荐值是128-256。内存在4GB左右的服务器该参数可设置为256M或384M。  
**（10）max\_heap\_table\_size**  
用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。  
这个变量和tmp\_table\_size一起限制了内部内存表的大小。如果某个内部heap（堆积）表大小超过tmp\_table\_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。  
**（11）tmp\_table\_size**  
临时表的大小，例如做高级GROUP BY操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。

mysql> SHOW GLOBAL STATUS LIKE 'created_tmp%';
    +-------------------------+----------+
    | Variable_name           | Value    |
    +-------------------------+----------+
    | Created_tmp_disk_tables | 2884297  |
    | Created_tmp_files       | 870      |
    | Created_tmp_tables      | 15899696 |
    +-------------------------+----------+

每次创建临时表，Created\_tmp\_tables增加，如果临时表大小超过tmp\_table\_size，则是在磁盘上创建临时表，Created\_tmp\_disk\_tables也增加。  
Created\_tmp\_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：  
Created\_tmp\_disk\_tables / Created\_tmp\_tables \* 100% <= 25%  
**（12）thread\_cache\_size**  
线程缓存。当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。

mysql> SHOW STATUS LIKE 'threads%';
    +-------------------+---------+
    | Variable_name     | Value   |
    +-------------------+---------+
    | Threads_cached    | 5       |
    | Threads_connected | 13      |
    | Threads_created   | 1095313 |
    | Threads_running   | 1       |
    +-------------------+---------+

Threads\_cached :代表当前此时此刻线程缓存中有多少空闲线程。如果过大，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加thread\_cache\_size  
Threads\_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。  
Threads\_created :代表从最近一次服务启动，已创建线程的数量。  
Threads\_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。  
建议设置接近Threads\_connected值，再结合物理内存：1G-8；2G-16；3G-32 综合考虑一下值。  
**（13）interactive\_timeout**  
一个交互连接在被服务器在关闭前等待行动的秒数。默认值是28800（8小时），可设置为7200。  
**（14）wait\_timeout**  
一个非交互连接在被服务器在关闭前等待行动的秒数。要同时设置interactive\_timeout和wait\_timeout才会生效。

#### 二、InnoDB配置 ####

**（1）innodb\_buffer\_pool\_size**  
**缓冲池的大小，缓存数据和索引**，**对InnoDB整体性能影响较大，****相当于MyISAM的key\_buffer\_size。如果只用Innodb，可以把这个值设为内存的70%-80%。越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。**  
**（2）innodb\_log\_buffer\_size**   
**尚未执行的事务的缓存大小，默认值为8M，**一般8M-16M。**如果你有很多事务的更新，插入或删除操作，通过这个参数会大量的节省了磁盘I/O**。但是如果你的事务中包含有二进制大对象或者大文本字段的话，这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb\_log\_waits状态变量，如果它不是0，应该增大这个值。但太大了也是浪费内存，因为1秒钟总会flush一次，所以不需要设到超过1秒的需求。  
**（3）innodb\_flush\_log\_at\_trx\_commit**  
把log buffer的数据写入日志文件并flush磁盘的策略，该值对插入数据的速度影响非常大。取值分别为0、1(默认值)、2(推荐值)  
0：事务提交时，不写入磁盘，而是每秒把log buffer的数据写入日志文件，并且flush(刷到磁盘)。速度最快，但不安全。mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。  
1：每次事务提交时把log buffer的数据写入日志文件，并且flush(刷到磁盘)。最安全，但也最慢。确保了事务的ACID。  
2：每次事务提交时把log buffer的数据写入日志文件，每秒flush(刷到磁盘)。速度较快，比0安全。操作系统崩溃或者系统断电会导致上一秒钟所有事务数据的丢失。  
**（4）innodb\_log\_file\_size**  
在一个日志组每个日志文件的大小，用于确保写操作快速而可靠并且在崩溃时恢复。一般用64M-512M，具体取决于服务器的空间。大的文件提供更高的性能，但数据库恢复时会用更多的时间。  
**（5）innodb\_additional\_mem\_pool\_size**  
存储数据字典和其他内部数据结构的内存池大小。默认为1M，对于2G内存的机器，推荐值是20M，通常不用太大，应该与表结构的复杂度有关系。如果不够用，MySQL会在错误日志中写入一条警告信息。  
**（6）innodb\_buffer\_pool\_instances**  
可以开启多个内存缓冲池，这样可以并行的内存读写。默认为8，一般为1-8。最常1s就会刷新一次，故不用太大。对于较大的事务，可以增大缓存大小。如果InnoDB缓存池被划分成多个区域，建议每个区域不小于1GB的空间。

2、 如何在MySQL中分配innodb\_buffer\_pool\_size

`innodb_buffer_pool_size`是整个MySQL服务器最重要的变量。

### 1. 为什么需要innodb buffer pool？ ###

在MySQL5.5之前，广泛使用的和默认的存储引擎是MyISAM。MyISAM使用操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存。所以非常需要有足够的InnoDB buffer pool空间。

### 2. MySQL InnoDB buffer pool 里包含什么？ ###

*  **数据缓存**  
    InnoDB数据页面
 *  **索引缓存**  
    索引数据
 *  **缓冲数据**  
    脏页（在内存中修改尚未刷新(写入)到磁盘的数据）
 *  **内部结构**  
    如自适应哈希索引，行锁等。

### 3. 如何设置innodb\_buffer\_pool\_size? ###

`innodb_buffer_pool_size`默认大小为128M。最大值取决于CPU的架构。在32-bit平台上，最大值为`2**32 -1`,在64-bit平台上最大值为`2**64-1`。当缓冲池大小大于1G时，将`innodb_buffer_pool_instances`设置大于1的值可以提高服务器的可扩展性。

大的缓冲池可以减小多次磁盘I/O访问相同的表数据。在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的80%。

#### 3.1 配置缓冲池大小时，请注意以下潜在问题 ####

*  物理内存争用可能导致操作系统频繁的paging
 *  InnoDB为缓冲区和control structures保留了额外的内存，因此总分配空间比指定的缓冲池大小大约大10％。
 *  缓冲池的地址空间必须是连续的，这在带有在特定地址加载的DLL的Windows系统上可能是一个问题。
 *  初始化缓冲池的时间大致与其大小成比例。在具有大缓冲池的实例上，初始化时间可能很长。要减少初始化时间，可以在服务器关闭时保存缓冲池状态，并在服务器启动时将其还原。
    
     *  **`innodb_buffer_pool_dump_pct`**：指定每个缓冲池最近使用的页面读取和转储的百分比。 范围是1到100。默认值是25。例如，如果有4个缓冲池，每个缓冲池有100个page，并且innodb\_buffer\_pool\_dump\_pct设置为25，则dump每个缓冲池中最近使用的25个page。
     *  **`innodb_buffer_pool_dump_at_shutdown`**：默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程。
     *  **`innodb_buffer_pool_load_at_startup`**：默认启用。指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb\_buffer\_pool\_dump\_at\_shutdown结合使用。

增大或减小缓冲池大小时，将以chunk的形式执行操作。chunk大小由`innodb_buffer_pool_chunk_size`配置选项定义，默认值为128 MB。

缓冲池大小必须始终等于或者是`innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances`的倍数。  
如果将缓冲池大小更改为不等于或等于`innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances`的倍数的值，  
则缓冲池大小将自动调整为等于或者是`innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances`的倍数的值。

innodb\_buffer\_pool\_size可以动态设置，允许在不重新启动服务器的情况下调整缓冲池的大小。 可以通过状态变量`Innodb_buffer_pool_resize_status`报告在线调整缓冲池大小操作的状态。

#### 3.2 配置示例 ####

在以下示例中，`innodb_buffer_pool_size`设置为3G，`innodb_buffer_pool_instances`设置为8。`innodb_buffer_pool_chunk_size默认值为`128M。

3G是有效的innodb\_buffer\_pool\_size值，因为3G是`innodb_buffer_pool_instances = 8 * innodb_buffer_pool_chunk_size = 128M`的倍数

在以下示例中，`innodb_buffer_pool_size`设置为3G，`innodb_buffer_pool_instances`设置为16. `innodb_buffer_pool_chunk_size`为128M。

3G不是有效的innodb\_buffer\_pool\_size值，因为3G不是`innodb_buffer_pool_instances = 16 * innodb_buffer_pool_chunk_size = 128M`的倍数，可以看出`innodb_buffer_pool_size`的值自动调整到4GB

#### 3.3 在线调整InnoDB缓冲池大小 ####

mysql> SET GLOBAL innodb_buffer_pool_size = 3221225472

#### 3.4 监控在线缓冲池调整进度 ####

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
    +----------------------------------+----------------------------------------------------+
    | Variable_name                    | Value                                              |
    +----------------------------------+----------------------------------------------------+
    | Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 180824 15:05:03. |
    +----------------------------------+----------------------------------------------------+

缓冲池大小调整进度也记录在服务器错误日志中。以下为增大时，记录的日志

2018-08-24T07:05:03.819049Z 2 [Note] InnoDB: Requested to resize buffer pool. (new size: 3221225472 bytes)
    2018-08-24T07:05:03.819141Z 0 [Note] InnoDB: Resizing buffer pool from 2684354560 to 3221225472 (unit=134217728).
    2018-08-24T07:05:03.819155Z 0 [Note] InnoDB: Disabling adaptive hash index.
    2018-08-24T07:05:03.824902Z 0 [Note] InnoDB: disabled adaptive hash index.
    2018-08-24T07:05:03.824933Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
    2018-08-24T07:05:03.824940Z 0 [Note] InnoDB: Latching whole of buffer pool.
    2018-08-24T07:05:03.824959Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 5 to 6.
    2018-08-24T07:05:03.839564Z 0 [Note] InnoDB: buffer pool 0 : 1 chunks (8192 blocks) were added.
    2018-08-24T07:05:03.839594Z 0 [Note] InnoDB: buffer pool 1 : resizing with chunks 5 to 6.
    2018-08-24T07:05:03.848910Z 0 [Note] InnoDB: buffer pool 1 : 1 chunks (8192 blocks) were added.
    2018-08-24T07:05:03.849046Z 0 [Note] InnoDB: buffer pool 2 : resizing with chunks 5 to 6.
    2018-08-24T07:05:03.856711Z 0 [Note] InnoDB: buffer pool 2 : 1 chunks (8192 blocks) were added.
    2018-08-24T07:05:03.856741Z 0 [Note] InnoDB: buffer pool 3 : resizing with chunks 5 to 6.
    2018-08-24T07:05:03.864867Z 0 [Note] InnoDB: buffer pool 3 : 1 chunks (8192 blocks) were added.
    2018-08-24T07:05:03.864902Z 0 [Note] InnoDB: Completed to resize buffer pool from 2684354560 to 3221225472.
    2018-08-24T07:05:03.864915Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
    2018-08-24T07:05:03.864935Z 0 [Note] InnoDB: Completed resizing buffer pool at 180824 15:05:03.

以下为减小时，记录的日志

2018-08-24T07:10:20.666816Z 2 [Note] InnoDB: Requested to resize buffer pool. (new size: 2684354560 bytes)
    2018-08-24T07:10:20.666880Z 0 [Note] InnoDB: Resizing buffer pool from 3221225472 to 2684354560 (unit=134217728).
    2018-08-24T07:10:20.666889Z 0 [Note] InnoDB: Disabling adaptive hash index.
    2018-08-24T07:10:20.673416Z 0 [Note] InnoDB: disabled adaptive hash index.
    2018-08-24T07:10:20.673508Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
    2018-08-24T07:10:20.673519Z 0 [Note] InnoDB: buffer pool 0 : start to withdraw the last 8192 blocks.
    2018-08-24T07:10:20.678441Z 0 [Note] InnoDB: buffer pool 0 : withdrawing blocks. (8192/8192)
    2018-08-24T07:10:20.678521Z 0 [Note] InnoDB: buffer pool 0 : withdrew 8192 blocks from free list. Tried to relocate 0 pages (8192/8192).
    2018-08-24T07:10:20.678919Z 0 [Note] InnoDB: buffer pool 0 : withdrawn target 8192 blocks.
    2018-08-24T07:10:20.678977Z 0 [Note] InnoDB: buffer pool 1 : start to withdraw the last 8192 blocks.
    2018-08-24T07:10:20.681644Z 0 [Note] InnoDB: buffer pool 1 : withdrawing blocks. (8192/8192)
    2018-08-24T07:10:20.682168Z 0 [Note] InnoDB: buffer pool 1 : withdrew 8192 blocks from free list. Tried to relocate 0 pages (8192/8192).
    2018-08-24T07:10:20.682235Z 0 [Note] InnoDB: buffer pool 1 : withdrawn target 8192 blocks.
    2018-08-24T07:10:20.682254Z 0 [Note] InnoDB: buffer pool 2 : start to withdraw the last 8192 blocks.
    2018-08-24T07:10:20.686560Z 0 [Note] InnoDB: buffer pool 2 : withdrawing blocks. (8192/8192)
    2018-08-24T07:10:20.686917Z 0 [Note] InnoDB: buffer pool 2 : withdrew 8192 blocks from free list. Tried to relocate 0 pages (8192/8192).
    2018-08-24T07:10:20.687002Z 0 [Note] InnoDB: buffer pool 2 : withdrawn target 8192 blocks.
    2018-08-24T07:10:20.687010Z 0 [Note] InnoDB: buffer pool 3 : start to withdraw the last 8192 blocks.
    2018-08-24T07:10:20.690038Z 0 [Note] InnoDB: buffer pool 3 : withdrawing blocks. (8192/8192)
    2018-08-24T07:10:20.690373Z 0 [Note] InnoDB: buffer pool 3 : withdrew 8192 blocks from free list. Tried to relocate 0 pages (8192/8192).
    2018-08-24T07:10:20.690433Z 0 [Note] InnoDB: buffer pool 3 : withdrawn target 8192 blocks.
    2018-08-24T07:10:20.690479Z 0 [Note] InnoDB: Latching whole of buffer pool.
    2018-08-24T07:10:20.690498Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 6 to 5.
    2018-08-24T07:10:20.693293Z 0 [Note] InnoDB: buffer pool 0 : 1 chunks (8192 blocks) were freed.
    2018-08-24T07:10:20.693357Z 0 [Note] InnoDB: buffer pool 1 : resizing with chunks 6 to 5.
    2018-08-24T07:10:20.695947Z 0 [Note] InnoDB: buffer pool 1 : 1 chunks (8192 blocks) were freed.
    2018-08-24T07:10:20.696011Z 0 [Note] InnoDB: buffer pool 2 : resizing with chunks 6 to 5.
    2018-08-24T07:10:20.698977Z 0 [Note] InnoDB: buffer pool 2 : 1 chunks (8192 blocks) were freed.
    2018-08-24T07:10:20.699288Z 0 [Note] InnoDB: buffer pool 3 : resizing with chunks 6 to 5.
    2018-08-24T07:10:20.702088Z 0 [Note] InnoDB: buffer pool 3 : 1 chunks (8192 blocks) were freed.
    2018-08-24T07:10:20.702398Z 0 [Note] InnoDB: Completed to resize buffer pool from 3221225472 to 2684354560.
    2018-08-24T07:10:20.702413Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
    2018-08-24T07:10:20.703896Z 0 [Note] InnoDB: Completed resizing buffer pool at 180824 15:10:20.

### 4. 配置的innodb\_buffer\_pool\_size是否合适？ ###

当前配置的innodb\_buffer\_pool\_size是否合适，可以通过分析InnoDB缓冲池的性能来验证。

可以使用以下公式计算InnoDB缓冲池性能：

`Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100`

**`innodb_buffer_pool_reads`**：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。

**`innodb_buffer_pool_read_requests`**：表示从内存中读取逻辑的请求数。

例如，在我的服务器上，检查当前InnoDB缓冲池的性能：

意味着InnoDB可以满足缓冲池本身的大部分请求。从磁盘完成读取的百分比非常小。因此无需增加innodb\_buffer\_pool\_size值。

#### 4.1 什么时候减小innodb\_buffer\_pool\_size？ ####

在专用MySQL服务器上，多余的innodb\_buffer内存不会有问题，但是当使用共享服务器时，可能会有性能影响。因为空闲内存对其他程序和操作系统很有用。

可以使用`SHOW ENGINE INNODB STATUS\G`命令检查内存状态：

mysql> show engine innodb status\G
    
    ...
    Total large memory allocated 26386366464
    Dictionary memory allocated 23826297
    Buffer pool size   1572672
    Free buffers       8192
    Database pages     1553364
    Old database pages 573246
    Modified db pages  36
    Pending reads      0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 881819, not young 18198964
    0.02 youngs/s, 0.05 non-youngs/s
    Pages read 681064, created 2749237, written 3988300
    0.02 reads/s, 0.12 creates/s, 11.50 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: 1553364, unzip_LRU len: 0
    I/O sum[5152]:cur[0], unzip sum[0]:cur[0]
    ...

**Free buffers** :表示有多少空闲buffer。如果 此值长时间都较高，则可以考虑减小InnoDB缓冲池大小。

**InnoDB buffer pool 命中率：**

`InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100`

此值低于99%，则可以考虑增加innodb\_buffer\_pool\_size。

### 5. InnoDB缓冲池状态变量有哪些？ ###

可以运行以下命令进行查看：

root@localhost [(none)] 16:00:31>show global status like '%innodb_buffer_pool_pages%';
    +----------------------------------+--------+
    | Variable_name                    | Value  |
    +----------------------------------+--------+
    | Innodb_buffer_pool_pages_data    | 457    |
    | Innodb_buffer_pool_pages_dirty   | 0      |
    | Innodb_buffer_pool_pages_flushed | 36     |
    | Innodb_buffer_pool_pages_free    | 163363 |
    | Innodb_buffer_pool_pages_misc    | 0      |
    | Innodb_buffer_pool_pages_total   | 163820 |
    +----------------------------------+--------+
    6 rows in set (0.00 sec)

**说明**：

*  **Innodb\_buffer\_pool\_pages\_data**  
    InnoDB缓冲池中包含数据的页数。 该数字包括脏页面和干净页面。 使用压缩表时，报告的Innodb\_buffer\_pool\_pages\_data值可能大于Innodb\_buffer\_pool\_pages\_total（Bug＃59550）。

*  **Innodb\_buffer\_pool\_pages\_dirty**  
    显示在内存中修改但尚未写入数据文件的InnoDB缓冲池数据页的数量（脏页刷新）。
 *  **Innodb\_buffer\_pool\_pages\_flushed**  
    表示从InnoDB缓冲池中刷新脏页的请求数。
 *  **Innodb\_buffer\_pool\_pages\_free**  
    显示InnoDB缓冲池中的空闲页面
 *  **Innodb\_buffer\_pool\_pages\_misc**  
    InnoDB缓冲池中的页面数量很多，因为它们已被分配用于管理开销，例如行锁或自适应哈希索引。此值也可以计算为`Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data`。
 *  **Innodb\_buffer\_pool\_pages\_total**  
    InnoDB缓冲池的总大小，以page为单位。
 *  **innodb\_buffer\_pool\_reads**  
    表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
 *  **innodb\_buffer\_pool\_read\_requests**  
    它表示从内存中逻辑读取的请求数。
 *  **innodb\_buffer\_pool\_wait\_free**  
    通常，对InnoDB缓冲池的写入发生在后台。 当InnoDB需要读取或创建页面并且没有可用的干净页面时，InnoDB首先刷新一些脏页并等待该操作完成。 此计数器计算这些等待的实例。 如果已正确设置innodb\_buffer\_pool\_size，则此值应该很小。如果大于0，则表示InnoDb缓冲池太小。
 *  **innodb\_buffer\_pool\_write\_request**  
    表示对缓冲池执行的写入次数。

### 6. InnoDB缓冲池当前使用了多少实际GB内存？ ###

通过将缓冲池中可用的数据与InnoDB页面（InnoDB缓冲池单位）大小相乘，我们可以发现InnoDB缓冲池此时正在使用的实际内存。

set @ibpdata = (select variable_value from information_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data');
    
    ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'
    
    #从MySQL 5.7.6开始，GLOBAL_STATUS表中提供的信息从Performance Schema获取
    
    mysql> set @ibpdata = (select variable_value from performance_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data');
    
    mysql> select @ibpdata;
    +----------+
    | @ibpdata |
    +----------+
    | 568      |
    +----------+
    1 row in set (0.00 sec)
    
    
    mysql> set @idbpgsize = (select variable_value from performance_schema.global_status where variable_name = 'innodb_page_size');
    
    mysql> select @idbpgsize;
    +------------+
    | @idbpgsize |
    +------------+
    | 16384      |
    +------------+
    1 row in set (0.00 sec)
    
    mysql> set @ibpsize = @ibpdata * @idbpgsize / (1024*1024*1024);
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @ibpsize;
    +-----------------+
    | @ibpsize        |
    +-----------------+
    | 0.0086669921875 |
    +-----------------+
    1 row in set (0.00 sec)