MariaDB buffer_pool_size 변경

공식 메뉴얼

1. mariadb 공식문서
2. mysql 공식문서

참고사항

버퍼풀 사이즈는 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 이여야 한다.
하지만 mariadb는 innodb_buffer_pool_instances 값이 1로 고정이므로, 버퍼풀 사이즈는 innodb_buffer_pool_chunk_size의 배수가 되어야 한다.

• 딱 맞게 변경하지 않으면 버퍼풀사이즈가 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 배수에 맞게 조절된다.
• 또한 성능이슈를 위해서 chunk의 갯수 (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size)는 1000개 이하여야 한다.

온라인 상에서도(기동중) buffer_pool_size를 변경할 수 있다. 다만 active한 트랜잭션이 모두 종료되어야 하고, resize중에 새로 들어오는 트랜잭션은 wait하게 된다.

명령어 -

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

Active transactions and operations performed through InnoDB APIs should be completed before resizing the buffer pool. When initiating a resizing operation, the operation does not start until all active transactions are completed. Once the resizing operation is in progress, new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes. The exception to the rule is that concurrent access to the buffer pool is permitted while the buffer pool is defragmented and pages are withdrawn when buffer pool size is decreased. A drawback of allowing concurrent access is that it could result in a temporary shortage of available pages while pages are being withdrawn.

buffer_pool_size resizing하는 중에 현재 진행상태를 확인할 수있다.

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

또한 mariadb 서버 로그에서도 버퍼풀 리사이징 관련 정보를 확인할 수 있다

Buffer pool resizing progress is also logged in the server error log

• 로그기록2021-12-02 10:56:48 5 [Note] InnoDB: Requested to resize buffer pool. (new size: 4026531840 bytes)
  2021-12-02 10:56:48 0 [Note] InnoDB: Resizing buffer pool from 2147483648 to 4026531840 (unit=134217728).
  2021-12-02 10:56:48 0 [Note] InnoDB: Disabling adaptive hash index.
  2021-12-02 10:56:48 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
  2021-12-02 10:56:48 0 [Note] InnoDB: Latching whole of buffer pool.
  2021-12-02 10:56:48 0 [Note] InnoDB: buffer pool resizing with chunks 16 to 30.
  2021-12-02 10:56:49 0 [Note] InnoDB: 14 chunks (112910 blocks) were added.
  2021-12-02 10:56:49 0 [Note] InnoDB: Completed to resize buffer pool from 2147483648 to 4026531840.
  2021-12-02 10:56:49 0 [Note] InnoDB: Completed resizing buffer pool at 211202 10:56:49.

온라인중 buffer_pool_size 변경 시 내부 동작

The resizing operation is performed by a background thread. When increasing the size of the buffer pool, the resizing operation:
Adds pages in chunks (chunk size is defined by innodb_buffer_pool_chunk_size)
Converts hash tables, lists, and pointers to use new addresses in memory
Adds new pages to the free list
While these operations are in progress, other threads are blocked from accessing the buffer pool
When decreasing the size of the buffer pool, the resizing operation:
Defragments the buffer pool and withdraws (frees) pages
Removes pages in chunks (chunk size is defined by innodb_buffer_pool_chunk_size)
Converts hash tables, lists, and pointers to use new addresses in memory
Of these operations, only defragmenting the buffer pool and withdrawing pages allow other threads to access to the buffer pool concurrently.

---

주의사항

buffer_pool_size 재설정 작업은 critical한 작업이기 때문에 online으로 가능은 하지만, 서버가 여유로운 시간대에 하는 것이 좋다. buffer_pool을 늘리는 작업은 시스템 영향도가 크지 않지만 buffer_pool을 줄이는 작업은 서비스 영향도가 크므로 가능하면 운영중에 하지 않도록 한다.

온라인 상에서도(기동중) buffer_pool_size를 변경할 수 있다. 다만 active한 트랜잭션이 모두 종료되어야 하고, resize중에 새로 들어오는 트랜잭션은 wait하게 된다.

---

궁금증

1.트랜젝션이 활성 상태이면 온란인 중에 사이즈 변경이 안될까 ?

트랜잭션을 활성시켜놓고, 사이즈 변경을 해보자

-> 무조건 안되는건 아니고 상황에따라 되기도 하는 것 같다.

• active한 트랜젝션이 있어도 사이즈 변경가능 하다
• 트랜젝션의 데이터가 줄이고자하는 chunk에 있을 경우에만 작업이 중지되는 것 같다.

2. set 명령어로 변경시, 재기동했을때도 변경상태가 유지되나 ? 아니면 따로 my.cnf 파일을 변경해야 하나 ?

-> 재기동시 반영되지 않는다. 따라서 my.cnf파일을 수정해야 한다.

따라서 운영중에 변경하고자 할때는

1. SET GLOBAL innodb_buffer_pool_size=402653184; 명령어를 통해 기동중에 버퍼풀 사이즈 변경
2. my.cnf 파일 수정하여, 이후 재기동시에도 변경사항 유지되도록 적용

• my.cnf 위치 찾기mysqld --verbose --help | grep -A 1 'Default options'

• 이중화서버( active-standby ) 일 경우 이중화 솔류션은 data영역 동기화만 하기 때문에, 2개 서버 모두 my.cnf파일에서 buffer_pool_size를 변경해주어야 한다.

작업 스크립트

-- 버퍼풀 사이즈 확인
show global variables like '%innodb_buffer_pool_size%'
show global variables like '%innodb_buffer_pool_chunk_size%'
show global variables like '%innodb_buffer_pool_instances%'

-- 프로세스 확인
show processlist


-- 버퍼풀 사이즈 변경
-- chunk 사이즈 * instance 갯수 의 배수로 생성
-- -> 사이즈가 chunk size * intance의 배수가 되지 않으면,  배수에 맞게 조절된다.
-- chunk 사이즈 = select 134217728/1024/1024 (128M)
-- instance = 1개
-- 12G로 변경시 ( select 12000*1024*1024 )
SET GLOBAL innodb_buffer_pool_size=12582912000;

-- buffer_pool 사이즈 변경 상태 확인
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'

-- 변경 상태 확인
show global variables like '%innodb_buffer_pool_size%'
> 12884901888


-- set으로 온라인중에 변경시 , 재기동시에는 반영되지 않는다. my.cnf파일을 변경해야한다.
-- my.cnf 위치 찾기

> mysqld --verbose --help | grep -A 1 'Default options'

> vi /etc/my.cnf

innodb_buffer_pool_size=12G 

 

원문 : https://velog.io/@emawlrdl/MariaDB-bufferpoolsize-%EB%B3%80%EA%B2%BD