[MySQL] InnoDB 버퍼 풀 구조와 로그

Loopy·2023년 7월 25일

InnoDB 버퍼 풀이란, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간이다. 또한 일괄 처리를 위한 쓰기 작업 레코드 버퍼 용도로도 사용된다.

일반적으로 INSERT/UPDATE/DELETE 처럼 데이터를 변경시키는 쿼리는 데이터 파일의 여러 곳에 위치한 레코드를 변경하기 때문에 Random Access 작업을 발생시키기 때문에, InnoDB 버퍼 풀에서 처리하면 이러한 랜덤 디스크 작업 횟수를 줄일 수 있다.

🔖 Random Access란
한 번에 하나의 블록만을 액세스 하는 방식이다. 반대로, 한 번에 여러개의 블록을 액세스 한다면 같은 양의 데이터에 대해 적은 횟수의 디스크 I/O가 발생하기 때문에 성능 향상의 효과를 얻을 수 있다. 주로 인덱스를 액세스하여 확인한 ROWID를 이용하여 다시 테이블을 액세스하는 경우 발생한다. (SELECT 절이나 WHERE 조건 칼럼에 인덱스가 안걸려 있는 경우 재접근 필요)
https://sas-study.tistory.com/448

밑에서 얘기하겠지만, 모든 로그 파일(언두, 리두)은 디스크 영역에 존재한다.

버퍼 풀 크기 설정

일반적으로 운영체제 공간이 8GB 미만이라면, 50% 정도만 InnoDB 버퍼 풀로 설정하고 나머지 메모리 공간은 MySQL 서버와 운영체제에 할당하는 것이 좋다.

기본적으로 MySQL 서버는 적은 메모리 공간을 사용하지만, 드물게 레코드 버퍼에서 큰 메모리를 필요하게 될 수도 있다. 필요 메모리 공간이 전체 커넥션 개수와 커넥션에서 읽기/쓰기 작업이 일어나는 테이블의 개수에 비례하기 때문이다.

버퍼 풀의 크기를 줄이거나 늘릴 때는, 128MB 단위로 처리되므로 동적으로 변경하게 된다면(특히 줄이는 작업) 주의하자.

또한 버퍼 풀을 여러개로 쪼개어, 개별 버퍼 풀 전체를 관리하는 잠금(세마포어)으로 일어나는 내부 잠금 경합을 개선할 수 있다. 1GB 미만일 때는 1개, 그 이상이면 기본이 8개로 초기화 된다. 40GB 이상이라면 버퍼 풀 인스턴스당 5GB 차지하게 인스턴스 개수를 설정하는 것이 좋다.

☁️ 버퍼 풀의 구조

InnoDB 스토리지 엔진은, 버퍼 풀 공간을 페이지 크기 조각으로 쪼개 해당 데이터 페이지를 읽어서 각 조각에 저장한다. 그리고 버퍼 풀 페이지 크기 조각을 관리하기 위해, 크게 3가지 리스트 자료구조를 관리한다.

프리 리스트
실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록이다.
LRU 리스트
디스크로부터 한번 읽어온 페이지를 최대한 오랫동안 버퍼 풀의 메모리에 유지해, 디스크 읽기를 최소화하는데 목적이 있다. 버퍼 풀 내부에서 최근 접근 여부에 따라 데이터 페이지의 포인터가 MRU 또는 LRU 로 이동한다.

LRU 리스트는 그림과 같이 New 공간(MRU)과 Old 공간(LRU)으로 나뉘어진다.
처음 한번 읽힌 데이터 페이지가 자주 사용된다면 MRU 방향으로 승급해 오래 살아남게 되고, 거의 사용되지 않는 데이터 페이지는 새롭게 디스크에서 읽히는 데이터 페이지들에 밀려 LRU 끝으로 밀려나 결국 버퍼 풀에서 제거된다.

플러시(Flush) 리스트

디스크에서 동기화되지 않은 데이터를 가진(변경된 데이터) 더티 페이지의 변경 시점 기준의 페이지 목록을 관리하는 공간이다. 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 추가되고, 리두 로그 및 버퍼 풀의 더티 페이지에도 변경 사항이 반영된다.

