Ubuntu 18.04 VPS 또는 전용 서버에서 MySQL 5.7의 성능 조정 및 최적화 방법

소개

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


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

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

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

특별 참고 사항 :보다 나은 웹 호스팅 서비스로 업그레이드하면 이러한 요소 중 몇 가지를 해결할 수 있습니다. HostAdvice는 전문가 및 사용자 리뷰를 기반으로 최고의 MySQL 호스팅 제공 업체를 제공합니다.

mysqltuner 및 튜닝 프라이머 설치

mysqltuner 설치

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

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

$ cd / tmp
$ sudo wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl –quiet –no-check-certificate –output-document = / usr / local / bin / mysqltuner
$ sudo chmod u + x / usr / local / bin / mysqltuner

튜닝 프라이머 설치

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

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

$ sudo apt install BC -y
$ sudo wget http://www.day32.com/MySQL/tuning-primer.sh –output-document = / usr / local / bin / tuning-primer
$ sudo chmod u + x / usr / local / bin / tuning-primer

MySQL 서버 튜닝 및 최적화

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

/etc/mysql/mysql.conf.d/mysqld.cnf

mysqltuner를 실행.

$ sudo mysqltuner
$ sudo mysqltuner
>> MySQLTuner 1.7.9-메이든 헤이든
>> http://mysqltuner.com/에서 버그 보고서, 기능 요청 및 다운로드
>> 추가 옵션 및 출력 필터링을 위해 ‘–help’로 실행

[-] MySQLTuner 스크립트의 버전 확인을 건너 Skip
[확인] 데비안 유지 보수 계정의 자격 증명을 사용하여 로그인.
[확인] 현재 지원되는 MySQL 버전 5.7.22-0ubuntu18.04.1을 실행 중
[확인] 64 비트 아키텍처에서 작동

——– 로그 파일 권장 사항 ——————————————————————
[-] 로그 파일 : /var/log/mysql/error.log(18K)
[확인] /var/log/mysql/error.log 로그 파일이 존재합니다
[확인] /var/log/mysql/error.log 로그 파일을 읽을 수 있습니다.
[확인] /var/log/mysql/error.log 로그 파일이 비어 있지 않습니다
[확인] /var/log/mysql/error.log 로그 파일이 32MB보다 작습니다.
[!!] /var/log/mysql/error.log에는 14 개의 경고가 있습니다..
[!!] /var/log/mysql/error.log에 3 개의 오류가 있습니다..
[-] /var/log/mysql/error.log에서 3 개의 시작이 감지되었습니다.
[-] 1) 2018-06-06T17 : 55 : 47.928847Z 0 [참고] / usr / sbin / mysqld : 연결 준비.
[-] 2) 2018-06-06T17 : 55 : 44.798684Z 0 [참고] mysqld : 연결 준비.
[-] 3) 2018-06-06T17 : 55 : 41.931105Z 0 [참고] mysqld : 연결 준비 완료.
[-] /var/log/mysql/error.log에서 2 개의 종료가 감지되었습니다.
[-] 1) 2018-06-06T17 : 55 : 46.410548Z 0 [참고] mysqld : 종료 완료
[-] 2) 2018-06-06T17 : 55 : 43.758366Z 0 [참고] mysqld : 종료 완료

——– 스토리지 엔진 통계 —————————————————————–
[-] 상태 : + ARCHIVE + BLACKHOLE + CSV -FEDERATED + InnoDB + MEMORY + MRG_MYISAM + MyISAM + PERFORMANCE_SCHEMA
[-] InnoDB 테이블의 데이터 : 16K (테이블 : 1)
[OK] 총 조각화 된 테이블 : 0

——– 보안 권장 사항 ——————————————————————
[확인] 데이터베이스 사용자에 대한 익명 계정이 없습니다
[확인] 모든 데이터베이스 사용자에게 암호가 할당되어 있습니다
[-] 버그 # 80860 MySQL 5.7 : validate_password가 활성화 될 때 비밀번호 테스트를 피하십시오

——– CVE 보안 권장 사항 ————————————————————–
[-] –cvefile 옵션이 정의되지 않아 건너 뜁니다.

