開発者ドキュメント

mysqlslapを使用してMySQLクエリのパフォーマンスを測定する方法

序章

MySQLには、バージョン5.1.4から使用されているmysqlslapと呼ばれる便利な小さな診断ツールが付属しています。 これは、DBAと開発者がデータベースサーバーの負荷テストを行うのに役立つベンチマークツールです。

mysqlslapは、データベースサーバーに同時にアクセスする多数のクライアント接続をエミュレートできます。 負荷テストのパラメーターは完全に構成可能であり、さまざまなテスト実行の結果を使用して、データベース設計またはハードウェアリソースを微調整できます。

このチュートリアルでは、mysqlslapを使用して、いくつかの基本的なクエリを使用してMySQLデータベースの負荷テストを行う方法と、ベンチマークがこれらのクエリの微調整にどのように役立つかを確認します。 いくつかの基本的なデモンストレーションの後、テスト用に既存のデータベースのコピーを作成し、ログからクエリを収集し、スクリプトからテストを実行する、かなり現実的なテストシナリオを実行します。

このチュートリアルに示されているコマンド、パッケージ、およびファイルは、 CentOS7でテストされています。 概念は他のディストリビューションでも同じです。

どのサイズのサーバーを使用する必要がありますか?

特定のデータベースサーバーのベンチマークに関心がある場合は、同じ仕様でデータベースの正確なコピーがインストールされているサーバーでテストする必要があります。

学習目的でこのチュートリアルを実行し、その中のすべてのコマンドを実行する場合は、少なくとも 2GBドロップレットをお勧めします。 このチュートリアルのコマンドはサーバーに負担をかけることを目的としているため、小さいサーバーではタイムアウトになる場合があります。

このチュートリアルのサンプル出力は、教育用の例を最適化するためにさまざまな方法で作成されました。

ステップ1—テストシステムへのMySQLCommunityServerのインストール

まず、MySQLCommunityServerの新しいコピーをテストデータベースにインストールします。 本番データベースサーバーでこのチュートリアルのコマンドやクエリを実行しないでください。

これらのテストは、テストサーバーにストレスを与えることを目的としており、運用サーバーで遅延やダウンタイムを引き起こす可能性があります。 このチュートリアルは、次の環境でテストされました。

まず、このチュートリアルに関連するすべてのファイルを保持するディレクトリを作成します。 これは物事を整頓するのに役立ちます。 このディレクトリに移動します。

sudo mkdir /mysqlslap_tutorial
cd /mysqlslap_tutorial

次に、MySQL CommunityReleaseyumリポジトリをダウンロードします。 ダウンロードしているリポジトリは、CentOS7で動作するRedHat EnterpriseLinux7用です。

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 CommunityServerが必要でした。

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

MySQLCommunityServerをインストールします。

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

これにより、一連のプロンプトが表示されます。 以下にプロンプトを表示し、redに入力する必要のある回答を示します。 最初はMySQLrootユーザーのパスワードがないので、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というユーザーアカウントを作成しましょう。 このアカウントは、rootユーザーではなく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—サンプルデータベースのインストール

次に、テスト用のサンプルデータベースをインストールする必要があります。 このデータベースはemployeesと呼ばれ、MySQLWebサイトから無料でアクセスできます。 データベースはLaunchpadからダウンロードすることもできます。 従業員データベースは、PatrickCrewsとGiuseppeMaxiaによって開発されました。 元のデータは、SiemensCorporateResearchのFushengWangとCarloZanioloによって作成されました。

大規模なデータセットを備えているため、employeesデータベースを選択しています。 データベースの構造は非常に単純です。テーブルは6つしかありません。 ただし、そこに含まれるデータには3,000,000を超える従業員レコードがあります(給与テーブル自体には約300万行があります)。 これは、より現実的な本番ワークロードをエミュレートするのに役立ちます。

まず、 /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分かかります。 ここでは2つのステップで実行しています。

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

プロンプトで、前のセクションで sysadminMySQLユーザー用に作成したパスワードを入力します。

プロセス出力は次のようになります。 実行には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

sysadminMySQLユーザーのパスワードを入力します。

新しい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ユーザーで1つのプロンプトを開き、1つのプロンプトをMySQLにログインさせます。

