내맘대로 정리하는 Real MySQL #3장) 아키텍처 - 스토리지 및 MySQL 로그 편

<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을 위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^

원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??

InnoDB

전체 구조

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중에서 거의 유일하게 레코드 기반의 잠금을 제공하고 있으며, 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

엔진의 특성

• PK에 의한 클러스터링 : PK 값의 순서대로 디스크에 저장된다는 뜻이며, 이로 인해 PK에 의한 range scan은 상당히 빨리 처리된다
• 잠금이 필요 없는 일관된 읽기 : MVCC(Multi Version Concurrency Control)이라는 기술을 이용해 락을 걸지 않고 읽기 작업을 수행한다.
• FK 지원 : 엔진 레벨에서 지원한다. InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많다.
• 자동 데드락 감지 : 발생함과 동시에 바로 감지되고, 감지된 데드락은 관련 트랜잭션 중 rollback이 가장 용이한 트랜잭션을 자동적으로 강제 종료한다.
• 자동화된 장애 복구 : MySQL 서버가 시작할때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다.

InnoDB 버퍼 풀

스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이한다.

버퍼 풀이 INSERT, UPDATE, DELETE 등의 변경된 데이터를 모아서 처리하게 되면 랜덤한 디스크 작업의 횟수를 줄일 수 있다. InnoDB의 버퍼풀은 인덱스와 데이터 모두 캐시하고 쓰기 버퍼링의 역할까지 처리한다. 많은 백그라운드 작업의 기반이 되기도 한다.

InnoDB 버퍼 풀은 아직 디스크에 기록되지 않은 변경된 데이터를 가지고 있다. 주기적으로 또는 어떤 조건이 되면 체크 포인트 이벤트가 발생하는데, 이때 write 스레드가 필요한 만큼의 더티 페이지만 디스크로 기록한다.

Undo 로그

UPDATE 문장이나 DELETE와 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터를 보관하는 곳이다. 트랜잭션의 롤백 대비용이자 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는데 사용된다.

인서트 버퍼

RDBMS에서 레코드가 INSERT 되거나 UPDATE 될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트 하는 작업도 필요하다. 이때 랜덤하게 디스크를 읽는 작업이 필요하므로 인덱스가 많다면 상당히 많은 자원을 소모하게 된다.

그래서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키게 된다. 이 임시 메모리 공간을 인서트 버퍼라 한다.

사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 Unique Index는 인서트 버퍼를 사용할 수 없다. 인서트 버퍼에 임시로 저장돼 있는 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 인서트 버퍼 머지 스레드라고 한다. innodb_change_buffering이라는 설정을 이용해 작업의 종류별로 인서트 버퍼를 활성화할 수 있으며, 인서트 버퍼가 비효율적일 때는 인스터 버퍼를 사용하지 않게 설정할 수 있도록 개선됐다.

• 정리

리두 로그 및 로그 버퍼

리두 로그(변경된 내용을 순차적으로 디스크에 기록하는 로그 파일) 버퍼링에 사용되는 공간

MVCC

잠금을 사용하지 않는 일관된 읽기를 제공하는 목적이다. 언두 로그를 이용해 이 기능을 구현할 수 있다.

UPDATE 실행 이후 Trx가 commit 되기 전 조회 쿼리가 들어왔을때 READ_COMMITED나 그 이상의 격리 수준인 경우는 아직 커밋되지 않았으므로 언두 영역의 데이터를 반환하는 것이다.

즉, 하나의 레코드에 대해 2개의 버전이 유지되고 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지게 된다. 이 언두 영역을 필요로 하는 트랜잭션이 더는 없을 때 비로소 삭제된다.

잠금 없는 일관된 읽기

격리 수준이 SERIALIZABLE이 아닌 READ-UNCOMMITED나 READ-COMMITED 그리고 REPEATABLE-READ 수준인 경우 INSERT와 연결되지 않은 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다.

오랜 시간 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 때가 가끔 있는데, 바로 이러한 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 발생하는 문제이다. 따라서 트랜잭션이 시작됐다면 가능한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.

MyISAM vs InnoDB

