MySQL 성능 튜닝

풀스택에는 데이터베이스 기술이 필수이며, 관계형 데이터베이스나 nosql, 메모리 데이터베이스나 부분 디스크 스토리지 데이터베이스, 객체 등 여러 종류가 있습니다 저장된 데이터베이스 또는 그래프 데이터베이스이지만 첫 번째 필수 기술은 MySQL이어야 합니다. LAMP의 등장부터 Mariadb의 등장, 심지어 PG의 등장까지 숙련된 MySQL 기술은 매우 유용하게 사용됩니다.

MySQL 데이터베이스 기술에는 여러 측면이 있습니다. 여기서는 필요한 성능 튜닝만 다루겠습니다. 주로 운영 환경, 구성 매개변수, SQL 성능 및 시스템 아키텍처 설계 튜닝을 포함하는 상향식 성능 튜닝을 권장합니다. .

운영 환경 튜닝

이것이 Linux의 세계이고, MySQL 실행 환경의 튜닝은 Linux 커널 튜닝과 함께 완료되는 경우가 많습니다. 물론 클라우드 서비스 RDS에 대한 특정 참조 역할도 있습니다.

Linux의 기본 IO 스케줄링 알고리즘을 조정합니다.

IO 스케줄러의 전반적인 목표는 헤드가 항상 한 방향으로 움직이게 한 다음, 반대 방향으로 보면 이것이 바로 실생활의 엘리베이터 모델이므로 IO 스케줄러를 엘리베이터라고도 하며, 해당 알고리즘을 Linux의 IO 스케줄링을 위한 여러 가지 엘리베이터 알고리즘이라고 합니다. (Anticipatory)로 하나는 cfq(Complete Fairness Queueing), 하나는 Deadline, 하나는 Noop(No Operation)이라고 합니다.

IO는 기본 IO 스케줄링 알고리즘에 더 큰 영향을 미칩니다. Linux의 cfq는 SSD 또는 PCIe-SSD 장치인 경우 noop로 수정해야 합니다. 다음 두 가지 수정 방법을 사용할 수 있습니다.

1. 다시 시작하면 온라인 동적 수정이 실패합니다.

echo “deadline” > /sys/block/sda/queue/scheduler

2. /etc/grub.conf를 수정하여 영구적으로 만듭니다.

/etc/grub.conf 구성 파일을 수정하고 커널 라인에 구성을 추가합니다. 예:

elevator=deadline

주로 엘리베이터 매개변수에 중점을 둡니다. 적용하려면 시스템을 다시 시작하세요.

Numa 기능 비활성화

신세대 아키텍처의 NUMA는 데이터베이스 실행에 적합하지 않습니다. 메모리가 부족하여 스왑 문제가 발생하므로 일반적으로 NUMA 스케줄링을 끄거나 수정하는 것이 좋습니다.

numa=off

2. mysqld 프로세스를 시작할 때 NUMA 스케줄링 메커니즘을 설정하도록 /etc/init.d/mysql 또는 mysqld_safe 스크립트를 수정합니다(예: numactl –interleave=all).

swappiness 설정 수정

swappiness는 물리적 메모리 교체 전략을 제어하는 ​​데 사용되는 Linux의 커널 매개변수로 최소 백분율 값을 허용합니다. 0 이면 최대값은 100이고 기본값은 60입니다. 이 설정 값은 어떤 영향을 줍니까?

vm.swappiness를 0으로 설정하면 스왑을 최대한 적게 사용한다는 의미이고, 100은 비활성 메모리 페이지를 스왑 또는 릴리스 캐시로 스왑한다는 의미입니다. 비활성 메모리는 프로그램에 의해 매핑되지만 "오랜 시간" 동안 사용되지 않는 메모리를 의미합니다. vmstat를 사용하여 시스템에 얼마나 많은 비활성 메모리가 있는지 확인할 수 있습니다.

# vmstat -a 1

이 값은 1로 설정하는 것이 좋습니다. 설정 방법은 다음과 같습니다. /etc/sysctl.conf 파일에 한 줄을 추가합니다.

vm.swappiness = 1

파일 설명자 확장

