著者は、 Free Software Foundation を選択して、 Write forDOnationsプログラムの一環として寄付を受け取りました。

序章

ProxySQL は、アプリケーションとデータベースの間に配置できるSQL対応のプロキシサーバーです。 複数のMySQLサーバー間の負荷分散やクエリのキャッシュレイヤーとして機能するなど、多くの機能を提供します。 このチュートリアルでは、ProxySQLのキャッシュ機能と、MySQLデータベースのクエリを最適化する方法に焦点を当てます。

MySQLキャッシングは、クエリの結果が保存されるときに発生するため、そのクエリが繰り返されると、データベースを並べ替えることなく結果を返すことができます。 これにより、一般的なクエリの速度が大幅に向上します。 ただし、多くのキャッシュ方法では、開発者はアプリケーションのコードを変更する必要があり、コードベースにバグが発生する可能性があります。 このエラーが発生しやすい方法を回避するために、ProxySQLでは透過キャッシュを設定できます。

透過キャッシングでは、データベース管理者のみがProxySQL構成を変更して、最も一般的なクエリのキャッシングを有効にする必要があります。これらの変更は、ProxySQL管理インターフェースを介して行うことができます。 開発者が行う必要があるのは、プロトコル対応プロキシに接続することだけです。プロキシは、バックエンドサーバーにアクセスせずに、キャッシュからクエリを提供できるかどうかを判断します。

このチュートリアルでは、ProxySQLを使用して、Ubuntu16.04でMySQLサーバーの透過キャッシュを設定します。 次に、 mysqlslap を使用してキャッシュの有無でパフォーマンスをテストし、キャッシュの効果と、多くの同様のクエリを実行するときに節約できる時間を示します。

前提条件

このガイドを開始する前に、次のものが必要です。

ステップ1—MySQLサーバーのインストールとセットアップ

まず、MySQLサーバーをインストールし、クライアントクエリを処理するためのバックエンドサーバーとしてProxySQLで使用されるように構成します。

Ubuntu 16.04では、次のコマンドを使用してmysql-serverをインストールできます。

  1. sudo apt-get install mysql-server

Yを押してインストールを確認します。

次に、MySQL rootユーザーパスワードの入力を求められます。 強力なパスワードを入力し、後で使用できるように保存します。

MySQLサーバーの準備ができたので、ProxySQLが正しく機能するようにサーバーを構成します。 ProxySQLはTCP接続やHTTPGET要求を使用するのではなく、SQLプロトコルを介してバックエンドサーバーをリッスンするため、MySQLサーバーを監視するにはProxySQLのmonitorユーザーを追加する必要があります。バックエンドが実行されていることを確認します。 monitor は、ダミーのSQL接続を使用して、サーバーが稼働しているかどうかを判別します。

まず、MySQLシェルにログインします。

  1. mysql -uroot -p

-urootは、MySQL root ユーザーを使用してログインし、-prootユーザーのパスワードの入力を求めます。 このrootユーザーは、サーバーの root ユーザーとは異なり、パスワードはmysql-serverパッケージのインストール時に入力したものです。

root パスワードを入力し、ENTERを押します。

次に、2つのユーザーを作成します。1つはProxySQL用の monitor という名前で、もう1つはクライアントクエリを実行して適切な権限を付与するために使用します。 このチュートリアルでは、このユーザーにsammyという名前を付けます。

モニターユーザーを作成します。

  1. CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';

CREATE USERクエリは、特定のIPから接続できる新しいユーザーを作成するために使用されます。 %を使用することは、ユーザーが任意のIPアドレスから接続できることを意味します。 IDENTIFIED BYは新しいユーザーのパスワードを設定します。 好きなパスワードを入力しますが、後で使用できるように忘れないでください。

ユーザーmonitorを作成したら、次にsammyユーザーを作成します。

  1. CREATE USER 'sammy'@'%' IDENTIFIED BY 'sammy_password';

次に、新しいユーザーに特権を付与します。 次のコマンドを実行して、monitorを構成します。

  1. GRANT SELECT ON sys.* TO 'monitor'@'%';

