MySQL/MariaDB Table lock 모니터링

MySQL/MariaDB Table lock 모니터링

 

·      Version : MySQL 5.7, Docker

 

MySQL/MariaDB에서 Table lock 모니터링하는 방법에 대해서 알아본다. Table lock(테이블락) 모니터링은 테이블에 락이 발생한 횟수를 나탄내다. 아래 명령어를 실행하면 테이블락과 관련된 성능 지표를 보여준다.

show status where variable_name like ‘table_locks%’

 

 

Variable Name	Commets
Table_locks_immediate	즉식 획득한 테이블 락 횟수
Table_locks_waited	테이블 락을 즉시 획득하지 못하고 대기한 횟수

 

MySQL에서 테이블락 관련해서 모니터링 할때 주의해야할 부분이 있다. MySQL의 경우 다양한 스토리지 엔진이 존재한다. 각 스토리지엔진은 다양한 락 레빌을 지원하며 동작하는 방식이 다르기 때문에 실제 위에서 보여지는 수치와 실제 스토리지 엔진 레벨에서 발생하는 락의 수치와는 다를 수 있다.

예를들어 MyISAM이나 Memory 스토리지 엔진은 테이블 레벨 락(table level lock)만 지원하지만 InnoDB 스토리지 엔진은 행단뤼 락(Row level lock)도 가능하다. InnoDB 스토리지 엔진은 UPDATE, DELETE 시 쿼리에 의해 갱신되는 행만 ROW 락이 발생시키며 경합을 최소한다. MySQL 엔진은 테이블락 획득을 요청하지만 실제 InnoDB엔진은 Row lock으로 다운그레이드하여 처리하는데, 이를 MySQL 엔진에게 Row lock으로 진행 한다는것을 레포팅 하지 않기 때문에 MySQL에서는 테이블락으로 간주되어 표시된다. 따라서 InnoDB스토리지 엔젠을 사용하는 경우 Table_locks_immedate 수치가 테이블락을 획득한 절대적인 수치가 아니라는 점을 명시해야한다.

 

만약 Table_locks_waited 수치가 높다면 락을 획득하는데 지연이 있다는 뜻이므로 이 수치가 증가한다면 이와 관련된 쿼리가 실행되는데 걸리는 시간이 증가하는 것으로 쿼리 튜닝이 필요하다.   Lock waited 대기 비율은 아래 공식으로 구할 수 있다.

(%) = Table_locks_immediate / (Table_locks_waited + Table_locks)immediate) * 100

 

항상 모니터링 관련해서 언급할때 강조하는 내용이지만 모니터링 할때 각종 지표에 대한 임계치는 운영하는 서비스에 대한 서버 유형, 하드웨어 성능, 쿼리 패턴등을 고려해서 적정 임계치를 정해야 한다. 따라서 지속적인 모니터링과 리뷰 과정을 통해서 적절한 임계치를 미리 파악할 수 있도록 하는것이 중요하다.

 

 

 [참고자료]

·       https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Table_locks_immediate

·       https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Table_locks_waited

 

 

2018-04-30 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, Table lock 모니터링, 락 모니터링, 테이블 락, Table_locks_immediate, Table_locks_waited,