서버 설정 최적화

개요

 

본 장에서는 MySQL 서버에서 적합한 설정 파일을 만드는 방법을 다룬다. 가령, 혹자는 "MySQL 설정 파일"에 다음과 같은 질문을 할 수도 있다. 

 

"32GB의 RAM과 12개 CPU 코어가 있는 서버에 가장 적합한 설정 파일은 무엇인가요?"

 

이 질문에 대한 답은 그리 간단하지 않다. 단순히 하드웨어의 스펙에 의존하는 것이 아니라 워크로드, 데이터, 어플리케이션의 특성이나 요구 사항에 맞추어 서버를 설정해야 한다. 

 

MySQL은 기본 설정으로 대부분의 시스템에서 훌륭하게 동작한다. 그래서 대부분은 기본 설정으로 MySQL 서버를 구성하고 스키마 최적화나 인덱스 및 쿼리 디자인 등에 더 많은 시간을 할애하는 것이 좋다. 심지어 기본 옵션을 변경하였을 때, 얻을 수 있는 잠재적 이점은 그리 크지 않을 뿐더러 오히려 사이드 이펙트를 불러일으키기 쉽다. ( 특히나 MySQL  포럼이나 Stack Overflow 와 같은 사이트를 맹신해서는 안된다 )

 

우리가 할 수 있는 최선의 조치는 아마도 InnoDB 버퍼 풀이나 로그 파일 크기와 같은 기본 값이 적절한지 확인하고, 안전 옵션을 적절히 설정한 뒤 극히 일부의 설정만을 바꾸면서 충분히 테스트를 거치는 것이다.

 

 

MySQL설정이 동작하는 방법

 

본 섹션에서는 MySQL의 설정 메커니즘에 대해 다루고자 한다. 

 

1. MySQL이 설정 파일의 설정과 커맨드라인 인수에 대해 어디서 정보를 얻는가?

- Uinix 계열 시스템에서 구성 파일은 보통 "/etc/my.cnf" 또는 "etc/mysql/my.cnf" 에 존재한다. ( 아마 MySQL 에 문제가 생겨 Stack Overflow를 돌아다니다보면 이 my.cnf 파일을 어떻게 바꾸라는 글을 많이 볼 수 있다 ) 운영 체제의 시작 스크립트를 사용하는 경우에는 일반적으로 여기가 설정 값을 지정할 수 있는 유일한 곳이다. 

 

2. 서버가 어떤 설정 파일로 서버를 실행하고 있는지 알고 싶을 때는 아래 명령어를 사용하면 된다.

 

$ which mysqld
/user/sbin/mysqld

$ /user/sbin/mysqld --verbose --help | grp -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/mysql/my.cnf~/.my.cnf /usr/etc/my.cnf

 

구문, 범위 및 다이나믹 ( Dynamism ) 여부

 

설정은 밑줄 또는 대시로 구분된 단어와 함께 모두 소문자로 작성된다. "-" 와 "_"는 동일한 설정을 나타내지만, 당연하게도 한 가지 스타일을 일관되게 적용하여 사용하는 것이 좋다.  

 

설정에는 여러 범위가 있을 수 있다. 일부는 전역 범위로 사용되고, 또 다른 일부는 세션 범위에 대해서만 영향을 줄 수도 있다. 많은 사람들이 설정 파일 내 설정이 무조건적으로 전역으로 설정될 것이라고 생각하지만 세션 범위에만 영향을 미치는 변수의 경우, 세션이 닫혔을 때 변경 사항은 사라진다. 이에 대해 몇 가지 주의점은 아래와 같다.

 

- max_connections 변수는 전역 변수이다.
- sort_buffer_size 변수에는 전역으로 기본값이 존재하지만, 세션별로도 설정이 가능하다.
- join_buffer_size 변수도 전역으로 기본 값이 존재하며, 세션별로도 설정이 가능하다. 여러 테이블을 조인하는 경우, 조인당 하나의 버퍼를 할당 받기 때문에 쿼리당 여러 개의 조인 버퍼를 사용할 수 있다.

 

만약, 서버가 실행되는 동안 변수의 전역 값을 설정하면 현재 세션과 기존 다른 세션의 값은 영햐을 받지 않는다. MySQL이 종료되면 해당 설정이 사라진다는 점을 유의해야 한다.

 

지속 ( Persisted ) 시스템 변수

 

MySQL 8.0 부터는 지속 시스템 변수라는 새로운 기능이 도입되었다. SET PERSIST 와 같은 구문을 통해 런타임에 한 번 값을 설정할 수 있으며, MySQL은 이 설정을 디스크에 저장해서 다음에 다시 시작할 때 사용한다. 

 

변수 설정의 부작용

 

변수를 동적으로 설정하면, 버퍼에서 더티 블록을 플러시하는 것과 같은 예기치않은 부작용을 겪을 수 있다. 대부분의 설정 변수들은 이름을 통해 그 기능이 유추될 수 있게끔 되어있지만 명명 규칙이 완벽하지는 않으므로 MySQL 공식 문서를 참조해나가면서 변수가 무엇인지 확인하는 것이 중요하다.

 

