MySQL 5.6 での table_open_cache パラメータの最適化と適切な構成の詳細な説明

1. はじめに

table_cache は非常に重要な MySQL パフォーマンス パラメータであり、バージョン 5.1.3 以降では table_open_cache と呼ばれます。 table_cache は主にテーブルキャッシュの数を設定するために使用されます。各クライアント接続は少なくとも 1 つのテーブルにアクセスするため、このパラメータの値は max_connections に関連しています。
たとえば、1000 個の接続が並列で実行される場合、少なくとも 1000 × N のテーブル キャッシュが必要です。ここで、N はアプリケーションが実行できるクエリの結合におけるテーブルの最大数です。さらに、一時テーブルとファイル用にいくつかの追加ファイル記述子を予約する必要があります。

2. キャッシュメカニズム

接続がテーブルにアクセスすると、MySQL は現在キャッシュされているテーブルの数をチェックします。テーブルがキャッシュ内で開かれている場合は、クエリを高速化するためにキャッシュ内のテーブルに直接アクセスされます。テーブルがキャッシュされていない場合は、現在のテーブルがキャッシュに追加され、クエリが実行されます。

キャッシュ操作を実行する前に、table_open_cache を使用してキャッシュ テーブルの最大数を制限します。現在キャッシュされているテーブルが table_open_cache に達していない場合は、新しいテーブルが追加されます。この値に達した場合、MySQL は最後のクエリ時間、クエリ レート、およびキャッシュ テーブルのその他のルールに基づいて以前のキャッシュを解放します。 MySQL がテーブルにアクセスするたびに、テーブル バッファに空きがあれば、テーブルが開かれ、そこに配置されるため、テーブルの内容に高速にアクセスできます。

3. 判断方法

ピーク時のステータス値 Open_tables と Opened_tables を確認することで、table_open_cache の値を増やす必要があるかどうかを判断できます。

open_tables が table_open_cache と等しく、opened_tables が増加している場合は、table_open_cache の値を増やす必要があります (上記のステータス値は、SHOW GLOBAL STATUS LIKE 'Open%tables' を使用して取得できます)。

table_open_cache を盲目的に大きな値に設定することはできないことに注意してください。設定値が高すぎると、ファイル記述子が不足し、パフォーマンスが不安定になったり、接続に失敗したりする可能性があります。

開いているテーブル / 開いているテーブル >= 0.85

オープンテーブル / テーブルキャッシュ <= 0.95

4. 提案

最初はよくわからない場合は、MySQL データベースを一定期間本番環境に置き、その後パラメータ値を Opened_tables の値より大きくなるように調整し、比較的負荷が高い極端な状況でも Opened_tables よりわずかに大きい値であることを確認します。
MySQL のデフォルト インストールでは、メモリが 2G 未満のマシンでは table_cache の値は 256 ～ 512 です。マシンに 4G のメモリがある場合、デフォルト値は 2048 です。ただし、マシンのメモリが大きいほど、この値を大きくする必要があるというわけではありません。table_cache を増やすと、MySQL の SQL への応答が速くなるため、必然的にデッド ロックが増え、データベース操作全体の速度が低下し、パフォーマンスに重大な影響を及ぼします。したがって、日常的なメンテナンスの際には、データベースの実際の状況に基づいて判断し、メンテナンスしているデータベースに最適な table_cache 値を見つける必要があります。

mysql>flush tables; open_tables をクリアできます

# service mysqld restart は opened_tables をクリアできます

以下はMySQL 5.6の手順です

table_open_cache はテーブル キャッシュのサイズを指定します。 MySQL がテーブルにアクセスするたびに、テーブル バッファに空きがあれば、テーブルが開かれ、そこに配置されるため、テーブルの内容に高速にアクセスできます。
ピーク時のステータス値 Open_tables と Opened_tables を確認することで、table_open_cache の値を増やす必要があるかどうかを判断できます。
open_tables が table_open_cache と等しく、opened_tables が増加している場合は、table_open_cache の値を増やす必要があります (上記のステータス値は、SHOW GLOBAL STATUS LIKE 'Open%tables' を通じて取得できます)。
table_open_cache を盲目的に大きな値に設定することはできません。値が大きすぎると、シェルのファイル記述子を超えてしまいます (ulimit -n で確認してください)。その結果、ファイル記述子が不足し、パフォーマンスが不安定になったり、接続に失敗したりする可能性があります。

テスト環境: Tencent Cloud CDB、メモリ 4000M、コンソールで table_open_cache=512 を確認し、table_open_cache 設定が適切かどうか、最適化する必要があるかどうかを監視します。

'%table_open_cache%' のような変数を表示します。 

「Open%tables」のようなグローバルステータスを表示します。

open_tables が table_open_cache と等しく、両方とも 512 であることがわかりました。これは、MySQL が新しいテーブルを収容するためにキャッシュされたテーブルを解放していることを示しています。この時点で、table_open_cache の値を増やす必要がある場合があります。4G メモリを搭載したマシンの場合は、2048 に設定することをお勧めします。

より適切な値:

開いているテーブル / 開いているテーブル >= 0.85
オープンテーブル / テーブルオープンキャッシュ <= 0.95

このパラメータについて確信が持てない場合は、非常に保守的な設定の提案があります。MySQL データベースを本番環境に一定期間配置して試用し、パラメータ値を Opened_tables の値よりも大きくなるように調整し、比較的負荷が高い極端な状況でも Opened_tables よりもわずかに大きいことを確認します。