——– 성능 지표 ———————————————————————–
[-] 최대 : 9m 8s (110 q [0.201 qps], 38 conn, TX : 187K, RX : 10K)
[-] 읽기 / 쓰기 : 98 % / 2 %
[-] 이진 로깅이 비활성화되었습니다
[-] 물리 메모리 : 3.6G
[-] 최대 MySQL 메모리 : 352.4M
[-] 기타 프로세스 메모리 : 175.4M
[-] 총 버퍼 : 192.0M 전역 + 스레드 당 1.1M (최대 스레드 151 개)
[-] P_S 최대 메모리 사용량 : 72B
[-] Galera GCache 최대 메모리 사용량 : 0B
[확인] 최대 메모리 사용량에 도달 : 194.1M (설치된 RAM의 5.26 %)
[OK] 가능한 최대 메모리 사용량 : 352.4M (설치된 RAM의 9.54 %)
[확인] 다른 프로세스에서 가능한 전체 메모리 사용량은 사용 가능한 메모리와 호환됩니다.
[확인] 느린 쿼리 : 0 % (0/110)
[확인] 사용 가능한 최대 연결 사용량 : 1 % (2/151)
[확인] 연결 중단 : 0.00 % (0/38)
[!!] 이름 확인이 활성화되었습니다 : 새로운 연결마다 역방향 이름 확인이 수행되어 성능이 저하 될 수 있습니다.
[!!] 뮤텍스 경합으로 인해 쿼리 캐시가 기본적으로 비활성화 될 수 있습니다.
[!!] 쿼리 캐시 효율 : 0.0 % (0 캐시 / 49 선택)
[확인] 하루에 캐시 캐시 제거 : 0
[OK] 임시 테이블이 필요한 정렬 : 0 % (0 임시 정렬 / 2 정렬)
[확인] 인덱스가없는 조인 없음
[확인] 디스크에서 생성 된 임시 테이블 : 4 % (디스크에서 36 / 총 846)
[OK] 스레드 캐시 적중률 : 94 % (2 개 생성 / 38 개 연결)
[OK] 테이블 캐시 적중률 : 95 % (591 개방 / 620 개방)
[확인] 열린 파일 제한 사용 : 1 % (50 / 5K)
[OK] 즉시 획득 한 테이블 잠금 : 100 % (158 즉시 / 158 잠금)

——– 성능 스키마 ————————————————————————
[-] P_S가 사용하는 메모리 : 72B
[-] Sys 스키마가 설치되었습니다.

——– ThreadPool 지표 ————————————————————————
[-] ThreadPool 통계가 비활성화되었습니다.

——– MyISAM 통계 —————————————————————————-
[!!] 키 버퍼 사용 : 18.3 % (3M 사용 / 16M 캐시)
[OK] 키 버퍼 크기 / 총 MyISAM 인덱스 : 16.0M / 43.0K
[!!] 읽기 키 버퍼 적중률 : 94.1 % (118 캐시 / 7 읽기)

——– InnoDB 지표 —————————————————————————-
[-] InnoDB가 활성화되었습니다.
[-] InnoDB 스레드 동시성 : 0
[OK] 테이블 당 InnoDB 파일이 활성화되었습니다
[OK] InnoDB 버퍼 풀 / 데이터 크기 : 128.0M / 16.0K
[!!] InnoDB 로그 파일 크기 / InnoDB 버퍼 풀 크기 (75 %) : 48.0M * 2 / 128.0M은 25 %와 같아야합니다.
[OK] InnoDB 버퍼 풀 인스턴스 : 1
[-] InnoDB 버퍼 풀 청크 수 : 1 버퍼 풀 인스턴스 1
[OK] Innodb_buffer_pool_chunk_size와 정렬 된 Innodb_buffer_pool_size & Innodb_buffer_pool_instances
[!!] InnoDB 읽기 버퍼 효율 : 88.39 % (1933 hits / 2187 total)
[!!] InnoDB 쓰기 로그 효율 : 0 % (총 5 회 / 0 회)
[OK] InnoDB 로그 대기 : 0.00 % (0 대기 / 5 쓰기)

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

——– TokuDB 지표 —————————————————————————-
[-] TokuDB가 비활성화되었습니다.

——– XtraDB 지표 —————————————————————————-
[-] XtraDB가 비활성화되었습니다.

——– RocksDB 지표 —————————————————————————
[-] RocksDB가 비활성화되었습니다.

——– 스파이더 지표 —————————————————————————-
[-] 스파이더가 비활성화되었습니다.

——– 메트릭 연결 —————————————————————————
[-] 연결이 비활성화되었습니다.

——– Galera 지표 —————————————————————————-
[-] Galera가 비활성화되었습니다.

——– 복제 메트릭 ———————————————————————–
[-] Galera 동기식 복제 : NO
[-]이 서버에 대한 복제 슬레이브가 없습니다.
[-] Binlog 형식 : ROW
[-] XA 지원 활성화 : ON
[-] 세미 동기식 복제 마스터 : 활성화되지 않음
[-] 세미 동기식 복제 슬레이브 : 활성화되지 않음
[-] 독립형 서버입니다

