[MySQL][InnoDB] Buffer Pool 상태저장과 복원(Buffer Pool Cache Warming기법)

■ 버퍼풀 상태 저장과 복원

서버를 다시 시작한 후 워밍업 기간을 줄이기 위해 InnoDB는 서버 종료시 각 버퍼 풀에 대해 가장 최근에 사용한 페이지의 백분율을 저장하고 서버 시작시 이러한 페이지를 복원합니다. 최근에 저장된 페이지의 백분율은 innodb_buffer_pool_dump_pct 구성 옵션으로 정의됩니다.

 

사용중인 서버를 다시 시작한 후에는 버퍼풀에 있던 디스크 페이지가 메모리로 다시 가져오기 때문에 (같은 데이터를 쿼리, 업데이트 등) 일반적으로 워밍업 시간이 꾸준히 증가합니다. 시작시 버퍼 풀을 복원하는 기능은 DML 조작이 해당 행에 액세스 할 때까지 기다리지 않고 다시 시작하기 전에 버퍼 풀에 있던 디스크 페이지를 다시로드하여 예열시간(warmup)을 단축시킵니다. 또한 대규모 일괄 처리로 I/O 요청을 수행 할 수 있으므로 전체 I/O가 더 빨라집니다. 페이지 로딩은 백그라운드에서 발생하며 데이터베이스 시작을 지연시키지 않습니다.

 

종료시 버퍼풀 상태를 저장하고 시작시 복원하는 것 외에도 서버가 실행중인 동안 언제든지 버퍼풀 상태를 저장하고 복원 할 수 있습니다. 예를 들어, 안정적인 워크로드에서 안정적인 처리량에 도달 한 후 버퍼풀의 상태를 저장할 수 있습니다. 해당 작업에 필요한 버퍼 페이지로 데이터 페이지를 가져오는 보고서 또는 유지관리 작업을 실행 한 후 또는 다른 비정형 워크로드를 실행 한 후 이전 버퍼풀 상태를 복원 할 수도 있습니다.

 

버퍼 풀의 크기는 수 기가 바이트 일 수 있지만 InnoDB가 디스크에 저장하는 버퍼 풀 데이터는 크기가 작습니다. 적절한 페이지를 찾는 데 필요한 테이블 스페이스ID 및 페이지ID만 디스크에 저장됩니다. 이 정보는 INNODB_BUFFER_PAGE_LRU INFORMATION_SCHEMA 테이블에서 파생됩니다. 기본적으로 테이블 스페이스ID 및 페이지ID 데이터는 ib_buffer_pool이라는 파일에 저장되며 InnoDB 데이터 디렉토리에 저장됩니다. 파일 이름 및 위치는 innodb_buffer_pool_filename 구성 매개 변수를 사용하여 수정할 수 있습니다.

 

데이터는 일반 데이터베이스 조작에서와 같이 버퍼풀에서 캐시되어 캐시에서 삭제되기 때문에 디스크 페이지가 최근에 갱신되거나 DML 조작이 아직 로드되지 않은 데이터를 포함하는 경우에는 문제가 없습니다. 로딩 메커니즘은 더 이상 존재하지 않는 요청 된 페이지를 건너 뜁니다.

 

기본 메커니즘에는 덤프 및 로드 작업을 수행하기 위해 전달되는 백그라운드 스레드가 포함됩니다.

 

압축 된 테이블의 디스크 페이지는 압축된 형식으로 버퍼 풀에로드됩니다. DML 작업 중에 페이지 내용에 액세스 할 때 평소와 같이 페이지가 압축 해제됩니다. 압축 해제 페이지는 CPU를 많이 사용하는 프로세스이므로 버퍼 풀 복원 작업을 수행하는 단일 스레드가 아니라 연결 스레드에서 동시 작업을 수행하는 것이 더 효율적입니다.

 

버퍼 풀 상태 저장 및 복원과 관련된 조작은 다음 주제에서 설명합니다.

+ 버퍼풀 페이지에 대한 덤프 백분율 구성

+ 종료시 버퍼 풀 상태 저장 및 시작시 복원

+ 버퍼풀 상태 온라인 저장 및 복원

+ 버퍼풀 덤프 진행률 표시

+ 버퍼풀 로드 진행률 표시

+ 버퍼풀 로드 작업 중단

+ 성능 스키마를 사용하여 버퍼풀 로드 진행률 모니터링

 

 

