웹사이트 검색

mysqlslap으로 MySQL 쿼리 성능을 측정하는 방법

소개

MySQL에는 버전 5.1.4부터 사용된 mysqlslap이라는 편리한 작은 진단 도구가 함께 제공됩니다. DBA와 개발자가 데이터베이스 서버를 로드 테스트하는 데 도움이 되는 벤치마킹 도구입니다.

mysqlslap은 동시에 데이터베이스 서버에 도달하는 많은 수의 클라이언트 연결을 에뮬레이트할 수 있습니다. 부하 테스트 매개변수는 완전히 구성 가능하며 다양한 테스트 실행의 결과를 사용하여 데이터베이스 설계 또는 하드웨어 리소스를 미세 조정할 수 있습니다.

이 튜토리얼에서는 mysqlslap을 사용하여 몇 가지 기본 쿼리로 MySQL 데이터베이스를 로드 테스트하는 방법을 배우고 벤치마킹이 이러한 쿼리를 미세 조정하는 데 어떻게 도움이 되는지 알아봅니다. 몇 가지 기본 데모 후에는 테스트를 위해 기존 데이터베이스의 복사본을 만들고 로그에서 쿼리를 수집하고 스크립트에서 테스트를 실행하는 상당히 현실적인 테스트 시나리오를 실행합니다.

이 자습서에 표시된 명령, 패키지 및 파일은 CentOS 7에서 테스트되었습니다. 개념은 다른 배포판에서도 동일하게 유지됩니다.

어떤 크기의 서버를 사용해야 합니까?

특정 데이터베이스 서버를 벤치마킹하는 데 관심이 있다면 동일한 사양과 설치된 데이터베이스의 정확한 복사본으로 서버에서 테스트해야 합니다.

학습 목적으로 이 자습서를 실행하고 포함된 모든 명령을 실행하려면 최소 2GB Droplet을 권장합니다. 이 자습서의 명령은 서버에 부담을 주기 위한 것이므로 더 작은 서버에서는 시간이 초과될 수 있습니다.

이 자습서의 샘플 출력은 교육용 예제를 최적화하기 위해 다양한 방법으로 생성되었습니다.

1단계 - 테스트 시스템에 MySQL 커뮤니티 서버 설치

테스트 데이터베이스에 MySQL 커뮤니티 서버의 새 복사본을 설치하는 것으로 시작하겠습니다. 프로덕션 데이터베이스 서버에서 이 자습서의 명령이나 쿼리를 실행하면 안 됩니다.

이러한 테스트는 테스트 서버에 스트레스를 주기 위한 것이며 프로덕션 서버에서 지연 또는 다운타임을 유발할 수 있습니다. 이 튜토리얼은 다음 환경에서 테스트되었습니다.

  • 센트OS 7
  • sudo 사용자가 실행하는 명령
  • 2GB Droplet 권장; 이 튜토리얼에 표시된 벤치마크 결과는 교육 목적으로 생성되었으며 특정 DigitalOcean 벤치마크를 반영하지 않습니다.

먼저 이 튜토리얼과 관련된 모든 파일을 보관할 디렉토리를 생성합니다. 이것은 물건을 깔끔하게 유지하는 데 도움이 될 것입니다. 다음 디렉토리로 이동합니다.

sudo mkdir /mysqlslap_tutorial
cd /mysqlslap_tutorial

다음으로 MySQL Community Release yum 리포지토리를 다운로드합니다. 다운로드하는 리포지토리는 CentOS 7에서 작동하는 Red Hat Enterprise Linux 7용입니다.

sudo wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

다음으로 rpm -Uvh 명령을 실행하여 리포지토리를 설치할 수 있습니다.

sudo rpm -Uvh mysql-community-release-el7-5.noarch.rpm

/etc/yum.repos.d 폴더의 내용을 보고 리포지토리가 설치되었는지 확인합니다.

sudo ls -l /etc/yum.repos.d

출력은 다음과 같아야 합니다.

