MySQL 최적화를 위한 21가지 팁

오늘 친구가 MySQL을 최적화하는 방법을 물었고, 내 생각대로 정리했는데 대략 21가지 방향으로 나눌 수 있다. 여기에 나열되지 않은 세부 사항(테이블 캐시, 테이블 디자인, 인덱스 디자인, 터미널 캐시 등)이 있습니다. 시스템의 경우 다음을 초기 단계에서 완료할 수 있는 것이 좋은 시스템입니다.

1. 메모리가 충분한지 확인하세요

데이터베이스를 효율적으로 실행할 수 있는 가장 중요한 요소는 메모리가 충분히 커야 한다는 것입니다. 캐시된 데이터, 업데이트는 메모리에서 먼저 완료될 수도 있습니다. 그러나 기업마다 메모리 요구 사항이 다릅니다. 특히 핫 데이터의 경우 메모리가 기본적으로 데이터베이스 크기의 80%에 도달하는 것이 좋습니다.

2. 더 많고 더 빠른 CPU가 필요합니다

MySQL 5.6은 64개의 코어를 활용할 수 있지만 각 MySQL 쿼리는 하나의 CPU에서만 실행될 수 있으므로 더 많은 CPU가 필요합니다. 동시성.

3. 적절한 운영 체제를 선택하려면

공식 권장 사항에 따르면 Solaris가 가장 권장됩니다. 실제 생산에서는 CentOS와 REHL을 모두 사용하는 것이 좋습니다. REHL 버전 6 이후에는 Oracle Linux도 좋은 선택입니다. Windows는 MySQL 5.5부터 최적화되었지만 동시성이 높은 환경에서는 Windows를 사용하지 않는 것이 좋습니다.

4. 시스템 매개변수를 적절하게 최적화합니다.

파일 핸들을 ulimit -n 기본값 1024로 변경합니다. 너무 작음

프로세스 번호 제한 ulimit -u 버전이 다름

NUMA numctl 비활성화 -interleave=all

5. 적절한 메모리 할당 알고리즘 선택

기본 메모리 할당은 c의 malloc입니다. 이제 최적화된 메모리 할당 알고리즘이 많이 있습니다:

jemalloc 및 tcmalloc

MySQL 5.5부터는 선언된 내부 저장 방식이 지원됩니다.

　[mysqld_safe]

malloc-lib = tcmalloc

또는 so 파일을 직접 가리킵니다

[mysqld_safe]

malloc- lib=/usr/local/lib/libtcmalloc_minimal.so

6. 더 빠른 저장 장치 SSD 또는 솔리드 스테이트 카드를 사용하십시오.

저장 매체는 MySQL의 무작위 읽기 및 쓰기 업데이트 속도에 큰 영향을 미칩니다. 차세대 저장 장치인 솔리드 스테이트 SSD 및 솔리드 스테이트 카드의 출현으로 인해 MySQL도 빛을 발하게 되었고 Taobao는 IOE에서 좋은 싸움을 벌였습니다.

7. 좋은 파일 시스템을 선택하세요

아직 ext2나 ext3를 사용하고 계신다면 XFS와 Ext4를 권장합니다. 가능한 한 빨리 업그레이드하세요. XFS를 권장합니다. 이는 Linux가 향후 지원할 파일 시스템이기도 합니다.

권장되는 파일 시스템: ,nobarrier)

ext4 매개변수 마운트:

　ext4(rw,noatime,nodiratime,nobarrier,data=ordered)

SSD 또는 솔리드 스테이트 디스크를 사용하는 경우 다음을 고려해야 합니다.

• innodb_page_size = 4K

　• Innodb_flush_neighbors = 0

9. 적절한 IO 스케줄링을 선택합니다.

정상인 경우 기본값은 noop을 사용하세요.

echo dealine >/sys/ block/{DEV-NAME }/queue/scheduler

10. 적절한 Raid 카드 캐시 전략을 선택하세요

강화된 Raid를 사용하고 Redo 로그, 바이너리 속도를 높이는 데 좋은 WriteBack을 활성화하세요. 로그 및 데이터 파일.

11. 쿼리 캐시 비활성화

쿼리 캐시는 Innodb에서 약간 쓸모가 없습니다. 쿼리 캐시는 Innodb 버퍼 풀에 캐시될 수 있습니다. 설정, 업데이트 및 쓰기 쿼리 캐시를 확인하면 쓰기 오버헤드가 증가합니다.

