[MySQL] Timeout 종류

MySQL에는 여러가지 Timeout 종류가 있습니다. 세션에서, 서버에서 혹은 접속시도중이나 기타 여러가지 상황에서의 Timout값이 존재합니다. 각 파라미터마다 특성이 있는데 이 관계를 잘 이해하고 설정해야 원치않는 상황에서 갑자기 세션이 종료되는 상황을 방지하거나, 필요없이 오래 접속되어 있는 세션을 종료시킬 수 있습니다. 이 Timeout 파라미터의 종류들에 대해 알아보겠습니다.

 

* connect_timeout :

mysqld 서버가 접속실패로 응답하기 전에 연결 패킷을 기다리는 시간( 초). 기본값은 10 초입니다.

 connect_timeout값을 늘리면 클라이언트에서 Lost Connection 오류가 자주 발생하는 경우 도움이 될 수 있습니다.

Lost connection to MySQL server at 'XXX', system error: errno.

상당히 먼 거리(유럽이나 미국쪽)로 접속을 시도할 경우 접속 시도 시간이 오래 걸릴 수 있습니다. 이때 이 시간을 늘려주면 도움이 될 수 있습니다.

 

* lock_wait_timeout :

이 변수는 메타 데이터 잠금 획득 시도에 대한 제한 시간 (초)을 지정합니다. 허용되는 값의 범위는 1-31536000 (1 년)입니다. 기본값은 31536000입니다.

 

이 timeout는 메타 데이터 잠금을 사용하는 모든 명령문에 적용됩니다.

여기에는 테이블, 뷰, 저장 프로 시저 및 저장 함수에 대한 DML 및 DDL 작업과 LOCK TABLES, FLUSH TABLES WITH READ LOCK 및 HANDLER 문이 포함됩니다.

 

GRANT 또는 REVOKE 문 또는 테이블 로깅 문으로 수정된 권한 부여 테이블과 같이 mysql 데이터베이스의 시스템 테이블에 대한 암시적 액세스에는 이 시간 제한이 적용되지 않습니다.

시간 종료는 SELECT 또는 UPDATE와 같이 직접 액세스 한 시스템 테이블에 적용됩니다.

 

timeout 값은 각 메타 데이터 잠금 시도에 대해 별도로 적용됩니다.

주어진 명령문에 둘 이상의 잠금이 필요할 수 있으므로 시간 종료 오류를 보고하기 전에 명령문이 lock_wait_timeout 값보다 길게 차단 될 수 있습니다.

잠금 시간 종료가 발생하면 ER_LOCK_WAIT_TIMEOUT이 보고됩니다. 

lock_wait_timeout은 지연된 삽입에는 적용되지 않으며 항상 1 년의 시간 제한으로 실행됩니다.

 

* net_read_timeout : 

네트워크 접속을 통해서 클라이언트가 서버에서 데이터를 받을 때 abort(중지)될때까지 기다리는 시간입니다. 여기에서 네트워크 접속이란 TCP/IP 접속을 얘기합니다. Unix socket file(로컬 서버에서의 접속), named pipe, 공유된 메모리를 통해 접속된 연결은 아닙니다.

서버가 클라이언트로부터 읽혀질때 net_read_timeout값은 abort(중지)를 제어합니다.

서버가 클라이언트로부터 쓰여질때 net_write_timeout값은 abort(중지)를 제어합니다.(아래 설명)

기본값은 30초입니다. 

Lost connection to MySQL server at 'XXX', system error: errno. 에러 발생시 값을 높여서 에러 발생을 방지합니다.

 

* net_write_timeout :

네트워크 접속을 통해서 클라이언트가 서버에 데이터 쓸 때 abort(중지)될때까지 기다리는 시간입니다. 여기에서 네트워크 접속이란 TCP/IP 접속을 얘기합니다. Unix socket file(로컬 서버에서의 접속), named pipe, 공유된 메모리를 통해 접속된 연결은 아닙니다.

기본값은 60초 입니다. 마찬가지로 Lost connection to MySQL server at 'XXX', system error: errno. 에러 발생시 값을 높여서 에러 발생을 방지합니다.

 

▶︎ net read write timeout 튜닝 가이드

대량의 데이터를 조회하거나 쓸 경우, 실행 중간에 끊기는 현상이 발생할 수도 있습니다. MySQL에서는 대량의 데이터를 네트워크 상에 쪼개서 읽거나 쓰게 되는데, 이때 MySQL 에서 딱히 조정할 수 있는 파라미터는 없는것으로 알고 있습니다. 네트워크 전송중 쓰거나 읽을 때 여러번에 걸쳐서 전송되게 되는데 이때 쿼리툴에서 중간중간 대기하다가 접속이 종료된다면, net_ㅇread_timeout 혹은 net_write_timeout을 늘려서 환경에 맞는 값을 찾아내야 합니다.

 

* wait_timeout :

비 대화식(non interactive) 연결에서 서버가 활동을 닫기 전에 서버가 대기하는 시간 (초)입니다.