-rw-r--r--. 1 root root 1612 Jul  4 21:00 CentOS-Base.repo
-rw-r--r--. 1 root root  640 Jul  4 21:00 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 1331 Jul  4 21:00 CentOS-Sources.repo
-rw-r--r--. 1 root root  156 Jul  4 21:00 CentOS-Vault.repo
-rw-r--r--. 1 root root 1209 Jan 29  2014 mysql-community.repo
-rw-r--r--. 1 root root 1060 Jan 29  2014 mysql-community-source.repo

설치를 위해 올바른 MySQL 릴리스가 활성화되어 있는지도 확인할 수 있습니다.

sudo yum repolist enabled | grep mysql

우리의 경우에는 MySQL 5.6 Community Server가 우리가 원하는 것입니다.

mysql-connectors-community/x86_64       MySQL Connectors Community           10
mysql-tools-community/x86_64            MySQL Tools Community                 6
mysql56-community/x86_64                MySQL 5.6 Community Server           64

MySQL 커뮤니티 서버를 설치합니다.

sudo yum install mysql-community-server

프로세스가 완료되면 설치된 구성 요소를 확인하십시오.

sudo yum list installed | grep mysql

목록은 다음과 같아야 합니다.

mysql-community-client.x86_64      5.6.20-4.el7      @mysql56-community
mysql-community-common.x86_64      5.6.20-4.el7      @mysql56-community
mysql-community-libs.x86_64        5.6.20-4.el7      @mysql56-community
mysql-community-release.noarch     el7-5             installed
mysql-community-server.x86_64      5.6.20-4.el7      @mysql56-community

다음으로 MySQL 데몬이 실행 중이고 서버가 부팅될 때 자동으로 시작되는지 확인해야 합니다. mysqld 데몬의 상태를 확인하십시오.

sudo systemctl status mysqld.service

중지된 경우 다음 출력이 표시됩니다.

mysqld.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled)
   Active: inactive (dead)

서비스 시작:

sudo systemctl start mysqld.service

부팅 시 자동 시작되도록 구성되어 있는지 확인하십시오.

sudo systemctl enable mysqld.service

마지막으로 MySQL을 보호해야 합니다.

sudo mysql_secure_installation

그러면 일련의 프롬프트가 나타납니다. 빨간색으로 입력해야 하는 답변과 함께 아래 프롬프트가 표시됩니다. 처음에는 MySQL 루트 사용자에 대한 암호가 없으므로 Enter를 누르십시오.

프롬프트에서 직접 선택해야 하는 새로운 보안 루트 암호를 제공해야 합니다. 익명 데이터베이스 사용자 계정을 제거하고, 원격 루트 로그인을 비활성화하고, 권한 테이블을 다시 로드하는 등의 작업을 수행하려면 y로 대답해야 합니다.

...
Enter current password for root (enter for none):
OK, successfully used password, moving on...
...
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
...
Remove anonymous users? [Y/n] y
 ... Success!
...
Disallow root login remotely? [Y/n] y
 ... Success!
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
...
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...

이제 데이터베이스에 연결하고 모든 것이 작동하는지 확인할 수 있습니다.

sudo mysql -h localhost -u root -p

프롬프트에서 방금 설정한 루트 MySQL 암호를 입력합니다. 다음과 같은 출력이 표시되어야 합니다.

Enter password:
Welcome to the MySQL monitor....

mysql>

mysql> 프롬프트에서 다음 명령을 입력하여 모든 데이터베이스를 봅니다.

show databases;

다음과 같은 출력이 표시되어야 합니다.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

마지막으로 sysadmin이라는 사용자 계정을 생성하겠습니다. 이 계정은 루트 사용자 대신 MySQL에 로그인하는 데 사용됩니다. mypassword를 이 사용자의 비밀번호로 바꾸십시오. 또한 이 계정에 모든 권한을 부여합니다. MySQL 프롬프트에서 다음 명령을 입력합니다.

create user sysadmin identified by 'mypassword';

산출:

Query OK, 0 rows affected (0.00 sec)

권한 부여:

grant all on *.* to sysadmin;