GRANTクエリは、ユーザーに特権を与えるために使用されます。 ここでは、sysデータベース内のすべてのテーブルのSELECTのみをmonitorユーザーに付与しました。 バックエンドサーバーをリッスンするために必要なのはこの特権だけです。

次に、すべてのデータベースに対するすべての特権をユーザーsammyに付与します。

  1. GRANT ALL PRIVILEGES on *.* TO 'sammy'@'%';

これにより、 sammy は、後でデータベースをテストするために必要なクエリを実行できるようになります。

次のコマンドを実行して、特権の変更を適用します。

  1. FLUSH PRIVILEGES;

最後に、mysqlシェルを終了します。

  1. exit;

これで、mysql-serverがインストールされ、ProxySQLがMySQLサーバーを監視するために使用するユーザーと、クライアントクエリを実行するためのユーザーが作成されました。 次に、ProxySQLをインストールして構成します。

ステップ2—ProxySQLサーバーのインストールと構成

これで、クエリのキャッシュレイヤーとして使用されるProxySQLサーバーをインストールできます。 キャッシングレイヤーは、アプリケーションサーバーとデータベースバックエンドサーバーの間のストップとして存在します。 これは、データベースに接続し、後で高速アクセスできるように、いくつかのクエリの結果をメモリに保存するために使用されます。

ProxySQLリリースGithubページは、一般的なLinuxディストリビューションのインストールファイルを提供します。 このチュートリアルでは、wgetを使用して、ProxySQLバージョン2.0.4Debianインストールファイルをダウンロードします。

  1. wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb

次に、dpkgを使用してパッケージをインストールします。

  1. sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb

インストールしたら、次のコマンドでProxySQLを起動します。

  1. sudo systemctl start proxysql

次のコマンドを使用して、ProxySQLが正しく起動したかどうかを確認できます。

  1. sudo systemctl status proxysql

次のような出力が得られます。

Output
root@ubuntu-s-1vcpu-2gb-sgp1-01:~# systemctl status proxysql ● proxysql.service - LSB: High Performance Advanced Proxy for MySQL Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled) Active: active (exited) since Wed 2019-06-12 21:32:50 UTC; 6 months 7 days ago Docs: man:systemd-sysv-generator(8) Tasks: 0 Memory: 0B CPU: 0

次に、ProxySQLサーバーをMySQLサーバーに接続します。 この目的のために、ProxySQL管理SQLインターフェイスを使用します。このインターフェイスはデフォルトでlocalhostのポート6032をリッスンし、ユーザー名とパスワードとしてadminを持ちます。

次のコマンドを実行して、インターフェイスに接続します。

  1. mysql -uadmin -p -h 127.0.0.1 -P6032

パスワードの入力を求められたら、adminと入力します。

-uadminは、ユーザー名をadminとして設定し、-hフラグは、ホストをlocalhostとして指定します。 ポートは6032で、-Pフラグを使用して指定されます。

デフォルトでは、MySQLクライアントはローカルソケットファイルとポート3306を使用して接続するため、ここではホストとポートを明示的に指定する必要がありました。

mysqlシェルにadminとしてログインしたので、ProxySQLが使用できるようにmonitorユーザーを構成します。 まず、標準のSQLクエリを使用して、2つのグローバル変数の値を設定します。

  1. UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
  2. UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';

変数mysql-monitor_usernameは、バックエンドサーバーが稼働しているかどうかを確認するために使用されるMySQLユーザー名を指定します。 変数mysql-monitor_passwordは、バックエンドサーバーに接続するときに使用されるパスワードを指します。 monitorユーザー名に作成したパスワードを使用してください。

ProxySQL管理インターフェースで変更を作成するたびに、適切なLOADコマンドを使用して、実行中のProxySQLインスタンスに変更を適用する必要があります。 MySQLグローバル変数を変更したので、それらをRUNTIMEにロードして、変更を適用します。

  1. LOAD MYSQL VARIABLES TO RUNTIME;

次に、SAVEは、再起動間で変更を永続化するためのディスク上のデータベースへの変更です。 ProxySQLは、独自の SQLite ローカルデータベースを使用して、独自のテーブルと変数を格納します。

  1. SAVE MYSQL VARIABLES TO DISK;