テスト用の特定のコマンドに入る前に、最も便利なmysqlslapオプションのリストを確認することをお勧めします。 これは、後で独自のmysqlslapコマンドを設計するのに役立ちます。

オプション その意味
-ユーザー データベースサーバーに接続するためのMySQLユーザー名
-パスワード ユーザーアカウントのパスワード。 コマンドラインでは空白のままにすることをお勧めします
-亭主 MySQLデータベースサーバー名
-ポート デフォルトが使用されていない場合にMySQLに接続するためのポート番号
–並行性 mysqlslapがエミュレートする同時クライアント接続の数
–反復 テストクエリが実行される回数
–create-schema テストを実行するスキーマ
–クエリ 実行するクエリ。 これは、SQLクエリ文字列またはSQLスクリプトファイルへのパスのいずれかです。
-作成 テーブルを作成するためのクエリ。 繰り返しますが、これはクエリ文字列またはSQLファイルへのパスにすることができます
-デリミタ 複数のSQLステートメントを区切るために使用される区切り文字
-エンジン 使用するMySQLデータベースエンジン(例:InnoDB)
–auto-generate-sql MySQLが独自の自動生成されたSQLコマンドを使用して負荷テストを実行できるようにします

ユースケース:自動生成されたSQLとデータによるベンチマーク

まず、mysqlslapのauto-generate-sql機能を使用します。 自動生成されたSQLを使用すると、mysqlslapは別の一時データベース(適切にはmysqlslapと呼ばれます)を作成します。 このデータベースには、1つの整数と1つのvarcharタイプの列にサンプルデータが入力された単純なテーブルが含まれます。 これは、データベースサーバーの全体的なパフォーマンスをすばやく簡単に確認する方法です。

自動生成されたSQLを1回繰り返して、単一のクライアント接続をテストすることから始めます。

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が表示されていることに注意してください。 クライアントあたりの平均クエリ数はゼロです。

自動生成されたSQLは、2つのフィールドを持つ単純なテーブルを作成します。 ほとんどの実稼働環境では、テーブル構造はそれよりもはるかに大きくなります。 テストテーブルにフィールドを追加することで、これをエミュレートするようにmysqlslapに指示できます。 そのために、次の2つの新しいパラメーターを使用できます。 --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が適しています。 特定のシステムが取ることができる負荷のレベルを知りたい場合に役立ちます。

ただし、特定のデータベース依存アプリケーションのパフォーマンスのトラブルシューティングを行う場合は、実際のデータに対して実際のクエリをテストする必要があります。 これらのクエリは、Webサーバーまたはアプリケーションサーバーから送信されている可能性があります。

今のところ、テストしたい特定のクエリを知っていると仮定します。 次のセクションでは、サーバーで実行されているクエリを見つける方法を示します。

インラインクエリから始めます。 –query オプションを使用して、mysqlslapにインラインクエリを指定できます。 SQLステートメントには改行を含めることはできず、セミコロン(;)で区切る必要があります。 クエリも二重引用符で囲む必要があります。

次のコードスニペットでは、dept_empテーブルに対して簡単なクエリを実行しています。 The dept_emp テーブルには30万を超えるレコードがあります。 employeesデータベースを–create-schemaオプションで指定した方法に注意してください。

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ステートメントを使用します。 次の例では、各クエリをセミコロンで終了しています。 –delimiter オプションを指定したため、mysqlslapはいくつかの個別の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に要求しています。 クエリ数パラメーターも指定しています。 このプロセスは2回繰り返され、出力にデバッグ情報が必要です。

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の量に確かに影響されましたが、適度な数のクライアント接続からの大量のクエリが2回繰り返されたことが原因でもありました。

非物理的なページフォールトが多数あったことがわかります。 ページフォールトは、データがメモリ内に見つからず、システムがディスク上のスワップファイルからデータをフェッチする必要がある場合に発生します。 出力には、CPU関連の情報も表示されます。 この場合、多数のコンテキストスイッチが表示されます。

ユースケース:実用的なベンチマークシナリオとライブクエリのキャプチャ

これまでの例では、元の従業員データベースに対してクエリを実行してきました。 これは、DBAがあなたに望んでいないことです。 そして、それには正当な理由があります。 本番データベースのロードを追加したくない場合や、本番テーブルにデータを削除、更新、または挿入する可能性のあるテストクエリを実行したくない場合。

