Linux의 높은 부하에서 mysql 데이터베이스를 철저하게 최적화합니다.

동시에 온라인 방문 횟수도 계속 증가하고 있습니다. 1G 메모리를 사용하는 서버에 심각한 부하가 걸리면 매일 충돌이 발생하거나 때때로 서버가 정지되는 문제가 절반 이상 나를 괴롭혔습니다. MySQL은 확장성이 매우 뛰어난 알고리즘을 사용하므로 일반적으로 더 적은 메모리로 실행하거나 더 나은 성능을 얻기 위해 MySQL에 더 많은 메모리를 제공할 수 있습니다.

mysql을 설치한 후 구성 파일은 /usr/local/mysql/share/mysql 디렉토리에 있어야 합니다. my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf를 포함한 여러 구성 파일이 있습니다. , 트래픽 흐름이 다른 웹 사이트와 구성이 다른 서버 환경에는 물론 다른 구성 파일이 필요합니다.

일반적인 상황에서는 my-medium.cnf 구성 파일이 대부분의 요구 사항을 충족할 수 있습니다. 일반적으로 구성 파일을 /etc/my.cnf에 복사하고 이 구성 파일만 수정하면 됩니다. mysqladmin 변수 Extended-status –u를 사용하면 됩니다. root –p는 현재 매개변수를 볼 수 있습니다. 가장 중요한 세 가지 구성 매개변수, 즉 key_buffer_size, query_cache_size, table_cache 가 있습니다.

key_buffer_size는 MyISAM 테이블에서만 작동합니다.

key_buffer_size는 인덱스 처리 속도, 특히 인덱스 읽기 속도를 결정하는 인덱스 버퍼의 크기를 지정합니다. 일반적으로 16M으로 설정했지만, 약간 큰 사이트에서는 이 숫자가 충분하지 않습니다. Key_read_requests 및 Key_reads 상태 값을 확인하면 key_buffer_size 설정이 적절한지 알 수 있습니다. key_reads / key_read_requests 비율은 가능한 낮아야 하며, 최소한 1:100, 1:1000이 더 좋습니다(위 상태 값은 SHOW STATUS LIKE 'key_read%'를 사용하여 얻을 수 있습니다). 또는 phpmyadmin을 설치했다면 서버 실행 상태를 통해 확인할 수 있습니다. 저자는 phpmyadmin을 사용하여 mysql을 관리할 것을 권장합니다. 다음 상태 값은 phpmyadmin을 통해 얻은 예제 분석입니다.

이 서버는 20일 동안 운영되었습니다.
key_buffer_size – 128M
key_read_requests - 650759289
key_reads - 79112
비율은 1:8000에 가깝고 건강상태는 매우 좋습니다.

key_buffer_size를 추정하는 또 다른 방법은 웹 사이트 데이터베이스에서 각 테이블의 인덱스가 차지하는 공간을 합산하는 것입니다. 이 서버를 예로 들어 보겠습니다. 여러 개의 큰 테이블의 인덱스를 합하면 테이블이 커짐에 따라 이 숫자가 증가합니다. 더 커져라.

4.0.1부터 MySQL은 쿼리 버퍼링 메커니즘을 제공합니다. 쿼리 버퍼링을 사용하여 MySQL은 SELECT 문과 쿼리 결과를 버퍼에 저장합니다. 앞으로는 동일한 SELECT 문(대소문자 구분)에 대해 결과를 버퍼에서 직접 읽습니다. MySQL 사용자 매뉴얼에 따르면 쿼리 버퍼링을 사용하면 최대 238%의 효율성을 얻을 수 있습니다.

다음 매개변수를 조정하면 query_cache_size 설정이 적절한지 여부를 알 수 있습니다.
Q캐시 삽입
Q캐시 적중
Qcache lowmem 자두
Qcache 프리 블록
Qcache 총 블록

