MySQL 데이터베이스 최적화 기술 구성 기술 요약_MySQL

이 글의 예시에서는 MySQL 데이터베이스 최적화 기술의 구성 방법을 설명합니다. 참고하실 수 있도록 모든 사람과 공유하세요. 자세한 내용은 다음과 같습니다.

(1) 데이터베이스 액세스 감소

정적으로 만들 수 있는 페이지는 최대한 정적으로 만드세요

동적 페이지에서 정적일 수 있는 부분을 정적화

일부 데이터는 XML로 생성되거나 텍스트 파일로 저장될 수 있습니다

MemCached와 같은 데이터 캐싱 기술을 사용하세요

(2) 최적화된 탐지 방법

1. 사용자 경험 감지

2.Mysql 상태 감지

Mysql 명령줄에서 show status 명령을 사용하여 현재 mysql 상태를 확인하세요.

주로 다음 속성에 중점을 둡니다.

key_read_requests(인덱스 읽기 요청 수)(key_buffer_size 설정에 영향을 받음)

key_reads(인덱스 읽기 응답 수)

Key_blocks_used

Q캐시_*

Open_tables(table_cache 설정의 영향을 받음)

Opened_tables

테이블 잠금

3. 타사 도구 감지

mysqlreport http://hackmysql.com/mysqlreport

mytop http://jeremy.zawodny.com/mysql/mytop/

시스템 및 MySQL 로그

시스템 명령: top, sar

Mysql 로그: Slow_query.log

(3) 하드웨어 최적화

하드웨어 측면에서 Mysql의 병목 현상이 가장 많이 발생하는 부분은 디스크이고, 그 다음이 CPU, 메모리 순입니다

디스크 측면

더 빠른 디스크를 사용하는 것이 Mysql에 큰 도움이 됩니다

더 많은 하드 드라이브를 사용하고 Raid를 사용하면 단일 디스크의 속도를 높일 수 있습니다

Raid 방식은 Raid 0+1 또는 Raid 1+0 사용을 권장합니다

CPU

더 높은 CPU 주파수와 더 많은 CPU가 MySQL에 더 많은 성능을 제공할 수 있다는 것은 의심의 여지가 없습니다.

고성능

추억

메모리가 높을수록 Mysql의 더 많은 데이터를 메모리에 캐시할 수 있습니다.

그러나 중요한 요소는 올바른 Mysql 구성이 필요하다는 것입니다

네트워크 카드

기가비트 네트워크 카드 및 기가비트 네트워크 사용

(4) 운영체제 최적화

1. 스왑 영역을 사용하지 마세요. 메모리가 부족한 경우 메모리를 추가하거나 메모리를 적게 사용하도록 시스템을 구성하세요.

2. NFS 디스크를 사용하지 마세요

3. 시스템 및 MySQL 서버에서 열린 파일 수를 늘립니다.

ulimit –n 65535 사용

4. 시스템의 프로세스 및 스레드 수를 늘립니다.

5. 불필요한 애플리케이션을 종료하고, 하드 디스크 매개변수를 최적화하고, hdparm을 사용하여 테스트합니다

(5) 애플리케이션 수준 최적화

1. 다중 서버 로드 밸런싱 사용(다중 읽기 및 쓰기, 데이터 동기화를 위한 복제 기술 사용)

2. 테이블 파티션(커스텀 파티션, mysql5.1 내장 파티션 기능 지원 시작)

3. 데이터 캐싱 기술 memcached를 사용하세요

(6) Mysql 구성 최적화

1.key_buffer(=512): 인덱스 버퍼가 사용하는 메모리 양

이것은 MyISAM 테이블에 매우 중요합니다. 상태 값 ​​​​Key_read_requests 및 Key_reads를 확인하여 사용 가능한 메모리의 25%-30%로 설정하는 것이 좋습니다.

key_buffer 설정이 합리적인지 알 수 있습니다. key_reads / key_read_requests 비율은 가능한 낮아야 합니다. 최소한 1:100, 1:1000이 더 좋습니다. 그렇지 않으면 key_buffer 설정이 너무 작다는 의미입니다.

2.innodb_buffer_pool_size (= 512) : 인덱스 버퍼가 사용하는 메모리 양

3.table_cache (=1024) : 데이터 테이블 캐시 영역의 크기

MySQL이 테이블에 액세스할 때마다 테이블 버퍼에 공간이 있으면 테이블을 열어 그 안에 배치하므로 테이블 내용에 더 빠르게 액세스할 수 있습니다.