스레드 시작시 wait_timeout 값은 클라이언트 유형에 따라 전역 wait_timeout 값 또는 전역 interactive_timeout 값에서 초기화됩니다(CLIENT_INTERACTIVE 연결 옵션으로 mysql_real_connect()에 정의 됨).

interactive_timeout도 참조합니다.(아래 설명)

기본값은 28800초 입니다.

 

* interactive_timeout

서버가 대화식 연결을 닫기 전에 활동을 기다리는 시간 (초)입니다. 대화 형 클라이언트는 mysql_real_connect ()에 CLIENT_INTERACTIVE 옵션을 사용하는 클라이언트로 정의됩니다. wait_timeout도 참조하십시오.

기본값은 28800초 입니다.

 

* rpl_semi_sync_master_timeout

시간 초과 및 비동기 복제로 되돌리기 전에 마스터가 슬레이브로부터 승인을 받기 위해 커밋을 기다리는 시간을 제어하는 값 (밀리 초)입니다.

기본값은 10000 (10 seconds)입니다.

 

* rpl_stop_slave_timeout

Default Value : 31536000

이 변수를 설정하여 시간 초과 전에 STOP SLAVE가 대기하는 시간 (초)을 제어 할 수 있습니다.

이는 슬레이브에 대한 다른 클라이언트 연결을 사용하여 STOP SLAVE와 다른 슬레이브 SQL 문 사이의 교착 상태를 피하기 위해 사용할 수 있습니다.

rpl_stop_slave_timeout의 최대 값과 기본값은 31536000 초 (1 년)입니다. 이 변수에 대한 변경 사항은 후속 STOP SLAVE 문에 적용됩니다.

이 변수는 STOP SLAVE 문을 발행하는 클라이언트에만 영향을줍니다. 시간 종료에 도달하면 발행 클라이언트는 명령 실행이 완료되지 않았다는 오류 메시지를 리턴합니다.

그런 다음 클라이언트는 슬레이브 스레드가 중지되기를 기다리는 것을 중지하지만 슬레이브 스레드는 계속 중지를 시도하고 STOP SLAVE 명령은 계속 유효합니다.

슬레이브 스레드가 더 이상 사용되지 않으면 STOP SLAVE 문이 실행되고 슬레이브가 중지됩니다.

 

* slave_net_timeout

Default Value : 60

Minimum Value : 1

슬레이브가 연결이 끊어진 것으로 간주하고 읽기를 중단 한 후 다시 연결하려고 시도하기 전에 마스터로부터 더 많은 데이터 또는 하트 비트 신호를 기다리는 시간 (초)입니다.

이 변수를 설정해도 즉각적인 효과는 없습니다. 변수 상태는 모든 후속 START SLAVE 명령에 적용됩니다. 

기본값은 60초입니다.

 

* innodb_flush_log_at_timeout

N 초마다 로그를 작성하고 플러시합니다. innodb_flush_log_at_timeout은 플러시를 줄이고 이진 로그 그룹 커밋의 성능에 영향을 미치지 않도록 플러시 사이의 시간 초과 기간을 늘릴 수 있습니다.

innodb_flush_log_at_timeout의 기본 설정은 초당 한 번입니다.

기본값은 1초입니다.

 

* innodb_lock_wait_timeout

Default Value : 50

InnoDB 트랜잭션이 롤백하기 전에 행 잠금을 기다리는 시간(초)입니다. 기본값은 50 초입니다.

다른 InnoDB 트랜잭션에 의해 잠긴 행에 액세스하려고 시도하는 트랜잭션은 다음 오류를 발행하기 전에 최대 몇 초 동안 행에 대한 쓰기 액세스를 기다립니다.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

잠금 대기 시간 종료가 발생하면 현재 명령문이 롤백됩니다 (전체 트랜잭션이 아님).

전체 트랜잭션을 롤백하려면 --innodb-rollback-on-timeout 옵션을 사용하여 서버를 시작합니다.

innodb_lock_wait_timeout은 InnoDB 행 잠금에만 적용됩니다. MySQL 테이블 잠금은 InnoDB 내에서 발생하지 않으며이 시간 제한은 테이블 잠금 대기에 적용되지 않습니다.

 

* innodb_rollback_on_timeout

InnoDB는 기본적으로 트랜잭션 시간 종료시 마지막 명령문만 롤백합니다. --innodb-rollback-on-timeout이 지정되면 트랜잭션 시간 초과로 인해 InnoDB가 전체 트랜잭션을 중단하고 롤백합니다.

기본값은 off입니다.

 

* delayed_insert_timeout

지원되지 않습니다. 공식문서에서 deprecated된것을 확인할 수 있습니다.

insert시 delay될 경우 대기하는 시간

 

* thread_pool_idle_timeout

마리아DB파라미터 입니다. 기본값은 60초입니다.

idle 상태인 쓰레드를 정리하기 전에 대기하는 시간(초)입니다.