うちの会社でも、IO-Drive2やSSDを使用したサーバが増えてきた。
ディスク性能が良いサーバがあっても、MySQL側がボトルネックになっていたら宝の持ち腐れになってしまう。そこで、MySQLで制限されている一つにinnodb_io_capacityというものがあるのでこの設定について考えてみたいと思う。
そもそもMySQLがディスクI/Oを必要とする処理はどのようなものがあるか考えてみよう。
・キャッシュされていないDBデータへのアクセスが発生した時。
・更新されたダーティページがディスクへフラッシュする時。
・エラーログが出力される時。
・バイナリログが出力される時。
・スロークエリログが出力される時。
・トランザクションログが出力される時。
・表やDBの定義や作成等でテーブルスペースの更新が発生した時。
他にもあると思うが、I/O処理にはフォアグランド、バックグラウンドの2種類が発生する。
このinnodb_io_capacityを設定する時に考慮が必要なのは、MySQLのバックグラウンド処理で使用するディスクI/Oにこの設定値が影響を受けるということ。これはダーティページのフラッシュ、挿入バッファのマージ等MySQLのバックグラウンド処理に対するI/Oになる。
フォアグランド処理もある程度ディスクI/Oを使用するためinnodb_io_capacityをディスク性能カタログ値の性能指標全てを割り当てるは避けた方が無難と考えている。ただし、どの程度フォアグランド処理で使用しているかはシステムで異なるため、これが正しいという値は無いと思う。ましてや求め方もとても難しいと思うので、性能試験、運用時にiostatの値を定期的に取得しどの程度のI/Oが普段発生しているかは確認し妥当性を確認するが無難。
$ iostat -x 1 1000
等で出力されるr/sとw/sを確認し、必要と思うIOPS値を見極めてみる。
とはいえ、それじゃ設定値がわからないじゃん!となってしまうため、ある程度勝手ながら私個人の指標は下記で考えています。
デフォルト値:200
RAID:500 ~ 1000
SSD:2000 ~ 5000
IO-Drive2:10000 ~ 50000
※RAIDはSAS、SATA両方とも同じ指標で考えてます。
※ディスクI/O性能もファイルシステムのブロックサイズ毎で微妙に性能も異なっているのでカタログ情報を見るときにはこのあたりも気にかけてあげよう。
尚、innodb_io_capacityとは別にMySQL5.6からはinnodb_io_capacity_maxという値も存在している。デフォルト値は2000となっている。
mysql> show global variables like 'innodb_io_capacity%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
+------------------------+-------+
2 rows in set (0.00 sec)
mysql>
この設定値は注意をしないと、innodb_io_capacity の設定が正しく設定されない。
例えば、innodb_io_capacityで3000の値を設定した場合、innodb_io_capacity_maxが2000なので、2000に自動的に設定されてしまう。
---------------------------
mysql> set global innodb_io_capacity = 3000;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql>
mysql> show global variables like 'innodb_io_capacity%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 2000 |
| innodb_io_capacity_max | 2000 |
+------------------------+-------+
2 rows in set (0.00 sec)
mysql>
---------------------------
そのため、先にinnodb_io_capacity_maxの値を変更しておく必要がある。
なので、innodb_io_capacityを設定するときは下記の流れが良いと思う。
①設定する値を決める。
②innodb_io_capacity_max値が①で決めた値を超えている場合は、先にinnodb_io_capacity_maxをそれと同じ値、もしくはそれ以上の値を設定する。
③innodb_io_capacityの設定を行う。
5.6になっても、200という数字はかなり小さい値であるため、システムに合わせて必要な値を設定してみよう!
ディスク性能が良いサーバがあっても、MySQL側がボトルネックになっていたら宝の持ち腐れになってしまう。そこで、MySQLで制限されている一つにinnodb_io_capacityというものがあるのでこの設定について考えてみたいと思う。
そもそもMySQLがディスクI/Oを必要とする処理はどのようなものがあるか考えてみよう。
・キャッシュされていないDBデータへのアクセスが発生した時。
・更新されたダーティページがディスクへフラッシュする時。
・エラーログが出力される時。
・バイナリログが出力される時。
・スロークエリログが出力される時。
・トランザクションログが出力される時。
・表やDBの定義や作成等でテーブルスペースの更新が発生した時。
他にもあると思うが、I/O処理にはフォアグランド、バックグラウンドの2種類が発生する。
このinnodb_io_capacityを設定する時に考慮が必要なのは、MySQLのバックグラウンド処理で使用するディスクI/Oにこの設定値が影響を受けるということ。これはダーティページのフラッシュ、挿入バッファのマージ等MySQLのバックグラウンド処理に対するI/Oになる。
フォアグランド処理もある程度ディスクI/Oを使用するためinnodb_io_capacityをディスク性能カタログ値の性能指標全てを割り当てるは避けた方が無難と考えている。ただし、どの程度フォアグランド処理で使用しているかはシステムで異なるため、これが正しいという値は無いと思う。ましてや求め方もとても難しいと思うので、性能試験、運用時にiostatの値を定期的に取得しどの程度のI/Oが普段発生しているかは確認し妥当性を確認するが無難。
$ iostat -x 1 1000
等で出力されるr/sとw/sを確認し、必要と思うIOPS値を見極めてみる。
とはいえ、それじゃ設定値がわからないじゃん!となってしまうため、ある程度勝手ながら私個人の指標は下記で考えています。
デフォルト値:200
RAID:500 ~ 1000
SSD:2000 ~ 5000
IO-Drive2:10000 ~ 50000
※RAIDはSAS、SATA両方とも同じ指標で考えてます。
※ディスクI/O性能もファイルシステムのブロックサイズ毎で微妙に性能も異なっているのでカタログ情報を見るときにはこのあたりも気にかけてあげよう。
尚、innodb_io_capacityとは別にMySQL5.6からはinnodb_io_capacity_maxという値も存在している。デフォルト値は2000となっている。
mysql> show global variables like 'innodb_io_capacity%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
+------------------------+-------+
2 rows in set (0.00 sec)
mysql>
この設定値は注意をしないと、innodb_io_capacity の設定が正しく設定されない。
例えば、innodb_io_capacityで3000の値を設定した場合、innodb_io_capacity_maxが2000なので、2000に自動的に設定されてしまう。
---------------------------
mysql> set global innodb_io_capacity = 3000;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql>
mysql> show global variables like 'innodb_io_capacity%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 2000 |
| innodb_io_capacity_max | 2000 |
+------------------------+-------+
2 rows in set (0.00 sec)
mysql>
---------------------------
そのため、先にinnodb_io_capacity_maxの値を変更しておく必要がある。
なので、innodb_io_capacityを設定するときは下記の流れが良いと思う。
①設定する値を決める。
②innodb_io_capacity_max値が①で決めた値を超えている場合は、先にinnodb_io_capacity_maxをそれと同じ値、もしくはそれ以上の値を設定する。
③innodb_io_capacityの設定を行う。
5.6になっても、200という数字はかなり小さい値であるため、システムに合わせて必要な値を設定してみよう!