산출:

Query OK, 0 rows affected (0.01 sec)

지금은 운영 체제 프롬프트로 돌아가 보겠습니다.

quit;

산출:

Bye

2단계 - 샘플 데이터베이스 설치

다음으로 테스트를 위해 샘플 데이터베이스를 설치해야 합니다. 이 데이터베이스는 직원이라고 하며 Launchpad입니다. 직원 데이터베이스는 Patrick Crews와 Giuseppe Maxia가 개발했습니다. 원본 데이터는 Siemens Corporate Research의 Fusheng Wang과 Carlo Zaniolo가 작성했습니다.

대용량 데이터 세트를 제공하기 때문에 직원 데이터베이스를 선택합니다. 데이터베이스 구조는 충분히 간단합니다. 테이블이 6개뿐입니다. 그러나 여기에 포함된 데이터에는 3,000,000개 이상의 직원 레코드가 있습니다(급여 테이블 자체에는 거의 3백만 행이 있음). 이를 통해 보다 현실적인 프로덕션 워크로드를 에뮬레이션할 수 있습니다.

먼저 /mysqlslap_tutorial 디렉토리에 있는지 확인합니다.

cd /mysqlslap_tutorial

직원 샘플 데이터베이스의 최신 버전을 다운로드합니다.

sudo wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2

아카이브 압축을 풀 수 있도록 bzip2 도구를 설치합니다.

sudo yum install bzip2

데이터베이스 아카이브의 압축을 풉니다. 1분 정도 소요됩니다. 여기서는 두 단계로 수행합니다.

sudo bzip2 -dfv employees_db-full-1.0.6.tar.bz2
sudo tar -xf employees_db-full-1.0.6.tar

콘텐츠는 employees_db라는 별도의 새 디렉토리에 압축 해제됩니다. 데이터베이스를 설치하는 쿼리를 실행하려면 이 디렉터리로 이동해야 합니다. 내용에는 README 문서, 변경 로그, 데이터 덤프 및 데이터베이스 구조를 생성할 다양한 SQL 쿼리 파일이 포함됩니다.

cd employees_db
ls -l

확인해야 할 내용은 다음과 같습니다.

-rw-r--r--. 1 501 games       752 Mar 30  2009 Changelog
-rw-r--r--. 1 501 games      6460 Oct  9  2008 employees_partitioned2.sql
-rw-r--r--. 1 501 games      7624 Feb  6  2009 employees_partitioned3.sql
-rw-r--r--. 1 501 games      5660 Feb  6  2009 employees_partitioned.sql
-rw-r--r--. 1 501 games      3861 Nov 28  2008 employees.sql
-rw-r--r--. 1 501 games       241 Jul 30  2008 load_departments.dump
-rw-r--r--. 1 501 games  13828291 Mar 30  2009 load_dept_emp.dump
-rw-r--r--. 1 501 games      1043 Jul 30  2008 load_dept_manager.dump
-rw-r--r--. 1 501 games  17422825 Jul 30  2008 load_employees.dump
-rw-r--r--. 1 501 games 115848997 Jul 30  2008 load_salaries.dump
-rw-r--r--. 1 501 games  21265449 Jul 30  2008 load_titles.dump
-rw-r--r--. 1 501 games      3889 Mar 30  2009 objects.sql
-rw-r--r--. 1 501 games      2211 Jul 30  2008 README
-rw-r--r--. 1 501 games      4455 Mar 30  2009 test_employees_md5.sql
-rw-r--r--. 1 501 games      4450 Mar 30  2009 test_employees_sha.sql

이 명령을 실행하여 MySQL에 연결하고 데이터베이스를 생성하고 데이터를 로드하는 employees.sql 스크립트를 실행합니다.

sudo mysql -h localhost -u sysadmin -p -t < employees.sql

프롬프트에서 이전 섹션에서 sysadmin MySQL 사용자에 대해 생성한 암호를 입력합니다.

프로세스 출력은 다음과 같습니다. 다음을 실행하는 데 1분 정도 걸립니다.

