Data Science Lab

MySQL/MariaDB InnoDB(Storage) row lock 모니터링

·      Version : MySQL 5.7, Docker

MySQL/MariaDB에서 사용하는 InnoDB 스토리지 엔진에서  row lock 정보는 스토리지 엔진 테이블에서 발생한 행 단위의 lock 발생 횟수와 lock 대기 시간등을 나타낸다. InnoDB row lock 모니터링은 아래 스크립트를 실행하여 확인 할 수 있다.

InnoDB의 row lock 수는 lock을 획득한 수가 아닌 lock을 얻기 위해 대기하는 횟수로 이 지표가 평소보다 급증한다면 쿼리 대기가 발생(락 경합)하는 것으로 쿼리 튜닝이 필요하다.

2018-05-18 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

MySQL, InnoDB row lock, 이노디비, 스토리지 잠금, Row lock, 행락, MySQL Lock, lock wait, 잠금, 잠금 대기, DBA, 쿼리 튜닝, DB모니터링

MySQL/MariaDB InnoDB(Storage) buffer pool 모니터링

·      Version : MySQL 5.7, Docker

MySQL/MariaDB에서는 다양한 스토리지 엔진을 지원하며 각 스토리지마다 특성 또한 다르다. 각 스토리지 엔진에 대한 특성은 아래 링크를 참고 한다.

·       Comparison of MySQL database engines : https://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines

이번 포스트에서는 일반적으로 OLTP 서비스에 많이 사용하는 InnoDB 스토리지 엔진에서 buffer pool(버퍼풀)과 관련한 모니터링 방법에 대해서 알아본다. 아래 스크립트를 실행 하면 InnoDB의 buffer pool에 대한 값을 확인할 수 있다.

InnoDB에서 버퍼풀의 크기는 MySQL 서버 변수에서 매우 중요한 부분이다. 너무 크게 설정하면 다른곳에서 사용할 메모리가 줄어들고, 너무 작게 설정하면 빈번한 디스크로의 요청이 발생하여 IO 부담을 가중시킬 수 있다. 일반 적으로 DB서버의 경우 DB외에 다른 서비스와 함께 사용하지 않으므로 OS가 필요한 공간( + 여유 공간) + MySQL 필요 공간(예상 커넥션 수, 스레드 수, 소트 메모리 등등)을 제외하고 버퍼풀로 사용하는경우가 많다.

버퍼풀 모니터링에서 중요하게 살펴보아야 하는 부분이Innodb_buffer_pool_pages_flushed 항목이다. 이는 버퍼풀의 더티 데이터를 디스크에 쓰는 과정으로 과도한 flushed 는 디스크에 부담을 줄 수 있다. 또한 한번에 플러시 되는 크기가 디스크 IO 성능에 비례하여 너무 클경우 디스크에서 쓰기 지연 현상이 발생할 수 있으므로 디스크의 성능과 함께 모니터링 해야한다.

Innodb_buffer_pool_reads의 수치가 높은경우 버퍼풀의 데이터를 활용하지 못하는것으로 디스크로 부터 직접 데이터를 읽어야 하기 때문에 디스크의 성능 저하가 발생 할 수 있다. 일반 적으로 버퍼풀의 크기가 작은 경우 버퍼풀에서 데이터를 오랫동안 캐싱할 수 없어 빈번한 IO가 발생할 수 있다.

[참고자료]

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

2018-05-15 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

MySQL, InnoDB bufferpool, 버퍼풀, 이노디비, MySQL Storage, 스토리지 엔진, buffer pool 모니터링, DBA, MySQL 모니터링

MySQL/MariaDB Threads 모니터링

·      Version : MySQL 5.7, Docker

MySQL/MariaDB에서 Threads 모니터링하는 방법에 대해서 알아본다.  MySQL 서버는 기본적으로 하나의 클라이언트가 MySQL 서버에 접속할 떄마다  해당 클라이언트에 종속적인 스레드를 생성하고 해당 커넥션에서의 스레드 역할(쿼리 작업)이 끝나면 스레드를 제거한다. 클라이언트가 접속할 때마다 스레드를 할당하고 제거하는 작업은 MySQL 서버 시스템 입장에서 보면 많은 리소스 낭비 및 성능 저하를 발생시키기 때문에 모니터링은 매우 중요한다.  아래 스크립트를 실행하면 스레드와 관련된 지표를 확인할 수 있다.

