Skip to content
컴퓨터잡담
2010.04.14 14:31

MySQL 조율(튜닝)에 대해

조회 수 15539 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄


MySQL 조율에 대해

출처 : http://blog.koolkuri.com/DK/121


http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01_1&m_no=22554&cat1=827&cat2=966&cat3=1010&lang=k


• 동시접속률을 증가시키기 위해 여러 개의 쓰레드가 하나의 캐시를 동시에 사용할 수 있다 : 물론 하나의 쓰레드가 캐시를 수정하는 경우 다른 쓰레드들은 수정이 완료될 때까지 잠시 기다린다.

• 키 캐시 블럭의 교체를 위해 기본적인 큐 방식 이외에도 추가적으로 매우 복잡한 알고리즘을 제공한다 : 이른바 ‘중간 삽입 전략(Midpoint Insertion Strategy)’이라고 하는 방식을 제공하는데 이는 사용률에 따라 구분하는 방식을 말한다. 이 방식이 적용될 경우 키 캐시 블럭의 교체를 위한 후보를 구분할 때 사용 빈도에 따라 핫리스트(Hot List)와 웜리스트(Warm List)로 구분한다. 

MyISAM 키 캐시의 설정


키 캐시가 어떻게 동작하는지 알아봤다. 이제는 키 캐시를 어떻게 설정하고 동작방식을 제어하는지 알아보자. 다중 키 캐시를 구성할 것인지와 만약 구성한다면 몇 개 정도를 구성할지 결정한다. 몇 개를 설정할지가 중요한 것이 아니라 각 키 캐시가 성능에 어떻게 영향을 미칠지 이해하는 것이 각 키 캐시를 설정하고 모니터링하며 튜닝하는 것만큼 중요하다. 각 키 캐시를 위해 적절한 값을 설정한다. 키 캐시를 만들 때마다 다음과 같은 몇 가지 항목에 대해 결정해야 한다.


• 키 캐시 버퍼의 메모리 크기를 정한다 : key_cache_block_size 파라미터를 통해 각 키 캐시 버퍼가 얼마나 많은 메모리를 사용할지 설정한다. 1024바이트가 기본 값이며 이 값은 대부분의 애플리케이션에 적합하다. 현재 MySQL에서는 이 값의 변화가 큰 의미를 가지지 않으나 앞으로 이 파라미터가 좀 더 중요한 역할을 하도록 변경될 예정이다.

• 키 캐시에 메모리를 할당한다 : 가장 중요한 항목이다. key_buffer_size 변수를 너무 작게 설정하는 것은 키 캐시가 주는 이득을 제대로 활용하지 못하는 것이며 너무 크게 주는 것은 소중한 메모리 공간을 낭비하는 것이 된다. 각자의 환경이 모두 다르기 때문에 이것이 가장 좋다고 추천할 수 있는 값은 존재하지 않지만 일단 모든 키 캐시의 메모리 사용량이 전체 메모리의 5~10%가 되도록 설정하는 것이 바람직한 튜닝의 시작이다. 먼저 이 정도의 값을 설정한 후 모니터링 결과에 따라 값을 올리도록 하자.

• 중간 삽입 전략을 사용할지 결정하자 : 이 방식을 사용하지 않으려면 key_cache_ division_limit를 100으로 설정하면 된다. 이 파라미터를 30으로 설정하면 웜리스트를 위해 30% 이상의 공간을 배정하지 않게 된다.


이제 인덱스와 원하는 캐시를 연결시켜 준다. CACHE INDEX문을 통해 키 캐시와 그 키 캐시를 사용할 인덱스를 지정해 줄 수 있다. 그 다음 키 캐시를 미리 로딩할지를 결정한다. MySQL은 키 캐시가 해당 레코드를 요청할 때 바로 로딩되거나 미리 로딩할지를 선택할 수 있다. 만일 미리 로딩하기를 원한다면 LOAD INDEX문을 통해 로딩 작업을 수행할 수 있다. LOAD INDEX문을 통해 로딩할 메모리의 양은 preload_buffer_size 파라미터를 통해 설정할 수 있다.


이렇게 설정한 후 키 캐시를 모니터링한다. 키 캐시의 성능을 모니터링하는 방법은 여러 가지가 있지만 MySQL Administrator를 사용하는 것이 가장 효율적이다. 키 캐시의 정보를 삭제하는 방법은 MySQL 서버를 재시작하거나 key_buffer_size를 변경하는 것이다.


MyISAM 키 캐시의 모니터링과 튜닝


키 캐시의 설정이 끝났다면 이제는 키 캐시의 모니터링을 위해 MySQL Administrator에 몇 가지 그래프를 추가하자. <화면 1>은 키 캐시 모니터링을 위해 MyISAM Activity라는 페이지를 만들어 필요한 그래프들을 추가해 놓은 상태이다.


키 캐시 사용량 
현재 얼마나 많은 양의 키 캐시가 사용되고 있는지 그래프로서 표현하려면 계속적으로 변하는 값인 key_block_used와 각 block의 크기인 key_block_size를 곱한 값을 최대 키 캐시 사이즈인 key_buffer_size 와 비교해 알 수 있다. 그래프를 추가한 후 value fomula에 ‘[Key_ blocks_used]×[key_cache_block_size]’를 사용하고 Max fomular에 key_buffer_size를 입력한 후 바 그래프(bar graph)로 설정하면 사용량을 쉽게 알 수 있다. 이 그래프를 모니터링함으로써 기본적으로 키 캐시 사이즈가 적절히 설정되었는지를 확인할 수 있다. 만약 순식간에 그래프가 최고치에 도달한다면 키 캐시에 좀 더 많은 메모리를 할당할 필요가 있으며 반대로 지속적으로 저조한 사용량을 보인다면 값을 줄일 필요가 있다. 10분 정도의 모니터링 결과로 값을 결정하는 것은 바람직하지 않으며 최소한 피크 타임과 일반적인 사용 시에 각각 1시간 정도씩 모니터링한 후 적절한 값을 설정하는 것이 좋다.


