MySQL 5.5の秘伝のタレが5.6では腐っていたはなし

もう寒の入りを過ぎましたね。DBAのたなかです。

GAからもうすぐ1年、社内ではもう相当カジュアルにMySQL 5.6をインストールしています。今までは新規サービス(や、新規機能)での導入がほとんどだった5.6を、このたびトラフィックガンガンのサービスにアップグレードで導入しました（と、偉そうに言っていますが私でない別のDBA氏が主担当のサービスです）

主な理由はInnoDB Compressedを使っていたのでその性能アップに期待…というところだったんですが、弊社DBAが神代の時代より試行錯誤を重ねたどり着いた究極のmy.cnf(?)、いわゆる秘伝のタレが

　残　念　な　が　ら　腐　っ　て　お　り

夜を徹してアップグレード作業をしていた担当DBA氏が青い顔(推定。チャットだった)で

　ス　ロ　ー　ク　エ　リ　ー　が　1　0　倍　く　ら　い　に　な　っ　た　ん　だ　け　ど　…

と訴え、彼はそのままノロウイルスにやられて12月の3連休が6連休に。担当DBA氏も倒れ、しかも5.6へのアップグレードを勧めたのが俺という極めてわかりやすい罪悪感にさいなまれつつ、今度はMySQL 5.6との3日間戦争を始めることになったのでした。

先に結論。

バックグラウンドI/Oがタイトで innodb_io_capacity を設定している場合、innodb_io_capacity_max もチェックする。

innodb_io_capacityのパラメーターは5.1のInnoDB Pluginからあります。このパラメーターの意味自体は変わっていないのですが、5.6では innodb_io_capacity_max という ~~お節介な~~ パラメーターが追加されており、

The limit up to which InnoDB is allowed to extend the innodb_io_capacity setting in case of emergency. Its default value is twice the default value of innodb_io_capacity, with a lower limit of 2000.

どんな時がemergencyだよと思いつつ、本当の頭打ちはinnodb_io_capacity_maxによってもたらされそうです。暗黙のデフォルトは(mysqld起動時の)innodb_io_capacityの設定値の2倍。5.5にはこのパラメーターは存在しないので、5.5までの秘伝のタレをそのまま5.6に使うと、最悪の場合(何を以て判断しているか知らないけれど、"case of emergency"に合致する場合)、innodb_io_capacityの2倍の値までバックグラウンドスレッドがIOPSを使おうとすることになります。

SSD+ キャッシュつきRAIDコントローラーな環境でinnodb_io_capacityの値はアップグレード前から割と高めで結構カツカツ(時間帯によってはたぶん高すぎる)なところに設定してあり、しかもUPDATEとREPLACEのステートメントがゴリゴリゴリゴリゴリゴリゴリゴリ入ってくるワークロードだったため、バックグラウンド処理でも容赦なくPending aio write大杉って状態になっていました。むしろそっちがemergencyです本当にありがとうございました。

innodb_io_capacity_maxを下げてやることでぼちぼち解消した訳ですが、最初はスローログに上がったクエリーが"SELECT .. WHERE col IN (.., .., ..)“の形ばっかりだったので、「あーこれ松信さんが言ってたやつだ」と思いパラメーターを変更しようと思ったんですが、

　暗黙のデフォルトが10ってことしかそのパラメーターについて憶えてない

という素敵な事実に直面したり（このときはTwitterで助けてもらいました）、SET GLOBAL innodb_io_capacity_max= ..で再設定して回っている間に、何故かinnodb_io_capacity_maxが2^ 64- 1(つまり設定上の最大値)に設定されてたりと色々手こずりましたが、SHOW ENGINE INNODB STATUSを見ていて楽しかったのでまあ良しとしたいと思います。

秘伝のタレはメジャーバージョンをまたぐと危険ですよ！