MySQL 성능 잠금 해제: 쿼리 캐시 최적화 마스터하기

쿼리 캐시란 무엇입니까?

쿼리 캐시는 SELECT 쿼리 결과를 캐시하여 데이터베이스 성능을 향상시키도록 설계된 MySQL의 기능입니다. 이전에 실행된 쿼리가 다시 실행되면 MySQL은 데이터베이스에 대해 결과를 다시 실행하는 대신 이 캐시에서 결과를 신속하게 가져올 수 있습니다. 이를 통해 데이터 검색 속도가 빨라질 뿐만 아니라 데이터베이스의 로드도 줄어들어 일관된 매개변수로 자주 실행되는 쿼리에 매우 효율적입니다.

쿼리 캐시 작동 방식

쿼리가 실행될 때마다 MySQL은 먼저 쿼리 캐시를 조사하여 동일한 쿼리의 결과가 이전에 저장되었는지 확인합니다. 일치하는 항목이 있으면 MySQL은 일반적인 쿼리 실행 프로세스를 우회하고 캐시된 결과를 직접 제공합니다. 시간이 많이 소요되는 쿼리 처리 및 디스크 액세스 단계를 피하고 대신 메모리 액세스 속도를 활용하므로 훨씬 더 빠릅니다.

이제 중요한 쿼리 캐시 변수인 query_cache_type, query_cache_size, query_cache_limit 및 query_cache_min_res_unit과 이것이 MySQL 성능에 미치는 영향에 대해 살펴보겠습니다.

1. 쿼리_캐시_유형

MySQL의 query_cache_type 변수는 쿼리 캐시의 활성화, 비활성화 또는 요청 시에만 작동하도록 설정 여부를 제어합니다.

query_cache_type 사용법

쿼리 캐시는 클라이언트에 전송된 해당 결과와 함께 SELECT 문의 텍스트를 저장합니다. 나중에 동일한 명령문이 수신되면 서버는 해당 명령문을 다시 구문 분석하고 실행하는 대신 캐시에서 결과를 검색합니다. 쿼리 캐시를 활성화하면 자주 실행되는 쿼리를 다시 실행하는 대신 메모리에서 제공함으로써 결과를 얻는 데 걸리는 시간을 크게 줄일 수 있습니다.

MySQL의 query_cache_type 변수를 다른 값으로 설정하면 쿼리 캐시의 작동 방식이 결정됩니다.

• 0(OFF) – query_cache_size 바이트의 버퍼가 여전히 할당되어 있지만 쿼리 캐시를 비활성화합니다.

• 1(ON) – 쿼리에 SQL_NO_CACHE가 지정되지 않은 한 모든 SELECT 쿼리에 대해 쿼리 캐시를 활성화합니다.

• 2(DEMAND) – SQL CACHE 절을 명시적으로 사용하는 쿼리에 대해서만 쿼리 캐시를 활성화합니다.

query_cache_type 구성

Query_cache_type은 서버가 실행 중일 때 오프라인 또는 온라인으로 구성할 수 있습니다. 테스트를 위해 구성은 온라인으로 선호될 수 있습니다. 서버가 다시 시작되면 query_cache_type이 되돌아갑니다.

명령줄 구성:
mysqld> 전역 query_cache_type 설정 = XX

XX를 데이터베이스 요구 사항에 맞는 값으로 바꾸세요. 변수가 변경되었는지 확인하려면:

mysqld> 'query_cache_type'과 같은 전역 변수 표시

구성 파일:
[mysqld]
query_cache_type = XX

XX를 데이터베이스 요구 사항에 맞는 값으로 바꾸세요. MySQL 서버를 다시 시작하세요.

query_cache_type 고려 사항

일반적인 권장 사항에 따르면 데이터 변경이 자주 발생하지 않지만 읽기가 빈번한 환경에서는 query_cache_type을 1(ON)로 설정해야 합니다. 캐시 유지 관리에 따른 오버헤드가 이점보다 클 수 있는 매우 동적인 환경에서는 0(끄기)으로 설정합니다.

쿼리 캐시를 무분별하게 활성화해도 항상 성능 이점을 얻을 수 있는 것은 아니며 일부 시나리오에서는 성능이 저하될 수도 있습니다. query_cache_type을 구성할 때 다음 요소를 고려하십시오.

