MySQL Ver. 5.7 Buffer Pool 상태 정보 저장하여 사용하기

MySQL은 서버를 재 시작 할때 시작하자 마자 계속 운영된 것처럼 Buffer Pool에 페이지 정보가 저장되어 사용할 수 있게 하는 시간을 줄이기 위해 자주 사용되는 페이지 정보를 저장하도록 기능이 추가되었다. 얼마나 자주 사용하는지 즉, 저장해야 할 정도로 사용량이 많은 페이지를 가늠하는 기준은 innodb_buffer_pool_dump_at_shutdown 파라메터를 통해 설정이 가능하다. 

바쁘게 사용하는 서버를 재 시작 한 후에는 일반적으로 어느정도 시간이 흘러야 사용하는 대부분의 페이지들이 메모리에 올라와 빠르게 사용할 수 있는 상태가 된다. 사용하는 대부분의 페이지가 메모리에 로딩되어 빠르게 동작할 수 있는 상태가 되는 일련의 작업을 warmup 이라고 하고 그 기간을 warmup period 라고 한다. 이 문서에서는 예열한다고 표현할 것이다. 이 예열 작업은 백그라운드 작업으로 진행되기 때문에 DB가 startup 하는 시간에 영향을 주지는 않는다. 

Shutdown 할때 Buffer Pool 정보를 저장하고, Startup 할 때 해당 정보를 다시 로딩하는 작업은 사실 사용자가 원하면 어느 때이든 진행할 수 있다. 즉, MySQL 서버가 현재 운영 중에도 진행할 수 있다. 예를 들어, 한창 서비스 중일때의 버퍼풀 상태정보를 저장하고, 새벽에 배치 작업 이후에 미리 저장해 두었던 버퍼풀의 정보를 복구하여 서비스 하게 할 수 있다. 

현재 Buffer Pool 상태를 저장할때 해당 Buffer Pool의 사이즈가 크더라도 저장하는데는 문제가 없다. 실제 메모리 상태를 저장할때는 메모리 내용을 전부 저장하는 것이 아니라 어떤 페이지가 로딩되어있는지, Tablespace ID와 Page ID만 저장되기 때문이다. 그래서, 실제 저장 내용은 그리 크지 않다. 기본적으로 이 내용은 ib_buffer_pool이라는 파일 안에 저장된다. 그리고, 해당 파일은 Data Directory 아래에 저장된다. 해당 파일의 이름과 위치는 innodb_buffer_pool_filename 시스템 변수를 통해 변경할 수 있다. 

중요한 사실은 Buffer Pool의 내용을 dump하고 load 하는 작업은 모두 백그라운드 스레드를 통해 진행된다는 것이다. 즉, dump하고 load하는 작업으로 인해 서비스에 영향을 주는 일이 거의 없도록 구현하였다. 

1. Buffer Pool Page Dump 비율 설정하기 

buffer pool 로 부터 페이지를 dump하기 전에, 사용자는 innodb_buffer_pool_dump_pct 시스템 변수를 설정하여 가장 최근에 사용한 페이지를 특정 비율로 dump하게 설정할 수 있다. 만약, 사용 중에 buffer pool에 올라간 페이지를 dump하고자 한다면 다음과 같이 운영중에 설정이 가능하다. 

SET GLOBAL innodb_buffer_pool_dump_pct=40;

만약, MySQL을 shutdown할때 dump하고 싶다면, innodb_buffer_pool_dump_pct를 my.cnf 에 지정하여 놓으면 된다. 

[mysqld]

innodb_buffer_pool_dump_pct=40

innodb_buffer_pool_dump_pct 시스템 변수의 기본값은 MySQL Ver. 5.7.7 부터 100~ 25(단위는 %) 사이로 변경할 수 있다. 그리고, innodb_buffer_pool_dump_at_shutdown과 innodb_buffer_pool_load_at_startup은 기본적으로 활성화되어 있다. 

