关于热点数据导出与加载的影响，多的
是你不知道的事
作者｜罗小波·高级数据库技术专家
想必数据库的同行们都知道，热点数据导出导入的动作，使得数据库重启之后，不需要再耗费大量的时间来预热，使得因为热点数据的原因导致数据库性能波动的时间大大减少，那么执行热点数据的导出与导入操作时究竟对数据库的性能有没有影响呢？如果你的服务器拥有超大内存，而且设置了一个对应内存一定比例的超大buffer pool，是否在纠结需不需要设置热点数据在重启时自动导出导入呢？下面一起看演示过程，答案就在其中。
背景
sysbench造数200个500W数据量的表，占用磁盘空间275G
服务器配置
·  CPU：72 vcpus
·内存：256G
·磁盘：1.5T SSD RAID10
数据库版本：5.6.34
数据库参数：innodb_buffer_pool_size=160G
测试目的：测试在innodb_buffer_pool_size设置的buffer poo size占满时，导出导入
ib_buffer_pool文件需要多长时间。
手动执行导出ib_buffer_pool
sysbench 32线程oltp持续加压
先查看一下buffer pool中当前有多少的数据量，是否达到或接近160G buffer大小，如果数据量已接近160G，则继续往下（注：Innodb_buffer_pool_pages_data为当前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;
+-----------------------+
mysql下载starting the server| 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]>
为了对比后续有热点数据和没有热点数据导入导出的差别，先执行一次不导出导入热点数据的数据库重启，并记录关闭和启动mysqld的时间，先在innodb_buffer_pool_dump_at_shutdown=off的情况下，关闭MySQL，看看关闭时间需要多久。
# 设置 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/my1f -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之前，先在myf中配置参数
innodb_buffer_pool_load_at_startup=OFF，再启动，看看需要多长时间，由于是挂后台，无法使用time命令查看，可以通过错误日志中的输出来大致判断启动时间需要多长
$time mysqld_safe --defaults-file=/home/mysql/conf/my1f --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)
从以上错误日志的输出信息中可以看到，Starting mysqld 到打印socket地址时间的日志时间判断，在没有加载ib_buffer_pool时的启动时间为20S，且memlock参数未使用的情况下
现在，重新使用sysbench持续32线程oltp加压，以使得热点数据尽量占满buffer pool
然后登录数据库执行set global innodb_buffer_pool_dump_now=on；语句(注意：在执行这个步骤之前，你需要确保Innodb_buffer_pool_pages_data参数中的页数接近占用全部的buffer pool size，否则后续的测试没有意义)
# 设置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)
现在，查看与MySQL ibdata1文件相同目录下的ib_buffer_pool文件。
$ 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
计算前面几个步骤获取的时间：通过数据库中的show status like
'Innodb_buffer_pool_dump_status'；查询结果，完成时间是16:58:42，通过stat命令查询到这个文件最后修改时间是16:58:42，时间一致，文件access时间与Modify时间相差3秒，表示ib_buffer_pool 文件在文件系统层3秒就完成了dump操作（从数据库层到文件系统层的写入），而对于数据库层，执行set global innodb_buffer_pool_dump_now=on；时是立即返回，说明该语句对于数据库语句执行来说，没有阻塞操作。
自动执行导出ib_buffer_pool
sysbench 32线程oltp持续加压
登录MySQL数据库中使用修改变量：set global
innodb_buffer_pool_dump_at_shutdown=on;  修改innodb_buffer_pool_dump_at_shutdown=on之后，mysqld在关闭时会自动导出热点数据页到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)
注意：innodb_fast_shutdown的值不能为2，否则将无法执行导出（经源码分析，在该参数设置为2时，不会进入dump函数执行ib_buffer_pool的导出），如果为2请先修改为1即可：set global innodb_fast_shutdown=1;
[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/my1f -uroot -p'password' -hlocalhost -P3306 shutdown

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。