• 쿼리 캐시 크기 – 캐시가 클수록 더 많은 쿼리 결과를 저장할 수 있지만 더 많은 메모리가 필요합니다.

• 쿼리 패턴 – 결과가 자주 변경되거나 결과 집합이 큰 쿼리는 캐싱의 이점을 얻지 못할 수 있습니다.

• 캐시 무효화 – 캐시된 테이블을 업데이트, 삽입 또는 삭제하면 해당 캐시 항목이 무효화되어 캐시 변동이 발생합니다.

• 동시성 – 쿼리 캐시는 경합 문제로 인해 동시 작업량이 많은 작업에는 적합하지 않습니다.

• MySQL 버전 – 쿼리 캐시 기능은 MySQL 5.7에서 더 이상 사용되지 않고 MySQL 8.0에서 제거되었습니다. 이 기능에는 제한이 있고 멀티 스레드 환경에서 경합을 일으킬 수 있기 때문입니다.

2. 쿼리_캐시_크기

캐시된 쿼리 결과를 저장하기 위해 할당되는 메모리 양을 지정합니다. 한 번에 얼마나 많은 결과를 캐시할 수 있는지 결정하는 주요 요소입니다.

query_cache_size 사용법

query_cache_size 변수는 쿼리 캐시에 할당되는 메모리 양을 결정합니다. 이 값은 워크로드의 특성과 사용 가능한 메모리 리소스에 따라 조정되어야 합니다.

• 작은 결과 세트 – 애플리케이션이 작은 결과 세트를 반환하는 쿼리를 자주 실행하는 경우 더 큰 쿼리 캐시 크기가 도움이 될 수 있습니다. 이렇게 하면 더 많은 쿼리를 캐시에 저장할 수 있어 쿼리 실행 필요성이 줄어듭니다.

• 자주 동일한 쿼리 – 동일한 쿼리가 반복적으로 실행되는 시나리오에서 query_cache_size를 늘리면 이러한 쿼리와 해당 결과를 캐시하여 성능을 향상시킬 수 있습니다.

• 쿼리 캐시 적중률 – 쿼리 캐시 적중률을 모니터링하면 캐시의 효율성에 대한 통찰력을 얻을 수 있습니다. 적중률이 낮은 경우 query_cache_size를 늘리면 캐시 효율성을 높이는 데 도움이 될 수 있습니다.

query_cache_size 구성

Query_cache_size는 서버가 실행 중일 때 오프라인 또는 온라인으로 구성할 수 있습니다. 테스트를 위해 구성은 온라인으로 선호될 수 있습니다. 서버가 다시 시작되면 query_cache_size가 원래대로 돌아갑니다.

명령줄 구성:
mysqld> 전역 query_cache_size 설정 = XX

XX를 데이터베이스 요구 사항에 맞는 값으로 바꾸세요. 변수가 변경되었는지 확인하려면:

mysqld> 'query_cache_size'와 같은 전역 변수 표시

구성 파일:
_[mysqld]
query_cache_size _= XX

XX를 데이터베이스 요구 사항에 맞는 값으로 바꾸세요. MySQL 서버를 다시 시작하세요.

query_cache_size 고려 사항

query_cache_size는 사용 가능한 메모리와 워크로드의 특성에 따라 설정되어야 합니다. 너무 크게 설정하면 메모리가 소모될 수 있으며, 너무 작게 설정하면 효율성이 제한될 수 있습니다.

캐시 활용도(적중 횟수와 삽입 횟수)를 모니터링하면 적절한 크기를 결정할 수 있습니다. 64MB~128MB 등 적당한 크기로 시작하여 성능과 사용 가능한 시스템 메모리에 따라 조정하세요.

query_cache_size를 구성할 때 다음 요소를 고려하세요.

• 쿼리 패턴 – 결과가 자주 변경되거나 결과 집합이 큰 쿼리는 캐싱의 이점을 얻지 못할 수 있습니다.

• 캐시 무효화 – 캐시된 테이블을 업데이트, 삽입 또는 삭제하면 해당 캐시 항목이 무효화되어 캐시 변동이 발생합니다.

• 동시성 – 쿼리 캐시는 경합 문제로 인해 동시 작업량이 많은 작업에는 적합하지 않습니다.

• MySQL 버전 – 멀티 스레드 환경의 제한과 경합으로 인해 쿼리 캐시 기능은 MySQL 5.7에서 더 이상 사용되지 않으며 MySQL 8.0에서는 제거되었습니다.

