[AWS RDS MySQL] InnoDB cache warming

-          버퍼풀 정보를 저장하고 시작시 로드하여 워밍업 단계 생략하기

 

l  Version : AWS RDS MySQL 5.6 later

 

InnoDB cache warming(캐시워밍) DB 인스턴스가 종료될 버퍼 풀의 현재 상태를 저장한 다음 DB 인스턴스가 시작될 저장된 버퍼 정보를 다시 로드하여 MySQL DB 인스턴스에 대한 성능 향상을 제공할 있다. 뜻은 정상적인 데이터베이스 사용에서 버퍼풀의 워밍업 필요를 무시하고 대신 알려진 공통 쿼리에 대한 페이지로 버퍼풀을 미리 로드한다. 버퍼풀 정보를 저장하는 파일은 페이지 자체가 아니라 버퍼풀에 있는 페이지에 대한 메타데이터만 저장한다. 따라서 결과적으로 파일에 많은 저장공간이 필요하지 않다. 파일크기는 캐시 크기의 0.2% 정도이다. 예를들어 64GB캐시의 경우 캐시 워밍업 파일은 128MB이다. 캐시워밍에 대한 자세한 내용은 MySQL 공식 문서인 아래 링크에서 확인할 있다.

l  Saving and Restoring the Buffer Pool State : https://dev.mysql.com/doc/refman/8.0/en/innodb-preload-buffer-pool.html

 

AWS RDS에서는 MySQL 5.6 버전 이상에 대해서 InnoDB 캐시 워밍업을 지원한다. InnoDB 캐시 워밍업을 활성화 하려면 DB 인스턴스의 파라미터 그룹에서 innodb_buffer_pool_dump_at_shutdown,  innodb_buffer_pool_load_at_startup 파라미터를 1 설정해야 한다. 해당 파라미터 값을 변경하면 해당 파라미터 그룹을 사용하는 모든 MySQL DB 인스턴스에 영향이 있기 때문의 사용시 주의하도록 한다. 특정 MySQL 대해서만 InnoDB 캐시 워밍업을 활성화 하려면 해당 인스턴스에 대한 파라미터 그룹을 생성 하도록 한다.

 

InnoDB 캐시 워밍은 주로 표준 스토리지를 사용하는 DB 인스턴스에 대한 성능 이점을 제공한다. PIOPS 스토리지를 사용하는 경우 일반적으로 상당한 성능 이점이 나타나지 않는다. InnoDB 캐시 워밍을 활성화 하였더라도 장애 조치중과 같이 MySQL DB 인스턴스가 정상적으로 종료되지 않으면 버퍼풀 상태가 디스크에 저장되지 않는다. 경우 MySQL DB 인스턴스가 다시 시작될 사용 가능한 모든 버퍼 파일을 로드 한다. 이런 경우에도 데이터베이스에는 아무런 이상이 없지만 복원된 버퍼풀은 가장 최근의 상태를 반영하지 않을 수도 있다. 시작 InnoDB 캐시를 워밍업 하는데 사용할 있는 버퍼풀의 최신 상태를 사용하려면 주기적으로 버퍼풀을 덤프하는 것이 좋다.

버퍼풀의 현재 상태를 디스크에 저장하려면 아래 프로시저를 호출 한다.

CALL mysql.rds_innodb_buffer_pool_dump_now();

 

디스크에 저장된 버퍼풀의 상태를 로드하려면 아래 프로시저를 호출 한다.

CALL mysql.rds_innodb_buffer_pool_load_now();

 

진행중인 로드 작업을 취소하려면 아래 프로시저를 호출 한다.

CALL mysql.rds_innodb_buffer_pool_load_abort();

 

버퍼풀을 주기적으로 자동으로 덤프하는 이벤트를 생성하여 사용할 경우 수동으로 매번 호출할 필요가 없다. 아래 스크립트는 매시간 버퍼풀을 덤프하는 periodic_buffer_pool_dump라는 이벤트를 생성한다.