다음은 일반적으로 사용되는 몇 가지 변수와 이것이 변경되었을 때의 효과이다. 

 

table_open_cache
해당 변수의 설정은 즉각적인 효과를 가져오지 못한다. 다음 번에 스레드가 테이블을 열 때 효과가 적용된다. MySQL은 변수의 값을 확인하고, 값이 캐시에 있는 테이블 수보다 크면 스레드는 새로 열린 테이블을 캐시에 삽입한다. 만약 값이 테이블 수보다 작으면 MySQL은 캐시에서 사용하지 않는 테이블을 삭제한다.

thread_cache_size
해당 변수의 설정 역시 즉각적인 효과를 가져오지 못한다. 다음 번 연결이 닫힐 때 효과가 적용된다. MySQL은 연결이 닫힐 때, 캐시에 스레드를 저장할 수 있는 공간이 남았는지 확인한다. 남아있을 경우, 스레드를 캐싱하지만 남아있지 않을 경우 스레드를 종료한다.즉, 스레드 캐시가 사용하는 메모리 양이 즉각적으로 감소하지는 않고, 새 연결이 이것을 사용하기 위해 캐시에서 스레드를 제거하는 경우만 감소하는 것이다.

read_buffer_size
MySQL은 쿼리가 필요할 때까지 이 버퍼에 메모리를 할당하지 않는다. 다만, 순차적 테이블 스캔과 같이 버퍼가 필요한 경우 지정한 전체 메모리 크기를 즉각 할당한다.

 

변수 변경 계획 

 

변수를 설정할 때는 "다다익선" 의 이치가 적용되지 않는다는 것을 명심하자. 값을 너무 높게 설정하면 메모리가 부족하거나 서버에서 스왑이 일어나고 문제가 쉽게 발생할 수 있다. 변경된 설정이 고객 경험에 악영향을 주어서는 안된다. 서버의 실제 성능을 지속적으로 모니터링하고, 자신도 모르는 사이에 성능이 저하되지는 않았는지 확인해야한다. 

 

변수의 변경의 Git 등의 버전 관리 시스템으로 추적 가능해야한다. 또한, 동작 중이 MySQL 서버는 단순히 설정 파일만 바꾸는 것이 아니라 런타임 설정도 함께 변경해야 한다는 것도 유의해야 한다.

 

사실 설정 변경을 시작하기 전에 최소한 인덱스 추가 같은 쿼리나 스키마의 최적화부터 진행을 해야한다. 설정과 쿼리 최적화를 함께 진행하면 어떤 것이 성능에 영향을 주었는지 확인하기 어렵다. 변경한 설정을 정확히 검증하려면, 하드웨어와 워크로드, 데이터가 완전히 고정되어 있어야 한다. 하지만 이것은 매우 어려운 일이고, 책의 필자는 "오전을 위한 완벽한 설정이 오후에는 적합하지 않다"라고 표현한다. 결국 서버에 마지막 온스까지 쥐어 짜내어 설정을 구성하려고 한다면, 시간 투자에 대한 수익이 매우 적을 수 밖에 없다. 그래서 "적당히 좋음"에서 멈추는 것도 괜찮다.

 

하지 말아야 할 것

 

'비율로 튜닝'하면 안된다. 전형적인 비율로 튜닝하는 것의 예는 InnoDB 버퍼 풀 적중률 ( hit ratio )이 일정 비율보다 높아야 하고, 적중률이 너무 낮으면 캐시 크기를 늘려야 한다는 것이다. 이것은 매우 잘못된 조언이다. 캐시 적중률은 캐시가 너무 크든 너무 작든 상관이 없다. 적중률은 워크로드에 의존하다. 일부 워크로드는 캐시가 얼마나 크든 작든 그냥 캐시되지 않는다. 더불어 캐시 적중은 의미가 없다. 

 

실제로 Oracle DBA 들은 몇 년 전에 비율 기반 튜닝을 포기했으며, 책의 필자는 이런 위험한 관행을 성문화하고 수천명의 사람들에게 가르치는 '튜닝 스크립트' 를 만들지 않기를 간절히 바란다고 말한다.

 

여담으로 Oracle DBA들은 튜닝이라는 단어보다 "설정" 또는 "최적화"라는 단어를 선호한다고 한다. 튜닝이라는 단어가 서버를 변경하고 무슨 일이 일어나는지 지켜보지 않는 초보자의 이미지를 연상 시킨다고 한다 ..

 

서론에서도 말했지만, 인터넷 자료를 맹신하는 것 역시 피해야 한다. 가장 믿을 수 있는 사람을 특정 지어서 말할 수는 없지만, 적어도 MySQL 서비스 제공자가 단순한 인터넷 검색보다 더 안전하고 신뢰성 있는 정보를 제공해줄 것이다.

 

끝으로, 인기 있는 메모리 소비 공식을 믿으면 안된다. 이 역시도 인터넷에서 떠돌아 다니는데, 진실은 MySQL의 메모리 소비에 상한선을 둘 수 없다는 것이다. MySQL 서버는 메모리 할당 제어를 엄격하게 규제하는 데이터베이스가 아니다.