次に、バックエンドサーバーについてProxySQLに通知します。 テーブルmysql_serversは、ProxySQLが接続してクエリを実行できる各バックエンドサーバーに関する情報を保持しているため、hostgroup_idの次の値を持つ標準SQLINSERTステートメントを使用して新しいレコードを追加します]、hostname、およびport

  1. INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);

変更を適用するには、LOADおよびSAVEを再度実行します。

  1. LOAD MYSQL SERVERS TO RUNTIME;
  2. SAVE MYSQL SERVERS TO DISK;

最後に、どのユーザーがバックエンドサーバーに接続するかをProxySQLに指示します。 sammy をユーザーとして設定し、sammy_passwordを前に作成したパスワードに置き換えます。

  1. INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sammy', 'sammy_password', 1);

テーブルmysql_usersには、バックエンドサーバーへの接続に使用されるユーザーに関する情報が含まれています。 usernamepassword、およびdefault_hostgroupを指定しました。

LOADおよびSAVEの変更点:

  1. LOAD MYSQL USERS TO RUNTIME;
  2. SAVE MYSQL USERS TO DISK;

次に、mysqlシェルを終了します。

  1. exit;

ProxySQLを使用してバックエンドサーバーに接続できることをテストするには、次のテストクエリを実行します。

  1. mysql -usammy -h127.0.0.1 -p -P6033 -e "SELECT @@HOSTNAME as hostname"

このコマンドでは、-eフラグを使用してクエリを実行し、接続を閉じました。 クエリは、バックエンドサーバーのホスト名を出力します。

注: ProxySQLは、着信接続をリッスンするためにデフォルトでポート6033を使用します。

出力は次のようになり、your_hostnameがホスト名に置き換えられます。

Output
+----------------------------+ | hostname | +----------------------------+ | your_hostname | +----------------------------+

ProxySQL構成の詳細については、 Ubuntu16.04でMySQLのロードバランサーとしてProxySQLを使用する方法のステップ3を参照してください。

これまで、MySQLサーバーをバックエンドとして使用するようにProxySQLを構成し、ProxySQLを使用してバックエンドに接続しました。 これで、mysqlslapを使用して、キャッシュせずにクエリのパフォーマンスをベンチマークする準備が整いました。

ステップ3—キャッシングなしでmysqlslapを使用してテストする

このステップでは、テストデータベースをダウンロードして、mysqlslapを使用してデータベースに対してクエリを実行し、キャッシュせずにレイテンシをテストして、クエリ速度のベンチマークを設定できるようにします。 また、ProxySQLがクエリのレコードをstats_mysql_query_digestテーブルに保持する方法についても説明します。

mysqlslap は、MySQLの負荷テストツールとして使用される負荷エミュレーションクライアントです。 自動生成されたクエリまたはデータベースで実行されたいくつかのカスタムクエリを使用して、MySQLサーバーをテストできます。 MySQLクライアントパッケージとともにインストールされるため、インストールする必要はありません。 代わりに、mysqlslapを使用できるテスト目的でのみデータベースをダウンロードします。

このチュートリアルでは、サンプル従業員データベースを使用します。 この従業員データベースを使用するのは、クエリ最適化の違いを説明できる大規模なデータセットを備えているためです。 データベースには6つのテーブルがありますが、データベースに含まれるデータには300,000を超える従業員レコードがあります。 これは、大規模な本番ワークロードをエミュレートするのに役立ちます。

データベースをダウンロードするには、最初に次のコマンドを使用してGithubリポジトリのクローンを作成します。

  1. git clone https://github.com/datacharmer/test_db.git

次に、test_dbディレクトリに入り、次のコマンドを使用してデータベースをMySQLサーバーにロードします。

  1. cd test_db
  2. mysql -uroot -p < employees.sql

このコマンドは、シェルリダイレクトを使用してemployees.sqlファイルのSQLクエリを読み取り、MySQLサーバーで実行してデータベース構造を作成します。

次のような出力が表示されます。

