MySQL에서 mysqlslap을 활용하여 쿼리 성능을 측정하는 방법 (4) - 사례 및 결론

사용 사례: 실제 벤치마킹 시나리오와 Live 쿼리 캡처링.

지금까지 우리의 예에서, 우리는 원래 직원 데이터베이스에 대해 쿼리를 실행하고있다. 즉, 당신이 원하는하지 않을 확실히 뭔가 DBA가 있습니다. 그리고 그것을위한 좋은 이유가있다. 당신은 당신의 프로덕션 데이터베이스를로드 추가하지 않고 삭제, 업데이트 또는 생산 테이블에 데이터를 삽입 할 수있는 테스트 쿼리를 실행하지 않습니다.

우리는 어떻게 프로덕션 데이터베이스의 백업을 만드는 방법을 보여 그것은 테스트 환경에 복사합니다. 이 예에서는 동일한 서버에,하지만 당신은 이상적으로는 동일한 하드웨어 용량을 별도의 서버에 복사합니다.

더 중요한 것은, 우리는 어떻게 쿼리가 프로덕션 데이터베이스에서 살고 기록하고 테스트 스크립트에 추가하는 방법을 보여 드리겠습니다. 즉, 프로덕션 데이터베이스에서 쿼리를 얻을 수 있지만, 테스트 데이터베이스에 대한 테스트를 실행하는 것입니다.

다음과 같은 일반적인 단계는, 당신은 어떤 mysqlslap 테스트에 사용할 수 있습니다 :

1. 테스트 환경을 프로덕션 데이터베이스를 복사합니다.

2. 구성 MySQL은 기록하고 프로덕션 데이터베이스의 모든 연결 요청 및 쿼리를 촬영합니다.

3. 테스트하려고하는 유스 케이스를 시뮬레이션합니다. 당신이 쇼핑 카트를 실행하는 경우 예를 들어, 당신은 당신의 응용 프로그램에서 모든 적절한 데이터베이스 쿼리를 실행하기 위해 뭔가를 구매해야합니다.

4. 쿼리 로깅을 끕니다.

5. 쿼리 로그를 확인하고 테스트 할 쿼리의 목록을 확인합니다.

6. 테스트 할 각 쿼리에 대한 테스트 파일을 만듭니다.

7. 테스트를 실행합니다.

8. 데이터베이스 성능을 향상시키기 위해 출력을 사용한다.

시작하기 위해,이 직원 데이터베이스의 백업을 만들 수 있습니다. 우리는 백업을위한 별도의 디렉토리를 생성합니다 :

sudo mkdir /mysqlslap_tutorial/mysqlbackup

cd /mysqlslap_tutorial/mysqlbackup

백업을 생성하고 새 디렉토리로 이동 :

sudo mysqldump --user sysadmin --password --host localhost employees > ~/employees_backup.sql

sudo cp ~/employees_backup.sql /mysqlslap_tutorial/mysqlbackup/

MySQL의 테스트 서버로 이동합니다.employees_backup 데이터베이스를 만듭니다 :

CREATE DATABASE employees_backup;

당신이 테스트를 위해 별도의 서버를 사용하는 경우이 시점에서, 당신은 employeesbackup.sql 그것에를 통해 파일을 복사해야합니다. 메인 터미널 세션에서 employeesbackup 데이터베이스로 백업 데이터를 가져옵니다

sudo mysql -u sysadmin -p employees_backup < /mysqlslap_tutorial/mysqlbackup/employees_backup.sql

생산 MySQL 데이터베이스 서버, MySQL은 일반 쿼리 로그를 활성화하고 그것의 파일 이름을 제공합니다.일반 쿼리 로그는 MySQL 데이터베이스 인스턴스에 대한 연결, 분리 및 쿼리 활동을 캡처합니다.

SET GLOBAL general_log=1, general_log_file='capture_queries.log';

이제 생산 MySQL 서버에 테스트 할 쿼리를 실행합니다. 이 예에서는 명령 줄에서 쿼리를 실행합니다. 그러나, 당신은 당신의 응용 프로그램 대신 직접 실행 한 쿼리를 생성 할 수 있습니다. 당신이 테스트하려는 느린 과정 또는 웹 사이트 페이지가있는 경우에, 당신은 그 과정을 통해 실행하거나 지금 웹 페이지에 액세스해야합니다. 당신이 쇼핑 카트를 실행하는 경우 예를 들어, 데이터베이스 서버의 모든 해당 쿼리를 실행해야하는, 지금 체크 아웃 프로세스를 완료 할 수 있습니다.

