Ubuntu18.04でクエリキャッシュを使用してMySQLを最適化する方法

投稿日: 2019-11-08 2022-04-20
タグ: Caching, Databases, mysql, Server Optimization, Ubuntu 18.04

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

序章

クエリキャッシュは、データベースからのデータ取得を高速化する優れたMySQL機能です。これは、MySQL SELECTステートメントを取得したレコードセットと一緒にメモリに保存することで実現されます。クライアントが同じクエリを要求した場合、データベースからコマンドを再度実行しなくても、データをより高速に処理できます。

ディスクから読み取られたデータと比較して、RAM（ランダムアクセスメモリ）からキャッシュされたデータはアクセス時間が短く、遅延が減少し、入出力（I / O）操作が向上します。たとえば、WordPressサイトやeコマースポータルで読み取り呼び出しが多く、データの変更が少ない場合、クエリキャッシュを使用すると、データベースサーバーのパフォーマンスが大幅に向上し、よりスケーラブルになります。

このチュートリアルでは、最初にクエリキャッシュなしでMySQLを構成し、クエリを実行して実行速度を確認します。次に、クエリキャッシュを設定し、それを有効にしてMySQLサーバーをテストして、パフォーマンスの違いを示します。

注：クエリキャッシュはMySQL 5.7.20で非推奨になり、MySQL 8.0で削除されましたが、サポートされているバージョンのMySQLを使用している場合は強力なツールです。ただし、新しいバージョンのMySQLを使用している場合は、 ProxySQL などの代替のサードパーティツールを採用して、MySQLデータベースのパフォーマンスを最適化することができます。

前提条件

始める前に、次のものが必要になります。

ファイアウォールと非rootユーザーで構成された1つのUbuntu18.04サーバー。 Ubuntu 18.04を使用したサーバーの初期設定ガイドを参照して、サーバーを構成できます。
このUbuntu18.04チュートリアルにMySQLをインストールする方法で詳しく説明されているようにセットアップされたMySQLサーバー。 MySQLサーバーのrootパスワードを設定していることを確認してください。

ステップ1—クエリキャッシュの可用性を確認する

クエリキャッシュを設定する前に、ご使用のバージョンのMySQLがこの機能をサポートしているかどうかを確認します。まず、sshをUbuntu18.04サーバーに挿入します。

ssh user_name@your_server_ip

次に、次のコマンドを実行して、rootユーザーとしてMySQLサーバーにログインします。

sudo mysql -u root -p

プロンプトが表示されたらMySQLサーバーのルートパスワードを入力し、ENTERを押して続行します。

次のコマンドを使用して、クエリキャッシュがサポートされているかどうかを確認します。

show variables like 'have_query_cache';

次のような出力が得られるはずです。

Output+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.01 sec)

have_query_cacheの値がYESに設定されていることがわかります。これは、クエリキャッシュがサポートされていることを意味します。お使いのバージョンがクエリキャッシュをサポートしていないことを示す出力を受け取った場合は、詳細について「はじめに」セクションの注記を参照してください。

MySQLのバージョンがクエリキャッシュをサポートしていることを確認して確認したので、データベースサーバーでこの機能を制御する変数の調査に進みます。

ステップ2—デフォルトのクエリキャッシュ変数を確認する

MySQLでは、いくつかの変数がクエリキャッシュを制御します。このステップでは、MySQLに付属しているデフォルト値を確認し、各変数が何を制御するかを理解します。

次のコマンドを使用して、これらの変数を調べることができます。

show variables like 'query_cache_%' ;

出力にリストされた変数が表示されます。

Output+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

query_cache_limit値は、キャッシュできる個々のクエリ結果の最大サイズを決定します。デフォルト値は1,048,576バイトで、これは1MBに相当します。

MySQLは、キャッシュされたデータを1つの大きなチャンクで処理しません。代わりに、ブロックで処理されます。各ブロックに割り当てられるメモリの最小量は、query_cache_min_res_unit変数によって決定されます。デフォルト値は4096バイトまたは4KBです。

query_cache_sizeは、クエリキャッシュに割り当てられるメモリの合計量を制御します。値がゼロに設定されている場合は、クエリキャッシュが無効になっていることを意味します。ほとんどの場合、デフォルト値は16,777,216（約16MB）に設定されます。また、query_cache_sizeは、その構造を割り当てるために少なくとも40KBが必要であることに注意してください。ここで割り当てられる値は、最も近い1024バイトのブロックに揃えられます。これは、報告された値が設定した値とわずかに異なる可能性があることを意味します。

