공유할 서비스 선택

TECH


TECH

ETC [ MariaDB ] MariaDB InnoDB buffer_pool 설정 및 사용량 확인

페이지 정보

작성자 Leesangwoo 아이디로 검색 전체게시물 댓글 0건 조회 142회 좋아요 0회 작성일 22-06-29 11:20

본문

1. InnoDB buffer_pool란?

  - 메인 메모리 내에서 데이터과 인덱스 데이터가 접근될 때 해당 데이터를 캐시하는 영역이다. 

    버퍼 풀을 통해서 자주 접근되는 데이터를 메모리에서 바로 획득할 수 있으며 전체 작업의 수행 속도를 증가 시킬 수 있다. 

    MySQL을 위한 서버에서는 물리 메모리의 최대 80%까지를 InnoDB의 버퍼 풀로 할당하여 사용하는 경우가 많다.

  

  - LRU 알고리즘

   위에서 언급했듯이 InnoDB의 버퍼풀은 링크드 리스트를 이용하여 least recentrly used (LRU) 알고리즘을 통해 관리한다. 

   새로운 페이지를 버퍼풀에 추가하기 위한 페이지 공간이 필요한 경우, 접  근이 가장 오래된 페이지를 선정하여 버퍼풀에서 제거하고, 

   새로운 페이지를 리스트의 중간지점에 삽입하는데 이를 midpoint insertion이라고 한다. 중간지점 삽입 전략을 통해 버퍼풀 전체 리스트를 아래에서 설명할 두가지 리스트로 나누어 관리한다.

    • 전체 리스트의 head는, 최근에 접근된 (young) 페이지들의 리스트를 가리킨다.

    • 전체 리스트의 tail은, 접근 시기가 가장 오래된 (old) 페이지를 가리킨다.


2. 버퍼풀을 리스트 형태로 관리하는 구조

c77fad2280f251e356baddf2279d6166_1656466942_2764.png
이러한 알고리즘을 통해 사용자 쿼리에서 가장 자주 접근 되는 데이터 페이지들을 new sublist에 보관한다. 
old sublist에는 잘 접근 되지 않은 데이터를 저장하며, 이후에 eviction 대상으로 선정된다.

기본적으로 알고리즘 전체 순서는 아래와 같다.
버퍼 풀의 3/8 은 old sublist로 사용된다.
버퍼 풀 전체의 midpoint는 new sublist의 tail과 old sublist의 head가 만나는 지점이다.
InnoDB가 새로운 데이터 페이지를 버퍼 풀로 읽어오는 경우에는, midpoint (old sublist의 head)에 삽입한다. 
      여기서 읽어오는 데이터에는 실제 사용자 쿼리에 의해 요청된 데이터도 있을 수 있지만, read-ahead에 의해 자동적으로 읽어오는 데이터도 포함된다.
old sublist에 존재하는 페이지를 접근할 경우 해당 페이지는 “young”이라고 판단 되며, 버퍼풀 전체의 head (new sublist의 head)로 이동된다. 
      사용자 쿼리나 중간 작업에 의해 실제로 필요해서 접근된 페이지의 겨웅 곧바로 young으로 변경되지만, read-ahead 작업에 의해 접근된 페이지의 경우에는 young으로 취급하지 않는다.
데이터베이스의 작업이 계속 진행됨에 따라서, 버퍼풀에 상주하는 데이터 페이지들 중 자주 접근 되지 않는 페이지는 자연스럽게 리스트이 tail로 이동하게 된다. 
      이는 직접적으로 데이터를 tail로 옮기는 것이 아닌, young 이 되는 페이지가 앞으로 이동하면서 발생한다. 
      추가로 old sublist의 페이지들은 새로운 페이지가 midpoint에 삽입 되면서 tail에 가까워지게 된다. 
      페이지 eviction시점까지 접근되지 않고 tail에 남아있는 페이지는 결국 버퍼 풀에서 삭제되게 된다.