키 캐시 읽기 적중률
MySQL이 디스크가 아닌 키 캐시에서 얼마나 많이 읽어오는지를 파악하려면 다음의 공식을 통해 그래프를 만들 수 있다.


계산식 = 100-(^[key_read]/^[key_read_request])×100


그렇다면 이 그래프를 어떻게 해석할 수 있을까? 만약 지속적으로 90% 이상의 적중률을 보인다면 키 캐시가 효율적으로 설정된 것이며 적중률이 지나치게 낮다면 키 캐시 메모리를 증가시킬 필요가 있다. 하지만 데이터베이스가 엄청나게 크거나 여러 데이터를 골고루 많이 읽는 데이터베이스라면 아무리 많은 양의 키 캐시를 설정해도 이와 같은 결과를 얻을 수밖에 없다. 그리고 적중률이 99~100%를 기록한다면 그 역시 너무 많은 메모리가 키 캐시에 할당된 것이다. 이런 경우에는 값이 떨어지기 시작하는 시점까지 지속적으로 키 캐시 메모리 할당량을 줄여야 한다.



<화면 1> MySAM 모니터링을 위해 구성해 놓은 MySQL Administrator


<표 1> MySAM의 각종 메모리 관련 파라미터
범위 파라미터 설명
서버 전체가 공유함 Key_buffer_size 인덱스를 메모리에 저장하는 버퍼의 크기
Table_cache 전체 쓰레드가 사용할 오픈 가능한 테이블의 수
Thread_cache_size 재사용을 위해 캐싱될 쓰레드의 수
각 쓰레드 별로 사용됨 myisam_sort_buffer_size 테이블 repair, Alter table, load data에 사용되는 버퍼 메모리 크기
join_buffer_size 조인을 위한 메모리 버퍼 크기
record_buffer 순차적인 검색을 위해 사용되는 메모리 버퍼 크기
record_rnd_buffer Order by 절을 사용하는 경우 디스크 사용을 피하기 위하여 사용하는 메모리 버퍼 크기
sort_buffer Order by와 group by에 사용되는 메모리 버퍼 크기
tmp_table_size Group by시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용되는 메모리 크기

키 캐시 쓰기 적중률
이 그래프는 키 캐시 쓰기 요청과 실제로 디스크에 쓰이는 키 블럭간의 상관관계를 볼 수 있다. 계산식은 다음과 같다.


