my.cnf ( my.ini ) MySQL 최적화와 관련된 변수.

MySQL 서버의 가장 적합한 설정이란 쉽지 않다.

단순 하드웨어가 아닌 워크로드, 데이터, 애플리케이션 요구 사항에 맞게 서버를 설정해야 합니다. MySQL에는 변경할 수 있는 수많은 설정이 있지만, 변경해서는 안되는 설정이 많다. 일반적으로 기본 설정이 더 좋으며 스키마 최적화, 인덱스, 쿼리 디자인에 더 많은 시간을 투자하는 것이 좋다.

스택오버플로, MySQL 포럼 등 인기 있는 사이트에서 최적의 설정이라고 올라온 자료들이 많다. 하지만 변경으로 인한 영향을 이해하지 않고 설정을 변경하면 서버 장애, 지속적인 지연, 성능 저하로 이어질 수 있다. 

 

 

 

my.cnf 파일은 시스템 환경 변수를 담고 있는 파일입니다. 성능 최적화를 하려면 하드웨어, RAID, MySQL의 I/O 동작, 메모리 사용량, 동시성 설정 등 다양한 옵션을 고려해서 최적화를 진행해야 합니다.

Stack overflow, MySQL 포럼에 최적의 설정이라고 올라온 자료들이 많지만 변경으로 인한 영향을 이해하지 않고 설정을 변경하게 된다면 서버 상애, 성능 저하로 이어질 수 있습니다.

1.InnoDB Buffer Pool과 InnoDB Log Buffer

 MySQL 성능은 Buffer Pool의 크기에 많이 의존하고 innodb_buffer_pool_size를 통해 변경할 수 있습니다. 그리고 innodb_dedicated_serve를 통해 자동화된 설정이 가능하지만 128MB, 서버의 리소스 * 0.5, 서버의 리소스 * 0.75로 정해진 값으로 변경되기 때문에 세부 조정이 필요합니다.

 Buffer Pool에는 ROW 데이터, Adaptive hash index, change buffer, 기타 내부 구조를 캐싱하는 기능을 지원합니다. 또, 데이터 캐싱 기능 뿐 아니라, 쓰기 버퍼링 기능을 지원하는데, 여기서 innodb_log_file_size, innodb_log_files_in_group과 밀접한 연관이 있습니다.

 InnoDB Buffer Pool의 더티 페이지는 특정 Redo 로그와 관계를 가지고, 체크포인트 발생 시 체크포인트 LSN보다 작은 Redo 로그와 더티 페이지는 디스크로 동기화 되는 flush가 발생하게 됩니다. 만약 log_file이 100MB 이고 Redo 로그 하나에 4kb, 데이터 페이지 하나에 16kb 이면 버퍼링 가능한 더티 페이지가 겨우 400MB 수준밖에 되지 않아 쓰기 버퍼링 기능을 제대로 사용할 수 없습니다. 이렇기 때문에 여기서 innodb_log_file_size * innodb_log_files_in_group 의 크기를 적절히 설정하여 사용하는 것이 좋습니다.

2. 다양한 데이터 flush, 스레드, 디스크 io capacity 등 다양한 옵션이 존재합니다.

 innodb_flush_log_at_trx_commit 의 옵션에서 0,1,2에 따른 성능과 안전성을 교환하여 설정이 가능합니다. 일반적으로 가장 안전한 옵션인 1번을 사용합니다.

 innodb_read_io_threads 및 innodb_write_io_threads를 통해 데이터 페이지 Read/Write에 대한 백그라운드 쓰레드 수 설정이 가능합니다.

 innodb_io_capacity옵션으로 데이터베이스에서 디스크 접근에 대한 i/o 성능 설정이 가능합니다. HDD, SSD 등 다양한 하드웨어에 적합한 I/O를 설정하여 사용이 가능합니다.

 

 

Buffer Pool과 페이지 관리 알고리즘

 

