[MySQL] time out(wait_timeout) 계산과 설정
컨텐츠 정보
- 19,435 조회
- 6 추천
- 목록
본문
[MySQL] time out(wait_timeout) 계산과 설정
- 작성자 : 김칠봉 <san2(at)linuxchannel.net>
- 작성일 : 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으로 봄).
문제가 되는 경우'
(
<FONT FACE='굴림체'>
동시 접속 connection 수(Y)
|
+ Max connection ------+------+------
| '| |
| ' | |
| ' | |
| . | |
| . | |
| . | |
| . . . . . ' | |
| | |
+----------------------+------+-------> X sec
N sec
</FONT>
* 시간이 지남에 따라 급격한 기울기(오목한 분포)
)
잘 튜닝된 경우
(
<FONT FACE='굴림체'>
동시 접속 connection 수(Y)
|
+ Max connection ------+------+------
| . ' ' |
| . |
| ' |
| ' |
| . |
| . |
| . . . . . ' |
| |
+----------------------+------+-------> X sec
N sec
</FONT>
* 시간이 지남에 따라 완만한 기울기(볼록한 분포)
)
가정
(
<FONT FACE='굴림체'>
동시 접속 connection 수(Y)
|
+ Max connection -------------+------
| | . '|
| | . ' |
| | . ' |
| + ' |
| . ' | |
| . ' | |
| . ' | |
| | |
+--------------+--------------+-------> X sec
(N+1)/2 N sec
</FONT>
* 커넥션 간격을 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
); // 아주 바쁜 서버로 과거에 이미 한계점에 초과한 경우
<FONT FACE='굴림체'>
+------+------+------+---------+-----------+-------+-------+
| 구분 | 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 |
+------+------+------+---------+-----------+-------+-------+
</FONT>
* 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 값을
선택하면 됩니다.
<FONT FACE='굴림체'>
+------+----------------------------------+
| 구분 | 최종 임계 life time (LPT) |
|------+----------------------------------|
| ex1 | 66 |
|------+----------------------------------|
| ex2 | 42 |
|------+----------------------------------|
| ex3 | 19 |
+------+----------------------------------+
</FONT>
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 고려)
<FONT FACE='굴림체'>
+------+-------+----------+--------------------+---------+
| 구분 | LPT | cpq(예) | wait_timeout | AVG |
|------+-------+----------+--------------------+---------|
| ex1 | 66 | 5 | 53 | 59 |
|------+-------+----------+--------------------+---------|
| ex2 | 42 | 7 | 36 | 39 |
|------+-------+----------+--------------------+---------|
| ex3 | 19 | 9 | 17 | 18 |
+------+-------+----------+--------------------+---------+
</FONT>
이 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 ....
제가 냄새에 민감해서 천만다행..
EOF
- 작성자 : 김칠봉 <san2(at)linuxchannel.net>
- 작성일 : 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으로 봄).
문제가 되는 경우'
(
<FONT FACE='굴림체'>
동시 접속 connection 수(Y)
|
+ Max connection ------+------+------
| '| |
| ' | |
| ' | |
| . | |
| . | |
| . | |
| . . . . . ' | |
| | |
+----------------------+------+-------> X sec
N sec
</FONT>
* 시간이 지남에 따라 급격한 기울기(오목한 분포)
)
잘 튜닝된 경우
(
<FONT FACE='굴림체'>
동시 접속 connection 수(Y)
|
+ Max connection ------+------+------
| . ' ' |
| . |
| ' |
| ' |
| . |
| . |
| . . . . . ' |
| |
+----------------------+------+-------> X sec
N sec
</FONT>
* 시간이 지남에 따라 완만한 기울기(볼록한 분포)
)
가정
(
<FONT FACE='굴림체'>
동시 접속 connection 수(Y)
|
+ Max connection -------------+------
| | . '|
| | . ' |
| | . ' |
| + ' |
| . ' | |
| . ' | |
| . ' | |
| | |
+--------------+--------------+-------> X sec
(N+1)/2 N sec
</FONT>
* 커넥션 간격을 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
); // 아주 바쁜 서버로 과거에 이미 한계점에 초과한 경우
<FONT FACE='굴림체'>
+------+------+------+---------+-----------+-------+-------+
| 구분 | 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 |
+------+------+------+---------+-----------+-------+-------+
</FONT>
* 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 값을
선택하면 됩니다.
<FONT FACE='굴림체'>
+------+----------------------------------+
| 구분 | 최종 임계 life time (LPT) |
|------+----------------------------------|
| ex1 | 66 |
|------+----------------------------------|
| ex2 | 42 |
|------+----------------------------------|
| ex3 | 19 |
+------+----------------------------------+
</FONT>
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 고려)
<FONT FACE='굴림체'>
+------+-------+----------+--------------------+---------+
| 구분 | LPT | cpq(예) | wait_timeout | AVG |
|------+-------+----------+--------------------+---------|
| ex1 | 66 | 5 | 53 | 59 |
|------+-------+----------+--------------------+---------|
| ex2 | 42 | 7 | 36 | 39 |
|------+-------+----------+--------------------+---------|
| ex3 | 19 | 9 | 17 | 18 |
+------+-------+----------+--------------------+---------+
</FONT>
이 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 ....
제가 냄새에 민감해서 천만다행..
EOF
관련자료
-
링크
댓글 0
등록된 댓글이 없습니다.
