MySQL의 IO 측정

 

 

MySQL의 IO 측정

SQL 튜닝을 위해 가장 눈여겨 볼 항목중 하나는 IO(Input, Output)입니다.

IO는 데이터를 디스크나 메모리에서 읽거나 쓰는 작업을 뜻합니다.

 

데이터베이스의 기본 기능은 데이터를 저장하는 것입니다.

데이터를 관리하기 위해 수 많은 데이터를 쉴새없이 저장(Input)합니다.

또한 데이터베이스에 저장된 데이터를 활용하기 위해, 수 없이 수 많은 데이터를 읽어올려(Output) 조회하게 됩니다.

이처럼 데이터베이스는 데이터를 읽고, 쓰는 작업을 주로 합니다. 이 과정에서 IO 횟수(데이터를 읽거나 저장한 횟수)에 따라 SQL의 성능이 달라집니다.

 

오라클이나 PostgreSQL은 SQL 실행시 발생한 IO 수치를 수집하거나 측정하는 것이 비교적 수월합니다.

반면에 MySQL은 이와 같은 IO 수치 측정이 번거롭게 되어 있습니다.

또한, 글로벌하게 측정이 되므로 여러 명이 동시에 사용 중인 DB에서는 측정이 불가능합니다.

아마도 스토리지 엔진과 DB 엔진이 나누어져 있는 아키텍쳐 때문인건 아닌지 생각해 봅니다.

 

어쨋든, MySQL도 IO를 측정할 수 있는 방법이 있으니 참고하시기 바랍니다.

MySQL에는 SHOW STATUS로 innodb_buffer_pool_read_requests를 확인해 IO 수치를 확인할 수 있습니다.

SQL 실행 전 후로 해당 수치를 측정해 두 수치의 차이를 구하면, 해당 SQL의 IO 수치가 됩니다.

(이미 말했듯이, 현재 DB를 혼자 사용하고 있어야 정확한 수치가 됩니다.)

 

아래는 특정 일자의 T_ORD_BIG 데이터를 조회하는 SQL의 IO를 측정하는 과정입니다.

현재 T_ORD_BIG에는 PK외에 인덱스가 없습니다. 그러므로 131,561이라는 높은 수치의 IO가 발생되고 있습니다.

실제 실행 시간도 5.9초 정도 걸립니다.

SHOW STATUS LIKE 'innodb_buffer_pool_read_requests'; -- 122952720

SELECT T1.CUS_ID ,COUNT(*) CNT
FROM   mytundb.T_ORD_BIG T1 
WHERE  T1.ORD_DT = STR_TO_DATE('20170103','%Y%m%d')
GROUP BY T1.CUS_ID;

SHOW STATUS LIKE 'innodb_buffer_pool_read_requests'; -- 123084281

SELECT 123084281 - 122952720 FROM DUAL; -- 131561

 

이제 아래와 같이 인덱스를 만들어 봅니다.

CREATE INDEX T_ORD_BIG_X01 ON mytundb.T_ORD_BIG(ORD_DT);

 

인덱스를 만들었으므로 앞에서 테스트한 SQL은 빨라질 것입니다. 빨라진다는 것은 IO 수치가 줄어든다는 것과 같습니다.

 

다시 IO수치를 측정해보면 다음과 같습니다.

IO 수치가 55889로 이전의 절반으로 줄어든 것을 알 수 있습니다. 실행시간 또한 감소되었습니다.

SHOW STATUS LIKE 'innodb_buffer_pool_read_requests'; -- 123220482

SELECT T1.CUS_ID ,COUNT(*) CNT
FROM   mytundb.T_ORD_BIG T1 
WHERE  T1.ORD_DT = STR_TO_DATE('20170103','%Y%m%d')
GROUP BY T1.CUS_ID;

SHOW STATUS LIKE 'innodb_buffer_pool_read_requests'; -- 123276371

SELECT 123276371 - 123220482 FROM DUAL; -- 55889

 

여러 명이 사용 중인 DB에서는 제대로 측정할 수 없는 단점과,

측정 방법이 불편한다는 단점이 있지만,

MySQL도 이와 같은 방법으로 IO를 측정할 수 있다는 점을 참고하시면 될거 같습니다.

 

---

[StartUP Tuning]

• SQL 입문을 넘어선 MySQL을 활용한 SQL 튜닝 입문 교육
• 2024년 5월 주말 오프라인 교육 과정 모집중: https://cafe.naver.com/dbian/7717

StartUP Tuning - MySQL/MariaDB를 활용한 SQL 튜닝 입문

 

[StartUP SQL]

• 데이터 분석가, 개발자, 기획자를 위한 SQL 입문 교육
• 교육용 슬라이드 전체 무료 오픈: https://sweetquant.tistory.com/497

StartUP SQL 소개 및 목차