3. query_cache_limit

이 변수는 캐시할 수 있는 개별 쿼리 결과의 최대 크기를 설정합니다. 이는 대규모 쿼리가 캐시 공간을 과도하게 소모하는 것을 방지합니다.

query_cache_limit 사용량

쿼리 결과가 query_cache_limit를 초과하면 결과가 캐시되지 않습니다. 이렇게 하면 지나치게 크거나 리소스를 많이 사용하는 쿼리가 자주 재사용되지 않을 수 있는 결과로 캐시를 채우는 것을 방지할 수 있습니다. query_cache_limit에 적절한 값을 설정하면 더 작고 자주 사용되는 쿼리 결과만 캐시되어 메모리 사용을 최적화할 수 있습니다.

query_cache_limit 구성

Query_cache_limit는 서버가 실행 중일 때 오프라인 또는 온라인으로 구성할 수 있습니다. 테스트를 위해 구성은 온라인으로 선호될 수 있습니다. 서버가 다시 시작되면 query_cache_limit가 되돌아갑니다.

명령줄 구성:
mysqld> 전역 query_cache_limit 설정 = XX

XX를 데이터베이스 요구 사항에 맞는 값으로 바꾸세요. 변수가 변경되었는지 확인하려면:

mysqld> 'query_cache_limit'와 같은 전역 변수 표시

구성 파일:
[mysqld]
query_cache_limit = XX

XX를 데이터베이스 요구 사항에 맞는 값으로 바꾸세요. MySQL 서버를 다시 시작하세요.

query_cache_limit 고려 사항

일반적으로 쿼리의 성격과 사용 가능한 캐시 크기에 따라 query_cache_limit를 1MB에서 4MB 사이로 설정하는 것이 좋습니다. query_cache_limit를 너무 낮게 설정하면 유용한 쿼리 결과가 캐시에서 제외되어 쿼리 캐시의 효율성이 떨어질 수 있다는 점에 유의하는 것이 중요합니다.

4. query_cache_min_res_unit

MySQL의 query_cache_min_res_unit 변수는 쿼리 캐시에 의해 할당된 블록의 최소 크기(바이트)를 결정합니다. 이 설정은 캐시된 결과의 세분성을 제어하여 쿼리 캐시의 효율성에 영향을 미칩니다.

query_cache_min_res_unit 사용법

쿼리 결과가 쿼리 캐시에 저장되면 일정량의 메모리를 차지합니다. query_cache_min_res_unit 변수는 이러한 캐시된 결과에 할당된 메모리 블록의 최소 크기를 정의합니다. 쿼리 결과가 이 값보다 작은 경우에도 query_cache_min_res_unit에 정의된 최소 크기를 차지합니다.

query_cache_min_res_unit 구성

Query_cache_min_res_unit은 서버가 실행 중일 때 오프라인 또는 온라인으로 구성할 수 있습니다. 테스트를 위해 구성은 온라인으로 선호될 수 있습니다. 서버가 다시 시작되면 query_cache_min_res_unit이 되돌려집니다.

명령줄 구성:
mysqld> 전역 query_cache_min_res_unit 설정 = XX

XX를 데이터베이스 요구 사항에 맞는 값으로 바꾸세요. 변수가 변경되었는지 확인하려면:

mysqld> 'query_cache_min_res_unit'과 같은 전역 변수 표시
_
구성 파일:
_[mysqld]
query_cache_min_res_unit = XX

XX를 데이터베이스 요구 사항에 맞는 값으로 바꾸세요. MySQL 서버를 다시 시작하세요.

query_cache_min_res_unit 고려 사항

query_cache_min_res_unit을 구성하려면 메모리 소비와 캐싱 효율성의 균형을 맞추는 적절한 값으로 변수를 설정해야 합니다. 워크로드 내 쿼리 결과의 평균 크기를 기준으로 값을 선택해야 합니다.

• 값이 작을수록 메모리 사용량이 더 효율적일 수 있지만 캐시 항목이 늘어나 오버헤드가 증가할 수 있습니다.

• 반대로, 값이 클수록 캐시 항목 수가 줄어들 수 있지만 쿼리 결과가 작을수록 메모리가 낭비될 수 있습니다.