기본적으로 read 쿼리에 의해 직접 접근되는 페이지들은 곧바로 new sublist로 이동하여 더 오랜기간 버퍼 풀에 상주할 수 있게 된다. table scan 쿼리 (mysqldump, 나 WHERE가 없는 SELECT 쿼리) 에 대량의 데이터가 한번에 버퍼 풀에 삽입되는 경우, midpoint 삽입에 의해 old sublist의 대량 데이터들이 모두 버퍼풀에서 삭제되게 된다. 이와 유사하게, read-ahead 작업에 의해 midpoint에 삽입 되었던 데이터가, 단 1번만 접근되더라도 new sublist의 head로 이동하게 되어, 실제 자주 접근되는 데이터가 old tail에 가까워지는 일이 발생할 수도 있다. 이러한 현상을 최적화 하기 위해서는  Making the Buffer Pool Scan ResistantConfiguring InnoDB Buffer Pool Prefetching (Read-Ahead) 를 읽어보길 바란다.


InnoDB 전체 성능 향상을 위해서 여러가지 측면의 설정을 변경할 수 있다. 아래는 설정에 관한 설명이다.
이상적으로는, 같은 서버에서 동작하는 다른 프로세스들이 쓸만큼 충분한 공간을 남겨둔 채, 대부분의 메모리 공간을 InnoDB의 버퍼 풀로 할당하는 것이 가장 좋다. 
      버퍼풀의 크기가 클 수록 InnoDB는 in-memory 데이터베이스 처럼 동작하게 되고, 이는 데이터를 디스크에서 한번만 읽으면 되고 이후에는 계속 버퍼풀에서 읽을 수 있게 됨을 말한다. 
      이러한 설정을 위해서는 Configuring InnoDB Buffer Pool Size을 참조하길 바란다.
충분한 메모리가 있는 64비트 환경에서는 버퍼 풀을 여러 파트로 쪼개서 관리할 수 있다. 
      이를 통해서 다중 사용자 환경에서 하나의 버퍼풀에 발생하는 여러가지 경합 현상을 완화할 수 있다. 
      이와 관련된 설정은Configuring Multiple Buffer Pool Instances 를 참조하길 바란다.
테이블 스캔이나 read-ahead 같은 특정한 상황속에서 많은 데이터가 읽어져 버퍼 풀의 기존 데이터를 삭제하는 경우에도, 
      사용자가 원하는 데이터를 버퍼풀에 계속해서 상주하도록 만들 수 있다. 이와 관련된 설정은 Making the BUffer Pool Scan Resistant를 참조하길 바란다.
곧 요청될 데이터를 미리 예측하여 사용자의 실제요청과 관계없이 비동기적으로 미리 데이터 페이지를 읽어오는 read-ahead 작업을, 언제/어떤방식으로 수행할 지 직접 지정할 수 있다. 
      이와 관련된 설정은 Configuring InnoDB Buffer Pool Prefetching (Read-Ahear)를 참조하길 바란다.
버퍼풀의 데이터에 대한 백그라운드 플러시 작업을 얼마나 자주 수행할지, 혹은 플러시 주기를 자동적으로 조절할지 말지에 대한 설정을 할 수 있다. 
       이와 관련된 설정은 Configuring InnoDB Buffer Pool Flushing를 참조하기 바란다.
버퍼 풀 플러시와 관련하여 더 큰 성능 향상으로 위해 세밀하게 조절할 수 있다. 이와 관련된 설정은 Fine-tuning InnoDB Buffer Pool Flushing을 참조하기 바란다.
서버를 재시작 한 이후에 기존의 버퍼 풀 상태를 그대로 유지하여, 길어질 수 있는 warm-up 시간을 줄일 수 있다. 
      이와 관련된 설정은 [Saving and Restoring the Buffer Pool State]를 참조하기 바란다.


innodb_buffer_pool_instances = innodb_buffer_pool_chunk_size *  innodb_buffer_pool_instances
만약 버퍼 풀이 초기화 될 때 새 innodb_buffer_pool_chunk_size 값 * innodb_buffer_pool_instances가 현재 버퍼 풀 크기보다 큰 경우 innodb_buffer_pool_chunk_size는 innodb_buffer_pool_size / innodb_buffer_pool_instances로 강제 적용된다.
###innodb_buffer_pool_instances 이거는 buffer pool size 를 몇개의 쓰레드로 나눌지. buffer pool size 가 1GB 이상일때만 사용가능