CREATE EVENT periodic_buffer_pool_dump
ON SCHEDULE EVERY 1 HOUR
DO CALL mysql.rds_innodb_buffer_pool_dump_now();

 

 

[참고자료]

l  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MySQL.html#MySQL.Concepts.VersionMgmt

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

l  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_innodb_buffer_pool_dump_now.html

l  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_innodb_buffer_pool_load_now.html

l  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_innodb_buffer_pool_load_abort.html

 

 

 

2022-03-17 / Sungwook Kang / http://sungwookkang.com

 

 

AWS RDS, MySQL, InnoDB Cache warming, 캐시워밍업, 버퍼풀, buffer pool

MySQL/MariaDB InnoDB(Storage) buffer pool 모니터링

 

·      Version : MySQL 5.7, Docker

 

MySQL/MariaDB에서는 다양한 스토리지 엔진을 지원하며 스토리지마다 특성 또한 다르다. 스토리지 엔진에 대한 특성은 아래 링크를 참고 한다.

·       Comparison of MySQL database engines : https://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines

 

이번 포스트에서는 일반적으로 OLTP 서비스에 많이 사용하는 InnoDB 스토리지 엔진에서 buffer pool(버퍼풀) 관련한 모니터링 방법에 대해서 알아본다. 아래 스크립트를 실행 하면 InnoDB buffer pool 대한 값을 확인할 있다.

Show status where variable_name like ‘innodb_buffer%’;

 

 

Variable Name

Comments

Innodb_buffer_pool_dump_status

버퍼풀에 보관된 페이지를 기록한 상태

Innodb_buffer_pool_load_status

버퍼풀이 이전 시점에 해당하는 페이지를 읽음으로 워밍업을 진행한 페이지

Innodb_buffer_pool_resize_status

버퍼풀의 크기를 동적으로 조정하는 작업 상태

Innodb_buffer_pool_pages_data

전체 버퍼풀에서 현재 사용중인 버퍼풀 페이지

Innodb_buffer_pool_bytes_data

전체 버퍼풀에서 현재 사용중인 버퍼풀 바이트

Innodb_buffer_pool_pages_dirty

버퍼풀의 데이터 변경된 페이지 (더티페이지 )

Innodb_buffer_pool_bytes_dirty

버퍼풀의 데이터 변경된 바이트 (더티 데이터 )

Innodb_buffer_pool_pages_flushed

버퍼풀에서 플러시한 페이지

Innodb_buffer_pool_pages_free

전체 버퍼풀에서 사용하지 않은(사용가능한) 페이지

Innodb_buffer_pool_pages_misc

Row lock, hash index 같이 오버헤드에 할당되어 사용된 버퍼풀의 페이지

Innodb_buffer_pool_pages_total

전체 버퍼풀의 페이지

Innodb_buffer_pool_read_ahead_rnd

랜덤으로 미리 읽기가 발생한 페이지

Innodb_buffer_pool_read_ahread

미리 읽기 백그라운드 스레드가 버퍼풀로 미리 읽어들인 페이지

Innodb_buffer_pool_read_ahead_evicted

버퍼풀에 미리 읽어들인 페이지 사용되지 않고 제거된 페이지

Innodb_buffer_pool_read_requests

버퍼풀에서 논리적인 읽기 요청 횟수

Innodb_buffer_pool_reads

버퍼풀에 데이터가 없어서 디스크에서 직접 읽은 논리적

Innodb_buffer_pool_wait_free

페이지를 읽거나 생성할때 사용가능한 클린 페이지가 없을 경우 InnoDB 더티페이지를 비우고 작업이 끝나기를 기다리는

Innodb_buffer_pool_write_requests

버퍼풀에 대한 쓰기 횟수

 

