MySQL의 캐싱

7.1 DB 측 캐싱

MySQL은 테이블 작업 시 확장성이 뛰어난 알고리즘을 사용하므로 적은 양의 메모리로도 MySQL을 실행할 수 있습니다. 당연히 더 나은 성능을 위해서는 더 많은 RAM이 필요합니다.

현재 설정을 보려면 데이터베이스에 연결하십시오.

#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

먼저 캐싱이 활성화되어 있는지 확인하십시오. 변하기 쉬운:

• query_cache_typeON(1) 또는 DEMAND(2)여야 합니다.
• query_cache_limit- 캐시에 들어갈 결과의 최대 크기를 결정합니다.
• query_cache_size제로와 달라야 합니다. DEMAND를 사용하는 경우 지시어가 포함된 요청만 캐시됩니다.SQL_CACHE;
• query_cache_min_res_unit캐시된 쿼리의 결과를 저장하기 위해 할당된 메모리 블록의 최소 크기입니다. query_cache_min_res_unitMySQL은 캐시를 하나의 큰 메모리 청크에 저장하지 않고 대신 요청 시 최소 크기(기본적으로 =4KB)로 블록을 할당합니다 . 이러한 마지막 블록은 데이터 크기로 잘리고 나머지 메모리는 해제됩니다.

캐싱의 효과는 서버가 요청을 수신할 때 요청의 해시가 캐시에 있는지 확인하는 것입니다. 해시가 일치하면 서버는 요청 구문 분석, 최적화 등을 수행하지 않고 즉시 결과를 반환합니다. 오버헤드 - 캐싱 메커니즘과 함께 - 캐시 탐색, 쿼리 결과를 캐시에 쓰기 등

그리고 캐시에 작은 요청이 많으면 많은 수의 사용 가능한 블록으로 인해 메모리 조각화가 발생할 수 있습니다. 그리고 이로 인해 메모리 부족으로 인해 캐시된 항목이 삭제됩니다. 이 경우 의 값을 줄이는 것이 합리적입니다 query_cache_min_res_unit. 대부분의 쿼리가 큰 결과를 생성하는 경우 이 설정을 늘리면 성능이 향상될 수 있습니다.

효과를 평가해 봅시다. 캐시 적중 카운터가 어떻게 변경되는지(Qcahe_hits), 메모리 부족으로 인해 유효하지 않은 것으로 선언된 요청 수(Qcache_lowmem_prunes), SELECT 유형의 총 요청 수(캐시된 요청만)를 살펴봅니다.

#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 현재 캐시 상태

쿼리 캐시를 모니터링하려면 다음이 사용됩니다 SHOW STATUS.

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blocks캐시에 얼마나 많은 여유 블록이 있는지 보여줍니다(캐시된 요청이 증가함에 따라 감소함).
• Qcache_total_blocks- 점유된 블록의 수
• Qcache_free_memory- 캐싱을 위한 여유 "사용 가능한" 메모리를 표시합니다.
• Qcache_hits- 실제로 데이터베이스에 액세스하지 않고 캐시에서 가져온 결과인 요청 수
• Qcache_inserts- 캐시에 추가된 요청 수
• Qcache_lowmem_prunes- 메모리 부족으로 인해 캐시에서 제거된 요청 수
• Qcache_not_cached- 시간 관리 기능 등의 사용으로 인해 캐시에 기록되지 않은 요청 수
• Qcache_queries_in_cache- 캐시에 있는 요청 수.

총 SELECT 쿼리 수를 볼 수 있습니다.

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 캐싱 효율성

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

현재 값은 다음과 같이 볼 수 있습니다.

SHOW VARIABLES LIKE 'query_cache_size';

다시 질문이 생깁니다. 적절한 값을 선택하는 방법query_cache_size?

이것은 도움이 될 것입니다 Qcache_lowmem_prunes. 이 변수는 새 요청을 캐시해야 하기 때문에 캐시에서 제거된 요청 수를 저장합니다. Qcache_lowmem_prunes약간만 증가하는 캐시 크기를 위해 노력할 필요가 있습니다 . 이를 위해 시간당 값의 차이 Qcache_lowmem_prunes와 같은 시간에 mysql이 받은 요청 수를 비교하는 것이 좋습니다.

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (10분 동안의 트래픽 양) * 1.2"

