CentOS 7 서버에서 MySQL 8.0의 성능 조정 및 최적화 방법

소개

시작하기 전에 MySQL 성능에 영향을 미치는 요소를 이해해야 올바른 방식으로 MySQL 서버를 조정 및 최적화하고 올바른 결과를 얻을 수 있습니다. 주요 요인은 다음과 같습니다.


  • 검색되는 데이터의 양>
  • 사용 가능한 리소스 (예 : CPU, 메모리 (RAM))
  • MySQL 서버에 의한로드 실행

MySQL 서버 성능 저하의 원인은 다음과 같습니다.

  • 불쌍한 데이터베이스 디자인
  • 하드웨어 병목
  • 불쌍한 코딩
  • 불완전한 인덱싱
  • 비효율적 인 구성

이 기사에서는 CentOS 7 Linux VPS에서 MySQL 8.0의 성능을 조정하고 최적화하는 방법을 보여줍니다..

전제 조건 :

  • CentOS 7 VPS 설정
  • MySQL 8.0 서버 설치
  • sudo 권한을 가진 사용자 작성

mysqltuner 및 튜닝 프라이머 설치

mysqltuner 설치

Mysqltuner는 고성능 MySQL 튜닝 Perl 스크립트로 MySQL 서버의 상태를 스냅 샷으로 제공하고 성능, 안정성, 효율성 향상, 향상에 대한 특정 권장 사항을 제공합니다..

mysqltuner를 설치하려면 아래 명령을 실행하십시오

mysqltuner 패키지가 epel-release 저장소에 있으므로 epel-release를 먼저 설치하십시오.

$ sudo yum 설치 -y epel-release

그런 다음 mysqltuner를 설치하십시오.

$ sudo yum 설치 -y mysqltuner

튜닝 프라이머 설치

Tuning Primer는 MySQL 서버 내부에서 정보를 가져오고 서버 변수 조정에 대한 권장 사항을 제공하는 셸 스크립트입니다.

튜닝 프라이머를 설치하려면 아래 명령을 실행하십시오.

$ sudo yum 설치 bc -y
$ git clone https://github.com/RootService/tuning-primer.git

특별 참고 사항 : git이 설치되지 않은 경우 다음을 사용하여 설치할 수 있습니다

$ sudo yum 설치 git -y

튜닝 프리 머 디렉토리로 변경하고 튜닝 프리 머 스크립트를 다음과 같이 실행 가능하게하십시오.

$ cd 튜닝 프라이머
$ sudo chmod + x tuning-primer.sh

MySQL 서버 튜닝 및 최적화

MySQL 서버의 튜닝은 MySQL 구성 파일에서 수행됩니다..

/etc/my.cnf

mysqltuner를 실행.

$ sudo mysqltuner

프롬프트가 표시되면 모든 권한 (이 경우 “root”)과 비밀번호를 가진 mysql 사용자를 입력하십시오.

[linuxuser @ centos7-hostadvice4 ~] $ sudo mysqltuner
MySQL 관리 로그인을 입력하십시오 : root
MySQL 관리 비밀번호를 입력하십시오 : >> MySQLTuner 1.6.0-메이든 헤이든
>> http://mysqltuner.com/에서 버그 보고서, 기능 요청 및 다운로드
>> 추가 옵션 및 출력 필터링을 위해 ‘–help’로 실행
[-] MySQLTuner 스크립트의 버전 확인을 건너 Skip
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
[!!] 현재 지원되지 않는 MySQL 버전 8.0.11을 실행 중
[확인] 64 비트 아키텍처에서 작동

——– 스토리지 엔진 통계 ——————————————-
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
[-] 상태 : + ARCHIVE + BLACKHOLE + CSV -FEDERATED + InnoDB + MRG_MYISAM
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
[-] InnoDB 테이블의 데이터 : 16K (테이블 : 1)
[OK] 총 조각화 된 테이블 : 0
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.

