MySQL でのキャッシュ

7.1 DB側のキャッシュ

MySQL はテーブルを操作するときに拡張性の高いアルゴリズムを使用するため、少量のメモリでも MySQL を実行できます。当然のことながら、パフォーマンスを向上させるには、より多くの RAM が必要になります。

現在の設定を表示するには、データベースに接続します

#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

まず、キャッシュが有効になっていることを確認します。変数：

• query_cache_typeON (1) または DEMAND (2) である必要があります
• query_cache_limit- キャッシュに入る結果の最大サイズを決定します
• query_cache_sizeゼロとは違うこと。DEMAND を使用する場合、ディレクティブを含むリクエストのみがキャッシュされます。SQL_CACHE;
• query_cache_min_res_unitキャッシュされたクエリの結果を保存するために割り当てられたメモリ ブロックの最小サイズ。MySQL はキャッシュを 1 つの大きなメモリ チャンクに保存せず、オンデマンドで最小サイズquery_cache_min_res_unit(デフォルトでは = 4KB) のブロックを割り当てます。最後のブロックはデータ サイズに切り詰められ、残りのメモリが解放されます。

キャッシュの効果は、サーバーがリクエストを受信したときに、リクエストのハッシュがキャッシュ内にあるかどうかを確認することです。ハッシュが一致した場合、サーバーはリクエストの解析や最適化などを行わずに、ただちに結果を返します。オーバーヘッド - キャッシュ メカニズムを伴う - キャッシュの参照、クエリ結果のキャッシュへの書き込みなど。

また、キャッシュ内に小さなリクエストが多数ある場合、多数の空きブロックが原因でメモリの断片化が発生する可能性があります。そして、これにより、メモリ不足によりキャッシュされたエントリが削除されてしまいます。この場合、 の値を減らすのが合理的ですquery_cache_min_res_unit。ほとんどのクエリが大きな結果を生成する場合、この設定を増やすとパフォーマンスが向上する可能性があります。

効果を評価してみましょう。キャッシュ ヒット カウンターがどのように変化するか (Qcahe_hits)、メモリ不足のために無効と宣言されたリクエストの数 (Qcache_lowmem_prunes)、SELECT タイプのリクエストの総数 (キャッシュされるリクエストのみ) を確認します。

#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 現在のキャッシュのステータス

クエリキャッシュを監視するには、次のものが使用されますSHOW STATUS。

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blocksキャッシュ内に空きブロックがいくつあるかを示します (キャッシュされたリクエストが増加すると減少します)。
• Qcache_total_blocks— 占有ブロックの数;
• Qcache_free_memory- キャッシュに「利用可能な」空きメモリを表示します。
• Qcache_hits- 実際にデータベースにアクセスせずにキャッシュから結果が取得されたリクエストの数。
• Qcache_inserts- キャッシュに追加されたリクエストの数。
• Qcache_lowmem_prunes- メモリ不足によりキャッシュから削除されたリクエストの数。
• Qcache_not_cached- 時間管理機能など​​の利用によりキャッシュに書き込まれなかったリクエストの数。
• Qcache_queries_in_cache- キャッシュ内にあるリクエストの数。

SELECT クエリの合計数を表示できます。

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 キャッシュ効率

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

現在の値は次のように表示できます。

SHOW VARIABLES LIKE 'query_cache_size';

ここでも、適切な値をどのように選択するかという問題が生じます。query_cache_size?

これは役に立ちますQcache_lowmem_prunes。この変数には、新しいリクエストをキャッシュする必要があるためにキャッシュから削除されたリクエストの数が格納されます。キャッシュ サイズがQcache_lowmem_prunesわずかに増加する程度に抑えるよう努める必要があります。Qcache_lowmem_prunesこれを行うには、 1 時間あたりの値の差と、同じ 1 時間に mysql によって受信されたリクエストの数を比較することをお勧めします。

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (10 分間のトラフィック量) * 1.2 "

