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,  



MySQL/MariaDB Sort 모니터링

 

·      Version : MySQL 5.7, Docker

 

MySQL 뿐만 아니라 다른 제품의 RDB에서도 정렬(Sort) 발생할 시스템 리소스가 추가적으로 발생하기 때문에 모니터링이 필요하다. Select 모니터링시 테이블 스캔이 발생하는지 또는 인덱스 사용하는지에 대한 부분에 대해서 살펴 보았다면 이번에는 정렬과정에서 발생하는 비용에 대해서 살펴본다.

 

·       MySQL/MariaDB Select 모니터링  : http://sqlmvp.kr/221260775725

 

아래 스크립트를 실행 하면 Sort 관련된 지표를 확인할 있다.

show status where variable_name like ‘sort%’

 


 

지표가 의미하는 내용은 아래 표를 참고 한다.

Variable Name

Comment

Sort_merge_passes

멀티 머지 처리 횟수

Sort_range

Index Range Scan결과에 대한 정렬 작업 횟수

Sort_rows

정렬한 전체 레코드

Sort_scan

Full table scan 결과에 대한 정렬 작업 횟수

 

일반적으로 ORDER BY, GROUP BY 사용하면 정렬과정이 필요하다. 정렬을 하기 위해서는 우선 모든 레코드를 메모리에 적재하고 정렬을 시도한다. 이때 데이터 양이 sort_buffer_size 용량 보다 크다면 모든 데이터를 메모리에 적재할 없으므로  MySQL 엔진은 정렬해야 레코드를 여러 조각으로 나누어서 처리한다. 이때 중간 저장을 위한 임시 저장소로 디스크를 사용한다.

 

Sort_buffer_size 확인은 아래 스크립트로 확인할 있다.

show variables where variable_name like ‘sort%’

 


 

정렬시 발생하는 과정을 살펴보면 아래 그림과 같다. 메모리의 SORT BUFFER에서 정렬을 수행하고 임시로 디스크(TEMP FILE) 저장한다. 그리고 모든 레코드에 대해서 앞의 과정을 반복한다. 버퍼크기 만큼 정렬되 레코드를 다시 병합하면서 정렬을 수행한다.


Sort 과정에서 데이터양이 많은 경우 디스크에서 임시 파일을 사용하게 되는데 디스크의 속도에 따라 처리 과정이 영향을 받는다. 따라서 sort_merge_passes 지표가 높게 나타난다면 sort_buffer_size 조절하여 디스크의 사용을 줄일 있는 방안을 검토해야 한다.

 

 

 [참고자료]

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_sort_buffer_size

 

 

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

 

MySQL, sort 모니터링, sort_buffer_size, sort merge, sort_merge_passes, 정렬 메모리,



MySQL/MariaDB Select 모니터링

 

·      Version : MySQL 5.7, Docker

 

MySQL 뿐만 아니라 대부분의 OLTP 데이터베이스에서는 SELECT 성능이 매우 중요하다. 일반적으로 관계형 데이터베이스에서는 JOIN 많이 사용하기 때문에 SELECT 따른 의도하지 않은 성능 문제나 잠금 문제가 발생할 있기 때문에 모니터링에 우선순위를 높여야 하며 최우선으로 튜닝을 해야 하는 포인트 이기도 하다. MySQL에서 SELECT 대한 모니터링은 아래 스크립트를 실행하여 확인할 있다.

show status where variable_name like ‘select%’

 


 

항목에 대해서 어떤 의미를 가지는지 살펴보자.

Variable Name

Comment

Select_full_join

다중 테이블 조인 후행 테이블에서 전체 테이블 스캔이 발생한 횟수

Select_full_range_join

다중 테이블 조인시 제한된 범위내에서 JOIN 값을 비교한 횟수

Select_range

SELECT쿼리의 범위 조건에 만족하는 레코드를 찾기 위해 제한된 범위를 디스크에서 읽은 횟수

Select_range_check

Select range 동일한 범위에서 선행 테이블의 JOIN 칼럼 데이터가 후행 테이블의 컬럼 데이터와 같은 값인지 비교하는 횟수

Select_scan

전체 테이블 스캔이 발생한 횟수

 

일반적으로 Select_full_join 발생하면select_scan 발생하기 때문에 지표 모두 값이 증가 한다. 또한 실행계획 타입에서 ALL 표시되면 카테시안곱이 발생한것을 의미한다. OLTP서비스에서는 Full scan 피해야하는 항목으로 튜닝의 최우선 포인트가 된다.

 

기본적으로 table scan 방지하기 위해서는 인덱스가 필요하며 JOIN 또는 Where 절에 사용되는 값에 대하여 인덱스를 사용할 있도록 디자인되어야 한다.

 

 [참고자료]

https://dev.mysql.com/doc/refman/5.6/en/show-slave-status.html

 

 

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

 

MySQL, select 모니터링, select_full_join, select_range, select_scan, OLTP, MySQL 모니터링



'MySQL MariaDB' 카테고리의 다른 글

MySQL/MariaDB Table lock 모니터링  (0) 2019.03.24
MySQL/MariaDB Sort 모니터링  (0) 2019.03.24
MySQL/MariaDB Select 모니터링  (0) 2019.03.24
MySQL/MariaDB Replication 모니터링  (0) 2019.03.24
Run the MySQL with Docker  (0) 2019.03.24
MySQL/MariaDB Handler 모니터링  (0) 2019.03.24