InnoDB에서 버퍼풀의 크기는 MySQL 서버 변수에서 매우 중요한 부분이다. 너무 크게 설정하면 다른곳에서 사용할 메모리가 줄어들고, 너무 작게 설정하면 빈번한 디스크로의 요청이 발생하여 IO 부담을 가중시킬 있다. 일반 적으로 DB서버의 경우 DB외에 다른 서비스와 함께 사용하지 않으므로 OS 필요한 공간( + 여유 공간) + MySQL 필요 공간(예상 커넥션 , 스레드 , 소트 메모리 등등) 제외하고 버퍼풀로 사용하는경우가 많다.

 

버퍼풀 모니터링에서 중요하게 살펴보아야 하는 부분이Innodb_buffer_pool_pages_flushed 항목이다. 이는 버퍼풀의 더티 데이터를 디스크에 쓰는 과정으로 과도한 flushed 디스크에 부담을 있다. 또한 한번에 플러시 되는 크기가 디스크 IO 성능에 비례하여 너무 클경우 디스크에서 쓰기 지연 현상이 발생할 있으므로 디스크의 성능과 함께 모니터링 해야한다.

 

Innodb_buffer_pool_reads 수치가 높은경우 버퍼풀의 데이터를 활용하지 못하는것으로 디스크로 부터 직접 데이터를 읽어야 하기 때문에 디스크의 성능 저하가 발생 있다. 일반 적으로 버퍼풀의 크기가 작은 경우 버퍼풀에서 데이터를 오랫동안 캐싱할 없어 빈번한 IO 발생할 있다.

 

 

[참고자료]

https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html

 

 

2018-05-15 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, InnoDB bufferpool, 버퍼풀, 이노디비, MySQL Storage, 스토리지 엔진, buffer pool 모니터링, DBA, MySQL 모니터링



MySQL/MariaDB InnoDB Storage Engine (Data Page)

 

·      Version : MySQL 5.5, 5.6, 5.7

 

MySQL / MariaDB 에서 사용하는 InnoDB 스토리지 엔진은 레코드 기반의 잠금을 제공하기 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다. InnoDB 구조는 크게 메모리 영역과 CPU 연산 영역, 디스크 스토리지 영역으로 구분할 있다. 메모리 영역에는 버퍼풀과 로그 버퍼가 있으며 CPU 연산에는 인서트 버퍼 머지 스레드, Write 스레드, 로그 스레드, 그외 기타 스레드가 있다. 디스크 영역에는 시스템 테이블 스페이스와 사용자 테이블 스페이스, 리두로그가 존재한다.

·       MySQL/MariaDB InnoDB 스토리지 엔진 특성 : http://sqlmvp.kr/220432956246

 

이번 포스트에서는 InnoDB 데이터 Page size Bufer pool에서 페이지가 디스크로 플러쉬 되는 내용에 대해서 알아본다.

Page 디스크(데이터 파일) 메모리(버퍼풀) 사이에서 InnoDB 한번에 전송할 있는 양을 나타내는 단위로Page에는 데이터 양에 따라 최소한 하나 이상의 행이 존재한다. 행이 완전히 단일페이지에 저장되지 않을때에는 다른 페이지에 데이터를 저장하고 해당 페이지에 포인터로 저장 위치를 표시 한다. 데이터를 읽고 쓰는 경우 I/O 처리량을 높이기 위해 한번에 익스텐트식 읽는다.


Page 많은 데이터를 저장하기 위해서는 압축된 페이지 형식을 사용하며 , BLOB, large text 필드 형태의 데이터를 별도의 분리된 테이블에 저장하여 데이터 조회시 해당 열을 참조하지 않는 경우 I/O 오버헤드 메모리 사용을 줄일 있다.

 

Page Size MySQL 5.5까지는 InnoDB에서 16KB 고정되어 있다. MySQL 5.6 부터는 innodb_page_size 설정 옵션에 따라 제어되며 4KB, 8KB, 16KB 지원된다. MySQL 5.7 이후부터는 32KB 64KB 페이지 크기를 지원한다. 32KB 64KB 페이지 크기의 경우 FOW_FORMAT= COMPRESSED 지원되지 않으며 최대 레코드 크기는 16KB 이다.

show Variables where Variable_name like 'innodb_p%';

 