이것은 우리가 생산 MySQL 서버에서 실행되는 쿼리입니다. 먼저 오른쪽 데이터베이스를 사용 :

USE employees;

SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date;

489903 rows in set (4.33 sec)

쿼리가 완료 될 때 우리는 일반적으로 로깅을 해제합니다 :

SET GLOBAL general_log=0;

당신이 로그인두면, 쿼리가 열심히 테스트 만들 수있는 로그에 추가 할 계속합니다. 그래서, 바로 시험을 마친 후에는 로그를 비활성화해야합니다. 의 로그 파일은 / var / lib 디렉토리 / mysql을 디렉토리에 생성 된 것을 확인하자 :

sudo ls -l /var/lib/mysql/capt*

-rw-rw----. 1 mysql mysql 861 Sep 24 15:09 /var/lib/mysql/capture_queries.log

우리의 MySQL의 test 디렉토리에이 파일을 복사 할 수 있습니다. 당신이 테스트를 위해 별도의 서버를 사용하는 경우, 해당 서버에 복사합니다.

sudo cp /var/lib/mysql/capture_queries.log /mysqlslap_tutorial/

이 로그 파일에있는 데이터의 꽤가 있어야합니다. 이 예제에서, 우리가 원하는 쿼리는 끝 부분에 있어야합니다.파일의 마지막 부분을 확인 :

sudo tail /mysqlslap_tutorial/capture_queries.log

         6294 Query show databases
         6294 Query show tables
         6294 Field List    departments 
         6294 Field List    dept_emp 
         6294 Field List    dept_manager 
         6294 Field List    employees 
         6294 Field List    salaries 
         6294 Field List    titles 
140930 15:34:52  6294 Query SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date
140930 15:35:06  6294 Query SET GLOBAL general_log=0

이 로그는 SQL 명령과 타임 스탬프를 보여줍니다.파일의 끝 부분에 SQL SELECT 문은 우리가 관심이있는 것입니다. 그것은 우리가 프로덕션 데이터베이스에서 실행 된 명령과 정확하게 일치해야합니다, 우리는 그것을 캡처 한 곳이기 때문이다.

이 예제에서, 우리는 이미 쿼리를 알고 있었다. 그러나 프로덕션 환경에서는이 방법을 사용하면 반드시 서버에서 실행되고 있는지에 대해 알고하지 않을 수 있습니다 쿼리를 찾는 데 매우 유용 할 수 있습니다.

당신이 실행 또는 로그인 할 때 다른 쿼리를 발생하는 경우,이 파일이 완전히 다른 모양 있습니다.실제 시나리오에서이 파일은 모든 다른 연결에서 오는 항목의 수백이 쇄도 할 수있다. 당신의 목표는 병목 현상을 일으키는 쿼리 또는 쿼리를 찾을 수 있습니다. 당신은 텍스트 쿼리를 포함 모든 라인의 목록을 만들어 시작할 수 있습니다. 그런 다음 쿼리를 테스트하는 동안 데이터베이스에서 실행 된 정확히의 목록을해야합니다.

테스트 할 각 쿼리를 들어,는 .sql 확장자로 끝나는 파일에 복사합니다.

sudo vi /mysqlslap_tutorial/capture_queries.sql

내용은 줄 바꿈없이 끝에 세미콜론없이 테스트 할 MySQL의 쿼리해야합니다 :

SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date

다음, 쿼리 결과가 캐시되지 않습니다 있는지 확인하십시오. 다시 테스트 MySQL의 세션으로 이동합니다. 다음 명령을 실행합니다 :

RESET QUERY CACHE;

이제 스크립트 파일로 mysqlslap 유틸리티를 실행하는 시간이다. 당신이 --query 매개 변수에 올바른 스크립트 파일 이름을 사용하십시오. 우리는 단지 10 개의 동시 연결을 사용하여 테스트를 두 번 반복합니다. 테스트 서버에서이 프로그램을 실행 :

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose

Benchmark
        Average number of seconds to run all queries: 68.692 seconds
        Minimum number of seconds to run all queries: 59.301 seconds
        Maximum number of seconds to run all queries: 78.084 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

그렇다면 우리는이 벤치 마크를 향상시킬 수 있습니까?

당신은 쿼리가 무엇을하고 있는지 평가하기 위해 MySQL의 쿼리 친숙의 일정 금액을해야합니다.