+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+

이제 MySQL에 로그인하고 일부 기본 쿼리를 실행하여 데이터를 성공적으로 가져왔는지 확인할 수 있습니다.

sudo mysql -h localhost -u sysadmin -p

sysadmin MySQL 사용자의 암호를 입력합니다.

새 employees 데이터베이스에 대한 데이터베이스 목록을 확인합니다.

show databases;

산출:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

직원 데이터베이스 사용:

use employees;

테이블을 확인하십시오.

show tables;

산출:

+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.01 sec)

원하는 경우 이러한 각 테이블의 세부 정보를 확인할 수 있습니다. titles 테이블에 대한 정보만 확인하겠습니다.

describe titles;

산출:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no    | int(11)     | NO   | PRI | NULL    |       |
| title     | varchar(50) | NO   | PRI | NULL    |       |
| from_date | date        | NO   | PRI | NULL    |       |
| to_date   | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

항목 수를 확인하십시오.

mysql> select count(*) from titles;
+----------+
| count(*) |
+----------+
|   443308 |
+----------+
1 row in set (0.14 sec)

원하는 다른 데이터를 확인하십시오. 이제 운영 체제 프롬프트로 돌아갈 수 있습니다.

quit;

3단계 — mysqlslap 사용

이제 mysqlslap을 사용할 수 있습니다. mysqlslap은 일반 쉘 프롬프트에서 호출할 수 있으므로 명시적으로 MySQL에 로그인할 필요가 없습니다. 하지만 이 자습서에서는 Linux 서버에 대한 또 다른 터미널 연결을 열고 이전에 생성한 sysadmin 사용자로 거기에서 새 MySQL 세션을 시작하여 MySQL에서 몇 가지 사항을 더 쉽게 확인하고 업데이트할 수 있습니다. 따라서 sudo 사용자로 프롬프트 하나를 열고 MySQL에 로그인한 프롬프트 하나를 갖게 됩니다.

테스트를 위한 특정 명령을 시작하기 전에 가장 유용한 mysqlslap 옵션 목록을 살펴보는 것이 좋습니다. 이는 나중에 자신만의 mysqlslap 명령을 설계하는 데 도움이 될 수 있습니다.

Option What it means
–user MySQL username to connect to the database server
–password Password for the user account. It’s best to leave it blank in command line
–host MySQL database server name
–port Port number for connecting to MySQL if the default is not used
–concurrency The number of simultaneous client connections mysqlslap will emulate
–iterations The number of times the test query will be run
–create-schema The schema in which to run the tests
–query The query to execute. This can either be a SQL query string or a path to a SQL script file
–create The query to create a table. Again, this can be a query string or a path to a SQL file
–delimiter The delimiter used to separate multiple SQL statements
–engine The MySQL database engine to use (e.g., InnoDB)
–auto-generate-sql Lets MySQL perform load testing with its own auto-generated SQL command

사용 사례: 자동 생성된 SQL 및 데이터를 사용한 벤치마킹

mysqlslap의 auto-generate-sql 기능을 사용하여 시작하겠습니다. 자동 생성 SQL을 사용할 때 mysqlslap은 mysqlslap이라는 별도의 임시 데이터베이스를 생성합니다. 이 데이터베이스에는 하나의 정수와 샘플 데이터로 채워진 하나의 varchar 유형 열이 있는 간단한 테이블이 있습니다. 이는 데이터베이스 서버의 전체 성능을 빠르고 쉽게 확인할 수 있는 방법입니다.

자동 생성 SQL을 한 번 반복하는 단일 클라이언트 연결을 테스트하는 것으로 시작합니다.

sudo mysqlslap --user=sysadmin --password --host=localhost  --auto-generate-sql --verbose

출력은 다음과 같아야 합니다.

Benchmark
        Average number of seconds to run all queries: 0.009 seconds
        Minimum number of seconds to run all queries: 0.009 seconds
        Maximum number of seconds to run all queries: 0.009 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

