アーカイブ

‘チューニング’ タグのついている投稿

mysqlクエリーキャッシュの設定

2017 年 6 月 22 日 Comments off

前回、MySQLTunerをインストールしてMySQLチューニングでmysqlのバージョンアップなどを行いチューニングを行うべくいろいろと調べてみました。

mysqlの設定を変えていくこととします。

第一弾としてクエリーキャッシュの設定

公式マニュアルなどは、こちらにあります。－－－－－－＞　Mysql 5.6 マニュアル　8.9.3 MySQL クエリーキャッシュ


mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.01 sec)

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

 

マニュアルにもあるようにhave_query_cache（クエリーキャッシュが使用できるかどうか）がYESでもquery_cache_typeがOFF（デフォルトのサイズは 0 ）のときは、キャッシュが無効になっている状態です。
そのほかの項目については、このような働きがあるようです。
query_cache_limit　キャッシュ可能な個々のクエリー結果の最大サイズを制御（デフォルト値：1 MB）
query_cache_min_res_unit　クエリーキャッシュの最小サイズのブロックを割り当て値 （デフォルト値：4 KB）
query_cache_size　クエリーキャッシュのサイズ

現在は、何もしていないのでデフォルトの状態となってます。
当然ですがクエリキャッシュは、使用されていません。
以下でキャッシュ関連の値を見ると


mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 1272923 |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

 

このように Qcache_hitsは、０となってます。キャッシュをＯＮにしてヒット率が大きければ
このキャッシュ有効化の恩恵が受けることが出来ます。

あとは、この値をどれくらいにするかですが、他のサイトやマニュアルより以下のようにしました。

#クエリキャッシュの機能を有効
query_cache_type=1
#キャッシュに利用するメモリ割当
query_cache_size=16M
query_cache_limit=1M
query_cache_min_res_unit=4k

という設定にしました。
マニュアルでは、query_cache_min_res_unitは、デフォルトで問題ないという話です。
そのほかquery_cache_limitもデフォルトで様子見、query_cache_sizeは、わからないのでとりあえずこの数値にして様子を見ます。
ちなみに当初my.cnfの最終行に書いてから再起動して反映されませんでした。
また、mysqlからコマンドを実行して以下のようなエラーが出ました。


mysql> SET GLOBAL query_cache_type=ON;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

 

もう一度my.cnfの設定を見直すと
以下ダメな設定ファイル


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server = utf8
# error log
# 警告メッセージのエラーログに出力レベルを指定します。
# level:0  メッセージは記録されません。
# level:1以上 警告メッセージが記録されます。デフォルトは1
log-warnings = 1
log_error = /var/log/mysql/error.log
#query log
#log=/var/log/mysql/query.log

#slow query log
slow_query_log=ON
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=5.0
#クエリログをテーブルとファイルに出力する
#general_log      = 1
#general_log_file = /var/log/mysql/query.log
#log_output       = FILE

# エラーログのタイムゾーンの指定
log_timestamps=SYSTEM

[mysqld_safe]
pid-file=/var/run/mysqld/mysqld.pido

#クエリキャッシュの機能を有効
query_cache_type=1
#キャッシュに利用するメモリ割当
query_cache_size=16M
query_cache_limit=1M
query_cache_min_res_unit=4k

 

以下設定が反映された設定ファイル


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server = utf8

#クエリキャッシュの機能を有効
query_cache_type=1
#キャッシュに利用するメモリ割当
query_cache_size=16M
query_cache_limit=1M
query_cache_min_res_unit=4k


# error log
# 警告メッセージのエラーログに出力レベルを指定します。
# level:0  メッセージは記録されません。
# level:1以上 警告メッセージが記録されます。デフォルトは1
log-warnings = 1
log_error = /var/log/mysql/error.log
#query log
#log=/var/log/mysql/query.log

#slow query log
slow_query_log=ON
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=5.0
#クエリログをテーブルとファイルに出力する
#general_log      = 1
#general_log_file = /var/log/mysql/query.log
#log_output       = FILE

# エラーログのタイムゾーンの指定
log_timestamps=SYSTEM

[mysqld_safe]
pid-file=/var/run/mysqld/mysqld.pido

 

上記のように[mysqld]の下に記述しないと反映されません。
（基本なんでしょうけれど・・・・(^^;）
私は、最終行に記述したので[mysqld_safe]の行下になったので反映されなかったようです。
ということでやっと反映されました。


mysql> SHOW VARIABLES LIKE 'query_cache_%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.51 sec)

 

キャッシュ有効直後のキャッシュ率等（まだ再起動してすぐなので数日の経過後確認したいと思います。）


mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 146      |
| Qcache_free_memory      | 15241168 |
| Qcache_hits             | 5403     |
| Qcache_inserts          | 2800     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 583      |
| Qcache_queries_in_cache | 401      |
| Qcache_total_blocks     | 1002     |
+-------------------------+----------+
8 rows in set (0.10 sec)

 

直後に確認しましたが意外と使われているようです。

Qcache_free_memory(キャッシュに使われていないメモリ量)の数値を見てFULL以外の値で数値が減ってなければquery_cache_size=16Mを減らしていけばいいようです。
また、 Qcache_lowmem_prunesの数値が高くなるようであればメモリ不足でキャッシュを削除された回数なのでメモリの使用方法を考慮しなければならないようです。

追記
mysqlのerrorログで以下のようなものがありました。

2017-06-22T07:12:09.168321Z 0 [Warning] The syntax ‘–log_warnings/-W’ is deprecated and will be removed in a future release. Please use ‘–log_error_verbosity’ instead.

 

日々の覚書のサイト様のところとマニュアルを見るとどうやら5.7.2からは、’–log_error_verbosity’を優先して使用するべきと記述があります。

As of MySQL 5.7.2, information items previously governed by log_warnings are governed by log_error_verbosity, which is preferred over, and should be used instead of, the older log_warnings system variable. (The log_warnings system variable and –log-warnings command-line option are deprecated and will be removed in a future MySQL release.)
MySQL 5.7.2以降、以前はlog_warningsによって管理されていた情報項目はlog_error_verbosityによって管理されていました。これは古いlog_warningsシステム変数より優先され、代わりに使用されるべきです。 （log_warningsシステム変数と–log-warningsコマンドラインオプションは廃止され、将来のMySQLリリースでは削除される予定です）。（google翻訳）

Items printed for log_warnings=1 or higher count as warnings and are printed for log_error_verbosity=2 or higher.
log_warnings=1は、log_error_verbosity=2と同じ仕様になるとのこと

以下のオプションでみると注意（notes）は、ログに出力されなくなってしまう。

Verbosity Value Message Types Logged
1 Errors only
2 Errors and warnings
3 Errors, warnings, and notes

ということでデフォルトの３の設定ですべてのログが出るようにするため置き換えてmy.cnfの設定を書き換えました。

#log-warnings = 1
#mysql5.7.2 ログ出力　下記のオプションに変更　デフォルトは3
log-error-verbosity=3

あとは、mysqldを再起動してOKです。

*****************************************************************************

ブログランキング・にほんブログ村へ
にほんブログ村

PVアクセスランキング にほんブログ村

カテゴリー: Linux, mysql タグ: ,