InnoDB가 엄청나게 좋다. (단점이라면 서버 설정 튜닝이 조금 까다롭다 → 책에서 설명한 것만 잘 하면 해결)

MyISAM

MyISAM 키 캐시는 인덱스만 대상으로 작동하며 또한 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 수행한다.

(사진 추가 예정)

MyISAM 테이블의 인덱스는 키 캐시를 이용해 디스크를 검색하지 않고도 충분히 빠르게 검색할 수 있다. 하지만 MyISAM 엔진에는 디스크로부터 I/O를 해결해 줄 만한 어떠한 캐시나 버퍼링 기능이 존재하지 않는다. 때문에 항상 운영체제의 디스크 읽기 또는 쓰기 작업을 요청할 수밖에 없다. OS의 캐시 공간은 남는 메모리를 사용하는 것이 기본 원칙이다.

Memory

HEAP 스토리지 엔진이라고도 하며, 데이터의 크기가 작고 아주 빠른 처리가 필요한 경우에 적합한 엔진이다. 주의사항이 몇 가지 있다.

• 테이블의 최대 크기 : max_heap_table_size 변수를 이용해 설정한다.
• 고정 길이 칼럼만 지원 : 칼럼의 크기는 최소한으로 결정하는 것이 메모리 공간을 절약하는데 많은 도움이 된다.
• LOB 타입 지원 X
• 해시 인덱스 사용

MEMORTY 테이블은 MySQL 엔진이 쿼리를 처리하는 과정에서 임시로 생성되는 임시 테이블 용도로 더 자주 사용된다.

NDB 클러스터

Network Database : 네트워크를 통해 데이터를 분산하는 스토리지 엔진

성능보다는 가용성에 집중된 구조 (MySQL 노드 → 관리노드 → 데이터 노드 / MySQL 노드 → 데이터 노드)

MySQL 로그 파일

로그 일을 이용하면 MySQL의 상태나 부하를 일으키는 원인을 찾아서 해결할 수 있다. 많은 사용자가 로그 파일의 내용을 무시하고 다른 방법으로 해결책을 찾으려고 노력하곤 하는데, 무엇보다 MySQL 서버에 문제가 생겼을 때는 로그 파일을 자세히 봐야할 필요가 있다.

에러 로그 파일

log_error 옵션으로 설정. 에러나 경고 메시지가 출력된다. 별다른 설정이 없다면 datadir에 *.err를 확인해보자.

• 특정 변수가 무시된 경우 MySQL 서버는 정상적으로 기동하지만 해당 파라미터는 MySQL에 적용되지 못하고 로그에 남는다
• 비정상적으로 종료된 경우 나타나는 InnoDB 트랜잭션 복구 메시지
• 쿼리 도중 발생하는 에러 메시지
• 클라이언트 애플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우, MySQL 서버의 에러 로그에 관련 내용이 쌓이게 된다.
• InnoDB 테이블의 모니터링, 락 모니터링, 엔진 상태 조회 명령에 대한 결과가 쌓이게 된다. InnoDB 모니터링을 활성화 해두면 로그가 무척 커질 것이다.
• MySQL의 종료메시지

General Log

MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아 검토해볼 때, 쿼리 로그를 활성화해서 쿼리를 쿼리 로그 파일로 기록하게 한 다음, 그 파일을 검토하면 된다.

Slow log query

설정 옵션들

• log-output = FILE 또는 TABLE
• slow-query-log = 1
• long_query_time = 1
• slow_query_log_file=/var/log/log/mysql-slow.log

슬로우 로그 해석하기

• Time : 쿼리가 종료된 시점
• Query_time : 쿼리가 실행되는데 걸린 전체시간
• Lock_time : MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간
• Rows_examined : 쿼리가 처리되기 위해 접근한 record 수
• Rows_sent : 클라이언트에 보낸 record 수

바이너리 로그, 릴레이 로그

포맷은 동일하고, 이진 형태의 바이너리 로그 파일을 텍스트 형태로 바꾸기 위해서는 mysqlbinlog를 사용해야 한다. 기본적으로 특정 시간 ~ 특정 위치의 바이너리 로그를 SQL 형태로 읽어 출력할 수 있다.