MySQL 5.6에서는 쿼리 캐시가 비활성화되어 있습니다.

12. Thread Pool 사용

이제 하나의 데이터가 5개 ​​이상의 App 시나리오에 해당하지만 MySQL에는 연결 수가 늘어날수록 성능이 저하되는 기능이 있으므로 더 많은 앱을 사용하는 경우 200개 이상의 연결 향후 시나리오에서는 스레드 풀 사용을 고려해 보십시오. 이는 훌륭한 발명품입니다.

13. 메모리를 적절하게 조정

13.1 연결에 대한 메모리 할당 줄이기

스레드 풀만큼 강력하지 않은 thread_cache_size를 사용하여 연결을 캐시할 수 있습니다. 연결 시 데이터베이스에 의해 할당된 메모리는 다음과 같습니다:

max_used_connections * (

read_buffer_size +

read_rnd_buffer_size +

Join_buffer_size +

sort_buffer_size +

binlog_cache_size +

thread_stack +

2 * net_buffer_length …

)

13.2 더 큰 버퍼 풀 만들기

메모리의 60~80%를 innodb_buffer_pool_size에 할당합니다. 이는 데이터 크기를 초과해서는 안 되며, 80%를 초과하여 할당하지 마십시오. 그렇지 않으면 스왑이 사용됩니다.

14. 합리적인 크기를 선택합니다. 로그 새로 고침 메커니즘

　Redo 로그:

　- innodb_flush_log_at_trx_commit = 1 // 가장 안전함

　- innodb_flush_log_at_trx_commit = 2 // 성능 향상

　- innodb_flush_ log_at_ trx_commit = 0 // best Qingneng

Binlog:

Binlog_sync = 1에는 그룹 커밋 지원이 필요합니다. 이 기능이 없으면 binlog_sync=0을 고려하여 더 나은 성능을 얻을 수 있습니다.

데이터 파일 :

innodb_flush_method = O_DIRECT

15. Innodb 테이블을 사용해주세요

더 많은 리소스를 사용할 수 있으며 온라인 변경 작업이 개선되었습니다. 현재 중국어 이외의 전문도 지원되며 Memcache API 액세스도 지원됩니다. 현재 MySQL에 가장 적합한 엔진입니다.

아직 MyISAM을 사용 중이라면 빠르게 전환하는 것을 고려해 보세요.

16. 더 큰 Redo 로그 설정

과거 Percona 5.5와 공식 MySQL 5.5가 성능 경쟁을 할 때, 승리의 팁은 4G Redo 로그보다 더 많이 할당하는 것이었고, 공식 MySQL5.5 redo 로그는 4G를 초과할 수 없습니다. MySQL 5.6부터는 4G를 초과할 수 있습니다. 일반적으로 redo 로그의 총 크기는 500M를 초과합니다. 생성된 Redo 로그의 양을 관찰하고 1시간 이상의 Redo 로그 양을 할당할 수 있습니다.

17. 디스크 IO 최적화

Innodb_io_capactiy SAS 15000rpm에서는 800만 구성하고, SSD에서는 2000 이상 구성하면 됩니다.

현재 새로운 기능은 독립 테이블스페이스 지원입니다.

테이블 공간 재활용 자르기

테이블 공간 전송

조각화를 최적화하는 것이 더 좋습니다. 성능이 좋아진다,

전체적으로 독립된 테이블스페이스를 사용하는 것은 쓸모가 없다.

19. 합리적인 동시성 구성

innodb_thread_concurrency = 동시성은 Innodb에서 가장 자주 변경되는 매개변수입니다. 다른 버전에서는 다른 마이너 버전이 변경될 수 있습니다. 일반 권장 사항:

스레드 풀을 사용하는 경우:

innodb_thread_concurrency = 0이 좋습니다.

스레드 풀이 없는 경우:

5.5 권장: innodb_thread_concurrency =16 – 32

5.6 권장 innodb_thread_concurrency = 36

20. 트랜잭션 최적화 격리 수준

기본값은 반복 읽기입니다

읽기 커밋을 사용하는 것이 좋습니다 Binlog 형식은 혼합 또는 행을 사용합니다

낮은 격리 수준 = 더 나은 성능

21. 모니터링에 주의하세요

모니터링과 환경이 분리될 수 없습니다. 모니터링이 없으면 맹인이 될 수도 있고 코끼리가 될 수도 있습니다. zabbix+mpm으로 모니터링을 구축하는 것이 좋습니다.