Ubuntu16.04でProxySQLキャッシングを使用してMySQLクエリを最適化する方法
著者は、 Free Software Foundation を選択して、 Write forDOnationsプログラムの一環として寄付を受け取りました。
序章
ProxySQL は、アプリケーションとデータベースの間に配置できるSQL対応のプロキシサーバーです。 複数のMySQLサーバー間の負荷分散やクエリのキャッシュレイヤーとして機能するなど、多くの機能を提供します。 このチュートリアルでは、ProxySQLのキャッシュ機能と、MySQLデータベースのクエリを最適化する方法に焦点を当てます。
MySQLキャッシングは、クエリの結果が保存されるときに発生するため、そのクエリが繰り返されると、データベースを並べ替えることなく結果を返すことができます。 これにより、一般的なクエリの速度が大幅に向上します。 ただし、多くのキャッシュ方法では、開発者はアプリケーションのコードを変更する必要があり、コードベースにバグが発生する可能性があります。 このエラーが発生しやすい方法を回避するために、ProxySQLでは透過キャッシュを設定できます。
透過キャッシングでは、データベース管理者のみがProxySQL構成を変更して、最も一般的なクエリのキャッシングを有効にする必要があります。これらの変更は、ProxySQL管理インターフェースを介して行うことができます。 開発者が行う必要があるのは、プロトコル対応プロキシに接続することだけです。プロキシは、バックエンドサーバーにアクセスせずに、キャッシュからクエリを提供できるかどうかを判断します。
このチュートリアルでは、ProxySQLを使用して、Ubuntu16.04でMySQLサーバーの透過キャッシュを設定します。 次に、 mysqlslap を使用してキャッシュの有無でパフォーマンスをテストし、キャッシュの効果と、多くの同様のクエリを実行するときに節約できる時間を示します。
前提条件
このガイドを開始する前に、次のものが必要です。
- Ubuntu 16.04初期サーバーセットアップガイドの説明に従って、2GB以上のRAMを搭載した1台のUbuntu16.04サーバー。root以外のユーザーがsudo権限とファイアウォールを使用してセットアップします。
ステップ1—MySQLサーバーのインストールとセットアップ
まず、MySQLサーバーをインストールし、クライアントクエリを処理するためのバックエンドサーバーとしてProxySQLで使用されるように構成します。
Ubuntu 16.04では、次のコマンドを使用してmysql-server
をインストールできます。
- sudo apt-get install mysql-server
Y
を押してインストールを確認します。
次に、MySQL rootユーザーパスワードの入力を求められます。 強力なパスワードを入力し、後で使用できるように保存します。
MySQLサーバーの準備ができたので、ProxySQLが正しく機能するようにサーバーを構成します。 ProxySQLはTCP接続やHTTPGET
要求を使用するのではなく、SQLプロトコルを介してバックエンドサーバーをリッスンするため、MySQLサーバーを監視するにはProxySQLのmonitorユーザーを追加する必要があります。バックエンドが実行されていることを確認します。 monitor は、ダミーのSQL接続を使用して、サーバーが稼働しているかどうかを判別します。
まず、MySQLシェルにログインします。
- mysql -uroot -p
-uroot
は、MySQL root ユーザーを使用してログインし、-p
はrootユーザーのパスワードの入力を求めます。 このrootユーザーは、サーバーの root ユーザーとは異なり、パスワードはmysql-server
パッケージのインストール時に入力したものです。
root パスワードを入力し、ENTER
を押します。
次に、2つのユーザーを作成します。1つはProxySQL用の monitor という名前で、もう1つはクライアントクエリを実行して適切な権限を付与するために使用します。 このチュートリアルでは、このユーザーにsammyという名前を付けます。
モニターユーザーを作成します。
- CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';
CREATE USER
クエリは、特定のIPから接続できる新しいユーザーを作成するために使用されます。 %
を使用することは、ユーザーが任意のIPアドレスから接続できることを意味します。 IDENTIFIED BY
は新しいユーザーのパスワードを設定します。 好きなパスワードを入力しますが、後で使用できるように忘れないでください。
ユーザーmonitorを作成したら、次にsammyユーザーを作成します。
- CREATE USER 'sammy'@'%' IDENTIFIED BY 'sammy_password';
次に、新しいユーザーに特権を付与します。 次のコマンドを実行して、monitorを構成します。
- GRANT SELECT ON sys.* TO 'monitor'@'%';
GRANT
クエリは、ユーザーに特権を与えるために使用されます。 ここでは、sys
データベース内のすべてのテーブルのSELECT
のみをmonitorユーザーに付与しました。 バックエンドサーバーをリッスンするために必要なのはこの特権だけです。
次に、すべてのデータベースに対するすべての特権をユーザーsammyに付与します。
- GRANT ALL PRIVILEGES on *.* TO 'sammy'@'%';
これにより、 sammy は、後でデータベースをテストするために必要なクエリを実行できるようになります。
次のコマンドを実行して、特権の変更を適用します。
- FLUSH PRIVILEGES;
最後に、mysql
シェルを終了します。
- exit;
これで、mysql-server
がインストールされ、ProxySQLがMySQLサーバーを監視するために使用するユーザーと、クライアントクエリを実行するためのユーザーが作成されました。 次に、ProxySQLをインストールして構成します。
ステップ2—ProxySQLサーバーのインストールと構成
これで、クエリのキャッシュレイヤーとして使用されるProxySQLサーバーをインストールできます。 キャッシングレイヤーは、アプリケーションサーバーとデータベースバックエンドサーバーの間のストップとして存在します。 これは、データベースに接続し、後で高速アクセスできるように、いくつかのクエリの結果をメモリに保存するために使用されます。
ProxySQLリリースGithubページは、一般的なLinuxディストリビューションのインストールファイルを提供します。 このチュートリアルでは、wget
を使用して、ProxySQLバージョン2.0.4Debianインストールファイルをダウンロードします。
- wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb
次に、dpkg
を使用してパッケージをインストールします。
- sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb
インストールしたら、次のコマンドでProxySQLを起動します。
- sudo systemctl start proxysql
次のコマンドを使用して、ProxySQLが正しく起動したかどうかを確認できます。
- sudo systemctl status proxysql
次のような出力が得られます。
Outputroot@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
を持ちます。
次のコマンドを実行して、インターフェイスに接続します。
- mysql -uadmin -p -h 127.0.0.1 -P6032
パスワードの入力を求められたら、admin
と入力します。
-uadmin
は、ユーザー名をadmin
として設定し、-h
フラグは、ホストをlocalhost
として指定します。 ポートは6032
で、-P
フラグを使用して指定されます。
デフォルトでは、MySQLクライアントはローカルソケットファイルとポート3306
を使用して接続するため、ここではホストとポートを明示的に指定する必要がありました。
mysql
シェルにadmin
としてログインしたので、ProxySQLが使用できるようにmonitorユーザーを構成します。 まず、標準のSQLクエリを使用して、2つのグローバル変数の値を設定します。
- UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
- 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
にロードして、変更を適用します。
- LOAD MYSQL VARIABLES TO RUNTIME;
次に、SAVE
は、再起動間で変更を永続化するためのディスク上のデータベースへの変更です。 ProxySQLは、独自の SQLite ローカルデータベースを使用して、独自のテーブルと変数を格納します。
- SAVE MYSQL VARIABLES TO DISK;
次に、バックエンドサーバーについてProxySQLに通知します。 テーブルmysql_servers
は、ProxySQLが接続してクエリを実行できる各バックエンドサーバーに関する情報を保持しているため、hostgroup_id
の次の値を持つ標準SQLINSERT
ステートメントを使用して新しいレコードを追加します]、hostname
、およびport
:
- INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);
変更を適用するには、LOAD
およびSAVE
を再度実行します。
- LOAD MYSQL SERVERS TO RUNTIME;
- SAVE MYSQL SERVERS TO DISK;
最後に、どのユーザーがバックエンドサーバーに接続するかをProxySQLに指示します。 sammy をユーザーとして設定し、sammy_password
を前に作成したパスワードに置き換えます。
- INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sammy', 'sammy_password', 1);
テーブルmysql_users
には、バックエンドサーバーへの接続に使用されるユーザーに関する情報が含まれています。 username
、password
、およびdefault_hostgroup
を指定しました。
LOAD
およびSAVE
の変更点:
- LOAD MYSQL USERS TO RUNTIME;
- SAVE MYSQL USERS TO DISK;
次に、mysql
シェルを終了します。
- exit;
ProxySQLを使用してバックエンドサーバーに接続できることをテストするには、次のテストクエリを実行します。
- 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リポジトリのクローンを作成します。
- git clone https://github.com/datacharmer/test_db.git
次に、test_db
ディレクトリに入り、次のコマンドを使用してデータベースをMySQLサーバーにロードします。
- cd test_db
- mysql -uroot -p < employees.sql
このコマンドは、シェルリダイレクトを使用してemployees.sql
ファイルのSQLクエリを読み取り、MySQLサーバーで実行してデータベース構造を作成します。
次のような出力が表示されます。
OutputINFO
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
が次のクエリで機能していることをテストします。
- mysqlslap -usammy -p -P6033 -h127.0.0.1 --auto-generate-sql --verbose
mysqlslap
には、mysql
クライアントと同様のフラグがあります。 このコマンドで使用されるものは次のとおりです。
-u
は、サーバーへの接続に使用されるユーザーを指定します。-p
はユーザーのパスワードの入力を求めます。-P
は指定されたポートを使用して接続します。-h
は指定されたホストに接続します。--auto-generate-sql
を使用すると、MySQLは独自に生成されたクエリを使用して負荷テストを実行できます。--verbose
は、出力に詳細情報を表示します。
次のような出力が得られます。
OutputBenchmark
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管理インターフェースに入ります。
- mysql -uadmin -p -h 127.0.0.1 -P6032
次に、このクエリを実行して、stats_mysql_query_digest
テーブルの情報を検索します。
- 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 1
とselect @@version_comment limit 2
は、同じダイジェストを持つ同じクエリとしてグループ化されます。
stats_mysql_query_digest
テーブルのクエリデータを確認する方法がわかったので、mysql
シェルを終了します。
- exit;
ダウンロードしたデータベースには、デモデータを含むいくつかのテーブルが含まれています。 次に、from_date
が2000-04-20
より大きいレコードを選択し、平均実行時間を記録することにより、dept_emp
テーブルでクエリをテストします。
次のコマンドを使用して、テストを実行します。
- 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'"
:ここでは、テストで実行されるクエリを指定しました。
テストには数分かかります。 完了すると、次のような結果が得られます。
OutputBenchmark
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管理インターフェースにログインします。
- mysql -uadmin -p -h127.0.0.1 -P6032
次に、このクエリを再度実行して、実行されたクエリとそのダイジェストのリストを取得します。
- 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
は、結果がメモリにキャッシュされるミリ秒数です。
- INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0xC5DDECD7E966A6C4',2000,1);
このコマンドでは、mysql_query_rules
テーブルに新しいレコードを追加しています。 このテーブルには、クエリを実行する前に適用されるすべてのルールが含まれています。 この例では、cache_ttl
列に値を追加します。これにより、指定されたダイジェストによる一致したクエリが、この列で指定されたミリ秒数の間キャッシュされます。 1
を[適用]列に配置して、ルールがクエリに適用されていることを確認します。
LOAD
およびSAVE
これらの変更を行ってから、mysql
シェルを終了します。
- LOAD MYSQL QUERY RULES TO RUNTIME;
- SAVE MYSQL QUERY RULES TO DISK;
- exit;
キャッシュが有効になったので、テストを再実行して結果を確認します。
- 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
これにより、次のような出力が得られます。
OutputBenchmark
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を最適化する方法をご覧ください。