▶ 버퍼 풀 페이지에 대한 덤프 백분율 구성

버퍼 풀에서 페이지를 덤프하기 전에 innodb_buffer_pool_dump_pct 옵션을 설정하여 덤프하려는 가장 최근에 사용 된 버퍼풀 페이지의 백분율을 구성 할 수 있습니다. 서버가 실행되는 동안 버퍼 풀 페이지를 덤프하려는 경우 옵션을 동적으로 구성 할 수 있습니다.

SET GLOBAL innodb_buffer_pool_dump_pct=40;

 

서버 종료시 버퍼풀 페이지를 덤프하려는 경우 구성 파일에서 innodb_buffer_pool_dump_pct를 알맞게 설정합니다.

[mysqld]
innodb_buffer_pool_dump_pct=40

 

innodb_buffer_pool_dump_at_shutdown 및 innodb_buffer_pool_load_at_startup이 기본적으로 활성화 된 경우 MySQL 5.7에서 innodb_buffer_pool_dump_pct 기본값이 100 (모든 페이지 덤프)에서 25 (최근에 사용한 페이지의 25 % 덤프)로 변경되었습니다.

 

▶ 종료시 버퍼 풀 상태 저장 및 시작시 복원

• innodb_buffer_pool_dump_at_shutdown

MySQL 서버가 종료될 때 InnoDB 버퍼 풀에 캐시된 페이지를 기록할지 여부를 지정하여 다음 재시작 시 워밍업 프로세스를 단축합니다. 일반적으로 innodb_buffer_pool_load_at_startup과 함께 사용됩니다. innodb_buffer_pool_dump_pct 옵션은 덤프할 가장 최근에 사용된 버퍼 풀 페이지의 백분율을 정의합니다.
기본값은 ON 입니다. 또한 Dynamic 파라미터라 바로 적용이 가능합니다. innodb_buffer_pool_load_at_startup 파라미터와 반대로 작동합니다.

MySQL을 종료하면 innodb_buffer_pool_filename에 정의된 파일 이름으로 data 디렉토리에 자동으로 만들어지게 됩니다. 파일 기본값은 ib_buffer_pool입니다. OS에서 인위적으로 지워도 MySQL종료시 자동으로 생성됩니다. 이 얘기는 MySQL이 종료될때마다 해당 파일을 덮어 쓴다는 이야기가 됩니다.

shell> ls -l ib_buffer_pool
shell> -rw-r-----.  1 mysql mysql     225467 Dec 17 08:52 ib_buffer_pool
mysql > SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

innodb_buffer_pool_dump_at_shutdown은 기본적으로 활성화되어 있습니다.

 

• innodb_buffer_pool_load_at_startup

MySQL 서버 시작 시 InnoDB 버퍼 풀이 이전에 보유했던 동일한 페이지를 로드하여 자동으로 워밍업되도록 지정합니다.  innodb_buffer_pool_dump_at_shutdown과 함께 사용됩니다. 기본값은 ON이지만 Dynamic 파라미터가 아니라서 동적으로 설정되지는 않습니다. innodb_buffer_pool_dump_at_shutdown 파라미터와 반대로 작동합니다.

shell > mysqld --innodb-buffer-pool-load-at-startup=ON;

innodb_buffer_pool_load_at_startup은 기본적으로 활성화되어 있습니다.

 

▶ 버퍼 풀 상태 온라인 저장 및 복원

 

• innodb_buffer_pool_dump_now

InnoDB 버퍼 풀에 캐시된 페이지를 즉시 기록합니다. 일반적으로 innodb_buffer_pool_load_now와 함께 사용됩니다. 기본값은 OFF이며 주로 mysql client에서 즉시 수행하는 명령어로 주로 사용됩니다. 사용방법은 다음과 같습니다.

mysql > SET GLOBAL innodb_buffer_pool_dump_now=ON;

 

• innodb_buffer_pool_load_now

서버 재시작을 기다리지 않고 데이터 페이지 세트를 로드하여 InnoDB 버퍼 풀을 즉시 워밍업합니다. 벤치마킹 중에 캐시 메모리를 알려진 상태로 되돌리거나 보고서 또는 유지 관리를 위한 쿼리를 실행한 후 MySQL 서버가 정상 작업을 재개하도록 준비하는 데 유용할 수 있습니다. 기본값은 OFF이며 Dynamic 파라미터라 즉시 적용이 가능합니다. 주로 즉시 명령을 수행할 때 사용합니다.

