关于热点数据导出与加载的影响，多的是你不知道的事

作者  罗小波

沃趣科技MySQL高级技术专家

导  读

想必数据库的同行们都知道，热点数据导出导入的动作，使得数据库重启之后，不需要再耗费大量的时间来预热，使得因为热点数据的原因导致数据库性能波动的时间大大减少，那么执行热点数据的导出与导入操作时究竟对数据库的性能有没有影响呢？如果你的服务器拥有超大内存，而且设置了一个对应内存一定比例的超大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;

+-----------------------+

| 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/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)