c77fad2280f251e356baddf2279d6166_1656468744_6283.png

그리고 Byte 단위이기 때문에 알아보기 힘들 수 있다.

c77fad2280f251e356baddf2279d6166_1656468778_7329.png

SELECT @@innodb_buffer_pool_size/1024/1024/1024;
하면 알아서 계산해 준다.

c77fad2280f251e356baddf2279d6166_1656468812_5592.png
--------------------------------------------------
SELECT SUBSTRING_INDEX(event_name,'/',2) AS
       code_area, FORMAT_BYTES(SUM(current_alloc))
       AS current_alloc
       FROM sys.x$memory_global_by_current_bytes
       GROUP BY SUBSTRING_INDEX(event_name,'/',2)
       ORDER BY SUM(current_alloc) DESC;
--------------------------------------------------
메모리 할당량을 살펴보면 1.09GB 이다 
이것은 할당 값보다 대량 10% 추가로 할당이 된다고 한다.

--------------------------------------------------
### INNODB Spectific options
default-storage-engine = InnoDB
#skip-innodb
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1024MB
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
--------------------------------------------------
my.cnf 의 Inno DB 설정이다.


- Inno DB buffer pool 상태 확인 

 

 

c77fad2280f251e356baddf2279d6166_1656468902_5043.png
 

SHOW STATUS LIKE 'Innodb_buffer%'; 

확인하면 현재 Innodb_buffer 로 시작하는 상태 값을 확인 할 수 있다.



- InnoDB Standard Monitor와 버퍼 풀 모니터링 

 

c77fad2280f251e356baddf2279d6166_1656468937_168.png
 

SHOW ENGINE INNODB STATUS \G; 

쿼리를 통해 확인할 수 있는 InnoDB의 기본 모니터링 툴에는 버퍼 풀과 관련된 작업에 대한 통계도 제공한다. 버퍼 풀과 관련된 통계자료는 모니터링 아웃풋의 BUFFER POOL AND MEMORY 섹션에서 확인할 수 있으며, 아래의 내용과 유사하게 출력된다.

 

항목 이름 출력되는 내용
Total memory allocated 버퍼 풀에 할당 전체 메모리의 바이트 단위 크기
Dictionary memory allocated InnoDB 데이터 딕셔너리에 할당된 메모리의 바이트 단위 크기
Buffer pool size 버퍼 풀에 할당된 페이지의 갯수
Free buffers 버퍼 풀의 free list 존재하는 페이지의 갯수
Database pages 버퍼 풀의 LRU 전체 리스트에 존재하는 페이지의 갯수
Old database pages 버퍼 풀의 old LRU sublist 존재하는 페이지의 갯수
Modified db pages 현재 버퍼 풀에서 dirty (변경) 페이지의 갯수
Pending reads 버퍼 풀에 삽입되길 기다리는 앞으로 read page 갯수
Pending writes LRU old sublist 존재하는 페이지 disk 플러시 되어야 하는 old page 갯수
Pending writes flush list 버퍼풀 전체 리스트 체크 포인트 시에 플러시 되어야 하는 페이지의 갯수
Pending writes single page 버퍼풀의 전체 리스트 중에서 single page write 기다리는 페이지 갯수
Pages made young 버퍼풀 전체 LRU 리스트에서 “young”으로 변경된 (old sublist에서 new sublist 이동 ) 페이지 갯수
Pages made not young 버퍼풀 전체 LRU 리스트에서 “Young”으로 변경되지 못한 (old sublist에만 계속 상주하는) 페이지 갯수
youngs/s old sublist 존재하는 페이지를 “young”으로 변경하는 페이지 접근 횟수를 초단위로 측정한 . 자세한 내용은 아래의 note 참조
non-youngs/s old sublist 접근은 하지만 “young”으로 만들지 않는 접근 횟수. 자세한 내용은 아래의 note 참조
Pages read 버퍼 풀에서 read 페이지 갯수 (hit 인듯)
Pages created 버퍼 내에 생성된 페이지 갯수
Pages written 버퍼 풀에서 disk 쓰여진 페이지의 갯수
reads/s 초당 버퍼풀 페이지 읽기 횟수
creates/s 초당 버퍼풀에 생성되는 페이지 갯수
writes/s 초당 버퍼풀에서 disk write되는 페이지 갯수
Buffer pool hit rate 버퍼 히트율. 버퍼 메모리 네에서 바로 읽히는 페이지 vs 디스크에서 읽어오는 페이지
Young-making rate 페이지 접근이 young 페이지를 생성하는 작업을 유발하는 경우에 대한 비율
not (young-making rate) 페이지를 접근 함에도 young 페이지 생성을 하지 않는 경우에 대한 비율. 위와 반대이다.
Pages read ahead 초당 read ahead 작업 횟수
Pages evicted without access 버퍼풀에서 접근되지 않은 데이터 페이지들의 초당 eviction 페이지 갯수
Random read ahead 초당 random read ahead 작업 횟수
LRU len 버퍼풀 전체 LRU 리스트의 페이지 갯수
unzip_LRU len 버퍼풀 전체 unzip_LRU 리스트의 페이지 갯수
I/O sum 최근 50초간 버퍼풀 전체 LRU 리스트에서 접근된 페이지
I/O cur 전체 기간동안 버퍼풀 전체 LRU 리스트에서 접근된 페이지
I/O unzip sum 최근 50초간 버퍼풀 전체 unzip_LRU 리스트에서 접근된 페이지
I/O unzip cur 전체 기간동안 버퍼풀 전체 unzip_LRU 리스트에서 접근된 페이지

  