mysql > SET GLOBAL innodb_buffer_pool_load_now=ON;

 

• innodb_buffer_pool_filename

innodb_buffer_pool_dump_at_shutdown 또는 innodb_buffer_pool_dump_now에 의해 생성된 테이블스페이스 ID 및 페이지 ID 목록을 보유하는 파일의 이름을 지정합니다. 테이블스페이스 ID 및 페이지 ID는 space, page_id 형식으로 저장됩니다. 기본적으로 파일 이름은 ib_buffer_pool이며 MySQL data 디렉토리에 있습니다. 기본이 아닌 위치는 데이터 디렉토리에 상대적으로 지정해야 합니다.

SET 문을 사용하여 런타임에 파일 이름을 지정할 수 있습니다.

mysql> SET GLOBAL innodb_buffer_pool_filename='파일 이름';

예제)

mysql> SET GLOBAL innodb_buffer_pool_filename='bufferpool_dump_file';
shell> -rw-r-----.  1 mysql mysql     210806 Dec 17 09:51 bufferpool_dump_file

 

▶ 버퍼 풀 덤프 진행률 표시

버퍼풀 상태를 디스크에 저장할 때 진행 상태를 표시하려면 다음 명령문을 실행합니다.

mysql > SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 211217  9:51:48 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.01 sec)

 

작업이 아직 시작되지 않은 경우 "시작되지 않음"이 반환됩니다. 작업이 완료되면 완료 시간이 인쇄됩니다 (예 : Finished at 110505 12:18:02). 작업이 진행중인 경우 상태 정보가 제공됩니다 (예 : Dumping buffer pool 5/7, page 237/2873).

 

▶ 버퍼 풀로드 진행률 표시

버퍼풀을 로드할 때 진행 상태를 표시하려면 다음 명령문을 발행하십시오.

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 211217  9:29:50 |
+--------------------------------+--------------------------------------------------+

 

작업이 아직 시작되지 않은 경우 "시작되지 않음"이 반환됩니다. 작업이 완료되면 완료시간이 인쇄됩니다 (예 : Finished at 110505 12:23:24). 작업이 진행중인 경우 상태 정보가 제공됩니다 (예 : Loaded 123/22301 pages).

 

▶ 버퍼 풀로드 작업 중단

innodb_buffer_pool_load_at_startup 또는 innodb_buffer_pool_load_now에 의해 트리거된 InnoDB 버퍼 풀 내용을 복원하는 프로세스를 중단합니다.
기본값은 OFF이며 Dynamic 변수라 바로 적용이 가능합니다. 주로 즉시 명령을 내릴때 사용합니다.

mysql > SET GLOBAL innodb_buffer_pool_load_abort=ON;

 

▶ 성능 스키마를 사용하여 버퍼 풀로드 진행률 모니터링

성능 스키마를 사용하여 버퍼 풀로드 진행 상황을 모니터링 할 수 있습니다.

 

다음 예제는 stage / innodb / buffer pool load stage 이벤트 인스트루먼트 및 관련 이용자 테이블을 사용하여 버퍼풀 로드 진행을 모니터하는 방법을 보여줍니다.

1. stage / innodb / buffer pool load instrument를 활성화합니다.

 

 

2. 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를 활성화하여 현재 버퍼 풀 상태를 덤프합니다.

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를 활성화하여 버퍼 풀 y를 로드합니다.

mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;

 

6. Performance Schema events_stages_current 테이블을 쿼리하여 버퍼 풀로드 조작의 현재 상태를 확인합니다. WORK_COMPLETED 열에는로드 된 버퍼 풀 페이지 수가 표시됩니다. 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 테이블은 버퍼 풀로드 조작이 완료된 경우 빈 세트를 리턴합니다. 이 경우 events_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 |
+-------------------------------+----------------+----------------+

 

노트

innodb_buffer_pool_load_at_startup을 사용하여 서버를 시작할때 버퍼풀을 로드하는데 이때 성능 스키마를 사용하여 버퍼풀 로드 진행 상황을 모니터링 할 수도 있습니다. 이 경우, 스테이지 / innodb / 버퍼 풀로드 인스트루먼트 및 관련 소비자는 시작시 활성화되어야합니다.