최대 실행 시간 동안 Open_tables 및 Opened_tables 상태 값을 확인하여 table_cache 값을 조정해야 하는지 여부를 결정할 수 있습니다.

open_tables의 값이 table_cache와 같고 Open_tables 상태 값이 증가하고 있는 것을 발견하면 table_cache 매개변수 값을 늘려야 합니다.

table_cache 매개변수를 너무 큰 값으로 설정할 수는 없습니다. 너무 높게 설정하면 파일 설명자가 부족하여 성능이 불안정해지거나 연결이 실패할 수 있습니다.

4.sort_buffer_size (=256) : 정렬을 위한 버퍼의 길이를 지정

이 매개변수에 해당하는 할당된 메모리는 각 연결마다 배타적입니다! 100개의 연결이 있는 경우 실제 할당된 총 정렬 버퍼 크기는 100 × 6 = 600MB입니다.

그래서 메모리가 4GB 정도 되는 서버의 경우 6~8M로 설정하는 것을 권장합니다

5.join_buffer_size: 연관 쿼리에 대한 버퍼 길이

메모리는 4G 이상, 32M 이상을 권장합니다. 이 매개변수에 해당하는 할당된 메모리도 각 연결에 대해 배타적입니다!

6.max_connections (=1024): 재사용 가능한 스레드 수

MySQL 서버에 동시에 연결이 허용되는 클라이언트 수는 시스템의 최대 동시 연결 수를 관찰하고 추정하여 설정할 수 있습니다

7.thread_cache(=*): 재사용 가능한 스레드 수

일반적으로 CPU 개수 × 2로 설정

8.innodb_buffer_pool_size(= 512): innodb 테이블 캐시 풀 크기

이것은 Innodb 테이블에 매우 중요합니다. Innodb 테이블은 MyISAM 테이블보다 버퍼링에 더 민감합니다. MyISAM은 기본 key_buffer_size 설정(

)에서 실행할 수 있습니다.

그러나 Innodb는 기본 innodb_buffer_pool_size 설정에서는 달팽이처럼 동작합니다.

Innodb는 데이터와 인덱스를 모두 캐싱하기 때문에 운영체제에 너무 많은 메모리를 맡길 필요가 없기 때문에 Innodb만 사용해야 한다면 사용 가능한 메모리의 70~80%까지 설정하면 됩니다.

key_buffer에 적용되는 몇 가지 규칙은 다음과 같습니다. 데이터 볼륨이 크지 않고 빠르게 증가하지 않는 경우 innodb_buffer_pool_size를 너무 크게 설정할 필요가 없습니다.

9.innodb_flush_logs_at_trx_commit(=1): 트랜잭션 커밋 후 로그 플러시 모드

Innodb가 MyISAM보다 1000배 느린 것이 걱정되시나요? 이 매개변수를 수정하는 것을 잊어버린 것 같습니다. 기본값은 1입니다. 이는 커밋된 모든 업데이트된 트랜잭션(또는 트랜잭션 외부의 모든 문)이 디스크에 플러시된다는 의미입니다.

특히 배터리 백업 캐시가 없으면 리소스를 많이 소모합니다. 많은 애플리케이션, 특히 MyISAM에서 변환된 애플리케이션은 단순히 값을 2로 설정합니다. 이는 로그가 디스크에 플러시되지 않음을 의미합니다.

운영 체제 캐시에만 플러시됩니다. 로그는 여전히 매초 디스크에 플러시되므로 일반적으로 초당 1~2회의 업데이트 비용이 손실되지 않습니다. 0으로 설정하면 속도가 훨씬 빨라지지만 상대적으로 안전하지도 않습니다.

MySQL 서버가 충돌하면 일부 트랜잭션이 손실됩니다. 운영 체제 캐시에 플러시된 트랜잭션 부분이 손실되도록 하려면 2로 설정합니다.

더 많은 MySQL 관련 콘텐츠에 관심이 있는 독자는 이 사이트의 특별 주제인 "MySQL 인덱스 작업 기술 요약", "MySQL 로그 작업 기술 종합 모음", "MySQL 트랜잭션 작업 기술 요약"을 확인할 수 있습니다. , "MySQL 저장 프로시저 기술 종합 모음" , "MySQL 데이터베이스 잠금 관련 기술 요약" 및 "자주 사용되는 MySQL 함수 요약"

이 기사가 MySQL 데이터베이스를 계획하는 모든 사람에게 도움이 되기를 바랍니다.