MySQL 성능을 최적화하는 5가지 방법

(1) 연결

연결은 일반적으로 웹 서버에서 이루어집니다. 아래에는 연결과 관련된 일부 매개변수 및 설정 방법이 나와 있습니다.

1. max_connections

이것은 웹 서버에서 허용하는 최대 연결 수입니다. 각 연결은 세션 메모리를 사용한다는 점을 기억하세요. (세션 메모리는 기사 후반부에서 다룹니다.)

2.max_packet_allowed

최대 패킷 크기는 일반적으로 하나의 큰 블록으로 반환해야 하는 가장 큰 데이터 세트의 크기와 동일합니다. 더 커져라.

3. aborted_connects

시스템 상태 카운터를 확인하여 숫자가 증가하면 연결 시 오류가 발생했음을 의미합니다.

4. thread_cache_size

MySQL에서 연결을 열고 닫는 것은 다른 데이터베이스와 달리 저렴하고 빠르기 때문에 인바운드 연결은 MySQL에서 새 스레드를 생성합니다. Oracle과 비슷하지만 스레드를 미리 생성하면 시간이 절약되지 않으므로 MySQL 스레드 캐시가 필요합니다.

만약 성장하고 있다면 생성된 스레드를 주의깊게 살펴보고 스레드 캐시를 더 크게 만드세요. thread_cache_size가 2550 또는 100이면 메모리 사용량이 많지 않습니다.

(2) 쿼리 캐시

MySQL의 캐시된 쿼리에는 두 가지 구문 분석 쿼리 계획이 포함되어 있으며, 기본 테이블 데이터 또는 구조가 변경되면 반환되는 데이터 세트가 생성됩니다. 쿼리 캐시의 항목을 무효화합니다.

1. query_cache_min_res_unit

MySQL 매개변수에 있는 query_cache_min_res_unit의 쿼리 캐시에 있는 블록은 이 크기를 기준으로 할당됩니다. 쿼리 캐시의 평균 크기를 계산하고 이를 설정합니다. MySQL은 쿼리 캐시를 보다 효율적으로 사용하고 더 많은 쿼리를 캐시하며 메모리 낭비를 줄입니다.

2. query_cache_size

이 매개변수는 쿼리 캐시의 전체 크기를 설정합니다.

3. query_cache_limit

이 매개변수는 MySQL에게 이 크기보다 큰 쿼리를 삭제하도록 지시합니다. 일반적으로 대규모 보고서에 대한 통계를 수행하기 위해 일괄 처리를 실행하는 등의 대규모 쿼리는 비교적 드뭅니다. 이러한 큰 결과 세트는 쿼리 캐시를 채워서는 안 됩니다.

qcache 적중률 = qcache_hits / (qcache_hits + com_select)

SQL> 'qcache%'와 같은 상태 표시를 사용하세요.

SQL> like 'com_%';

다음 변수를 찾으세요.

평균 쿼리 크기 = (query_cache_size - qcache_free_memory)/qcache_queries_in_cache

SQL> 'query%'와 같은 표시 변수를 사용하세요.

qcache_* 상태 변수

SQL> show status like 'qcache%';

query_cache_size 값을 가져옵니다.

(3) 임시 테이블

메모리 속도가 상당히 빠르므로 모든 정렬 작업이 메모리에서 수행되기를 바랍니다. 결과 메모리 정렬을 달성하려면 더 작게 설정하거나 변수를 더 크게 설정하십시오.

tmp_table_size

max_heap_table_size

MySQL에서 임시 테이블을 생성할 때마다 디스크에 임시 테이블을 구축할 때를 제외하고는 이 두 변수의 최소값을 임계값으로 사용합니다. 테이블 외에도 많은 세션이 생성되며 이러한 세션은 제한된 리소스를 점유하므로 이러한 매개 변수를 더 높게 설정하는 것보다 쿼리를 조정하는 것이 좋습니다. 동시에 BLOB 또는 TEXT가 있는 테이블에 유의해야 합니다. 필드 유형은 디스크에 직접 기록됩니다. MySQL 양방향 복제 기술에 대한 심층 설명

(4) 세션 메모리

MySQL의 각 세션에는 고유한 메모리가 있습니다. SQL 쿼리. 따라서 필요한 만큼 크게 만들고 싶습니다. 하지만 동시에 데이터베이스의 일관된 세션 수의 균형을 맞춰야 합니다. 여기서 다소 암울한 점은 MySQL이 필요에 따라 캐시를 할당하므로 캐시를 추가하고 세션 수를 곱할 수 없다는 것입니다. 그러면 MySQL의 일반적인 사용량보다 훨씬 더 큰 추정치가 나옵니다. 모범 사례는 MySQL을 시작하고 모든 세션을 연결한 다음 계속해서 최상위 세션의 VIRT 열에 집중하는 것입니다. mysqld 행의 수는 일반적으로 상대적으로 안정적으로 유지됩니다. 이는 실제 전체 정적 MySQL 메모리 영역을 뺀 값입니다. 모든 세션의 실제 메모리를 얻은 다음 세션 수로 나누어 평균을 얻습니다.

1. read_buffer_size

캐시는 지속적으로 블록을 검색합니다. 이 캐시는 MyISAM 테이블뿐만 아니라 스토리지 엔진 전반에 걸쳐 있습니다.

2.sort_buffer_size

정렬 버퍼의 크기는 1M~2M로 설정하는 것이 가장 좋으며, 특정 쿼리에 대해 더 높은 값을 설정하려면 세션에서 설정하는 것이 좋습니다.

3.join_buffer_size

공동 쿼리 실행을 위해 할당되는 버퍼의 크기를 1M~2M 크기로 설정한 후, 세션마다 필요에 따라 개별적으로 설정합니다.

4. read_rnd_buffer_size

는 작업별 정렬 및 정렬에 사용되며, 1M로 설정한 후 세션에서 더 큰 값으로 설정하는 것이 가장 좋습니다.

(5) 느린 쿼리 로그

느린 쿼리 로그는 MySQL의 매우 유용한 기능입니다.

1.log_slow_queries

my.cnf 파일의 MySQL 매개변수에 log_slow_queries 매개변수를 설정하고 on으로 설정합니다. 기본적으로 MySQL은 파일을 데이터 디렉터리에 저장하며 파일 이름은 "hostname-slow" 형식으로 지정됩니다. .log". 그러나 이 옵션을 설정할 때 이름을 지정할 수도 있습니다.

2.long_query_time

기본값은 10초이며, 값 범위는 1부터 on으로 설정하면 데이터베이스가 시작됩니다. 기본적으로 꺼져 있습니다. 5.1.21 및 Google 패치 버전부터 이 옵션은 마이크로초 단위로 설정할 수 있습니다. 이는 놀라운 기능입니다. 1초 이상 걸리는 모든 쿼리를 제거하면 조정이 매우 성공적이며 이는 문제가 있는 SQL을 제거하는 데 도움이 될 수 있기 때문입니다. 큰 문제가 됩니다.

3. log_queries_not_using_indexes

이 옵션을 켜는 것이 좋습니다. 이 옵션은 실제로 모든 행을 반환하는 쿼리를 기록합니다.

요약

MySQL 매개변수 설정의 5가지 주요 범주를 소개했습니다. 일반적으로 이러한 매개변수는 데이터베이스 성능 튜닝 및 오류 진단을 수행할 때 여전히 매우 유용합니다.