youngs/s는 old sublist 에 있는 페이지들에 대해서만 측정된다. 
      평균값은 전체 페이지 갯수에 대한 평균이 아닌, 페이지 접근 횟수에 대한 평균값이다. 동일한 페이지에 여러번의 접근이 발생할 경우 해당 접근 횟수를 모두 계산한다. 
      대량 읽기 작업이 없음에도 불구하고 매우 적은 수의 youngs/s가 기록된 경우에는 young 판별을 위한 delay time을 줄이거나, old sublist가 버퍼 풀에서 차지 하는 비중을 늘릴 필요가 있다.
      old sublist의 비중을 늘릴 경우 페이지 들이 old tail까지 도달하는데 더 많은 시간이 걸리고, 그 추가된 기간동안 다시 접근 되어 young으로 변할 가능성을 늘릴 수 있다.
non-youngs/s는 old sublist에 있는 페이지들에 대해서만 측정된다. 평균값은 전체 페이지 갯수에 대한 평균이 아닌, 페이지 접근 횟수에 대한 평균값이다. 
      동일한 페이지에 여러번의 접근이 발생할 경우 해당 접근 횟수를 모두 게산한다. 
      대량의 테이블 스캔 (1번만 읽고 버리는 페이지를 대량 생성)을 수행함에도 불구하고 non-youngs/s가 작다면, young으로 판별을 위한 delay를 늘려서 불필요한 youngs를 방지할 수 있다.
young-making 비율 통계는 old sublist만이 아닌 버퍼 풀 전체 리스트를 대상으로 측정한 값이다. 
      young-making 비율이나 not 비율은 버퍼 풀의 전체 히트 레이트보다는 거의 낮은 값을 갖는다. 
      old sublist에 존재하는 페이지에 대한 히트는 해당 페이지를 new sublist로 옮기는 작업을 유발하고, 
      new sublist에 존재하는 페이지에 대한 히트는 해당 페이지가 new head에서 일정 거리만큼 멀어진 경우에만 다시 new head로 옮기는 작업을 유발한다.
not (young-making rate)는 innodb_old_blocks_time 설정에 의해 정의된 delay 시간에 의해 페이지 접근이 young-making으로 판별되지 않는 접근이나
      new sublist에 대한 접근 중 new head로 이동하는 작업을 유발하지 않는 접근의 비율을 나타낸다. 
      이 값은 위의 설명처럼 old sublist뿐만 아닌 버퍼풀의 전체 LRU 리스트에 대한 값을 측정한다.

