1.intro
mysql 에 character set이 utf8에서 utf8mb4로 바뀌면서 단순히 emoji 정도만 추가된 것이라고 생각하셨다면잠시나마 여유가될 떄 아래 글을 한번 보는 것도 좋을 거 같다.
기록은 장애가 발생한 원인부터 발생시점 그리고 해결과정을 시간의 순서대로 기록한다.
2. mysql DB를 업그레이드하다
서비스 DB는 mysql 5.5이고 default character set은 utf8로 서비스해왔다.
mysql 5.7로 업데이트를 진행하면서 default가 utfmb4인 것을 알게되었다
utf8 -> utf8mb4 로 모든테이블을 마이그레이션할까 하다 굳이 emoji를 안쓰는데 바꿀이유는 없어서
default character set은 utf8mb4를 설정했지만
테이블 character set은 utf8을 그대로 사용하는 것으로 마이그레이션을 진행하였고
별무리없이 서비스 오픈하였다
난 스토리지 용량도 효율적으로 사용해서 1byte씩 더 아낀 효율적인 DBA라고 생각하면서..
3. 문제가 발생하다
그 이후 몇 년 지나 .. row가 1억 건이 넘는 대용량 테이블이 하나둘 생겨났고
어느 날 성능 이슈가 발생하였다.
이슈는 함수와 프로시저를 사용할 때 발생했는데
실행계획을 분석해봤지만
별다른 문제점을 찾을 수 없었는데
테이블 별로 쪼개서 분석하다 보니 join 등의 영향보단
함수 안에서 특정 함수를 호출하는 것이 있는데
그 함수를 사용시 급격한 성능 저하가 발생하는 것을 확인하게 되었다
함수는 단순한 구조로 되어 있었고 실행계획을 보면
별문제는 없었는데
왜 select ~ from 안에서 function 으로 호출하면 문제가 되는 걸까?
조인하는 테이블이 많다보니 스택에서 join buffer의 량이 너무 많아서
임시테이블까지 쓰는 것일지 확인해보려
Select 문 아래 조인테이블을 모두 제거 해도 현상은 동일했고
좀처럼 원인을 알 수 없었다.
4. 이슈를 좁히다
일단 문제가 되는 Function 에 집중하기로 했다
그러던 중 function 안에서 호출하는 테이블 중
하나가 대용량테이블이라는 것을 알게 되었고
이것이 인덱스를 사용하지 못했을 때를 가정하고
테스트했을 때 성능저하때와 동일한 리턴시간이 걸리는 것을 확인했다
이때부터 full scan을 의심했고 인덱스를 사용하지 못할 상황에 대해
알아보기 시작했다.
그리고 분명 DB Tool에서는 인덱스 스캔인데
CLI로 확인해보려고 mysql -uroot -p로 들어가서 실행계획을 확인해보니
Full scan하는 것을 확인했다
문제의 원인은 full scan 이 맞았다.
근데 왜 이런현상이 발생할까?
인덱스 컬럼은 varchar 였고 관련 내용을 구글링하던중
character set이 다를 경우 인덱스 스캔을 못할 수도 있다는 것을 알게 되었다
#character set 이 달라 인덱스 사용못하는 사례
https://stackoverflow.com/questions/71771201/mysql-character-set-select-query-performance-in-stored-procedure
#글내용중 요약
utf8mb4 문자 세트 및 데이터 정렬이 있는 문자열과 비교하도록 강제하고 있습니다.
인덱스는 정렬된 데이터 구조이며 정렬 순서는 열의 데이터 정렬에 따라 다릅니다.
해당 인덱스를 사용한다는 것은 정렬 순서를 활용하여 모든 행을 검사하지 않고 빠르게 값을 조회한다는 의미입니다.
열을 데이터 정렬이 다른 문자열과 비교할 때 MySQL은 정렬 순서 또는 UUID 상수의 문자열 등가가 호환 가능하다고 추론할 수 없습니다.
따라서 문자열 비교를 row 단위의 어려운 방식으로 수행해야 합니다.
이것은 버그가 아니며 데이터 정렬이 작동하도록 의도된 방식입니다.
인덱스를 활용하려면 호환되는 데이터 정렬이 있는 문자열과 비교해야 합니다.
5. 장애를 해결하다
1. mysql default character set 설정확인
mysql> show global variables like '%coll%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_unicode_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | utf8mb4_unicode_ci | +----------------------+--------------------+ 3 rows in set (0.00 sec) mysql> show global variables like '%char%'; +--------------------------+----------------+ | Variable_name | Value | +--------------------------+----------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 || +--------------------------+----------------+ 8 rows in set (0.00 sec)
2. 문제가 되는 대용량 테이블 character set 확인
SELECT CHARACTER_SET_NAME ,COLLATION_NAME FROM INFORMATION_SCHEMA.`COLUMNS` WHERE TABLE_NAME = 'TB_TEMP_TABLE' and COLUMN_NAME = 'TG_YYMM'; ---------------------------- utf8 | utf8_general_ci | ----------------------------
아하~!
default set이 utf8mb4라서 function은 utf8mb4의 varchar를 인식하고
테이블은 utf8이라서 두 개를 동등 비교하지 못해
utf8 != utf8mb4
인덱스스캔을 하지 못하는 것이었다
sql을 utf8로 맞춰서 Casting을 해보면
update TB_TEMP_TABLE
SET RCP_DT = CDP_DT
where TG_YYMM = CONVERT(varMonth USING utf8)
and CAST_CD in ('xxx','xxx')
and LENGTH(RCP_DT) !=8;
엄청난 성능 향상을 보여준다.
2분 이상 걸리던 쿼리가 0.05초 만에 끝났고
인덱스 스캔도 빠른 ref 을 쓰는 것을 확인하였다.
5. Why?
mysql의 옵티마이져는 user가 똑똑하지 못한 것을 이미 알고 있고
어느 정도 선에서 자동 형변환을 지원한다.
이것을 테스트하는 가장 빠른 방법은 테이블 2개를 생성해서 조인해보면 된다.
1. 100~200만건 정도의 row 테이블 1개 생성하고 조인할 컬럼을 varchar(10) 정도로 선언
2. 100~200만건 정도의 row 테이블 1개 생성하고 조인할 컬럼을 bigint(20) 정도로 선언
둘다 조인할 컬럼에 인덱스를 생성하고 두 개를 조인해보면 인덱스를 제대로 사용하지 못하는 것을 알 수 있다.
row 수를 줄이다 보면 어느 선에서 갑자기 인덱스 스캔이 한다는 것을 알 수 있는데
이건 옵티마이져가 한쪽의 컬럼을 강제로 형변환해서 인덱스를 사용할 수 있게 해준 것이다.
6. 결론
대용량 데이터가 되었을 때는 default character set과 table character set을 맞춰 주는 것이
성능이슈를 야기하지 않는다는 것을 알게되었다
향후 다시 DB 업그레이드를 하게 된다면
테이블 및 모든 데이터를 utf8mb4로 전부 character set을 맞춰서
성능 이슈가 발생하지 않게 하는 것이 좋겠다.
끝.