Output
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 data_load_time_diff 00:00:32

データベースがMySQLサーバーにロードされたら、mysqlslapが次のクエリで機能していることをテストします。

  1. mysqlslap -usammy -p -P6033 -h127.0.0.1 --auto-generate-sql --verbose

mysqlslapには、mysqlクライアントと同様のフラグがあります。 このコマンドで使用されるものは次のとおりです。

  • -uは、サーバーへの接続に使用されるユーザーを指定します。
  • -pはユーザーのパスワードの入力を求めます。
  • -Pは指定されたポートを使用して接続します。
  • -hは指定されたホストに接続します。
  • --auto-generate-sqlを使用すると、MySQLは独自に生成されたクエリを使用して負荷テストを実行できます。
  • --verboseは、出力に詳細情報を表示します。

次のような出力が得られます。

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

この出力では、すべてのクエリの実行に費やされた平均、最小、および最大の秒数を確認できます。 これにより、多数のクライアントによるクエリの実行に必要な時間がわかります。 この出力では、クエリの実行に1つのクライアントのみが使用されました。

次に、ProxySQLのstats_mysql_query_digestを調べて、最後のコマンドで実行されたクエリmysqlslapを確認します。 これにより、クエリの digest のような情報が得られます。これは、キャッシュを有効にするために後で参照できるSQLステートメントの正規化された形式です。

次のコマンドを使用して、ProxySQL管理インターフェースに入ります。

  1. mysql -uadmin -p -h 127.0.0.1 -P6032

次に、このクエリを実行して、stats_mysql_query_digestテーブルの情報を検索します。

  1. SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

次のような出力が表示されます。

+------------+----------+-----------+--------------------+----------------------------------+
| count_star | sum_time | hostgroup | digest             | digest_text                      |
+------------+----------+-----------+--------------------+----------------------------------+
| 1          | 598      | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname    |
| 1          | 0        | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ? |
+------------+----------+-----------+--------------------+----------------------------------+
2 rows in set (0.01 sec)

前のクエリは、ProxySQLで実行されたすべてのクエリに関する情報を含むstats_mysql_query_digestテーブルからデータを選択します。 ここでは、5つの列が選択されています。

  • count_star:このクエリが実行された回数。
  • sum_time:このクエリの実行にかかった合計時間(ミリ秒)。
  • hostgroup:クエリの実行に使用されるホストグループ。
  • digest:実行されたクエリのダイジェスト。
  • digest_text:実際のクエリ。 このチュートリアルの例では、2番目のクエリは、変数パラメーターの代わりに?マークを使用してパラメーター化されています。 したがって、select @@version_comment limit 1select @@version_comment limit 2は、同じダイジェストを持つ同じクエリとしてグループ化されます。

stats_mysql_query_digestテーブルのクエリデータを確認する方法がわかったので、mysqlシェルを終了します。

  1. exit;

ダウンロードしたデータベースには、デモデータを含むいくつかのテーブルが含まれています。 次に、from_date2000-04-20より大きいレコードを選択し、平均実行時間を記録することにより、dept_empテーブルでクエリをテストします。

次のコマンドを使用して、テストを実行します。

  1. mysqlslap -usammy -P6033 -p -h127.0.0.1 --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

ここでは、いくつかの新しいフラグを使用しています。

  • --concurrency=100:これは、シミュレートするユーザーの数を設定します。この場合は100です。
  • --iterations=20:これにより、テストが20回実行され、それらすべてから結果が計算されます。
  • --create-schema=employees:ここではemployeesデータベースを選択しました。
  • --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'":ここでは、テストで実行されるクエリを指定しました。

テストには数分かかります。 完了すると、次のような結果が得られます。

Output
Benchmark Average number of seconds to run all queries: 18.117 seconds Minimum number of seconds to run all queries: 8.726 seconds Maximum number of seconds to run all queries: 22.697 seconds Number of clients running queries: 100 Average number of queries per client: 1

あなたの番号は少し異なるかもしれません。 キャッシュを有効にした後の結果と比較するために、これらの数値をどこかに保持します。

