Mytop을 사용하여 MySQL 성능을 모니터링하는 방법
소개
Mytop은 MySQL 성능을 모니터링하는 데 사용되는 오픈 소스 명령줄 도구입니다. top이라는 Linux 시스템 모니터링 도구에서 영감을 얻었으며 모양과 느낌이 비슷합니다. Mytop은 MySQL 서버에 연결하고 주기적으로 show processlist 및 show global status 명령을 실행합니다. 그런 다음 정보를 유용한 형식으로 요약합니다. mytop을 사용하면 MySQL 스레드, 쿼리 및 가동 시간을 (실시간으로) 모니터링할 수 있을 뿐만 아니라 어떤 사용자가 어떤 데이터베이스에서 쿼리를 실행하고 있는지, 어떤 것이 느린 쿼리인지 등을 확인할 수 있습니다. 이 모든 정보는 MySQL 서버 성능을 최적화하는 데 사용할 수 있습니다.
이 자습서에서는 mytop을 설치, 구성 및 사용하는 방법에 대해 설명합니다.
전제 조건
이 자습서를 시작하기 전에 다음이 있어야 합니다.
- CentOS 7 64비트 Droplet(CentOS 6에서도 작동)
- sudo 권한이 있는 루트가 아닌 사용자. 이 유형의 사용자를 설정하려면 CentOS 7 자습서로 초기 서버 설정을 따르십시오. 모든 명령은 이 사용자로 실행됩니다.
- Droplet에서 실행되는 MySQL 서버. MySQL을 설치하려면 CentOS에 Linux, Apache, MySQL, PHP(LAMP) 스택을 설치하는 방법 문서의 2단계를 따르세요.
1단계 - Mytop 설치
mytop에 필요한 패키지를 설치하겠습니다.
먼저 서버에 EPEL(Extra Packages for Enterprise Linux) yum 리포지토리를 설치해야 합니다. EPEL은 Enterprise Linux용 고품질 오픈 소스 애드온 소프트웨어 패키지 세트를 생성, 유지 및 관리하는 Fedora Special Interest Group입니다. 다음 명령을 실행하여 서버에 EPEL 리포지토리를 설치하고 활성화합니다.
CentOS 7:
- sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
CentOS 6:
- sudo rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
계속하기 전에 다음을 사용하여 EPEL 저장소가 활성화되어 있는지 확인하십시오.
- sudo yum repolist
활성화하면 출력에 다음 리포지토리가 나열됩니다.
epel/x86_64 Extra Packages for Enterprise Linux 7 - x86_64
다음으로 yum 플러그인 protectbase를 사용하여 기본 패키지를 EPEL로부터 보호해 보겠습니다.
- sudo yum install yum-plugin-protectbase.noarch -y
protectbase 플러그인의 목적은 특정 yum 리포지토리를 다른 리포지토리의 업데이트로부터 보호하는 것입니다. 보호되지 않은 저장소에 최신 버전이 있더라도 보호된 저장소의 패키지는 보호되지 않은 저장소의 패키지에 의해 업데이트되거나 재정의되지 않습니다.
이제 mytop 패키지를 설치할 준비가 되었습니다. 다음 명령을 실행하여 설치하십시오.
- sudo yum install mytop -y
이렇게 하면 mytop 패키지와 모든 종속성(주로 perl 모듈)이 설치됩니다.
2단계 - Mytop 구성
mytop을 사용하기 전에 mytop에 대해 .mytop이라는 사용자 지정 구성 파일을 만듭니다. 다음 명령을 실행합니다.
- sudo nano /root/.mytop
파일에 다음 내용을 추가하고 저장하고 종료하십시오.
host=localhost
db=mysql
delay=5
port=3306
socket=
batchmode=0
color=1
idle=1
이 구성 파일은 루트로 mytop을 직접 실행할 때와 루트가 아닌 sudo 사용자로 그 앞에 sudo 명령을 사용하여 실행할 때 사용됩니다.
필요에 따라 이 구성 파일을 변경할 수 있습니다. 예를 들어 delay 옵션은 디스플레이 새로 고침 사이의 시간을 초 단위로 지정합니다. mytop 디스플레이를 3초마다 새로 고치려면 다음을 사용하여 /root/.mytop 파일을 편집할 수 있습니다.
- sudo nano /root/.mytop
다음을 변경합니다.
delay=3
idle 매개변수는 유휴(수면) 스레드가 mytop 디스플레이 화면의 목록에 나타나도록 허용할지 여부를 지정합니다. 기본값은 유휴 스레드를 표시하는 것입니다. 유휴 스레드가 생략되면 가장 오래 실행되는 쿼리가 목록의 맨 위에 표시되도록 기본 정렬 순서가 반대로 됩니다. 이렇게 하려면 /root/.mytop 파일을 편집하고 다음을 변경하십시오.
idle=0
구성 파일의 모든 매개변수에 대한 정보는 mytop의 매뉴얼 페이지를 참조할 수 있습니다. 여기에는 각 매개변수에 대한 설명이 포함되어 있습니다. 매뉴얼 페이지에 액세스하려면 다음 명령을 사용하십시오.
- man mytop
q를 입력하여 설명서를 종료할 수 있습니다.
3단계 - Mytop에 연결
이 섹션에서는 mytop에 연결하고 이를 사용하여 MySQL 쿼리를 보는 방법에 대해 설명합니다.
Mytop은 데이터베이스에 액세스하려면 자격 증명이 필요합니다. 자격 증명은 명령줄에서 프롬프트를 통해 제공하거나 구성 파일에 저장할 수 있습니다. 더 나은 보안을 위해 매번 암호를 묻는 mytop에 --prompt 옵션을 사용합니다.
- sudo mytop --prompt
프롬프트에 MySQL 루트 암호를 입력합니다. mytop 명령과 함께 여러 명령줄 인수를 사용할 수도 있습니다. 전체 목록은 매뉴얼 페이지를 참조하십시오. 예를 들어 sammy와 같은 다른 mysql 사용자를 사용하여 mytop에 연결하려면 다음 명령을 실행합니다.
- sudo mytop -u sammy --prompt
특정 데이터베이스만 연결하고 모니터링하려면 다음 명령을 사용할 수 있습니다.
- sudo mytop -d databasename --prompt
mytop을 종료하고 쉘 프롬프트로 돌아가려면 q를 입력하십시오.
4단계 - Mytop 디스플레이 보기 및 해석
이 섹션에서는 mytop 디스플레이를 해석하는 방법과 도구에서 제공하는 다양한 기능을 살펴봅니다.
mytop --prompt를 사용하여 mytop에 연결하면 스레드 보기로 이동합니다. 다음과 유사한 내용이 표시됩니다.
Output of mytopMySQL on localhost (5.5.41-MariaDB) up 0+00:05:52 [01:33:15]
Queries: 148 qps: 0 Slow: 0.0 Se/In/Up/De(%): 09/00/00/00
qps now: 2 Slow qps: 0.0 Threads: 6 ( 5/ 0) 67/00/00/00
Key Efficiency: 2.0% Bps in/out: 14.7/320.7k Now in/out: 192.5/731.8k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
2 root localhost mysql 0 Query show full processlist
16 root localhost 0 Sleep
17 root localhost testdb 0 Query SELECT * FROM dept_emp
18 root localhost testdb 0 Query SELECT * FROM dept_emp
19 root localhost testdb 0 Query SELECT * FROM dept_emp
20 root localhost testdb 0 Query SELECT * FROM dept_emp
다른 보기에 있는 경우 t를 입력하여 이 보기로 돌아갈 수 있습니다.
위의 디스플레이 화면은 두 부분으로 나뉩니다. 맨 위 4개 라인은 SHIFT-H를 눌러 켜거나 끌 수 있는 헤더로 구성됩니다. 헤더에는 MySQL 서버에 대한 요약 정보가 포함되어 있습니다.
- 첫 번째 줄은 서버의 호스트 이름과 실행 중인 MySQL 버전을 식별합니다. 오른쪽에는 MySQL 서버 프로세스의 가동 시간이 일+시:분:초 형식과 현재 시간으로 표시됩니다.\n
- 두 번째 줄에는 서버가 처리한 총 쿼리 수(이 경우 148개), 초당 평균 쿼리 수, 느린 쿼리 수 및 선택, 삽입, 업데이트 및 삭제 쿼리의 비율이 표시됩니다.\n
- 세 번째 줄은 마지막 mytop 새로 고침 이후의 실시간 값을 보여줍니다. mytop의 일반적인 새로 고침(지연) 시간은 5초이므로 새로 고침 이후 마지막 5초 동안 100개의 쿼리가 실행된 경우
현재 qps숫자는 20이 됩니다. 첫 번째 필드는 숫자입니다. 초당 쿼리 수(현재 qps: 2). 두 번째 값은 초당 느린 쿼리 수입니다.Threads: 6 ( 5/ 0)세그먼트는 총 6개의 연결된 스레드가 있고 5개는 활성 상태이고(하나는 휴면 상태) 스레드 캐시에 0개의 스레드가 있음을 나타냅니다. 세 번째 줄의 마지막 필드는 이전 줄과 같지만 마지막 mytop 새로 고침 이후의 쿼리 비율을 보여줍니다.\n - 네 번째 줄은 키 버퍼 효율성(디스크가 아닌 버퍼에서 키를 읽는 빈도)과 전체 및 마지막 mytop 주기에서 MySQL이 보내고 받은 바이트 수를 표시합니다.
키 효율성: 2.0%는 2%의 키가 디스크가 아닌 버퍼에서 읽혀짐을 나타냅니다.Bps 인/아웃: 14.7/320.7k는 시작 이후 MySQL이 인바운드 트래픽의 평균 14.7kbps, 아웃바운드 트래픽의 평균 320.7kbps를 보여줍니다.Now in/out은 트래픽을 다시 표시하지만 마지막 mytop 새로 고침 이후입니다.\n
디스플레이의 두 번째 부분에는 유휴 시간에 따라 정렬된 현재 MySQL 스레드가 나열됩니다(최소 유휴 우선). 필요한 경우 O를 눌러 정렬 순서를 반대로 할 수 있습니다. 스레드 ID, 사용자 이름, 사용자가 연결된 호스트, 사용자가 연결된 데이터베이스, 유휴 시간(초), 스레드가 실행 중인 명령(또는 스레드 상태) 및 쿼리의 첫 번째 부분 모든 정보가 여기에 표시됩니다. 스레드가 쿼리 상태(즉, Cmd가 쿼리를 표시함)에 있으면 다음 열 쿼리 또는 상태에 실행 중인 쿼리의 첫 번째 부분이 표시됩니다. 명령 상태가 Sleep 또는 Idle인 경우 Query 또는 State 열은 일반적으로 비어 있습니다. 위의 예제 출력에서 ID가 2인 스레드는 실제로 정보를 수집하기 위해 show processlist 쿼리를 실행하는 mytop입니다. ID가 16인 스레드가 잠자기 상태입니다(쿼리를 처리하지 않지만 여전히 연결되어 있음). ID가 17인 스레드가 testdb 데이터베이스에서 SELECT 쿼리를 실행 중입니다.
이제 mytop의 기본 표시를 이해했으므로 이를 사용하여 MySQL 스레드 및 쿼리에 대한 자세한 정보를 수집하는 방법을 살펴보겠습니다. 다음 mytop 디스플레이를 살펴보겠습니다.
[secondary_output Output of mytop]
MySQL on localhost (5.5.41-MariaDB) up 0+00:13:10 [23:54:45]
Queries: 2.8k qps: 4 Slow: 51.0 Se/In/Up/De(%): 45/00/00/00
qps now: 17 Slow qps: 0.0 Threads: 52 ( 51/ 0) 96/00/00/00
Key Efficiency: 100.0% Bps in/out: 215.4/ 7.6M Now in/out: 2.0k/16.2M
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
34 root localhost testdb 0 Query show full processlist
1241 root localhost 1 Sleep
1242 root localhost testdb 1 Query SELECT * FROM dept_emp
1243 root localhost testdb 1 Query SELECT * FROM dept_emp
1244 root localhost testdb 1 Query SELECT * FROM dept_emp
1245 root localhost testdb 1 Query SELECT * FROM dept_emp
1246 root localhost testdb 1 Query SELECT * FROM dept_emp
1247 root localhost testdb 1 Query SELECT * FROM dept_emp
위에 표시된 mytop 스레드 보기(기본 보기)에서 쿼리가 잘립니다. 전체 쿼리를 보려면 F를 누르면 다음과 같이 묻습니다.
Full query for which thread id:
보려는 쿼리의 스레드 ID를 입력합니다. 예를 들어 1244를 입력합니다. 그러면 다음과 같이 표시됩니다.
Thread 1244 was executing following query:
SELECT * FROM dept_emp WHERE ...
-- paused. press any key to resume or (e) to explain --
쿼리를 설명하기 위해 e를 입력할 수 있습니다. 쿼리가 최적화되었는지 확인할 수 있도록 실행 중인 쿼리에 대해 설명합니다. EXPLAIN은 까다로운 MySQL 쿼리를 이해하고 최적화하기 위한 가장 강력한 도구 중 하나입니다. 예를 들어:
EXPLAIN SELECT * FROM dept_emp:
*** row 1 ***
table: dept_emp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 332289
Extra: NULL
-- paused. press any key to resume --
아무 키나 눌러 이 모드를 종료하거나 t를 입력하여 기본 스레드 보기로 돌아갈 수 있습니다.
mytop에서 사용할 수 있는 또 다른 유용한 보기는 명령 보기입니다. 명령 보기에 액세스하려면 c를 입력합니다. 다음과 유사하게 표시됩니다.
Command Total Pct | Last Pct
------- ----- --- | ---- ---
select 1782 55% | 100 8%
show status 723 22% | 533 45%
show processlist 708 22% | 532 45%
change db 2 0% | 0 0%
show variables 1 0% | 0 0%
Compression 0 0% | 0 0%
명령 열은 실행 중인 명령 또는 쿼리의 유형을 보여줍니다. Total 열은 서버가 시작된 이후 실행된 해당 명령 유형의 총 수를 나타내며 Pct 열은 백분율로 동일하게 표시됩니다. 수직선의 다른 쪽에는 mytop을 마지막으로 새로 고친 이후 실행된 해당 유형의 명령 수를 알려주는 마지막 열이 있습니다. 이 정보는 MySQL 서버가 단기 및 장기적으로 수행하는 작업에 대한 통찰력을 제공합니다.
이 튜토리얼에서 mytop의 중요하고 유용한 기능 중 일부에 대해 논의했습니다. 다른 많은 것들이 있습니다. 전체 옵션 목록을 보려면 ? mytop이 실행되는 동안 키를 누릅니다.
결론
이제 mytop을 사용하여 MySQL 서버를 모니터링하는 방법을 잘 이해하셨을 것입니다. 또한 문제가 있는 SQL 쿼리를 찾아 최적화하여 서버의 전반적인 성능을 높이는 출발점이기도 합니다. 이 자습서에서 서버의 MySQL 쿼리 및 테이블을 최적화하는 방법에 대한 자세한 정보를 얻을 수 있습니다.