MySQL Buffer Pool 조정하기

1.  MySQL Buffer Pool이란?

MySQL 버퍼 풀은 InnoDB 스토리지 엔진에서 사용하는 메모리 영역으로, 자주 사용되는 테이블 데이터와 인덱스 페이지를 캐싱하여 쿼리 성능을 향상시킵니다. 즉, 디스크에서 데이터를 읽는 대신 메모리에서 직접 데이터에 접근하여 훨씬 빠른 속도로 쿼리를 처리할 수 있게 해줍니다.

 

버퍼 풀의 주요 기능:

• 데이터 캐싱: 자주 사용되는 테이블 데이터와 인덱스 페이지를 메모리에 저장하여 디스크 I/O 작업을 줄임
• 쿼리 성능 향상: 디스크 접근 시간이 훨씬 느린 것에 비해 메모리 접근 속도가 훨씬 빠르기 때문에 쿼리 처리 속도를 크게 향상
• 데이터 접근성 향상: 버퍼 풀에 캐시된 데이터는 디스크에 저장된 데이터보다 훨씬 빠르게 접근할 수 있음
• 기록 순서 조정 : 데이터 파일과 로그 파일이 기록되는 순서를 조정하는 역할

 

버퍼 풀 크기 설정:

• innodb_buffer_pool_size: 버퍼 풀 크기를 설정하는 변수
• 버퍼풀의 크기가 클수록 성능에 유리하나 시스템 메모리의 성능대비해서 50%이하부터 점점 올려 설정하는 것을 권장
  
  • 100%로 하지 않는 이유 : 100%로 설정하면 다른 시스템 작업에 사용할 메모리가 부족해져 메모리 할당 오버헤드가 증가, 또한버퍼 풀 크기가 100%라도 모든 데이터를 메모리에 저장할 수 없는 경우가 생기므로 디스크 성능저하를 일으킴
  • 50% 이하로 테스트하는 이유 : 초기 스타트업의 경우 8GB 미만의 메모리를 가진 데이터베이스를 많이 쓸것이다. 이 때 50%만 버퍼풀로 설정하며 다른 프로그램이 사용할 수 있는 메모리 공간을 확보해줘야하는 것이다. 만약 데이터베이스를 서버리스로 사용해서 그 서버가 오로지 데이터베이스를 위한 메모리로 사용할 수 있다면 80% 이상으로 해도되겠지만, 한 인스턴스 안에 데이터베이스가 어플리케이션 서버와 같이 있는 경우에는 상당히 비추천한다.

 

버퍼 풀 관련 주요 용어:

• LRU (Least Recently Used): 버퍼 풀에서 가장 오랫동안 사용되지 않은 페이지를 제거하는 알고리즘
• Hit Ratio: 버퍼 풀에서 필요한 데이터를 찾을 수 있는 비율
• Page Fault: 버퍼 풀에서 필요한 데이터를 찾지 못하고 디스크에서 읽어야 하는 경우

버퍼 풀 성능 최적화:

• 버퍼 풀 크기를 적절하게 설정: 워크로드 특성에 따라 적절한 크기로 조정
• LRU 알고리즘 이해: LRU 알고리즘의 작동 방식을 이해하고 필요에 따라 조정
• Hit Ratio 모니터링: Hit Ratio를 모니터링하여 버퍼 풀 크기가 적절한지 확인
• Page Fault 감소: Page Fault를 줄이기 위해 버퍼 풀 크기 조정 또는 쿼리 최적화

 

---

 

2.  언제 Buffer Pool을 수정해야할까?

조회 Read를 읽는데 문제가 생긴다면 슬로우 쿼리 파악이 우선이다.

그 슬로우 쿼리에서 코드상 문제가 있는지, 정규화/비정규화가 잘되어 있는지, 인덱스 설계가 잘 되어있는지 등을 파악해야한다.

만약, 인덱스까지도 파악했는데 slow query가 해결되지 않는다면 버퍼 풀 메모리를 의심해볼 필요가 있다.

 

대부분 innoDB 스토리지 엔진을 사용중일것이다.(5.5이후 기본 스토리지 엔진이 innodb) 

그때 innodb_buffer_pool_size 파라메터를 의심해봐야 한다.

