index타던 쿼리가 full scan할 때(range_optimizer_max_mem_size)
Index 잘 타던 쿼리가 fullscan을 하면서 CPU가 100% 를 치는 이슈가 발생했습니다.
range_optimizer_max_mem_size 라는 생소한 파라미터를 설정하여 이슈 해결한 사례를 공유합니다.
이슈 상황
DB server에서 갑자기 CPU가 100%를 치는 상황이 발생
원인은 아래 Slow query 가 수행되면서 발생한것으로 추측
SELECT *
FROM tb_test tb1
LEFT OUTER JOIN tb_test2 AS tb2 ON tb1.name = tb2.name
WHERE tb1.status = 'active'
AND tb1.resource_id in (1,2 ......... N)
=> where IN절에 bind값이 많기는 하지만 필요한 인덱스는 모두 있어서 문제될 법한 쿼리는 아닌 듯 했으나 full scan을 하면서 CPU가 급증함
원인
where 조건의 범위가 제한된 메모리보다 초과하여 index를 타지 못하고 full scan 하게 됨
- MySQL5.7.14
default : range_optimizer_max_mem_size = 8MB
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| range_optimizer_max_mem_size | 8388608 |
+------------------------------+---------+
공식문서 https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html
- or 는 하나당 230bytes
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
- and 는 하나당 125bytes
SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;
재현 테스트
- range_optimizer_max_mem_size = 8388608
select count(*) from tb_test ;
+----------+
| count(*) |
+----------+
| 2097152 |
+----------+
mysql> select * from tb_test where i in (1,2,3,4,5,6,7,8,9,10);
+---+--------------------------------------+----------+----+
| i | s | t | g |
+---+--------------------------------------+----------+----+
| 1 | ecb5774f-4e92-11eb-9465-00163e462f55 | 13:44:10 | 38 |
| 2 | ecb9f236-4e92-11eb-9465-00163e462f55 | 13:44:10 | 16 |
| 3 | ecbac904-4e92-11eb-9465-00163e462f55 | 13:44:10 | 26 |
| 4 | ecbacaf0-4e92-11eb-9465-00163e462f55 | 13:44:10 | 22 |
| 6 | ecbb4b04-4e92-11eb-9465-00163e462f55 | 13:44:10 | 30 |
| 7 | ecbb4d68-4e92-11eb-9465-00163e462f55 | 13:44:10 | 26 |
| 8 | ecbb4dc6-4e92-11eb-9465-00163e462f55 | 13:44:10 | 37 |
| 9 | ecbb4e01-4e92-11eb-9465-00163e462f55 | 13:44:10 | 49 |
+---+--------------------------------------+----------+----+
8 rows in set (0.00 sec)
mysql> explain select * from tb_test where i in (1,2,3,4,5,6,7,8,9,10);
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_test| NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show status like '%handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
...
| Handler_read_key | 10 |
=> PK 인덱스를 활용하여 10개의 row 만 접근한 것을 확인할 수 있음
handler_read_key : 단일 row inddex key 값을 읽은 횟 수, PK나 UK scan이나, range scan 시 처음 읽은 시작점으로 접근했을 때 증가
- range_optimizer_max_mem_size = 500
mysql> set range_optimizer_max_mem_size = 500;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb_test where i in (1,2,3,4,5,6,7,8,9,10);
+---+--------------------------------------+----------+----+
| i | s | t | g |
+---+--------------------------------------+----------+----+
| 1 | ecb5774f-4e92-11eb-9465-00163e462f55 | 13:44:10 | 38 |
| 2 | ecb9f236-4e92-11eb-9465-00163e462f55 | 13:44:10 | 16 |
| 3 | ecbac904-4e92-11eb-9465-00163e462f55 | 13:44:10 | 26 |
| 4 | ecbacaf0-4e92-11eb-9465-00163e462f55 | 13:44:10 | 22 |
| 6 | ecbb4b04-4e92-11eb-9465-00163e462f55 | 13:44:10 | 30 |
| 7 | ecbb4d68-4e92-11eb-9465-00163e462f55 | 13:44:10 | 26 |
| 8 | ecbb4dc6-4e92-11eb-9465-00163e462f55 | 13:44:10 | 37 |
| 9 | ecbb4e01-4e92-11eb-9465-00163e462f55 | 13:44:10 | 49 |
+---+--------------------------------------+----------+----+
8 rows in set, 1 warning (0.82 sec)
mysql> explain select * from tb_test where i in (1,2,3,4,5,6,7,8,9,10);
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | tb_test| NULL | ALL | PRIMARY | NULL | NULL | NULL | 2088128 | 50.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql> show status like '%handler%';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
...
| Handler_read_rnd_next | 2097153 |
=> index를 활용하지 못하고 full scan한 것 확인
Handler_read_rnd_next : full scan 발생 시 random I/O 횟수
해결방법
- 쿼리의 where 조건이 정상적으로 들어온 것을 먼저 확인 후
- range_optimizer_max_mem_size=0 으로 설정하거나 늘릴것을 검토한다. dynamic parameter이며 0으로 설정 시 unlimit을 의미