mysqlslap은 출력에 표시된 대로 몇 가지 벤치마킹 통계를 보고합니다. 쿼리를 실행하는 데 걸린 평균, 최소 및 최대 시간(초)을 보고합니다. 또한 이 부하 테스트에 사용된 클라이언트 연결 수가 1개임을 확인할 수 있습니다.

이제 50개의 동시 연결을 시도하고 자동 생성된 쿼리를 10번 실행합니다.

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=10 --auto-generate-sql --verbose

이 명령이 의미하는 바는 50개의 시뮬레이션된 클라이언트 연결이 각각 동일한 테스트 쿼리를 동시에 발생시키고 이 테스트가 10번 반복된다는 것입니다.

출력은 증가된 부하에 따른 현저한 차이를 보여줍니다.

Benchmark
        Average number of seconds to run all queries: 0.197 seconds
        Minimum number of seconds to run all queries: 0.168 seconds
        Maximum number of seconds to run all queries: 0.399 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

쿼리를 실행하는 클라이언트 수: 필드에 이제 50이라는 값이 표시됩니다. 클라이언트당 평균 쿼리 수는 0입니다.

자동 생성된 SQL은 두 개의 필드가 있는 간단한 테이블을 생성합니다. 대부분의 프로덕션 환경에서 테이블 구조는 그보다 훨씬 큽니다. 테스트 테이블에 추가 필드를 추가하여 이를 에뮬레이트하도록 mysqlslap에 지시할 수 있습니다. 이를 위해 --number-char-cols 및 --number-int-cols라는 두 가지 새로운 매개변수를 사용할 수 있습니다. 이 매개변수는 테스트 테이블에 추가할 열의 varchar 및 int 유형 수를 지정합니다.

다음 예에서는 5개의 숫자 열과 20개의 문자 유형 열이 있는 테이블에 대해 실행되는 자동 생성 SQL 쿼리를 사용하여 테스트하고 있습니다. 또한 50개의 클라이언트 연결을 시뮬레이션하고 테스트를 100번 반복하려고 합니다.

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=100 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql --verbose

이것은 조금 더 오래 걸릴 것입니다. 테스트가 실행되는 동안 MySQL 세션이 실행 중인 다른 터미널 창으로 전환하여 진행 상황을 확인할 수 있습니다. 너무 오래 기다리면 테스트가 완료되고 테스트 데이터베이스를 볼 수 없습니다.

MySQL 프롬프트에서:

show databases;

mysqlslap 데이터베이스에 유의하십시오.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| mysqlslap          |
| performance_schema |
+--------------------+
5 rows in set (0.01 sec)

원하는 경우 테스트 데이터베이스에서 테이블을 확인할 수 있습니다. t1이라고 합니다.

다른 터미널 창을 다시 확인하십시오. 테스트가 완료되면 로드가 증가함에 따라 성능이 훨씬 더 느려지는 것을 확인할 수 있습니다.

Benchmark
        Average number of seconds to run all queries: 0.695 seconds
        Minimum number of seconds to run all queries: 0.627 seconds
        Maximum number of seconds to run all queries: 1.442 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

MySQL 터미널 세션으로 돌아갑니다. mysqlslap이 일회용 데이터베이스를 삭제한 것을 볼 수 있습니다. MySQL 프롬프트에서:

show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

사용 사례: 사용자 정의 쿼리로 벤치마킹

서버의 물리적 리소스를 평가하는 경우 자동 생성 SQL이 좋습니다. 주어진 시스템이 감당할 수 있는 부하 수준을 찾고자 할 때 유용합니다.

그러나 특정 데이터베이스 종속 애플리케이션의 성능 문제를 해결하려는 경우 실제 데이터에서 실제 쿼리를 테스트하고 싶을 것입니다. 이러한 쿼리는 웹 또는 애플리케이션 서버에서 올 수 있습니다.

지금은 테스트하려는 특정 쿼리를 알고 있다고 가정합니다. 다음 섹션에서는 서버에서 실행 중인 쿼리를 찾는 방법을 보여줍니다.