これにより、リクエストが 10 分間キャッシュされ、キャッシュの断片化と追加のキャッシュ予約用に追加の 20% メモリが与えられます。

Bytes_sent変数を使用して、リクエストに対する応答の数と平均量をそれぞれ計算できます。

そして、query_cache_size値を増やしました。その後、値と に注意を払う必要がQcache_total_blocksありQcache_free_blocksますQcache_queries_in_cache。MySQL はキャッシュをブロックに保存します。1 つのリクエストに対して 2 つのブロックが必要です。1 つはリクエスト テキスト自体用、もう 1 つは結果用です。

値から表を考えるとQcache%

キャッシュブロックの総数Qcache_total_blocks – 28

リクエスト 6 は現在キャッシュされています。これは、6 * 2 = 12 ブロックがビジーであることを意味します

無料のブロックQcache_free_blocks – 10。アイドル状態が多いほどQcache_free_blocks、キャッシュの「断片化」の程度が大きくなります。

ほとんどのクエリの結果データが少量である場合は、最小キャッシュ ブロック サイズquery_cache_min_res_unit(デフォルトでは 4 KB) を減らす価値があります。

ほとんどのリクエストが大量のデータを返す場合は、キャッシュ ブロックのサイズを増やす価値があります。

重要なことは、最小値を達成することですQcache_free_blocks。

カウンタQcache_not_cachedが大きい場合は、変数を増やすことを試みることができますquery_cache_limit。これにより、制限を増やし、「適合しない」クエリの結果をキャッシュできるようになります。

次の構成変数は、クエリ キャッシュの使用を担当します。

• query_cache_size— クエリキャッシュのサイズ。query_cache_size = 0キャッシュの使用を無効にします。
• query_cache_limit- キャッシュに保存される最大サンプルのサイズ。
• query_cache_wlock_invalidate- データが属するテーブルが読み取り用にロックされている場合に、データをキャッシュから取得するかどうかを決定します。
=

mysql クエリのキャッシュを有効にするには、次の行をmy.cnf (セクション [mysqld]) に追加するだけです。

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

そしてサービスを再起動します。

7.4 キャッシュを使用しない場合

キャッシュされていない:

• からのリクエストSQL_NO_CACHE
• 準備されたクエリ(Prepared statements);
• 外部クエリのサブクエリであるクエリ。
• ストアド プロシージャおよび関数内のクエリ。
• 関数を使用するクエリ:

  BENCHMARK ()、CONNECTION_ID ()、CONVERT_TZ ()、CURDATE ()、CURRENT_DATE ()、CURRENT_TIME ()、CURRENT_TIMESTAMP ()、CURTIME ()、DATABASE ()、ENCRYPT ()引数が 1 つある、FOUND_ROWS ()、GET_LOCK ()、 、 LAST_INSERT_ID ()、 、LOAD_FILE ()、 、 、MASTER_POS_WAIT ()、 、NOW ()、RAND ()、 、RELEASE_LOCK ()、SLEEP ()、 、SYSDATE ()、UNIX_TIMESTAMP ()、USER ()、UUID ();

• ストアド関数、ユーザー変数、またはmysqlシステム データベースまたはINFORMATION_SCHEMA内のテーブルの参照を使用したクエリ。
• • 次の形式でのリクエスト:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• 一時テーブルを使用するクエリ、またはテーブルをまったく使用しないクエリ。
• 警告を生成するリクエスト (警告)。

次のコマンドを使用してキャッシュをデフラグできます。

mysql>flush query cache;

クリア - コマンド:

mysql>flush query cache;

最も重要な

本番環境ではデフォルト設定で作業しないでください。これにより、サーバーのリソースのほとんどが使用されなくなります。MySQL を適切にチューニングすると、データベースのパフォーマンスが数倍向上します。これにより、アプリケーションが高速化されるだけでなく、重い負荷にも対処できます。