계산식 = 100-(^[key_writes]/^[key_write_requests[]×100


일반적으로 키 캐시 쓰기 적중률은 읽기 적중률보다 상당히 낮은 값을 나타내는 것이 정상이다. 하지만 대용량 데이터 입력이나 큰 인덱스를 생성하는 경우 순간적으로 값이 높아질 수 있으니 주의해야 한다.


키 캐시 읽기 I/O
<화면 1>의 키 캐시 I/O 중 위에 있는 두 그래프는 key_read_request와 key_read의 횟수를 보여주는 그래프이다. 두 가지 그래프를 비교함으로써 MySQL이 얼마나 많은 정보를 디스크가 아닌 키 캐시에서 읽어 오는지를 알 수 있다.


키 캐시 쓰기 I/O
<화면 1>의 키 캐시 I/O 중 밑에 있는 두 그래프를 통해 키 캐시가 얼마나 많이 쓰이고 있는지 알 수 있다. 성공적인 키 캐시 쓰기 요청(key_write_request)과 디스크에 쓰여진 횟수(key_writes)를 비교하게 된다.


MyISAM 기타 메모리 관련 파라미터 정리


MyISAM에는 키 캐시 외에도 각각의 작업별로 영향을 미치는 여러 가지 메모리 관련 파라미터들이 있다. <표 1>은 각종 파라미터들을 전체 스토리지 엔진에 영향을 미치는 파라미터와 각 쓰레드에만 영향을 미치는 파라미터로 나눠 정리한 것이다.


InnoDB 스토리지 엔진 튜닝


MySQL 3.x 버전에서 트랜잭션을 지원하기 위해 도입된 InnoDB 스토리지 엔진은 처음에는 MyISAM에 비해 지나치게 느린 성능 등을 이유로 많이 사용되지 않았다. 하지만 성능이 지속적으로 향상되고 트랜잭션 지원에 대한 사용자들의 요구사항이 많아지면서 현재는 MyISAM과 거의 대등한 위치에 올라선 스토리지 엔진이다. InnoDB 스토리지 엔진의 특징이라면 무엇보다도 ACID를 완벽히 만족하는 트랜잭션의 지원과 로우 레벨 락킹이다. MySQL이 점차 다양한 기업 환경에 사용되고 주요 업무에도 도입되면서 InnoDB는 점차 MySQL의 주요 스토리지 엔진으로 자리 잡을 것으로 예상되며 MySQL AB에서도 InnoDB에 성능개선에 많은 노력을 기울여왔다. 그 결과가 이번에 발표되는 MySQL 5.0 버전이며, MySQL 5.0.7 베타 버전을 기준으로 행해진 내부 벤치마크 테스트에서는 Select에서도 MyISAM보다 30% 이상 뛰어난 성능을 보여 MySQL AB의 내부에서도 큰 반향을 일으켰다. 그럼 앞으로 MySQL을 대표하는 스토리지 엔진이 될 InnoDB의 튜닝에 대해 알아보자


InnoDB 버퍼 풀의 설정


MyISAM의 튜닝에서 가장 큰 부분을 차지하는 것이 키 캐시라면 InnoDB에서 가장 큰 부분을 차지하는 것은 바로 버퍼 풀이다. 버퍼 풀의 크기를 조절하는 파라미터는 innodb_buffer_pool_size로 일반적으로 전체 시스템 메모리의 50~80% 정도를 설정한다. 다음은 버퍼 풀의 크기를 결정하기 전에 고려할 사항이다.


• 서버의 용도 : 만일 서버를 MySQL 전용 서버로 사용한다면 마음 놓고 버퍼 풀 크기를 올릴 수 있다. 하지만 서버에서 웹 서버나 애플리케이션 서버를 함께 운영한다면 꼭 필요한 만큼만 설정하도록 주의를 기울여야 한다. 

• 사용 가능한 시스템 메모리 : MySQL 전용 서버라 할지라도 전체 물리적 메모리의 80%나 그 이상을 설정하는 것은 전반적인 시스템 성능에 큰 무리를 준다. 

• 데이터베이스의 사용 유형 : 쓰기 중심의 데이터베이스 서버와 읽기 중심의 데이터베이스 서버는 버퍼 풀에 대한 요구사항이 크게 달라진다. 이것이 바로 버퍼 풀의 사용현황을 꾸준히 모니터링해야 하는 이유이다. 

• 다중 스토리지 엔진의 사용 : MyISAM의 키 캐시와 InnoDB 버퍼 풀은 서로에게는 전혀 도움이 되지 않고 오히려 한정된 메모리를 가지고 경쟁하는 관계이다. 그러므로 각 스토리지 엔진 별로 사용 빈도나 용도를 잘 따져서 설정해야 한다.


이런 사항들을 고려해 버퍼 풀 크기를 결정했다면 이제는 버퍼 풀과 관련된 몇 가지 파라미터들을 조정해야 한다. 버퍼 풀은 InnoDB의 인덱스와 데이터 정보를 캐싱하는 메인 메모리 캐시이기 때문에 다음 두 가지 사항은 매우 중요한 문제이다


• 변화된 캐시 페이지는 정기적으로 디스크에 기록해야 한다. 그렇지 않으면 서버 다운이 예상치 못하게 발생했을 때 캐싱된 데이터는 유실된다.

• 버퍼 풀에는 새로운 데이터 또는 인덱스 그리고 사용자가 직접 데이터베이스를 조작할 경우를 위한 공간을 반드시 남겨 둬야 한다.


Innodb_max_dirty_page_pct는 이런 두 가지 요구사항을 충족시키는 것을 도와주는 파라미터이다. 0부터 100까지 설정할 수 있으며 설정한 수치에 따라 InnoDB 쓰레드가 디스크와 싱크하도록 지시한다. 예를 들어 이 파라미터를 80으로 설정하면 InnoDB는 전체 버퍼 풀의 80% 이상이 수정되었거나 필요 없는 캐시 페이지 즉 더티 페이지를 가질 수 없도록 한다.



<화면 2> InnoDB 모니터링을 위해 구성해 놓은 MySQL Administrator


윈도우에서의 버퍼 풀 크기 한계를 뛰어넘기 위한 파라미터 역시 제공하는데 이것이 MS에서 최근에 발표한 AWE(Address Windowing Extensions)라는 메모리 확장 기술을이다. 이는 기존 윈도우 시스템의 메모리 한계인 4GB 이상을 관리자가 사용할 수 있도록 해 주는 기술로써 innodb_buffer_pool_awe_mem_mb 라는 파라미터이다. 이 파라미터를 통해 64GB까지의 메모리를 버퍼 풀로 사용할 수 있다. 그리고 버퍼 풀 이외에 메모리가 필요한 작업들을 위해 설정하는 파라미터로서 innodb_additinoal_mem_pool_size라는 파라미터가 있는데 대부분의 경우는 기본 값으로 설정해도 무방하다.


InnoDB 버퍼 풀의 모니터링과 튜닝


앞에서 MyISAM 스토리지 엔진에서 한 것과 같이 InnoDB 버퍼 풀을 위해 MySQL Administrator에 커스텀 그래프들을 생성해 모니터링하고 이를 바탕으로 튜닝을 하자. <화면 2>는 버퍼 풀을 모니터링하기 위해 새로운 페이지를 구성한 것이다.


버퍼 풀 사용량
전체 버퍼 풀 크기인 innodb_buffer_pool_pages_total 중에서 현재 사용중인 버퍼 풀 크기인 innodb_buffer_pool_pages_data로 전체 버퍼 풀 사용량을 알 수 있다. 시작하자마자 차지하는 용량은 InnoDB가 내부적인 용도로 사용중인 것이다.


버퍼 풀 내의 더티 페이지 사용량
전체 버퍼 풀 크기인 innodb_buffer_pool_pages_total 중에서 현재 사용중인 더티 페이지 크기인 innodb_ _pool_pages_dirty로 전체 버퍼 풀 중 더티 페이지가 차지하는 용량을 알 수 있다.


버퍼 풀 적중률
버퍼 풀이 용도에 맞게 효율적으로 설정되었는지 판단하는 가장 중요한 그래프이다. 그래프의 계산 공식은 다음과 같다.


계산공식 = 100-(100×(^[innodb_pages_read]/^[innodb_buffer_pool_read_ requests]))


이 계산 공식은 전체 버퍼 풀 읽기 요청 중 실제로 디스크에서 읽지 않고 버퍼 풀에서 읽은 횟수를 계산하는 것이다. 이 수치가 높다는 것은 버퍼 풀이 적절히 구성되어 동작중이라는 것이고 수치가 낮은 것은 버퍼 풀에서 실제로 자주 필요로 하는 정보를 찾을 수 없다는 것이다.


버퍼 풀 읽기 요청
이 그래프는 시시각각 변하는 버퍼 풀 읽기 요청을 모니터링하기 위해 쓰인다. 언제 어떤 작업을 수행하기 위해 갑자기 버퍼 풀 읽기 요청이 증가하는지를 파악하는데 도움을 준다.


버퍼 풀의 연속적인 데이터 미리 읽기 활동 측정
InnoDB는 복잡한 알고리즘으로 구현되어 있으며 어떠한 프로그램이 많은 양의 연속적인 데이터 읽기 작업으로 보통 전체 테이블 스캔되는 경우 등이 발생할지를 미리 판단한다. Innodb_buffer_pool_ read_ahead_seq의 상태를 지속적으로 모니터링함으로써 파악할 수 있으며 계속해서 상승하는 것은 InnoDB가 더 많은 테이블 스캔을 하고 있다는 의미이다.


버퍼 풀의 랜덤한 미리 읽기 활동 측정
InnoDB의 미리 읽기 알고리즘(read-ahead algorithm)은 연속적인 읽기뿐만 아니라 비연속적인 읽기가 대량으로 발생하는 것도 미리 예측해 작업을 한다. 이는 innodb_buffer_pool_read_ahead_rnd의 상태를 모니터링함으로써 파악할 수 있다.


버퍼 풀에 대한 쓰기 요청
버퍼 풀이 얼마나 자주 변경되는지 파악하려면 계속적으로 변하는 값인 innodb_buffer_pool_write_request의 상태를 추적하면 된다.


플러시된 버퍼 풀 페이지
앞에서 언급했듯이 MySQL은 정기적으로 버퍼 풀에 있는 페이지를 디스크로 싱크하는 작업을 한다. 이는 데이터의 순간적인 손실을 막기 위한 작업이다. Innodb_buffer_pool_pages_flushed의 상태를 추적함으로서 모니터링할 수 있다.


버퍼 풀에 들어가기 위해 대기하고 있는 큐의 수
버퍼 풀의 용량이 충분하지 못하면 앞의 플러시 이벤트가 발생할 때까지 기다린다. 이런 상황이 얼마나 발생하는 지 카운트하는 것은 매우 중요하다. 이런 일이 자주 발생한다는 것은 현재 버퍼 풀의 크기가 요구량에 비해 작게 설정되어 있다는 뜻이다. 이는 innodb_ buffer_pool_wait_free를 추적함으로써 모니터링할 수 있다.


InnoDB 로그 파일의 설정


InnoDB는 바이너리 로그 파일을 반드시 생성해야 하며 로그 파일과 관련된 설정 역시 성능에 큰 영향을 미치게 된다. MySQL 데이터베이스 관리자로서 InnoDB를 관리하려면 로그 파일과 관련해 몇 가지 결정해야 할 문제가 있다. 먼저 몇 개의 로그 파일을 만들지 결정해야 한다. 기본으로 설정되어 있으며 최소 값은 2이다. 더 많은 로그 파일을 만들려면 innodb_log_files_in_group 설정을 수정하면 된다.


몇 개의 로그 파일을 생성할지 결정되었다면 로그 파일의 용량을 결정해야 한다. Innodb_log_file_size로 설정할 수 있고 기본 값은 5MB로 되어 있다. 이는 작은 데이터베이스를 위해서는 충분한 값이다. 하지만 기업에서 사용하기에는 훨씬 큰 용량이 필요하다. 로그 파일이 작게 설정되었을 경우에는 자주 메모리 기반의 버퍼 풀과 디스크 기반의 로그 파일간의 체크포인트 생성 작업이 이뤄진다. 이는 InnoDB 성능을 매우 떨어뜨리는 주범이 된다. 일반적으로 적당한 로그 파일의 크기는 전체 버퍼 풀 크기를 앞서 설정한 로그 파일의 개수로 나눈 값이다. 예를 들어 버퍼 풀 크기가 180MB이고 innodb_ log_files_in_group을 3으로 설정했다면 적당한 innodb_log_file_size는 60MB가 된다.


로그 파일의 수를 몇 개로 하고 각각의 용량을 얼마로 할지를 정했다면 이제 innodb_log_buffer_size를 설정해야 한다. 로그 버퍼는 디스크로 쓰기 전 메모리에 트랜잭션 정보를 담아두기 위한 버퍼 메모리의 크기를 말한다. 1MB부터 8MB까지 설정할 수 있으며 이 용량이 클수록 디스크의 사용이 줄어들어 성능이 향상되지만 그만큼 갑작스러운 시스템 다운이 발생했을 때 손실되는 트랜잭션의 양도 늘어난다. 메모리가 충분히 많고 특별한 사고 위험이 없다면 크게 잡으면 잡을수록 좋다. 일반적으로 8MB를 추천한다. 마지막으로 COMMIT가 수행되었을 때 로그 버퍼와 파일이 어떤 작업을 하게 될지를 결정해야 한다. Innodb_flush_log_at_trx_commit 설정은 다음과 같은 옵션을 제공한다.


• 디스크에 바로 쓰기 : 가장 안전한 방법이지만 가장 느린 방법이다. 1로 설정하면 디스크에 바로 쓰게 된다.

• 조금 기다린 후 디스크에 쓰기 : 0 또는 2를 설정할 수 있으며 0으로 설정할 경우 commit의 수행 여부와 상관없이 매초 로그 버퍼가 디스크에 기록하게 된다. 2로 설정하면 commit이 수행되면 강제로 로그 버퍼를 로그 파일에 쓰게 만들지만 1초가 지나기 전에는 디스크에 저장되지 않는다. 0으로 설정할 경우 성능은 많이 향상되지만 1초 간의 트랜잭션 정보는 잃을 위험을 동반한다.


InnoDB 로그 파일의 모니터링과 튜닝


MySQL은 5.0.2 버전 이후로 서버에서 볼 수 있는 상태 정보를 엄청나게 많이 늘렸다. 이를 통해 사용자는 데이터베이스 내부의 상황을 더욱 자세히 파악할 수 있게 되었다. MySQL Administrator와 함께 세밀한 튜닝이 가능해 졌다. 새로 추가된 상태 정보들을 통해 로그 파일을 모니터링하고 이를 통해 튜닝 포인트들을 점검해보자.


로그 파일 쓰기 요청
계속해서 변하는 값인 innodb_log_write_requests를 추적함으로써 파악할 수 있다. 이를 모니터링함으로써 애플리케이션이 얼마나 자주 로그 파일의 쓰기를 요청하는지 파악할 수 있다


로그 파일 쓰기 횟수
첫 번째 그래프는 로그 파일 쓰기 요청을 추적하고 이 그래프에서 innodb_log_writes를 추적함으로써 실제적으로 얼마나 많은 쓰기가 이뤄지는지 파악할 수 있다. 첫 번째와 두 번째 그래프 사이에 눈에 띄게 많은 랙이 존재한다면 이를 통해 로깅이 병목 현상을 일으키고 있음을 알 수 있다.


로그 버퍼 웨이팅InnoDB는 로그 버퍼에 쓰기위해 기다리는 경우가 발생할 때마다 계속해서 innodb_log_waits의 값을 올리게 된다. 이러한 증상이 자주 발생하는 주원인은 실제의 요청량에 비해 로그 버퍼가 지나치게 작게 설정되어 있기 때문이다. 이런 증상은 특히 대용량의 정보를 로딩하거나 갑자기 트랜잭션이 증가할 때 발생한다. 이런 증상이 자주 발생한다면 innodb_log_buffer 설정을 증가시켜주는 것이 좋다.



<화면 3> 로그 파일 모니터링을 위해 구성해 놓은 MySQL Ad,inistrator의 모습


로그 파일과 운영체제간의 상호작용
변경된 정보는 결국 로그 파일로 디스크에 저장되고 이는 결국 운영체제의 파일 시스템과 상호작용을 하게 된다. 그러므로 InnoDB와 파일 시스템 간에 벌어지는 일들을 모니터링하는 작업도 매우 중요하다. <화면 3>의 두 번째 그룹의 첫 번째 그래프는 파일 시스템의 병목으로 로그 파일에 쓰는 것이 늦춰지는 것을 파악한다. 이는 innodb_ os_log_pending_writes를 통해 추적해 볼 수 있다. 그리고 다음 두 가지의 그래프는 실제적인 디스크 쓰기를 담당하는 Fsync() 함수를 innodb_os_log_fsyncs를 통해 추적함으로써 실제적인 쓰기 요청이 얼마나 일어나는지와 fsync()의 실행이 늦춰지는 경우는 얼마나 있는지를 innodb_os_log_fsyncs_pending을 통해 파악함으로써 현재 innodb 시스템이 얼마나 I/O의 병목현상 없이 잘 실행되고 있는지를 파악할 수 있다.


낚시하는 방법을 배우자


공개된 여러 튜닝 정보들을 보면 각각의 튜닝이 어떤 내부 구동 원리를 통해 이뤄지는가를 설명하기보다는 각 항목에 대해 구체적인 추천 수치만을 제시함으로써 일회성의 수동적인 튜닝에 그치는 경우가 많다. 그래서 이번 2회에 걸친 연재에서는 구체적인 수치를 제시하기 보다는 각 항목들이 데이터베이스 내부에서 어떤 역할을 하며 수치를 변경하는 것이 어떤 결과를 낳게 되는지 그리고 모니터링을 통해 이를 실제로 확인할 수 있게 하는 것을 주로 살펴봤다. 이번 연재에서 설명이 미흡하거나 따라하기 힘든 부분도 많아 아쉬움이 남지만 MySQL이라는 데이터베이스의 활용에 작은 도움이라도 되었으면 한다.



MySQL 조율에 대해

MySQL 서버를 빠르게 하기 위한 방법은 세 가지가 있는데, 효율이 낮은 쪽에서 높아지는 쪽으로 나열하면 다음과 같다.

  1. 하드웨어로 문제를 푼다.
  2. MySQL 프로세스 설정을 조율한다.
  3. 질의를 최적화한다.

하드웨어로 문제를 푸는 방법이 가장 먼저 떠오른다. 특히 데이터베이스가 자원을 잡아먹는 괴물이라는 사실을 감안하면 말이다. 하지만 이 해법에는 한계가 있다. 현실을 고려할 때 CPU나 디스크 속력은 두 배로, 메모리 용량은 네 배에서 여덟 배 정도만 늘일 수 있다.

두 번째로 좋은 방법은 mysqld라는 MySQL 서버 조율이다. 이 프로세스 조율은 올바른 위치에 메모리를 할당하고 어떤 부하가 걸릴지 mysqld에 알려주는 조정 기법을 의미한다. 디스크 속력을 좀 더 빠르게 만드는 대신, 필요한 디스크 접근 횟수를 줄이는 편이 유리하다. 비슷하게, MySQL 프로세스가 올바르게 동작하도록 만드는 조율은 개발자가 임시 디스크 테이블과 파일 여닫기 같은 배경 작업에 신경을 쓰는 대신 질의에 대한 서비스에 좀 더 많은 시간을 보낼 수 있음을 의미한다. mysqld 조율은 이번 기사에서 주로 다룰 내용이다.

최고로 좋은 방법은 질의 최적화다. 이는 적절한 색인을 테이블에 만들어 놓고, MySQL의 장점을 최대로 활용하는 방향으로 질의를 작성하는 조율 기법을 의미한다. 이번 기사에서 질의 조율을 다루지는 않지만(이 주제로 책을 써도 되겠다), mysqld 환경 설정을 변경해 조율이 필요한 질의를 보고하도록 만든다.

중요한 조율 순서를 제시하긴 했지만, 그렇다고 해서 적절히 조율을 마친 질의를 위해 하드웨어나 mysqld 설정을 무시하라는 말은 아니다. 느린 기계는 느린 기계일 뿐이며, 제대로 작성한 질의를 돌리더라도 부하가 걸려 실패하는 경우를 목격했는데, mysqld가 질의를 서비스하는 대신 바쁘게 움직이느라 시간을 소모하고 있었기 때문이었다.


느린 질의 기록

SQL 서버에서 자료 테이블은 디스크에 위치한다. 색인은 전체 테이블을 찾지 않고서 서버가 테이블에서 자료 열을 찾아내도록 도와준다. 전체 테이블을 찾을 때 테이블 탐색을 수행한다고 부른다. 종종 테이블에서 일부만 원하는 경우가 있는데, 전체 테이블 탐색은 디스크 I/O와 시간을 상당히 많이 소비한다. 이런 문제는 테이블 조인 과정에서 복합적으로 나타나는데, 양쪽 테이블에 들어있는 열을 하나씩 비교해야 하기 때문이다.

물론 테이블 탐색이 항상 두통거리만은 아니다. 종종 전체 테이블을 읽는 경우가 일부만 읽는 경우보다 더 효과적인 경우도 있다(이런 결정을 내리려면 질의 계획이라는 작업을 거쳐야 한다). 색인을 비효율적으로 사용하거나 전혀 색인을 사용하지 않으면 질의가 느려지며, 테이블 크기가 증가하면서 서버에 부하가 걸리면 이런 문제점은 더욱 두드러진다. 실행을 위해 주어진 시간보다 더 오래 걸리는 질의는 느린 질의라고 부른다.

mysqld 환경 설정에서 느린 질의 로그라고 적절히 이름이 붙은 느린 질의 기록을 활성화할 수 있다. 관리자는 이 로그 파일을 살펴 응용 프로그램에서 어느 곳을 추가로 조사할지 결정한다. Listing 1은 느린 질의 로그를 활성화하기 위해 my.cnf에 필요한 환경 설정을 보여준다.


Listing 1. MySQL 느린 질의 로그 활성

                
[mysqld]
; 느린 질의 로그를 활성화한다. 기본은 10초다.
log-slow-queries
; 5초 이상 걸리는 질의를 기록한다.
long_query_time = 5
; long_query_time보다 적게 걸릴 경우 색인을 사용하지 않는 질의를 기록한다.
; MySQL 4.1 이상 버전에만 통한다
log-queries-not-using-indexes

이와 같은 세 가지 설정을 함께 사용하면, 5초 이상 지속되는 질의나 색인을 사용하지 않는 질의를 기록한다. log-queries-not-using-indexes에 대한 경고가 하나 있다. 반드시 MySQL 4.1 이상 버전을 사용해야만 한다. 느린 질의 로그는 MySQL 자료 디렉터리에 들어 있으며, 파일 형식은 hostname-slow.log이다. 다른 이름이나 경로를 사용한다면, my.cnf에서 log-slow-queries = /new/path/to/file을 지정하자.

느린 질의 로그를 읽으려면 mysqldumpslow 명령을 내린다. 로그 파일 경로를 지정하는 방법으로 느린 질의를 발견 순서에 따라 정렬한 목록을 얻는다. 도움을 주는 기능 한 가지는 mysqldumpslow가 결과를 비교하기 앞서 사용자 정의 자료를 제거하므로 동일한 질의로 여러 번 수행해도 하나로 센다. 이는 대다수 작업에 필요한 질의를 찾아내는 데 도움을 준다.


질의 캐시

대다수 LAMP 응용 프로그램은 데이터베이스에 상당히 의존하며 동일한 질의를 여러 번 반복한다. 질의를 만들 때마다 데이터베이스는 똑같은 작업을 해야만 한다. 즉 질의를 해석해, 실행 방법을 결정하고, 디스크에서 정보를 메모리에 올리고, 클라이언트에 이를 반환한다. MySQL은 질의 캐시라는 기능을 사용해서 메모리에 질의 결과를 저장하며 필요할 때 찾아쓴다. 여러 인스턴스에서 이런 캐시는 극적으로 성능을 높힌다. 하지만 질의 캐시는 기본적으로 비활성화되어 있다는 사실을 염두에 두자.

query_cache_size = 32M를 /etc/my.conf에 추가하면 질의 캐시로 32MB를 잡는다.

질의 캐시 감시

질의 캐시를 활성화한 다음에, 효율적으로 사용하고 있는지 이해하는 과정이 중요하다. MySQL은 여러 변수를 사용해서 캐시에서 어떤 일이 벌어지는지 감시하도록 만든다. Listing 2는 캐시 상태를 보여준다.


Listing 2. 질의 캐시 통계 출력

                
mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 5216       |
| Qcache_free_memory      | 14640664   |
| Qcache_hits             | 2581646882 |
| Qcache_inserts          | 360210964  |
| Qcache_lowmem_prunes    | 281680433  |
| Qcache_not_cached       | 79740667   |
| Qcache_queries_in_cache | 16927      |
| Qcache_total_blocks     | 47042      |
+-------------------------+------------+
8 rows in set (0.00 sec)

각 항목을 분리하면 표 1과 같다.


표 1. MySQL 질의 캐시 변수

변수 이름 설명
Qcache_free_blocks 캐시에 있는 연속적인 메모리 블록 숫자. 높은 숫자는 단편화가 일어난 징표다.FLUSH QUERY CACHE는 캐시 조각을 모아 자유 블록 하나로 만든다.
Qcache_free_memory 캐시에 있는 자유 메모리
Qcache_hits 캐시에서 질의를 가져올 때마다 값이 증가한다.
Qcache_inserts 질의가 들어올 때마다 증가한다. inserts를 hits로 나누면 비적중률을, 1에서 비적중률을 빼면 적중률을 구할 수 있다. 직전 에제에서 대략 질의 중 87%를 캐시에서 가져왔다.
Qcache_lowmem_prunes 캐시를 위한 메모리가 부족해져 더 많은 질의를 위한 공간을 확보하기 위해 정리되어야 하는 횟수. 이 숫자를 계속해서 살펴보는데, 증가 추세에 있다면 단편화가 심각하거나 메모리가 부족하다는 징표다(위에서 언급한 free_blocksfree_memory를 살펴본다).
Qcache_not_cached 일반적으로 SELECT 구문이 아니기 때문에 캐시 후보에서 제외된 질의 숫자
Qcache_queries_in_cache 현재 캐시되어 있는 질의 숫자(응답 숫자 포함)
Qcache_total_blocks 캐시에 있는 블록 숫자

종종 이런 값의 변화 추이를 살펴보면 캐시를 효율적으로 사용하는지 파악하는 데 도움을 준다. FLUSH STATUS는 몇몇 카운터를 초기화하므로 서버가 동작 중에 있을 경우 도움이 된다.

모든 내용을 캐시하도록 과도하게 큰 캐시를 잡고 싶은 유혹이 든다. mysqld는 메모리 부족으로 인한 정리 작업과 같은 캐시 관리 작업도 해야 하므로, 여기에만 신경을 쓸 경우 서버가 꼼짝달싹하지 못한다. 일반적인 규칙을 설명하자면 FLUSH QUERY CACHE가 오래 걸린다면 캐시가 너무 큰 상황이다.


제약 가하기

시스템 부하가 자원 부족으로 이어지지 않도록 mysqld에 몇 가지 제약을 가해야 한다. Listing 3은 my.cnf에서 몇 가지 중요한 자원 관련 설정을 보여준다.


Listing 3. MySQL 자원 설정

                
set-variable=max_connections=500
set-variable=wait_timeout=10
max_connect_errors = 100

최대 접속은 첫째 행에서 다룬다. 아파치가 사용하는 MaxClients와 같이, 서비스가 가능한 접속 수만 허용한다. 지금까지 서버가 처리한 최대 접속 수를 확인하려면 SHOW STATUS LIKE 'max_used_connections' 명령을 내린다.

둘째 행은 mysqld가 10초 이상 쉬고 있는 접속을 끊어버리도록 만든다. LAMP 응용 프로그램에서 데이터베이스 접속은 일반적으로 웹 서버가 요청을 처리하는 동안에만 이뤄진다. 종종 부하가 걸린 상태에서 연결이 일시 정지된 상황에서 접속 테이블 공간을 차지하는 경우가 있다. 활성 사용자가 많거나 데이터베이스에 영속적인 접속이 이뤄지고 있다면, 이 값을 낮춰잡는 정책은 바람직하지 않다.

마지막 행은 안전 벨트다. 호스트에 서버 접속 관련 문제가 생겨 너무 많이 요청을 취소한다면, FLUSH HOSTS를 수행할 때까지 호스트는 잠겨버린다. 기본적으로 열 번 정도 실패하면 잠겨버리도록 설명한다. 이 값을 100으로 바꾸면 문제가 무엇이든 복구할 시간을 서버에 충분히 준다. 더 높은 값으로 설정하더라도 그다지 도움을 주지 않는 이유는 서버가 한 번에 100번 연결해도 실패한다면, 이후 계속 시도하더라도 연결에 성공할 가능성이 희박하기 때문이다.



버퍼와 캐시

MySQL은 100개가 넘는 조율 설정값을 지원한다. 하지만 천만다행으로 이 중에서 몇 가지만 알면 충분하다. 설정을 올바르게 하려면, SHOW STATUS 명령을 통해 상태 변수를 살펴보고, 이를 통해 mysqld가 원하는 방식으로 움직이는지 파악한다. 시스템에 존재하는 메모리 자원을 넘어서 버퍼와 캐시를 할당할 수 없기에 종종 조율 과정에서 타협이 필요하다.

MySQL 조율값은 mysqld 프로세스 전체나 개별 클라이언트 세션에 대해 설정이 가능하다.

서버 단위 설정

각 테이블은 디스크에 파일 형태로 저장되며, 테이블을 읽기 위해서는 파일을 열어야 한다. 파일 읽기 과정을 빠르게 하기 위해,mysqld는 /etc/mysqld.conf에 지정된 숫자(table_cache)만큼 열린 파일을 캐시한다. Listing 4는 열린 테이블에 대한 활동 상황을 출력하는 방법을 보여준다.


Listing 4. 열린 테이블 활동 상황 출력

                
mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 5000  |
| Opened_tables | 195   |
+---------------+-------+
2 rows in set (0.00 sec)

Listing 4는 현재 테이블 5000개가 열려있으며, 테이블 195개가 열려야 했음을 보여준다. 캐시에 유효한 파일 기술자가 없기 때문에 이런 현상이 일어난다(직전에 통계를 초기화했으므로 5000개 열린 테이블 중에 단지 195개만 열렸다고 기록이 남는다). SHOW STATUS 명령을 다시 실행할 때 Opened_tables가 급격하게 올라가면, 캐시 적중률이 떨어진 상황이다. table_cache 설정값보다Open_tables 설정값이 훨씬 낮으면, 캐시를 너무 크게 잡았다(물론 여유있게 설정하는 방식은 나쁘지 않다). 예를 들어,table_cache = 5000으로 테이블 캐시 값을 조정한다.

테이블 캐시와 마찬가지로 스레드를 위한 캐시도 있다. mysqld는 접속을 받을 때 필요한 스레드를 만든다. 바쁜 서버에서 접속이 빠르게 연결되었다 끊어지면, 초기 접속 속력을 높아기 위해 나중에 사용할 요량으로 스레드를 캐시한다.

Listing 5는 충분한 스레드가 캐시되었는지 살펴보는 방법을 보여준다.


Listing 5. 스레드 사용량 통계 보기

                
mysql> SHOW STATUS LIKE 'threads%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Threads_cached    | 27     |
| Threads_connected | 15     |
| Threads_created   | 838610 |
| Threads_running   | 3      |
+-------------------+--------+
4 rows in set (0.00 sec)

여기서 가장 중요한 값은 Threads_created로 mysqld가 새로운 스레드를 생성할 때마다 하나씩 증가한다. 연속적으로 SHOW STATUS 명령을 내릴 때, 이 숫자가 급격하게 올라가면 스레드 캐시 수치를 높여야 한다. 예를 들어, my.cnf에서 thread_cache = 40을 설정하면 된다.

키 버퍼는 MyISAM 테이블을 위한 색인 블록을 저장한다. 이상적으로 이런 블록에 대한 요청은 디스크가 아니라 메모리에서 일어나야 한다. Listing 6은 메모리와 디스크에서 얼마나 많은 블록을 읽는지 확인하는 방법을 보여준다.


Listing 6. 키 효율성 확인

                
mysql> show status like '%key_read%';
+-------------------+-----------+
| Variable_name     | Value     |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads         | 98247     |
+-------------------+-----------+
2 rows in set (0.00 sec)

Key_reads는 디스크에서 요청한 숫자이며, Key_read_requests는 전체 숫자다. Key_reads를 Key_read_requests로 나누면 비적중률이 나온다. Listing 6을 보면 1000개 요청 당 0.6개가 적중하지 않았다. 1000개 요청 당 1개 이상 적중하지 않는다면 키 버퍼를 늘려야 한다. 예를 들어, key_buffer = 384M를 지정하면 버퍼를 384MB로 늘인다.

임시 테이블은 GROUP BY 절과 같이 추가 처리가 필요할 때 임시로 자료를 저장할 곳으로, 좀 더 고급 질의에서 사용된다. 이상적으로 이런 테이블은 메모리에 생성하지만, 임시 테이블이 너무 커질 경우 디스크에 써야 한다. Listing 7은 임시 테이블 생성과 관련한 통계를 보여준다.


Listing 7. 임시 테이블 사용량 보기

                
mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 30660 |
| Created_tmp_files       | 2     |
| Created_tmp_tables      | 32912 |
+-------------------------+-------+
3 rows in set (0.00 sec)

임시 테이블을 사용하면 Created_tmp_tables가 증가한다. 디스크 기반 테이블을 사용하면 Created_tmp_disk_tables가 증가한다. 이 비율을 정확하고 빠르게 결정하지 못하는 이유는 질의에 의존하기 때문이다. 시차를 두고 Created_tmp_disk_tables를 관찰하면 생성된 디스크 테이블 비율을 알 수 있고, 설정 값이 유효한지 살펴볼 수 있다. tmp_table_size와 max_heap_table_size 둘 다 임시 테이블 최대 크기를 제어하므로, my.cnf에서 양쪽 설정을 모두 확인해야 한다.

세션 단위 설정

이어지는 설정은 세션 단위다. 이 값을 설정할 때 신경을 써야 하는 이유는 잠재적인 접속 숫자에 설정값이 곱해지므로 메모리 사용량이 늘어나기 때문이다. 코드에서 해당 세션 값을 변경하거나 my.cnf에서 모든 세션 값을 변경할 수 있다.

MySQL이 정렬 작업을 수행할 때, 디스크에서 읽는 열을 저장하기 위한 정렬 버퍼를 할당한다. 정렬할 자료 크기가 너무 크다면, 디스크에 임시 파일로 자료를 저장하고, 다시 한번 정렬해야 한다. sort_merge_passes 상태값이 높으면, 디스크 활동량이 많다는 증거다. Listing 8은 정렬 관련 상태 카운터 몇 가지를 보여준다.


Listing 8. 정렬 통계 보기

                
mysql> SHOW STATUS LIKE "sort%";
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Sort_merge_passes | 1       |
| Sort_range        | 79192   |
| Sort_rows         | 2066532 |
| Sort_scan         | 44006   |
+-------------------+---------+
4 rows in set (0.00 sec)

sort_merge_passes가 높다면, sort_buffer_size 쪽에 관심을 기울여야 한다. 예를 들어, sort_buffer_size = 4M를 지정하면, 정렬 버퍼를 4MB로 늘인다.

MySQL은 또한 테이블을 읽기 위한 메모리를 할당한다. 이상적으로 보면 색인은 필요한 열에서만 읽도록 충분한 정보를 제공하지만, (자료 특성 때문이나 설계 잘못으로 인해) 읽어야 할 테이블이 많은 질의도 있기 마련이다. 이런 행동 양식을 이해하려면, (색인으로 직접 접근하는 대신) 테이블에서 다음 열을 직접 읽어야 하는 숫자와 SELECT 문 개수를 알아야 한다. 이렇게 하려면 Listing 9에서 소개하는 명령을 내린다.


Listing 9. 테이블 탐색 비율 확인

                
mysql> SHOW STATUS LIKE "com_select";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 318243 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE "handler_read_rnd_next";
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Handler_read_rnd_next | 165959471 |
+-----------------------+-----------+
1 row in set (0.00 sec)

Handler_read_rnd_next / Com_select는 테이블 탐색 비율을 보여주는데, Listing 9에서는 521:1이다. 4000이 넘어가면,read_buffer_size = 4M와 같이 read_buffer_size 값이 충분히 크게 설정되어 있는지 확인한다. 이 값이 8M 이상으로 커진다면, 개발자에게 질의 조율이 필요하다고 알려주자!


세 가지 필수 도구

구체적인 설정값을 파고 들 때, SHOW STATUS 명령이 유용하긴 하지만, mysqld에서 제공하는 방대한 자료를 해석하는 과정에 도움을 주는 몇 가지 도구가 필요하다. 세 가지 필수 도구를 찾아내었는데, 참고자료 절에 정리해놓았다.

대다수 시스템 관리자는 top 명령에 익숙하다. top은 태스크가 소비하는 CPU와 메모리를 주기적으로 갱신하면서 보여준다.mytop은 top을 모델로 만든 프로그램으로 현재 동작 중인 질의와 연결된 모든 클라이언트를 보여준다. mytop은 또한 키 버퍼와 질의 캐시 효율성에 대한 실시간 및 과거 자료를 제공하며, 실행 중인 질의 통계도 보여준다. 상황 파악에 유용한 도구이며, 10초 내로 서버 상태와 문제를 초래하는 모든 접속을 표시할 수 있다.

mysqlard는 MySQL 서버에 접속하는 데몬으로, 5초마다 자료를 수집해 라운드 로빈으로 동작하는 데이터베이스 뒷단에 저장한다. 웹 페이지는 테이블 캐시 사용량, 키 효율성, 접속된 클라이언트, 임시 테이블 사용량과 같은 자료를 출력한다. mytop은 서버 상태를 스냅 사진으로 찍어주며, mysqlard는 장기간에 걸친 서버 상태를 보여준다. 보너스로, mysqlard는 수집한 몇몇 정보를 활용해 서버 조율 방법을 제안한다.

SHOW STATUS 정보를 수집하는 또 다른 도구는 mysqlreport다. 이 도구가 mysqlard가 보고하는 내용보다 훨씬 자세한 보고 내역을 제공하는 이유는 서버의 모든 측면을 분석하기 때문이다. mysqlreport가 서버 조율에 뛰어난 이유는 상태 변수를 적절히 계산해 수정이 필요한 내용을 알려주기 때문이다.


요약

MySQL 조율 기본기를 설명하는 이 기사로 LAMP 컴포넌트 조율을 다루는 연재물을 마무리한다. 조율은 대부분 동작 원리를 이해하고 적절하게 동작하는지 확인하고 조정하고, 다시 평가하는 작업이다. 리눅스, 아파치, PHP, MySQL로 대표되는 각 컴포넌트마다 각자 요구 사항이 존재한다. 개별적으로 컴포넌트를 이해하고 있으면, 응용 프로그램을 느리게 만드는 병목을 제거하는 데 도움이 된다.


로그인 후 댓글쓰기가 가능합니다.

?

http://urin79.com

우린친구블로그

sketchbook5, 스케치북5

sketchbook5, 스케치북5

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소