MySQL 최적화를 위한 21가지 팁-MySQL 튜토리얼-php.cn

MySQL 최적화를 위한 21가지 팁

怪我咯

풀어 주다： 2017-04-08 09:49:07

원래의

1609명이 탐색했습니다.

오늘 친구가 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를 사용하고 활성화하십시오. WriteBack은 redo 로그, 바이너리 로그 및 데이터 파일을 가속화하는 데 좋습니다.

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 더 큰 버퍼 풀 만들기

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

14. 로그 새로 고침 메커니즘을 합리적으로 선택하십시오

redo logs :

- innodb_flush_log_at_trx_commit = 1 // SAFEST

- innodb_flush_log_at_trx_commit = 2 // 더 나은 성능

- innodb_flush_log_at_trx_commit = 0 // 최고의 성능

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가 성능 경쟁을 할 때 Redo 로그를 4G 이상 할당하는 것이 승리의 팁이었고, 공식 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