MySQL 最適化 query_cache_limit パラメータの説明

クエリキャッシュ制限

query_cache_limit は、単一のクエリで使用できるバッファサイズを指定します。デフォルトは 1M です。

query_cache_size の最適化

MySQL 4.0.1 以降では、クエリバッファメカニズムが提供されます。クエリバッファリングを使用すると、MySQL は SELECT ステートメントとクエリ結果をバッファに保存します。今後、同じ SELECT ステートメント (大文字と小文字が区別されます) の結果はバッファから直接読み取られます。 MySQL ユーザーマニュアルによると、クエリバッファを使用すると最大 238% の効率向上が達成できます。

ステータス値Qcache_*を確認することで、query_cache_sizeの設定が適切かどうかを知ることができます（上記のステータス値はSHOW STATUS LIKE 'Qcache%'を使用して取得できます）。 Qcache_lowmem_prunes の値が非常に大きい場合、バッファが不足することが多いことを示しています。Qcache_hits の値も非常に大きい場合は、クエリバッファが非常に頻繁に使用されていることを示し、バッファサイズを増やす必要があります。Qcache_hits の値が大きくない場合は、クエリの繰り返し率が非常に低いことを示しています。この場合、クエリバッファを使用すると効率に影響するため、クエリバッファを使用しないことを検討できます。さらに、SELECT ステートメントに SQL_NO_CACHE を追加すると、クエリバッファが使用されていないことが明示的に示されます。

クエリバッファリングに関連するその他のパラメータには、query_cache_type、query_cache_limit、query_cache_min_res_unit などがあります。 query_cache_type は、クエリバッファリングを使用するかどうかを指定し、0、1、または 2 に設定できます。この変数は、SESSION レベルの変数です。 query_cache_limit は、単一のクエリで使用できるバッファサイズを指定します。デフォルトは 1M です。 query_cache_min_res_unit はバージョン 4.1 以降で導入されました。割り当てられたバッファスペースの最小単位を指定します。デフォルトは 4K です。ステータス値 Qcache_free_blocks を確認します。値が非常に大きい場合は、バッファー内にフラグメントが多数存在し、クエリ結果が比較的小さいことを意味します。この場合、query_cache_min_res_unit を減らす必要があります。

サーバーのコンテンツは96Gなので、次のように設定します

クエリキャッシュサイズ = 128M
クエリキャッシュ制限 = 8M

比較的完全な my.ini ファイルです。MySQL の実行が失敗しないように、独自のサーバーに応じて適切に調整できます。これは MySQL バージョン 5.6 用です。

[クライアント]
ポート = 3306
ソケット = /tmp/mysql.sock
デフォルトの文字セット = utf8
#クライアントの文字エンコーディングを設定する
[mysqld]
# 一般的な設定オプション
ポート = 3306
ソケット = /tmp/mysql.sock
basedir="E:/database/mysql/"
データディレクトリ="E:/database/mysql/data/"
tmpdir = "E:/database/mysql/tmp/"
#*** 文字セット ***
文字セットサーバー = utf8
#サーバーの文字エンコーディングを設定する
#次の3つのパラメータのデフォルトは12500、1400、2000です
パフォーマンス_スキーマ_最大_テーブル_インスタンス = 20000
テーブル定義キャッシュ = 2000
テーブルオープンキャッシュ = 4096

#*** ネットワーク ***
バックログ = 1024
#skip-networking #デフォルトでは有効になっていません
最大接続数 = 10000
#最大接続エラー数 = 3000
テーブルオープンキャッシュ = 4096
#外部ロック #デフォルトでは有効になっていません
最大許容パケット = 256M
最大ヒープテーブルサイズ = 128M
secure_file_priv=''
明示的なタイムスタンプのデフォルト=true
同時挿入=2

＃*** タイムアウト ***
インタラクティブタイムアウト=1000
待機タイムアウト=1000
# *** グローバルキャッシュ ***
読み取りバッファサイズ = 64M
読み取りバッファサイズ = 64M
ソートバッファサイズ = 64M
結合バッファサイズ = 1024M

＃ *** 糸 ***
スレッドキャッシュサイズ = 64
# スレッド同時実行性 = 8
スレッドスタック = 512K

# *** クエリキャッシュ ***
クエリキャッシュサイズ = 128M
クエリキャッシュ制限 = 8M

＃ *** 索引 ***
ft_min_word_len = 8

#memlock #デフォルトでは有効になっていません
デフォルトのストレージエンジン=MYISAM
innodb=オフ
デフォルトの tmp ストレージエンジン = MYISAM
transaction_isolation = 繰り返し読み取り

# *** 一時テーブル ***
tmp_table_size = 1024M

# *** bin ログ ***
#ログ bin = mysql bin
binlog_cache_size = 4M
binlog_format=混合
#log_slave_updates #デフォルトでは有効になっていません
#log #デフォルトでは有効になっていません。これはクエリログです。有効にすると、サーバーのパフォーマンスに影響します。
log_warnings #警告ログを開く

# *** 遅いクエリログ ***
遅いクエリログ
長いクエリ時間 = 10
# *** レプリケーション関連の設定
#サーバーID = 1
#サーバーID = 2
#master-host = <ホスト名>
#master-user = <ユーザー名>
#master-password = <パスワード>
#マスターポート = <ポート>
#読み取り専用
#*** MyISAM 固有のオプション
#マイサム回復
キーバッファサイズ = 2048M
バルク挿入バッファサイズ = 128M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover_options=強制、バックアップ

# *** INNODB 固有のオプション ***
#skip-innodb #デフォルトでは有効になっていません
緩いinnodb-trx=0
緩いInnoDBロック=0
緩いInnoDBロック待機=0
ルーズ-innodb-cmp=0
インデックスごとの緩い Innodb cmp = 0
緩い Innodb cmp インデックスごとのリセット = 0
緩い innodb cmp リセット = 0
緩い innodb-cmpmem=0
緩い innodb cmpmem リセット = 0
緩い InnoDB バッファページ = 0
緩い InnoDB バッファページ LRU = 0
緩い Innodb バッファプール統計 = 0
緩いInnoDBメトリック=0
ルーズ-innodb-ft-デフォルト-ストップワード=0
#loose-innodb-ft-inserted=0
緩い innodb ft 削除済み = 0
緩い Innodb ft が削除される = 0
ルーズ-innodb-ft-config=0
緩い InnoDB フィートインデックスキャッシュ = 0
ルーズ-innodb-ft-インデックステーブル=0
緩いInnoDBシステムテーブル=0
緩い innodb-sys-tablestats=0
緩い Innodb システムインデックス = 0
緩い innodb sys 列 = 0
緩いInnoDBシステムフィールド=0
ルーズ-innodb-sys-foreign=0
緩い innodb sys 外部列 = 0
[mysqlダンプ]
素早い
最大許容パケット = 256M
[mysql]
自動再ハッシュなし
[マイサムチク]
キーバッファサイズ = 2048M
ソートバッファサイズ = 2048M
読み取りバッファ = 32M
書き込みバッファ = 32M
[mysqlホットコピー]
対話タイムアウト
[mysqld_safe]
オープンファイル制限 = 10240
sql_mode=NO_ENGINE_SUBSTITUTION、STRICT_TRANS_TABLES