자주 수정되는 매개변수로, 동시성이 높은 프로그램에서 수정됩니다.

ulimit -n 51200

2. 구성을 수정합니다. 파일, 영구적으로 유효합니다.

在/etc/security/limits.conf配置文件中增加

* hardnofile 51200
 
* softnofile 51200

面向session的进程文件描述符的修改稍有不同，在云上的修改也略有差异，可以参见一样的“open too many files”

优化文件系统挂载参数。

对于文件系统，如无特殊要求，最好采用ext4.

文件系统挂载参数是在/etc/fstab文件中修改,重启时候生效。

noatime表示不记录访问时间,nodiratime不记录目录的访问时间。

barrier=0,表示关闭barrier功能.

barrier的主要目的是为了保证磁盘写数据的安全性,但是会降低性能。如果有BBU之类的电池备份电源保证控制卡不瞬间掉电,那么这个功能就可以放心大胆的关闭。

配置参数调优

my.cnf中的配置参数调优取决于业务，负载或硬件，在慢内存和快磁盘、高并发和写密集型负载情况下，都需要特殊的调整。

基本配置

query_cache_size

query cache是一个众所周知的瓶颈，甚至在并发并不多时也如此。 最 好是一开始就停用，设置query_cache_size = 0，并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。如果已经启用了query cache并且还没有发现任何问题，query cache可能有用。如果想停用它，那就得小心了。

innodb_buffer_pool_size

缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

innodb_log_file_size

redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，可以同时拥有较高的写入性能和崩溃恢复性能。在MySQL 5.6里可以被提高到4GB以上。如果应用程序需要频繁的写入数据，可以一开始就把它这是成4G。

max_connections

max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

back_log

要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间检查连接并且启动一个新线程。back_log指明在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，需要增加它，换句话说，该值对到来的tcp/ip连接的侦听队列的大小。

Innodb配置

innodb_file_per_table

이 설정은 InnoDB가 모든 테이블의 데이터와 인덱스를 공유 테이블 공간에 저장해야 하는지(innodb_file_per_table = OFF) 또는 각 테이블의 데이터를 별도의 .ibd 파일에 배치해야 하는지(innodb_file_per_table = ON) 여부를 알려줍니다. 테이블당 하나의 파일을 사용하면 테이블을 삭제하거나 자르거나 재구축할 때 디스크 공간을 회수할 수 있습니다. 이는 데이터 압축과 같은 일부 고급 기능에도 필요합니다. 그러나 성능상의 이점은 없습니다. MySQL 5.6에서 이 속성의 기본값은 ON입니다.

innodb_flush_log_at_trx_commit

기본값은 1이며 이는 InnoDB가 ACID 기능을 완벽하게 지원함을 나타냅니다. 이 값은 마스터 노드와 같이 데이터 보안이 우려되는 경우 가장 적합합니다. 그러나 디스크(읽기 및 쓰기) 속도가 느린 시스템의 경우 리두 로그에 변경 사항을 플러시할 때마다 추가 fsync가 필요하므로 엄청난 오버헤드가 발생합니다. 값이 0이면 더 빠르지만 시스템 충돌 시 일부 데이터가 손실될 수 있으므로 원 패스는 백업 노드에만 적용됩니다.

innodb_flush_method

이 구성은 데이터와 로그가 하드 디스크에 기록되는 방식을 결정합니다. 일반적으로 하드웨어 RAID 컨트롤러가 있고 해당 독립 캐시가 후기입 메커니즘을 사용하고 배터리 전원 오류 보호 기능이 있는 경우 O_DIRECT로 구성해야 하며, 그렇지 않은 경우 대부분의 경우 fdatasync(기본값)로 설정해야 합니다. sysbench는 이 옵션을 결정하는 데 도움이 되는 훌륭한 도구입니다.

innodb_log_buffer_size

이 구성은 아직 실행되지 않은 트랜잭션에 할당되는 버퍼를 결정합니다. 그러나 트랜잭션에 바이너리 대형 개체 또는 대형 텍스트 필드가 포함되어 있는 경우 Innodb_log_waits 상태 변수를 확인하세요. 0이 아닌 경우 innodb_log_buffer_size를 늘리세요.

