MySQL 최적화 아이디어 소개

이 기사의 내용은 mysql 최적화 아이디어에 대한 소개입니다. 도움이 필요한 친구들이 참고할 수 있기를 바랍니다.

데이터베이스 수준의 문제 해결을 위한 아이디어

일반적인 비상 튜닝 아이디어:
갑작스러운 업무 처리 지연이 발생할 경우 정상적인 업무 처리가 불가능합니다! 당장 해결해야 할 시나리오!

1、show processlist

2、explain select id ,name from stu where name='clsn'; # ALL id name age sex
select id,name from stu where id=2-1 函数 结果集>30;
show index from table;

3、通过执行计划判断，索引问题(有没有、合不合理)或者语句本身问题

4、show status like '%lock%'; # 查询锁状态
kill SESSION_ID; # 杀掉有问题的session

일반적인 튜닝 아이디어:
주기적인 비즈니스 지연의 경우 예를 들어 매일 10-11시에 비즈니스가 극도로 느리지만 여전히 사용할 수 있으며 이 기간 이후에는 괜찮습니다.

1. 슬로우로그를 확인하고, 슬로우로그를 분석하고, 슬로우 쿼리문을 분석합니다.

2. 모든 느린 문장을 특정 우선순위에 따라 하나씩 확인하세요.

3. 상위 SQL을 분석하고, 디버깅 설명을 수행하고, 명령문 실행 시간을 확인합니다.

4. 인덱스나 명령문 자체를 조정하세요.

1. 시스템 수준

cpu:
vmstat, sar top, htop, nmon, mpstat

메모리:
free, ps -aux,

IO 장치(디스크, 네트워크):
iostat, ss, netstat, iptraf , iftop, lsof,

vmstat 명령 설명:
Procs: r CPU 시간을 기다리고 있는 프로세스 수를 표시합니다. b 중단 불가능한 절전 모드의 프로세스 수를 표시합니다. I/O

Memory를 기다리는 중: swpd는 디스크로 스왑된 데이터 블록 수를 표시합니다. 사용되지 않은 데이터 블록, 사용자 버퍼 데이터 블록 및 운영 체제에서 사용하는 데이터 블록의 수

스왑: 운영 체제가 초당 디스크에서 메모리로, 메모리에서 디스크로 스왑하는 데이터 블록의 수입니다. s1 및 s0은 0

Io인 것이 바람직합니다. b1이 장치에서 읽혀지는 초당 장치 b0에 기록되는 데이터 블록 수입니다. 디스크 I/O 반영

System: 초당 발생하는 인터럽트(in) 및 컨텍스트 전환(cs) 수를 표시

Cpu: 사용자 코드, 시스템 코드, 유휴, 대기를 실행하는 데 사용된 횟수 표시 /O CPU time

iostat 명령 설명
명령 예: iostat -dk 1 5
iostat -d -k -x 5 (장치 사용량(%util) 및 응답 시간(await) 보기)

tps: 장치의 시간(대기) 두 번째 전송 횟수입니다. "전송"은 "I/O 요청"을 의미합니다. 여러 논리적 요청을 "단일 I/O 요청"으로 결합할 수 있습니다.

iops: 하드웨어가 공장에서 출고될 때 제조업체는 초당 최대 IO 수를 정의합니다. "1회 전송" 요청의 크기는 알 수 없습니다.

kB_read/s: 초당 장치에서 읽은 데이터 양(드라이브 표현)

KB_wrtn/s: 초당 장치에 쓴 데이터 양(드라이브 표현) 읽은 데이터 ;

kB_wrtn: 작성된 총 데이터 양은 킬로바이트입니다.

시스템 수준 문제에 대한 해결 방법

1. 부하가 높은 쪽이 낫다고 생각하시나요, 아니면 낮은 쪽이 낫다고 생각하시나요?

실제 생산에서는 일반적으로 CPU 점유율이 90%를 넘지 않는 이상 문제가 없다고 여겨집니다.

물론 다음과 같은 특별한 상황도 배제되지 않습니다.

문제 1: 높은 CPU 로드, 낮은 IO 로드

메모리 부족

디스크 성능 저하

SQL 문제------>데이터베이스 계층으로 이동하여 추가 문제 해결 SQL 문제

IO에 문제가 있습니다(디스크가 중요함, RAID 디자인이 좋지 않음, RAID가 다운그레이드됨, 잠금, 단위 시간당 tps가 너무 높음)

tps가 너무 높음: 많은 작은 데이터 IO, 많은 전체 테이블 스캔

질문 2: 높은 IO 로드, 낮은 CPU 로드

많은 수의 작은 IO 쓰기 작업:

autocommit, 많은 수의 작은 IO 생성

IO/PS 고정 디스크 값에 따라 하드웨어가 공장에서 출고될 때 제조업체는 초당 최대 IO 수를 정의합니다.

많은 수의 대규모 IO 쓰기 작업

SQL 문제가 발생할 확률이 상대적으로 높습니다.

문제 3: IO 및 CPU 부하가 매우 높습니다.

부족한 하드웨어 또는 SQL 문제

다섯 번째, 기본 최적화

최적화 아이디어

1. 문제 지점 찾기:

하드웨어--> 애플리케이션--> 데이터베이스--> 아키텍처(고가용성, 읽기-쓰기 분리, 하위 데이터베이스 및 하위 테이블)

처리 방향:

최적화 목표 및 성능을 명확히 하고 보안을 손상시키고 문제가 발생하기 전에 예방하세요

하드웨어 최적화

1. 호스트 측면:

데이터베이스 유형, 호스트 CPU 선택, 메모리 용량 선택, 디스크 선택에 따라

메모리 및 디스크 리소스 균형을 맞추세요

랜덤 I/O 및 순차 I/O

호스트 RAID 카드의 BBU(배터리 백업 장치)가 꺼졌습니다.

CPU 선택:

CPU의 두 가지 핵심 요소: 코어 수 및 기본 주파수

에 따라 선택 다양한 비즈니스 유형에 적용:

cpu 집약적 ：더 많은 계산, OLTP에는 더 많은 CPU 및 높은 빈도의 코어가 필요함

IO 집약적: 쿼리 비교, OLAP에는 더 많은 코어가 필요하지만 빈도가 반드시 높은 것은 아닙니다

메모리 선택:

OLAP 유형 데이터베이스의 경우 데이터 수집 규모와 관련하여 더 많은 메모리가 필요합니다.

OLTP 유형 데이터의 일반 메모리는 CPU 코어 수의 2~4배이며 모범 사례가 없습니다.

저장소:

저장된 데이터의 다양한 유형에 따라 다양한 저장 장치 선택

합리적인 RAID 수준 구성(raid 5, raid 10, 핫 스페어 디스크)

운영 체제의 경우 너무 특별할 필요는 없습니다. 선택 , 중복성(raid1)을 만드는 것이 가장 좋습니다(ssd, sas, sata)

raid 카드: 호스트 RAID 카드 선택:

운영 체제 디스크 중복성 실현(raid1)

메모리 및 디스크 리소스 균형

랜덤 I/O 및 순차 I/O

호스트 RAID 카드의 BBU(배터리 백업 장치)를 닫아야 합니다

네트워크 장비:
네트워크 장비(스위치, 라우터, 네트워크 케이블, 네트워크 카드, HBA 카드)를 지원하려면 더 높은 트래픽을 사용합니다.

참고: 위 계획은 시스템 초기 설계 중에 고려해야 합니다.

1. 서버 하드웨어 최적화

1. 물리적 상태 표시등:

2. 관리 장비와 함께 제공: 원격 제어 카드(FENCE 장비: ipmi ilo idarc), 전원 켜기 및 끄기, 하드웨어 모니터링.

3. 타사 모니터링 소프트웨어 및 장비(snmp, 에이전트)는 물리적 시설을 모니터링합니다.

4. 스토리지 장비: 모니터링 플랫폼 내장. EMC2(HP 인수), Hitachi(hds), IBM 저가형 OEM hds, 고급 스토리지는 자체 기술, Huawei 스토리지

1. 시스템 최적화

Cpu:
기본적으로 조정이 필요하지 않고 열심히만 하면 됩니다. 하드웨어 선택에 .

메모리:
기본적으로 조정이 필요하지 않으며 하드웨어 선택에만 집중하세요.

SWAP:
MySQL에서는 스왑 사용을 피하세요. Alibaba Cloud 서버의 기본 스왑은 0

IO입니다.
raid, no lvm, ext4 또는 xfs, ssd, IO 예약 전략

스왑 조정(스왑 파티션을 사용하지 않음)

이 매개변수는 Linux가 스왑을 사용하는 경향이 있는지 여부를 결정합니다. , 여전히 파일 시스템 캐시를 해제하는 경향이 있습니다. 메모리가 부족한 경우 값이 낮을수록 파일 시스템 캐시를 확보할 가능성이 높아집니다. 물론 이 매개변수는 스왑 사용 확률을 줄일 수 있을 뿐, Linux가 스왑을 사용하는 것을 막을 수는 없습니다.

MySQL 구성 매개변수 innodb_flush_method를 수정하고 O_DIRECT 모드를 활성화하세요. 이 경우 InnoDB의 버퍼 풀은 파일 시스템 캐시를 직접 우회하여 디스크에 액세스하지만 리두 로그는 여전히 파일 시스템 캐시를 사용합니다. Redo 로그가 덮어쓰기 모드에 있다는 점은 주목할 가치가 있습니다. 파일 시스템 캐시를 사용하더라도 IO 스케줄링 전략:

시스템 매개변수 조정

1. Linux 시스템 커널 매개변수 최적화:

사용자 제한 매개변수(MySQL은 다음 구성을 설정할 필요가 없습니다):

애플리케이션 최적화

1. 비즈니스 애플리케이션과 데이터베이스 애플리케이션은 독립적입니다. 방화벽: iptables, selinux 및 기타 쓸모 없는 서비스(폐쇄):

시작하지 마세요. 그래픽 인터페이스가 설치된 경우 그래픽 서버 인터페이스 런레벨 3. 또한 향후 우리 비즈니스에 실제로 MySQL이 필요한지, 아니면 다른 유형의 데이터베이스를 사용할지 생각해 보십시오. 데이터베이스 사용의 가장 높은 상태는 데이터베이스를 사용하지 않는 것입니다.

6. 데이터베이스 최적화

SQL 최적화 방향:

실행 계획, 인덱스, SQL 재작성

아키텍처 최적화 방향:

고가용성 아키텍처, 고성능 아키텍처, 하위 데이터베이스 및 하위 테이블

데이터베이스 매개변수 최적화

1. 조정:

전체 인스턴스(고급 최적화, 확장)

연결 레이어(기본 최적화)

합리적인 연결 고객 및 연결 방법 설정

이 기사는 여기에서 끝났습니다. 더 많은 흥미로운 내용을 보려면 PHP 중국어에 주목하세요. 웹사이트

MySQL 튜토리얼 영상

칼럼!

위 내용은 MySQL 최적화 아이디어 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!