MySQL/MariaDB Replication 모니터링

 

·      Version : MySQL 5.7, Docker

 

MySQL Replication 기본적으로 비동기 방식의 복제 매커니즘이다.  그래서 Master DB Slave DB 간에 동기화 지연이 발생하면 데이터에 데한 차이가 발생하고 서비스에 영향이 있을 수도 있다.

 

·       MySQL 복제 구축 : http://sqlmvp.kr/220383304252

·       MySQL Replication Architecture : https://www.slideshare.net/SvenSandberg/oracle-openworld-2013-hol9737-mysqlreplicationbestpractices


 

슬레이브DB 대한 복제 상태를 확인하는데 가장 많이 사용되는 명령은 show slave status 명령이다. 많은 정보가 나타나지만 기본적으로 복제 서비스 상태가 정상인지 확인하는 항목은 IO스레드 동작상태를 나타내는 Slave_IO_Running SQL 스레드 동작 상태를 나타내는 Slave_SQL_Running 이다. Yes 정상 상태이고 No이면 비정상 상태이다.

show slave status;

 


출처  :https://www.techjini.com/wp-content/uploads/2009/11/screenshot.png

 

항목에 대해 자세하게 살펴보자.

Variable Name

Comments

Slave_IO_State

IO 스레드의 동작 상태

Master_Host

슬레이브 DB 접속한 마스터 DB 호스트 주소

Master_User

슬레이브 DB 마스터 DB 접속한 계정명

Master_Port

슬레이브DB 마스터 DB 접속한 포트번호

Connect_Retry

접속이 끊어졌을때 재시도 하는 시간(), 기본값 60. CHANGE MASTER TO 구문으로 설정 가능.

Master_Log_File

IO 스레드가 현재 읽고 있는 마스터DB 바이너리 로그 파일명

Read_Master_Log_Pos

IO 스레드가 현재 읽고 있는 마스터DB바이너리 로그 파일의 로그 포지션

Relay_Log_File

SQL 스레드가 최종적으로 읽고 실행한 슬레이브 DB 릴레이 로그 파일명

Relay_Log_Pos

SQL 스레드가 최종적으로 읽고 실행한 슬레이브 DB 릴레이 로그 파일의 포지션

Relay_Master_Log_File

Relay_Log_File 값으로 나타나는 릴레이 로그 파일과 연관된 마스터DB 바이너리 로그 파일명

Salve_IO_Running

IO 스레드가 실행되고 마스터 DB 정상적으로 접속됬는지 나타냄.

Slave_SQL_Running

SQL 스레드가 작동하고 있는지 여부. Yes 또는 No 표시됨

Replicate_Do_DB

옵션(replicate_do_db) 명시된 MySQL 데이터베이스 리스트

Replicate_Ignore_DB

옵션(replicate_Ignore_db) 명시된 MySQL 데이터베이스 리스트

Replicate_Do_Table

옵션(replicate_do_Table) 명시된 MySQL 테이블 리스트

Replicate_Ignore_Table

옵션(replicate_Ignore_Table) 명시된 MySQL 테이블 리스트

Replicate_Wild_To_Table

옵션(replicate_Wild_To_Table) 명시된 MySQL 테이블 리스트

Replicate_Wild_Ignore_Table

옵션(replicate_Wild_Ignore_Table) 명시된 MySQL 테이블 리스트

Last_Errno

Last_SQL_Errno 같은

Last_Error

Last_SQL_Error 같은 .

Skip_Counter

시스템 변수인 sql_slave_skip_counter 현재 값을 나타냄

Exec_Master_Log_Pos

SQL 스레드가 읽고 처리한 현재 마스터DB 바이너리 로그 파일의 포지션으로 다음 번에 수행될 트랜잭션이나 이벤트의 시작을 표시

Relay_Log_Space

존재하는 모든 릴레이 로그 파일의 크기를 합한

Until_Condition

마스터DB 바이너리 로그 파일 또는 슬레이드 DB 릴레이 로그 파일의 특정 부분까지만 실행하고자 사용하는 옵션

Until_Log_File

SQL 스레드가 실행하다가 중단시킬 로그파일의 이름

Until_Log_Pos

SQL 스레드가 실행하다가 중단시킬 로그파일의 포지션

Master_SSL *

슬레이브DB 마스터DB 연결할 사용되는 SSL 관련된 파라메터

Second_Behind_Master

슬레이브DB Replicaton  속도가 느린지를 나타내는 . 0이면 Replication 완료된 것이며 0보다 경우 Replication 데이터가 남아있는것으로 느린것이다.(Lag 발생한것이다.)

 

 

[참고자료]

https://dev.mysql.com/doc/refman/5.6/en/show-slave-status.html

 

 

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

 

MySQL,복제 모니터터링, Replication monitoring, show slave status, DB복제, MySQL 복제


'MySQL MariaDB' 카테고리의 다른 글

MySQL/MariaDB Sort 모니터링  (0) 2019.03.24
MySQL/MariaDB Select 모니터링  (0) 2019.03.24
MySQL/MariaDB Replication 모니터링  (0) 2019.03.24
Run the MySQL with Docker  (0) 2019.03.24
MySQL/MariaDB Handler 모니터링  (0) 2019.03.24
MySQL/MariaDB Connection 모니터링  (0) 2019.03.24

+ Recent posts