MySQLは、query_cache_type変数を調べて、キャッシュするクエリを決定します。この値を0またはOFFに設定すると、キャッシュされたクエリのキャッシュまたは取得が防止されます。 1に設定して、 SELECTSQL_NO_CACHEステートメントで始まるクエリを除くすべてのクエリのキャッシュを有効にすることもできます。 2の値は、SELECT SQL_CACHEコマンドで始まるクエリのみをキャッシュするようにMySQLに指示します。

変数query_cache_wlock_invalidateは、クエリで使用されるテーブルがロックされている場合にMySQLがキャッシュから結果を取得するかどうかを制御します。デフォルト値はOFFです。

注： query_cache_wlock_invalidate変数は、MySQLバージョン5.7.20で非推奨になりました。その結果、使用しているMySQLのバージョンによっては、これが出力に表示されない場合があります。

MySQLクエリキャッシュを制御するシステム変数を確認したら、最初に機能を有効にせずにMySQLがどのように機能するかをテストします。

ステップ3—クエリキャッシュなしでMySQLサーバーをテストする

このチュートリアルの目的は、クエリキャッシュ機能を使用してMySQLサーバーを最適化することです。速度の違いを確認するには、クエリを実行して、機能を実装する前後のパフォーマンスを確認します。

このステップでは、サンプルデータベースを作成し、いくつかのデータを挿入して、クエリキャッシュなしでMySQLがどのように機能するかを確認します。

MySQLサーバーにログインしたまま、データベースを作成し、次のコマンドを実行してsample_dbという名前を付けます。

Create database sample_db;

OutputQuery OK, 1 row affected (0.00 sec)

次に、データベースに切り替えます。

Use sample_db;

OutputDatabase changed

2つのフィールド（customer_idとcustomer_name）を持つテーブルを作成し、customersという名前を付けます。

Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;

OutputQuery OK, 0 rows affected (0.01 sec)

次に、次のコマンドを実行して、サンプルデータを挿入します。

Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');
Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');
Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');
Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');
Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');
Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');
Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');
Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');
Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');
Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');

OutputQuery OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
...

次のステップは、 MySQLプロファイラーを起動することです。これは、MySQLクエリのパフォーマンスを監視するための分析サービスです。現在のセッションのプロファイルをオンにするには、次のコマンドを実行して、1に設定します。これはオンになっています。

SET profiling = 1;

OutputQuery OK, 0 rows affected, 1 warning (0.00 sec)

次に、次のクエリを実行してすべての顧客を取得します。

Select * from customers;

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

Output+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | JANE DOE      |
|           2 | JANIE DOE     |
|           3 | JOHN ROE      |
|           4 | MARY ROE      |
|           5 | RICHARD ROE   |
|           6 | JOHNNY DOE    |
|           7 | JOHN SMITH    |
|           8 | JOE BLOGGS    |
|           9 | JANE POE      |
|          10 | MARK MOE      |
+-------------+---------------+
10 rows in set (0.00 sec)

次に、SHOW PROFILESコマンドを実行して、実行したSELECTクエリに関するパフォーマンス情報を取得します。

SHOW PROFILES;

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

Output+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00044075 | Select * from customers |
+----------+------------+-------------------------+
1 row in set, 1 warning (0.00 sec)

出力には、データベースからレコードを取得するときにMySQLが費やした合計時間が表示されます。クエリキャッシュが有効になっている場合は、次の手順でこのデータを比較するため、Durationに注意してください。これは、SHOW PROFILESコマンドが将来のMySQLリリースで削除され、パフォーマンススキーマに置き換えられることを示しているだけなので、出力内の警告は無視してかまいません。

次に、MySQLコマンドラインインターフェイスを終了します。

quit;

クエリキャッシュを有効にする前にMySQLでクエリを実行し、Durationまたはレコードの取得に費やした時間を書き留めました。次に、クエリキャッシュを有効にして、同じクエリを実行したときにパフォーマンスが向上するかどうかを確認します。

ステップ4—クエリキャッシュの設定

前の手順では、クエリキャッシュを有効にする前に、サンプルデータを作成し、SELECTステートメントを実行しました。このステップでは、MySQL構成ファイルを編集してクエリキャッシュを有効にします。

nanoを使用して、ファイルを編集します。

sudo nano /etc/mysql/my.cnf

次の情報をファイルの最後に追加します。

/etc/mysql/my.cnf

...
[mysqld]
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K

ここでは、query_cache_typeを1に設定して、クエリキャッシュを有効にしました。また、個々のクエリ制限サイズを256Kに設定し、query_cache_sizeの値を10Mに設定して、クエリキャッシュに10メガバイトを割り当てるようにMySQLに指示しました。 ]。

CTRL + X、Y、ENTERの順に押して、ファイルを保存して閉じます。次に、MySQLサーバーを再起動して、変更を実装します。

