mysql in 预处理 mysql数据预热

转载

数据大侠客 2024-05-16 22:04:55

文章标签 mysql in 预处理数据库 mysql linux 缓冲池 文章分类 MySQL 数据库

在数据库压力很大的情况下，重启完数据库，通过手工执行下列语句，把热数据加载到innodb_buffer_pool缓冲池中进行预热，从而避免早高峰连接数升高，程序报错。

select count() from user; select count() from ***; ......

在mysql5.6里为了解决上述问题，提供了一个新特性来快速预热buffer_pool缓冲池，只需在my.cnf中加入如下命令：

参数	注释
innodb_buffer_pool_dump_at_shutdown=1	关闭时dump热数据到本地磁盘
innodb_buffer_pool_dump_now=1	采用手工方式把热数据dump到本地磁盘
innodb_buffer_pool_load_at_start=1	在启动时把热数据加载到内存
innodb_buffer_pool_load_now=1	采用手工方式把热数据加载到内存

mysql>show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
|Variable_name                       |Value          |
+-------------------------------------+----------------+
|innodb_buffer_pool_dump_at_shutdown | OFF            |
|innodb_buffer_pool_dump_now         |OFF            |
|innodb_buffer_pool_filename         |ib_buffer_pool |
|innodb_buffer_pool_instances        |8              |
|innodb_buffer_pool_load_abort       |OFF            |
|innodb_buffer_pool_load_at_startup  |OFF            |
| innodb_buffer_pool_load_now         | OFF            |
|innodb_buffer_pool_size             |786432000      |
+-------------------------------------+----------------+
8 rows inset (0.01 sec)
[root@gflinuxmysql]# service mysql stop
Shuttingdown MySQL............                            [  OK  ]
[root@gflinuxmysql]# cd mysql/
[root@gflinuxmysql]# ll
......
-rw-rw----1 mysql mysql      3703 Aug  8 14:24 ib_buffer_pool
-rw-rw----1 mysql mysql 616562688 Aug  8 14:24ibdata1
-rw-rw----1 mysql mysql  50331648 Aug  8 14:24 ib_logfile0
-rw-rw----1 mysql mysql  50331648 Aug  8 14:20 ib_logfile1
......
[root@gflinuxmysql]# tail -100f ../log/gflinux.err
2014-08-0814:24:32 16207 [Note] InnoDB: Starting shutdown...
2014-08-0814:24:32 62ad2b90 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool
2014-08-0814:24:32 62ad2b90 InnoDB: Buffer pool(s) dump completed at 140808 14:24:32
2014-08-0814:24:34 16207 [Note] InnoDB: Shutdown completed; log sequence number 27441703699

加载：

[root@gflinuxmysql]# service mysql start

StartingMySQL.... [ OK ]

[root@gflinuxmysql]# tail -100f ../log/gflinux.err

......

2014-08-0814:28:13 16696 [Note] InnoDB: 128 rollback segment(s) are active.

2014-08-0814:28:13 16696 [Note] InnoDB: 5.6.17 started; log sequence number 27441703699

2014-08-0814:28:13 16696 [Note] Semi-sync replication initialized for transactions.

2014-08-0814:28:13 16696 [Note] Semi-sync replication enabled on the master.

2014-08-0814:28:13 62adab90 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool

2014-08-0814:28:13 62adab90 InnoDB: Buffer pool(s) load completed at 140808 14:28:13

在关闭mysql时，会把内存中的热数据保存在磁盘的ib_buffer_pool文件中，该文件位于数据目录下。