☁️ 버퍼 풀과 리두 로그

InnoDB 버퍼 풀에는 크게 두가지 기능이 존재한다.

디스크의 데이터 파일 캐시로 인한 성능 향상
쓰기 버퍼링으로 인한 성능 향상

단순히 버퍼 풀 메모리 크기만 늘려서는 1번 기능 밖에 충족하지 못한다. 쓰기 버퍼링 기능까지 향상시키려면, 리두 로그와 버퍼 풀의 관계를 이해해야 한다.

리두 로그

버퍼 풀은, 디스크에서 읽은 원본을 가지고 있는 클린 페이지와 INSERT/UPDATE/DELETE 으로 변경된 데이터를 가진 더티 페이지를 가지고 있으며, 바로 이 허용 가능한 더티 페이지의 크기가 쓰기 버퍼링의 성능과 관계가 있다.

리두 로그 파일의 공간은 계속 순환하면서 재사용 되기 때문에, 기록될 때마다 고유 번호인 LSN:Log Sequence Number 가 증가한다.

하지만 더티 페이지는 디스크와 데이터 상태가 다르기 때문에 언젠가 동기화가 필요하다. 버퍼 풀의 더티 페이지는 특정 리두 로그 공간과 1:1 로 관계를 가지면서 체크포인트 이벤트가 발생하면 체크포인트의 LSN 보다 작은 리두 로그 엔트리와 관련된 더티 페이지(변경 페이지)는 모두 디스크로 동기화 시킨다.

이처럼 메모리에서만 변경 작업이 일어나다가, 체크 포인트 이벤트 발생 시점에 한번에 디스크로 반영되므로 쓰기 버퍼링이라고 한다.

그렇다면 리두 로그 공간이 무조건 클수록 좋을까?

리두 로그 공간이 너무 크다면, 그만큼 한꺼번에 매우 많은 더티 페이지를 기록해야 하므로 MySQL 서버의 사용자 쿼리 처리 성능까지 영향을 준다. 따라서 오라클에서는 리두 로그 공간 기본 값을 1-200 GB 까지만 설정하고 있다.

당연하지만 버퍼 풀 크기 > 리두 로그 크기가 일반적이다. 버퍼 풀은 데이터 페이지를 통째로 가지지만, 리두 로그는 변경분만 가지고 있기 때문에 훨씬 작은 공간만 있어도 된다.

☁️ 버퍼 풀 플러시

하지만 MySQl 5.7 버전 이후부터는 위에서의 디스크 쓰기 폭증 현상을 걱정하지 않아도 되는데, 2개의 플러시 기능을 백그라운드로 실행하고 있기 때문이다.

1. 플러시 리스트 플러시

리두 로그 공간을 재활용을 위해서는 오래된 리두 로그 공간을 비워야 하는데, 그 이전에 버퍼 풀의 더티 페이지가 먼저 디스크로 동기화되어야 한다.

그리고 아래 여러 시스템 변수들을 제공함으로써 얼마나 많은 더티 페이지를 한번에 디스크로 기록해 동기화할지를 유연하게 조정할 수 있다.

innodb_page_cleaners
더티 페이지를 디스크로 동기화 하는 클리너 스레드 개수 조정 가능
innodb_max_diry_pages_pct
버퍼 풀의 더티 페이지 비율 조정 가능(높을 수록 디스크 쓰기 작업 버퍼링 효과가 커짐)
innodb_max_dirty_pages_pct_lwm
특정 개수 이상의 더티 페이지가 존재하면 디스크로 기록하도록 조정 가능
innodb_io_capcity / innodb_io_capcity_max
각 데이터베이스 서버에서 어느 정도 디스크 읽고 쓰기가 가능한지 설정 가능(더티 페이지 쓰기)
innodb_flush_neighbors
더티 페이지가 서로 인접해 있는 경우 함께 묶어서 디스크로 기록하는 기능의 활성화 여부 설정 가능
innodb_adaptive_flushing / innodb_adaptive_flushing_lwm
InnoDB 스토리지 엔진이 설정값에 의존하지 않고, 리두 로그 증가 속도 분석을 통해 적절한 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행(어댑티브 플러시 알고리즘)