——– 보안 권장 사항 ——————————————-
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
[확인] 모든 데이터베이스 사용자에게 익명 계정이 없습니다
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
1 행의 오류 1054 (42S22) : ‘where 절’의 알 수없는 열 ‘password’
[확인] 모든 데이터베이스 사용자에게 암호가 할당되어 있습니다
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
1 행의 오류 1064 (42000) : SQL 구문에 오류가 있습니다. 1 행의 ‘(user) OR CAST (이진 비밀번호) = PASSWORD (UPPER (사용자)) OR CAST (비밀번호 B’) 근처에서 사용할 올바른 구문은 MySQL 서버 버전에 해당하는 매뉴얼을 확인하십시오.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
[!!] 기본 비밀번호 파일 목록이 없습니다 !
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
mysql : [경고] 명령 행 인터페이스에서 암호를 사용하면 안전하지 않을 수있다.
/ bin / mysqltuner 라인 1934에서 (+)와 함께 초기화되지 않은 값 사용, <>
2 호선 (# 1)
(W 초기화되지 않음) 정의되지 않은 값이 이미있는 것처럼 사용되었습니다.
한정된. 그것은로 해석되었다 "" 또는 0이지만 실수 일 수 있습니다..
이 경고를 표시하지 않으려면 정의 된 값을 변수에 지정하십시오.

정의되지 않은 것을 파악할 수 있도록 펄은
정의되지 않은 변수 (있는 경우)의 이름 일부 경우에
이 작업을 수행 할 수 없으므로 어떤 작업을 사용했는지 알려줍니다.
정의되지 않은 값. 그러나 펄은 프로그램을 최적화합니다.
경고에 표시된 작업이 반드시 나타나지 않을 수 있습니다
말 그대로 프로그램에서. 예를 들어, "그 $ foo" 보통
에 최적화 "그 " . $ foo, 경고는
연결이 없더라도 연결 (.) 연산자 에
당신의 프로그램.

/ bin / mysqltuner 라인 1934에서 나누기 (/)에 초기화되지 않은 값 사용, <>
2 호선 (# 1)
초기화되지 않은 값 $ mystat {"Qcache_lowmem_prunes"} 숫자 eq (==)
/ bin / mysqltuner 1949 행, <> 2 호선 (# 1)

——– 성능 지표 ————————————————-
[-] 최대 : 1 시간 37 분 3 초 (12 q [0.002 qps], 20 콘, TX : 20K, RX : 2K)
[-] 읽기 / 쓰기 : 100 % / 0 %
[-] 이진 로깅이 활성화되었습니다 (GTID MODE : OFF)
[-] 총 버퍼 : 168.0M 전역 + 스레드 당 1.1M (최대 스레드 151 개)
[확인] 최대 메모리 사용량에 도달 : 169.1M (설치된 RAM의 4.78 %)
[OK] 가능한 최대 메모리 사용량 : 341.4M (설치된 RAM의 9.65 %)
[확인] 느린 쿼리 : 0 % (0/12)
[확인] 사용 가능한 최대 연결 사용량 : 0 % (1/151)
[!!] 연결 중단 : 40.00 % (8/20)
초기화되지 않은 값 $ myvar {"query_cache_size"} 숫자 lt (<)에서
/ bin / mysqltuner 2281 행, <> 2 호선 (# 1)
[!!] 쿼리 캐시가 비활성화되었습니다
[확인] 디스크에 생성 된 임시 테이블 : 0 % (디스크에 0 / 총 2 개)
[OK] 스레드 캐시 적중률 : 95 % (1 개 생성 / 20 개 연결)
[OK] 테이블 캐시 적중률 : 83 % (118 열림 / 142 열림)
[확인] 열린 파일 사용 한도 : 0 % (2 / 10K)
[OK] 즉시 획득 한 테이블 잠금 : 100 % (즉시 2 개 / 잠금 2 개)
[OK] Binlog 캐시 메모리 액세스 : 0 % (0 Memory / 0 Total)

——– MyISAM 통계 —————————————————–
[!!] 키 버퍼 사용 : 18.2 % (1M 사용 / 8M 캐시)
[!!] MyISAM 인덱스 크기를 계산할 수 없음-루트 사용자로 스크립트를 다시 실행

——– InnoDB 지표 —————————————————–
[-] InnoDB가 활성화되었습니다.
[OK] InnoDB 버퍼 풀 / 데이터 크기 : 128.0M / 16.0K
[OK] InnoDB 버퍼 풀 인스턴스 : 1
[!!] InnoDB 사용 버퍼 : 11.08 % (사용 된 908/8192)
[OK] InnoDB 읽기 버퍼 효율 : 94.07 % (12243 적중 / 총 13015)
[!!] InnoDB 쓰기 버퍼 효율 : 0.00 % (0 hits / 1 total)
[OK] InnoDB 로그 대기 : 0.00 % (0 대기 / 12 쓰기)

——– AriaDB 지표 —————————————————–
[-] AriaDB 비활성화.

——– 복제 메트릭 ————————————————-
[-]이 서버에 대한 복제 슬레이브가 없습니다.
[-] 독립형 서버입니다..

——– 추천 —————————————————–
일반적인 권장 사항 :
MySQL은 지난 24 시간 이내에 시작되었습니다. 권장 사항이 정확하지 않을 수 있습니다
닫히지 않은 연결 및 네트워크 문제 감소 또는 제거
조정할 변수 :
query_cache_size (>= 8M)
[linuxuser @ centos7-hostadvice4 ~] $

“권장 사항”에서 출력의 마지막 부분에 초점을 맞추고 증가해야 할 변수를 확인하십시오.

mysql 구성 파일의 백업을 작성하고 실제 구성 파일을 편집하십시오..

$ sudo cp /etc/my.cnf /etc/my.cnf.bak
$ sudo vim /etc/my.cnf

권장 사항에 따라 파일 변수를 조정하십시오. 기본 변수 값을 주석 처리하고 변수 값을 기본값의 두 배로 늘릴 수 있습니다. 실제로 서버에 가장 적합한 값을 찾는 데 며칠이 걸릴 수 있습니다.

변경 후 mysql 서버를 다시 시작하십시오..

$ sudo systemctl 재시작 mysql

특별 참고 사항 : 다시 시작하지 못하면 돌아가서 새 값을 주석 처리하고 하나씩 조정을 시작하고 어떤 변수 조정으로 인해 서버 오류가 발생하는지 확인하십시오.

서버가 성공적으로 다시로드되면 mysqltuner를 다시 실행하고 변수 값 조정에 대한 추가 권장 사항이 있는지 확인하십시오..

프라이머 튜닝을위한 유사한 프로세스 수행.

튜닝 프라이머 실행

$ sudo 튜닝 프라이머
[linuxuser @ centos7-hostadvice4 튜닝 프라이머] $ sudo ./tuning-primer.sh
— MySQL 성능 튜닝 프라이머 2.0.1-r1 —
– 게시자 : Matthew Montgomery –
– 게시자 : Markus Kohlmeyer –

MySQL 버전 8.0.11 x86_64

가동 시간 = 0 일 0 시간 2 분 52 초
평균 qps = 0
총 질문 = 4
연결된 스레드 = 1

경고 : 서버가 최소 48 시간 동안 실행되지 않았습니다.
이러한 권장 사항을 사용하는 것이 안전하지 않을 수 있습니다

각 방법에 대한 자세한 정보를 찾으려면
런타임 변수는 성능 방문에 영향을줍니다.
http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
http://www.mysql.com/products/enterprise/advisors.html 방문
MySQL의 엔터프라이즈 모니터링 및 자문 서비스에 대한 정보

느린 질문
느린 쿼리 로그가 활성화되지 않았습니다.
현재 long_query_time = 10.000000 초.
25 개 중 0 개가 10.000000 초보다 오래 걸립니다. 완료
long_query_time이 너무 높을 수 있습니다. 일반적으로 5 초 미만으로 설정했습니다..

이진 업데이트 로그
이진 업데이트 로그가 활성화되었습니다
expire_logs_days가 설정되지 않았습니다.
mysqld는 RESET MASTER 또는 PURGE MASTER LOGS 명령이 수동으로 실행될 때까지 전체 바이너리 로그를 유지합니다
expire_logs_days를 설정하면 오래된 바이너리 로그를 자동으로 제거 할 수 있습니다
http://dev.mysql.com/doc/refman/8.0/en/purge-master-logs.html을 참조하십시오

노동자 실
현재 thread_cache_size = 9
현재 쓰레드 _ 캐시 = 0
현재 쓰레드 _per_sec = 0
히스토리 스레드 _per_sec = 0
당신의 thread_cache_size는 괜찮습니다

최대 연결
현재 최대 연결 수 = 151
현재 쓰레드 _ 연결됨 = 1
역사적인 max_used_connections = 1
사용 된 연결 수는 구성된 최대 값의 0 %입니다..
구성된 max_connections의 10 % 미만을 사용하고 있습니다..
max_connections를 낮추면 메모리 초과 할당을 피할 수 있습니다.
보다 "메모리 사용량" 과도하게 할당되지 않도록 섹션

이노 DB 상태
현재 InnoDB 인덱스 공간 = 240K
현재 InnoDB 데이터 공간 = 2M
현재 InnoDB 버퍼 풀 사용 가능 = 88 %
현재 innodb_buffer_pool_size = 128M
innodb 인덱스가 차지하는 공간의 양에 따라 안전 할 수 있습니다
이 값을 총 시스템 메모리의 2/3까지 증가

메모리 사용량
최대 메모리 할당량 : 153 M
구성된 최대 스레드 당 버퍼 : 178 M
구성된 최대 글로벌 버퍼 : 152M
구성된 최대 메모리 제한 : 330 M
임시 테이블 당 16M 더하기
물리 메모리 : 3.45 G
최대 메모리 제한이 허용 가능한 표준 내에있는 것 같습니다

키 버퍼
키 읽기 없음?!
일부 색인 사용을 진지하게 살펴보십시오.
현재 MyISAM 인덱스 공간 = 0 바이트
현재 키 버퍼 크기 = 8 M
키 캐시 미스율은 1 : 0입니다.
키 버퍼 프리 비율 = 81 %
당신의 key_buffer_size는 괜찮은 것 같습니다

QUERY CACHE
쿼리 캐시가 지원되지만 활성화되지 않았습니다
아마도 당신은 query_cache_size를 설정해야합니다

열악한 작동
현재 sort_buffer_size = 256K
현재 read_rnd_buffer_size = 256K
정렬 작업이 수행되지 않았습니다
정렬 버퍼가 괜찮은 것 같습니다

조인
현재 join_buffer_size = 260.00 K
조인이 인덱스를 올바르게 사용할 수없는 쿼리가 3 개 있습니다.
활성화해야합니다 "로그 쿼리를 사용하지 않는 인덱스"
그런 다음 느린 쿼리 로그에서 인덱싱되지 않은 조인을 찾으십시오..

열린 파일 제한
현재 open_files_limit = 10000 개 파일
open_files_limit는 일반적으로 2x-3x 이상으로 설정해야합니다
MyISAM 사용량이 많은 경우 table_open_cache.
open_files_limit 값이 괜찮은 것 같습니다

테이블 캐시
현재 table_open_cache = 4000 테이블
현재 table_definition_cache = 2000 테이블
총 136 개의 테이블이 있습니다.
165 개의 열린 테이블이 있습니다.
table_open_cache 값은 괜찮은 것 같습니다

온도 표
현재 max_heap_table_size = 16 M
현재 tmp_table_size = 16M
임시 테이블 95 개 중 디스크에 0 %가 생성되었습니다.
작성된 디스크 tmp 테이블 비율이 양호 해 보입니다.

테이블 스캔
현재 read_buffer_size = 128K
현재 테이블 스캔 비율 = 793 : 1
read_buffer_size는 괜찮은 것 같습니다

테이블 잠금
현재 잠금 대기 비율 = 0 : 286
테이블 잠금은 괜찮은 것 같습니다

[linuxuser @ centos7-hostadvice4 튜닝 프라이머] $

특별 참고 사항 : mysql 성능을 최적화하고 개선하는 방법에 대한 권장 사항을 제공하므로 빨간색과 노란색으로 강조 표시된 텍스트에 더 집중하십시오.

권장 사항에 따라 파일 변수를 조정하십시오. 기본 변수 값을 주석 처리하고 권장 사항에 제안 된대로 변수 값을 조정할 수 있습니다.

변경 한 후 MySQL 서버를 다시 시작하십시오..

$ sudo systemctl 재시작 mysql

특별 참고 사항 : 다시 시작하지 못하면 돌아가서 새 값을 주석 처리하고 하나씩 조정을 시작하고 어떤 변수 조정으로 인해 서버 오류가 발생하는지 확인하십시오.

서버가 성공적으로 다시 시작되면 tuneprimer를 다시 실행하고 변수 값 조정에 대한 추가 권장 사항이 있는지 확인하십시오..

결론

튜닝 도구 mysqltuner 또는 Tuning-primer는 성능이 떨어지는 MySQL 서버를위한 솔루션이 아니라 MySQL 서버를 미세하게 조정하는 데 도움이되는 스크립트라는 점에 유의해야합니다..

최상의 성능을 위해,

  • 서버로 전송 된 쿼리를 철저히 검토하고 데이터베이스 구조 / 디자인 및 SQL 쿼리를 최적화
  • 소개에 설명 된대로 MySQL 성능에 영향을 미치는 요소 평가.
  • 성능 향상을 위해 물리적 서버의 하드웨어 업그레이드를 고려하고 하드웨어 사양에 따라 MySQL 구성을 최적화하십시오. 최고의 MySQL 호스팅 옵션은 HostAdvice를 참조하십시오.
  • MySQL 서버를 활용하는 애플리케이션을 기반으로 MySQL 구성을 조정하는 방법에 대한 추가 연구 수행.

의미있는 사용 통계를 얻기 위해 MySQL 서버를 다시 시작한 후 48 시간 동안 mysqltuner 또는 Tuning-primer 도구를 정기적으로 실행하는 것이 좋습니다..

다음 3 가지 MySQL 호스팅 서비스를 확인하십시오.

A2 호스팅

시작 가격 :
$ 3.92


신뢰할 수 있음
9.3


가격
9.0


사용자 친화적
9.3


지원하다
9.3


풍모
9.3

리뷰 읽기

A2 호스팅 방문

FastComet

시작 가격 :
$ 2.95


신뢰할 수 있음
9.7


가격
9.5


사용자 친화적
9.7


지원하다
9.7


풍모
9.6

리뷰 읽기

FastComet 방문

주인공

시작 가격 :
$ 0.99


신뢰할 수 있음
9.3


가격
9.3


사용자 친화적
9.4


지원하다
9.4


풍모
9.2

리뷰 읽기

Hostinger 방문

관련 사용법 기사

  • Ubuntu 18.04 VPS 또는 전용 서버에서 MySQL 5.7의 성능 조정 및 최적화 방법
    전문가
  • CentOS 7 VPS 또는 전용 서버에 phpMyAdmin을 설치하는 방법
    중급
  • Ubuntu 18.04 VPS 또는 전용 서버에서 MySQL 데이터베이스를 백업하는 방법
    중급
  • CentOS 7 Linux VPS 또는 전용 서버에서 MYSQL 8.0을 설치하고 데이터베이스를 만드는 방법
    중급
  • 새로운 MySQL 사용자 계정을 생성하고 MySQL 권한을 관리하는 방법
    초보자
Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me