しがないエンジニアのつぶやき

某IT企業でシステムエンジニアをしてます。 メイン担当はデータベースです。 色々な技術情報をメモしていこうと思っています。

カテゴリ: MySQL

うちの会社でも、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_config_editorでパスワード情報を配布しないで済むように定義してみたいと思います。
ちなみにこの設定は、5.6からの新機能なので、5.5以前は使えないのでご注意ください。

①定義したいOSユーザにスイッチする。
$ su - user01
$ id
user01

②mysql_config_editorで定義をする。
OSユーザ:user01
MySQLでログインするユーザ:user01
ログインのパス名称:user01
※ログインのパス名称は任意の名前でなんでもOKですね。

$ /usr/bin/mysql_config_editor set --login-path=user01 --user=user01 --password

③定義を確認
$  mysql_config_editor print --all
[user01]
user = user01
password = *****
※設定がされてるね!

④実際にログインをしてみる。
$ mysql --login-path=user01
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.6.11-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
⇒いい感じにログインができるね!

ちなみに・・・複数の定義を設定することも可能ですね。
例えば下記な感じの設定を追加したいとする。

OSユーザ:user01
MySQLでログインするユーザ:user02
ログインのパス名称:user02

$ id
user01
$ /usr/bin/mysql_config_editor set --login-path=user02 --user=user02 --password
$ mysql_config_editor print --all
[user01]
user = user01
password = *****
[user02]
user = user02
password = *****
⇒二つの定義が入ってるね。

$ mysql --login-path=user01
$ mysql --login-path=user02
⇒どっちでもログインできますね。

ちなみにこの定義ファイルは~/.mylogin.cnfに作成されます。
注意点として、OSの所有者と権限を気にする必要があります。
デフォルトでは下記の権限が付与されています。
-rw------- 1 user01 user01 176  2月 15 17:09 2014 /home/user01/.mylogin.cnf

まぁ、デフォルトから変更をしなくてもいいのですが、例えば別のOSユーザにuser02というのがいて
MySQLのログインユーザはuser01を使うという場合は、OSユーザ:user01の.mylogin.cnfを
OSユーザ:user02へコピーし、所有者を変更するだけで使えます。
もし、設定変更をしたくないという場合は書き込み権限は除外してもいいと思います。

# cp /home/user01/.mylogin.cnf /home/user02/.mylogin.cnf
# chown user02:user02 /home/user02/.mylogin.cnf
⇒これをするだけで、user02もuser01と同じ条件でログインが可能となります。

あと、↑の権限変更で言いたかったのはグループやその他に読み込み権限を付与しただけでも使用できなくなります。仮に付与してログインをすると下記のエラーがでてログインできなくなります。
-----
Warning: /home/user01/.mylogin.cnf should be readable/writable only by current user.
-----

ちなみに設定をリセットしたいときは下記のコマンドでリセットできますね。

$ mysql_config_editor reset
$ mysql_config_editor print --all
⇒何も出力されない。

この設定は結構使えると思っています。デメリットもあんまりないと思いますのでパスワードは配布したくないけど、ログインはしてもらう必要があるというときに有効ですね。

mysqldumpコマンドで、--no-dataのオプションを使用することで、該当データベースの表定義だけを取得することができるよ!

こんな感じ。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test      |
| mysql              |
| performance_schema |
+--------------------+

testデータベースの表定義をぬきだそう!

$ mysqldump -u root -p --no-data test > /tmp/test.sql

testデータベースの表のみを取得。
これを適用する場合はこんな感じ。

$ mysql -u root -p
mysql > create database test;
mysql > use test;
mysql > source /tmp/test.sql

sourceでtestデータベースの表を読み込んだらOK.
ちなみに、この場合はデータベースのdropやcreateは含まれないので、手動で作成等が必要になります。

MySQLで準同期レプリケーション組んで、複数のスレーブDBを複製するためたまたま仮想サーバだったのでクローンして数台増やしたところ、下記のメッセージがマスター側で大量に出力されてた。

------- 2013-08-23 22:17:51 5705 [Note] Stop asynchronous binlog_dump to slave (server_id: 2199) 2013-08-23 22:17:51 5705 [Note] Start asynchronous binlog_dump to slave (server_id: 2199), pos(mysql-bin.000015, 120) 2013-08-23 22:17:51 5705 [Note] Stop semi-sync binlog_dump to slave (server_id: 2103) 2013-08-23 22:17:51 5705 [Note] Start semi-sync binlog_dump to slave (server_id: 2103), pos(mysql-bin.000015, 120) ------- 上記メッセージが1秒に30行以上出続ける。やばいよ、すぐにエラーログがいっぱいに。。
一応マスタDBでエラーログの監視はしてたが、ステータスがNoteなので検知できず。。

で、本エラーが出る原因は主に2つであることが調査してすぐにわかりました。 1.server-idが被った場合。 2.uuidが被った場合。
server-idはmy.cnfでserver-idパラメータを全サーバで分けてるのでここは影響をうけないはず!
では、uuidか?

複数のスレーブDBのuuidを確認。

・スレーブDB 1台目

mysql> show variables like 'server_uuid'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 17e7fe43-0bbf-11e3-8f75-df9b683ae8de | +---------------+--------------------------------------+

・スレーブDB 2台目

mysql> show variables like 'server_uuid'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 17e7fe43-0bbf-11e3-8f75-df9b683ae8de | +---------------+--------------------------------------+
いっしょじゃん!
これが原因で出力されていることが分かった。
結局のところdatadir配下で自動作成されるauto.cnfが原因であることが分かった。
クローンで作ったサーバだから削除を忘れてたよ・・。

なので、auto.cnfを削除してMySQLを再起動したところuuidが自動で割り当てられて、マスタ側の大量のログ出力がストップした!

ちなみにauto.cnfの中身はこんな感じ。
[auto]
server-uuid=17e7fe43-0bbf-11e3-8f75-df9b683ae8de

う~ん、MySQL5.6の機能GTIDでuuidを割り当てる必要があるために自動で作成されるファイルのようだが、不要なファイルは削除しないとトラブルの種だね。

↑このページのトップヘ