이렇게 하면 10분 동안 요청을 캐시하고 캐시 조각화 및 추가 캐싱 예약을 위한 추가 20% 메모리를 제공합니다.

Bytes_sent 변수를 사용하여 요청에 대한 응답의 수와 평균 볼륨을 각각 계산할 수 있습니다.

그래서 우리 는 가치를 높였습니다. 그 후에는 가치 query_cache_size에주의를 기울여야합니다 . MySQL은 캐시를 블록에 저장합니다. 1개의 요청에 대해 2개의 블록이 필요합니다. 하나는 요청 텍스트 자체용이고 다른 하나는 결과용입니다.Qcache_total_blocksQcache_free_blocksQcache_queries_in_cache

값에서 테이블을 고려하면Qcache%

총 캐시 블록 수Qcache_total_blocks – 28

이제 요청 6이 캐시됩니다. 즉, 6 * 2 = 12 블록이 사용 중임을 의미합니다.

무료 블록 Qcache_free_blocks – 10. 유휴 가 많을수록 Qcache_free_blocks캐시의 "조각화" 정도가 커집니다.

대부분의 쿼리에 소량의 결과 데이터가 있는 경우 query_cache_min_res_unit기본적으로 4KB인 최소 캐시 블록 크기를 줄이는 것이 좋습니다.

대부분의 요청이 많은 데이터를 반환하는 경우 캐시 블록의 크기를 늘릴 가치가 있습니다.

가장 중요한 것은 최소값을 달성하는 것입니다 Qcache_free_blocks.

카운터 Qcache_not_cached가 크면 변수를 늘릴 수 있습니다. query_cache_limit이렇게 하면 제한을 늘리고 "적합하지 않은" 쿼리 결과를 캐시할 수 있습니다.

다음 구성 변수는 쿼리 캐시 사용을 담당합니다.

• query_cache_size— 쿼리 캐시의 크기. query_cache_size = 0캐시 사용을 비활성화합니다.
• query_cache_limit- 캐시에 저장된 최대 샘플의 크기
• query_cache_wlock_invalidate- 데이터가 속한 테이블이 읽기용으로 잠긴 경우 데이터를 캐시에서 가져올지 여부를 결정합니다.
=

mysql 쿼리 캐싱을 활성화하려면 my.cnf ([mysqld] 섹션) 에 다음 줄을 추가하기만 하면 됩니다 .

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

그리고 서비스를 다시 시작하십시오.

7.4 캐시가 사용되지 않는 경우

캐시되지 않음:

• 요청SQL_NO_CACHE
• 준비된 쿼리(Prepared statements);
• 외부 쿼리의 하위 쿼리인 쿼리입니다.
• 저장 프로시저 및 함수 내부의 쿼리
• 함수를 사용하는 쿼리:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), 인수 없이 , , , , , , , DATABASE (), , , _ _ENCRYPT ()FOUND_ROWS ()GET_LOCK () LAST_INSERT_ID ()LOAD_FILE ()MASTER_POS_WAIT ()NOW ()RAND ()RELEASE_LOCK ()SLEEP ()SYSDATE ()UNIX_TIMESTAMP ()USER ()UUID ();

• 저장된 함수, 사용자 변수 또는 mysql 시스템 데이터베이스 또는 INFORMATION_SCHEMA의 참조 테이블을 사용하여 쿼리합니다.
• • 다음 형식의 요청:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• 임시 테이블이 있는 쿼리 또는 테이블을 전혀 사용하지 않는 쿼리
• 경고 생성 요청(경고)

다음 명령을 사용하여 캐시 조각 모음을 수행할 수 있습니다.

mysql>flush query cache;

지우기 - 명령:

mysql>flush query cache;

가장 중요한

기본 설정으로 프로덕션 환경에서 작업하지 마십시오.. 이로 인해 대부분의 서버 리소스가 사용되지 않습니다. 적절한 MySQL 튜닝은 데이터베이스 성능을 몇 배로 향상시킬 수 있습니다. 이렇게 하면 애플리케이션 속도가 빨라질 뿐만 아니라 무거운 부하에도 대처할 수 있습니다.