MySQL Optimizer Error
옵티마이저 에러 & 힌트
RDBMS의 쿼리 최적화 기능(Optimizer)은 MySQL 서버 뿐만 아니라 모든 RDBMS에서 매우 복잡한 부분 중 하나에요. 옵티마이저는 제한된 정보를 이용해서 매우 짧은 시간내에 쿼리의 최적화된 실행 계획을 수립해야 하기 때문에, 매우 정교하면서도 빠르게 실행되어야 해요.
이런 이유로 많은 상용 RDBMS에서 아직도 옵티마이저 관련 오류가 빈번하게 발생해요. 그래서 많은 상용 RDBMS들은, 이런 문제를 회피할 수 있도록 옵티마이저 힌트 기능을 제공하고 있어요. 하지만 옵티마이저 힌트는 인덱스의 이름을 프로그래밍 코드에 명시해야 하며, RDBMS의 옵티마이저는 주어진 인덱스 힌트를 매우 신뢰하기 때문에 더 나은 실행 계획이 있다 하더라도 SQL에 주어진 힌트를 사용하는 경향이 있어요. SQL 문장에서 옵티마이저 힌트를 한번 사용하게 되면, 왠만해서는 그 힌트를 변경하지 않아요. 하지만 시간이 지나면서 DBMS의 데이터는 계속 바뀌게 되고 예전의 최적 실행 계획이 지금은 최적이 아닌 경우가 발생할 수 있기 때문에 가능하면 옵티마이저 힌트를 사용하지 않는 것을 추천해요.
오늘은 상당히 치명적인 옵티마이저 오류 케이스를 살펴보고, 옵티마이저가 왜 그런 잘못된 선택을 했는지 원인을 분석하는 과정을 한번 살펴보고, 이를 회피할 수 있는 방법은 어떤 것들이 있는지도 한번 살펴보려고 해요.
실행 계획 오류 (ref vs range) 케이스
우선 대상 테이블의 구조와 테이블 정보는 다음과 같아요.
CREATE TABLE tab (
id bigint NOT NULL AUTO_INCREMENT,
bid bigint NOT NULL,
created_at datetime DEFAULT NULL,
...
PRIMARY KEY (id),
KEY ix_bid_id (bid,id),
KEY ix_bid_createdat (bid,created_at)
);
mysql> SHOW INDEXES FROM tab;
+-------+------------+------------------+--------------+-------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Cardinality |
+-------+------------+------------------+--------------+-------------+-------------+
| tab | 0 | PRIMARY | 1 | id | 11797584 |
| tab | 1 | ix_bid_id | 1 | bid | 581387 |
| tab | 1 | ix_bid_id | 2 | id | 11797584 |
| tab | 1 | ix_bid_createdat | 1 | bid | 561151 |
| tab | 1 | ix_bid_createdat | 2 | created_at | 11702179 |
+-------+------------+------------------+--------------+-------------+-------------+이제 ix_bid_createdat(bid, created_at) 인덱스와 ix_bid_id(bid, id) 인덱스를 사용할 것으로 예상되는 2개 쿼리의 실행 계획을 한번 확인해볼게요.
mysql> EXPLAIN SELECT COUNT(*) FROM tab WHERE bid=1198442 AND created_at<DATE_SUB(NOW(), INTERVAL 9 HOUR);
+------+-----------+---------+-------+---------+----------+-------------+
| type | key | key_len | ref | rows | filtered | Extra |
+------+-----------+---------+-------+---------+----------+-------------+
| ref | ix_bid_id | 8 | const | 1632722 | 33.33 | Using where |
+------+-----------+---------+-------+---------+----------+-------------+
mysql> EXPLAIN SELECT COUNT(*) FROM tab WHERE bid=1198442 AND id<14025956;
+------+------------------+---------+-------+---------+----------+--------------------------+
| type | key | key_len | ref | rows | filtered | Extra |
+------+------------------+---------+-------+---------+----------+--------------------------+
| ref | ix_bid_createdat | 8 | const | 1426712 | 50.00 | Using where; Using index |
+------+------------------+---------+-------+---------+----------+--------------------------+MySQL 옵티마이저가 뭔가 잘못된 인덱스를 선택한 것이 보이시죠. 그냥 깊이 생각하지 않아도 첫번째 쿼리는 ix_bid_createdat(bid, created_at)인덱스를 사용하면 커버링 인덱스로 매우 빠르게 처리될 수 있고, 두번째 쿼리는 ix_bid_id(bid, id)인덱스를 사용하면 똑같이 커버링 인덱스로 빠르게 처리될 쿼리라는 것을 알수 있어요. 하지만 MySQL 옵티마이저는 2개 쿼리에 대해서 거꾸로 인덱스를 선택해버렸어요.
이로 인해서 2개 쿼리는 0.2초면 완료된 쿼리가 3초 가까이 걸려서 완료되었어요. 뿐만 아니라 디스크 읽기 작업과 CPU 사용량 또한 상당히 많이 높아졌고, 결과적으로 DBMS 서버의 모든 쿼리가 지연되는 문제가 발생하기도 해요.
간단한 해결 방법
다음과 같이 옵티마이저 힌트를 이용하면 어렵지 않게 회피할 수 있어요.
SELECT /*+ INDEX(tab ix_bid_createdat) */ COUNT(*)
FROM tab
WHERE bid=1198442
AND created_at<DATE_SUB(NOW(), INTERVAL 9 HOUR);
SELECT /*+ INDEX(tab ix_bid_id) */ COUNT(*)
FROM tab
WHERE bid=1198442
AND id<14025956;힌트를 사용한 쿼리의 실행 계획을 한번 살펴보면 다음과 같아요. 하지만 오늘은 왜 옵티마이저가 이런 판단을 했는지, 옵티마이저 힌트 없이 최적화 오류를 회피할 수 있는 방법은 없는지 깊이 있게 한번 생각해보려고 해요.
SELECT /*+ INDEX(tab ix_bid_createdat) */ COUNT(*)
FROM tab
WHERE bid=1198442
AND created_at<DATE_SUB(NOW(), INTERVAL 9 HOUR);
+-------+------------------+---------+------+---------+----------+--------------------------+
| type | key | key_len | ref | rows | filtered | Extra |
+-------+------------------+---------+------+---------+----------+--------------------------+
| range | ix_bid_createdat | 14 | NULL | 1490996 | 100.00 | Using where; Using index |
+-------+------------------+---------+------+---------+----------+--------------------------+여기에서 주의깊게 봐야 할 부분은 성능이 느린 실행 계획에서는 type 필드의 값이 ref 였었는데, 옵티마이저 힌트를 사용해서 성능이 개선된 쿼리의 실행 계획에서는 range로 바뀌었다는 것이에요. 실제 이 쿼리의 최적화 오류는 ref 접근 방법의 비용 계산이 너무 낮게 계산되면서 발생한 문제인데, ref 접근 방법의 비용이 이렇게 낮게 계산된 이유를 이제부터 살펴보려고 해요.
쿼리 비용 계산
우선 MySQL 서버의 쿼리 비용 계산 과정을 살펴보기 전에, MySQL 서버에서 사용하는 2가지 단위 작업에 대한 비용 상수(메뉴얼에서는 이를 Cost Model 이라고 해요)에 대해서 이해를 해야 하는데, MySQL 서버에서 단위 작업에 대한 비용 상수는 아래와 같이 확인할 수 있어요. 이 비용 상수에 대한 자세한 설명은 Real MySQL 1권을 참고해주세요. 우선 지금은 비용 상수는 변경없이 MySQL 서버의 기본 값을 그대로 사용중이라는 것을 기억해주세요.
mysql> SELECT * FROM mysql.server_cost;
+------------------------------+------------+---------------+
| cost_name | cost_value | default_value |
+------------------------------+------------+---------------+
| disk_temptable_create_cost | NULL | 20 |
| disk_temptable_row_cost | NULL | 0.5 |
| key_compare_cost | NULL | 0.05 |
| memory_temptable_create_cost | NULL | 1 |
| memory_temptable_row_cost | NULL | 0.1 |
| row_evaluate_cost | NULL | 0.1 |
+------------------------------+------------+---------------+
mysql> SELECT * FROM mysql.engine_cost;
+------------------------+------------+---------------+
| cost_name | cost_value | default_value |
+------------------------+------------+---------------+
| io_block_read_cost | NULL | 1 |
| memory_block_read_cost | NULL | 0.25 |
+------------------------+------------+---------------+이제 MySQL 서버에서 다음 쿼리가 ix_bid_id(bid, id)인덱스와 ix_bid_createdat(bid, created_at)인덱스를 사용할 때, 각각 쿼리 처리에 필요한 비용이 얼마일지 예측한 값을 비교해보았어요.
mysql> EXPLAIN FORMAT=JSON
SELECT /*+ INDEX(tab ix_bid_id) */ COUNT(*)
FROM tab
WHERE bid=1198442 AND created_at<DATE_SUB(NOW(), INTERVAL 9 HOUR);
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "236946.95"
},
"table": {
"table_name": "tab",
"access_type": "ref",
"key": "ix_bid_id",
"used_key_parts": [
"bid"
],
"key_length": "8",
"rows_examined_per_scan": 1632722,
"rows_produced_per_join": 544186,
"filtered": "33.33",
"cost_info": {
"read_cost": "73674.75",
"eval_cost": "54418.62",
"prefix_cost": "236946.95",
"data_read_per_join": "24M"
},
...
}
}
}
mysql> EXPLAIN FORMAT=JSON
SELECT /*+ INDEX(tab ix_bid_createdat) */ COUNT(*)
FROM tab
WHERE bid=1198442 AND created_at<DATE_SUB(NOW(), INTERVAL 9 HOUR);
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "302192.75"
},
"table": {
"table_name": "tab",
"access_type": "range",
"key": "ix_bid_createdat",
"used_key_parts": [
"bid",
"created_at"
],
"key_length": "14",
"rows_examined_per_scan": 1490996,
"rows_produced_per_join": 1490996,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "153093.15",
"eval_cost": "149099.60",
"prefix_cost": "302192.75",
"data_read_per_join": "68M"
},
...
}
}
}ix_bid_id(bid, id) 인덱스를 사용한 경우 전체 쿼리 비용은 234128.51(query_cost)이며, ix_bid_createdat(bid, created_at) 인덱스를 사용한 경우 쿼리 비용은 302192.75(query_cost)였다는 것을 확인할 수 있어요. 이 결과만 보면, 옵티마이저는 ix_bid_id 인덱스를 사용하는 실행 계획이 더 빠를 것이라고 예측했다는 것을 알 수 있죠. 하지만 실제 쿼리 실행 결과는 10배나 더 걸렸다는 것을 위해서 확인했죠.
MySQL 의 쿼리 비용은 참조해야 하는 모든 테이블의(read_cost + eval_cost)의 합이에요. read_cost는 CPU 기반 처리 작업의 비용이며, eval_cost 는 디스크 또는 메모리에서 데이터 페이지 읽기 작업의 비용을 의미해요. 이번 예제는 조인이 없기 때문에 단순히 read_cost와 eval_cost의 합이 전체 쿼리 비용(query_cost)이 되요. 그런데 ix_bid_createdat 인덱스의 경우 query_cost=(read_cost + eval_cost) 수식이 성립되지만, ix_bid_id 인덱스의 경우 query_cost는 (read_cost + eval_cost)와 일치하지 않아요. 이는 ref 접근 방법의 비용 표현에서 일부가 누락되었기 때문이에요.
또한 MySQL 8.0 버전부터는 테이블의 데이터 페이지가 메모리(InnoDB Buffer Pool)에 얼마나 적재되어 있는지 비율도 같이 계산되도록 개선되었는데, 이 비율은 다음과 같이 확인해 볼 수 있어요.
SELECT tables.NAME as table_name,
indexes.NAME as index_name,
stats.stat_value as n_total_pages,
cached.N_CACHED_PAGES as n_cached_pages,
(stats.stat_value - cached.N_CACHED_PAGES) as n_not_cached_pages,
(100.0*cached.N_CACHED_PAGES/stats.stat_value) as cached_ratio,
(100-(100.0*cached.N_CACHED_PAGES/stats.stat_value)) as not_cached_ratio
FROM information_schema.INNODB_TABLES AS tables
INNER JOIN information_schema.INNODB_INDEXES indexes ON indexes.TABLE_ID = tables.TABLE_ID
INNER JOIN information_schema.INNODB_CACHED_INDEXES cached ON cached.INDEX_ID = indexes.INDEX_ID
INNER JOIN mysql.innodb_index_stats stats ON stats.database_name=substring_index(tables.NAME,'/',1) AND stats.table_name=substring_index(tables.NAME,'/',-1) AND stats.index_name=indexes.NAME AND stats.stat_name='size'
WHERE tables.NAME='test/tab';
+------------+------------------+---------------+----------------+--------------------+--------------+------------------+
| table_name | index_name | n_total_pages | n_cached_pages | n_not_cached_pages | cached_ratio | not_cached_ratio |
+------------+------------------+---------------+----------------+--------------------+--------------+------------------+
| test/tab | PRIMARY | 47680 | 30829 | 16851 | 64.65814 | 35.34186 |
| test/tab | ix_bid_id | 28672 | 11 | 28661 | 0.03836 | 99.96164 |
| test/tab | ix_bid_createdat | 36608 | 11 | 36597 | 0.03005 | 99.96995 |
+------------+------------------+---------------+----------------+--------------------+--------------+------------------+이제 각 인덱스를 사용하는 실행 계획의 비용 계산 과정을 살펴보려고 해요. 그런데 MySQL 서버 내부 코드 로직은 접근 방법에 따라서 매우 다양한 형태의 계산식을 활용해요. 그래서 아래의 계산식이 모든 쿼리의 비용 계산에 사용될 수 있는 공식은 아니며, 지금 살펴보고 있는 쿼리에만 적용된다는 것을 기억해주세요. 하지만 다음 계산식을 통해서 MySQL 옵티마이저가 어떻게 쿼리의 비용을 예측하는지 대략적으로 살펴볼 수 있을 거에요. MySQL에서 ref와 range는 모두 인덱스를 효율적으로 사용하는 실행 계획이긴 하지만, 두 접근 방법의 비용 계산 방식은 매우 다르게 구현되어 있어요.
ix_bid_id 인덱스 활용 실행 계획
total_pages_of_table = 47680
estimated_rows = 1632722
pct_cached_pages(Primary) = 64.65814 %
pct_not_cached_pages(Primary) = (100 - 64.65814) %
row_evaluate_cost = 0.10
memory_block_read_cost = 0.25
disk_block_read_cost = 1.00
worst_seek_cost(84095.62) = (estimated_rows/10 * pct_cached_pages * 0.25) + (estimated_rows/10 * pct_not_cached_pages * 1)
total_scan_cost(24558.25) = (total_pages_of_table * pct_cached_pages * 0.25) + (total_pages_of_table * pct_not_cached_pages * 1)
eval_cost(163272.2) = estimated_rows * row_evaluate_cost
read_cost(73674.75) = MIN(worst_seek_cost, total_scan_cost*3)
query_cost(236946.95) = read_cost + eval_costix_bid_createdat 인덱스 활용 실행 계획
estimated_rows = 1490996
pct_cached_pages(Index ix_bid_createdat) = 0.03005 %
pct_not_cached_pages(Index ix_bid_createdat) = (100 - 0.03005) %
row_evaluate_cost = 0.10
memory_block_read_cost = 0.25
disk_block_read_cost = 1.00
keys_per_block(373.36) = 16 * 1024 / 2 / (8+5+1+8) + 1
read_index_pages(3994.45) = (estimated_rows + keys_per_block - 1) / keys_per_block
read_time(3993.55) = (read_index_pages * pct_cached_pages * 0.25) + (read_index_pages * pct_not_cached_pages * 1)
eval_cost(149099.6) = estimated_rows * row_evaluate_cost
read_cost(153093.15) = eval_cost + read_time
query_cost(302192.75) = read_cost + eval_cost이 계산 과정에서 ix_bid_id 인덱스가 채택될 수밖에 없는 결정적인 이유는 이 인덱스를 사용할 때 실행 계획의 read_cost를 매우 낮은 수준으로 판단했기 때문이에요. read_cost를 계산할 때 MySQL 옵티마어지는 worst_seek_cost와 total_scan_cost 2개의 값중에서 최소값을 선택하는데, worst_seek_cost는 ix_bid_id 인덱스를 읽고 레코드 건수만큼 데이터 페이지를 읽는 비용을 의미하고, total_scan_cost는 테이블의 모든 데이터 페이지를 읽는 비용을 의미해요. 그런데 total_scan_cost가 너무 낮은 비용으로 평가되면서 쿼리의 실행 계획이 잘못되기 시작한거죠. total_scan_cost 가 너무 낮은 비용으로 계산된 이유는 ref 실행 계획의 비용은 ix_bid_id 인덱스가 아닌 PRIMARY 인덱스의 데이터 페이지 읽기를 계산하는데, 일반적으로 인덱스보다 데이터 파일의 페이지가 메모리에 상주할 가능성이 높기 때문이에요.
처음에 살펴보았던 아래 쿼리도 ix_bid_id 가 아닌 ix_bid_createdat 인덱스를 사용하게 되는 이유도 동일해요.
SELECT COUNT(*) FROM tab WHERE bid=1198442 AND id<14025956;range 접근 방식보다 ref 접근 방식의 비용이 낮게 계산되면서, 쿼리 조건에 딱 맞는 인덱스는 사용하지 못하고 조금 비슷한 패턴의 인덱스만 사용하도록 실행 계획이 수립되고 있었던 거죠.
Cost Model 해결 방법
아래 쿼리가 ix_bid_id 인덱스가 아닌 ix_bid_createdat 인덱스를 사용하도록 하려면, 옵티마이저의 단위 비용 상수를 조정하여 ref 접근 방법의 비용을 더 높이거나 range 접근 방법의 비용을 더 낮춰 줘야 해요
SELECT COUNT(*)
FROM tab
WHERE bid=1198442 AND created_at<DATE_SUB(NOW(), INTERVAL 9 HOUR);그런데 row_evaluate_cost 단위 비용 상수외에는 두 실행 계획의 비용 차별성을 만들어낼 수가 없어요. 뿐만 아니라 이 쿼리가 ix_bid_createdat 을 사용하지 못하고 ix_bid_id 를 사용하게 되는 가장 큰 이유는 (위의 비용 계산식에서 눈치챘겠지만), 읽는 레코드에 대해서 eval_cost (row_evaluate_cost) 가 전체 비용에 두번이나 영향을 미치고 있다는 것이에요.
그래서 우리는 row_evaluate_cost 비용 상수를 더 낮은 값으로 설정해서, ix_bid_createdat 의 실행 계획 비용을 상대적으로 더 낮추는 전략을 고려해볼 수 있어요.
UPDATE mysql.server_cost
SET cost_value=0.03 /* 데이터 분포도에 따라서 옵티마이저는 여전히 ix_bid_id 인덱스를 선호할 수 있음*/
WHERE cost_name='row_evaluate_cost';
FLUSH OPTIMIZER_COSTS;
-- // 현재 세션은 종료하고, 새로 MySQL 서버에 로그인해야 변경된 비용으로 쿼리 실행 계획이 수립되는 것을 확인할 수 있어요.이제 성능 문제가 있었던 쿼리의 실행 계획을 다시 살펴볼게요.
mysql> EXPLAIN SELECT COUNT(*) FROM tab WHERE bid=1198442 AND created_at<DATE_SUB(NOW(), INTERVAL 9 HOUR);
+-------+------------------+---------+------+---------+----------+--------------------------+
| type | key | key_len | ref | rows | filtered | Extra |
+-------+------------------+---------+------+---------+----------+--------------------------+
| range | ix_bid_createdat | 14 | NULL | 1490996 | 100.00 | Using where; Using index |
+-------+------------------+---------+------+---------+----------+--------------------------+쿼리의 실행 계획이 ix_bid_createdat 인덱스를 사용하도록 변경되었어요.
mysql> EXPLAIN FORMAT=JSON
SELECT /*+ INDEX(tab ix_bid_id) */ COUNT(*)
FROM tab
WHERE bid=1198442 AND created_at<DATE_SUB(NOW(), INTERVAL 9 HOUR);
...
"cost_info": {
"read_cost": "73429.79",
"eval_cost": "16325.59",
"prefix_cost": "122411.45",
"data_read_per_join": "24M"
},
mysql> EXPLAIN FORMAT=JSON
SELECT /*+ INDEX(tab ix_bid_createdat) */ COUNT(*)
FROM tab
WHERE bid=1198442 AND created_at<DATE_SUB(NOW(), INTERVAL 9 HOUR);
...
"cost_info": {
"read_cost": "48727.16",
"eval_cost": "44729.88",
"prefix_cost": "93457.04",
"data_read_per_join": "68M"
},실제 쿼리의 비용을 한번 살펴보면, ix_bid_id 인덱스를 사용하는 실행 계획은 전체 비용이 122411.45로 떨어졌고, ix_bid_createdat인덱스를 사용하는 실행 계획은 93457.04로 비용이 더 크게 떨어진 것을 확인할 수 있어요.
주의 사항
mysql.engine_cost 와 mysql.server_cost 테이블을 이용해서 단위 작업 비용을 튜닝하는 작업은 매우 신중히 진행되어야 해요. 이 테이블의 단위 작업 비용은 지금 튜닝하고자 하는 쿼리 뿐만 아니라, DBMS에 실행되는 모든 쿼리의 실행 계획에 영향을 미치기 때문에 잘못 설정된 값은 DBMS 서버를 즉시 응답 불능 상태로 만들어버릴 수도 있어요. 반드시 단위 작업 비용들을 큰 값 또는 작은 값으로 설정하게 되면 어떤 실행 계획이 더 자주 사용되고 어떤 실행 계획이 덜 자주 사용되도록 바뀔지 예측을 해보고, 변경 적용할 것을 권장드려요.
또한 이 케이스의 옵티마이저 오류는 이미 버그 레포트가 되어 있으며(Oracle에서도 버그로 인식), 현재 최신 버전인 MySQL 8.0.33 버전에서도 아직 개선되진 않았지만, 조만간 개선이 될 거라 예상해요. 그래서 MySQL 서버의 Cost Model에 대해서 전문적인 지식이 없다면, 당분간은 옵티마이저 힌트로 버그를 회피하고, 버그가 해결되면 버전 업그레이드를 하는 방법도 좋아 보여요.
참고
Ref Access Cost 관련 주요 코드
- https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/sql_optimizer.cc#L5827-L5838
- https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/handler.cc#L6005-L6017
Range Access Cost 관련 주요 코드
당근마켓에서 함께 고민을 나누고 싶다면 여기를 눌러 당근마켓 채용 공고를 확인해보세요!