2. LRU 리스트 플러시

사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지가 들어올 공간을 만드는데 사용된다. LRU 리스트를 innodb_lru_scan_depth 크기 만큼 스캔하면서, 더티 페이지는 디스크에 동기화 시키고 클린 페이지는 즉시 프리 리스트로 옮긴다.

☁️ 언두 로그(Undo Log)

언두 로그는 DML 이 실행되었을 때, 변경되기 이전의 데이터를 저장하는 곳이다.

주의할 점은 커밋하지 않아도 실제 데이터 파일 내용의 변경 내용은 기록이 된다는 것인데, 변경 이전 데이터는 언두 로그에 백업이 된다. 이후 커밋하면 현재 상태를 그대로 유지하고 롤백 시 언두 로그 정보를 사용해 이전 데이터 파일로 복구한다.

update member set name='홍길동' where member_id=1;

언두 로그는 다음 두 가지 역할을 한다.

트랜잭션 보장
트랜잭션이 롤백되면 변경되기 이전의 데이터로 복구해야 하는데, 언두 로그 데이터를 사용해 데이터를 복구한다.
격리 수준 보장
특정 커넥션에서 데이터를 변경하는 도중에, 다른 커넥션에서 데이터를 조회하려 하면 언두 로그에 백업해둔 이전 데이터를 반환한다. 트랜잭션을 격리 수준을 유지하면서, 락이 없으므로 높은 동시성을 제공한다.

언두 로그의 문제점

MySQL 5.5 버전 이전

이전 버전에서는 트랜잭션이 완료, 즉 커밋이 되었다고 해서 언두 로그를 즉시 삭제할 수 없었다. 따라서 언두 로그의 데이터 양이 급격히 증가될 수 있다는 문제가 발생했다.

왜냐면 언두 영역은 필요로 하는 트랜잭션이 없을 때 삭제할 수 있으므로, A/B/C 트랜잭션이 있을 때 B와 C 트랜잭션 종료 여부와 상관 없이 A 트랜잭션이 끝나지 않았다면 다른 트랜잭션에서 생성된 언두 로그도 삭제되지 못하고 계속 쌓이게 되기 때문이다.

진짜 문제는 언두 로그 디스크 용량의 증가가 아니라, 그 동안 빈번하게 변경된 레코드를 조회하는 쿼리가 실행되었을 때 언두 로그 이력 스캔에 시간이 많이 걸리므로 쿼리 성능 자체가 떨어지게 된다.

또한 한번 생성된 언두 로그 공간은 로그 데이터 삭제 여부와 관계 없이 공간 자체는 MySQL 서버를 새로 구축하지 않는 이상 줄이거나 변경할 수 없었다.

MySQL 5.7 / 8.0

언두 로그 공간을 동적으로 디스크 공간을 늘이고 줄이는 것도 가능해졌고, MySQL 서버가 필요한 시점에 자동으로 줄여 주기도 한다. 참고로 기존에는 시스템 페이블 스페이스에 저장되었었는데, 확장의 한계 때문에 이후 테이블 스페이스 외부 별도 로그 파일에 기록되도록 개선되었다.

CREATE UNDO TABLESPACE [테이블 스페이스 이름] ADD DATAFILE [새로운 데이터 파일 경로];
ALTER UNDO TABLESPACE [테이블 스페이스 이름] SET INACTIVE;
DROP UNDO TABLESPACE [테이블 스페이스 이름];

언두 로그 모니터링

SHOW ENGINE INNODB STATUS \G

현재 MySQl 서버의 언두 로그 건수를 History list length 필드에서 확인할 수 있다.

-------
TRANSACTIONS
-------
History list length 31

🔖 주의사항
INSERT 와 UPDATE/DELETE 문장의 언두 로그는 별도로 관리된다. 후자는 MVCC와 데이터 복구에 사용이 되지만, INSERT는 데이터 복구에만 사용이 되기 때문이다.