기타 구성

log_bin

데이터베이스 서버가 마스터 노드의 백업 노드 역할을 하는 경우 바이너리 로그를 활성화해야 합니다. 서버가 하나만 있는 경우에도 특정 시점 데이터 복구를 수행하려는 경우 유용합니다. 일단 생성된 바이너리 로그는 영구적으로 저장됩니다. 디스크 공간이 부족해지는 것을 원하지 않으면 PURGE BINARY LOGS를 사용하여 오래된 파일을 제거하거나 만료_logs_days를 설정하여 로그가 자동으로 제거될 일 수를 지정할 수 있습니다. 바이너리 로깅에는 오버헤드가 없는 것이 아니므로 기본 노드가 아닌 레플리카 노드에서 필요하지 않은 경우 이 옵션을 끄는 것이 좋습니다.

interactive_timeout

서버가 대화형 연결을 닫기 전에 작업을 기다리는 시간(초)입니다. 대화형 클라이언트는 mysql_real_connect()에 client_interactive 옵션을 사용하는 클라이언트로 정의됩니다. 기본값은 28800이며, 7200으로 변경하는 것을 권장합니다.

table_open_cache

MySQL은 테이블을 열 때마다 table_open_cache 캐시로 일부 데이터를 읽습니다. MySQL은 이 캐시에서 해당 정보를 찾을 수 없으면 디스크에서 해당 정보를 읽습니다. 시스템에 200개의 동시 연결이 있다고 가정하면 table_open_cache가 큰 값으로 설정된 경우 시스템이 너무 많은 파일을 처리할 수 없는 경우 이 매개변수를 200*N(N은 각 연결에 필요한 파일 설명자 수)으로 설정해야 합니다. 설명 기호를 사용하면 클라이언트가 실패하고 연결을 설정할 수 없습니다.

max_allowed_packet

허용되는 패킷 크기, 필요할 때만 추가 메모리가 할당되므로 이 변수의 값을 늘리는 것이 안전합니다. 예를 들어, MySQLd는 긴 쿼리를 실행하거나 MySQLd가 큰 결과 행을 반환해야 하는 경우에만 더 많은 메모리를 할당합니다. 이 변수의 작은 기본값은 클라이언트와 서버 간의 오류 패킷을 캡처하고 실수로 큰 패킷을 사용하여 메모리 오버플로가 발생하지 않도록 하기 위한 예방 조치입니다.

skip_name_resolve

클라이언트가 데이터베이스 서버에 연결할 때 DNS가 느리면 연결 설정도 느려집니다. 따라서 DNS 조회를 수행하지 않고 서버를 시작할 때는 Skip_name_resolve 옵션을 끄는 것이 좋습니다.

SQL 문 튜닝

애플리케이션 계층에서는 pt 도구와 느린 쿼리 로그의 조합을 통해 전체 테이블 스캔의 문을 쉽게 식별할 수 있습니다.

기본 원칙

• 전체 테이블 스캔 방지

• 인덱스 구축

• 클라이언트에게 많은 양의 데이터를 반환하지 않도록 노력하세요. 데이터의 양이 너무 많으면 해당 요구 사항이 합리적인지 고려해야 합니다

• 대규모 트랜잭션 작업을 피하고 시스템 동시성 기능을 향상시키세요

• 커서 기반 방법이나 임시 테이블 방법을 사용하기 전에 먼저 문제를 해결할 수 있는 집합 기반 솔루션을 찾아야 합니다. 일반적으로 집합 기반 방법이 더 효율적입니다. 커서는 효율성이 떨어지므로 사용하지 마십시오.

팁

where

• where 절에서 피해야 하는 조건에 대해 가능한 한 != 또는 a8093152e673feb7aba1828c43532094 연산자를 사용하십시오. 그렇지 않으면 엔진이 인덱스 사용을 포기하고 전체 테이블 스캔을 수행합니다.

• 조건을 연결하기 위해 where 절에 or를 사용하는 것을 피해야 합니다.