인라인 쿼리부터 시작하겠습니다. –query 옵션을 사용하여 mysqlslap에 인라인 쿼리를 제공할 수 있습니다. SQL 문은 줄 바꿈을 포함할 수 없으며 세미콜론(;)으로 구분해야 합니다. 쿼리도 큰따옴표로 묶어야 합니다.

다음 코드 스니펫에서는 dept_emp 테이블에 대해 간단한 쿼리를 실행하고 있습니다. dept_emp 테이블에는 30만 개가 넘는 레코드가 있습니다. –create-schema 옵션을 사용하여 employees 데이터베이스를 어떻게 지정했는지 확인하십시오.

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=10 --create-schema=employees --query="SELECT * FROM dept_emp;" --verbose

실행하는 데 시간이 걸립니다. 1~2분 후에 다음과 같은 성능 벤치마크를 받아야 합니다.

Benchmark
        Average number of seconds to run all queries: 18.486 seconds
        Minimum number of seconds to run all queries: 15.590 seconds
        Maximum number of seconds to run all queries: 28.381 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1

(참고: 이 쿼리가 10분 이상 중단되거나 출력이 제공되지 않으면 –concurrency 및/또는 –iterations에 대해 더 낮은 숫자로 다시 시도해야 합니다. , 또는 더 큰 서버에서 시도하십시오.)

다음으로 –query 매개변수에서 여러 SQL 문을 사용합니다. 다음 예에서는 각 쿼리를 세미콜론으로 종료합니다. mysqlslap은 우리가 –delimiter 옵션을 지정했기 때문에 여러 별도의 SQL 명령을 사용하고 있음을 알고 있습니다.

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=20 --iterations=10 --create-schema=employees --query="SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" --delimiter=";" --verbose

이 테스트는 동일한 수의 연결과 동일한 반복 횟수를 사용합니다. 그러나 성능은 여러 SELECT 문의 경우 점진적으로 느려졌습니다(평균 23.8초 대 18.486초).

Benchmark
        Average number of seconds to run all queries: 23.800 seconds
        Minimum number of seconds to run all queries: 22.751 seconds
        Maximum number of seconds to run all queries: 26.788 seconds
        Number of clients running queries: 20
        Average number of queries per client: 5

프로덕션 SQL 문은 복잡할 수 있습니다. 테스트를 위해 입력하는 것보다 스크립트에 복잡한 SQL 문을 추가하는 것이 더 쉽습니다. 따라서 스크립트 파일에서 쿼리를 읽도록 mysqlslap에 지시할 수 있습니다.

이를 설명하기 위해 SQL 명령에서 스크립트 파일을 생성해 보겠습니다. 아래 코드 스니펫을 사용하여 이러한 파일을 만들 수 있습니다.

sudo echo "SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" > ~/select_query.sql

sudo cp ~/select_query.sql /mysqlslap_tutorial/

이제 select_query.sql 파일에 5개의 SELECT 문이 모두 포함됩니다.

이 스크립트에는 여러 쿼리가 있으므로 새로운 테스트 개념을 도입할 수 있습니다. mysqlslap은 쿼리를 병렬화할 수 있습니다. 각 테스트 클라이언트가 실행해야 하는 쿼리 수를 지정하여 이를 수행할 수 있습니다. mysqlslap은 –number-of-queries 옵션으로 이를 수행합니다. 따라서 50개의 연결과 1000개의 쿼리를 실행할 경우 각 클라이언트는 각각 약 20개의 쿼리를 실행합니다.

마지막으로 –debug-info 스위치를 사용하여 사용된 컴퓨팅 리소스를 표시할 수도 있습니다.

다음 코드 스니펫에서는 방금 생성한 스크립트 파일을 사용하도록 mysqlslap에 요청합니다. 쿼리 수 매개변수도 지정하고 있습니다. 프로세스가 두 번 반복되며 출력에 디버깅 정보가 필요합니다.

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=20 --number-of-queries=1000 --create-schema=employees --query="/mysqlslap_tutorial/select_query.sql" --delimiter=";" --verbose --iterations=2 --debug-info

