E-mail :
- 작성자 : 김칠봉
- 작성일 : 2003-08-11(보완, 그래프 추가)
2003-08-04
- 내 용 : life time 계산에 의한 wait_timeout 설정
- 수 준 : 초중급 이상
- 키워드 : MySQL, wait_timeout, interactive_timeout, connect_timeout,
max_connections, Aborted_clients, Connections,
Max_used_connections, Slow_queries, Questions, Uptime
*주1)
이 문서에 대한 최신 내용은 아래 URL에서 확인할 수 있습니다.
http://www.linuxchannel.net/docs/mysql-timeout.txt
*주2)
이 문서에서 사용한 life time, EXP, CUR, PAS, POL, DEF, LPT ... 와 같은
용어는 필자가 자의적으로 붙인 가칭용어로써 공식적인 용어가 아닙니다.
---------------------------------------------------------
목차
0. 배경
1. MySQL의 time out
1-1. connect_timeout
1-2. interactive_timeout
1-3. wait_timeout
2. 연결 취소율(POC)과 connection life time
2-1. 연결 취소율(POC)
2-2. connection life time
3. 임계 life time
3-1. 현재 최대(최악) 예상 임계 life time (EXP)
3-2. 현재 평균 임계 life time (CUR)
3-3. 지난 과거 최대 임계 life time (PAS)
3-4. 지난 과거 유추 최대 임계 life time (POL)
4. 임계 life time(LPT) 계산(예제)
5. wait_timeout 계산 및 보정
6. 결과 확인
7. 후기
---------------------------------------------------------
0. 배경
아주 바쁜 MySQL 서버에서는 간혹 'Too many connections' 이라는 에러를 만날
수 있을 겁니다.
대부분 이 에러를 해결하기 위해서,
- max_connections
- wait_timeout
이 두개의 파라메터를 튜닝하면서 설정하는 것이 일반적입니다.
그런데, MySQL 매뉴얼에는 이 에러에 대한 자세한 설명이 빠져 있습니다.
예를들어, 실제 Max_used_connections 이 한계 max_connections 에 도달하지 않았는데도
불구하고 이런 에러를 만나면 상당히 난처합니다.
이런 경우는 대부분 max_connections 값을 올리고, wait_timeout 값을 줄여서 튜닝
하곤 하지만 역시 정확한 튜닝이 어렵습니다.
실제로 좀더 정확하게 튜닝하기 위해서는,
- 시스템 전체 상황(실제 어느 정도로 바쁜지에 대한 상대적 수치),
- 초당 connections 수,
- 커넥션당 평균 쿼리 요청수,
- 커넥션당 생성된 평균 쓰레드 수
- 초당 평균 전송량
- DISK에 생성된 임시 테이블 생성 비율
- Slow_queries
- 한계 도달 N 초 계산
- 커넥션 life time
이런 값들을 계산 및 고려하여,
- max_connections
- wait_timeout
- back_log
- thread_cache_size
- key_buffer_size
- record_buffer (read_buffer_size)
- record_rnd_buffer
- sort_buffer_size
- 기타 메모리 설정
이런 파라메터에 설정을 해줘야 합니다.
실제 MySQL 서버의 파라메터 설정은,
1) 사용가능한 최대 전체 데이터베이스 크기와 각 테이블 평균 크기 계산
2) MySQL 이 사용하는 시스템 물리적 메모리 크기
3) 1)에 의한 shared 메모리와 쓰레드 메모리 할당 및 계산
4) 2)과 3)에 의한 최대 동시 접속 가능한 max_connections 계산
5) time out 설정
6) 그 외 설정
7) 시스템을 운영하면서 지난 통계 데이터에 의한 설정값 다시 튜닝
이와 같은 순서로 튜닝해 나가야 합니다.
2)번과 3)번 같은 경우는
MySQL 이 사용할 물리적 메모리 총합 =
(
key_buffer_size +
(read_buffer_size + sort_buffer_size) * max_connections
)
이와 같은 공식을 사용합니다.
전자의 key_buffer_size 는 모든 쓰레드들이 항상 공유해서 사용하는 shared
메모리이고, 그 밑의 두개는 각 쓰레드마다 사용하는 not shared 메모리입니다.
간략하게 정리하면 다음과 같습니다.
MySQL caches(all threads shared)
(
- key_buffer_size : 8MB < INDEX key
- table_cache : 64 < number of open tables for all threads
- thread_cache_size : 0 < number of keep in a cache for reuse
)
MySQL buffers(not shared)
(
- join_buffer_size : 1MB < FULL-JOIN
- myisam_sort_buffer_size : 8MB < REPAIR, ALTER, LOAD
- record_buffer : 2MB < sequential scan allocates
- record_rnd_buffer : 2MB < ORDER BY(avoid disk)
- sort_buffer : 2MB < ORDER BY, GROUP BY
- tmp_table_size : 32MB < advanced GROUP BY(avoid disk)
)
MySQL memory size
(
- INDEX(key) : 8MB < key_buffer_size (shared)
- JOIN : 1MB < join_buffer_size (not shared)
- RECORD(read) : 2MB < record_buffer (not shared)
: 2MB < record_rnd_buffer (not shared)
- SORT : 8MB < myisam_sort_buffer_size (not shared)
: 2MB < sort_buffer (not shared)
- TABLE(temporary) : 32MB< tmp_table_size(not shared)
)
MySQL timeout
(
- connect_timeout : 5 > bad handshake timeout
- interactive_timeout : 28800 > interactive to re-interactive timeout
- wait_timeout : 28000 > none active to re-active timeout
)
MySQL connections
(
- max_connections : 100 < 'to many connections' error
- max_user_connections : 0(no limit) < user limit
)
이 글은 메모리 설정에 관한 내용은 상당히 방대하므로 여기에서는 제외하고,
MySQL 의 time out 계산에 관한 내용입니다.
이 글의 전제 조건
(
- MySQL 서버가 상당히 바빠서 time out 설정이 필요하다.
- 서버의 바쁜 정도와 데이터베이스 크기에서 계산한
max_connections 설정값이 현재 MySQL 서버의 한계점이다.
(초과시 서버 증설이 필요함)
- 연결 취소율(Aborted_clients*100/Connections)이 높은 경우
(튜닝 기준 0.1% 또는 0.5% 이상)
)
time out 설정 목적
(
- 제한된 자원(메모리)의 효율적 이용
- MySQL 성능 극대 (한계점에 극대, 한계점을 초과하지 않음)
- 원할한 커넥션 처리
- 절대적으로 불필요한 커넥션을 강제로 close(불필요한 커넥션 반환)
- 기타
)
1. MySQL의 time out
MySQL 서버(정확히 `mysqld')의 time out 설정은 크게 3가지가 있습니다.
mysqld time out
(
- connect_timeout (bad handshake timeout)
- interactive_timeout (interactive 모드에서 connection time out)
- wait_timeout (none interactive 모드에서 connection time out)
)
connect_timeout, interactive_timeout 은 튜닝과 좀 거리가 멀고,
실제로 바쁜 서버라면, 반드시 wait_timeout 을 따로 튜닝하여 설정해줘야 합니다.
(실제 이 글의 내용임)
1-1. connect_timeout
이 설정은 mysqld 와 mysql client 가 연결(connection)을 맺기 위해서
mysqld 가 연결 패킷을 기다리는 최대 시간입니다.
즉 TCP 연결을 맺는 과정(3-way handshake)에서, connect_timeout 동안에도
연결 패킷이 들어오지 않으면 연결이 실패(취소가 아님)되고,
bad handshake 로 응답합니다.
*참고)
(
- 연결 실패 : 연결 과정중에서 fail 되는 경우 (Aborted_connects)
- 연결 취소 : 연결이 된 상태에서 강제로 close 된 경우 (Aborted_clients)
)
바꾸어 말하면 mysqld 와 mysql client 가 TCP 연결을 맺는 최대 시간으로
이 시간보다 큰 경우는 모두 Aborted_connects 에 해당됩니다.
(단위는 초)
연결 실패율(POF) =
(
Aborted_connects * 100 / Connections
)
연결이 실패되는 경우
(
- 연결 패킷에 올바른 연결 정보가 없는 경우
- 특정 user 가 권한이 없는 데이터베이스에 접근할 경우
- mysqld 접근 password 가 틀린 경우
- connect_timeout 보다 긴 연결 과정
)
연결 실패율(POF)이 높은 경우는, 대부분 권한이 없는 데이터베이스 연결이나,
틀린 password 를 사용할 경우가 많습니다.
기본값은 대부분 5(초)로 설정되어 있으며, 따로 튜닝할 필요는 없습니다.
mysqld 의 --warnings 옵션 사용과 xxx.err 파일에 기록됩니다.
1-2. interactive_timeout
interactive 모드에서 time out 을 말합니다.
interactive 모드는 'mysql>' 과 같은 프롬프트 있는 콘솔이나 터미널 모드를
말합니다.
mysqld 와 mysql client 가 연결을 맺은 다음, 다음 쿼리까지 기다리는
최대 시간을 의미합니다.
설정된 interactive_timeout 까지도 아무런 요청(쿼리)이 없으면 연결은
취소되고, 그 이후에 다시 요청이 들어오면 연결은 자동으로 맺어집니다.
interactive_timeout 안에 다시 요청이 들어오면 wait time은 0으로 초기화
됩니다(CLIENT_INTERACTIVE).
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 12002
Current database: xxx
이와 같은 연결 취소는 Aborted_clients 에 누계되고, wait_timeout 의
결과도 함께 포함됩니다
기본 값은 28800(8시간) 초로 설정되어 있는데 상당히 관대한(?) 설정입니다.
약 1시간(3600) 정도로 설정하는 것을 권장합니다.
1-3. wait_timeout
이 설정은 제일 중요한 파라메터 항목입니다.
interactive 모드가 아닌 경우에 해당되며,
mysqld 와 mysql client 가 연결을 맺은 후, 다음 쿼리까지 기다리는
최대 시간을 의미합니다.
즉 대부분 PHP 나 C, PERL, python 등등의 API 를 이용한 client 프로그램
모드를 말합니다.
interactive_timeout 과 마찬가지로 wait_timeout 까지 아무런 요청(쿼리)이
없으면 연결은 취소되고 그 결과는 Aborted_clients 에 누계됩니다.
wait_timeout 안에 다시 요청이 들어오면 wait time 은 0 으로 초기화 됩니다.
(SESSION.WAIT_TIMEOUT)
연결 취소율(POC) =
(
Aborted_clients * 100 / Connections
)
연결이 취소되는 경우(강제 종료됨)
(
- 종료(exit) 되기전 mysql_close() 가 없는 경우
- wait_timeout 이나 interactive_timeout 시간까지 아무런 요청(쿼리)
이 없는 경우
)
기본 값은 interactive_timeout 과 마찬가지로 28800(8시간) 초로 설정되어
있는데, 역시 너무 관대한(?) 설정이라고 할 수 있습니다.
앞에서 연결 취소율(POC)을 계산해 보면, MySQL 서버가 어느 정도 비율로 강제
종료하는지 알 수 있습니다.
예를 들어 POC 가 1 % 이라면, 100 개의 커넥션당 하나 정도는 mysql_close()
없이 강제 종료(exit)되고 있다는 의미입니다.
이 값이 0 %에 가까울수록 좋습니다. 이 의미는 클라이언트 프로그램에서
모두 정상적으로 종료했다는 의미입니다.
2. 연결 취소율(POC)과 connection life time
2-1. 연결 취소율(POC)
연결 취소율 값이 지나치게 높으면, wait_timeout 설정이 너무 짧거나,
대부분 client 프로그램이 exit(종료)를 정상적으로 하지 않은 경우이므로 반드시
튜닝이 필요합니다.
특히 서버가 매우 바쁜 경우는, 이 wait_timeout 시간을 짧게 설정하여 불필요한
커넥션을 모두 제거해 줘야합니다(메모리 한계 문제).
wait_timeout 튜닝이 필요한 경우
(
- 보통 POC(연결 취소율)가 1 % 이상인 경우
- 아주 바쁜 서버에서는 튜닝전 0.01 % 이상인 경우
- 기타 튜닝 기준에 따라 다름
)
주의할 점은,
client 프로그램(예: PHP)을 수정하지 않는 상태에서, wait_timeout 을 튜닝하면
튜닝전에 비해서 POC 가 더 올라가야 정상입니다. 이 의미는 비정상적인 커넥션을
반환하는 비율이므로, 정상적인 서비스하에서 이 값이 올라가야 함을 이미합니다.
warning
(
이하 다루는 'life time' 이나 '임계 life time' 등의 내용은 반드시
wait_timeout 튜닝이 필요한 경우에 해당됩니다.
만약, wait_timeout 튜닝이 필요하지 않다면 이하 내용을 필요하지 않습니다.
)
그럼 이제 wait_timeout 값을 계산해 봅시다.
이 값을 계산하기 위해서 mysqld 로그 파일을 분석해야 하는데,
불행히도 시간 기록이 1 초 간격으로 기록되어 있어서 접속이나 close 또는
각 쿼리 시간 통계를 구하기 어렵습니다.
(표본을 하루 단위로 축출하여 계산할 경우, 좀더 정확한 자료가 됨)
따라서, 현재 MySQL 서버의 STATUS 통계 자료를 이용하는 것도 하나의 방법입니다.
통계를 얻는 방법
(
mysql> tee /path/to/mysql.status.txt
mysql> SHOW STATUS;
or
shell> mysqladmin [OPTIONS] extended-status > mysql.status.txt
)
주요 MySQL STATUS
(
Aborted_clients
Connections
Max_used_connections
Slow_queries
Questions
Uptime
...
)
직접적인 평균 wait_timeout 값을 구할 수 없기 때문에,
각 커넥션을 일렬로 늘어놓은 상태의 평균적인 커넥션 life time 값을
구해야 합니다.
2-2. connection life time
'life time' 은 하나의 커넥션이 연결된 후 완전히 close 될 때까지 시간을 말하며,
필자가 자의적으로 붙인 용어입니다.
즉, 여기에서의 life time 은 하나의 커넥션이 살아있는 동안의 시간을 말하며,
이 시간에는 쿼리 실행, connection idle, wait timeout, interactive timeout 등등이
모두 포함되어 있는 time을 말합니다.
조건
(
connection idle ==> wait time out
interval of connection ==> 0
)
가정
(
+----------------------------+------------------->+--------->
|connection | close|connection
|<-- queries execute time -->|<-- wait timeout -->|
|<------------- connection life time ------------>|
)
life time (하나의 커넥션당 평균)
(
= 쿼리 실행 시간(long query 포함)
+= { wait timeout | interactive timeout }
)
life time =
(
connection
[ wait time out ]
[ += 1'th query execute time ]
[ += wait time out]
[ += 2'th query execute time ]
[ += wait time out]
[ ... ]
[ += wait time out]
[ += n'th query execute time ]
[ += wait time out]
close
)
커넥션 관련 통계
(
cps = Connecions / Uptime ; // 초당 커넥션 수
spc = Uptime / Connections ; // 커넥션 주기(초)
cpq = Question / Connections ; // 커넥션당 요청 쿼리 수
)
실제 life time 은 mysqld 로그 파일을 분석해봐야 하는데,
이것 역시 상당히 까다롭습니다(exit 시간 기록이 없기 때문).
따라서, 여기에서는 '실제 life time'이 아닌 가상의 '임계 life time'
를 구해서 wait time out 설정에 중요한 자료로 활용하고자 하는 것이
이 문서의 본 내용입니다.
3. 임계 life time
모든 커넥션이 close 되지 않는다는 가정하에서, 즉 모든 커넥션이
한번 접속후 계속적으로 살아있다는 가설을 내리고,
한계 도달 N sec 를 구해서 이에 대한 평균값(AVG)을 구해보면
이 평균값이 커넥션당 '평균 임계 life time'이 됩니다.
바꾸어 말하면,
모든 커넥션들을 꼬리에 꼬리를 무는 가상의 평균적인 일직선으로 만들어
놓고, 한계 도달 N sec 를 구하는 방법입니다
(커넥션과 커넥션 간격을 0으로 봄).
문제가 되는 경우'
(
동시 접속 connection 수(Y)
|
+ Max connection ------+------+------
| '| |
| ' | |
| ' | |
| . | |
| . | |
| . | |
| . . . . . ' | |
| | |
+----------------------+------+-------> X sec
N sec
* 시간이 지남에 따라 급격한 기울기(오목한 분포)
)
잘 튜닝된 경우
(
동시 접속 connection 수(Y)
|
+ Max connection ------+------+------
| . ' ' |
| . |
| ' |
| ' |
| . |
| . |
| . . . . . ' |
| |
+----------------------+------+-------> X sec
N sec
* 시간이 지남에 따라 완만한 기울기(볼록한 분포)
)
가정
(
동시 접속 connection 수(Y)
|
+ Max connection -------------+------
| | . '|
| | . ' |
| | . ' |
| + ' |
| . ' | |
| . ' | |
| . ' | |
| | |
+--------------+--------------+-------> X sec
(N+1)/2 N sec
* 커넥션 간격을 0으로 봄(직선적 기울기)
)
한계 도달 N sec 계산법
(
1 sec : 1 * cps
2 sec : 2 * cps
3 sec : 3 * cps
...
N sec : N * cps => max_connections or Max_used_connections
)
or
(
N sec : max_connections or Max_used_connections / cps
)
최대(최악 상태) 한계 도달 life times 분포와 그 평균값(AVG) 계산법
(
1 sec : 1 * cps'th connection : N sec (life time)
2 sec : 2 * cps'th connection : N - 1 sec
3 sec : 3 * cps'th connection : N - 2 sec
...
N sec : N * cps'th connection(max) : N - (N -1) sec
AVG : (N + 1) / 2 sec (life time) ; // 임계 life time
)
모든 커넥션이 각각(평균) 이 '임계 life time'와 같거나 더 클 경우
결국 N sec 에 도달해서 full connection 이 된다는 의미입니다.
그 반대로,
커넥션 평균 life time 이 임계 life time 보다 작으면 N sec 이후에서
full connection 이 된다는 결론이 나옵니다.
이것은 mysqld 가 최악의 상태를 말하며, 아주 바쁜 MySQL 서버이거나
아주 바쁜 시간대(rush hour)에 충분히 이런 상황이 될 수 있다는 것을
반증합니다.
이 '임계 life time' 을 구해서 wait_timeout 설정하는데 중요한 자료로
활용하는 것이 본 글의 목적이며, 이 '임계 life time'을 다른 변수값들과
서로 보정하여 최종 wait_timeout 으로 설정하는 내용입니다.
현재 최대(최악) 예상 임계 life time 계산(EXP)
(
N sec = max_connections / cps
= max_connections * spc
= max_connections * Uptime / Connections
임계 life time(EXP)
= (N + 1) / 2
)
현재 평균 임계 life time 계산(CUR)
(
N sec = Max_used_connections / cps
= Max_used_connections * spc
= Max_used_connections * Uptime / Connections
임계 life time(CUR)
= (N + 1) / 2
)
지난 과거 최대 임계 life time 계산(PAS)
(
N sec = Max_used_connections / maximum of cps
= Max_used_connections * minimum of spc
임계 life time(PAS)
= (N + 1) / 2
)
지난 과거 유추 최대 임계 life time 계산(POL)
(
N sec = max_connections / maximum of cps
= max_connections * minimum of spc
임계 life time(POL)
= (N + 1) / 2
)
3-1. 현재 최대(최악) 예상 임계 life time(EXP)
이 값은 MySQL 서버가 시작후 현재까지의 초당 평균 커넥션 수('이하 'cps')
를 기준으로 계산할 때, full connection 인 max_connections 에 도달할 때의
평균 임계 life time 입니다.
즉 모든 커넥션은 각각 절대로 이 EXP 보다 크면 안된다는 의미가 됩니다.
(한계점이므로)
실제로 (wait_timeout > EXP) 경우는 wait_timeout 효과를 기대하기 어렵습니다.
예를 들어
(
wait_timeout = 120
EXP = 63
)
이와 같은 경우가 많은데, 이것을 분석(해석)해 보면,
모든 커넥션의 평균 임계 life time 이 64 초인데 굳이 120 초까지 기다려서
close 하는 경우가 거의 없다는 의미가 됩니다. 물론 평균적인 계산할 때의
얘기입니다.
따라서 최소한 wait_timeout 은 EXP 와 같거나 이 값보다 작게 설정해주는 것이
효과가 있습니다.(크면 별 효과를 기대하기 어려움)
3-2. 현재 평균 임계 life time (CUR)
이 값은 현재까지 최대 동시 접속 커넥션(Max_used_connections)에 도달할 때의
평균 임계 life time 입니다.
즉 life time 이 현재 추세로, 평균적으로 진행할 때의 임계 life time 입니다.
EXP 보다 항상 작거나 같습니다. 서로 같은 경우는 지난 과거에 벌써
full connection 까지 도달했다는 의미가 됩니다.
이 값은 단지 평균적인 현재 추세를 알아보는데 유용합니다.
그런데,
EXP 와 CUR 모두 현재 평균적인 추세에 대한 life time 입니다.
모든 프로그램이 그렇듯이 평균적인 작동원리는 설정은 상당히 위험합니다.
즉 최악의 상태까지 고려해서 프로그램에 임해야 한다는 것입니다.
따라서, EXP와 CUR 값보다 더 작은 임계 life time 을 구해야 하는데,
이것은 지난 과거에 가장 바빴던 cps 로 계산한 POL 해야 합니다.
3-3. 지난 과거 최대 임계 life time (PAS)
지난 과거중에서 최대 cps 값을 선택하여 계산할 때의 임계 life time 입니다.
이 값은 다른 임계 life time 보다 항상 작습니다.
과거중에서 최대 cps 구하는 방법이 조금 까로운데 이것은 매 시간대마다 또는
rush hour 에 체크하여 그 통계의 결과치를 구해야 합니다.
만약 최대 cps 를 구하기 어려우면 현재 평균 cps * (1.5 ~ 2.0) 정도로 계산하면
됩니다.
이 PAS 값은 wait_timeout 값을 구하는데 결정적인 자료로 쓰이며,
CUR 의 보정값이라고 생각하면 됩니다.
3-4. 지난 과거 유추 최대 임계 life time (POL)
EXP 가 현재 평균 cps 값으로 계산한 임계 life time 에 반해서, POL 은 PAS 와
같이 과거중 최대 cps 값으로 계산한 임계 life time 입니다.
즉,
EXP 는 평균적 cps 에 의해서 각 커넥션을 일직선으로 늘어 놓는데 반해서,
POL 은 최대 cps 에 의해서 각 커넥션을 일직선으로 늘어 놓은 상태에서 계산한
life tiem 값입니다.
이 값도 PAS 와 같이 wait_timeout 값을 구하는데 결정적인 자료로 쓰이며,
EXP 의 보정값이라고 생각하면 됩니다.
4. 최종 임계 life time(LPT) 계산(예제)
실제 예를 들어 보겠습니다.
ex1
(
max_connections = 100
Max_used_connections = 13
AVG of cps = 0.1
MAX of cps = 0.3
); // 매우 바쁘지 않지만 과거 어느 순간에 극대로 바빴던 경우
ex2
(
max_connections = 100
Max_used_connections = 92
AVG of cps = 0.8
MAX of cps = 1.1
); // 비교적 바쁜 서버로써 거의 한계점에 도달하고 있는 경우
ex3
(
max_connections = 100
Max_used_connections = 100
AVG of cps = 2.4
MAX of cps = 2.7
); // 아주 바쁜 서버로 과거에 이미 한계점에 초과한 경우
+------+------+------+---------+-----------+-------+-------+
| 구분 | EXP | POL | | CUR (C%) | PAS | (DEF) |
|------+------+------+---------+-----------+-------+-------|
| ex1 | 201 | 167 | | 66 (33%) | 22 | 44 |
|------+------+------+---------+-----------+-------+-------|
| ex2 | 63 | 46 | | 58 (92%) | 42 | 16 |
|------+------+------+---------+-----------+-------+-------|
| ex3 | 21 | 19 | | 21(100%) | 19 | 2 |
+------+------+------+---------+-----------+-------+-------+
* C% : (CUR * 100 / EXP) ; // 평균 cps 에 대한 임계 도달 사용율
* DEF : CUR - PAS ; // 편차
서버가 바쁠수록 EXP 나 CUR 값이 점점 작아지고, C% 값은 점점 커집니다.
각각 max_connections이 서버의 한계라는 설정에서 EXP 나 CUR 값이 10 보다
작으면 서버의 증설이 필요하다는 것을 의미합니다.
(slow query 10 sec 에 기준을 둔다면)
여기에서 중요한 임계 life time 은 PAS 값인데,
이 PAS 값은 제일 바쁜 상태가 계속된다는 가정이므로 다른 값보다 항상
제일 작습니다.
실제 위의 예를 보면,
ex1이 ex2 보다 평균적으로 더 바쁘지 않지만 PAS 값이 더 작습니다.
이 의미는 과거의 어느 순간에 ex2 보다 더 바빴다는 증거이고 앞으로
그럴 가능성이 있다는 의미입니다.
DEF 값이 크다는 의미는 평균과 최대치의 life time 의 차가 크다는 의미로
서버가 바쁠때와 그렇지 않을때의 차가 크다는 의미입니다.
또한 PAS 값이 10 보다 작으면 서버 증설 필요성이 상당히 높다는 의미가
됩니다. (slow query 10 sec 에 기준을 둔다면)
EXP, POL, CUR, PAS 중에서 튜닝할 임계 life time 값을 선택해야 하는데,
POL 이나 PAS 값 중에서 하나를 선택하면 됩니다.
POL 값을 선택할 경우는 매우 관대한 정책(설정)이 될 것이고, PAS 값을
선택하면 매우 제한적인 설정이 됩니다.
주의할 점은 선택한 임계 life time 이 10 보다 작으면 서버가 아주 바쁜
상태에 목표를 두고 튜닝해야 하므로 주의해야 합니다.
여기에서는 편의상 PAS 값을 선택하겠습니다.
그런데 ex1 같은 경우는 DEF 편차가 상당히 큰데, ex1 비교적 그리 바쁘지
않으므로 그 다음으로 작은 CUR 값을 선택하는 것이 좋습니다.
만약 서버가 전체적으로 비교적 바쁘다고 생각하면 제일 작은 PAS 값을
선택하면 됩니다.
+------+----------------------------------+
| 구분 | 최종 임계 life time (LPT) |
|------+----------------------------------|
| ex1 | 66 |
|------+----------------------------------|
| ex2 | 42 |
|------+----------------------------------|
| ex3 | 19 |
+------+----------------------------------+
5. wait_timeout 계산 및 보정
이제 life time 이 결정되었으니 wait_timeout 값을 설정해 봅시다.
앞서 얘기했듯이 life time 은 각 커넥션을 평균적으로 일직선상에
놓았을 경우, 한계 도달 시간을 의미합니다.
즉 각 커넥션은 평균적으로 이 life time 값을 넘어서면 안된다는 의미
입니다. (max_connections 값이 이미 정해진 한계이기 때문에)
LPT 값이 19 이라면(ex3의 경우),
cpq = 8(예)
(
Questions / Connections
); // 커넥션당 평균 쿼리 요청수
LPT =
(
(connection)
(
[ = wait time out]
[ += 1'th query execute time ]
)
(
[ += wait time out]
[ += 2'th query execute time ]
)
(
[ ... ]
)
(
[ += wait time out]
[ += n'th query execute time ] ; // n => cpq
[ += wait time out]
)
(close)
) < 19
이런 조건식이 나옵니다.
그리고
하나의 쿼리가 실행한 시간과 그 다음 쿼리까지의 시간을 평균적으로
계산하면 다음과 같습니다.
2.4 sec = 19 / 8 = LPT / cpq
보통 하나의 쿼리가 실행하는 시간은 상당히 짧은 0.001 ~ 2.0 sec 정도
되는 것이 보통입니다.(물론 예외도 있음)
그러면 대충 wait time out 값을 계산 또는 짐착할 수 있습니다.
즉 평균적으로 2.4 초 보다 항상 작다는 결론이 나옵니다.
그러나
이 값을 곧바로 wait_timeout 값으로 설정하면 상당히 위험합니다.
이 값은 어디까지나 평균적인 수치일 뿐 편차에 대한 고려가 전혀 없습니다.
예를 들어, 각 커넥션마다 쿼리가 하나이면 상관은 없지만,
다음과 같은 경우가 상당히 많기 때문입니다.
life time
(
connection
(
= wait time out ; // 0.1 sec
= 1'th query execute time ; // 0.4 sec
)
(실제 쿼리에 상관없는 작업 시간 = 3.1 sec)
(
+= wait time out ; // 3.1 sec > 2.4 sec
+= 2'th query execute time ; // 0.2 sec
)
close
) < 19 ; // 예제
따라서 앞에서 계산한 2.4 sec 는 실제로 wait_timeout 에 적용하기에는
매우 적절하지 않습니다.
결론적으로
하나의 커넥션이 최소한 하나 이상의 쿼리가 있는 경우가 거의 대부분이므로
이 점을 고려서 계산하면 다름과 같은 최종적인 계산식이 나옵니다.
wait_timeout
(
= LPT - (LPT / cpq)
= LPT - (LPT * Connections / Questions)
)
* 단) LPT > 10
* 단) cpq > 1
* 단) wait_timeout > 10 (한계값, slow query 고려)
+------+-------+----------+--------------------+---------+
| 구분 | LPT | cpq(예) | wait_timeout | AVG |
|------+-------+----------+--------------------+---------|
| ex1 | 66 | 5 | 53 | 59 |
|------+-------+----------+--------------------+---------|
| ex2 | 42 | 7 | 36 | 39 |
|------+-------+----------+--------------------+---------|
| ex3 | 19 | 9 | 17 | 18 |
+------+-------+----------+--------------------+---------+
이 wait_timeout 은 상당히 제한적인 정책입니다.
좀더 관대한 설정을 원한다면 LPT 값을 wait_timeout 값으로 설정하거나
LPT와 계산한 wait_timeout 과 평균값(AVG)을 최종 wait_timeout 값으로
설정하면 됩니다.
6. 결과 확인
이제 최종적으로 wait_timeout 값이 정해졌므로 이 값을 서버에 적용해
봅니다.
적용 예
(
shell> safe_mysqld ... -O wait_timeout=17 &
or
[mysqld]
...
set-variable = wait_timeout=17
)
튜닝전에 비해서 연결 취소율(POC)이 더 올라가야 정상입니다.
이 의미는 비정상적인 커넥션을 반환하는 비율이므로, 성공적인
튜닝이라고 할 수 있습니다.
7. 후기
헙... 어디에서 쇠(?) 타는 냄새가 난다했더니....
CPU 팬이 안도네요(설마 설마 했더니)....T.T
그것도 AMD CPU ....
제가 냄새에 민감해서 천만다행..
my.cnf에 wait_timeout설정하는 문제
Tips 2008/01/31 17:21
구글링을 해보면 이 문제로 많은 사람들이 삽질을 이미 했다. 그리고 대다수가 버그라고 생각하고 있었다.
버그가 아니다. 당연한 삽질이었다.
my.cnf의 [mysqld] section에 wait_timeout=300 으로 설정 추가하고 다시 띄운다.(기본값은 28800)
mysql로 접속하여 show variables like 'wait_timeout' 을 해본다.
어라? 28800이다. 왜 적용이 안되는 걸까..
show variables like '%timeout%'을 해보면, interactive_timeout이라는게 있는데, 이게 바로 커맨드라인 클라이언트인 mysql (보통 /usr/local/mysql/bin/mysql 또는 /usr/local/bin/mysql 등등)을 말하는 거다. 당연히 mysql 기본 클라이언트로 interactive mode접속하면 wait_timeout을 interactive_timeout값으로 바꿔주는거다.
왜냐구? interactive mode로 접속했으니까 그렇지 ㅡ_ㅡ
show global variables like '%timeout%'을 해본다.
wait_timeout=300으로 설정이 잘 되어있다. 즉, interactive mode가 아닌 접속 쓰레드에 대해서 wait_timeout이 새로 설정한 값이 된다는거다. 당연히 mysql로 접속해서는 확인을 못한다.
본인이 작성하는 프로그램의 라이브러리를 이용하여 테스트 커넥션을 만들고 300초간 아무것도 하지 않은 후에 쿼리를 하나 날려보자 "SELECT 1" 같은..
에러가 난다. 2006, Mysql server has gone away. 설정한 wait_timeout이 먹었다.
뭐.. 그런거다.. 설정 잘 돼있는거 가지구 착각하고, 삽질하지 말자.