• In and not in 대신에 Union 사용을 고려해 보세요. 또한 연속 값을 사용하려면 in

• in 대신에 between을 사용하지 말고 in을 사용하지 마세요.

  where 절

데이터 유형 정보

• 숫자 정보만 포함하는 필드의 경우 문자 유형으로 디자인하지 마십시오. . 이는 쿼리 및 연결의 효율성을 감소시키고 스토리지 오버헤드를 증가시킵니다.

• 가변 길이 필드의 저장 공간이 작고 쿼리의 경우 비교적 작은 필드에서 검색 효율성이 높기 때문에 char/nchar 대신 varchar/nvarchar를 최대한 사용하십시오. 분명히 더 높습니다.

• 데이터베이스에 NULL을 두지 않는 것이 가장 좋습니다. 데이터베이스를 최대한 채우려면 NOT NULL을 사용하세요. 메모, 설명, 설명 등은 NULL로 설정할 수 있습니다. 다른 경우에는 NULL을 사용하지 않는 것이 가장 좋습니다.

• select * from t를 어디서나 사용하지 말고, "*"를 특정 필드 목록으로 바꾸고, 사용하지 않는 필드를 반환하지 마세요.

임시 테이블 정보

• 임시 테이블을 자주 생성하고 삭제하지 않도록 하여 시스템 테이블 리소스 소비를 줄이세요. 일회성 이벤트의 경우 내보내기 테이블을 사용하는 것이 가장 좋습니다.

• 임시 테이블 생성 시 한번에 삽입되는 데이터의 양이 많은 경우에는 create table 대신 select into를 사용하여 대량의 로그 발생을 방지하여 속도를 향상시킬 수 있습니다. ; 데이터의 양이 많지 않은 경우에는 시스템 테이블의 자원을 절약하기 위해 먼저 테이블을 생성한 후 삽입해야 합니다.

• 임시 테이블을 사용하는 경우 마지막에 모든 임시 테이블이 명시적으로 삭제되면 먼저 테이블을 잘라낸 다음 테이블을 삭제합니다. 이렇게 하면 시스템 테이블이 장기간 잠기는 것을 방지할 수 있습니다.

색인 정보

• 먼저 where 및 order by와 관련된 열에 대한 색인 생성을 고려해야 합니다.

• 인덱스 필드를 조건으로 사용할 때, 인덱스가 복합 인덱스인 경우 시스템이 인덱스를 사용하는지 확인하기 위해 인덱스의 첫 번째 필드를 조건으로 사용해야 하며, 그렇지 않으면 인덱스가 사용되지 않으며 필드 순서는 가능할 때마다 인덱스 순서와 일치해야 합니다.

• 인덱스는 많을수록 좋습니다. 인덱스는 해당 선택의 효율성을 향상시킬 수 있지만 삽입 또는 업데이트 중에 다시 작성될 수 있기 때문에 삽입 및 업데이트의 효율성도 감소합니다. .index이므로 경우에 따라 다릅니다. 하나의 테이블에 인덱스가 7개를 넘지 않는 것이 가장 좋으며, 인덱스가 너무 많으면 일반적으로 사용되지 않는 일부 컬럼에 인덱스를 구축할 필요가 있는지 고려해야 합니다.

데이터베이스 아키텍처 튜닝

최하위에서 애플리케이션 계층, 최종적으로 아키텍처 계층까지 그러나 비즈니스 로직이 없는 아키텍처를 이야기하는 것은 불량배. 데이터베이스 아키텍처 역시 비즈니스 시스템에 따라 달라지며, 비즈니스 시스템을 안정적이고 유연하게 서비스하는 것이 핵심이다. 아키텍처 튜닝 방향은 다음과 같습니다.

• 파티션 및 테이블

• 비즈니스 하위 라이브러리

• 슬레이브 동기화와 읽기 및 쓰기의 주요 분리

• 데이터 캐시

• 마스터-슬레이브 상시 대기 및 HA 활성-활성

• …..

위 내용은 MySQL 성능 튜닝 내용이며, 기타 관련 내용은 PHP 중국어를 참고하시기 바랍니다. 홈페이지(www.php.cn)!