워크로드를 분석하여 쿼리 결과의 평균 크기를 확인하세요. 이 분석을 기반으로 query_cache_min_res_unit 값을 조정하여 메모리 소비와 캐싱 효율성 간의 균형을 유지하세요. 대부분의 설정에서 이 크기는 16MB에서 64MB 사이입니다.

4단계로 쿼리 캐시 활성화 및 조정

쿼리 캐시는 MySQL 5.7.20부터 더 이상 사용되지 않으며 MySQL 8.0에서는 완전히 제거되었습니다. 귀하의 MySQL 버전이 여전히 query_cache를 사용할 수 있는 경우 기본적으로 비활성화되어 있으므로 활성화해야 합니다. MySQL, MariaDB 또는 Percona에서 쿼리 캐시를 활성화하고 구성하려면 일반적으로 서버의 my.cnf 또는 my.ini 파일에 액세스해야 합니다. 단계별 접근 방식은 다음과 같습니다.

1. 쿼리 캐시 활성화 – query_cache_type을 1 또는 2로 설정합니다. query_cache_type 또는 query_cache_size를 0으로 설정하면 항상 캐시가 비활성화됩니다. 선택적 캐싱(대부분의 사용 사례에 권장)의 경우 다음을 사용합니다.

query_cache_type = 1

1. 캐시 크기 설정 – query_cache_size를 정의합니다. 시작점은 사용 가능한 총 메모리의 10-20%일 수 있지만 이를 위해서는 워크로드에 따라 조정이 필요합니다.

query_cache_size = 100M

3. 결과 크기 제한 정의 – 저장된 결과의 크기를 제어하도록 query_cache_limit를 구성합니다. 일반적인 쿼리 크기에 따라 몇 메가바이트부터 시작할 수 있습니다.

query_cache_limit = 2M

4. 최소 결과 단위 조정 – 필요에 따라 query_cache_min_res_unit을 수정합니다. 이를 기본값 아래로 줄이면 특히 작은 쿼리가 많이 예상되는 경우 캐시 공간을 보다 효율적으로 활용하는 데 도움이 될 수 있습니다.

query_cache_min_res_unit = 512

상태 확인을 통해 쿼리 캐시 모니터링

QCache 조각화는 MySQL의 쿼리 캐시 성능을 나타내는 주요 지표입니다. 이 기능은 반복되는 요청이 쿼리를 다시 실행할 필요 없이 신속하게 처리될 수 있도록 SELECT 쿼리의 결과를 저장하여 성능을 향상시키도록 구축되었습니다. 그러나 시간이 지남에 따라 쿼리 캐시가 단편화되어 효율성이 저하될 수 있습니다.

QCache 조각화 계산

QCache 조각화 계산 방법에 대한 정보와 단계별 지침이 포함된 포괄적인 상태 점검 문서를 확인하세요.

QCache Fragmentation과 QcacheDeleteRate를 계산한 후에는 결과를 해석해야 합니다. 이상적으로는 QCache Fragmentation은 10 미만, QcacheDeleteRate는 20 미만이어야 합니다.

권장 조치

QCache 조각화가 높으면 조각화를 줄이기 위해 쿼리 캐시의 크기를 조정해야 할 수도 있습니다. QcacheDeleteRate가 높으면 쿼리 캐시의 크기를 늘리거나 쿼리를 최적화하여 INSERT 수를 줄여야 할 수도 있습니다.

쉽게 쿼리 캐시를 최적화하세요

MySQL 쿼리 캐시를 조정하려면 메모리 사용량 관리부터 쿼리 시간 단축까지 데이터베이스 성능을 최적화하기 위한 여러 설정을 조정해야 합니다. 여기에서 논의된 변수는 좋은 기초를 형성하지만 효과적인 관리에는 실제 시스템 로드 및 성능을 기반으로 지속적인 모니터링과 업데이트가 필요합니다.

이 프로세스를 단순화하려면 이러한 조정을 자동화하는 Releem과 같은 강력한 관리 도구를 사용하는 것이 좋습니다. 이러한 도구는 시스템 성능을 지속적으로 모니터링하고 query_cache 설정을 실시간으로 동적으로 업데이트할 수 있습니다.

이를 통해 Releem이 쿼리 캐시 최적화의 복잡한 작업을 처리하는 동안 더 넓은 목표에 집중할 수 있습니다.

위 내용은 MySQL 성능 잠금 해제: 쿼리 캐시 최적화 마스터하기의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!