MySQLバージョンアップ後のディスクI/O増加について（ver 5.1→5.6）
いつもお世話になっております。
Zabbix Web画面上の「最新データ」画面のレスポンス向上を期待して
MySQLをCentOS 6.5デフォルトの5.1から最新の5.6にアップデートを行いました。
（order by時のLIMIT句のパフォーマンス向上の恩恵を受けるため）
バージョンアップによって、パフォーマンスを向上することは叶ったのですが
以下の事象が発生しており、原因を突き止めたいと思い投稿させてもらいます。
□パージョンアップ後発生した事象
Zabbix ServerのDiskI/O(Writeのみ）が常に発生するようになった。
※vfs.dev.write[,sps,avg1]の値
バージョンアップ前：
通常時）10～100 sector 程（ほぼ皆無）
housekeeper動作時）100K sector
バージョンアップ後：
通常時）150K sector 程
housekeeper動作時）500K sector
バージョンアップ前後で、ホストやアイテム等は増やしておらず、
監視方法も変更していないため、MySQLバージョンアップにによる
デフォルトパラメータの変化によるものだと思われますが、
どのパラメータが起因しているのか分からず、ご助力いただけないでしょうか。
現状、これが原因でパフォーマンスが劣化しているとは感じられませんが
今後ホスト・アイテムともに数倍に増やしていく予定なため、抑えられえる
負荷は可能な限り抑えたいと思っております。
□環境
OS：CentOS 6.5
Zabbix：2.2.3
Zabbix 登録ホスト数：約300
Zabbix 登録アイテム数：約55000
Zabbix 登録トリガー数：約15000
Zabbix 1秒あたりの監視項目数：661
MySQL：5.1.71→5.6.17
MySQL Storage Engine：InnoDB
MySQL my.cnf：添付ファイルをご確認ください（その他はデフォルト設定です）
その他：Zabbixの以下履歴系のテーブルは、range(clock)によりpartition化しています（1partition/1日）
（history / history_uint / history_str / history_log / history_text / trends / trends_uint）
ご教授のほど、よろしくお願い致します。
- my.cnf_.txt (718 バイト)
TNK - 投稿数: 4768
パラメータ的には、特に問題はないように見受けられますが、
具体的に、どのような手順で5.1.71から5.6.17まであげられ
ましたか？
例えば、5.1から5.6直接のアップグレードは推奨されていない
ということも聞いたことがあるので、5.1 -> 5.5 -> 5.6と段階
を踏んでバージョンアップされたかと、mysql_upgradeなどを
どのタイミングで実行されたかとかも関係があるかもしれません。
とはいえ、何らかの障害が発生している可能性も考えられない
わけではないので、MySQLのログにも何らかのエラーや警告など
出力されていないかも確認してみてください。
TF0814 - 投稿数: 49
返信ありがとうございます。
5.1→5.6は推奨されてないのですね・・・確認不足でした・・・
（検証環境なので、安易に作業してしまいました）
手順は以下のように実施しました。
1.zabbix関連サービス停止
2.zabbix DBバックアップ
mysqldump -uroot zabbix > zabbix.dump
3.zabbix DB削除
drop database zabbix;
4.mysqld停止
5.yumリポジトリインストール
yum install http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
6.yum update mysql（5.1→5.6）
※アップデートしたもの
mysql-community-server
mysql-community-client
mysql-community-libs
mysql-community-libs-compat
mysql-community-common
mysql-connector-odbc
postfix
7.my.cnfの修正（最初の投稿の添付）
8.mysql起動
9.zabbix DBリストア
mysql -uroot zabbix < zabbix.dump
10.mysql_upgrade実行
11.zabbix起動
起動後mysqlのエラーは出力されておりません。
mysql一旦削除して、入れなおした方が良いでしょうか・・・。
TNK - 投稿数: 4768
監視項目数の大きな環境を別途用意することは困難であったため、
小さな環境を新規に作成して試してみましたが、桁が変わるほどの
vfs.dev.write[]の値の大きな増加は見られませんでした。
# 320sps → 420sps程度の変化
メモリなども小さなサーバしか用意できませんでしたので、ご提示
頂いたmy.cnfとはメモリ関連の設定が異なります。
あと、アップグレード手順として、以下のものが異なります。
・yum updateで更新されたのは以下のパッケージ
mysql-community-common-5.6.17-4.el6.i686
mysql-community-libs-5.6.17-4.el6.i686
mysql-community-client-5.6.17-4.el6.i686
mysql-community-server-5.6.17-4.el6.i686
mysql-community-libs-compat-5.6.17-4.el6.i686
・MySQLのパッケージを更新してmy.cnfを修正してmysqld起動後
すぐmysql_upgradeを実行とmysqld再起動。
・MySQL上にデータベースzabbixを作成してからdumpからのロード
を実行。
手順として大きく違うのは、Zabbixのデータベースのダンプを読み
込む前に、mysql_upgradeを実行しmysqldを再起動した点です。
mysqld起動時のログにもあった通り、各種管理テーブルの構造に問
題があるというエラーが出ていたと思いますので、Zabbixのテーブ
ルをロードする前に各種管理テーブルをアップグレードする手順を
優先して実行してみました。
最低限の環境であれば、MySQL 5.1から5.6に上げたからといって、
vfs.dev.write[]の値が極端に増加するわけではないようです。
規模による問題である可能性も考えられますが、DBのアクセス以外
にもMySQLに限らず、他のアプリケーションも合わせてログファイ
ルなどの出力が増加していないかもご確認ください。
TF0814 - 投稿数: 49
環境構築及びご確認頂きありがとうございます。
バージョンアップを実施しても、そこまで変化はなかったのですね。
やはり手順の問題・・・？
ただ、若干ですが増加しているのは少し気になりました。
（誤差程度かもしれませんが・・・）
あと、私も色々確認しています。
まず、write i/oの原因が本当にmysqlなのか確認しました。
dstatを使用して、1時間程確認したところ、やはりi/oの原因はmysqlのようです。
※添付txtをご確認ください。
また、vfs.dev.writeの推移を確認したところ、日を跨ぐと一旦writeが減少し
その後数時間をかけて150K sectorまで上昇するような挙動でした。
※添付画像をご確認ください。
日を跨ぐことに関連しそうなのは、partition化したテーブルの切り替わりが考えられます。
ということは、partition化に関連してwrite i/oが増加していると推察できます。
mysql5.6でデフォルトパラメータの設定値が結構変更されているようなので、
そのあたりを修正して変化が無いか確認してみよう思います。
TNK様で、何か思い当たる設定値等はありませんでしょうか・・・？
改善が見られない場合は、mysqlを入れなおす事も最終的には検討してみます。
TNK - 投稿数: 4768
ログファイルの出力状態はご確認頂けましたか？
mysqldプロセスがディスクへのI/Oを多く発生させていたとしても、
ご提示いただいたdstatの情報では、それがデータベースのデータ
としてのI/Oなのか、ログファイルへの出力なのかは不明だと思い
ます。
前回ご提示頂いた手順では、MySQL自体が正常に稼働するためのシ
ステムテーブルのアップグレード行う前にダンプデータからzabbix
データベースをロードされていたと思われるので、何らかのMySQL
上の管理データ上の問題が発生していることを危惧しています。
パラメータの変更に関してですが、5.5から5.6に関しては、以下の
資料が参考になるかもしれません。
参考資料：
MySQL 5.6 パラメータ 検討会
http://dbstudy.info/files/20130729/mysql56_parameters_r2.pdf
TF0814 - 投稿数: 49
ご回答ありがとうございます。
> ログファイルの出力状態はご確認頂けましたか？
申し訳ありません。記載するのを忘れておりました。
mysqlはbinログ等出力させていないので、特にi/oが増えるようなものは
ありませんでした。
通常のmysqlログも殆ど書き込みはありませんし、その他のアプリログ
（といってもほぼないのですが・・・）も出力が増えているようなものは
ありませんでした。
やはり、update手順の不備を疑うべきですかね・・・。
ご提示頂いた資料はすでに確認しております。
ioに関わりそうなパラメータ*はある程度調整してみましたが大きな変化は
ありませんでした。
*innodb_io_capacityやinnodb_max_dirty_pages_pctなど
mysqlの再インストールも検討しようと思います。
fripper - 投稿数: 495
デフォルト値の変化、Version毎の対応関係‥あたりまで把握しきれていませんが‥
innodb_checksums
innodb_doublewrite
innodb_file_format=Barracuda
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_max_dirty_pages_pct = 90
私は 5.5/5.6 あたりを使っていますが、それらのサーバで利用している my.cnf にて、
これらパラメータを調整しつつ使っています
innodb_doublewrite
や
innodb_flush_method
あたりのデフォルト値が変わっているのかもしれません‥
TF0814 - 投稿数: 49
ご回答ありがとうございます。
> innodb_max_dirty_pages_pct = 90
このパラメータですが、5.6ではデフォルトが75%になっており
もしかしてこれか！と思って修正してみたのですが、特に変化ありませんでした・・・
innodb_doublewriteや innodb_flush_methodのデフォルト値は
5.1と変わりなかったので、おそらく関係はないと思います。
5.1から比べると5.6は凄まじくパラメータ数が増加しており
どれが原因なのか特定が非常に難しそうです。
一旦、mysqlの再インストールを行って、まずupdateの不備なのかパラメータの
問題なのか切り分けをしようと思います。
TF0814 - 投稿数: 49
MySQLを一旦アンインストールし、再度インストールを行ってみましたが
Write I/Oに変化はありませんでした。
やはり、手順の問題ではなさそうです。
再インストールした環境で、MySQLのshow statusの"innodb_buffer_pool"系の値を
確認していたところ、MySQL再起動後から2回目のhousekeeperが動作した直後から
dirty pageのフラッシュが常に行われているような状態が見て取れました。
innodb_buffer_pool_page_dirtyの値が2000程度になると、即時にフラッシュされ
値が0になり、innodb_buffer_pool_page_dirtyの値が2000程度→フラッシュ・・・
という挙動がずっと繰り返されているようです。
innodb_max_dirty_pages_pctの値は90に設定しており、buffer poolにある程度
データが溜まらないとdiskへのフラッシュはされないものだと思っていたのですが、
dirty pageのフラッシュ間隔？に関するその他の設定パラメータ等が関係しているのでしょうか？