해당 파라메터의 크기가 클수록 쿼리 실행시 디스크보다 메모리를 사용하게 되어 빠른 결과를 얻을 수 있다.

 

버퍼 풀 메모리가 충분히 큰 양으로 할당되어 있다면 innodb는 in-memory 데이터베이스처럼 동작한다.

Access를 위한 select 데이터 뿐 아니라, Insert 및 Update 작업에도 도움이 되는 캐싱을 하기 때문에 적절하게 조정하여 사용하는 것이 핵심이다.

 

참고

InnoDB:

• 알고리즘:
  • 데이터 저장 및 관리: B+ 트리
  • 버퍼 풀 관리: LRU 
• 장점:
  • 안정적이고 안전한 트랜잭션 처리
  • 높은 성능
  • 데이터 무결성 보장
• 단점:
  • Memory 엔진보다 느림
• 적합한 상황:
  • 대부분의 상황
  • 트랜잭션 처리가 필요한 경우
  • 데이터 무결성이 중요한 경우

 

MyISAM:

• 알고리즘:
  • 데이터 저장: 해시
  • 인덱스 관리: B+ 트리
• 장점:
  • 빠른 읽기 속도
  • Innodb보다 빠름
• 단점:
  • 트랜잭션 처리 기능 부족
  • 데이터 무결성 보장 약함
• 적합한 상황:
  • 읽기 작업이 많은 테이블
  • 빠른 읽기 속도가 중요한 경우

 

Memory:

• 알고리즘:
  • 데이터 저장: 해시
• 장점:
  • 엄청난 속도
• 단점:
  • 데이터 손실 위험
  • 대용량 데이터에 적합하지 않음
• 적합한 상황:
  • 작은 테이블
  • 성능이 중요한 테이블

---

 

3. MYSQL Buffer Pool 설정으로 트랜잭션 Lock 경합 줄이기

innoDB를 선택한 이후인 5.5버전부터 buffer pool instance 수를 설정할 수 있게 되었다.

멀티 스레드 구조인 Mysql에서 스레드 간 버퍼 풀 조작에서 Exclusive Lock 처리가 필요한데, 이 때 버퍼 풀접근을 위해 뮤텍스를 사용하고 동시 다발적으로 접근 시 뮤텍스에 대한 경합이 발생한다.

인스턴스 수를 늘릴수록 많은 수의 스레드가 동시에 버퍼 풀에 접근하더라도 Lock 경합을 피할 수 있다.

CPU 코어 수가 많은 시스템일수록 인스턴스 수를 늘릴 수 있다고 보면 된다.

 

1. MySQL 설정 파일 (my.cnf)

• innodb_buffer_pool_instances 옵션을 사용하여 인스턴스 수를 설정
• 기본값은 8
• 1 이상의 값을 설정
• 설정 예시:

innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=8 (core 수 * 2로 계산)

 

innodb_buffer_pool_size = innodb_buffer-_pool_instances * innodb_buffer_pool_chunk_size
→ 버퍼 풀 크기는 자동으로 innodb-buffer-pool-instances * innodb_buffer_pool_chunk_size로 조정됨

 

2. 서버 실행 시 --innodb_buffer_pool_instances 옵션 사용

• 서버를 실행할 때 --innodb_buffer_pool_instances 옵션을 사용하여 인스턴스 수를 지정
• 설정 예시:

mysqld --innodb_buffer_pool_size=1G --innodb_buffer_pool_instances=8

 

주의 사항:

• 버퍼 풀 인스턴스 수를 변경하기 전에 시스템에 충분한 메모리가 있는지 확인해야 함
• 인스턴스 수를 증가하면 성능이 향상될 수 있지만, 메모리 사용량도 증가한다는 것을 알아야함
• 변경 후에는 MySQL 서버를 다시 시작해야 수정된 파일이 반영됨(mysql start 명령어)

 

---

4. MYSQL Buffer Pool 설정으로 파일 캐시 배제

