Ubuntu16.04でProxySQLキャッシングを使用してMySQLクエリを最適化する方法

投稿日: 2022-07-28 2022-10-19
タグ: Caching, Databases, mysql, open source, Server Optimization, Ubuntu 16.04

序章

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接続やHTTPを使用するのではなく、SQLプロトコルを介してバックエンドサーバーをリッスンするため、MySQLサーバーを監視するにはProxySQLのmonitorユーザーを追加する必要があります。 GET バックエンドが実行されていることを確認するためのリクエスト。 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';

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

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

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

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

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

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

次に、すべてのデータベースに対するすべての特権をユーザー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インターフェースを使用します 6032 の上 localhost と持っています 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が接続してクエリを実行できる各バックエンドサーバーに関する情報を保持するため、標準SQLを使用して新しいレコードを追加します INSERT 次の値を持つステートメント hostgroup_id, 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

このコマンドは、シェルリダイレクトを使用してSQLクエリを読み取ります employees.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つのクライアントのみが使用されました。

次に、どのクエリを見つけます mysqlslap ProxySQLを見て最後のコマンドで実行された stats_mysql_query_digest. これにより、クエリの 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)

前のクエリは、からデータを選択します stats_mysql_query_digest このテーブルには、ProxySQLで実行されたすべてのクエリに関する情報が含まれています。ここでは、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;

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

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

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を最適化する方法をご覧ください。

getdocs

13036

序章

前提条件

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

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

ステップ3—を使用したテスト mysqlslap キャッシングなし

ステップ4—を使用したテスト mysqlslap キャッシングあり

結論

getdocs

ステップ3—を使用したテスト `mysqlslap` キャッシングなし

ステップ4—を使用したテスト `mysqlslap` キャッシングあり