PHP+MYSQL 아키텍처 웹사이트를 운영하는 동안 MySQL, PHP, CPU, 디스크 IO, 캐시 등 다양한 성능 문제가 자주 발생합니다. 그중 MySQL 병목 현상은 웹 사이트 성능에 영향을 미치는 가장 일반적이고 해결하기 어려운 요소입니다. 일반적으로 우리는 콘텐츠를 캐시하기 위해 redis 및 memcached와 같은 캐싱 소프트웨어를 사용하지만 이는 실제로 웹 사이트 프로그램 지원이 필요합니다. 그러나 가장 일반적으로 사용되는 웹사이트 프로그램은 이러한 캐싱 소프트웨어를 지원하지 않거나 완벽하게 지원할 수 없습니다. 오늘은 MySQL 병목 현상 문제를 완화하기 위해 MySQL 자체 구성 조정을 통해 MySQL 성능을 최적화하는 방법에 대해 이야기하겠습니다.
준비사항:
1. Pagoda Linux 패널 공식 버전 5.2.0+(2017/09/20 출시) 베타 버전 5.2.4+
2. 일반적으로 MySQL 튜닝은 다음과 같이 나뉩니다. 다음 여러 부분:
1. MySQL 구성 매개변수 조정(웹 사이트 운영 조건에 따라 조정 필요)
2. 데이터 테이블 인덱스 조정(효과는 분명하지만 일반적으로 우수한 오픈 소스 프로그램은 조정할 필요가 없습니다. )
3. SQL 문 튜닝(프로그래머나 DBA가 하는 일)
오늘은 주로 Pagoda 패널의 새로운 기능으로 MySQL 구성 매개변수를 튜닝하는 방법에 대해 이야기하겠습니다. 먼저 두 장의 사진을 살펴보겠습니다
분명히 (그림 1)은 MySQL의 현재 실행 상태를 보여주고, (그림 2)는 MySQL의 주요 구성 매개변수를 보여줍니다.
이 두 그림을 해석해 보겠습니다.
1 연결 수. 현재 활성 상태인 (그림 1)은 1입니다. MySQL 서비스가 시작된 이후 최대 연결 수는 54입니다. 최대 연결 수가 (그림 2)의 max_connections에 가깝거나 같으면 max_connections를 적절하게 늘려야 합니다. 그리고 주의가 필요합니다. 가장 중요한 것은 한 번에 너무 많이 늘리지 않는 것입니다. 매번 50씩 늘리고 일정 기간 동안 관찰하여 충분하지 않으면 계속 늘리는 것이 좋습니다.
2. (그림 1)의 스레드 캐시 적중률은 99.78%입니다. 이 값이 90% 미만인 경우 (그림 2)의 thread_cache_size를 8씩 늘리는 것이 좋습니다. 시간.
3. 인덱스 적중률(그림 1) 인덱스 적중률은 99.50%입니다. 이 값이 95% 미만인 경우에는 (그림 2)의 key_buffer_size를 64로 늘리는 것이 좋습니다. 데이터베이스가 Innodb 엔진을 사용하는 경우 이 옵션을 무시할 수 있습니다
4. Innodb 인덱스 적중률(그림 1) 이 값이 95% 미만인 경우 Innodb 인덱스 적중률은 100%입니다. , (그림 2)에서 innodb_buffer_pool_size를 적절하게 늘리는 것이 좋습니다. 매번 64씩 늘리는 것이 좋습니다. 데이터베이스가 Innodb 엔진을 사용하지 않는 경우 이 옵션을 무시할 수 있습니다
5. 쿼리 캐시 적중률 MySQL 쿼리 캐시는 개인적으로 사용할 때 사용하는 것이 좋습니다. redis 및 memcached와 같은 캐싱 소프트웨어를 사용할 때는 (그림 2)에서 query_cache_size를 0으로 설정하여 끄십시오. 캐싱 소프트웨어를 사용하지 않고 과도한 메모리 사용량이 있고 데이터베이스 병목 현상이 분명할 경우 쿼리 캐시를 활성화해 볼 수 있습니다. 이는 데이터 테이블 구조와 SQL 문 최적화에 크게 의존하는 기능입니다. 구조와 SQL 문은 쿼리 캐싱에 최적화되어 있지만 그 효과는 여전히 매우 좋습니다.
6. 디스크에 임시 테이블 생성(그림 1) 디스크에 임시 테이블을 생성하는 비율은 0.42%로, 이는 대부분의 임시 테이블이 메모리에 생성되므로 디스크 IO 오버헤드가 크게 증가하지 않음을 나타냅니다. 비율이 2%보다 크면 (그림 1)에서 tmp_cache_size를 적절하게 늘리십시오. 비율이 60%보다 크면 포기하지 않는 경우도 있습니다. 특별히 최적화된 SQL 문이므로 작업 중에 많은 수의 임시 파일이 열리므로 테이블에 캐시를 아무리 많이 추가해도 충분하지 않습니다.
7. 오픈 테이블(그림 1)의 오픈 테이블이 (그림 2)의 table_open_cache와 비슷하거나 같으면 table_open_cache를 적절하게 늘릴 수 있습니다. 그러나 설정이 너무 크면 프로그램이 자주 중단될 수 있습니다. MySQL 연결은 1024 이내를 권장하며 최대값은 2048을 초과할 수 없습니다.
8. 사용되지 않는 인덱스의 양과 인덱스되지 않은 JOIN의 양. 0이 아닌 경우 실제로 하루에 수천 개씩 증가하지 않는 한 데이터 테이블 인덱스를 확인하십시오. , 이는 일반적으로 무시될 수 있습니다. 결국 인덱스를 최적화하는 것은 프로그래머나 DBA가 수행하는 것이 더 적절할 것입니다.
9. 정렬 후 병합 횟수 이 값이 천천히 증가하는 경우 (그림 2)에서 sort_buffer_size를 적절하게 늘리는 것이 좋습니다. 매번 512씩 늘리는 것이 좋지만 최대값은 8192를 초과하면 안 됩니다. 이 값은 급속도로 증가하고 있으며 sort_buffer_size도 증가합니다. 그래도 작동하지 않으면 이 옵션을 포기하세요. 책임은 프로그램 개발자에게 있습니다.
10. 테이블 잠금 수 서버 CPU의 CPU 오버헤드가 낮고 테이블을 미친 듯이 잠글 경우 모든 데이터 테이블을 innodb로 변환하고 변환하기 전에 반드시 백업하는 것을 권장합니다.
11. 최적화 계획 메모리 크기를 기준으로 권장되는 최적화 계획입니다. 각 구성 항목은 실제 상황에 따라 조정되어야 합니다.
참고: 매개변수 구성을 저장한 후에는 MySQL 서비스를 다시 시작해야 합니다.