Qcache_lowmem_prunes 값이 너무 크다면 버퍼링이 부족한 경우가 많다는 뜻이고, Qcache_hits 값이 너무 크다면 쿼리 버퍼가 매우 자주 사용된다는 뜻이다. Qcache_hits 값이 작으면 쿼리 반복률이 매우 높다는 의미입니다. 이 경우 쿼리 버퍼링을 사용하면 효율성에 영향을 미치므로 쿼리 버퍼링을 사용하지 않는 것이 좋습니다. 또한 SELECT 문에 SQL_NO_CACHE를 추가하면 쿼리 버퍼가 사용되지 않음을 명확하게 나타낼 수 있습니다.
Qcache_free_blocks, 값이 매우 크면 버퍼에 조각이 많다는 의미입니다. query_cache_type은 쿼리 버퍼링 사용 여부를 지정합니다.

나는 다음을 설정했다:
query_cache_size = 32M
query_cache_type= 1

다음 상태 값을 가져옵니다.
캐시 12737의 Qcache 쿼리는 현재 캐시된 항목 수를 나타냅니다.
Q캐시 삽입 20649006
Qcache가 79060095에 도달했습니다. 반복 쿼리 비율이 상당히 높은 것 같습니다.
Qcache lowmem prunes 617913 캐시가 너무 부족할 때가 많습니다.
Qcache가 캐시되지 않음 189896
Qcache 여유 메모리 18573912 현재 남은 캐시 공간
Qcache 사용 가능 블록 5328 이 숫자는 약간 크고 조각난 것 같습니다.
Qcache 총 블록 수 30953
메모리가 32M을 허용한다면 더 추가해야 합니다.

table_cache는 테이블 캐시의 크기를 지정합니다. MySQL이 테이블에 액세스할 때마다 테이블 버퍼에 공간이 있으면 테이블이 열리고 그 안에 배치되므로 테이블 내용에 더 빠르게 액세스할 수 있습니다. 피크 타임의 Open_tables 및 Opened_tables 상태 값을 확인하여 table_cache 값을 늘려야 하는지 판단할 수 있습니다. open_tables가 table_cache와 동일하고 open_tables가 증가하고 있는 경우 table_cache의 값을 늘려야 합니다(위의 상태 값은 SHOW STATUS LIKE 'Open%tables'를 사용하여 얻을 수 있음). table_cache는 무작정 큰 값으로 설정할 수 없습니다. 너무 높게 설정하면 파일 설명자가 부족하여 성능이 불안정해지거나 연결이 실패할 수 있습니다.

메모리가 1G인 머신의 경우 권장 값은 128-256입니다.

작성자 설정
table_cache = 256

다음 상태를 얻습니다.
오픈 테이블 256
오픈 테이블 9046
open_tables는 이미 table_cache와 동일하지만 서버 실행 시간에 비해 20일 동안 실행되었으며 open_tables의 값도 매우 낮습니다. 따라서 table_cache 값을 늘리는 것은 거의 쓸모가 없습니다. 6시간 동안 실행한 후 위의 값이 나타나면 table_cache를 늘리는 것을 고려해야 합니다.

바이너리 로그를 기록할 필요가 없으면 이 기능을 끄십시오. 이 기능을 끄면 문제가 발생하기 전의 데이터를 복원할 수 없습니다. 바이너리 로그에는 데이터를 업데이트하는 모든 명령문이 포함되어 있습니다. 목적은 데이터베이스를 가능한 한 최종 상태로 복원하는 데 사용하는 것입니다. 또한 동기 복제(Replication)를 수행하는 경우 수정 사항을 전송하려면 바이너리 로그도 사용해야 합니다.