MySQL서버에서는 스레드 할당으로 인한 리소스 낭비 및 성능 저하를 막기 위해서 특정 개수의 스레드를 메모리에 캐싱(thread pool)해서 사용한다. 스레드 캐시를 활용하면 클라이언트가 MySQL서버에 접속하여 스레드 할당이 필요할 때 스레드 캐시에 대기중인 스레드를 할당한다. (대기중인 스레드가 없다면 새로운 스레드를 생성하여 할당한다.) 클라이언트 작업이 종료되면 스레드는 캐시로 반환되거나 삭제 된다.

스레드 캐시를 활용하기 위해 캐시 사이즈르 크게 할당한다면 메모리 낭비 및 예기치 못한 leak이 발생할 수도 있다. 반면 너무 적게 설정한다면 MySQL 서버가 스레드 캐시를 잘 활용하지 못하므로 신규 스레드를 생성하는데 리소스 낭비가 발생한다. 아래 공식은 스레드 캐시 사용하지 못한 비율이다.

운영중인 서버를 모니터링 하면서(특히 가장 바쁜 시간과 가장 한가한 시간에 대한 편차가 크니 주한다.) 적절한 임계치를 찾아서 설정 할 수 있도록 한다.

[참고자료]

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_thread_handling

2018-05-07 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

MySQL, thread monitoring, connection thread, thread pool, connection pool, 스레드 모니터링, MySQL 스레드, 스레드 캐시, 스레드 사이즈

MySQL/MariaDB Temp table and Temp file 모니터링

·      Version : MySQL 5.7, Docker

MySQL/MariaDB에서 Temp table 및 Temp file 모니터링하는 방법에 대해서 알아본다. 데이터베이스 서브 시스템에서 상대적으로 느린 부분이 디스크이다. 임시 테이블 및 임시 파일이 디스크에 기록될때 데이터베이스의 성능 저하가 발생할 수 있기기 때문에 지속적인 모니터링을 통해서 디스크에 생성되는 임시 테이블 및 파일이 높지 않은지 살펴보아야 한다.

아래 스크립트는 temp table 및 temp file의 지표를 나타낸다.

위 지표에서 성능 저하가 발생하는 부분은 Created_tmp_disk_tables 항목이다. 이 항목의 수치가 높다면 현재 쿼리를 처리하기 위해 디스크를 많이 사용하고 있다는 뜻이다. 일반적으로 아래와 같은 쿼리는 임시 테이블을 생성한다.

·       OEDER BY 및 GROUP BY에 명시된 컬림이 다른 쿼리

·       한 개의 쿼리에 DISTINCT와 ORDER BY가 동시에 존재하는 경우

·       DISTINCT가 인데스러 처리되지 못하는 쿼리

·       UNION ALL이 사용된 쿼리

·       실행계획에 select_type이 UNION RESILT가 나타나는 경우

·       실행계획에 select_type이 DERIVDE 인 쿼리

이 외에도 임시 테이블에 저장되는 레코드의 크기가 tmp_table_size 또는 max_heap_table_size보다 큰 경우 디스크에 임시 테이블을 생성하여 저장한다. 디스크 사용을 줄이기 위해 tmp_table_size 및 max_heap_table_size의 시스템 변수 값을 증가시키면 MySQL 서버가 사용할 수 있는 메모리 공간이 줄어들기 때문에 주의해야 한다. 아래 스크립트는 tmp_table_size 및 max_heap_table_size를 확인하는 스크립트 이다.

SELECT 절에서 사용되는 컬럼에 따라서도 디스크에 임시 테이블이 생성되는데, TEXT, BLOB 형식의 컬럼이 포함되는 경우 MEMORY 스토리지 엔진이 이 데이터 타입을 지원하지 않아 디스크에 임시 테이블을 생성한다.

[참고자료]

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

2018-05-02 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

MySQL, temp table, temp file 모니터링, 임시 테이블, 임시 파일 모니터링, tmp_table_size, max_heap_table_size, created_tmp_disk_tables, created_tmp_files, created_tmp_tables