MySQL 버퍼 풀 사이즈 조정으로 count 쿼리 성능 개선(37초 -> 0.026초)

api_request_history에 50만 개의 데이터를 넣어두고

SELECT * FROM api_request_history ORDER BY request_date DESC;

해당 쿼리를 날려보자. request_date에 인덱스가 걸려있는 상태다.

 

1.32초면 나쁘진 않군요!

 

SELECT count(*) FROM api_request_history;

이번엔 count를 날려봅시다.

아? 무려 30초가 넘어서 연결이 끊겼네요

 

왜 count보다 전체 데이터 조회가 성능이 빠른 걸까? 물론 index가 걸려있다지만 이렇게 차이가 심한 정도인가?

일단 count를 통해 full scan을 때리기 때문에 속도가 느린 것이 문제다.

 

아 뭔가 QueryDSL 안 쓰고 해결해보려고 했는데, 대부분 count 성능 개선하려고 QueryDSL을 기본으로 깔고 간다. 

 

그래서 이전에 썼던 방식대로 QueryDSL 도입.

        // 전체 요청 이력 수 조회를 위한 쿼리 생성
        long total = queryFactory
                .select(apiRequestHistory.count())
                .from(apiRequestHistory)
                .fetchOne();

        // Page 객체 생성 및 반환
        return new PageImpl<>(dtoList, pageable, total);

total의 개수를 리턴하기 위해 fetchCount()는 곧 없어진다 해서 위의 쿼리로 실행.

어림없는 볼

 

이것 저것 조언을 구하던 중 디비 설정이 문제일 것 같다는 힌트를 남겨주셨다.

 

그래서 뭔가 이것저것 많이 건드려봤다.

 

1. 원래 날리던 쿼리에 EXPLAIN 붙여서 실행계획 확인

EXPLAIN SELECT count(*) FROM api_request_history;

이렇게 앞에 EXPLAIN을 붙여 실행 계획을 확인해 봤다.

인덱스도 사용은 하고 있고, 딱히 문제가 보이지 않는다.

 

2. InnoDB 버퍼 풀 크기 확인

show variables like '%innodb_buffer%';

해당 명령어로 확인해 보았다.

난 이게 정상인 줄 알았다. 여태 MYSQL을 건든 적이 없으니.

근데 대부분 128MB가 정상이라고 하네??? (문제점을 찾았다)

(8388608 / 1024) / 1024를 하면 8이 나온다. 즉 나는 버퍼 풀 사이즈가 8MB였던 것.

 

그러면 이게 작으면 뭐가 문제인가?

 

Buffer Pool

My SQL의 경우 InnoDB 엔진을 사용한다. 

• Innodb에서 테이블이나 인덱스 데이터를 캐시 하는 메모리 영역을 말한다.
• 데이터를 메모리에서 직접 액세스 하기에 I/O 작업 시 속도가 빠르다.
• 일반적으로 서버 메모리의 약 50%를 설정한다.(내 컴퓨터는 16GB 메모리이므로 8GB를 보통 쓰나 보다)

 

즉 퍼버 풀의 사이즈가 커야 더 많은 데이터와 인덱스를 메모리에 캐싱하게 되므로, 조회 성능이 빨라지게 된다!!

나는 8MB니깐 당연히 느릴 수밖에 없었던 것. 
(이걸 몰라서 QueryDSL을 적용하고, 이것저것 알아보면서 9시간을 소비했다)

우선 버퍼 풀의 사이즈를 128MB로 늘려주기 위해 my.ini 파일을 메모장 관리자 권한으로 열어주었다.

해당 부분을 128로 업! 시켜주었다.

그다음 mysql을 껐다가 켜주면 된다.

cmd를 관리자 권한으로 연 다음 net stop MySQL80 명령어 실행(안되면 mysql로도 해보기)

그 다음 net start MySQL80을 해주면 동작이 된다.

사이즈가 잘 늘어난 것을 확인할 수 있다.

 

이후 다시 페이징 쿼리를 날려보면

37초 -> 0.023초로 약 1/1000으로 줄일 수 있었다.

 

이거에 9시간을 쏟았다 어우...