Chinaunix首页 | 论坛 | 博客
  • 博客访问: 528592
  • 博文数量: 100
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1172
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 09:50
个人简介

一个守望数据库的老菜鸟

文章分类

全部博文(100)

文章存档

2019年(21)

2018年(17)

2017年(38)

2016年(24)

我的朋友

分类: Mysql/postgreSQL

2017-07-06 16:06:56

博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址:http://blog.chinaunix.net/uid-31396856-id-5767262.html


本次调整的数据库版本如下:
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.18-log |
+------------+
1 row in set (0.01 sec)


step1:
mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 85899345920 |
+-------------------------+-------------+
1 row in set (0.00 sec)
当前buffer pool大小为80G

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 16    |
+------------------------------+-------+
1 row in set (0.00 sec)
当前有16个buffer pool实例

我这里打算调整增加到90G,计算下字节数
mysql> select 90*1024*1024*1024;
+-------------------+
| 90*1024*1024*1024 |
+-------------------+
|       96636764160 |
+-------------------+
1 row in set (0.00 sec)

step2:开始调整
mysql> set global innodb_buffer_pool_size=96636764160 ;
Query OK, 0 rows affected (0.00 sec)

step3:检查结果
mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 96636764160 |
+-------------------------+-------------+
1 row in set (0.00 sec)


mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 16    |
+------------------------------+-------+
1 row in set (0.01 sec)

调整完成。

当然记得配置调整内核参数:shmmax为相应的值哦。

在线调整innodb buffer pool size的后台日志如下:
2017-07-06T15:48:22.864337+08:00 8 [Note] InnoDB: Requested to resize buffer pool. (new size: 96636764160 bytes)
2017-07-06T15:48:22.868202+08:00 0 [Note] InnoDB: Resizing buffer pool from 85899345920 to 96636764160 (unit=134217728).
2017-07-06T15:48:22.868222+08:00 0 [Note] InnoDB: Disabling adaptive hash index.
2017-07-06T15:48:23.081501+08:00 0 [Note] InnoDB: disabled adaptive hash index.
2017-07-06T15:48:23.081548+08:00 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2017-07-06T15:48:23.081562+08:00 0 [Note] InnoDB: Latching whole of buffer pool.
2017-07-06T15:48:23.081633+08:00 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.132947+08:00 0 [Note] InnoDB: buffer pool 0 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.132985+08:00 0 [Note] InnoDB: buffer pool 1 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.181751+08:00 0 [Note] InnoDB: buffer pool 1 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.185124+08:00 0 [Note] InnoDB: buffer pool 2 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.238412+08:00 0 [Note] InnoDB: buffer pool 2 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.238454+08:00 0 [Note] InnoDB: buffer pool 3 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.289552+08:00 0 [Note] InnoDB: buffer pool 3 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.289588+08:00 0 [Note] InnoDB: buffer pool 4 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.338803+08:00 0 [Note] InnoDB: buffer pool 4 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.338838+08:00 0 [Note] InnoDB: buffer pool 5 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.388154+08:00 0 [Note] InnoDB: buffer pool 5 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.388196+08:00 0 [Note] InnoDB: buffer pool 6 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.437965+08:00 0 [Note] InnoDB: buffer pool 6 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.438005+08:00 0 [Note] InnoDB: buffer pool 7 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.494750+08:00 0 [Note] InnoDB: buffer pool 7 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.494786+08:00 0 [Note] InnoDB: buffer pool 8 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.544400+08:00 0 [Note] InnoDB: buffer pool 8 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.544436+08:00 0 [Note] InnoDB: buffer pool 9 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.600689+08:00 0 [Note] InnoDB: buffer pool 9 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.600739+08:00 0 [Note] InnoDB: buffer pool 10 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.650422+08:00 0 [Note] InnoDB: buffer pool 10 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.650458+08:00 0 [Note] InnoDB: buffer pool 11 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.700974+08:00 0 [Note] InnoDB: buffer pool 11 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.701010+08:00 0 [Note] InnoDB: buffer pool 12 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.759245+08:00 0 [Note] InnoDB: buffer pool 12 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.759287+08:00 0 [Note] InnoDB: buffer pool 13 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.812728+08:00 0 [Note] InnoDB: buffer pool 13 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.812766+08:00 0 [Note] InnoDB: buffer pool 14 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.863099+08:00 0 [Note] InnoDB: buffer pool 14 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.863134+08:00 0 [Note] InnoDB: buffer pool 15 : resizing with chunks 40 to 45.
2017-07-06T15:48:23.916792+08:00 0 [Note] InnoDB: buffer pool 15 : 5 chunks (40960 blocks) were added.
2017-07-06T15:48:23.917110+08:00 0 [Note] InnoDB: Completed to resize buffer pool from 85899345920 to 96636764160.
2017-07-06T15:48:23.917133+08:00 0 [Note] InnoDB: Re-enabled adaptive hash index.
2017-07-06T15:48:23.917148+08:00 0 [Note] InnoDB: Completed resizing buffer pool at 170706 15:48:23.

继续调整如下:
在线减小innodb buffer pool size大小为80G
mysql> set global innodb_buffer_pool_size=85899345920;
Query OK, 0 rows affected (0.00 sec)

如果不是80G呢?
mysql> set global innodb_buffer_pool_size=85899345950;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '85899345950' |
+---------+------+------------------------------------------------------------------+
1 row in set (0.00 sec)

出现警告了,再看实际大小

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 88046829568 |
+-------------------------+-------------+
1 row in set (0.00 sec)

这是因为,调整后的buffer pool大小必须满足如下条件:
innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances的倍数,即128M的倍数。

---The end
阅读(3084) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~