2. Shutdown시 페이지를 Dump하고, 시작할때 로딩하기

MySQL 을 Shutdown할때, 사용하던 buffer pool page의 정보를 덤프하고자 한다면 다음과 같이 설정해야 한다. 

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

innodb_buffer_pool_dump_at_shutdown 시스템 변수는 MySQL 5.7.7부터 기본적으로 활성화 되게 설정된다. 

만약, MySQL이 시작할 때 shutdown시 저장한 buffer pool 의 페이지 정보를 로딩하고자 한다면 다음과 같이 시작 시에 설정해야 한다. 

mysqld --innodb_buffer_pool_load_at_startup=ON;

innodb_buffer_pool_load_at_startup 시스템 변수도 innodb_buffer_pool_dump_at_shutdown 시스템 변수와 마찬가지로 MySQL Ver. 5.7.7 부터 자동으로 활성화된다. 

3. buffer pool load 상태 정보 보기 

다음과 같이 실행해서 현재 어떻게 진행되는지 상태 정보를 확인할 수 있다. 

SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

만약, 해당 작업이 시작 전이라면, "not started"로 상태가 보일것이다. 만약, 완료되었다면 다음과 같이 결과값이 나올 것이다. "Finished at 110101 12:11:11" 만약, 진행중이라면 다음과 같이 로그가 보일 것이다. " Loaded 111/123221 pages "

4. Buffer pool Loading 중지하기 

buffer pool load 작업을 중지하고 싶다면, 다음과 같이 중지 구문을 실행하면 된다. 

SET GLOBAL innodb_buffer_pool_load_abort=ON;

5. Performance Schema를 사용하여 Buffer Pool Load 작업 모니터링 하기 

MySQL Ver. 5.7.6부터 사용자는 Performance Schema를 사용하여 buffer pool load 작업을 모니터링 할 수 있다.  모니터링 하기 위해서는 Performance Schema 중 stage/innodb/buffer_pool_load를 활성화하여 사용하면 된다. 간단히 사용법을 확인하면 다음과 같다. 

1) stage/innodb_buffer_pool_load 활성화하기

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/buffer%';

2) stage event consumer 테이블을 활성화 하는데, events_stages_current, events_stages_history와 events_stages_history_long을 포함하여 활성화 한다. 

mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';

3) innodb_buffer_pool_dump_now 시스템 변수를 활성화 하여 현재 buffer pool의 상태 정보를 dump한다. 

mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;

4) 다음과 같이 작업이 끝났는지를 모니터링 한다. 

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status' \G

*************************** 1. row ***************************

Variable_name: Innodb_buffer_pool_dump_status

        Value: Buffer pool(s) dump completed at 150202 16:38:58

5) innodb_buffer_pool_load_now 시스템 변수를 활성화하여 buffer pool 정보를 로딩한다. 

mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;

6) 다음과 같이 Performance Schema의 events_stages_current 테이블에 쿼리를 통해 조회하여 로딩 작업에 대한 정보를 확인해 볼 수 있다. WORK_COMPLEATED 컬럼의 값이 로드된 buffer pool 페이지의 수를 의미한다. WORK_ESTIMATED 컬럼의 수는 남아있는 페이지의 수를 의미한다. 

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED

       FROM performance_schema.events_stages_current;

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

| EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |

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

| stage/innodb/buffer pool load |           5353 |           7167 |

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

로드 작업이 모두 완료되면 events_stages_current 테이블은 빈값을 결과값으로 보여주게 된다. 완료된 작업에 대한 정보를 확인하고자 하는 경우 event_stages_history 테이블을 확인하면 된다. 

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED 

       FROM performance_schema.events_stages_history;

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

| EVENT_NAME                    | WORK_COMPLETED | WORK_ESTIMATED |

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

| stage/innodb/buffer pool load |           7167 |           7167 |

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

https://dev.mysql.com/doc/refman/5.7/en/innodb-preload-buffer-pool.html