<aside> 💡

요약

MySQL8 버전 업그레이드 대비 Deprecated Hint, Functions 제거

</aside>

MySQL8.0.17 Deprecated ( [Hint]SQL_CALC_FOUND_ROWS / [Fun]FOUND_ROWS()) 제거

개요

• 프로젝트 RDBMS 로 MySQL 5.7 로 운영 중에 있다. 향후 MySQL 8.0.17 마이그레이션 계획이 잡혀 있다.
  • MySQL 5.7 의 경우 EOL 이 선언 AWS RDB 를 활용하면 유지 비용이 추가로 부여가 되고 이를 위해 업그레이드를 진행 한다
• 일부 코드 중에서 MySQL 8.0.17 부터 Deprecated 된 기능이 존재하여 이를 제거 처리 하려고 한다.

기능 분석

• DB Connection 세션 단위로 라이프 사이클이 동작하게 된다.
  • [Hint]SQL_CALC_FOUND_ROWS
    • 마지막 SELECT 에 대해서 전체 Count 를 세션에 임시 보관한다.
  • [Fun]FOUND_ROWS()
    • 세션에 임시 보관된 Count 가져온다. ( default : 0 )
• Hint / Functions 를 활용하기 위해서는 Hint → Functions 순으로 진행 되어야 원하는 Count 의 결과를 기반으로 페이징에 활용이 가능하다.
• 추가로 Functions 를 사용하기 전에 동일한 세션안에서 다른 SELECT ( Hint ) 를 동작하게 되면 마지막에 동작한 SELECT 의 Count 가 저장되니 필히 SELECT ( Hint ) → FOUND_ROW() 를 사용해야 한다.

해결 방법

• 여러 서비스에 걸쳐 활용되고 있기 때문에 최소한의 영향을 가지는 서비스를 기반으로 MySQL 5.x 호환성 테스트와 성능 테스트를 진행 후 반영하기로 함.

• 간단한 성능 테스트

  • SELECT COUNT() + SELECT ~ LIMIT
  • SELECT SQL_CALC_FOUND_ROWS ~ LIMIT

• 성능 테스트를 위해 쿼리 메모리 사용량 등을 모니터링 해야 하는데 이는 MySQL 에서 제공되고 있다. profile 을 활용하면 가능하다. 향후 Profile 도 Deprecated 되어 performance_schem 로 대체된다.

  MySQL :: MySQL 8.4 Reference Manual :: 15.7.7.32 SHOW PROFILE Statement

  MySQL :: MySQL 8.4 Reference Manual :: 29.19.1 Query Profiling Using Performance Schema

  • 현 프로젝트는 MySQL 5.7 → 8.0.36 마이그레이션을 계획하고 있기에 profile 을 이용해 메모리, CPU 사용량 등을 확인 해보자.

    -- profiling 은 세션 단위로 활성화 
    -- profiling deprecated 대신 performance_schem 활용 권고 
    -- profiling 옵션 확인 0 : off / 1 : on
    select @@profiling;
    
    -- 쿼리 프로파일링 활성화
    set profiling=1; -- on
    set profiling=0; -- off
    

    

  • 모니터링 하고자 하는 QUERY 동작 후 ID 값을 기반으로 CPU 사용률 그리고 CODE 블럭 단위로 소요된 시간 등을 확인할 수 있다.

    -- Query 별 총 소요 시간을 볼 수 있다.
    SHOW profiles;
    
    SELECT QUERY_ID, SUM(DURATION) AS TOTAL_TIME
    FROM INFORMATION_SCHEMA.PROFILING
    WHERE QUERY_ID = {Query_ID}
    ORDER BY SEQ;
    
    -- Query 를 좀 더 상세하게 블록단위로 볼 수 있으며, Option 을 통해서 제공하는 Option 의 정보도 볼 수 있다. 
    SHOW profile FOR QUERY {Query_ID};
    SHOW profile CPU FOR QUERY {Query_ID};
    
    SELECT STATE, FORMAT(DURATION, 6) AS DURATION
    FROM INFORMATION_SCHEMA.PROFILING
    WHERE QUERY_ID = {Query_ID} ORDER BY SEQ;
    

• 프로파일링을 통해서 SELECT COUNT() + SELECT ~ LIMIT / SELECT SQL_CALC_FOUND_ROWS ~ LIMIT 2 가지의 경우의 CPU 사용량, 소요 시간을 기반으로 성능을 확인해보자.

  테스트 Data 1,520,395 건

  | 구분 | 총 소요 시간 ( 초 ) | 소요 시간 ( 초 ) | CPU 사용 시간 ( USER + SYSTEM ) ( 초 ) | CPU 사용 시간 ( USER + SYSTEM ) ( 초 ) | | --- | --- | --- | --- | --- | | SELECT COUNT() + SELECT ~ LIMIT | 0.397087 | COUNT() : 0.389429

  SELECT ~ LIMIT : 0.007658 | 0.404771 | COUNT() : 0.403542

  SELECT ~ LIMIT : 0.001229 | | SQL_CALC_FOUND_ROWS + FOUND_ROWS() | 1.387430 | SQL_CALC_FOUND_ROWS : 1.386942

  FOUND_ROWS() : 0.000488 | 1.388153 | SQL_CALC_FOUND_ROWS : 1.387711

  FOUND_ROWS() : 0.000442 |

  • 결과적으로 대략 소요 시간과 CPU 사용 시간이 힌트를 사용하는 경우가 2 ~ 4배 정도의 차이가 발생하고 있다. 1,000,000 건 이상도 실무에서는 그렇게 큰 데이터가 아님에도 불구하고 생각보다 큰 차이를 보여주고 있다.
  • FOUND_ROWS() 함수만 별도로 보면 비교하지 못할 정도록 빠르게 처리가 되고 있지만 SQL_CALC_FOUND_ROWS SELECT 문에 의해 압도적으로 시간이 소요되는 것을 볼 수 있다.
  • 일부 데이터가 가지는 최대 값 10,549 건으로 API 전체 조회 시 아래와 같은 결과를 얻을 수 있다.
    • COUNT + SELECT ~ LIMIT
      • 67,000,000나노초 → 0.067초
    • SQL_CALC_FOUND_ROWS + FOUND_ROWS
      • 109,000,000나노초 → 0.109초
    • 작은 데이터 상에서도 결과에 차이가 나는 것을 볼 수 있다.
  • 물론 모든 경우가 성능이 좋아진다고 볼 수는 없다. MySQL 버전을 업그레이드 하면서 사라질 기능을 대체하고 충분한 성능을 낼 수 있다면 수정을 해볼 만 하다.

결과 및 느낌