버퍼 풀에서 이미 데이터에 대한 캐시 역할을 하기 때문에, 데이터 파일에 대한 I/O 를 수행할 때 파일 시스템 캐시를 중복으로 이용할 필요가 없다.
따라서, 파일 캐시를 이용하지 않고 직접 쓰도록 하기 위해 데이터 파일을 Open 할 때 O_DIRECT 모드를 설정하는데, 이는 innodb_flush_method 파라미터를 통해 설정할 수 있다.
파일 캐시를 사용하지 않는 것이 좋은 또 한가지 이유는 시스템 파일 캐시의 공격적인 메모리 사용에 있다. 

O_DIRECT 모드를 사용하지 않으면 시스템이 여유 메모리를 파일 캐시에 공격적으로 할당하여 메모리를 쉽게 고갈시킬 수 있다. 

 

InnoDB 플러시 모드는 InnoDB 버퍼 풀에서 변경된 데이터 페이지를 디스크로 플러시(플러시란 데이터를 메모리에서 디스크로 이동하는 것)하는 방식을 결정한다. 다음과 같은 세 가지 모드가 있다:

• 0 (기본값): 변경된 페이지를 비동기적으로 플러시한다. 이는 성능을 향상시키지만 데이터 손실 위험을 증가시킬 수 있다.
• 1: 변경된 페이지를 동기적으로 플러시한다. 이는 데이터 무결성을 보장하지만 성능을 저하시킬 수 있다.
• 2: 변경된 페이지를 fsync() 호출 후 플러시한다. 이는 1과 유사하지만 fsync() 호출의 오버헤드를 추가한다.
• 설정 예시:

innodb_flush_method=1

 

---

 

5. I/O 용량의 중요성

 

 

1. 스토리지 I/O 성능과 InnoDB 데이터 변경 총량:

1. InnoDB는 데이터베이스 엔진으로서 데이터를 디스크에 저장하고 관리
2. 데이터 변경(삽입, 수정, 삭제) 작업이 발생하면 InnoDB는 변경된 데이터를 메모리 버퍼에 저장
3. 버퍼가 가득 차면 변경된 데이터는 디스크로 플러시
4. 디스크 I/O 성능이 데이터 변경 총량보다 낮으면 플러시 작업이 지연되어 성능 저하가 발생
5. 따라서 스토리지 I/O 성능이 InnoDB 데이터 변경 총량을 충분히 커버할 수 있도록 설정해야함

2. InnoDB 자동 플러시 속도 조절:

• InnoDB는 디스크 속도에 따라 자동으로 플러시 속도를 조절하지 않는다.
• 따라서 디스크 속도가 느리면 플러시 작업이 지연되어 성능 저하가 발생하는게 당연하다.
• 이를 방지하기 위해 innodb_io_capacity 값을 설정으로 초당 처리할 수 있는 I/O 값을 늘려야한다.

3. innodb_io_capacity 설정:

• innodb_io_capacity는 InnoDB가 초당 처리할 수 있는 I/O 작업 수를 말한다.
• 값은 초당 IOPS(Input/Output Operations Per Second) 단위로 설정한다.
• 현재 사용하고 있는 디스크의 IOPS와 유사한 값으로 설정해야 한다.
  • 디스크 벤치마킹 도구(FIO, BONNIE++), 시스템 모니터링도구(sar, iostat), 디스크 제조업체 정보로 디스크의 IOPS 확인
  • 예를 들어, 디스크의 IOPS가 1000이면 innodb_io_capacity 값을 1000으로 설정

4. 설정 시 고려 사항:

• innodb_io_capacity 값을 너무 높게 설정하면 시스템의 다른 작업에 영향을 줌
• 값을 너무 낮게 설정하면 InnoDB 플러시 작업이 지연되어 성능 저하가 발생하게 됨

5. 설정 방법:

• MySQL 설정 파일 (my.cnf)에 innodb_io_capacity 옵션을 추가
• 예시:

innodb_io_capacity=1000

 

---

참고

https://owlyr.tistory.com/23

http://cloudrain21.com/mysql-innodb-basic-performance-tunning

https://velog.io/@yangsijun528/MySQL-%EC%8A%A4%ED%86%A0%EB%A6%AC%EC%A7%80-%EC%97%94%EC%A7%84

 

https://dev.mysql.com/doc/refman/8.3/en/innodb-buffer-pool-flushing.html