著者はhttps://www.brightfunds.org/organizations/apache-software-foundation[Apache Software Foundation]を選択して、https://do.co/w4do-cta [Donationsの書き込み]の一部として寄付を受け取りましたプログラム。

前書き

https://dev.mysql.com/doc/refman/5.7/en/query-cache.html [クエリキャッシュ]は、https://www.mysql.com/ [MySQL]の顕著な機能であり、データベース。 これは、取得したレコードセットと共にMySQLの `+ SELECT +`ステートメントをメモリに保存することでこれを実現し、クライアントが同一のクエリを要求した場合、データベースからコマンドを再実行せずにデータを高速に処理できます。

RAMからキャッシュされたデータ(ランダムアクセスメモリ)は、ディスクから読み取られたデータと比較してアクセス時間が短いため、待ち時間が短縮され、入出力(I / O)操作が改善されます。 一例として、WordPressサイトや、読み取り呼び出しが多くデータの変更頻度が低いeコマースポータルの場合、クエリキャッシュを使用すると、データベースサーバーのパフォーマンスが大幅に向上し、スケーラビリティが向上します。

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

前提条件

開始する前に、次のものが必要です。

  • ファイアウォールと非rootユーザーで構成された1つのUbuntu 18.04サーバー。 Ubuntu 18.04での初期サーバーセットアップガイドを参照して、サーバーを構成できます。

  • このhttps://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-18-04 [Ubuntu 18.04にMySQLをインストールする方法]チュートリアルで説明されているように、MySQLサーバーをセットアップします。 MySQLサーバーのルートパスワードを設定していることを確認します。

手順1-クエリキャッシュの可用性の確認

クエリキャッシュを設定する前に、MySQLのバージョンがこの機能をサポートしているかどうかを確認します。 まず、 `+ ssh +`をUbuntu 18.04サーバーに追加します。

次に、次のコマンドを実行して、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 `の値が `+`に設定されており、クエリキャッシュがサポートされていることを確認できます。 ご使用のバージョンがクエリキャッシュをサポートしていないことを示す出力を受け取った場合は、詳細について「概要」セクションの注を参照してください。

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+」に設定して、https://dev.mysql.com/doc/refman/5.7/en/query-cache-in-select.htmlで始まるクエリを除くすべてのクエリのキャッシュを有効にすることもできます。 [+ SELECT SQL_NO_CACHE `]ステートメント。 値が「+2」の場合、MySQLは「+ SELECT SQL_CACHE +」コマンドで始まるクエリのみをキャッシュします。

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

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

手順3-クエリキャッシュを使用しないMySQLサーバーのテスト

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

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

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

Create database ;
OutputQuery OK, 1 row affected (0.00 sec)

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

Use ;
OutputDatabase changed

2つのフィールド( `および `)を持つテーブルを作成し、名前を `++`にします。

Create table  ( INT PRIMARY KEY,  VARCHAR(50) NOT NULL) Engine = InnoDB;
OutputQuery OK, 0 rows affected (0.01 sec)

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

Insert into (, ) values ('1', 'JANE DOE');
Insert into (, ) values ('2', 'JANIE DOE');
Insert into (, ) values ('3', 'JOHN ROE');
Insert into (, ) values ('4', 'MARY ROE');
Insert into (, ) values ('5', 'RICHARD ROE');
Insert into (, ) values ('6', 'JOHNNY DOE');
Insert into (, ) values ('7', 'JOHN SMITH');
Insert into (, ) values ('8', 'JOE BLOGGS');
Insert into (, ) values ('9', 'JANE POE');
Insert into (, ) values ('10', 'MARK MOE');
OutputQuery OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
...

次のステップは、https://dev.mysql.com/doc/refman/5.5/en/show-profile.html [MySQLプロファイラー]の起動です。これは、MySQLクエリのパフォーマンスを監視するための分析サービスです。 現在のセッションでプロファイルをオンにするには、次のコマンドを実行し、オンになっている「1」に設定します。

SET profiling = 1;
OutputQuery OK, 0 rows affected, 1 warning (0.00 sec)

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

Select * from ;

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

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リリースで削除され、https://dev.mysql.com/doc/refman/8.0/en/performance-に置き換えられることを示しているためです。 schema.html [パフォーマンススキーマ]。

次に、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 +`に設定されています。これにより、前の手順で定義したパラメーターでクエリキャッシュを有効にしたことが確認されます。

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

Use ;

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

SET profiling = 1;

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

最初のクエリを実行すると、MySQLが結果のキャッシュを作成するため、キャッシュをトリガーするにはクエリを2回実行する必要があります。

Select * from ;
Select * from ;

次に、プロファイル情報をリストします。

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)

ご覧のとおり、クエリの実行にかかる時間は、このステップで `(ステップ3のクエリキャッシュなし)から `(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クエリキャッシュ機能を有効にすると、読み取り速度が向上します。

結論

Ubuntu 18.04でMySQLサーバーを高速化するためにクエリキャッシュを設定しました。 MySQLのクエリキャッシュなどの機能を使用すると、WebサイトまたはWebアプリケーションの速度を向上させることができます。 キャッシングは、SQLステートメントの不必要な実行を削減し、データベースを最適化するための強く推奨される一般的な方法です。 MySQLサーバーの高速化の詳細については、https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysqlを試してください-on-ubuntu-18-04 [Ubuntu 18.04でMySQLを使用してサイトパフォーマンスを最適化するためにリモートデータベースを設定する方法]チュートリアル。