——– 추천 —————————————————————————
일반적인 권장 사항 :
/var/log/mysql/error.log 파일에 경고 라인 제어
/var/log/mysql/error.log 파일로 오류 라인 제어
최근 24 시간 이내에 MySQL이 시작되었습니다. 권장 사항이 정확하지 않을 수 있습니다
IP 또는 서브넷 만 사용하여 계정을 구성한 다음 skip-name-resolve = 1로 구성을 업데이트하십시오.
innodb_log_file_size 및 / 또는 innodb_log_files_in_group을 변경하기 전에 다음을 읽으십시오. http://bit.ly/2wgkDvS
조정할 변수 :
query_cache_size (= 0)
query_cache_type (= 0)
query_cache_limit (> 1M 또는 더 작은 결과 집합 사용)
가능한 경우 innodb_log_file_size는 (= 16M)이어야하므로 InnoDB 전체 로그 파일 크기는 버퍼 풀 크기의 25 %와 같습니다..

“권장 사항”섹션에서 출력의 마지막 부분에 중점을두고 증가해야하는 변수를 확인하십시오..

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

$ sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

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

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

$ sudo systemctl 재시작 mysql

특별 참고 사항 : 다시 시작하지 못하면 돌아가서 새 값을 주석 처리하고 하나씩 조정을 시작하고 서버 장애를 일으키는 변수 조정을 확인하십시오.

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

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

튜닝 프라이머 실행

$ sudo 튜닝 프라이머
$ sudo 튜닝 프라이머

— MySQL 성능 튜닝 프라이머 —
– 게시자 : Matthew Montgomery –

MySQL 버전 5.7.22-0ubuntu18.04.1 x86_64

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

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

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

느린 질문
느린 쿼리 로그가 활성화되지 않았습니다.
현재 long_query_time = 10.000000 초.
36 개 중 0 개가 10.000000 초보다 오래 걸립니다. 완료
long_query_time은 괜찮은 것 같습니다

이진 업데이트 로그
이진 업데이트 로그가 활성화되어 있지 않습니다.
특정 시점 복구를 수행 할 수 없습니다
http://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html을 참조하십시오

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

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

활성화 된 InnoDB 지원 없음!

메모리 사용량
최대 메모리 할당량 : 177 M
구성된 최대 스레드 당 버퍼 : 160 M
구성된 최대 글로벌 버퍼 : 176 M
구성된 최대 메모리 제한 : 336 M
물리 메모리 : 3.60 G
최대 메모리 제한이 허용 가능한 표준 내에있는 것 같습니다

키 버퍼
현재 MyISAM 인덱스 공간 = 43K
현재 키 버퍼 크기 = 16 M
키 캐시 미스율은 1 : 2입니다.
키 버퍼 프리 비율 = 81 %
당신의 key_buffer_size는 괜찮은 것 같습니다

QUERY CACHE
쿼리 캐시가 활성화되었습니다
현재 query_cache_size = 16M
현재 query_cache_used = 16K
현재 query_cache_limit = 1M
현재 쿼리 캐시 메모리 채우기 비율 = .10 %
현재 query_cache_min_res_unit = 4K
귀하의 query_cache_size가 너무 높은 것 같습니다.
아마도 당신은 다른 곳에서 이러한 자원을 사용할 수 있습니다
MySQL은 query_cache_limit 크기보다 큰 쿼리 결과를 캐시하지 않습니다.

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

조인
현재 join_buffer_size = 260.00 K
조인이 인덱스를 올바르게 사용할 수없는 쿼리가 0 개 있습니다.
조인이 인덱스를 올바르게 사용하는 것 같습니다

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

테이블 캐시
현재 table_open_cache = 2000 테이블
현재 table_definition_cache = 1400 테이블
총 119 개의 테이블이 있습니다
열린 테이블이 249 개 있습니다..
table_cache 값은 괜찮은 것 같습니다

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

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

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

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

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

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

$ sudo systemctl 재시작 mysql

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

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

결론

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

최상의 성능을 위해,

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

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

다음 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 방문

관련 사용법 기사

  • CentOS 7 서버에서 MySQL 8.0의 성능 조정 및 최적화 방법
    전문가
  • Ubuntu 18.04 VPS 또는 전용 서버에서 MySQL 데이터베이스를 백업하는 방법
    중급
  • Ubuntu 18.04 Linux VPS에서 MYSQL 8.0을 설치하고 데이터베이스를 만드는 방법
    중급
  • MySQL 5를 비활성화하는 방법 "엄격한 모드" Ubuntu 18.04 VPS 또는 전용 서버
    중급
  • Ubuntu 18.04 VPS 또는 전용 서버에 PhpMyAdmin을 설치하는 방법
    중급
Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me