일단 Buffer Pool의 구조에 대해 먼저 이해하고 있어야 합니다.

Buffer Pool에서 페이지 크기 조각을 관리하기 위해 LRU(Least Recently Used),MRU ( Most Recently Used ) 리스트, 플러시(Flush) 리스트, 프리(Free) 리스트 3가지 자료 구조를 사용하여 관리합니다.

- 프리 리스트는 Innodb Buffer Pool에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지 목록이며, 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용됩니다.

- LRU + MRU 리스트는 디스크로부터 한 번 읽어온 페이지를 최대한 오래 InnoDB Buffer Pool 메모리에 유지시켜 디스크의 읽기를 최소화하는 것입니다.

- 플러시 리스트는 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지의 변경 시점 기준의 페이지 목록을 관리합니다. 디스크에서 읽은 상태 그대로 전혀 변경이 없다면 플러시 리스트에서 관리되지 않지만, 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에서 관리되고 특정 시점이 되면 디스크로 기록됩니다.

1. 필요한 레코드가 저장된 데이터 페이지가 Buffer Pool에 있는지 검사합니다. 검사하는 과정에서는 InnoDB Adaptive hash index를 이용해 페이지를 검색합니다. 해당 테이블의 b tree 인덱스를 이용해 Buffer Pool에 페이지가 있는지 검색합니다. 만약 Buffer Pool에 데이터가 있다면 MRU로 페이지가 이동됩니다.

2. 디스크에서 필요한 데이터 페이지를 Buffer Pool에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더에 추가합니다.

3. Buffer Pool의 LRU 헤더 부분에 적재된 데이터가 실제로 읽히면 MRU 헤더 부분으로 이동됩니다.

4. Buffer Pool에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 age가 부여되고, Buffer Pool에 상주하는 동안 오랫동안 사용되지 않으면 데이터 페이지에 부여된 age가 오래되고 Buffer Pool에서 제거됩니다. Buffer Pool의 데이터가 사용되면 age가 초기화되어 MRU헤더 부분으로 옮겨집니다.

5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를  InnoDB Adaptive hash index에 추가되고, B-tree 구조를 타지 않고 바로 데이터에 접근할 수 있게 됩니다.

 

 

redo log, wal

 WAL은 트랜잭션 ACID 중 Durable이랑 가장 밀접하게 연관되어 있다. 하드웨어나 소프트웨어 등 여러 가지 장애로 인해 MySQL 서버가 비정상적으로 종료되었을 경우, 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 기능이다.

 MySQL 서버는 데이터 변경 내용을 로그로 먼저 기록한다. 그 이유는 MySQL은 쓰기보다 읽기 성능을 고려하여 만들어진 자료 구조인 B-tree 를 사용하기 때문에 데이터를 파일에 기록하려면 큰 비용이 필요하다. 이로 인한 성능 저하를 막기 위해 쓰기 비용이 낮은 WAL에 기록해서 디스크와 동기화 시킨다.

 하지만 로그 발생할 때마다 로그를 파일에 기록하게 되면 많은 부하가 발생하기 때문에 로그 버퍼에 기록하고 일정한 간격으로 디스크에 동기화시킨다. 동기화 시간, 로그 버퍼의 크기를 관리하는 설정은 my.cnf에서 설정이 가능하다. 로그 버퍼의 크기는 일반적으로 16MB 수준이 적합하며, BLOB, TEXT와 같이 큰 데이터를 자주 변경하는 경우 조정이 필요하다. innodb_flush_log_at_trx_commit 설정을 통하여 0,1,2에 따라 디스크 동기화 방식이 달라지게 되고, innodb_flush_log_at_timeout을 통하여 디스크 동기화 주기를 변경할 수 있다.

이런 이유 때문에 WAL은 중요하다. 이러한 WAL도 날라갈 수 있기 때문에 로그 아카이빙을 통하여 레코드 손실을 방지해야 한다.