이 명령이 완료되면 몇 가지 흥미로운 결과를 볼 수 있습니다.

Benchmark
        Average number of seconds to run all queries: 217.151 seconds
        Minimum number of seconds to run all queries: 213.368 seconds
        Maximum number of seconds to run all queries: 220.934 seconds
        Number of clients running queries: 20
        Average number of queries per client: 50


User time 58.16, System time 18.31
Maximum resident set size 909008, Integral resident set size 0
Non-physical pagefaults 2353672, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 102785, Involuntary context switches 43

여기에서 MySQL 인스턴스의 모든 쿼리를 실행하는 평균 시간(초)은 217초, 거의 4분입니다. 이는 가상 머신에서 사용할 수 있는 RAM 및 CPU 양의 영향을 받았지만 적당한 수의 클라이언트 연결에서 두 번 반복되는 많은 쿼리 때문이기도 했습니다.

많은 수의 비물리적 페이지 폴트가 있음을 알 수 있습니다. 메모리에서 데이터를 찾을 수 없고 시스템이 디스크의 스왑 파일에서 데이터를 가져와야 할 때 페이지 오류가 발생합니다. 출력에는 CPU 관련 정보도 표시됩니다. 이 경우 많은 수의 컨텍스트 스위치가 표시됩니다.

사용 사례: 실용적인 벤치마킹 시나리오 및 라이브 쿼리 캡처

지금까지 예제에서는 원래 직원 데이터베이스에 대해 쿼리를 실행했습니다. 이는 DBA가 원하지 않는 일입니다. 그럴만 한 이유가 있습니다. 프로덕션 데이터베이스에 부하를 추가하고 싶지 않고 프로덕션 테이블에 데이터를 삭제, 업데이트 또는 삽입할 수 있는 테스트 쿼리를 실행하고 싶지 않습니다.

프로덕션 데이터베이스를 백업하고 테스트 환경에 복사하는 방법을 보여드리겠습니다. 이 예에서는 동일한 서버에 있지만 하드웨어 용량이 동일한 별도의 서버에 복사하는 것이 이상적입니다.

더 중요한 것은 프로덕션 데이터베이스에서 쿼리를 라이브로 기록하고 테스트 스크립트에 추가하는 방법을 보여줍니다. 즉, 프로덕션 데이터베이스에서 쿼리를 가져오지만 테스트 데이터베이스에 대해 테스트를 실행합니다.

일반적인 단계는 다음과 같으며 모든 mysqlslap 테스트에 사용할 수 있습니다.

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

시작하려면 직원 데이터베이스의 백업을 생성해 보겠습니다. 백업을 위해 별도의 디렉토리를 생성합니다.

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;

이때 테스트를 위해 별도의 서버를 사용하는 경우 employees_backup.sql 파일을 해당 서버에 복사해야 합니다. 기본 터미널 세션에서 백업 데이터를 employees_backup 데이터베이스로 가져옵니다.

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 테스트 디렉터리에 복사해 보겠습니다. 테스트를 위해 별도의 서버를 사용하는 경우 해당 서버에 복사합니다.

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 문이 우리가 관심을 갖는 부분입니다. 생산 데이터베이스에서 실행한 명령과 정확히 동일해야 합니다. 그곳에서 캡처했기 때문입니다.

이 예에서 우리는 이미 쿼리를 알고 있었습니다. 그러나 프로덕션 환경에서 이 방법은 서버에서 실행 중인 쿼리에 대해 반드시 알 필요는 없는 쿼리를 찾는 데 매우 유용할 수 있습니다.

로깅하는 동안 다른 쿼리를 실행하거나 트리거한 경우 이 파일은 완전히 다르게 보입니다. 실제 시나리오에서 이 파일은 서로 다른 모든 연결에서 오는 수백 개의 항목으로 넘쳐날 수 있습니다. 목표는 병목 현상을 일으키는 쿼리를 찾는 것입니다. Query 텍스트를 포함하는 모든 줄의 목록을 만드는 것으로 시작할 수 있습니다. 그러면 테스트 중에 데이터베이스에서 정확히 어떤 쿼리가 실행되었는지 목록이 표시됩니다.

