mysql チューニング方法と設定確認
mysqlのメモリ量を抑えるためにまた少しだけチューニングしてみようかと色々と確認しました。
まず、現在のmysqlのメモリ量の確認です。
[root@www16071ue etc]# ps alx | grep mysql | awk '{printf ("%d\t%s\n", $8,$13)}'
2156 mysql
872 grep
124 /bin/sh
84340 /usr/sbin/mysqld
/usr/sbin/mysqldのところですが約８４Ｍ使用中です。
ここでメモリ消費を抑えるために有効な設定があるそうでこちらのブログ様 メモリ食い過ぎてたのは、mysqlの設定が原因だった – Qiita を参考にさせていただきました。
mysql> show variables like 'table_definition_cache';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| table_definition_cache | 1400 |
+------------------------+-------+
1 row in set (0.00 sec)
当該サーバも同じようにデフォルトの1400です。
https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_table_definition_cache
上記のマニュアルを見るとデフォルトは、400と書かれているのですが、
mysql5.7になると英語では、そのような記述は書いてませんでした。
Mysql 5.7 マニュアル table_open_cache
なのでここは、そのままにしました。もし5.68より以前ならば下記のような設定がいいようです。
まず、my.cnfの設定
[mysqld]
#テーブル定義のみキャッシュする
table_definition_cache=400
設定終了後mysqlサーバ再起動
[root@www16071ue etc]# /etc/init.d/mysqld restart
mysqld を停止中: [ OK ]
mysqld を起動中: [ OK ]
設定確認
mysql> show variables like 'table_definition_cache';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| table_definition_cache | 400 |
+------------------------+-------+
1 row in set (0.00 sec)
上記までがmysql5.6.8以前のチューニング設定です。
当方の5.7のバージョンでも一度設定変更しました。その際、使用メモリも若干減りダイエット化に成功しました。
また、サーバ上、特に不備は見当たりませんでしたがマニュアルを読み返して
上記のような理由がありデフォルト値（1400）に再度変更しました。
ここからは、私のサーバ（mysql5.7）のチューニングの確認
[root@www16071ue MySQLTuner-perl-master]# ./mysqltuner.pl -user root -pass パスワード
>> MySQLTuner 1.7.2 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.7.18-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 22M (Tables: 26)
[--] Data in InnoDB tables: 106M (Tables: 42)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 8d 15h 50m 27s (3M q [5.098 qps], 44K conn, TX: 20G, RX: 413M)
[--] Reads / Writes: 79% / 21%
[--] Binary logging is disabled
[--] Physical Memory : 996.0M
[--] Max MySQL memory : 345.9M
[--] Other process memory: 560.2M
[--] Total buffers: 176.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 190.6M (19.14% of installed RAM)
[OK] Maximum possible memory usage: 345.9M (34.72% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (269/3M)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Aborted connections: 0.06% (29/44688)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 66.4% (2M cached / 3M selects)
[!!] Query cache prunes per day: 42529
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 254K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 13% (27K on disk / 206K total)
[OK] Thread cache hit rate: 99% (37 created / 44K connections)
[!!] Table cache hit rate: 15% (1K open / 12K opened)
[OK] Open file limit used: 10% (509/5K)
[OK] Table locks acquired immediately: 99% (984K immediate / 984K locks)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 40.6% (3M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/6.3M
[OK] Read Key buffer hit rate: 100.0% (20M cached / 2K reads)
[!!] Write Key buffer hit rate: 93.5% (57K cached / 53K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/106.5M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (24618970420 hits/ 24618972615 total)
[!!] InnoDB Write Log efficiency: 2.14% (458 hits/ 21436 total)
[OK] InnoDB log waits: 0.00% (0 waits / 20978 writes)
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (5000) variable
should be greater than table_open_cache (2000)
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 8M)
table_open_cache (> 2000)
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.
途中省略して記述してます。
以前のバージョンアップ前と比べると大分改善したようですが、まだOKが出ませんね。
最後の——– Recommendations —–のところを改善していきます。
（１）Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (5000) variable
should be greater than table_open_cache (2000)
ファイル記述子の制限を避けるためにtable_open_cacheを徐々に増やしてください
64を超えてtable_open_cacheを増やす前にこれを読んでください：http://bit.ly/1mi7c4C
そのopen_files_limit（5000）変数に注意してください
table_open_cache（2000）より大きい必要があります。(by google翻訳)
上記サイトを確認するとどうやら、table_open_cacheを大きくしすぎるとバグなのかあまりよくないということが書いてあります。
現在の設定確認
mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 2000 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'table_definition_cache';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| table_definition_cache | 1400 |
+------------------------+-------+
1 row in set (0.00 sec)
デフォルトのままで table_open_cacheは、2000、先ほどの設定にあるtable_definition_cacheは、1400になってます。
とりあえずtable_open_cache=2000になっているのでこのまま問題ないのかなと思い今のところ処置なし。
（２）query_cache_size (> 8M)とあるもクエリキャッシュサイズの空き容量は、Qcache_free_memory = 4677680
となっており十分にまだ余裕があるのでこのままでいいのかなと思います。こちらも処置なしです。
一応クエリキャッシュヒット率は、下記で計算できるようです
Qcacheキャッシュヒット率 = Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) * 100 [%]
下記から算出すると
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 611 |
| Qcache_free_memory | 4677680 |
| Qcache_hits | 2094409 |
| Qcache_inserts | 894015 |
| Qcache_lowmem_prunes | 368311 |
| Qcache_not_cached | 163413 |
| Qcache_queries_in_cache | 1270 |
| Qcache_total_blocks | 3270 |
+-------------------------+---------+
8 rows in set (0.01 sec)
ヒット率は、20%を下回る場合は、使用しないほうがいいようですが今のところ６６％超なので大丈夫そうです。
（３）３つ目のものですがログサイズの件らしいいです。要は、オラクルでいうREDOログ（作業履歴ログ）なので結構大事なログですね。このログをメモリ上に持っているサイズになるようです。下記の公式が当てはまるように設定するのがいいようです。
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.
可能であれば、innodb_log_file_size * innodb_log_files_in_groupは、バッファプールサイズ（= 64M）の1/4に等しくなければなりません。（by google翻訳）
現在のリファレンスマニュアルとデフォルト設定を確認すると
MYSQL5.7 リファレンスマニュアルによるとinnodb_buffer_pool_sizeは、128MBとのこと
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
上記のようにデフォルトです。
では、innodb_log_file_sizeを確認します。
mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name | Value |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
1 row in set (0.01 sec)
48MBでこちらもデフォルト値です。
code>mysql> show variables like 'innodb_log_files_in_group'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | innodb_log_files_in_group | 2 | +---------------------------+-------+ 1 row in set (0.00 sec)
innodb_log_files_in_groupについてもデフォルトの２となってます。
これで計算すると
innodb_log_file_size(48M) * innodb_log_files_in_group（2） = 96M
——– InnoDB Metrics ————-の項目の下記
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
これの意味として
Buffer pool sizeの25%がいいようですが現在は、75%を使用しているのでInnoDB Buffer pool sizeを容量アップもしくは、InnoDB log file sizeを減らす方法がありますが
リファレンスマニュアル（innodb-buffer-poolの項目）によると
理想的には、バッファープールのサイズをできるだけ大きな値に設定して、サーバー上のほかのプロセスが過剰なページングなく実行するように、十分なメモリーを残します。バッファープールが大きいほど、InnoDB はさらにインメモリーデータベースのように動作し、ディスクから 1 回データを読み取り、後続の読み取り時に、メモリーからデータにアクセスします。パフォーマンス向上のため、ディスク書き込みをグループ化できるように、・・・・・＜mysql5.6 リファレンスマニュアル 抜粋＞
となっておりなるべく多くのサイズをプールすることが望ましいいそうですが、メモリも潤沢にあるわけではなくまた、この記述としては、純粋なデータベースサーバとしての話と思います。
現在の運用上では、InnoDB log file sizeを少なくするのがいいようですが、現在の状況的でログサイズを抑えた場合は、ログファイル量（バッファ上）への書き込み多くなると当然I/Oディスクへの書き込みが多くなる弊害があると考えます。
現段階では、そのまま現状維持で行く方針が最善かと思います。もし、SLOWログ等が頻繁するようであれば、別途InnoDB Buffer pool sizeの容量アップなどのチューニングが必要になるかと思います。
ということで現在の設定方針などを調べてみました。現在のところチューニングには、至っていません。
*****************************************************************************