페이지 크기는 MySQL 인스턴스를 생성 설정되며 이후에는 일정하게 유지된다. 페이지 크기는 시스템 테이블 스페이스, 테이블 파일 테이블 스페이스 일반 테이블 스페이스를 포함하여 모든 InnoDB 테이블 스페이스에 동일하게 적용된다.

 

Page cleaner 버퍼풀에서 더티페이지를 플러시할때 MySQL 5.6 이전 버전에서는 master thread 의해서 작업이 수행되었다. 페이지 클리너 스레드의 수는 MySQL 5.7부터  innodb_page_cleaners 구성 옵션에 의해 제어 된다.

 

[참고자료]

·       http://sqlmvp.kr/220432956246

·       https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_page

·       https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_cleaners

 

 

 

2017-10-16 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, MariaDB, InnoDB, InnoDB Storage Engine, Mysql Page size, innodb extent, 스토리지 엔진, 버퍼풀, 이노DB,




SQL Server Page Life Expectancy (PLE)

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

SQL Server Page Life Expectancy(PLE)는 페이지 참조 없이 데이터가 버퍼 풀에 남아 있는 시간으로 성능 카운터의 SQL Server:Buffer Manager, SQL Server:Buffer Node에서 현재 값을 확인 할 수 있다.

 

  • SQL Server:Buffer Manager / Page life expectancy : 페이지가 참조 없이 버퍼풀에 남아 있는 시간(초)
  • SQL Server:Buffer Node / Page life expectancy : 페이지가 참조 없이 노드에서 버퍼풀에 남아 있는 시간(초).

 

여기에 표시되는 값으로 버퍼풀에 데이터가 머무르는 시간을 확인 할 수 있다. 하지만 여기에서 나타내는 PLE 값이 정말 유용한 정보인지 생각해 보아야 한다. 오늘날의 대부분 시스템들은 NUMA를 사용한다. 그래서 버퍼 풀은 각 NUMA 노드로 분리된 자신의 버퍼풀 목록을 관리한다.

 

Buffer Manager: Page life expectancy카운터는 각 노드의 버퍼풀의 PLE를 추가한 다음 평균을 계산한다. 이 수치는 우리가 생각하는 산술평균보다 값이 낮다. (버퍼 관리자의 비율 평균 : http://en.wikipedia.org/wiki/Harmonic_mean). 이 뜻은 각 NUMA 노드에서 어떤 일이 일어나는지 진정한 의미가 부여 되지 않음을 의미한다.

 

예를 들어 8 NUMA 노드에서 PLE가 4000이면 전반적인 PLE는 4000이다.

4 / (1/(1000 x 4000) + 1/(1000 x 4000) + 1/(1000 x 4000) + 1/(1000 x 4000)) / 1000 = 4000

 

하나의 노드 PLE가 2200으로 떨어지면 전체는 3321이 된다.

4 / (1/(1000 x 2200) + 1/(1000 x 4000) + 1/(1000 x 4000) + 1/(1000 x 4000)) / 1000 = 3321

 

하나의 노드 PLE가 200으로 떨어진다면 전체는 695가 된다.

4 / (1/(1000 x 200) + 1/(1000 x 4000) + 1/(1000 x 4000) + 1/(1000 x 4000)) / 1000 = 695

 

이 수치는 서버가 전반적으로 강한 압력을 받고 있다고 생각되지만 하나의 노드를 제외한 나머지는 아무런 문제가 없다. 따라서 버퍼풀의 압력에 대한 정확한 뷰를 확인하여면 각 노드에서의 PLE를 확인하여 경고에 대한 적절한 임계값을 찾아야 한다.

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

http://blogs.msdn.com/b/psssql/archive/2015/05/14/sql-server-page-life-expectancy-ple.aspx

 

2015-05-20 / 강성욱 / http://sqlmvp.kr

 

Sqlserver, mssqlserver, Page life expectancy, PLE, 버퍼풀, buffer pool, NUMA

+ Recent posts