本番データベースのバックアップを作成し、それをテスト環境にコピーする方法を説明します。 この例では、同じサーバー上にありますが、理想的には、同じハードウェア容量を持つ別のサーバーにコピーします。

さらに重要なのは、本番データベースからライブでクエリを記録し、テストスクリプトに追加する方法を示すことです。 つまり、本番データベースからクエリを取得しますが、テストデータベースに対してテストを実行します。

一般的な手順は次のとおりであり、任意のmysqlslapテストに使用できます。

1.本番データベースをテスト環境にコピーします。 2。 本番データベース上のすべての接続要求とクエリを記録およびキャプチャするようにMySQLを構成します。 3.テストしようとしているユースケースをシミュレートします。 たとえば、ショッピングカートを実行する場合は、アプリケーションから適切なデータベースクエリをすべてトリガーするものを購入する必要があります。 4.クエリログをオフにします。 5. クエリログを見て、テストするクエリのリストを作成します。 6。テストするクエリごとにテストファイルを作成します。 7.テストを実行します。 8. 出力を使用して、データベースのパフォーマンスを向上させます。

まず、employeesデータベースのバックアップを作成しましょう。 バックアップ用に別のディレクトリを作成します。

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サーバーでテストするクエリを実行します。 この例では、コマンドラインからクエリを実行します。 ただし、クエリを直接実行するのではなく、アプリケーションからクエリを生成することもできます。 テストしたい遅いプロセスまたはWebサイトページがある場合は、そのプロセスを実行するか、今すぐそのWebページにアクセスする必要があります。 たとえば、ショッピングカートを実行している場合は、今すぐチェックアウトプロセスを完了して、データベースサーバーで適切なクエリをすべてトリガーする必要があります。

これは、本番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コマンドとそのタイムスタンプが表示されます。 ファイルの終わり近くにあるSQLSELECT ステートメントは、私たちが関心を持っているものです。 キャプチャした場所であるため、本番データベースで実行したコマンドとまったく同じである必要があります。

この例では、クエリはすでにわかっています。 ただし、実稼働環境では、このメソッドは、サーバーで実行されているクエリについて必ずしも知っているとは限らないクエリを見つけるのに非常に役立ちます。

ロギング中に別のクエリを実行またはトリガーした場合、このファイルは完全に異なって見えることに注意してください。 実際のシナリオでは、このファイルには、すべての異なる接続からの何百ものエントリが殺到する可能性があります。 目標は、ボトルネックの原因となっている1つまたは複数のクエリを見つけることです。 テキスト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個の同時接続のみを使用し、テストを2回繰り返します。 テストサーバーからこれを実行します。

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つのインデックスを作成します。 employeeテーブルのemp_noフィールドに1つのインデックスが作成され、dept_empemp_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が出力を表示しない

テストコマンドを実行しても出力が得られない場合、これはサーバーリソースが最大になっている可能性があることを示しています。 症状には、ベンチマーク出力の欠如、または次のようなエラーが含まれる場合があります。 mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query.

–concurrencyまたは–iterations パラメーターの数値を小さくして、テストを再試行することをお勧めします。 または、テストサーバー環境のアップグレードを試すこともできます。

これは、データベースサーバーの容量の限界を見つけるための良い方法です。

結論

mysqlslapは、使いやすく、MySQLデータベースエンジンとネイティブに統合されたシンプルで軽量なツールです。 バージョン5.1.4以降のMySQLのすべてのエディションで使用できます。

このチュートリアルでは、mysqlslapをさまざまなオプションとともに使用する方法を確認し、サンプルデータベースを試してみました。 MySQLサイトから他のサンプルデータベースをダウンロードして、それらを使って練習することもできます。 前述したように、本番データベースサーバーでテストを実行しないでください。

このチュートリアルの最後のユースケースには、1つのクエリのみが含まれていました。 3つのテーブルすべてにインデックスを追加することで、そのクエリのパフォーマンスをいくらか改善しましたが、実際のプロセスはそれほど単純ではない場合があります。 インデックスを追加するとシステムパフォーマンスが低下することがあり、DBAは多くの場合、インデックスを追加することのメリットと、発生する可能性のあるパフォーマンスコストを比較検討する必要があります。

実際のテストシナリオはより複雑ですが、これにより、データベースのパフォーマンスのテストと改善を開始するためのツールが提供されます。

モバイルバージョンを終了