버퍼 풀의 server status variables나 INNODB_BUFFER_POOL_STATS 테이블도 InnoDB의 기본 모니터링 아수풋에서 볼 수 있는 결과값들을 제공한다. 자세한 내용은 Querying the INNODB_BUFFER_POOL_STATS Table를 참조하기 바란다.



- Inno DB buffer pool 사용량 확인 

 

c77fad2280f251e356baddf2279d6166_1656469185_3242.png
Buffer_pool 사용량은 33882112 라고 한다. 

 

c77fad2280f251e356baddf2279d6166_1656469212_0643.png
 

Database pages X 16KB [ InnoDB page size (default 16KB) ] X 1024 

2068 X 16 X 1024 = 33882112 ( 32.3 MB ) 사용 중이다.

65536 ( Buffer pool size ) X 16KB [ InnoDB page size (default 16KB) ] X 1024 = 1073741824 ( 1GB ) 

 

현재 3.1 %의 사용률을 보여주고 있다.


 

 

댓글목록

등록된 댓글이 없습니다.

TECH 목록
번호 제목 작성자 작성일 조회수
133 Middleware 미들웨어 아이디로 검색 전체게시물 09-21 23
(오픈소스 활용-14) Elasticsearch(엘라스틱서치) - start, stop, status

카테고리 : Middleware

23 0
작성자 : 미들웨어 22/09/21
132 Middleware 미들웨어 아이디로 검색 전체게시물 09-07 53
(오픈소스 활용-13) Elasticsearch(엘라스틱서치) - 다운로드 및 설치 방법

카테고리 : Middleware

53 0
작성자 : 미들웨어 22/09/07
131 Middleware 미들웨어 아이디로 검색 전체게시물 08-22 86
(오픈소스 활용-12) "ISTIO 이스티오" 2장 - 설치 및 대시보드

카테고리 : Middleware

86 0
작성자 : 미들웨어 22/08/22
130 Middleware 미들웨어 아이디로 검색 전체게시물 08-08 104
(오픈소스 활용-11) "ISTIO 이스티오" 1장 - 오픈소스 서비스 메시 플랫폼

카테고리 : Middleware

104 0
작성자 : 미들웨어 22/08/08
129 Middleware 미들웨어 아이디로 검색 전체게시물 07-25 137
(오픈소스 활용-10) GC 분석을 빠르고 간편하게 할 수 있는 GCeasy 소개 합니다

카테고리 : Middleware

137 0
작성자 : 미들웨어 22/07/25
128 ETC Leesangwoo 아이디로 검색 전체게시물 07-22 112
[ mssql ] Centos 8 에서 mssql 설치

카테고리 : ETC

112 0
작성자 : Leesangwoo 22/07/22
127 Middleware 미들웨어 아이디로 검색 전체게시물 07-06 159
(오픈소스 활용-9) Jenkins 활용 방법 추가 정리 (Docker 활용, Tomcat 설치없이 기동, 에러로그등)

카테고리 : Middleware

159 0
작성자 : 미들웨어 22/07/06
126 OS LeeHyongDong 아이디로 검색 전체게시물 07-01 275
[Linux] RHEL 9.0 변경 사항

카테고리 : OS

275 0
작성자 : LeeHyongDong 22/07/01
125 ETC Leesangwoo 아이디로 검색 전체게시물 06-30 173
[ Postgresql ] Postgresql 설치 및 구성

카테고리 : ETC

173 0
작성자 : Leesangwoo 22/06/30
열람중 ETC Leesangwoo 아이디로 검색 전체게시물 06-29 143
열람중
[ MariaDB ] MariaDB InnoDB buffer_pool 설정 및 사용량 확인

카테고리 : ETC

143 0
작성자 : Leesangwoo 22/06/29
Total 133건
게시물 검색

주식회사 클럭스| 대표 : 이찬호| 사업자등록번호 : 107-87-27655
주소 : 서울특별시 영등포구 국회대로 800, 여의도파라곤
E-mail : sales@chlux.co.kr
Copyright © 클럭스 www.chlux.co.kr All rights reserved.
상단으로Top