테스트하려는 각 쿼리에 대해 .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 쿼리에 어느 정도 익숙해야 합니다.

쿼리를 다시 살펴보면 여러 테이블에서 여러 조인을 수행하고 있음을 알 수 있습니다. 쿼리는 직원 작업 기록을 표시하고 그렇게 함으로써 emp_no 필드로 다른 테이블을 조인합니다. 조인을 위해 dept_no 필드도 사용하고 있지만 부서 레코드가 적기 때문에 무시하겠습니다. 데이터베이스에 많은 emp_no 항목이 있으므로 emp_no 필드에 인덱스를 생성하면 쿼리가 개선될 수 있다고 가정하는 것이 논리적입니다.

약간의 연습을 통해 서버에 부담을 주는 쿼리를 찾으면(mysqlslap이 도움이 되는 부분입니다!) MySQL 및 데이터베이스에 대한 지식을 기반으로 쿼리에 대한 평가를 할 수 있습니다.

다음으로 데이터베이스 또는 데이터베이스에서 실행 중인 쿼리를 개선할 수 있습니다.

우리의 경우 위에서 언급한 인덱스를 추가해 보겠습니다. emp_no에 3개의 인덱스를 생성합니다. employees 테이블의 emp_no 필드에 하나의 인덱스가 생성되고 dept_emp의 emp_no 필드에 다른 인덱스가 생성됩니다. 테이블이고 마지막 항목은 titles 테이블의 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

쿼리를 실행하는 평균, 최소 및 최대 시간이 즉시 개선되었음을 알 수 있습니다. 평균 68초가 아닌 이제 쿼리가 55초 내에 실행됩니다. 이는 동일한 부하에 대해 13초가 향상된 것입니다.

이 데이터베이스 변경은 테스트 환경에서 좋은 결과를 가져왔으므로 이제 프로덕션 데이터베이스 서버로 롤아웃하는 것을 고려할 수 있습니다. 하지만 일반적으로 데이터베이스 변경에는 장단점이 있습니다.

로그에서 수집한 모든 쿼리를 사용하여 명령 및 개선 사항을 테스트하는 프로세스를 반복할 수 있습니다.

문제 해결 - mysqlslap이 출력을 표시하지 않음

테스트 명령을 실행했는데 출력이 나오지 않으면 서버 리소스가 최대가 될 수 있다는 좋은 표시입니다. 증상에는 Benchmark 출력이 없거나 mysqlslap: Error when stored result: 2013 Lost connection to MySQL server during query와 같은 오류가 포함될 수 있습니다.

–concurrency 또는 –iterations 매개변수에서 더 작은 숫자로 테스트를 다시 시도할 수 있습니다. 또는 테스트 서버 환경을 업그레이드해 볼 수 있습니다.

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

결론

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

이 튜토리얼에서 우리는 다양한 옵션과 함께 mysqlslap을 사용하는 방법을 보았고 샘플 데이터베이스를 가지고 놀았습니다. MySQL 사이트에서 다른 샘플 데이터베이스를 다운로드하여 연습할 수도 있습니다. 앞에서 언급했듯이 프로덕션 데이터베이스 서버에서 테스트를 실행하지 마십시오.

이 자습서의 마지막 사용 사례에는 하나의 쿼리만 포함되었습니다. 세 테이블 모두에 추가 인덱스를 추가하여 해당 쿼리의 성능을 어느 정도 개선했지만 실제 프로세스는 그렇게 간단하지 않을 수 있습니다. 추가 인덱스를 추가하면 때때로 시스템 성능이 저하될 수 있으며 DBA는 종종 발생할 수 있는 성능 비용에 대해 추가 인덱스 추가의 이점을 비교해야 합니다.

실제 테스트 시나리오는 더 복잡하지만 데이터베이스 성능 테스트 및 개선을 시작하는 도구를 제공해야 합니다.