sudo systemctl restart mysql

これで、クエリキャッシュが有効になりました。

クエリキャッシュを構成し、変更を適用するためにMySQLを再起動したら、先に進み、機能を有効にしてMySQLのパフォーマンスをテストします。

ステップ5—クエリキャッシュを有効にしてMySQLサーバーをテストする

このステップでは、ステップ3で実行したのと同じクエリをもう一度実行して、クエリキャッシュがMySQLサーバーのパフォーマンスをどのように最適化したかを確認します。

まず、rootユーザーとしてMySQLサーバーに接続します。

sudo mysql -u root -p

データベースサーバーのrootパスワードを入力し、ENTERを押して続行します。

次に、前の手順で設定した構成を確認して、クエリキャッシュが有効になっていることを確認します。

show variables like 'query_cache_%' ;

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

Output+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 262144   |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 10485760 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.01 sec)

変数query_cache_typeはONに設定されます。これにより、前の手順で定義したパラメーターを使用してクエリキャッシュが有効になったことを確認できます。

以前に作成したsample_dbデータベースに切り替えます。

Use sample_db;

MySQLプロファイラーを起動します。

SET profiling = 1;

次に、クエリを実行して、十分なプロファイリング情報を生成するために、すべての顧客を少なくとも2回取得します。

最初のクエリを実行すると、MySQLは結果のキャッシュを作成するため、キャッシュをトリガーするにはクエリを2回実行する必要があることに注意してください。

Select * from customers;
Select * from customers;

次に、プロファイル情報を一覧表示します。

SHOW PROFILES;

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

Output+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00049250 | Select * from customers |
|        2 | 0.00026000 | Select * from customers |
+----------+------------+-------------------------+
2 rows in set, 1 warning (0.00 sec)

ご覧のとおり、クエリの実行にかかる時間は、このステップで0.00044075（ステップ3のクエリキャッシュなし）から0.00026000（2番目のクエリ）に大幅に短縮されました。

最初のクエリを詳細にプロファイリングすることで、クエリキャッシュ機能を有効にすることで最適化を確認できます。

SHOW PROFILE FOR QUERY 1;

Output+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000025 |
| Waiting for query cache lock   | 0.000004 |
| starting                       | 0.000003 |
| checking query cache for query | 0.000045 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000014 |
| init                           | 0.000018 |
| System lock                    | 0.000008 |
| Waiting for query cache lock   | 0.000002 |
| System lock                    | 0.000018 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000013 |
| preparing                      | 0.000010 |
| executing                      | 0.000003 |
| Sending data                   | 0.000048 |
| end                            | 0.000004 |
| query end                      | 0.000006 |
| closing tables                 | 0.000006 |
| freeing items                  | 0.000006 |
| Waiting for query cache lock   | 0.000003 |
| freeing items                  | 0.000213 |
| Waiting for query cache lock   | 0.000019 |
| freeing items                  | 0.000002 |
| storing result in query cache  | 0.000003 |
| cleaning up                    | 0.000012 |
+--------------------------------+----------+
25 rows in set, 1 warning (0.00 sec)

次のコマンドを実行して、キャッシュされている2番目のクエリのプロファイル情報を表示します。

SHOW PROFILE FOR QUERY 2;

Output+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| Waiting for query cache lock   | 0.000003 |
| starting                       | 0.000002 |
| checking query cache for query | 0.000006 |
| checking privileges on cached  | 0.000003 |
| checking permissions           | 0.000027 |
| sending cached result to clien | 0.000187 |
| cleaning up                    | 0.000008 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)

プロファイラーからの出力は、MySQLがディスクからデータを読み取る代わりにクエリキャッシュからデータを取得できたため、2番目のクエリにかかる時間が短縮されたことを示しています。各クエリの2セットの出力を比較できます。 QUERY 2のプロファイル情報を見ると、sending cached result to clientのステータスは、Opening tablesステータスがないため、データがキャッシュから読み取られ、テーブルが開かれていないことを示しています。

サーバーでMySQLクエリキャッシュ機能を有効にすると、読み取り速度が向上します。

結論

Ubuntu18.04でMySQLサーバーを高速化するためのクエリキャッシュを設定しました。 MySQLのクエリキャッシュなどの機能を使用すると、WebサイトまたはWebアプリケーションの速度を向上させることができます。キャッシングはSQLステートメントの不要な実行を減らし、データベースを最適化するために強く推奨され、一般的な方法です。 MySQLサーバーの高速化の詳細については、 Ubuntu18.04チュートリアルでMySQLを使用してサイトパフォーマンスを最適化するためのリモートデータベースを設定する方法を試してください。

getdocs

13036