다시 쿼리에서 보면, 우리는 여러 테이블에서의 조인이 수를하고있어 볼 수 있습니다. 쿼리는 직원의 작업 기록을 보여주고 그렇게함으로써, 그것은 EMPNO 필드가 다른 테이블을 조인합니다. 또한 접합 용 DEPTNO 필드를 사용하고, 그러나 약간 부서 레코드가 있기 때문에, 우리는 이것을 무시할 것이다.데이터베이스 많은 empno에 엔트리가 존재하기 때문에,이 EMPNO 필드 인덱스를 작성하는 쿼리를 개선 할 수 있다고 가정하는 것이 논리적이다.

당신이 서버를 (즉 mysqlslap 함께 도움이 부분입니다!) 과세되는 쿼리를 찾았하면 약간의 연습으로, 당신은 MySQL의 지식과 데이터베이스를 기반으로 쿼리에 대한 평가를 할 수있을 것입니다.

다음으로, 당신은 당신의 데이터베이스 또는에서 실행되고있는 쿼리를 개선하기 위해 시도 할 수 있습니다.

우리의 경우, 이제 우리는 위에서 언급 한 인덱스를 추가 할 수 있습니다. 우리는 EMPNO에 세 개의 인덱스를 생성합니다. 하나의 인덱스가 직원 테이블의 EMPNO 필드에 생성되며 다른 인덱스는 deptemp 테이블의 EMPNO 필드에 생성되며, 마지막 하나는 제목 테이블의 EMP_NO 필드에 생성됩니다.

의는 우리의 테스트 MySQL의 세션으로 이동하여 다음 명령을 실행하자

USE employees_backup;

CREATE INDEX employees_empno ON employees(emp_no);

CREATE INDEX dept_emp_empno ON dept_emp(emp_no);

CREATE INDEX titles_empno ON titles(emp_no);

우리는 동일한 매개 변수 mysqlslap을 실행할 경우 다시 테스트 서버에 우리의 주요 터미널 창에오고, 우리는 벤치 마크의 차이를 볼 수 있습니다 :

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose

Benchmark
        Average number of seconds to run all queries: 55.869 seconds
        Minimum number of seconds to run all queries: 55.706 seconds
        Maximum number of seconds to run all queries: 56.033 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1




앞서서 mysqlslpa을 이용하는 방법, 쿼리 성능을 측정하는 방법 및 실제 사용 사례도 설명하였습니다.

결론입니다.

Troubleshooting - mysqlslap Doesn't Show Output

당신이 테스트 명령을 실행하고 출력을 얻을 수없는 경우,이 서버 자원이 끝나가 될 수있는 좋은 지표이다.

증상은 벤치 마크의 출력 부족, 또는 에러 등을 포함 할 수있다 mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query.

당신은 --concurrency 또는 --iterations 매개 변수에 적은 수 테스트를 다시 시도 할 수 있습니다. 또는, 당신은 당신의 테스트 서버 환경을 업그레이드 시도 할 수 있습니다.

이 데이터베이스 서버의 용량의 외부 한계를 찾을 수있는 좋은 방법이 될 수 있습니다.

결론적으로,

mysqlslap 사용하기 쉽고 간단하고 가벼운 도구이며 그것은 MySQL 데이터베이스 엔진이 기본적으로 통합되어 있습니다. 이 버전 5.1.4에서의 MySQL의 모든 버전에 사용할 수 있습니다.

이 튜토리얼에서 우리는 다양한 옵션 mysqlslap 사용하는 방법을 보았다 및 샘플 데이터베이스와 주위했다. 당신도 그와 MySQL의 사이트와 연습에서 다른 샘플 데이터베이스를 다운로드 할 수 있습니다. 우리가 전에 언급 한 바와 같이, 프로덕션 데이터베이스 서버에서 테스트를 실행하지 마십시오.

이 튜토리얼의 마지막 유스 케이스는 하나의 쿼리를하고있었습니다. 우리가 다소 세 테이블에 추가 인덱스를 추가하여 해당 쿼리의 성능을 개선하는 동안, 과정은 현실에서 그렇게 단순하지 않을 수 있습니다. 추가 여분 인덱스는 때때로 시스템 성능을 저하시킬 수 있으며, DBA는 종종 발생할 수있는 성능 저하에 대해 여분의 인덱스를 추가의 이점을 계량 할 필요가있다.

실제 테스트 시나리오는 더 복잡하지만 이것은 당신에게 테스트 및 데이터베이스 성능을 향상 시작하는 도구를 제공합니다.

BY LEE JI EUN