log_bin은 로그 파일을 지정합니다. 파일 이름이 제공되지 않으면 MySQL은 기본 파일 이름 자체를 생성합니다. MySQL은 파일 이름 뒤에 숫자 인덱스를 자동으로 추가하고 서비스가 시작될 때마다 새로운 바이너리 파일을 재생성합니다. 또한, 인덱스 파일을 지정하려면 log-bin-index를 사용하고, 기록할 데이터베이스를 지정하려면 binlog-do-db를 사용하고, 기록하지 않을 데이터베이스를 지정하려면 binlog-ignore-db를 사용하십시오. 참고: binlog-do-db 및 binlog-ignore-db는 한 번에 하나의 데이터베이스만 지정합니다. 여러 데이터베이스를 지정하려면 여러 문이 필요합니다. 또한 MySQL은 모든 데이터베이스 이름을 소문자로 변경합니다. 데이터베이스를 지정할 때 모든 이름을 소문자로 사용해야 합니다. 그렇지 않으면 작동하지 않습니다.

이 기능을 끄려면 앞에 # 기호를 추가하면 됩니다.
#로그빈

느린 쿼리 로그 켜기(느린 쿼리 로그)

느린 쿼리 로그는 문제가 있는 쿼리를 추적하는 데 유용합니다. long_query_time을 확인한 모든 쿼리를 기록하며, 필요한 경우 인덱스를 사용하지 않고 기록한다. 다음은 느린 쿼리 로그의 예입니다.

느린 쿼리 로그를 활성화하려면 log_slow_queries, long_query_times 및 log-queries-not-using-indexes 매개변수를 설정해야 합니다.

log_slow_queries는 로그 파일을 지정합니다. 파일 이름이 제공되지 않으면 MySQL은 자체적으로 기본 파일 이름을 생성합니다. long_query_times는 느린 쿼리 임계값을 지정하며 기본값은 10초입니다. log-queries-not-using-indexes는 4.1.0 이후에 도입된 파라미터로, 인덱스를 사용하지 않는 쿼리를 로그에 기록한다는 의미이다. 작성자는 long_query_time=10으로 설정합니다.

작성자 설정:

sort_buffer_size = 1M
max_connections=120
wait_timeout = 120
back_log=100
read_buffer_size = 1M
thread_cache=32
Interactive_timeout=120
thread_concurrency = 4

매개변수 설명:

뒤로_로그
MySQL이 보유해야 하는 연결 수입니다. 이는 메인 MySQL 스레드가 짧은 시간 내에 많은 연결 요청을 받은 후, 메인 스레드가 연결을 확인하고 새 스레드를 시작하는 데 약간의 시간이 걸릴 때 작동합니다. back_log 값은 MySQL이 일시적으로 새 요청에 대한 응답을 중지하기 전에 짧은 시간 동안 스택에 저장할 수 있는 요청 수를 나타냅니다. 짧은 시간 내에 많은 연결이 예상되는 경우에만 연결을 늘려야 합니다. 즉, 이 값은 들어오는 TCP/IP 연결에 대한 청취 대기열의 크기입니다. 운영 체제에는 이 대기열 크기에 대한 자체 제한이 있습니다. Unix Listen(2) 시스템 호출에 대한 매뉴얼 페이지에 자세한 내용이 나와 있습니다. 이 변수의 최대값을 알아보려면 OS 설명서를 확인하세요. back_log를 운영 체제 제한보다 높게 설정하려고 해도 아무런 효과가 없습니다.
최대_연결
최대 동시 연결 수는 120개입니다. 이 값을 초과하면 자동으로 복구되어 문제가 자동으로 해결됩니다.

스레드_캐시
구체적인 지시 사항은 찾을 수 없었지만 32로 설정하고 나니 20일 만에 400개 이상의 스레드가 생성됐는데, 전날에는 수천 개의 스레드가 생성됐기 때문에 여전히 유용하다.

스레드_동시성
#CPU 수 x2로 설정합니다. 예를 들어 CPU가 하나만 있는 경우 thread_concurrency=2입니다.
#CPU가 2개이면 thread_concurrency=4입니다.

건너뛰기-innodb
#innodb 지원 제거