☁️ 체인지 버퍼

RDBMS에서 새로운 레코드가 삽입되거나 업데이트 되면 데이터 파일 변경 뿐만 아니라 인덱스도 업데이트해주어야 한다. 하지만 인덱스 업데이트는 Random access 로 인한 성능 저하를 일으킨다.

따라서 InnoDB는 변경해야할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 하지만, 디스크에 접근해서 업데이트해야 한다면 체인지 버퍼라는 임시 공간에 저장해두는 형태로 성능을 향상시킨다.

중복 체크를 반드시 해야 하는 유니크 인덱스는, 체인지 버퍼를 사용할 수 없다. 무결성 검증을 위해 디스크에 접근해야하기 때문이다.

innodb_change_buffering : 작업 종류별(inserts/deletes/changes/purges)로 체인지 버퍼 활성화
innodb_change_buffer_max_size : 체인지 버퍼 공간 설정

☁️ 리두 로그 및 로그 버퍼

리두 로그(Redo Log)는, 언두 로그와 다르게 바로 데이터의 변경 내용이 기록되는 디스크 공간이다. 이를 이용하면, MySQL 서버가 비정상적으로 종료되는 경우 서버가 종료되기 바로 직전의 상태로 복구할 수 있다.

그리고 이는 트랜잭션의 ACID 속성 중 Durable 속성에 해당한다.

🔖 트랜잭션 ACID
Atomic : 한 트랜잭션의 연산들이 모두 성공하거나, 반대로 전부 실패되어야 한다.(롤백 관련)
Consistent : 트랜잭션이 일어난 이후의 데이터베이스는 데이터베이스의 제약이나 규칙을 만족해야 한다.
Isolated : 여러 사용자가 같은 테이블에서 모두 동시에 읽고 쓰기 작업을 할 때, 각 트랜젝션은 고립(격리)되어 있어 연속으로 실행된 것과 동일한 결과를 내야 한다.
Durable : 하나의 트랜잭션이 성공적으로 수행되었다면, 해당 트랜잭션에 대한 로그가 남아야한다.
https://hanamon.kr/데이터베이스-트랜잭션의-acid-성질/

리두 로그는 아래와 같이 서버가 비정상적으로 종료되었을 때 발생하는 잘못된 데이터들을 해결해준다.

커밋되었지만 데이터 파일에 기록되지 않은 데이터
리두 로그에 저장된 데이터를 데이터 파일에 복사할 수 있다.
롤백됐지만 데이터 파일에 이미 기록된 데이터
변경되기 전 데이터를 가진 언두 로그의 내용을 가져와 데이터 파일에 복사할 수 있다.

리두 로그 기록 주기 설정

innodb_flush_log_at_trx_commit 옵션을 통해 리두 로그를 어느 주기로 기록할지 정할 수 있다. 하지만, 트랜잭션이 커밋되면 즉시 디스크로 기록하고 데이터 파일과 동기화 되도록 하는 것이 좋은데 장애 직전 시점까지의 복구가 완전히 가능해지기 떄문이다.

https://jione-e.tistory.com/128

옵션 0: 0.1초에 한번씩 리두 로그 디스크로 기록 + 동기화
옵션 1 : 트랜잭션 커밋마다 디스크 기록 + 동기화
옵션 2 :트랜잭션 커밋마다 디스크 기록 + 동기화는 1초에 한번씩 실행

참고로 리두 로그를 기록하는 작업도 DB I/O 이기 때문에 로그 버퍼라는 메모리 공간에 먼저 저장을 해두고, 한번에 기록을 한다.

☁️ 어댑티브 해시 인덱스

사용자가 수동으로 테이블에 생성해둔 B-Tree 인덱스가 아니라, InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다.

set innodb_adaptive_hash_index = 1/0;

기존 B-Tree의 문제점

B-Tree 인덱스는 항상 검색 시간이 매우 빠름이 보장되지 않는다.

결국 루프 노드를 거쳐 리프 노드까지 트리 탐색을 해야 원하는 레코드를 얻을 수 있으므로, 당연히 해당 작업 몇천개가 동시에 수행된다면 CPU는 엄청난 프로세스 스케줄링을 하게 되고 쿼리 성능이 떨어지게 된다.

