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

投稿日: 2022-10-16 2022-10-16
タグ: Caching, Databases, mysql, Server Optimization, Ubuntu 18.04

序章

クエリキャッシュは、データベースからのデータ取得を高速化する優れた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 Ubuntu 18.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)

The query_cache_limit valueは、キャッシュできる個々のクエリ結果の最大サイズを決定します。デフォルト値は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 MySQLにで始まるクエリのみをキャッシュするように指示します SELECT SQL_CACHE 指図。

変数 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 MySQLに割り当てるように指示しました 10 の値を設定してキャッシュをクエリするメガバイト query_cache_size に 10M.

を押してファイルを保存して閉じます 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