( MySql ) show processlist; sleep

show processlist; 

마엘코

출처 : http://www.mysqlkorea.com/gnuboard4/bbs/board.php?bo_table=community_04&wr_id=705

메일 서버에서 DB 상태를 확인해 보니.   Sleep인 상태가 많이 존재 합니다.   도대체 무슨 이유에서 sleep 상태가 생기는지 모르겠습니다.   어떡해 해야지만 sleep 상태를 없앨 수 있을까요?   sleep 상태가 생기는 이유도 알고 싶습니다.

까칠맨 interactive_timeout  wait_timeout  두가지 값을 함 수정해 보세요 ^^

남동훈 mysql> show processlist;  sleep 상태 : 클라이언트에서 연결을 하고 연결을 종료 하지 않았거나, 느린 쿼리가 실행되어 역시 연결 종료를 하지 못했을 경우에 발생하는 것입니다.

강창기 답변 진심으로 감사 드립니다.  만약 클라이언트에서 종료를 제대로 하지 않은 상태라면 어떡해 종료를 해야 하나요? kill로 죽이면 되나요??  마지막으로 쿼리를 날릴때 어떤 쿼리가 느리게 실행 되는지 알기 위해서는 EXPLAIN을 사용하면 되는지요?

모서리 강제로 mysql 데몬을 죽일 때 killall -9 mysqld, killall -9 mysqld_safe 로 죽이시면 되구요  또 어떤 쿼리가 늦게 실행되는 지 잡아 낼려면 my.cnf 파일에 아래와 같이 설정해 주시면  스로우 쿼리 로그가 쌓이게 됩니다.  # slow query  long_query_time = 2 <---- 2초 이상 걸린 쿼리  log-slow-queries ( = 경로 )  경로를 적어주지 않으면 기본 데이타 디렉토리에 쌓이게 됩니다.

강창기 빠른 답변 감사 드립니다.  mysql 에 대해서 모르는 사항이 많아 염치 무릎쓰고 한가지 더 질문 하겠습니다.  interactive_timeout  wait_timeout  이 두가지를 설정해 보시라고 하셨는데요.  설정을 어떡해 바꿔야 하는지요?

미끄리 MySQL의 time out을 물으시는 것 같네요  MySQL 서버의 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 을 따로 튜닝하여 설정해줘야 합니다.  [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 파일에 기록됩니다.  [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) 정도로 설정하는 것을 권장합니다.  [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 %에 가까울수록 좋습니다. 이 의미는 클라이언트 프로그램에서모두 정상적으로 종료했다는 의미입니다.

강창기 미끄리님 답변 진심으로 감사 드립니다.  제가 이해하기에는 좀 어려운 감이 없이 않아 있지만 반복해서 읽어 보도록 하겠습니다.  죄송하지만 한 가지를 더 여쭤보고 싶습니다.  현재 저희 메일 서버 DB my.cnf 파일을 확인해 보니깐  interactive_timeout  wait_timeout  값을 설정해 놓은 부분이 없는거 같습니다.  그럼 따로 추가를 해주면 되는지요?  참고로 mysql version 5.0 이며 이노디비를 사용하고 있습니다.

모서리 vi에서 "/timeout" 으로 검색해보시고 없을 수도있으니 만약 보이지 않는다면 추가해 넣으세요.  그렇지 않으면 디폴트값으로 적용되니까요  [connect_timeout/interactive_timeout/wait_timeout]  connect_timeout은 MySQL이 클라이언트로부터 접속 요청을 받는 경우 몇 초까지 기다릴지를 설정하는 변수이다. 기본 값은 5초이며 일반적으로 수정할 필요는 없다. Interactive_timeout은 ‘mysql>’과 같은 콘솔이나 터미널 상에서의 클라이언트 접속을 말한다. 기본 값으로 8시간이 잡혀 있으나 1시간 정도로 낮추는 것이 좋다. 이런 접속은 그다지 빈번하지 않으며 작업을 위해 접속하는 경우가 많기에 따로 설정하지 않아도 큰 영향은 없다. 가장 중요한 것은 wait_ timeout으로 wait_timeout은 접속한 후 쿼리가 들어올 때까지 기다리는 시간이다. 접속이 많은 데이터베이스 시스템에서는 이 값을 낮춰 sleep 상태로 커넥션만 유지하고 있는 클라이언트들의 접속을 빠르게 끊어줘 동시 접속을 낮추는 것으로 전체 성능을 크게 향상시킬 수 있다.  하지만 주의할 점은 너무 낮추게 되면 실제로 서비스를 하기도 전에 끊어진다든지 지나치게 잦은 커넥션이 발생한다는 것이다. 일반적으로 15~20 사이의 값이 적당하며 SHOW STATUS를 통해 aborted_client가 가장 적게 발생하도록 값을 맞춰야 한다. Aborted client는 2% 아래인 것이 바람직하며 물론 없는 것이 가장 좋은 상태이다.

강창기 답변 진심으로 감사 드립니다.  이제 슬슬 감이 오는거 같습니다. ^^  답글 달아 주신 분들게 진심으로 한번 더 감사 드립니다.