キャッシュなしでProxySQLをテストした後、同じテストを再度実行しますが、今回はキャッシュを有効にします。

ステップ4—キャッシュを使用したmysqlslapを使用したテスト

このステップでは、キャッシュにより、同様のクエリを実行する際のレイテンシを減らすことができます。 ここでは、実行されたクエリを特定し、ProxySQLのstats_mysql_query_digestテーブルからダイジェストを取得し、それらを使用してキャッシュを有効にします。 次に、違いを確認するためにもう一度テストします。

キャッシュを有効にするには、キャッシュされるクエリのダイジェストを知る必要があります。 次のコマンドを使用して、ProxySQL管理インターフェースにログインします。

  1. mysql -uadmin -p -h127.0.0.1 -P6032

次に、このクエリを再度実行して、実行されたクエリとそのダイジェストのリストを取得します。

  1. SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

次のような結果が得られます。

Output
+------------+-------------+-----------+--------------------+------------------------------------------+ | count_star | sum_time | hostgroup | digest | digest_text | +------------+-------------+-----------+--------------------+------------------------------------------+ | 2000 | 33727110501 | 1 | 0xC5DDECD7E966A6C4 | SELECT * from dept_emp WHERE from_date>? | | 1 | 601 | 1 | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname | | 1 | 0 | 1 | 0x226CD90D52A2BA0B | select @@version_comment limit ? | +------------+-------------+-----------+--------------------+------------------------------------------+ 3 rows in set (0.00 sec)

最初の行を見てください。 2000回実行されたクエリについてです。 これは、以前に実行されたベンチマーククエリです。 ダイジェストを取得して保存し、キャッシュ用のクエリルールの追加に使用します。

次のいくつかのクエリは、前のクエリのダイジェストと一致する新しいクエリルールをProxySQLに追加し、それにcache_ttl値を設定します。 cache_ttlは、結果がメモリにキャッシュされるミリ秒数です。

  1. INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0xC5DDECD7E966A6C4',2000,1);

このコマンドでは、mysql_query_rulesテーブルに新しいレコードを追加しています。 このテーブルには、クエリを実行する前に適用されるすべてのルールが含まれています。 この例では、cache_ttl列に値を追加します。これにより、指定されたダイジェストによる一致したクエリが、この列で指定されたミリ秒数の間キャッシュされます。 1を[適用]列に配置して、ルールがクエリに適用されていることを確認します。

LOADおよびSAVEこれらの変更を行ってから、mysqlシェルを終了します。

  1. LOAD MYSQL QUERY RULES TO RUNTIME;
  2. SAVE MYSQL QUERY RULES TO DISK;
  3. exit;

キャッシュが有効になったので、テストを再実行して結果を確認します。

  1. mysqlslap -usammy -P6033 -p -h127.0.0.1 --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

これにより、次のような出力が得られます。

Output
Benchmark Average number of seconds to run all queries: 7.020 seconds Minimum number of seconds to run all queries: 0.274 seconds Maximum number of seconds to run all queries: 23.014 seconds Number of clients running queries: 100 Average number of queries per client: 1

ここでは、平均実行時間の大きな違いを確認できます。18.117秒から7.020に減少しました。

結論

この記事では、ProxySQLを使用して透過キャッシュを設定し、データベースクエリの結果をキャッシュします。 また、キャッシュがある場合とない場合のクエリ速度をテストして、キャッシュによる違いを確認しました。

このチュートリアルでは、1つのレベルのキャッシュを使用しました。 また、 Webキャッシングを試すこともできます。これは、Webサーバーの前に配置され、同様の要求への応答をキャッシュして、バックエンドサーバーにアクセスせずにクライアントに応答を送り返します。 これはProxySQLキャッシングと非常に似ていますが、レベルが異なります。 Webキャッシングの詳細については、 Webキャッシングの基本:用語、HTTPヘッダー、およびキャッシング戦略の入門書を参照してください。

MySQLサーバーにも独自のクエリキャッシュがあります。 詳細については、Ubuntu18.04チュートリアルでクエリキャッシュを使用してMySQLを最適化する方法をご覧ください。