Warm-up InnoDB Buffer Pool 워밍업

[출처] Warm-up InnoDB Buffer Pool|작성자 bomyzzang

http://blog.naver.com/bomyzzang/220118755731

MySQL 5.6 새로운 기능 (Percona Server and MariaDB​ 지원)

 

InnoDB 설정 중 innodb_buffer_pool_size, 기본적으로 메모리에 InnoDB의 데이터와 인덱스를 저장하는 방식.

 

MySQL 서버를 재시작 할 경우, 버퍼-풀이 비어진 상태이기 때문에, 보통의 경우 버퍼의 워밍업에 약간의 시간이 소요됨.

 

이럴경우, 사용하는 방식이다.

 

 

1. mysql>  SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;

 

MySQL 서버를 재기동하기 전에 해당 명령어 사용, shutdown 할 경우, 버퍼-풀 덤프 받음.​

 

MySQL 서버를​ 중지할 때, ib_buffer_pool 이라는 파일이 MySQL의 DATADIR 에 생성되어짐.

 

 

 

2. ( my.cnf 설정 추가)  

 

[mysqld]​

innodb_buffer_pool_dump_at_shutdown = 1

innodb_buffer_pool_load_at_startup = 1 

 

 

3. MySQL 재기동 (mysql.err 로그 확인 또는 status 확인)

 

2014-09-11 14:18:24 7fba24d5a700 InnoDB: Loading buffer pool(s) from /data/mysql_data/ib_buffer_pool

2014-09-11 14:18:24 7fba24d5a700 InnoDB: Buffer pool(s) load completed at 140911 14:18:24 

 

 ​mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

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

| Variable_name                                    | Value                                 |

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

| Innodb_buffer_pool_load_status            | Loaded 5121/6441 pages     |

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

1 row in set (0.00 sec)

 

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

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

| Variable_name                         | Value                                                               |

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

| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 140911 14:18:24 |

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

 

 

4. innodb_buffer_pool_dump_now ,innodb_buffer_pool_load_now.​ 사용하여

 

재기동 없이 즉시 버퍼-풀 적용하는 방법

 

 

 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.19-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> show global variables like "innodb_buffer_pool_dump_now";

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

| Variable_name                              | Value    |

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

| innodb_buffer_pool_dump_now       | OFF      |

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

1 row in set (0.00 sec)

 

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

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

| Variable_name                                    | Value                                                                               |

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

| Innodb_buffer_pool_load_status            | Buffer pool(s) load completed at 140911 14:57:18                |

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

1 row in set (0.00 sec)

 

mysql> set global innodb_buffer_pool_dump_now = on ;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

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

| Variable_name                                    | Value                                                                               |

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

| Innodb_buffer_pool_dump_status          | Buffer pool(s) dump completed at 140911 14:58:05              |

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

1 row in set (0.00 sec)

 

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

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

| Variable_name                                    | Value                                                                               |

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

| Innodb_buffer_pool_load_status            | Buffer pool(s) load completed at 140911 14:57:18                |

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

1 row in set (0.00 sec)

 

mysql> set global innodb_buffer_pool_load_now = on ;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

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

| Variable_name                                    | Value                                                                               |

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

| Innodb_buffer_pool_load_status            | Buffer pool(s) load completed at 140911 14:58:41                |

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

1 row in set (0.00 sec)

 

[출처] Warm-up InnoDB Buffer Pool|작성자 bomyzzang