MySQL 구성 파일 my.cnf 최적화에 대한 자세한 설명

MySQL 5.5.13
매개변수 설명:
[클라이언트]
문자 집합- 서버 = utf8
포트 = 3306
소켓 = /data/mysql/3306/mysql.sock
[mysqld]
문자 집합 서버 = utf8
사용자 = mysql
포트 = 3306
소켓 = / data/mysql/3306/mysql.sock
basedir = /usr/local/webserver/mysql
datadir = /data/mysql/3306/data
로그 오류 = /data/mysql/3306/mysql_error.log
pid 파일 = /data/mysql/3306/mysql.pid
# table_cache 매개변수는 테이블 캐시 수를 설정합니다. 연결이 들어올 때마다 적어도 하나의 테이블 캐시가 열립니다. #따라서 table_cache의 크기는 max_connections의 설정과 관련이 있어야 합니다. 예를 들어, 200개의 연결이 병렬로 실행되는 경우 # 최소 200 × N의 테이블 캐시가 있어야 합니다. 여기서 N은 애플리케이션이 쿼리를 실행할 수 있는 조인의 최대 테이블 수입니다. 또한 임시 테이블 및 파일용으로 일부 추가 파일 설명자를 예약해야 합니다.
# Mysql이 테이블에 액세스할 때 테이블이 캐시에 열려 있으면 캐시에 직접 액세스할 수 있습니다. # 캐시되지 않았지만 MySQL 테이블에 공간이 남아 있는 경우 buffer , 테이블이 열리고 테이블 버퍼에 저장됩니다. 테이블 캐시가 가득 차면 현재 사용되지 않는 테이블이 특정 규칙에 따라 해제되거나 테이블 캐시가 임시로 확장되어 저장됩니다. 캐시는 테이블 내용에 대한 더 빠른 액세스를 제공합니다. 플러시 테이블을 실행하면 #캐시 내용이 지워집니다. 일반적으로 데이터베이스의 피크 실행 시간 동안 Open_tables # 및 Opened_tables의 상태 값을 보면 table_cache 값을 늘려야 하는지 판단할 수 있습니다. 여기서 open_tables는 현재 열려 있는 테이블 수이고, Opened_tables는 열려 있는 테이블 수입니다. 즉, open_tables가 table_cache에 가깝고 Opened_tables의 값이 점차 증가하고 있다면 이 값의 크기를 # 늘리는 것을 고려해야 합니다. 또한 Table_locks_waited가 상대적으로 높으면 table_cache도 늘려야 합니다.
open_files_limit = 10240
table_cache = 512
#비동적 변수, 서비스를 다시 시작해야 함
# MySQL에 가능한 연결 수를 지정합니다. MySQL 메인 스레드가 짧은 시간 내에 많은 연결 요청을 받으면 이 매개변수가 적용되며, 메인 스레드는 연결을 확인하고 새 스레드를 시작하는 데 짧은 시간을 소비합니다. back_log 매개변수의 값은 MySQL이 일시적으로 새 요청에 대한 응답을 중지하기 전에 짧은 시간 동안 스택에 저장할 수 있는 요청 수를 나타냅니다. 시스템에 짧은 시간 동안 많은 연결이 있는 경우 들어오는 TCP/IP 연결에 대한 청취 대기열의 크기를 지정하는 이 매개변수의 값을 늘려야 합니다. 운영 체제마다 이 대기열 크기에 대한 제한이 있습니다. back_log를 운영 체제 제한보다 높게 설정하려고 해도 아무런 효과가 없습니다. 기본값은 50입니다. Linux 시스템의 경우 512보다 작은 정수로 설정하는 것이 좋습니다.
back_log = 600
#MySQL에서는 최대 연결 수를 허용합니다.
max_connections = 5000
#Yes 허용되는 오류 연결 수
max_connect_errors = 6000
#외부 잠금을 방지하려면 --skip-external-locking MySQL 옵션을 사용하세요. 이 옵션은 기본적으로 활성화되어 있습니다.
external-locking = FALSE
# 최대 패킷 크기를 설정하고, 서버에서 허용하는 데이터 패킷 크기를 제한하여 문제를 방지하세요. MySQL 클라이언트 또는 mysqld 서버가 max_allowed_packet 바이트보다 큰 패킷을 수신하면 "패킷이 너무 큼" 오류가 발생하고 연결이 종료됩니다. 일부 클라이언트의 경우 통신 패킷이 너무 크면 쿼리 실행 중에 "MySQL 서버에 대한 연결 끊김" 오류가 발생할 수 있습니다. 기본값은 16M입니다.
#dev-doc: //m.sbmmt.com/
max_allowed_packet = 32M
# Sort_Buffer_Size는 연결입니다. Level 매개변수는 각 연결(세션)이 처음으로 이 버퍼를 사용해야 할 때 설정된 메모리를 한 번 할당합니다.
#Sort_Buffer_Size는 크지 않을수록 좋습니다. 연결 수준 매개변수이므로 너무 큰 설정과 높은 동시성은 시스템 메모리 리소스를 소모할 수 있습니다. 예: 500개의 연결은 500*sort_buffer_size(8M)=4G 메모리
#Sort_Buffer_Size를 소비합니다. 2KB를 초과하면 메모리 할당을 위해 malloc() 대신 mmap()이 사용됩니다. 효율성이 감소됩니다.
#기술소개//m.sbmmt.com/
#dev-doc: //m.sbmmt.com/
#주문 제한이 나타나는 테이블에서 선택 설명
#주요 최적화 매개변수
sort_buffer_size = 8M
#테이블 간 연관 캐시에 사용되는 크기
join_buffer_size = 1M
# 서버 스레드 캐시 이 값은 캐시에 저장된 재사용 가능한 스레드 수를 나타냅니다. 연결이 끊어졌을 때 캐시에 여전히 공간이 있으면 스레드가 캐시에 배치됩니다. 다시 요청되면 캐시에서 요청을 읽습니다. 캐시가 비어 있거나 새 요청이면 스레드가 다시 생성됩니다. 새 스레드가 많으면 이 값을 늘리면 시스템 성능이 향상될 수 있습니다. Connections와 Threads_created 상태 변수를 비교하면 이 변수의 역할을 알 수 있습니다.
thread_cache_size = 300
# thread_concurrency 값이 올바르게 설정되었는지 여부는 다중 CPU(또는 다중 코어)의 경우 thread_concurrency 값을 잘못 설정하면 MySQL이 다중 CPU(또는 다중 코어)를 완전히 활용할 수 없고 하나의 CPU(또는 다중 코어)만 활용할 수 있습니다. 핵심)은 동시에 작동할 수 있습니다. thread_concurrency는 CPU 코어 수의 2배로 설정되어야 합니다. 예를 들어 듀얼 코어 CPU가 있는 경우 thread_concurrency는 2개의 듀얼 코어 CPU에 대해 4여야 하며 thread_concurrency 값은 8이어야 합니다.
# 핵심 최적화 매개변수입니다
thread_concurrency = 8
# MySQL을 사용하는 사용자라면 누구나 이 변수에 익숙할 것입니다. 지난 몇 년간 MyISAM 엔진 최적화에서 이 매개변수는 중요한 최적화 매개변수이기도 했습니다. 그러나 개발 과정에서 이 매개변수에는 몇 가지 문제도 노출되었습니다. 기계의 메모리는 점점 더 커지고 있으며 사람들은 이전에 유용했던 매개변수에 점점 더 큰 값을 할당하는 데 익숙해졌습니다. 이 매개변수를 늘리면 일련의 문제도 발생합니다. 먼저 query_cache_size의 작동 원리를 분석해 보겠습니다. DB에서 SELECT 쿼리가 수행된 후 DB는 해당 SQL을 다시 DB로 가져와 호출하면 테이블을 변경하지 않고 이를 캐시합니다. 캐시에서 클라이언트. 여기서 중요한 점은 DB가 Query_cache를 사용하여 작업할 때 해당 기간 동안 해당 명령문에 포함된 테이블이 변경되지 않아야 한다는 것입니다. 그러면 테이블이 변경되면 Query_cache의 데이터는 어떻게 처리되나요? 먼저 테이블과 관련된 모든 Query_cache 및 문을 무효화한 다음 업데이트를 작성합니다. 그런 다음 Query_cache가 매우 크고 테이블에 쿼리 구조가 많으며 쿼리 문이 느리게 유효하지 않으면 업데이트 또는 삽입이 매우 느려지므로 업데이트 또는 삽입이 왜 그렇게 느린지 알 수 있습니다. 따라서 데이터베이스 쓰기 또는 업데이트 양이 상대적으로 큰 시스템에서는 이 매개변수가 너무 큰 할당에는 적합하지 않습니다. 또한 동시성이 높고 쓰기량이 많은 시스템에서는 이 기능을 비활성화하는 것이 좋습니다.
#주요 최적화 매개변수(주요 데이터베이스 추가, 삭제 및 수정-MyISAM)
query_cache_size = 512M
#버퍼를 지정하세요. 단일 쿼리로 사용할 수 있습니다. 영역 크기, 기본값은 1M
query_cache_limit = 2M
#기본값은 4KB, 큰 값을 설정하는 것이 빅데이터에 좋습니다. 하지만 쿼리가 작은 경우에는 작은 데이터 쿼리로 인해 쉽게 메모리 조각화 및 낭비가 발생할 수 있습니다
#쿼리 캐시 조각화율 = Qcache_free_blocks / Qcache_total_blocks * 100%
#만약 쿼리 캐시 조각화 비율이 20%를 초과하는 경우 FLUSH QUERY CACHE를 사용하여 캐시 조각 모음을 수행하거나 쿼리가 모두 작은 데이터 볼륨인 경우 query_cache_min_res_unit을 줄여보세요.
#쿼리 캐시 활용도 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
#쿼리 캐시 활용도가 25% 미만이면 query_cache_size가 너무 크게 설정되어 적절하게 줄일 수 있다는 뜻이고, 쿼리 캐시 활용도가 80%를 초과하고 Qcache_lowmem_prunes > 50이면 query_cache_size가 약간 클 수 있다는 의미입니다. 작거나 너무 조각화되어 있습니다.
# 쿼리 캐시 적중률 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
query_cache_min_res_unit = 2k
default-storage- 엔진 = MyISAM
#각 데이터베이스 스레드에 사용되는 스택 크기를 제한합니다. 대부분의 애플리케이션에는 기본 설정으로 충분합니다.
thread_stack = 192K
# 사용 가능한 기본 트랜잭션 격리 수준은 다음과 같습니다.
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# 1.READ UNCOMMITTED-읽기 커밋되지 않음 2.READ COMMITTE-읽기 커밋 3.REPEATABLE READ-반복 읽기 4 . SERIALIZABLE - SERIAL
transaction_isolation = READ-COMMITTED
# tmp_table_size의 기본 크기는 32M입니다. 임시 테이블이 이 크기를 초과하면 MySQL은 The table tbl_name is full 형식의 오류를 생성합니다. 고급 GROUP BY 쿼리를 많이 수행하는 경우 tmp_table_size 값을 늘리십시오.
tmp_table_size = 246M
max_heap_table_size = 246M
#인덱스 캐시 크기: 데이터베이스 인덱스 처리 속도, 특히 인덱스를 결정합니다. 읽기 속도
key_buffer_size = 512M
# MySql 읽기 버퍼 크기. 테이블의 순차 스캔 요청은 읽기 버퍼를 할당하고 MySql은 이에 대한 메모리 버퍼를 할당합니다. read_buffer_size 변수는 이 버퍼의 크기를 제어합니다. 테이블에 대한 순차 스캔 요청이 매우 빈번하고 빈번한 스캔이 너무 느리게 실행된다고 생각되면 이 변수의 값과 메모리 버퍼 크기를 늘려 성능을 향상시킬 수 있습니다.
read_buffer_size = 4M
# MySql의 랜덤 읽기(쿼리 연산) 버퍼 크기입니다. 임의의 순서(예: 정렬된 순서)로 행을 읽으면 임의 읽기 버퍼가 할당됩니다. 정렬 쿼리를 수행할 때 MySql은 디스크 검색을 방지하고 쿼리 속도를 향상시키기 위해 먼저 버퍼를 스캔합니다. 많은 양의 데이터를 정렬해야 하는 경우 이 값을 적절하게 늘릴 수 있습니다. 그러나 MySql은 각 고객 연결에 대해 이 버퍼 공간을 할당하므로 과도한 메모리 오버헤드를 방지하려면 이 값을 적절하게 설정해야 합니다.
read_rnd_buffer_size = 16M
#삽입 효율성을 효과적으로 향상시킬 수 있는 일괄 삽입 데이터 캐시 크기, 기본값은 8M
bulk_insert_buffer_size = 64M
# MyISAM 테이블 변경 시 재정렬을 위해 버퍼링 필요
myisam_sort_buffer_size = 128M
# MySQL이 인덱스를 재구축할 때 허용되는 최대 임시 파일 크기(REPAIR, ALTER TABLE 또는 LOAD DATA INFILE 시).
# If 파일 크기가 이 값보다 크면 키-값 버퍼링을 통해 인덱스가 생성됩니다(느림)
myisam_max_sort_file_size = 10G
# 테이블이 다음보다 많은 경우 MyISAM은 병렬 정렬을 통해 하나 이상의 스레드를 사용하여 이를 수정할 수 있습니다.
# 여러 개의 CPU와 대용량 메모리를 사용하는 사용자에게 적합한 선택입니다.
myisam_repair_threads = 1
# 제대로 닫히지 않은 MyISAM 테이블을 자동으로 확인하고 복구합니다.
myisam_recover
interactive_timeout = 120
wait_timeout = 120
innodb_data_home_dir = /data/mysql/3306/data
#테이블스페이스 파일 중요 데이터
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#이 매개변수는 InnoDB에 저장된 데이터 디렉토리 정보 및 기타 내부 데이터 구조의 메모리 풀 크기를 설정하는 데 사용됩니다. 오라클의 라이브러리 캐시 . 이는 필수 매개변수가 아니므로 초과할 수 있습니다.
innodb_additional_mem_pool_size = 16M
# 이는 Innodb 테이블에 매우 중요합니다. Innodb 테이블은 MyISAM 테이블보다 버퍼링에 더 민감합니다. MyISAM은 기본 key_buffer_size 설정에서 잘 실행될 수 있지만 Innodb는 기본 innodb_buffer_pool_size 설정에서 달팽이처럼 실행됩니다. Innodb는 데이터와 인덱스를 모두 캐싱하기 때문에 운영 체제에 너무 많은 메모리를 남겨둘 필요가 없으므로 Innodb만 사용해야 한다면 사용 가능한 메모리의 70~80%까지 설정하면 됩니다. key_buffer에 적용되는 몇 가지 규칙은 다음과 같습니다. 데이터 볼륨이 크지 않고 폭발적으로 증가하지 않는 경우 innodb_buffer_pool_size를 너무 크게 설정할 필요가 없습니다.
innodb_buffer_pool_size = 512M
#파일 IO 스레드 수는 일반적으로 4개이지만 Windows에서는 더 크게 설정할 수 있습니다.
innodb_file_io_threads = 4
# InnoDb 코어에 허용되는 스레드 수
# 최적의 값은 애플리케이션에 따라 다릅니다. 하드웨어 및 운영 체제의 스케줄링 방법.
# 값이 너무 높으면 스레드 상호 배제 스래싱이 발생할 수 있습니다.
innodb_thread_concurrency = 8
# 이 매개변수를 1로 설정하면 각 트랜잭션이 커밋된 후 로그가 디스크에 기록됩니다. 성능을 제공하기 위해 0 또는 2로 설정할 수 있지만 장애 발생 시 데이터가 손실될 위험이 있습니다. 0으로 설정하면 트랜잭션 로그가 로그 파일에 기록되고 로그 파일이 초당 한 번씩 디스크에 플러시된다는 의미입니다. 2로 설정하면 커밋 시 트랜잭션 로그가 로그에 기록되지만 로그 파일은 한 번에 한 번씩 디스크에 플러시됩니다.
innodb_flush_log_at_trx_commit = 2
#이 매개변수는 M에서 일부 로그 파일이 사용하는 메모리 크기를 결정합니다. 버퍼가 클수록 성능이 향상될 수 있지만 예기치 않은 오류가 발생할 경우 MySQL 개발자는 1~8M
innodb_log_buffer_size = 16M
# 이 매개변수로 설정하는 것이 좋습니다. 데이터 로그 파일의 크기를 결정합니다. M 단위로 설정하면 성능이 향상될 수 있지만 실패한 데이터베이스를 복구하는 데 필요한 시간도 늘어납니다.
innodb_log_file_size = 128M
#성능을 향상시키기 위해 MySQL은 루프에서 여러 파일에 로그 파일을 쓸 수 있습니다. 권장 설정은 3M
innodb_log_files_in_group = 3
# 권장 읽기 //m.sbmmt.com/
# Buffer_Pool Dirty_Pages 수는 InnoDB 종료 시간에 직접적인 영향을 미칩니다. innodb_max_dirty_pages_pct 매개변수는 Buffer_Pool의 Dirty_Page 비율을 직접 제어할 수 있으며, 다행히 innodb_max_dirty_pages_pct는 동적으로 변경할 수 있습니다. 따라서 InnoDB를 닫기 전에 innodb_max_dirty_pages_pct를 줄이고 일정 기간 동안 데이터 블록을 강제로 플러시하면 MySQL 종료 시간이 크게 단축될 수 있습니다.
innodb_max_dirty_pages_pct = 90
# InnoDB에는 완료되지 않은 트랜잭션이 롤백될 수 있는 교착 상태 감지 메커니즘이 내장되어 있습니다. 그러나 InnoDB와 함께 MyISAM의 잠금 테이블 문이나 타사 트랜잭션 엔진을 사용하는 경우 InnoDB는 교착 상태를 인식할 수 없습니다. 이러한 가능성을 제거하기 위해 innodb_lock_wait_timeout을 다른 트랜잭션이 궁극적으로 트랜잭션 롤백 대상이 되는 데이터를 수정하도록 허용하기 전에 MySQL에 대기할 시간(초)을 지시하는 정수 값으로 설정할 수 있습니다.
innodb_lock_wait_timeout = 120
#전용 테이블 공간(닫힘)
innodb_file_per_table = 0
# –slow-query- log-file=로 mysqld 시작 /data/mysql/3306/slow.log
slow_query_log
long_query_time = 1
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
#데이터베이스에서 업데이트 구성 바이너리 작성 여부 이 슬레이브 라이브러리가 다른 슬레이브 라이브러리의 마스터 라이브러리가 될 경우 슬레이브 라이브러리가 로그 동기화를 수행할 수 있도록 이 매개변수를 설정해야 합니다. -logs-bin
log-slave-updates
log-bin = /data/mysql/3306/binlog/binlog
binlog_cache_size = 4M
#STATEMENT,ROW,MIXED
# 명령문 기반 복제(SBR), 행 기반 복제(RBR), 혼합 모드 복제(혼합 기반 복제, MBR). 이에 따라 binlog에는 STATEMENT, ROW 및 MIXED의 세 가지 형식이 있습니다.
binlog_format = 혼합
max_binlog_cache_size = 64M
max_binlog_size = 1G
relay-log-index = /data/mysql/3306/relaylog/relaylog
relay-log-info-file = /data/mysql/3306/relaylog/relaylog
relay-log = /data/mysql/3306/relaylog/relaylog
expire_logs_days = 30
skip-name-resolve
#master-connect -재시도 = 10
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
서버 ID = 1
[mysqldump]
빠른
max_allowed_packet = 32M
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer_size = 2M
write_buffer = 2M
[ mysqlhotcopy]
interactive-timeout

위는 MySQL 구성 파일 my.cnf의 최적화에 대한 자세한 설명입니다. 내용이 궁금하시다면 PHP 중국어 넷(m.sbmmt.com)을 주목해주세요!