어댑티브 해시 인덱스 장점

어댑티브 해시 인덱스는 자주 사용되는 칼럼을 해시로 정의하여, B-Tree 를 타지 않고 바로 데이터에 접근할 수 있게 한다.

Key : B-Tree 인덱스 고유 번호 + B-Tree 인덱스 실제 키 값 조합으로 생성된 고유 인덱스 키
Data : InnoDB 버퍼 풀에 로딩된 데이터 페이지 메모리 주소

버퍼 풀에 올려진 데이터 페이지에 대해서만 관리되기 때문에 당연히 버퍼 풀에서 해당 데이터 페이지가 없어지면 어댑티브 해시 인덱스에서도 해당 정보가 사라진다.

실제로 카카오 기술블로그에 따르면 Adaptive Hash Index를 사용하지 않는 경우 CPU가 100% 였으나, Adaptive Hash Index를 사용한 이후에는 60% 정도로 사용률이 내려가고 쿼리 처리량도 올라갔다고 한다.

어댑티브 해시 인덱스를 사용하면 좋은 경우

디스크 읽기가 많지 않은 경우(디스크 데이터가 InnoDB 버퍼 풀 크기와 2비슷한 경우)
동등 조건 검색(동등 비교 / IN 연산자)이 많은 경우
쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

어댑티브 해시 인덱스를 사용하면 나쁜 경우

디스크 읽기가 많은 경우
특정 패턴의 쿼리가 많은 경우(조인, LIKE)
매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

하지만 어댑티브 해시 인덱스를 더 많이 사용할수록, 테이블 삭제 또는 변경 작업에서 2배로 변경 작업이 일어난다. 또한 InnoDB 버퍼의 메모리를 잡아먹으로 적당한 트레이드 오프가 필요하다.

☁️ MySQL 로그 파일

로그 파일은 매우 중요한 정보이다. MySQL 서버의 상태나 부하를 일으키는 원인을 쉽게 찾아서 해결할 수 있기 때문이다.

1. 에러 로그 파일

MySQL 이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 파일이다.
위치는 MySQL 설정 파일인 my.cnf 에서 log_error 라는 이름의 파라미터로 정의된 경로에 생성된다.

mysql --verbose --help | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

[mysqld]
# 슬로우 쿼리를 TABLE로 출력한다. FILE로도 설정할 수 있다
# FILE로 설정했다면 slow_query_log_file로 출력할 파일 위치를 설정할 수 있다
log_output = TABLE

# 슬로우 쿼리 활성화
slow_query_log = 1

# 아래 변수에 지정된 초(seconds)이상 쿼리가 수행되면 슬로우 쿼리에 기록된다
# 여기서는 테스트를 위해 0.05초로 지정했다
long_query_time = 0.05

# log_ouput을 TABLE로 설정했다면 slow_query_log_file 변수는 주석 처리하자
# MySQL 버그인지 필자의 개발환경 문제인지 모르겠지만, log_output을 TABLE로 설정했을 때
# 해당 변수가 설정되어 있으면 슬로우 쿼리가 활성화되지 않는 문제가 발생했다
# slow_query_log_file=...
# ...

주로 주목해서 봐야 하는 메시지들은 다음과 같다.

MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
설정된 변수 이름이나 값이 명확히 설정되고 적용되었는지 확인이 필요하다.
종료할 때 비정상적으로 종료된 경우 나타난 InnoDB 트랜잭션 복구 메시지
다시 시작하면서 완료되지 못한 트랜잭션을 정리하고, 디스크에 변경 사항을 기록하는 재처리 작업이 수행된다.
쿼리 처리 도중에 발생하는 에러 메시지
비정상적으로 종료된 커넥션 메시지(Aborted Connection)
해당 로그가 많이 찍혀 있다면,
InnoDB 모니터링 또는 상태 조회 명령(SHOW ENINGE INNODB STATUS)
모니터링을 사용한 이후에는 다시 비활성화해서 에러 로그 파일이 커지지 않게 막아야 한다.
MySQL 종료 메시지

