MySQL チューニング
投稿日時:2020/02/26
毎日定時に cron で動かしているプログラムの終了時間が徐々に遅くなり調べたところ MySQL が遅くなっているみたいなので設定回りを色々と弄った覚書
環境は
CentOS 6.10
MySQL 5.7.29
とりあえずログを確認
エラーは特に無し
メモリの設定はデフォルト値
InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
一応、MySQL でコマンド叩いて確認
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_%';バッファープールを増やすことで調整
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
『インスタンスの数はinnodb_buffer_pool_instancesで指定されています。
バッファープールは各インスタンスごとに、innodb_buffer_pool_chunk_sizeで指定したサイズ分、増減します。
バッファープールのサイズはinstances x chunk_size 単位でしか調整できません。』
ということなので my.cnf を
[mysqld]と変更
innodb_buffer_pool_size = 512M
innodb_buffer_pool_instances = 2
innodb_buffer_pool_chunk_size = 256M
再起動してログを確認すると
InnoDB: Adjusting innodb_buffer_pool_instances from 2 to 1 since innodb_buffer_pool_size is less than 1024 MiBと、インスタンスが 1 に変更されてる?
InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 256M
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_%';確かにインスタンスが 1 に変更されている
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 268435456 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 536870912 |
+-------------------------------------+----------------+
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0092
を確認すると
「innodb_buffer_pool_sizeに設定する値はinnodb_buffer_pool_instances×innodb_buffer_pool_chunk_size(デフォルト128MB)単位で設定します。
innodb_buffer_pool_instancesはサーバーのメモリが1GB以上の場合はデフォルトで8,1GB未満の場合は1になります。」
メモリ 8G 積んでるんだけど・・・
もしかして、innodb_buffer_pool_size が 1G ないといけないのだろうか?
[mysqld]再起動してログを確認すると
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 4
innodb_buffer_pool_chunk_size = 256M
InnoDB: Initializing buffer pool, total size = 1G, instances = 4, chunk size = 256M
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_%';うまくいったみたい
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 268435456 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 4 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 1073741824 |
+-------------------------------------+----------------+
で、cron 処理を回してみると改善が感じられず・・・。
こちら参考にデータのサイズとバッファのサイズを算出
https://qiita.com/h141dev/items/ae0bc872e0c2d8990738
・・・あれ? MyISAM がアウトだ
ストレートエンジンを確認してみる
mysql> use データベース名;InnoDB と MyISAM が混在していた
mysql> show table status;
こちら参考に MyISAM のバッファを 2G に変更
https://qiita.com/zaburo/items/65bac3b8e0a635ada68c
[mysqld]ついでにスレッドバッファも増やしておく
key_buffer_size = 2G
[mysqld]これでどうだ・・・
read_buffer_size = 1M
sort_buffer_size = 1M
read_rnd_buffer_size = 4M
劇的に改善はされてないけどなんだかちょっと早くなった気がする
しばらくこれで様子を見てみるとします
以下、参考サイト
https://qiita.com/mamy1326/items/9c5eaee3c986cff65a55
http://momota.github.io/blog/2017/04/20/mysql/
https://open.mixi.jp/user/7593622/diary/1972419761
https://qiita.com/mita2/items/8fd915164f0851c96e54
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0092
https://サーバー構築と設定.com/?p=1016
https://qiita.com/muran001/items/14f19959d4723ffc29cc
https://qiita.com/h141dev/items/ae0bc872e0c2d8990738
https://yohei-a.hatenablog.jp/entry/20180610/1528650004
https://qiita.com/jinnai73/items/e1bb8d7c82498175e311
以下、関連書籍
- 関連記事
-
- MySQL チューニング
- ダミーの個人情報データ
- レプリケーションの設定