※ 글쓰기에 앞서
● 설명은 글쓴이 편의상 경어체를 생략합니다. ^^
● 이 글은 Linux / MySQL 4.0~5.0 환경을 기초로 써진 글입니다.
● Windows버전의 my.ini의 설정은 비슷하면서도 약간씩 틀리니 참조만 하는 정도로 사용하시기 바랍니다.
● 설정 정보는 순수히 “참조” 사항이며, 실제 서버에 적용하기 전에 반드시 충분한 테스팅을 하시기 바랍니다.
● 이 글은 일정 이상의 MySQL 사전지식을 요합니다.
● 질의문(쿼리/query)은 글쓴이 편의상 쿼리로 통칭합니다.
● 질의문(쿼리/query)은 글쓴이 편의상 쿼리로 통칭합니다.
● 중간중간 참조한 사이트나 책 부분은 본문에 삽입하였습니다.
show status를 통한 MySQL 상태 분석하기
1. 현재 DB상태 분석을 위한 사전정보들 및 권장사항
mysql 실행 이후 다음의 기본적인 사항을 염두에 두고 내용을 사전 분석해보길 바란다.
1.1 my.cnf(my.ini) 의 이해
my.cnf는 MySQL 시작시 초기에 로딩하는 설정파일이다.
보통 기본 설치옵션대로 설치시 /etc/my.cnf 또는 /etc/mysql/my.cnf 등에 위치하며, 설정정보를 갱신할때는
mysql을 재시작 해주면 된다.
재시작 할 수 없을때는 임시로 SET 명령을 통해 일부 변수들만 값을 바꿀수 있으나 이경우 MySQL 재시작시
매번 적용해줘야 하는 불편이 있으며 이를 피하기 위해서 my.cnf 내 init_connect 명령으로
아래 예시와 같이 처리도 가능하다.
ex) init_connect = “SET NAMES euckr”
1.2 환경변수와 상태변수의 차이
환경변수(show variables)는 수치를 조정해서 MySQL의 상태를 제어할 수 있는 변수값이며
상태변수(show status)는 현재 MySQL 가동 상태를 알 수 있는 변수값이다
1.2.1 show variables 쿼리를통해 MySQL에서 사용되는 기본적인 환경변수의 값을 알 수 있다.
쿼리를 통해 볼 수 있는 변수는 mysqladmin 옵션형태로 적용 또는
my.cnf 내에서 사용할변수=값 형태로 사용 가능하다.
1.2.2 show status 쿼리를 통해 MySQL의 전체적인 상태를 알 수 있다.
쿼리를 통해 볼 수 있는 값들은 MySQL의 현재 상태들이며 이 문서에서 기본적으로 설명할 값들이다.
튜닝할때 지속적으로 분석해봐야 하며 가장 효율적인 분석은 가장 부하량이 많을때와 평균 상태를 잘 파악해야
효율적인 수치를 계산할 수 있으므로 바쁠때와 평균상태의 일정 주기를 정해서 분석해보는게 좋다.
1.3 서버 용도에 유의 해야 한다.
MySQL은 읽기(select)전용 DB서버와 쓰기(insert/delete/update)전용DB 서버의 기본적인 세팅을
틀리게 잡는게 유리하므로 서버가 초기 설계단계이거나 용도 변경이 가능 시 읽기/쓰기용 DB를
별도 분리시키면 세팅에 따라 높은 효과를 볼 수 있다.
참조 : 읽기전용 DB서버의 세팅시 RAID를 사용한다면 RAID1이 0보다 읽기속도가 더 빠르다.
1.4 하드웨어 Specification(이하 스펙)을 잘 파악해야 효율적인 세팅이 가능하다.
효율적인 세팅을 위해서는 가용가능한 메모리나 CPU등의 하드웨어 스펙을 미리 분석하고 있어야 한다.
MySQL은 기본적으로 한정된 메모리자원을 효율적으로 쓸수록 고성능을 발휘할 수 있게 된다.
주의할 사항으로 무조건적인 높은 수치가 고성능을 발생시키지 않는다.
2. show status를 통한 기초적인 분석방법 (MySQL 4.0 기반 설명)
기본 분석에 앞서 show status를 통해 볼 수 있는 상태변수들은 mysql 한글메뉴얼 부분을 참조하면 편합니다.
(4.0 기반 설명인데 링크 메뉴얼은 5.0이군요.. 죄송합니다.^^)
아래 링크에서도 MySQL 조율방법이 상세하게 나와있다.
일부 이해되지 않았던 내용은 서적 대용량 서버 구축을 위한 MySQL 성능 최적화 내 관련 사항 참조하였다.
위 서적에서 이 글에서 표현하지 못한 많은 내용을 알 수 있으므로 강력히 추천한다.
2.1 Aborted_clients / Aborted_connects : 0에 가까울수록 좋은 수치
네트워크 문제가 아닌이상 Aborted_clients가 올라가는 원인은 대부분 연결 프로그램에서 연결은 해놓고
닫지 않았거나, 환경변수인 max_allowed_packet을 초과하는 쿼리가 발생 혹은 연결했는데 wait_timeout이
경과할때까지 동작수행이 계속 되어 mysql자체에서 강제로 끊어버렸을 가능성이 가장 크다.
Aborted_connects 의 경우 최대 동시접속자(Max_used_connections)수치가
설정보다 높아서 발생할 가능성이 가장크며 그외엔 거의 발생할 이유가 없다.
2.2 Bytes_received / Bytes_sent
Bytes_sent가 높다면 이 서버는 읽기위주 작업(select)
Bytes_received 가 높다면 이 서버는 쓰기 위주작업(insert 등) 서버임을 알 수 있다.
위의 Questions,Uptime과 함께 응용해보면 1개 쿼리당 평균 Byte, 시간당 처리Byte등을 계산 할 수 있어
네트워크 트래픽등의 계산(예상 트래픽을 초과하는 쿼리가 있는지 등)에 유효하게 사용될 수 있다.
2.3 Questions / Uptime / Connections / Max_used_connections
Uptime 은 서버가 가동되고 나서 초를 의미하고, Questions는 서버가 가동되고 나서 수행된 쿼리의 수이다.
Connections는 서버 가동되고 나서 연결시도된 수치다.(현재 연결수는 Threads_connected)
Max_used_connections는 서버 가동 이후 최대 동시 연결 수치이다.
즉 환산해보면
Questions/Uptime = 초당 쿼리수를 나타낸다.
Connections / Uptime = 초당 연결수를 알 수 있다.
Questions / Connections = 연결당 쿼리 처리수를 알 수 있다.
놀고 있는 시간과 바쁜시간의 차이를 알기위해 1분간격으로 측정한다면 다음과 같은 내용 확인이 가능하다.
1분간의 초당 쿼리 처리건수 = (현재의 Questions – 1분전의 Question) / (현재의 Uptime / 1분전의 Uptime)
2.4 Create_tmp_disk_tables / Create_tmp_files /Created_tmp_tables
이 수치는 생각보다 많은 고민을 안겨주는데 Create_tmp_disk_tables의 수치가 높다면
물리디스크 엑세스를 자주 하고 있을것이므로 고속처리에서 문제를 가질 수 있다.
즉 고속처리를 원한다면 Create_tmp_disk_tables 수치는 0에 가까운게 좋다.
주로 읽기전용 서버에서 Create_tmp_disk_tables의 수치가 높아지기 쉬우며
쿼리가 BLOB또는 TEXT컬럼을 선택하는중에 임시테이블을 생성했다던지 할때 높아지기 쉽다.
이경우 메모리의 여유가 있다면 tmp_table_size 환경변수값이나 max_heap_table_size을 늘리면
좀더 나은 효과를 기대 할 수 있다.
Created_tmp_tables(메모리내 임시테이블 생성) 수치가 의도보다 높을땐 쿼리최적화 이외에 해결책이 없다.
2.5 Com_ 상태변수(Com_insert, Com_update, Com_delete 등)
각 명령문이 실행된 수치를 알려주며, 대략적으로 서버의 용도나 가장 빈번히 일어나는
쿼리의 형태를 알아낼 수 있다. 쿼리 타입과 관련된 사항이므로 의도하지 않은 쿼리가 발생하고 있다면
면밀히 살펴보길 바란다.
예를들어 Com_drop_db 등이 발생하거나 Com_drop_table, Com_truncate 등의 수치가 올라가 있다면
테이블이나 DB를 날린적이 있거나 종종 날린다는 소리이므로 의도한 동작인지를 확인해봐야 한다.
2.6 Delayed_errors / Delayed_insert_threads / Delayed_writes
insert delayed 를 별도로 사용하지 않는다면 올라갈 일이 없을 상태변수
덤으로 MySQL INSERT 성능 향상을 위한 좋은 포스팅을 소개한다.
근원e 님의 블로그 : http://www.lovelgw.com/Blog/225
2.7 Handler_ 관련 상태변수(Handler_delete / Handler_update / Handler_write 등)
핸들러 관련 상태변수는 보통 쿼리최적화와 연관되어 있으며, 해당 수치의 높고 낮음으로
DB에 연결되어 동작하는 쿼리문이 효율적으로 동작하는지 비효율적으로 동작하는지를 알아낼 수 있다.
예를들어 테이블 탐색 비율 = Handler_read_rnd_next / Com_select
인데 이 수치가 높을수록 인덱스를 비효율적으로 이용하는것으로 볼 수 있으며
단순 성능향상은 read_buffer_size 환경변수값의 조절을 통해 어느정도 가능하지만 근본적인 문제의
해결을 위해서는 쿼리문을 Explain으로 분석하여 효율적으로 인덱스를 활용해서 비율을 줄이는게 좋다.
(보통 4000:1 비율을 넘어가면 매우 비효율적으로 알려져있다.)
해당 핸들러 관련 상태변수는 아래 설명할 Key_관련 상태변수와도 연계되어 분석하는게 좋다.
2.8. Key_blocks_used / Key_read_requests / Key_reads / Key_write_requests / Key_writes
Key_read(초당 읽기 개수) 와 Key_writes(초당 쓰기 개수)는 높아질수록
물리디스크의 읽고 쓰는 비중이 높아지므로 속도 저하의 원인이 된다.
또한 읽기 관련 수치가 높아지는것은 캐시히트가 제대로 안되고 있을 가능성이 크다.
이경우 아래와 같은 식으로 캐시실패율을 구할 수 있으며, 캐시 실패율이 1% 이상인경우
쿼리 분석을 통해 효율적으로 인덱스를 태우도록 개선이 필요하다.
(임시방편으로 key_buffer_size 값을 늘리는 것도 가능하지만 근본적인 해결방법이 아니다!)
캐시실패율(%) = (Key_reads / Key_read_requests) * 100
또한 적당한 Key_buffer_size값을 구하려면 DB가 가장 바쁜시점에
Key_blocks_used * key_cache_block_size(4.0에서는 해당 환경변수가 없으므로 1024로 계산)
수치가 key_buffer_size 환경변수보다 훨씬 작다면 메모리 낭비가 있다는 소리이므로
Key_buffer_size의 값을 적당히 위값의 1.5~2배정도로 세팅하는게 좋다.
2.9 Open_files / Open_streams / Open_tables / Opened_tables
별다른 사항이 없다면 MyISAM 타입의 DB에서는 MYI(인덱스) MYD(데이터) 두개 파일을 열기 마련이므로
Open_files = Open_tables * 2 정도의 수치를 일반적으로 보이게 된다.
평균적인 Open_files 값이 환경변수 table_cache값보다 더 큰경우 Opened_tables가 증가할 수 있다.
Open_files는 환경변수인 open_files_limit를 넘지 않도록 조정해야한다.
table_cache 수치는 Open_tables * 2 정도로 조정하면 넉넉한 편이다.
(Opened_files 수치가 증가하지 않을 정도로 조금씩 줄여보는게 좋다.)
2.10 Qcache_ 관련 상태변수 (Qcache_free_blocks / Qcache_hits / Qcache_not _cached 등)
쿼리 캐시 관련 내용은 성능에 많은 영향을 주면서도 세팅이 다소 복잡한 편이므로 주의를 요한다.
해당 내용은 “대용량 시스템 구축을 위한 MySQL 성능 최적화” 책내 237 Page 설명을 참조하였다.
쿼리캐시는 기본적으로 비활성화 상태이므로 환경변수값이 초기에 세팅되어 있어야
사용가능하며, 이를통해 읽기작업이 빈번한 서버에서 효과적이다.
(query_cache_type / query_cache_size /query_cache_limit 등)
쿼리캐시는 데이터베이스를 이용시 동일한 쿼리가 여러번 반복되는 형태라면 효율적으로 동작 할 수 있으며
결과를 메모리에 저장해서 필요할때마다 찾아서 쓸 수 있어 성능을 높이는데 효과적이지만 과도한 쿼리캐시는
역시 역효과이므로 각각의 상태 변수값을 잘 확인해보는게 중요하다.
일반적으로 알려진 쿼리캐시 적중률은 다음과 같이 계산한다.
쿼리캐시적중률 = Qcache_hits / (Qcache_hits+Com_select)
캐시 적중률은 높은게 좋긴 하지만 높다고 무조건 좋은것은 아니며, 효율적인 튜닝을 위해서는
다음과 같은 사항을 염두에 두고 처리한다.
2.10.1 Qcache_lowmem_prunes
이 값이 높으면 메모리 부족때문에 무효화 된 쿼리가 있다는것이므로 쿼리캐시의 사이즈를 늘려본다.
2.10.2 Qcache_free_memory
평균적으로 이 수치가 높다면 활용되지 않고 있는 여유 메모리 공간이 있는것이므로 적당히 줄여본다.
2.10.3 Qcache_not_cached
이 수치는 쿼리가 캐시불가일때 올라갈 수 있으며 이 수치가 과도하게 높다면 쿼리가 비확정요소로 인해
캐시저장이 제대로 안되고 있다는 소리이다 ( 즉 I/O 부하가 걸리기 쉽다.)
예를들어 DATETIME형태에서 사용되는 NOW(),UNIX_TIMESTAMP() 등은 비확정요소로 취급되며 이도 저도
아닌경우에는 결과값이 너무 큰경우에도 비확정요소가 된다.
최종적으로 쿼리 개선으로 해결해야 할 사항이며 비확정요소 개선이 안되는경우 쿼리캐시는 비효율적이다.
2.10.4 Qcache_inserts
일반적인 상황에서 Qcache_inserts는 Com_select보다 작은편이 좋다.
2.10.5 그외 알아 둘 사항
● update가 많은 DB인 경우 쿼리캐시의 사용률 개선이 다소 어렵다.
● COUNT(*) 등의 집계형 쿼리가 자주 동작하면 쿼리캐시의 혜택을 받기 좋다.
● 복합적인 형태로 쿼리 캐시를 필요에 따라서 사용해야 한다면 환경변수 query_cache_type 을 조정한다
(0, 사용안함 1. 사용 2. SQL_CACHE 힌트가 있을때만 사용)
● 쿼리캐시의 직접적인 혜택을 측정해보려면 쿼리캐시를 일단 꺼보고 나서 성능을 측정 후 다시 켜고 나서
일정시간 지난후 측정해보는게 좋다.
● 쿼리캐시는 반복되는 쿼리가 많을수록 효과적이므로 늘 새로운 쿼리가 발생하는 형태라면
거의 효율을 낼 수 없다.
2.11 Select_관련 상태변수(Select_full_join / Select_full_range_join / Select_range 등)
Select관련 상태변수는 읽기 쿼리의 최적화가 되고 있는지를 파악하기 편하며, 쿼리최적화와 관련된 사항
이므로 위에 설명한 쿼리캐시 세팅과 맞물려 진행하면 나름대로 효과를 볼 수 있을것이다.
몇가지 중요한 부분만 체크해보면 아래와 같다.
2.11.1 Select_full_join
이 수치가 올라간다는것은 인덱스가 없이 join이 일어나고 있다는 것이며 이경우 상당한 부하를 줄 수 있다.
2.11.2 Select_full_range_join (join 상황) / Select_range(일반 상황)
이 수치는 테이블 내에서 범위조회(예를들어 limit 100, 50) 을 많이 실행할때 증가하기 쉽다.
Select_range_check 값이 증가한다면 쿼리가 사용하기 적합한 인덱스를 찾지 못하는 경우가 빈번한것으로
볼 수 있으며 Explain으로 쿼리를 분석해서 인덱스를 제대로 찾는지를 검토해보아야 한다.
2.12. Slow_launch_thread / Sort_merge_passes / Table_locks_wait
위 3개 상태변수는 한번 발생할때마다 심각한 지연을 줄 가능성이 크므로 꼼꼼히 살펴보는게 좋다.
2.12.1 Slow_launch_thread
이값이 증가하면 연결할때마다 스레드지연이 발생한다는 소리이므로
서버 자체의 문제 또는 시스템 과부하가 걸리고 있을 가능성이 있다.
2.12.2 Sort_merge_passes
파일로 정렬하는 행위가 발생할때 증가하므로 sort_buffer_size환경변수 값을 늘리거나, 쿼리를 개선한다.
예를들면 인덱스가 없는 필드를 기준으로 데이터를 order by할때 발생할 가능성이 있다.
2.12.3 Table_locks_waited
Table locks_waited 는 테이블락이 걸린 총 시간이다. 모든 커넥션에서 락이 발생할경우 누적되며
시간대별로 값을 측정후 이 값이 급격히 올라간다면 그 시간대에 지연이 발생하는 원인을 찾아야 할것이며
측정 방식은 아래의 예시등 자신이 생각하는 수치를 추출하여 작업해본다.
| 구간 자료 샘플 | A | B | C |
| Uptime(서버 가동시간/초) | 22467 | 22871 | 23145 |
| Connection(연결) | 5907 | 6446 | 6761 |
| Question(질의) | 572915 | 634509 | 749820 |
| Table_locks_waited(잠금지연/초) | 37292 | 46464 | 76449 |
| 구간별 산출값 샘플 | A~B구간 | B~C구간 | 비고 |
| 경과시간(초) | 404 | 274 | A~B구간은 6분44초 경과 B~C구간은 4분 34초가 경과 |
| 연결수 (연결 / 경과시간) | 539 | 315 | 연결은 줄어듬 |
| 쿼리수 (쿼리 / 경과시간) | 61594 | 115311 | 쿼리는 늘어남 |
| 잠금지연시간 | 9172 | 29985 | 구간별로 잠금지연이 급격히 증가하는것을 알 수 있음 |
| 연결당 쿼리수(쿼리수 / 연결수) | 114.3 | 366.1 | 적은 연결에서 더많은 쿼리를 수행중 |
| 초당 쿼리수 – 이하 AA (쿼리수/경과시간) |
152.5 | 420.8 | 1초에 평균적으로 처리하는 쿼리 수는 152건/ 420건 |
| 초당 잠금지연시간 – 이하 BB (잠금지연/경과시간) |
22.7 | 109.4 | 짧은 지연이 지속적으로 발생하여 성능에 악영향을 미치는것으로 예상됨 |
| 평균 쿼리별 잠금지연(AA/BB) | 6.7 | 3.8 | B~C구간보다는 A~B구간에서 긴 잠금이 발생했을 가능성이 큼 |
테이블 잠금은 동시간대 같은 테이블의 내용을 변경하여 데이터가 꼬이는것을 원천적으로 막기 위해
발생하는데 이를 개선하기 위해서는 테이블 분산 / 테이블 락 알고리즘 수동처리 혹은 필요에 따라
DB자체의 변경이 필요할 수 있다.
(보통 MyISAM 타입에서 서버수준의 잠금이 일어나며, InnoDB의 경우 레코드수준 잠금이라 큰 영향이 없다.)
단, 위 권장사항들은 사전계획을 제대로 세우고 하지 않으면 큰 재앙을 안길 수 있으니 주의할 것
2.13 Thread_cached / Threads_connected / Thread_created /Thread_running
스레드 관련 정보는 아래 재한님의 블로그중 관련 페이지를 일부 참조하였다.
재한님의 블로그 : http://jaehan.tistory.com/110
스레드는 CPU의 성능에 따라 적절한 조절이 필요하며, 아래의 경우에
Thread_created의 수치가 초과한만큼 증가한다.
Thread_connected(현재 연결수) – Thread_cached > thread_cache_size(환경변수)
Thread_created가 증가하고 있다면 Thread_cache 환경변수의 값을 올려주는게 중요한데
보통 CPU * 2 한도 내에서 조절을 권장한다.
(예를들어 쿼드코어 * 2CPU의 8 Core환경이라면 최대 16 이내에서 조절하는게 좋다)
3. 여담 및 마무리
애매하거나 궁금한 자료는 쭉 찾아보면서 글을 쓰다보니 이 글을 완료하는데만 7시간은 작업한 것 같다.
MySQL을 처음 손대던때에는 멋모르고 이것저것 크게만 세팅 해서 그냥 돌아가긴 했지만
어느순간부터 비효율적으로 돌아간다는게 느껴져서 본격적으로 세팅을 다시 잡아보려고 써놓는 글이다.
]]>
좋은 글 감사합니다.