2. 제너럴 쿼리 로그 파일

제너럴 쿼리 로그 파일의 경로는 general_log_file 이라는 이름의 파라미터에 설정되어 있다. MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 확인할 수 있다.

단, 쿼리 실행 중에 오류가 발생해도 일단 파일에 기록된다.

SHOW GLOBAL VARIABLES LIKE 'gerenal_log_file';

3. 슬로우 쿼리 로그

MySQL 서버 쿼리 튜닝 작업에 이용되는 로그로, 이미 서비스가 운영되는 중에 서버의 전체적인 성능 저하를 검사해야 하는 경우 사용하면 많은 도움이 된다.

long_query_time 으로 설정된 시간 보다 수행 시간이 오래걸린 쿼리들이 슬로우 쿼리 로그 파일에 기록되며, 제너럴 쿼리 로그 파일과는 다르게 정상적으로 수행되었지만 느린 쿼리들만 기록이 된다.

슬로우 쿼리 내용을 분석해보자.

Time : 쿼리가 종료된 시점이므로, 시작 시간은 Time-Query_time 으로 구할 수 있다.
User@Host : 쿼리를 실행한 사용자 계정을 의미한다.
Query_time : 쿼리가 실행되는데 걸린 전체 시간을 의미한다.
Lock_time : MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간을 의미한다.(스토리지 엔진 잠금 X) 즉, 해당 쿼리를 실행하기 위해 기다린 시간이며 간혹 잠금 체크와 같은 코드 실행 부분까지 포함되기 때문에 아주 작은 값이라면 무시해도 좋다.
Rows_examined : 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지 의미한다.
Rows_sent : 실제 클라이언트로 몇 건의 처리 결과가 보내졌는지 의미한다.

만약 Rows_examined보다 Rows_sent 값이 매우 적다면, 조금 더 작은 레코드만 접근하도록 튜닝해볼 가치가 있다.(GROUP BY/MAX/ 와 같은 집합 함수 쿼리 제외)

MyISAM 이나 메모리 스토리지 엔진에서는 MVCC 개념이 없기 때문에, 읽기 연산이여도 Lock_time 이 1초 이상 소요될 수 있다.

InnoDB 테이블에 대한 읽기 연산인데 1초 이상 소요된 경우, InnoDB 가 아닌 MySQL 엔진 레벨에서 설정한 테이블 잠금 때문일 수 있어서 InnoDB 테이블에만 접근하는 쿼리 문장의 슬로우 쿼리 로그에서는 Lock_time 값을 무시해도 된다.

슬로우 로그 파일 분석하기

Percona Toolkit의 pt-query-digest 스크립트를 이용하면 빈도나 처리 성능별로 쿼리를 정렬해서 볼 수 있다.

linux> pt-query-digest --type='genlog' general.log > parsed_general.log   // 제너럴 쿼리 
linux> pt-query-digest --type='slowlog' mysql-slow.log > parsed_mysql-slow.log  // 슬로우 쿼리

슬로우 쿼리 통계
모든 쿼리를 대상으로 슬로우 쿼리 로그의 실행 시간과 잠금 대기 시간 등에 대한 평균 및 최소/최대 값을 표시한다.
실행 빈도 및 누적 실행 시간순 랭킹
응답 시간과 실행 횟수를 보여주며, --order-by 옵션을 통해 정렬 순서를 변경할 수 있다. QueryID는 쿼리 문장을 통해 만들어진 해시값이므로 같은 모양의 쿼리는 같은 ID를 가지게 된다.
쿼리별 실행 횟수 및 누적 실행 시간 상세 정보
Query ID 별 쿼리 랭킹에 표시된 순대로 자세하게 보여준다.

https://storyofgondae.tistory.com/12

Loopy

개인용으로 공부하는 공간입니다. 잘못된 부분은 피드백 부탁드립니다!

이전 포스트

[Effective Java] 아이템 89: 인스턴스 수를 통제해야 한다면 readResolve 보다는 열거 타입을 사용하라

다음 포스트