导 读 想必数据库的同行们都知道,热点数据导出导入的动作,使得数据库重启之后,不需要再耗费大量的时间来预热,使得因为热点数据的原因导致数据库性能波动的时间大大减少,那么执行热点数据的导出与导入操作时究竟对数据库的性能有没有影响呢?如果你的服务器拥有超大内存,而且设置了一个对应内存一定比例的超大buffer pool,是否在纠结需不需要设置热点数据在重启时自动导出导入呢?下面一起看演示过程,答案就在其中。
背 景
手动执行导出ib_buffer_pool
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 17:11:51]>show status like '%Innodb_buffer_pool_pages_data%';
+-------------------------------+----------+
| Variable_name | Value |
+-------------------------------+----------+
| Innodb_buffer_pool_pages_data | 10319938 |
+-------------------------------+----------+
1 row in set (0.00 sec)
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 17:12:02]>select 10319938*16/1024/1024;
+-----------------------+
| 10319938*16/1024/1024 |
+-----------------------+
| 157.46975708 |
+-----------------------+
1 row in set (0.00 sec)
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 17:12:17]>
# 设置 innodb_buffer_pool_dump_at_shutdown=off;
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 15:37:26]>set global innodb_buffer_pool_dump_at_shutdown=off;
Query OK, 0 rows affected (0.00 sec)
# 执行关闭mysqld,并记录time命令打印的执行时间
$time mysqladmin --defaults-file=/home/mysql/conf/my1.cnf -uroot -p'password' -hlocalhost -P3306 shutdown
170428 18:34:16 mysqld_safe mysqld from pid file /home/mysql/data/mysqldata1/sock/mysql.pid ended
[1]+ Done
......
real 1m4.020s
user 43m35.121s
sys 11m36.453s
# 执行启动mysqld,启动mysqld之前,先在my.cnf中配置参数innodb_buffer_pool_load_at_startup=OFF,再启动,看看需要多长时间,由于是挂后台,无法使用time命令查看,可以通过错误日志中的输出来大致判断启动时间需要多长
$time mysqld_safe --defaults-file=/home/mysql/conf/my1.cnf --user=mysql &
$cat /data2/mysqldata1/log/error.log
......
170428 17:18:09 mysqld_safe Starting mysqld daemon with databases from /home/mysql/data/mysqldata1/mydata
2017-04-28 17:18:09 0 [Warning] Using unique option prefix collation instead of collation-server is deprecated and will be removed in a future release. Please use the full name instead.
......
2017-04-28 17:18:28 16701 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000228' at position 58975338, \
relay log '/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000462' position: 4
2017-04-28 17:18:28 16701 [Note] Event Scheduler: Loaded 0 events
2017-04-28 17:18:28 16701 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.34-log' socket: '/home/mysql/data/mysqldata1/sock/mysql.sock' port: 3306 MySQL Community Server (GPL)
# 设置innodb_buffer_pool_dump_now=on,表示立即导出热点数据页到ib_buffer_pool文件中
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 16:58:00]>set global innodb_buffer_pool_dump_now=on;
Query OK, 0 rows affected (0.00 sec)
# 使用Innodb_buffer_pool_dump_status状态变量查看一下dump完成时间(如果没有显示dump completed 则需要继续等待并持续查看这个变量,直到显示dump completed 为止)
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 17:00:15]>show status like 'Innodb_buffer_pool_dump_status';
+--------------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 170428 16:58:42 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)
$ ls -lh /data2/mysqldata1/innodb_ts/
total 2.1G
-rw-rw---- 1 mysql mysql 95M Apr 28 16:58 ib_buffer_pool
-rw-rw---- 1 mysql mysql 2.0G Apr 28 16:58 ibdata1
# 使用stat命令查看这个文件的秒级别access和Modify时间,在这个场景下,我们可以认为access代表了文件创建时间(文件句柄打开时间),Modify代表了文件最后变更的时间(数据写入完成时间)
#stat /data2/mysqldata1/innodb_ts/ib_buffer_pool
......
Access: 2017-04-28 16:58:39.078935848 +0800
Modify: 2017-04-28 16:58:42.568889476 +0800
Change: 2017-04-28 16:58:42.568889476 +0800
自动执行导出ib_buffer_pool
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 17:12:17]>set global innodb_buffer_pool_dump_at_shutdown=on;
Query OK, 0 rows affected (0.00 sec)
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 17:14:20]>show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_fast_shutdown | 2 |
+----------------------+-------+
1 row in set (0.00 sec)
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 17:14:31]>set global innodb_fast_shutdown=1;
Query OK, 0 rows affected (0.00 sec)
在shell命令行中停止MySQL,留意停止时间需要多长。
$time mysqladmin --defaults-file=/home/mysql/conf/my1.cnf -uroot -p'password' -hlocalhost -P3306 shutdown
......
real 1m18.036s
user 0m0.005s
sys 0m0.004s
2017-04-28 17:16:21 7ed606745700 InnoDB: Dumping buffer pool(s) to /home/mysql/data/mysqldata1/innodb_ts/ib_buffer_pool
2017-04-28 17:16:25 7ed606745700 InnoDB: Buffer pool(s) dump completed at 170428 17:16:25
#ls -lh /data2/mysqldata1/innodb_ts/ib_buffer_pool
-rw-rw---- 1 mysql mysql 95M Apr 28 17:16 /data2/mysqldata1/innodb_ts/ib_buffer_pool
[root@master /root]
#stat /data2/mysqldata1/innodb_ts/ib_buffer_pool
......
Access: 2017-04-28 17:16:21.938770942 +0800
Modify: 2017-04-28 17:16:25.291726062 +0800
Change: 2017-04-28 17:16:25.291726062 +0800
手动执行导入ib_buffer_pool
170428 17:18:09 mysqld_safe Starting mysqld daemon with databases from /home/mysql/data/mysqldata1/mydata
2017-04-28 17:18:09 0 [Warning] Using unique option prefix collation instead of collation-server is deprecated and will be removed in a future release. Please use the full name instead.
......
2017-04-28 17:18:28 16701 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended.\
Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-04-28 17:18:28 16701 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000228' at position 58975338,\
relay log '/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000462' position: 4
2017-04-28 17:18:28 16701 [Note] Event Scheduler: Loaded 0 events
2017-04-28 17:18:28 16701 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.34-log' socket: '/home/mysql/data/mysqldata1/sock/mysql.sock' port: 3306 MySQL Community Server (GPL)
# 执行load ib_buffer_pool文件
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 17:24:53]>set global innodb_buffer_pool_load_now=ON;
Query OK, 0 rows affected (0.00 sec)
# 查看load 完成时间,如果没有查看到load completed,则持续查看Innodb_buffer_pool_load_status状态变量
[root@master:/root 5.6.34-log_Instance1 root@localhost:sbtest 18:14:21]>show status like '%Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170428 18:10:32 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)
# load ib_buffer_pool期间加载期间,可以使用iostat命令查看data磁盘载情况,如下
avg-cpu: %user %nice %system %iowait %steal %idle
0.06 0.00 0.15 1.15 0.00 98.64
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdd 0.00 0.00 4410.00 0.00 141120.00 0.00 32.00 0.86 0.19 0.19 85.70
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
md127 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdf 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
自动执行导入ib_buffer_pool
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 18:46:50]>set global innodb_buffer_pool_dump_at_shutdown=on;
Query OK, 0 rows affected (0.00 sec)
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 18:46:58]>set global innodb_fast_shutdown=1;
Query OK, 0 rows affected (0.00 sec)
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 18:47:03]>
$ mysqladmin --defaults-file=/home/mysql/conf/my1.cnf -uroot -p'password' -hlocalhost -P3306 shutdown
cat /home/mysql/conf/my1.cnf
......
innodb_buffer_pool_load_at_startup=ON
$ mysqld_safe --defaults-file=/home/mysql/conf/my1.cnf --user=mysql &
2017-04-28 18:49:55 7fa0dfb83700 InnoDB: Loading buffer pool(s) from /home/mysql/data/mysqldata1/innodb_ts/ib_buffer_pool
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 18:50:22]>show status like '%Innodb_buffer_pool_load_status';
+--------------------------------+------------------------------+
| Variable_name | Value |
+--------------------------------+------------------------------+
| Innodb_buffer_pool_load_status | Loaded 119681/10319883 pages |
+--------------------------------+------------------------------+
1 row in set (0.00 sec)
[root@master:/root 5.6.34-log_Instance1 root@localhost:sbtest 18:51:52]>select * from sbtest1 order by id desc limit 1;
+---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 9999999 | 2507960 | 25744810828-37032637642-87919764377-76779868555-49415634722-86176448317-10050757951-88422706101-49861857465-53325889077 | 55855883347-94289537806-43993911203-91112041707-01357150366 |
+---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)
[root@master:/root 5.6.34-log_Instance1 root@localhost:sbtest 18:52:02]>update sbtest1 set k=k+1 where id=9999999;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
[root@master:/root 5.6.34-log_Instance1 root@localhost:sbtest 18:54:05]>select * from sbtest1 order by id desc limit 1;
+---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 9999999 | 2507961 | 25744810828-37032637642-87919764377-76779868555-49415634722-86176448317-10050757951-88422706101-49861857465-53325889077 | 55855883347-94289537806-43993911203-91112041707-01357150366 |
+---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)
# 查看错误日志
$ cat /data2/mysqldata1/log/error.log
......
2017-04-28 19:30:32 7fa0dfb83700 InnoDB: Buffer pool(s) load completed at 170428 19:30:32
# 数据库中的状态查询到Load完成
[root@master:/root 5.6.34-log_Instance1 root@localhost:test 19:46:10]>show status like '%Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170428 19:30:32 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)
总 结
从以上测试结果可以看到,ib_buffer_pool的Dump时间160G左右的数据量,1000W左右的数据页,只需要几秒时间,在Load的时候,需要大约40分钟,但是在Load ib_buffer_pool文件的时候并不会阻塞对数据库的访问,所以理论上在MySQL的起停中开启ib_buffer_pool的自动Dump和Load对数据库前台的操作影响极小,建议在配置文件中设置如下参数以实现该功能。
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON
innodb_fast_shutdown=1