ムック「データベース徹底攻略」 - MySQL/Redis/MongoDB/Redshift

最近発売された技術評論社のムック「データベース徹底攻略」に寄稿しました。

この本は、データベースのための本ということで、データベース設計、SQL、MySQL、Redis、MongoDB、Redshiftという代表的な要素技術についてのまとめとなっています。各プロダクト(MySQL、Redis、MongoDB、Redshift)については、現場で実際に本格的に使われている方々による記事なので大いに参考になると思います。

私は冒頭のまとめ記事を寄稿しました。詳細はぜひお手に取って読んでくださればと思います。ここでも自分が各技術を現時点でどのようにとらえているか、本ではいささか書きづらい内容について、最近流行りの言葉でもある「技術的負債」という観点も踏まえて書いておこうと思います。

・MySQL (RDBMS)
　私はMySQLの中の人でもありましたし、これまで至るところで話してきたので省略します。Facebookでの使い方に興味のある方は、Percona Live等で私をつかまえてくださればと思います。

・Redis (NoSQL)
　従来型RDBMSで扱いづらいデータ構造であるSortedSetsやListが使えるのが魅力のデータベースです。このためだけにソーシャルゲームのリアルタイム・ランキングや、動画の最新100件とかいった用途に採用しているケースもあります。正となるデータをRDBMSに、キャッシュ的に使うデータ(壊れてもRDBMSから復元可能なデータ)をRedisで、というパターンが多いです。耐障害性に難がありますが、キャッシュとして使う限りは許容範囲内だと思います。

・MongoDB (NoSQL)
　データベース設計ができない/できなかった/する時間が取れない、といった人たちや、Shardingのためのロジックを書くのがめんどくさい、といった人たちを中心に好まれるデータベースです。Facebookでも、子会社の中にMongoDBを使っているところがあります。
　MongoDBを使うか、RDBMSを使うかという議論は、よく「先に楽をして後で苦労するか、先に苦労して後で楽をするか」という表現にたとえられます。「技術的負債」を語る上で良いモデルケースと言えるでしょう。
　稼働までなら、MongoDBの方が、正規化が必須なRDBMSより楽です。その一方で、設計を放棄したツケは、後々になってデータ整合性問題、(重複値の多発による)データ量増加、それによるコスト増といった様々な負債となって現れます。裏の実装はMyISAMと大して変わらないので、データ量がメモリに乗らないくらいの規模になってくると、InnoDBよりも急激なペースで参照・更新性能とも悪化します。典型的な「技術的負債」のパターンと言えるのではないでしょうか。コスト増のことを「技術的負債」と言うのか? という意見もあると思いますが、そのコストの代償として間接的に給与が削減されたり、良いエンジニアを取りづらかったりといった不利益を受ける可能性がありますし、そのコストを減らすためには後々技術的な取り組みが必要になることから、「技術的負債」と言って良いのではないかと思います。ここではこれらの技術的・時間的・金銭的な不利益をまとめて「技術的負債」と呼ぶことにします。

　私が考えるMongoDBの魅力は、多くの場合に、こうした技術的負債を返済しなくて済むということです。負債が顕在化するのは、そのサービスが大きく成長した場合のみです。一般的なサービスの成功確率の低さを考えると、たいていのケースでは負債は返さなくて済みます。これは非常に魅力的なポイントです。競争に勝つためには、早期にサービスを出すことはとても大切で、様々な課題を先送りできるMongoDBがスタートアップを中心に好まれるのはある意味当然とも言えます。そもそも妥当な設計には正規化を中心としたデータベース基礎理論の理解が必要で、それに詳しい人をスタートアップで確保することは多くの場合難しいという事情も無視できません。サービスがうまくいったら、それなりにお金もあるはずなので、データベース設計に強い人を雇用することも難しくなくなってくるでしょう。

　ちなみに、元MySQL社の営業チームのメンバーの中にはMongoDB社に転職した人も何人かいます。上述のように導入時の優位性があるので、MongoDBの営業は「RDBMSよりも速くサービスを作り稼働させることができる」「シャーディングやフェイルオーバーなどの作り込みがいらないので実装コストが低い」などといった売り込みができます。営業は売ったもの勝ちなので、後々技術的負債がどうなるかを考えてくれたりはしません。それどころか、知り合いの賢いMongoDBの営業は、負債の顕在化によって後でサーバ台数がたくさん必要になったり、自動シャーディングやフェイルオーバーが期待通りに動かないことで、お客さんが困ってサポートをたくさん買ってくれるということまで見据えていたりします。怖いですね。
　なお、営業トークの1つである「RDBMSはスキーマ変更が大変」というのは、今の時代それほど重要では無いということを補足しておきます。MySQL5.6(InnoDB)ではオンラインDDLができますし、それ以前のバージョンでも無停止での変更手段はいくらでもあります。どちらかというと自動シャーディングや自動フェイルオーバーの方がセールストークとしては強力でしょう。

　一方でMongoDBを使う側としては、万一サービスがヒットした場合に必ず顕在化するであろう技術的負債について、どう取り組むかは考えておいた方が良いでしょう。現実解としては実績のある他RDBMSへの移行ですが、止めたときにユーザに直接影響が出る用途で使うことが多いでしょうから、移行するのはなかなか大変です。数少ない大規模リニューアル時やメンテナンス時などのタイミングで移すことになるでしょう。
　北米では、「規模が大きくなりすぎたMongoDBベースのWebサイトを、他のRDBMSに移行する」という案件も実際に存在するのですが、MongoDBを最初に導入した人はその時点では退職していたりします。MongoDB社、導入を進めた人、移植先のRDBMSベンダー、移植を進める人たち全員に仕事があるという、経済(GDP)に優しいデータベースとも言えるでしょうか?
　RDBMSとMongoDBのどちらが良いかについて一言でまとめるなら、「サービスが失敗したらMongoDBの勝ち、成功したらRDBMSの勝ち」というのが私の考えです。


・Redshift (ビッグデータ)
　PostgreSQLベースのDWH運用サービスで、AWS上で使うことができます。列指向、圧縮、複数ノード並列処理などひととおりの重要機構を備えています。Facebookでも、子会社の中にRedshiftを使っているところがあります。
　Redshiftは、特にコストパフォーマンスの観点で非常に優れています。2TBあたり年間数十万円で使えるという低価格は、1つのイノベーションと言えると思います。登場し始めた頃は、1週間で100社のペースで顧客が増えていたそうで、これを使うためだけにAWSに移行したという話も聞いたことがあります。機能の追加も良いペースで行われています。HDDベースとSSDベースがあり、価格と性能のトレードオフを踏まえた上で選べば良いでしょう。

　ビッグデータを扱う環境では、技術的負債という観点において、上に書いたMongoDBとは状況が異なります。すでにたくさんデータがあるということは、サービスとしてはそこそこ当たっている状況のはずです(そもそもデータが数10GBとかしか無ければ、RDBMSで十分です)。この先サービスが収束して終了する可能性はほぼ無いと考えられます。多くのスタートアップがMongoDBを使う背景にある「サービスが早期終了すれば負債は残らない」という予測は、DWHを必要とするほどの環境になると期待できません。負債は必ず顕在化します。必ず顕在化するのであれば、そうならないように事前に手を打っておくことはとても重要です。
　正規化などを含めた様々なことを先送りして、Redshiftのかわりに他の手段を採用するとした場合、その代償について慎重に吟味する必要があります。特に容量単価をはじめとしたコスト面で割高な場合は、データ量が増えれば増えるほどコスト差が広がるのでより深刻です。サービスが存続する限り、拡大するコスト差を負担し続けなければならず、それを改善するためには、データの削除や保持期間の縮小、後からRedshiftに移行する、といった手間をかけなければいけないからです。これも「技術的負債」の典型的なケースと言えます。
　このような点から、私はビッグデータに関しては、後で苦労するくらいなら先に苦労した方が良いという、技術的負債を先送りしないアプローチを取りたいと考えます。現時点での私の大まかな感覚では、これから3年間使うとして、そのトータルコストが4TB(圧縮後)あたり600万円～900万円(3年の累計)、10TBあたり1000万円～1500万円のレンジを超えてくるようなら、すでに技術的負債と言える(=より低コストな手段を模索すべき)と思います。このレンジは時間の経過とともにもっと下がるのは確実です。
　AWSでビッグデータを扱うのであれば「Redshiftを使いこなせれば勝ち、そうでなければ負け」というのが私の考えです。幸いDWH環境の場合、メインのWebサービスに比べて可用性や安定性に対する要求が低く(利用者が社員など一部に限定されるので)、止めて移行することは現実的に可能です。だからこそ過剰投資を防ぐ余地があります。すでに高コストな選択肢を選んでしまったとしても、手遅れではありません。稼働後もいかに(Redshiftなどで)トータルコストを下げる(技術的負債を減らす)かを模索していくと良いのではないかと思います。こうした低コスト化の推進の効果を、エンジニアたちの所得アップに還元してくれる会社がたくさん出てくると良いなと思います。

　Redshiftを見ていると、商用RDBMSの世界を徐々にMySQLが侵食していった頃のことを思い出します。当然ながら商用DWHベンダーにとって、低価格なRedshiftの登場は面白く無いわけです。「MySQLはXXがダメだ、だからYY(商用RDBMSの名前)を使いましょう」とアピールしていくことが、(より高い)商用RDBMSベンダーや、そのパートナー企業の人たちの仕事でした。ユーザにとってベストなのかどうかは関係ありません。ベンダーにとってベストなことをするのがベンダーの論理です。MySQLは最もコスト競争力が高いRDBMSだったため、歴史的にずっとこの攻撃を受ける側でした。同じように、商用DWHベンダーは、例えRedshiftで実現可能なことであっても、さも実現できないかのようにアピールし、自社の(より高い)DWH製品を勧めていくという戦略を取ります。ユーザーのためになっていないですが、それが商用DWHベンダーの人たちの仕事なのです。読者の皆さんには、こうしたネガティブマーケティングに惑わされないようにしてほしいと思います。

米国への引越とFacebook入社のご報告

件名の通り、私は米Facebookに最近入社したことをここでご報告します。

相変わらずMySQL関係の仕事をすることになります。
チームメンバーはアジアには誰もおらず大半が米国本社(Menlo Park)にいるため、自分も引っ越すこととなりました。
すでに生活の拠点を会社から10km未満というそう遠くないところに移しています。

当然ながら入社前に話をオープンにするわけにもいかないので、DeNAの中の方たちや、ごく親しい人にしかご挨拶ができずにすみませんでした。

このBlogの読者であればご存知のように、私はキャリアの途中からほぼMySQL一本で仕事をしてきています。

実はDeNAは、世界でも指折りのMySQLのヘビーユーザとして認知されています。
去年の米MySQL Conferenceでは会社としてAwardを受けました。それも純粋に技術的な貢献が評価された上での受賞であり、「日本でのコミュニティ活動に貢献した」のような海外のユーザにとって技術的な意味のないものではありませんでした。MySQLヘビーユーザ企業としては国内では文句なくNo.1で、海外でも2-5番目くらいの位置にいると言えるでしょう。

では世界で最もMySQLを使い倒しているユーザ企業と言えば、それはFacebookであることに疑いの余地はありません。
というわけで引き続きMySQL関係のお仕事をやっていきます。

DeNAでは1年半しか仕事をしなかったのですが、実力のある同僚に恵まれたこともあり、マスターの無停止切り替えや自動フェイルオーバーによる可用性の向上、効率化/高速化によるコスト大幅改善など、良い成果を出すことができました。
去年の後半には社内で社長賞を頂いたのですが、言うまでもなくそれは周りの方のおかげです。
一度問題を解決すると、類似の問題をことごとく自力で解決してくれる有能な若手技術者に恵まれたことや、マネジメント層が色々と気を使ってくれたりしたことで、
自分が現場にいながらにして中長期的な施策に多くの時間を割くことができました。
インフラチームや、ソーシャルゲームチームの同僚たちにとても感謝しています。ほかの会社ではこの短期間で同等の成果を出すことは難しかったと思います。
ですから、今でもエンジニアに対してDeNAという会社を自信を持って勧めることができます。

では、なぜ移るという決断に至ったのかというと、それは去年後半にお誘いを受けたというのが直接のきっかけではあるのですが、以下のような様々なことを考えて総合的に決めました。

・データベース技術の未来について
　・データベース技術にはどのような分類があり、
　それぞれの領域は今後どのように進化していき、
　どのような人材が求められるようになるのか、
　ということに対する自分の考え

・エンジニアのキャリアについて
　・インフラエンジニアのキャリア形成について
　・一点集中型と全方位型の違いについて
　・枯れた技術と新しい技術の違いについて
　・ベンダーとサービス企業の違いについて
　・海外に住んで働くということについて
　・日本という国についてどう思っているか
　・家族/結婚/出産/住宅ローンなどについて
　・ソーシャルゲームやライバル企業について

2月末に、DeNAの多くのエンジニアの方に対して、この「データベース技術の未来と エンジニアのキャリアを考える」というテーマで90分間にもわたって自分の考えを発表させて頂くという機会を頂きました。この内容は社外秘の情報を含むのでここでお見せすることはできませんが、参加してくださった方々の反応を見てもかなりの手応えがあったので、自分としても後悔の無いようにやっていきたいと思います。

今後ともどうぞよろしくお願い致します。

MHA for MySQLとオープンソースとDeNAの話

　実に久しぶりの投稿ですが、最近リクルートのセミナーで、私が少し前に公開した「Master High Availability Manager and tools for MySQL (MHA)」と、オープンソース界隈の技術者として、DeNAでどのような活躍の可能性があるかといった話をしてきました。

MHA for MySQLとDeNAのオープンソースの話

View more presentations from Yoshinori Matsunobu

　ust配信とかはしていなかったので残念ながらデモ動画は残ってませんが。。

　何点か今の時点での私の考えをまとめると、以下のような感じです。

・どのように作るかよりも何を作るかの方が大事だと思っている
・そのプロダクトの技術力が最も高いのはベンダーだが、何を作るかという発想はサービス会社での経験から生まれることも多いと思っている
・DBスペシャリストのような特殊技能を生かした仕事をサービス事業者で行なうには、大規模サービス事業者でないと現実的には困難
・今の為替レートは海外で働くには不利すぎる。今のご時世、海外でないとできないことはそれほど多くない
・とはいえ、スペシャリストとしてキャリアを築くのであれば、海外のカンファレンスなりである程度発信し続けることは必要。そうした活動に対する理解のある会社で働くことは重要(こうした理解が本当にある日本企業はとても限られているので注意)。

　DeNAに入ってから1年強が経ったのですが、これまではおおむね自分の想定通りに働けている感じで満足しています。

Leaving Oracle, Joining DeNA

　9月1日付けでオラクルを退職し、9月2日付けでディー・エヌ・エーに転職しました。新会社への入社の報告よりも(だいぶ)前に退職の報告をする方が多いようですが、私が外向けにオラクル退職の報告をするのはこれが初めてです。退職と転職の報告と同時に行なうのは、多くの方がナーバスになっているオラクルとMySQLの行く末について、できるだけ悪い印象を与えたくないと考えたためです。ディー・エヌ・エーは世界でも指折りのMySQLの超ヘビーユーザとして知られています。オープンソースの世界では、ベンダーからヘビーユーザの事業会社に転職し、その専門性を生かした仕事を続けることは珍しくありません(オープンソースのメリットの1つです)。というわけで、引き続きMySQLの仕事を続けます。MySQLコミュニティの方はどうか安心してくださればと思います。今後ともどうぞよろしくお願い致します。

　私は2006年9月にMySQLに入社して以来、2度の買収(サン、オラクル)を経て、のべ4年間を「MySQL本家のMySQLコンサルタント」として過ごしてきました。サービスに対して対価を受け取るコンサルタントとして勤務するのも社会人として初めてのことだったのですが、それ以外にも以下のような得難い経験をさせて頂いて、自分にとって忘れられない4年間となりました。

・米国やインドなど海外の顧客に対する難度の高いコンサルティング業務の遂行
・MySQLの開発者会議への参加や、コードのコントリビュートを通じてのOSS活動
・多いときは月数回の海外出張
・上司がアメリカ人。人事考課や交渉事なども米国流で当然すべて英語
・同僚や顧客の多くが外国人で、時差を調節しながらの在宅勤務をベースとした仕事
・メール、会議、カンファレンス等はすべて英語。質疑応答なども当然英語

　英語圏の企業で働くということは、言葉が変わるだけでなく文化も当然のことながら大きく異なるわけで、この点については「会議で言葉を英語にしただけの日本企業」では決して得られない経験ができたと思います。特に、彼らの本当の意味でバランスの取れた「ワーク・ライフ・バランス」には驚きました。年間25日の有給を取ったり、1ヶ月の長期休暇を取るようなワークスタイルは、ただ時間をかけて働くことが全てでは無いと改めて感じるものでした。1ヶ月ホテル暮らしでその月の経費精算が100万円を超えたことや、オフィスへの定期代の支給をしてもらうためにSkypeで上司と(米国では定期代支給の文化が無い)激論を交わしたことも今となっては良い思い出です。また英語漬けの環境で、私自身もずいぶんと国際化した感じがしました。趣味の面では、マンU vs チェルシーを現地観戦したり、米国でマリナーズ戦を観たりと普段できないことができて良かったと思います。

　ソニーで5年半働いた後に、2006年9月にMySQLに入社した時は、会社が存続する限り働きたい、できれば5年は持って欲しいと思っていました。まさか1年半弱で(サンに買われたことで)会社が無くなってしまうとは想像もしていませんでした。また、サン自体もソニー時代からよく知っていた会社で、多少なりとも愛着があったのですが、最終的にBEAとかマカフィーすらも下回る企業価値となって消滅したことは残念でなりません(Javaの本家なのにWebLogicより価値無いのかよ!)。ですが、オラクルは安い買い物をしたと確信しています。MySQL、サン、オラクルと、のべ4年間をMySQL本家で働けたことに後悔は全くありません。特に2007-2008年は良い同僚にも恵まれ、幸せでした。

　2度の買収というのは精神的にも事務手続き面(経費精算とか勤務管理とかの諸制度も変わる)でもタフなイベントだったのですが、最後にオラクルで働く中で「MySQLデータベースの行く末はまだまだ安泰」ということを強く感じました。性能面、拡張性、ユーザーベース、品質担保、使い勝手などどれを取ってもMySQLはきわめて優れており、だからこそ世界中で支持されてきたのだと思います。多くの方はすでに忘れているようですが、MySQLの中核のInnoDB(の開発元のInnobase)は、2005年にオラクルに買収されました。当時は誰もがMySQLは終わった、InnoDBはもう進化しない、と考えましたが、その後の進化ぶりは見ての通りです。InnoDB作者のHeikkiをはじめ、MySQL/InnoDB開発者の大半は今もオラクルに在籍しています。簡単にMySQLをオープンソースRDBMS2番手以下の地位に甘んじさせるとは考えられません。また製品評価の指標から見落とされることが多いのですが「品質」は極めて重要なポイントで、この品質は最終的には多くのユーザから使われることによってのみ担保されます。現在ユーザーベースでぶっちぎりの一番手にいるMySQLよりも品質の良いオープンソースの代替製品が果たして出てくるのかどうか。MySQLの行く末に不安を持つ方も多いと思いますが、片手間ではなく本気でMySQLを使っている人こそ、この点について共感してもらえるのではないでしょうか。


　新しい職場のディー・エヌ・エーのことについても話をしたいと思います。ディー・エヌ・エーと私の関わりは今に始まったことではなく、実は3年以上前からお誘いの話を頂いていました。私自身がMySQL本家を辞めることを考え始めたのはごく最近のことだったのですが、ちょうどその時期にディー・エヌ・エーがMySQLのスペシャリストを探していたのでラッキーでした。同じ時期に米国と欧州のMySQL系企業からもお誘いの話を頂いたのですが、海外の企業からそういった話が自分のところに来るというのはMySQLに行く前はありえなかった話で、自分自身も成長した気がします。日本で面白い職を見つけられたことは良かったし、私に対して価値を見出してくれたディー・エヌ・エーの方にはとても感謝しています。チームメンバーのこれまでの成果(オープンソース製品やDBマガジン等の記事など)を見るに、技術レベルが相当に高いという印象を持っています。その環境はプレッシャーにもなりますが、真のグローバル企業であったMySQL社の中で積んできた経験と知識・感性を真正面からぶつけていくことで、早期に貢献していければいいなと思っています。どうぞよろしくお願い致します。

デブサミ2010でLinux/DBに関する話をします

デブサミ2010で「高性能・安定運用のためのLinux-DBシステム構築/運用技術」というタイトルで講師をすることになりました。2010年2月18日の13:10から14:00のセッションです。
DB サーバには、RDBMSはもちろんのこと、OSやハードウェアも当然必要ですし、運用管理ツールやクラスタリングソフトウェアなどの支援ツール併用することも多いです。現実の運用では、支援ツールを使うことによって引き起こされるトラブルもあります。DBサーバを取り巻く全体像を把握した上で適切な対処を取れることが大切です。本セッションではこうした話をします。またRDBMSを安定稼働させ、かつ性能を発揮させるにあたって、ハードウェア、スワップ、ファイルシステム、I/Oスケジューラ、Linuxカーネルなど、どういった要素や現実問題としてトラブルになりやすく、どうすれば効果があるか、といった実戦的な話もします。
書籍「Linux-DBシステム構築/運用入門」とタイトルが大きくかぶっていますが、この本をすでに読まれている方にも、読んだことの無い方にとっても役立つ話をしたいと考えています。ご期待下さい。

MyISAMとInnoDBのどちらを使うべきか

Twitterで話題になってたので簡単にまとめました。

●MyISAMにしか無い機能を使いたい場合はMyISAMを使うしかない
・全文検索 (TritonnやSphinx)
・GIS

●InnoDBの利点(MyISAMの欠点)
▲障害対応系
・クラッシュしても再起動するだけでリカバリができる
・クラッシュリカバリにかかる時間はテーブルサイズに比例するようなことはなく、コミット済みのデータは修復できる (巨大なMyISAMテーブルのREPAIRには数日単位で時間がかかることがある)
・オンラインバックアップができる
・INSERTやLOAD DATAなどを実行している途中でCtrl+Cでその更新系SQL文を止めても、テーブルは壊れないし、中途半端な状態で更新されることも無いし、スレーブが止まることも無い

▲性能系
・行レベルロックなので並列性が高い(MyISAMはテーブルロック)。またSELECTと更新系SQL文が競合しない。
・主キー検索が高速 (クラスタ索引のため)
・ダイレクトI/O(innodb_flush_method=O_DIRECT)を使えるためキャッシュ効率が高い
・インデックスの追加/削除をするにあたってテーブルを再編成する必要がなく、そのインデックスだけを再構築するので効率が良い(InnoDB Plugin)
・Insert Bufferという仕組みにより、セカンダリインデックスへのINSERT処理の効率がMyISAMよりも良い

▲従来は欠点だったが、InnoDB Pluginによって改善されたもの
・グループコミットが無効化されるため同時更新性能が著しく低下していた
・I/Oスレッドが事実上読み書き1本ずつしか無かったため並列性が低く、RAIDやSSDを有効活用できなかった
・CPUスケーラビリティが悪く、4CPUコアくらいまでしかスケールしなかった
・同じ量のデータを投入してもテーブルサイズがMyISAMよりも倍以上大きくなることがある(InnoDB Pluginの圧縮機能を使うことで緩和する手がある)

▲ほか
・InnoDBでは外部キーが使える


●MyISAMの利点
・WHERE条件無しのSELECT COUNT(*)が一瞬で返る
(InnoDBの場合はテーブルをなめる必要がある)
・メモリにおさまらないほど巨大なテーブルのフルテーブルスキャン系の処理効率が良い
(InnoDBではこうしたバッチ処理でバッファプールの中身が追い出されてしまうし、バッファプールの管理オーバーヘッドもあるが、MyISAMは専用のバッファプールを持たないので効率が良い)
・OSコピーによってテーブルの移動が極めて簡単にできる
・MERGEテーブルを使うことができる (InnoDBでも5.1のレンジパーティショニングを使えば十分なことは多い)
・リードオンリーのテーブルであれば圧縮できる
・ALTER TABLE ENABLE/DISABLE KEYSを使える。例えばインデックスなしの状態で高速にロードして、後からインデックスを有効化とかができる (InnoDB Pluginではインデックス単位の再構築ができるのでかなり緩和できる)
・InnoDBはテーブルのオープン処理がシリアライズされるため、大量の数のテーブルを初回オープンするような処理がきつい

▲ソリューションによって緩和できるもの
・クラッシュ時に壊れる問題やリカバリ(REPAIR TABLE)の遅さは、生きているスレーブを使って復旧すれば解決できる
・テーブルが巨大になることで引き起こされる性能問題は、小さなテーブルに分割することで解決できる


　自分は、特別な事情が無い限り、5.1最新版に含まれるInnoDB Pluginを勧めています(*注)。ログ蓄積系のテーブルではMyISAMが良いと考えている方が結構多いのですが、MyISAMでは複数のクライアントから同じテーブルに対してINSERTをすれば競合してしまいますし、InnoDBのInsert BufferのようなI/O最適化の仕組みが無い(詳しくは「Linux-DBシステム構築/運用入門」の9章あたりを参考にしてください)ので、InnoDBに比べても処理効率が悪いです。MyISAMを活用する場合は、上に挙げたようなテクニックを使って問題を緩和するのが効果的です。
　ちなみに、マスターをInnoDBにして、スレーブをMyISAMにするのは以前Postしたように特別な注意が必要です。

(*注追加) InnoDB Pluginの現時点での位置づけはベータです。ただし、Dynamic/Compressedという特別なテーブルフォーマットを使わない限り、既存のInnoDBテーブルと互換性があるので、InnoDB Pluginを使いつつ、不安定な現象に遭遇したらパラメータを変えて通常のInnoDBに戻すことが可能です。追加インストールやインストールし直し等が必要ないという手軽さが魅力です。

Okyuu.comからインタビューを受けました

　先日カカクコムさんが運営しているサイト「Okyuu.com」からインタビューを受け、その記事が公開されました。
　私は幼少の頃から技術者魂全開、というキャラとは程遠く、気づいたらこの業界でDB技術系の仕事をしていた、という程度なのですが、趣味と仕事が完全に一致したという恵まれた方はむしろ少数派で、多くの方は生活のために(それと多少の興味と一致して)この業界で仕事をしているのだと思います。この業界はコードを書くのが趣味でなければ生きていけない、というプレッシャーをひしひしと感じさせますが、そうでない人にとっても活躍の場はあるのだ、という安心感を持っていただけると幸いです。
　インタビュー記事について、少しだけ補足をしたいと思います。

・お金を払うユーザーを大切にするのは当然ですが、無償で使うユーザーを無視しているわけではありません。そもそも無償で使うユーザーを無視するようではOSSビジネスは成立しません。多くの利用者が試し、時に地雷を踏みそれを修復するサイクルを繰り返すことで、ようやくお金を払っても良いかなという品質に達すると考えています。誰も使っていないプロダクト(地雷を踏むリスクが高い)にお金を払うユーザーは、昨今では非常に少ないのではないでしょうか。

・第3者企業がOSS製品をさんざん利用した挙句、ちょっと機能を追加してクローズドソースにして「自社製品です」と言い張って販売するのは、私は「邪道」のビジネスだと考えています。もちろん程度問題で、ユーティリティライブラリ程度なら全然いいと思いますが、コアの機能がもろにOSSを使っているのなら、それを拡張してクローズドソースにして販売するくらいなら本家にフィードバックしろよ、と思うわけです。

・OSSに貢献する道は、パッチを書くだけではありません。たとえば新バージョンのバグを見つけてバグレポート上で再現手順を報告するというのも立派な貢献です。こうしたバグ報告によって製品の品質は徐々に上がっていきます。その意味では、会社としてはお金を出せないしパッチを書くほどのスキルは無いけどOSSに貢献したい、というような方は、できるだけ新しいバージョンを積極的に使って、バグを見つけて報告するのが良いのではないかと思います。ただし、セキュリティ上の脆弱性に関しては、バグフィックスよりも先にBlog等で発信して一般に認知されたりすると、攻撃の対象になりかねないので気をつけて頂きたいと思います(MySQLのバグレポートでは、脆弱性の問題については、報告を受けた後に一般ユーザーが閲覧できないように権限設定されます)。

米大統領選でMySQLはどのように使われたのか

　日本の衆議院選挙が間近に迫っていますが、昨年米国で行われた大統領選において、オバマ陣営がIT技術を駆使したという話はよく知られています。MySQLももちろん使われていました。今年4月にサンタクララで開催されたMySQL Conference & Expo 2009というイベントでは、最終日のキーノートにおいて、Obama Tech Teamの方々より、大統領選においてMySQLがいかに使われたかという発表が行われました。
　本当はカンファレンス終了後にすぐちゃんとしたレポートを書いて公開する予定だったのですが、その週に起こった草なぎ剛逮捕とか、そのほかの出来事にすっかり気を取られて放置していました。Blogを始めた契機にTwitterの中で興味を持っている方がいるかどうか聞いたところ、そこそこの方が興味を示したので、ここで簡単にまとめたメモを公開することにします。


●チームメンバー
発表者は以下の5人で、ほか何名かでチームを構成。
Chuck Hagenbuch (Blue State Digital)
Leigh Heyman (Blue State Digital)
Stephen Gunn (Google)
Mikey Dickerson (Google)
Ian Gulliver (Google)
(Google社内のメーリングリストなどでボランティアの公募があって、それに乗る形で参加されたそうです)

●主なミッション
▲Fundraising(資金調達)
・インターネット経由で資金調達できる仕組みの整備
・当初の目標は$290Mの調達
・実際には$500M以上(当時の為替レートで550億円くらい)を調達することができた

▲大量のeメールの送信(購読者に応じて内容を変える)
・当初の目標は500-600万人の購読者に対して計2億5000万通程度のメール送信をすること
・実際には購読者は1300万人に達し、計20億通のメールを送信
(日本だと公職選挙法で選挙期間中のメール配信とかは違法になると思うのですが、米国ではセーフらしいです)

▲Webサイトの整備
・当初の目標は秒間800PVをさばくこと
・実際には最高で秒間4300に達した(2008年10月29日)

●インフラ設計
▲ハードウェア
・Dell 2950 w/ Disk Array (x2)
・Amazon EC2も併用したが、大事なところでは使わなかった。選挙日に2時間止まったりしたらどうしようもないので
(今のEC2のサービスレベルを見ると99.95%保証のようです。大統領選のような「特定の時間帯は絶対に動いている必要がある」というタイプのサービスではまだ厳しいのではないでしょうか)

▲MySQL
・最初はMyISAMのみ。レプリケーション使用、mysqldumpでバックアップ。
・データ量の増加に対してバックアップが追いつかなくなった。
・データ量は5TBを超えた。論理バックアップでは新規レプリカの作成にも数日単位でかかった。
・物理バックアップに移行(ファイルシステムスナップショット。おそらくflush tables with read lock + tar)。
・テーブルロックによって夜間バッチジョブが動かなかった
・結局ロックが深刻になりすぎたので、中心的なトランザクショナルなテーブルをInnoDBにした


●テーブル/データ設計

▲有権者の行動をトレースするための仕組み
・有権者がどこで何をしたかという情報を蓄積して選挙活動に活かす
・キャンペーンのe-mailは、全員に同じ内容が届くわけではない。有権者が過去に何をしたかによってグループ分けをしていて、グループごとに内容が違う。(過去に1回でもオバマ陣営のボランティアをしたか、まったくしていないかとか)
・「AとBをした人にメールを送りたい」という要求があったときに
　行動Aに関するテーブル
　行動Bに関するテーブル
を管理して、それぞれで1300万人の購読者を保持したらレコード数が膨大になってしまう。
・「デフォルト行動」を定義して、デフォルトでない行動をした人の情報だけをテーブルで持つようにした。例えばAをした人よりもしていない人の方が圧倒的に多ければ、Aをしていない人のレコードだけを格納する(Bも同様)。「Aをした人 and Bをした人」は、全体から「Aをしていない人 or Bをしていない人」を引いたものと同じだが、この場合は後者の方が処理効率が良い
・これでレコード件数を減らすことができ、パフォーマンスが上がった

▲有権者情報の検索処理
・MyISAMではロック競合が深刻だったのでInnoDBにした。
・InnoDBにしたことでCOUNT(*)に時間がかかるようになった
　・AUTO_INCREMENT列に対してMAX関数を使うことで、最大値を取るようにした。最大値=件数という想定
　・誰か(開発者)がテスト用に巨大な値をセットしたら、値が本来の値(件数)ではなくなってしまった。
　・AUTO_INCREMENTは再起動かALTER TABLEをしないと戻らないのでmaxは使えなくなった(ダウンタイムを設けることができない状況)
　・サマリーテーブルに件数情報を書くようにした。ある一定のタイミングで、最新の件数をサマリーテーブルに書き出すようにして、高速に件数を取れるようにした
・MyISAM→InnoDBによってデータ量が増えたので、広範囲にまたがる集計系処理にかなりの時間がかかるようになった
・リアルタイム性の求められない処理だったので、同一テーブルのコピーをMyISAMで作成し、そのMyISAMテーブルに対して集計処理を行うことで高速化した

▲大量のeメールの送信履歴管理
・メールの送受信情報を記録する要求がある
・1時間に200万通を送る必要があり、その履歴も管理
・MyISAMテーブルを使用
・テスト環境では100万件の登録に2分だったが、本番環境では1時間かかった
・テスト環境では空だったのが、本番環境では既存のレコードが多いのが原因
・できるだけ空のテーブルにINSERTさせるように設計変更した
・MyISAMテーブルを複数用意して、日付で分割、空のMyISAMテーブルにINSERTさせる
・これらをMERGEテーブルとして結合
(本番環境でINSERTに長時間を要したのは、インデックスの影響と考えられます)


▲重いバッチ処理によってマスターがスローダウンする
・バッチ処理の中で、重たいSELECTをスレーブで行って、最終結果をマスターに書くようにした


▲Early Vote (期日前投票)推進
・有権者の行動をいち早く分析して、支持者に早期に期日前投票に行くように促す仕組み
・「どの日に、どの週で、どの政党に、どの性別の人が、何秒投票し、平均年齢は何歳で...」といった情報を解析したい
・もともとは各テーブルに行動情報を蓄積して、7テーブルくらいをジョインして結果を返していた
　・4000秒以上かかっていた
・日ごとに集計するのが分かっていたので、日を切り口にしたサマリーテーブルを導入。秒単位で結果を返せるようになった




●感想
　発表者たちの主な活動期間は、2008年9月中旬頃から11月までだったそうです。極めて限られた期間の中では、事前に練りに練ったMySQLアーキテクチャ構成を元に型にはめていくというアプローチが難しかったようで、かなりの試行錯誤を繰り返しながらそれでもなんとかなった、という感じで生々しい話でした。Early Voteを強力に推進したことが大統領選勝利の大きなポイントだったというニュースは日本にも伝わってきましたが、それを裏方で支えていたのは、必要な情報を正しく迅速に取れるための仕組みであり、そのためのデータモデルであったということを強く感じました。個々のテクニックはよく知られたものばかりですが、それを適切に組み合わせられるのは経験と技術あってのものですし、適切なスキルがあればOSSでもこうした重要なシステムを安定稼動できる(逆に無ければ商用製品でも困難)、ということを示す良いキーノートだったと思います。

正しいベンチマークをするための10のポイント

　世の中ではたくさんの人が独自にベンチマークを行ない、独自に情報発信がされています。そのベンチマークの中には、非常に参考になるものもあれば、現実性に大きく欠けるものもあります。競合他社が、ライバル社の製品にとって不利な条件でベンチマークを行い、それを発信することも日常的に行われています。ベンチマークの結果を鵜呑みにすることは危険で、結果の意味を判断するスキルを持つことが重要です。これはプロジェクトにおいて負荷テストを行う場合にも重要です。負荷テストの条件設定が正しいかどうかを判断できるようになるためです。
　ここでは、私がDBサーバのベンチマーク/負荷テストを行ったり結果を読んだりする上で、心がけているポイントを10個ほど紹介したいと思います。

■ハードウェアに関する4つのポイント

1. ハードウェアのスペックと設定を注視する
　ハードウェア構成によってベンチマーク結果は劇的に変わるので、言わずもがなでしょう。以下のような点、特にDBサーバであればメモリ容量やストレージ構成に注意を払いたいところです。

・CPU型番、クロック、コア数
・メモリ容量
・ストレージ本数、RAID設定、HDDの回転数、SSDの製品名、ライトキャッシュの設定など
・ネットワーク

　RDBMSにおいては、ライトキャッシュの設定(バッテリーバックアップつきライトキャッシュが有効になっているかどうか)が非常に大切だということも付け加えておきます。


2. 大まかなアクセス性能を知っておく
　CPU、メモリ、HDD、ネットワークなどの各ハードウェアに対して、1回アクセスする際にどの程度の時間がかかるのか、あるいは1秒間に何回程度のアクセスができるのか、（概算値を）即答できるでしょうか。何度もアプリケーション運用を経験していると、このあたりの性能指標は嫌でも意識させられることでしょう。
　ざっくりとした感覚では、現在Linux-DB サーバとして主に用いられているハードウェア環境では、単一接続という条件では、CPUへのアクセス時間が10ns、メモリへのアクセス時間が60ns、HDD へのアクセス時間が5ms、Intel SSDが0.2ms、ネットワーク(Gigabit Ethernet)が0.1ms程度といったところではないでしょうか。データ転送量が多ければそれだけ時間はかかりますし、CPUクロック数やキャッシュの存在によっても値は全然違ってくるので、「正確性」という意味では突っ込みどころがあります。しかし、桁の感覚としてはおおむね正しいでしょう。特に、HDDがメモリどころかネットワークに比べても大きく性能が落ちるというのは、パフォーマンスチューニング上で非常に大切なポイントです。HDD へのアクセス時間が5ms ということは、1秒あたり200回くらいしかアクセスできないことを意味しています。
　こういった性能指標を叩き込んでおけば、ベンチマーク結果を見たときに不自然かどうかを見分けられるようになります。


3. 並列性を考慮する
　上で挙げた性能指標は単一接続時のものですが、アプリケーションでは複数の接続が張られるため、並列性についても考慮する必要があります。同時何接続から処理が行われた状態でのベンチマークなのか、ということです。CPUやメモリは桁違いのアクセス性能なので置いておくとしても、HDD/SSD/ネットワークの並列性に関する理解は重要です。ストレージの場合は、RAIDやコマンドキューイングによって並列性を高めることができます。コマンドキューイングと一口で言っても、HDDはSASとSATAによっても違いますし、HDDとSSDでも大きく変わってきます。SAS HDDでは単一接続でのランダムアクセス性能が200IOPSくらいだったのが、大量接続になると500IOPSくらいを得ることができるでしょう。Intel SSDではこれがさらに跳ね上がって、1接続では毎秒のランダムリード性能が5,000くらいだったのが、大量接続では25,000近くを得ることも不可能ではありません。
　ネットワーク(TCP/IP)も並列性が大変優れています。接続数が数十になった場合、ネットワークだけで見ればトータルで秒間20万回クラスのリモートアクセスも可能で、これはSSDをも大きく上回る性能です。
　RDBMSから見ると、同時接続数が増えた場合、内部的なmutex競合などでスループットが落ちるケースも頻発します。現実問題としては、無尽蔵に接続を張るのではなく、コネクションプーリングやMaxClientsなどで接続数を限定することも必要になってきます。期待されるアクセス性能に対して、どの程度落ちた値になるか、という観点での性能分析も面白いところです。


4. どこのスループットやレスポンスタイムかを確認する(ローカルアクセスかリモートアクセスかを確認する)
　「ライブラリBはライブラリAよりも処理速度が100倍速い」という状況はよくあります。これを見て、ライブラリAをBに入れ替えたら、アプリケーションのレスポンスタイムやスループットが100倍速くなると考える人はいないでしょう。スループットやレスポンスタイムという観点では、Webブラウザ→Web/APサーバ→DBサーバというアクセス経路全体を見る必要があり、特定のサーバの特定のロジックだけを改善しても、それが全体から見て無視できる範囲であれば大きな効果にはなりません。経験的には、DBサーバがボトルネックになることが多いため、DBサーバだけをベンチマークしてX倍速くなった、と言うことが多いですが、RDBMSと比較して別の製品(キャッシュサーバ等)を評価するような場合は、単体だけではなく、全体的なスループットの面でどれだけ効果があるのかも確認したいところです。
　多くのベンチマークでは、負荷をかけるクライアント、DBサーバともに同一マシン上に同居させた状態で行われています。しかし、現実的な用途ではアプリケーションサーバとDBサーバは別のマシン上に置かれるので、リモートアクセスになります。
　これは、「同一プロセス空間内でアクセスできる製品」のベンチマークに顕著な影響を及ぼします。同一プロセス内でローカルアクセスする場合と、リモートアクセスになる場合とでは、ネットワークのオーバーヘッドだけでなく、オブジェクト・シリアライゼーションのオーバーヘッドも加わるので極端な差(条件次第では100倍を超える差)が生まれます。逆の言い方をすれば、可能ならローカルアクセスで完結させるのは良い設計だと言えます(EHCacheやTimesTenなどをAPサーバ上でローカルで使うというアプローチ)。
　「秒間100万アクセスができました」などと宣伝している製品も見かけますが、リモートアクセスをさせた場合は、GbEのネットワークアクセス性能に引きずられるので、1台あたりではせいぜい10数万かそれ以下のスループットに落ち着くのではないでしょうか。


■ベンチマーク用アプリケーションに関する4つのポイント

5. 処理の内容に気を配る
　ベンチマークプログラムが処理する内容と、実際のアプリケーションで処理する内容が大きくかけ離れているケースがあります。例えばDBサーバではI/Oが主なボトルネックになりますが、そこでI/O性能を測定するベンチマークをしようとしても簡単ではありません。以下のように性能に影響を与える要因が数多く存在し、どれを変えても結果が変わるためです。

・扱うデータ量
・メモリの搭載量
・ディスク本数、ディスクの種類(SSD/HDDなど)
・RAID構成
・ランダムI/OかシーケンシャルI/Oか
・ダイレクトI/Oかbuffered I/Oか
・ライトキャッシュの有無
・I/O単位(1KBか10MBか)
・同時I/Oスレッド本数
・扱うファイル数
・上書きか追記か
・ファイルシステムおよびマウントオプションの違い

　ddコマンドなどによってシーケンシャルなファイル書き込みを行ない、毎秒100MBの転送量が出たとします。いざDB サーバを動かすと毎秒数MB程度しか転送量が出ず、「このデータベースはおかしい」などと言い出す人がたまにいますが、それは行なっている処理が違うためです。
　多くのパフォーマンスの変動要因があるので、簡易なツールを用いてI/O の負荷測定を行なうのは簡単ではありません。やはり、DB サーバに対して直接負荷をかけたほうが確実性が高いでしょう。


6. データサイズに注意する
　バージョンも同じ、テーブル構成も同じ条件でベンチマークをしても、本番環境とかけ離れた結果になることはよくあります。最も大きな要因がデータサイズではないでしょうか。データサイズが実メモリに十分おさまる場合、すべてがメモリアクセスになるため高速になります。MySQL(InnoDB)でも、すべてがインメモリであれば、以下のように主キー検索がローカルアクセスで14万クエリ/秒、リモートアクセスで9万クエリ/秒などといった値をたたき出すことができます(Nehalem 2.8GHz x 16コアでmysqlslapを使用)。

ローカル50接続：
mysqlslap --concurrency=50 --iterations=1 --number-int-cols=2 
--number-char-cols=3 --auto-generate-sql --engine=innodb 
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key
 --auto-generate-sql-write-number=10000 --number-of-queries=1000000
 --host=127.0.0.1 --port=3306
Benchmark
      Running for engine innodb
      Average number of seconds to run all queries: 6.866 seconds
      Minimum number of seconds to run all queries: 6.866 seconds
      Maximum number of seconds to run all queries: 6.866 seconds
      Number of clients running queries: 50
      Average number of queries per client: 20000

リモート50接続：
mysqlslap --concurrency=50 --iterations=1 --number-int-cols=2 
--number-char-cols=3 --auto-generate-sql --engine=innodb 
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key
 --auto-generate-sql-write-number=10000 --number-of-queries=1000000
 --host=remote --port=3306
Benchmark
      Running for engine innodb
      Average number of seconds to run all queries: 11.200 seconds
      Minimum number of seconds to run all queries: 11.200 seconds
      Maximum number of seconds to run all queries: 11.200 seconds
      Number of clients running queries: 50
      Average number of queries per client: 20000

　しかし、データがメモリにおさまらなくなればディスクアクセスの割合が多くなるため、こんな値を出すことは不可能になります。範囲検索の場合はランダムアクセスの割合も増えるため、ディスクI/Oが多くなりがちで、性能はさらに落ちる傾向にあります。負荷テストをするときに、簡単のためデータ量は1GBで、などとやっていると現実的な結果を得ることは困難です。


7. データのアクセス範囲に注意する
　データサイズを十分に揃えたとしても、それだけでは十分ではありません。アクセス範囲をカバーすることも大切です。例えば、ユーザ数が10万人、同時にアクセスするユーザ数が100人と見積もったとします。ここでよくやってしまうのが、負荷テストに使うユーザ数をあらかじめ決めた100ユーザで固定してしまうことです。これは実際のアクセスパターンとは異なります。実際のアクセスパターンは100ユーザ固定ではなく、さまざまなユーザにまたがることでしょう。
　トータルで10万人のユーザがアクセスしてきた場合、DBのデータはキャッシュに収まりきらず、ディスクアクセスの頻発が予想されます。一方で100ユーザに固定した場合、100ユーザ分のデータしかアクセスされないので、すべてのアクセスがインメモリで行なわれることになるでしょう。つまり、実際に行なわれるであろう処理よりもずっと高速に処理されてしまい、実際よりも良い結果が得られてしまうのです。


8. データの内容に注意する
　多くのWeb アプリケーションでは、傾向の違いはあるとはいえ「頻繁にアクセスしてくるヘビーユーザ」と「ほどほどにアクセスするユーザ」「めったにアクセスしないライトユーザ」がいます。各ユーザによってDB サーバ内でのアクセス傾向は変わってきます。当然ながら、ライトユーザよりもヘビーユーザのほうがアクセス頻度は高くなります。このため、負荷テストを厳密に行なうなら、ユーザIDを均等に割り振っていくのではなく、ヘビーユーザのIDに対して重み付けをすることにも意味があります。
　ヘビーユーザはDBサーバに対する負荷の与え方も変わってきます。例えば、書いた日記の一覧を取り出すために「SELECT 日記ID FROM 日記テーブルWHERE ユーザID=?」というSQL 文を実行したとします。この場合、マッチするレコード数はヘビーユーザほど多くなるでしょう。ここで取得した日記ID を用いて日記の本文を取り出すためには、日記ID の数だけランダムアクセスが必要になります。このため、ヘビーユーザほどランダムアクセスの回数が増える、つまりSQL 文の実行負荷が増えることになります。一方でヘビーユーザに関しては、アクセス回数が多いことからよりキャッシュされやすく、ディスクアクセスの頻度は高くなりにくいというプラス材料も挙げられます。


■データベース設定に関する2つのポイント

9. OS/ミドルウェアのバージョンと設定に注意する
　OSの種類やバージョン、RDBMSのバージョン選定、パラメータ設定なども注意が必要です。これも説明は不要でしょう。
　RDBMSの場合、耐障害性の高い設定にするか、低い設定にするかによっても大きくスループットが変わるので注意が必要です。ベンチマーク結果を見るときは、自分がどういう設定にしようとしているかを踏まえた上で見るようにすべきでしょう。


10. 統計コマンドの使い方と見方を知る
　ボトルネックを特定する上で統計分析ツールは必須です。Linuxならvmstat、iostat、mpstatの読み方は最低限おさえておきたいところです。MySQLではスロークエリログ分析(mysqldumpslow等)、ステータス変数の推移分析(mysqladmin extended-status等)、実行中のクエリ解析(show full processlist等)の使い方と読み方くらいは知っておきたいところです。


　これらのポイントをおさえておくと、DBサーバのベンチマークを見たときに、その妥当性がある程度判断できるようになります。例題として「RDBMSは1レコードのアクセスに数ms程度かかる。一方でTimesTenのようなインメモリデータベースは数マイクロ秒でアクセスできる。だからインメモリデータベースはRDBMSより1000倍速い。これはインメモリデータベースの内部構造がメモリアクセスに最適化されているからだ」という説明があったときに、それをどう解釈するかを考えてみましょう。
　1レコードのアクセスに数msということは、1秒あたり数百回のアクセスしかできないことを意味します。数百回というのはHDD1本レベルの値なので、まったくキャッシュされていない条件での値だと判断できます。一方で数マイクロ秒という値は、リモートアクセス(GbE)では不可能なので、ローカルアクセスをした上での値だと考えられます。つまり、リモートアクセスな上にまったくキャッシュされていない状態のRDBMSと、ローカルアクセスでインメモリなデータベースの性能を比較して、1000倍速いと言っているのだということが分かります。ディスクアクセスとネットワークアクセスが発生する環境とまったく発生しない環境では、1000倍近い差は発生し得ます。決して、データベースの内部構造の違いだけで1000倍の差が発生しているわけではありません。上記のような説明を受けたときに、そのまま信じ込んでしまう人は多いのですが、DBスペシャリストが近くにいれば、説明を受けた瞬間に矛盾に気づくことができるでしょう。
　現実的なアプリケーションでは、それなりの量のメモリを搭載するはずなので、RDBMSはここまで不利な条件にはなりません。そこで、より正しい評価をするためには「メモリにキャッシュされている状態でのRDBMSとの性能比較」であったり、「同一メモリ容量でどれだけ多くのレコードをキャッシュできるか(T-TreeはB+-Treeよりも空間効率に優れる)」といった観点でのベンチマークをするのが妥当と考えられます。リモートアクセス前提であれば、前述のmysqlslapが示すように、InnoDBでも秒間9万クエリ級をさばくことが可能(すべてメモリにキャッシュされている状態での主キー検索の場合)で、これはリモートアクセスした状態のインメモリデータベースと比べても大きく見劣りしないでしょう。
　ベンチマークは悪意を持って行われることはむしろ少数派で、ほとんどの場合は得られた結果をそのまま発信しているだけなので、結果を発信した人を責めるのは良くないと考えています(私自身も間違えることは良くあります)。むしろ積極的な情報交換があった方が良いでしょう。ただ結果の変動要因が非常に多いので、それを読む側の人にもスキルが求められます。ベンチマークは簡単ではないのです。

InnoDBのAUTO_INCREMENTが遅い問題は5.1でどう改善されたのか

　MySQL5.1のGA版が出てから8ヶ月余りが経過しましたが、まだ5.0(あるいはそれ以前)をメインで使っている方も多いのではないでしょうか。5.1の何が良いのかいまいち分からないという方も多いかもしれません。そんな方にとって分かりやすい例の1つが、「5.1でInnoDBのAUTO_INCREMENT性能が大幅に改善された」という点です。私は仕事柄Web系の技術者の方と話をする機会もよくありますが、意外と知られていない改善なので(まさにトラフィックと同時接続数の多いWeb系システムのための改善なのに…)この機会に取り上げることにします。
　簡単に言えば、AUTO_INCREMENTを持つテーブルに対してINSERTをするクライアント数が数十、数百と増えていった時に、従来はスループットが指数関数的に落ちてしまっていたのが、5.1では高速かつ安定するようになりました。以下にmysqlslapのINSERTベンチマークの結果を示します。

mysqlslap  --concurrency=1,5,10,20,30,50,100,200,300,500 \
--iterations=1 --engine=innodb \
--auto-generate-sql --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=write \
--number-of-queries=100000

　MySQLの比較対象バージョンは5.0.83と5.1.37。my.cnfパラメータはWebアプリ向けにしました。log-binでバイナリログを有効化。innodb_flush_log_at_trx_commitは2にしてコミット時同期書き込みではなく「コミット時ファイルシステムキャッシュに書き込み」に変更(1にしていない理由は先週取り上げたグループコミットの性能問題を緩和するため)。max_connectionsを大きめにとってあります。

[mysqld]
innodb_flush_log_at_trx_commit=2
innodb_log_files_in_group=2
innodb_buffer_pool_size=11G
innodb_flush_method=O_DIRECT
innodb_log_file_size=256M
innodb_data_file_path=ibdata1:100M:autoextend
innodb_file_per_table
log-bin
max_connections=1000
table_cache=8192
server-id=1

　グラフの縦軸は計10万件をINSERTするのに要した時間です。見てのとおり、MySQL5.0では接続数の増加に対してINSERT性能が加速度的に低下しています。200接続のときは10万件のINSERTに150秒を要しており、秒間660件程度しかINSERTできていません。ディスクI/Oがボトルネックになっているわけでもないのに、660というのは低すぎる値です。さらに上図のグラフは同時接続数200の時点でグラフが切れていますが、それ以上のときは以下のようなエラーで止まってしまいました。

mysqlslap: Cannot run query INSERT INTO t1 VALUES
(NULL,100669,'qnMdipW5KkXdTjGCh2PNzLoeR0527frpQDQ8uw67Ydk1K06uuNHtkxYBxT5w8plb2Bbp
zhwYBgPNYX9RmICWGkZD6fAESvhMzH3yqzMtXoH4BQNylbK1CmEIPGYlC6')
ERROR : Deadlock found when trying to get lock; try restarting transaction

　メッセージを見るとデッドロックのように見えますが、実際にはもちろん(2つのトランザクションが互いにロック待ちになる)デッドロックではありません。同じロックを待つクライアント数が一定ライン(ソース上の定数LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK:200固定)を超えると、デッドロック扱いにして強制的にロールバックさせる、というInnoDBの実装に起因します。
　一方MySQL5.1では接続数の増加に対してINSERT性能が安定しています。500接続まで試しましたが、どれも10万件のINSERTに5秒前後で終了しており、平均INSERT数は秒間2万前後になっています。現実的な用途ではテーブル/インデックスサイズが大きいことによるディスクI/Oがボトルネックになりがちなので、ここまで速くはなりませんが、それでも5.0に比べれば十分に優れた値と言えるでしょう。
　AUTO_INCREMENTな主キーはInnoDBのクラスタ索引と相性が良い(理由は今後解説)ので、性能問題が改善されたことには大きな意味があります。このAUTO_INCREMENTの動作の変化については、オンラインマニュアルに記載があります。ここでも紹介します。

●AUTO-INCテーブルロック
　AUTO_INCREMENTを持つテーブルに対してINSERTをする場合、そのINSERT文が完了するまでの間、「AUTO-INC」と呼ばれるテーブルロックが確保されるという特徴がありました。テーブルロックを持つ理由は、マスターに対して行った更新結果と、バイナリログに書かれているSQL文を実行した結果(スレーブに反映される)を確実に一致させるためです。例えば、空のテーブルtに対して以下の処理をするケースを考えてみます。

マスター：
1) INSERT INTO t (val) SELECT val FROM t2; (t2は1000レコードあると仮定)
2) INSERT INTO t (id, val) VALUES (null, 'abc'); (1001が割り当てられる)

　この場合、2)のAUTO_INCREMENTに何の値が入るかは、1)で何レコードINSERTされるかが確定しないと分かりません。そのため、1)の完了を待つ必要があります。バイナリログには以下のように記録されます。

SET INSERT_ID=1;
INSERT INTO t (val) SELECT val FROM t2;
SET INSERT_ID=1001;
INSERT INTO t (id, val) VALUES (null, 'abc');

　1)の完了を待ってから2)を実行することで、2)で入るAUTO_INCREMENT値が1001であることが特定できるので、バイナリログには「SET INSERT_ID=1001;」を埋めることができます。この結果、どのスレーブで処理をしても1001がINSERTされ、マスターとスレーブで値を一致させることができます。
　通常のトランザクションのロックは、トランザクションが終了(コミットまたはロールバック)するまで保持されますが、AUTO-INCテーブルロックはINSERT文の終了時点で解放されます。したがってINSERTをした後にロールバックをしても、増えたAUTO_INCREMENT値は元に戻りません。またロックの期間も短くなります。
　5.0までは、AUTO_INCREMENTを持つテーブルに対するINSERT文すべてがAUTO-INCテーブルロックをかけます。AUTO_INCREMENT列に対してNULLを入れると新しい番号が採番されるというのがMySQLの仕様ですが、NULLではなく明示的に値をセットしたときにも、AUTO-INCテーブルロックは確保されます。
　テーブルロックを持つことの最大のデメリットは、同時実行性能が極端に低下してしまうことです。ロックの期間が短いといっても、INSERTが集中すれば大量の待ち行列ができるため上図のようにとんでもなく性能が悪化します。



●5.1ではAUTO-INCテーブルロックではなくmutexになった
　しかし、本来はINSERT文の実行が終わるまで常にテーブルロックを保持しなければならないわけではありません。例えば、以下の2つのINSERTを考えてみます。

3) INSERT INTO t (id, val) VALUES (null, 'abc'); # 3が入った
4) INSERT INTO t (id, val) VALUES (null, 'def'); # 4が入った

　別々のクライアントから3)→4)の順に実行した場合、テーブルロックをINSERT完了まで保持しないと、AUTO_INCREMENT値の採番順序とコミット順序(バイナリログへの記録順序)がずれる可能性が高くなります。しかし、実はずれてしまっても問題は起きません。バイナリログには以下のように、次にどのAUTO_INCREMENT値をセットするかの情報も一緒に記録されるからです。例えば以下のような感じで記録されます。

SET INSERT_ID=4;
INSERT INTO t (id, val) VALUES (null, 'def');
SET INSERT_ID=3;
INSERT INTO t (id, val) VALUES (null, 'abc');

　このため、バイナリログへの記録の順番が逆転しても最終結果は同じになります。そもそも、テーブルロック自体がINSERTの終了時点で解放されてしまうので、以下のような処理をすればMySQL5.0でも逆転現象は起きます。

T1:
BEGIN;
INSERT INTO t VALUES(null, 1);

T2:
BEGIN;
INSERT INTO t VALUES(null, 2);
COMMIT;

T1:
COMMIT;

　この場合、AUTO_INCREMENTの採番はT1の方が先に行われますが、コミットおよびバイナリログへの記録はT2の方が先に行われます。それでも「SET INSERT_ID=N」がバイナリログに一緒に記録されるため、矛盾した結果にはなりません。

　このようなINSERT文では、文の完了までテーブルロックを持たなくても矛盾が無いように処理できます。同じAUTO_INCREMENT値が採番されてはまずいので、採番の部分はテーブルロックをかける必要がありますが、それ以外は不要です。
　つまり、次にどのAUTO_INCREMENT値を割り当てれば良いかが分かれば、INSERT文が終わるまでテーブルロックを保持する必要はありません。別の言い方をすれば、そのINSERT文によって何件INSERTされるかが事前に判断できる場合には、INSERT文が終わるまでテーブルロックを保持する必要は無いということです。3)も4)も、実際にINSERTされるのは多くても1件だということは、INSERT構文から(実際に処理をしなくても)判断できます。そして、世の中で実行される大半のINSERT文はこの形です。そこで5.1では、「何件INSERTされるかをSQL構文から判断できる場合」のAUTO_INCREMENTロックのかけ方が以下のように変更されました。

mutex(table->autoinc_mutex)を取る
AUTO_INCREMENT値を採番する
mutexを解放する

　番号が重複してはいけないので、採番処理自体はクリティカルセクションの中に置く必要がありますが、それ以外は並列で動作できます。INSERT文が完了するまでがクリティカルセクションであった従来と比べると、同時実行性が大幅に高まっています。これが上図のような大きな差が生じた原因です。現在MySQL5.0以下を使っていて、AUTO_INCREMENT性能の遅さに悩まされている方にこそ、ぜひMySQL5.1を試してみてほしいです。

　ただし、いつでもこのような高速な動作ができるわけではありません。これができるのは、事前に何レコード入れるのかが分かっている場合だけです。最初に例として挙げたINSERT ... SELECTやLOAD DATAのように、何レコードがINSERTされるかをSQL文の実行前に判断できない処理については、いくつまでAUTO_INCREMENT番号を付与すれば良いかを特定することができません。こうしたSQL文を実行するときは、AUTO-INCテーブルロックが引き続き確保されます。AUTO-INCテーブルロックが確保されている間は、通常のINSERT文であっても(mutexではなく)AUTO-INCテーブルロックを確保しようとします。このため同時実行性が低下します。
　これを防ぐ手立てとしては、innodb_autoinc_lock_modeというパラメータを2にして(デフォルトは1)、なおかつバイナリログのフォーマットをSQLステートメントベースから行ベースに変える、という方法があります。この場合にはAUTO-INCテーブルロックが確保されなくなります。ただし、SQL文ベースのバイナリログでは整合性を保証できないので行ベースにする必要があること、行ベースにすることでの性能低下があるなどの副作用があることに注意が必要です。ユースケースによっては検討の余地があるでしょう。innodb_autoinc_lock_mode=0は従来型の動作をしますが、これは互換性を保証するためのもの(1や2では、処理内容によってAUTO_INCREMENT番号が飛ぶ場合がある。詳細はマニュアルを参照)で、よほどの理由が無い限り設定する必要はありません。
　もしこのあたりの実装の詳細を知りたい方がいたら、ha_innodb.ccのha_innobase::innobase_lock_autoinc(void)あたりを見てみてください。innodb_autoinc_lock_modeが1のときは条件付でAUTO-INCテーブルロックをかけることなどが分かるでしょう。

DBチューニングではディスクI/O性能を注視する

　DBチューニングにおいて、気を配るべきところは数多くありますが、中でも真っ先に見るべきところはディスクI/Oでしょう。なぜかというと、メモリアクセスに比べてHDDの方が圧倒的に遅く、最もパフォーマンス阻害要因になりやすいためです。ディスクI/Oネックの解決方法を探っていくと、「テーブル/インデックス設計やSQL文の見直し」に行き着くこともまた多いです。これらが不適切だと、結果として大量のレコードをアクセスすることになり、ディスクI/Oが多く発生してしまうためです。根本的な原因はディスクI/Oにあります(CPUネックになることもありますが、その例は別の機会に取り上げます)。
　ディスクI/Oには大きく分けてシーケンシャルアクセスとランダムアクセスの2種類のアクセスパターンがありますが、RDBMSではインデックスアクセスが主体となるため、ルート→ブランチ→リーフ→実レコードという経路でのランダムアクセスが発生します。
　秒間に処理できるI/O数のことをIOPSと呼びます。HDDへのランダムアクセスはシーク待ち時間や回転待ち時間のコストが極めて高く、1回あたり5ms級の時間を要すため、HDD1本あたりのランダムIOPSはせいぜい数百程度にしかなりません。メモリアクセスが数十ナノ秒単位ですから、HDDと比べて数十万倍クラスのアクセス性能差があります。1個のSQL文を処理するという観点からは、1回のメモリアクセス vs 1回のディスクアクセスにはならず、構文解析/実行計画生成/文字列コピーなどさまざまな処理が行なわれるので、格差という意味では数十万倍ほどにはなりません。それでも100倍くらいの差は簡単に発生してしまいます。すべてがインメモリの状態であれば、単純な主キー検索について、秒間数万クエリくらいは処理できます。一方で大半がディスクに向かう場合は、4本くらいのRAID1+0構成であっても毎秒数百クエリ程度にとどまってしまうことは少なくありません。
　仮に、インメモリで完結するSQL文の処理時間が1、ディスクアクセスを伴う場合の処理時間が100としましょう。バッファプールのヒット率が100%の場合は、100個のリクエストを処理するのにかかる延べ時間は1×100で100です。一方ヒット率95%の場合は、1×95+100×5=595となり、6倍くらいかかってしまいます。わずかなヒット率のダウンで、スループットが1/6にまで低下してしまう計算です。ヒット率が80%の場合は、1×80+100×20=2080で、最初の状態の約1/21まで落ちてしまいます。バッファプールにおさまるようにアクセスさせることが重要なことが分かります。実際のRDBMSのパフォーマンスも、これと似たような傾向を示します。データ量の増加に対してスループットがいかに低下していくかという例は、SH2さんという方が過去に行なったInnoDBのベンチマークが分かりやすいです。
グラフ：http://f.hatena.ne.jp/sh2/20090704095053
エントリ：http://d.hatena.ne.jp/sh2/20090705

　テーブルやインデックスのサイズに対して実メモリが十分に大きい場合、あるいはテーブル/インデックスサイズが大きくてもアクセス範囲が限定されているような場合は、すべての処理がキャッシュされるためディスクI/Oはほとんど発生しません。DBチューニングでは、この状態を目指していきます。DBサーバはメモリを増設すればそれでOK、と言う人が多いのはこのためです。それ以外にも、データ型の見直し(文字列→数値など)などによってデータサイズを小さくすることで、同じメモリサイズでもより多くのレコードをキャッシュできるようにすることも非常に効果的です。1個の巨大なテーブルあたり、20-40%程度のデータサイズ縮小ができることは珍しくありません。またIOPSを減らすという観点では、大量レコードをスキャンしないと返せない処理(件数取得とか)において、サマリーテーブルを用意してインデックスからのルックアップ一発で済ましたり、memcachedなどにキャッシュしておいてそもそもDBサーバにアクセスさせない、といった手も有効です。すでにさまざまな手立てを行なっている方が多いと思います。
　インデックス戦略も性能に決定的な影響を与えます。現実的な用途では、ルートとブランチはキャッシュされやすいので、多くてもリーフと実レコードの2回のランダムI/Oが発生すると見て良いでしょう。ただしこれはインデックス経由でレコードを1個だけ取る場合の話です。範囲検索によって10レコードを取る場合は、リーフ1回に対して実レコード10回の、計11回のランダムI/Oが発生し得ます。SQLはシンプルなのになんで遅いの?という質問を受けることがよくありますが、SQLがシンプルかどうかは関係なく、どれだけのI/Oが発生するかがポイントになります。ディスクI/O性能問題を解決する上で、インデックス戦略の見直しは大きなインパクトがあります。このあたりの詳細なテクニックは、今後さまざまな機会で取り上げていきたいと思います。

InnoDB Plugin 1.0.4 - InnoDB史上極めて重要なリリース

　日本時間の今日、InnoDB Pluginの新バージョン1.0.4がリリースされました。このバージョンでは、「バイナリログを有効にするとグループコミットが効かなくなる問題」が修正されています。ほとんどの環境にとって極めて効果の高い修正です。ほかにもI/Oスレッドの多重化(同様のものがMySQL5.4にも搭載)など効果的な修正が行なわれています。
　InnoDB PluginはまだGA(安定版)ではないので、品質面では標準搭載されているInnoDBよりも落ちます。ただしMySQL Enterpriseサブスクリプションを買っている方であれば追加費用無しでInnoDB Pluginのサポートを受けることができるので、お気軽に試してみて頂ければと思います。
　グループコミット問題修復の効果のほどは、一目瞭然なので図を見た方が分かりやすいでしょう。下図は、mysqlslapで、複数のコネクションから並列でINSERTを行なったときの結果です。

mysqlslap  --concurrency=30 --iterations=1 --engine=innodb \
--auto-generate-sql --auto-generate-sql-load-type=write \
--number-of-queries=100000

my.cnfのパラメータは以下のとおり。H/WはIntel Xeon X5560 Nehalem 2.80GHz * 16cores, 12GB RAM, SAS HDDです。innodb_flush_log_at_trx_commit=1でコミット時同期書き込みを有効にして、バイナリログを有効にして、ストレージではバッテリーバックアップつきライトキャッシュを有効にする、というオーソドックスな設定です。

[mysqld]
basedir=/usr/mysql5137
datadir=/data/mysql5137/data
ignore_builtin_innodb
plugin-load=innodb=ha_innodb.so;innodb_trx=ha_innodb.so;
innodb_locks=ha_innodb.so;innodb_lock_waits=ha_innodb.so;
innodb_cmp=ha_innodb.so;innodb_cmp_reset=ha_innodb.so;
innodb_cmpmem=ha_innodb.so;innodb_cmpmem_reset=ha_innodb.so

innodb_log_files_in_group=2
innodb_buffer_pool_size=2G
innodb_flush_method=O_DIRECT
innodb_log_file_size=512M
innodb_data_file_path=ibdata1:500M:autoextend
innodb_file_per_table
log-bin
table_cache=8192

明らかにInnoDB Plugin 1.0.4の方が高速化しています。通常版のInnoDBでは接続数の増加に対してスケールしていませんが、InnoDB Plugin 1.0.4ではスケールしており、30接続では6.1倍もの性能差が出ていることが分かります(innodb_support_xa=1の場合)。innodb_support_xaを0にすることもできますが、後述の理由で一般的には推奨されません。
以下では、このような性能差が出た理由について述べます。

●グループコミットの修正とは何か
　耐障害性を高めるために、トランザクションがコミットされると、その更新情報がREDOログファイルに同期書き込みされます。このことをコミット時同期書き込みと呼びます。ディスクへの同期書き込みはコストの高い処理で、バッテリーバックアップつきライトキャッシュが搭載されていてもせいぜい毎秒10000回程度しかできません。高速なSELECTなら毎秒数万回くらいは実行できますから、fsyncはパフォーマンスの阻害要因になります。
　そこでInnoDBでは、複数のスレッドから同期書き込みが行なわれた場合は、まとめ上げて少数回数の同期書き込みを行なう機能を搭載しています。これをグループコミットと呼びます。グループコミットは、ほとんどのRDBMSが機能として持っています。
　一方MySQL5.0から、2相コミットの機能が搭載されました。2相コミットは、異なるサーバー間のトランザクションの整合性を保つという(あまり使われない)機能がありますが、MySQLでは異なる(トランザクション対応の)ストレージエンジンおよびバイナリログ間でのトランザクションの整合性を保つという上で、インスタンス1個の環境でも意味があります。ほとんどのケースで「バイナリログとInnoDB」の整合性を保つために使われます。
　たとえば、「バイナリログに書き込んだ後、InnoDBログファイルに書き込む前にクラッシュした」という状況が起きたとします。2相コミットでない場合、リカバリするとバイナリログにだけ書かれていてInnoDBには書かれていないデータが存在することになります。それはスレーブにも転送されるので、「スレーブには存在するけどマスターには無いデータがある」という状態になってしまいます。2相コミットを使うと、「InnoDBログファイルにprepareする→バイナリログに書き込む→InnoDBログファイルにcommitする」という動作になります(2相とはprepare、commitの2フェーズという意味)。「バイナリログに書き込んだ後、InnoDBログファイルに書き込む前にクラッシュした」場合には、事前にprepareで書き込んだデータを用いてリカバリできます。また「バイナリログに書き込む前にクラッシュした」場合には、prepareしたものを無かったことにできます。いずれの場合でも、バイナリログとInnoDB間を整合性のある状態にできます。
　InnoDBでの2相コミットの実装ですが、従来はこうなっていました。

mutex(prepare_commit_mutex)確保
  prepareのためにInnoDBログに書き、同期書き込みする
  バイナリログに書く
  commitのためにInnoDBログに書き、同期書き込みする
mutex解放

　mutex内は同時に1個のスレッドしか動くことができないので、従来型のアプローチだと、ファイルへの同期書き込みがシリアライズされます。つまり100個のスレッドが同時にコミットしたら、prepareのために100回のfsync、commitのために100回のfsyncの計200回が呼ばれます。つまりグループコミットが崩れてしまっていたのです。
　一方、InnoDB Plugin 1.0.4ではこうなりました。

prepareのためにInnoDBログに書き、同期書き込みする
mutex確保
  バイナリログに書く
  commitのためにInnoDBログに書く
mutex解放
InnoDBログを同期書き込みする

　InnoDB Plugin1.0.4のアプローチでは、同期書き込みをクリティカルセクションの外に出しています。そのため、複数のトランザクションからの同期書き込み命令をまとめあげるグループコミットが機能します。これによって、fsyncの実行回数を大幅に削減できます。以下は、fsyncの実行された回数を示す、Innodb_data_fsyncsの推移を示したものです。

　5.1.37+Builtin(support_xa=1)では、同時接続数に関係なく、1トランザクションあたり2回のfsyncが発生しています。innodb_support_xa=0の場合は1トランザクションあたり1回です。いずれも、グループコミットが効いていないことが分かります。最初のグラフと重ね合わせると、どちらも1秒あたりのfsync回数が10,000前後になっていますが、これは一般的なHDD(+ライトキャッシュ)の限界に近い値で、fsyncがボトルネックになっていると考えられる結果です。一方InnoDB Plugin 1.0.4の場合は、接続数の増加に対して劇的にInnodb_data_fsyncsの増加量が減っています。たとえば30接続(innodb_support_xa=1)のときは、10万トランザクションに対して増加量が200251から26092と、87%も減っています。よってグループコミットが効いていることが分かります。
　また、クリティカルセクションの中でバイナリログとInnoDBログの両方に書いている(同期書き込みはしていない)ので、「InnoDBログファイルへの書き込み順と、バイナリログへの書き込み順が一致しなくなる」という問題が起きることはありません。
　Prepareの順序は保証されないではないか、と言われるかもしれませんが、これは問題ありません。障害のタイミングによっては、prepareされたけれどもcommitされていないトランザクションが存在しえますが、これはそのままではアプリケーションからは見えません。MySQLでは、リカバリ時にはバイナリログをまず読んで、トランザクションID(xid)をピックアップします。次にストレージエンジン(InnoDB)を読んで、prepareされているけれどcommitされていないxidを特定します。そうして特定されたxidを、バイナリログへのコミット順にリカバリします。この結果、最終的にはバイナリログの書き込み順序とInnoDBログファイルへの確定(コミット)順序が一致します。
　というわけで、データ整合性の問題を引き起こさずに、スケーラビリティを上げることができました。バイナリログを同期書き込みする(sync-binlog=1)場合は依然として遅くなってしまいますが、デフォルトの0であれば上図のように非常に高速になります。興味のある方は試してみてください。

全テーブルの統計情報をサイズ順に一覧表示する

　MySQLにおいて、テーブルサイズやインデックスサイズ、レコード数、平均レコード長などの統計情報を知る上でshow table statusは定番です。ただ雑多な表示項目も多いので、たくさんのテーブルの統計を見る場合、必要な情報だけを返したいことは多いです。また全テーブルのうち、どのテーブルが一番大きいのかを知りたいとか、サイズが多い順に一覧表示したいとか、一目で分かるような情報がほしいことも多いです。
　こういうときはinformation_schema.tablesを使うと便利です。以下の例では、appデータベースの全テーブルについて、「テーブルサイズ+インデックスサイズ」の大きい順に、ストレージエンジン、レコード数、平均レコード長、テーブルサイズ(MB)、インデックスサイズ(MB)などを返しています。

use app;
select
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
floor((data_length+index_length)/1024/1024) as allMB,
floor((data_length)/1024/1024) as dMB,
floor((index_length)/1024/1024) as iMB
from information_schema.tables
where table_schema=database()
order by (data_length+index_length) desc;

+------------+--------+----------+------+-------+------+------+
| table_name | engine | tbl_rows | rlen | allMB | dMB  | iMB  |
+------------+--------+----------+------+-------+------+------+
| stock      | InnoDB |  9999831 |  381 |  3639 | 3639 |    0 |
| order_line | InnoDB | 28493701 |   95 |  2593 | 2593 |    0 |
| customer   | InnoDB |  2972004 |  673 |  2130 | 1909 |  221 |
| orders     | InnoDB |  3000309 |   60 |   301 |  171 |  129 |
| history    | InnoDB |  2997455 |   82 |   236 |  236 |    0 |
| new_order  | InnoDB |   905600 |   37 |    48 |   32 |   15 |
| item       | InnoDB |   100160 |  110 |    10 |   10 |    0 |
| district   | InnoDB |      917 |  178 |     0 |    0 |    0 |
| warehouse  | InnoDB |      100 |  163 |     0 |    0 |    0 |
+------------+--------+----------+------+-------+------+------+
9 rows in set (0.84 sec)

　自分は、ここをスタートラインにして、怪しいテーブルに焦点をあててチューニングをすることが多いです。ただしshow table statusと同様、数値項目は概算値なので、毎回微妙に値が変わることに注意してください。

マスターInnoDB、スレーブMyISAMが勧められない理由

　MySQLにおいて、マスターをInnoDBにして、スレーブをMyISAMにすると幸せになれるという主張をよく聞くことがあります。マスターは耐障害性の高いInnoDBにする一方で、スレーブは耐障害性が低くても大丈夫なので、InnoDBのかわりに高速とされるMyISAMを使えば、可用性と性能の両方をバランス良く実現できる、という考えです。
　しかし、多くの場合これで幸せになることはできません。マスターとスレーブでストレージエンジンを合わせた方が無難です。その理由を以下に示します。

●MyISAMはテーブルロックになる
　マスターへの更新結果はバイナリログに更新系SQL文として書かれ、スレーブのI/Oスレッドによってリレーログとして同じフォーマットで記録され、スレーブのSQLスレッドによってその更新系SQL文がそのまま実行されます。この更新系SQL文は、当然ながらスレーブに対して発行されるSELECT文と競合します(5.1のオプションである、行ベースのバイナリログでも同様)。MyISAMにはconcurrent insertという、INSERTと並行でSELECTを処理できる機能がありますが、テーブルが虫食い状態になっていると並行できないなどの制限もあります。またUPDATEとSELECTはばっちり競合してしまいます。
　集計系の処理のために数十分クラスのバッチ系のSELECT文を実行すると、多くの場合レプリケーションも数十分止まってしまいます。InnoDBは行ロックで、かつMVCCの恩恵でSELECTが更新系SQL文によってロックされないため、こうした問題を防げます。

●SQLスレッド停止時の対処が面倒になる
　InnoDBはトランザクション対応で、MyISAMは非対応です。トランザクションのACID特性の1つA特性である、ロールバックができるという機能をMyISAMは持っていません。InnoDBは持っています。この違いがあるため、レプリケーションにおいてSQLスレッドが止まったときにちょっと面倒なことになります。
　以下のようなSQL文をマスターで実行し、バイナリログに記録されたものとします。

1. START TRANSACTION;
2. INSERT
3. INSERT
4. INSERT
5. COMMIT;

　スレーブでも同じSQL文が実行されますが、ここで4でなんらかのエラー(一意制約違反など)が起きてスレーブ(SQLスレッド)が止まったとします。この場合、エラーを修正してSQLスレッドを再開することになりますが、このとき4からではなく、1から再スタートするのです。バイナリログはトランザクションの存在を認識していて、トランザクションの開始時点から再スタートするためです。しかしMyISAMはトランザクション対応ではないので、SQLスレッド停止前の時点ですでに2、3のINSERT結果が確定しています。1から再開すると、2、3はもう一度実行されるため、重複したINSERTが行なわれてしまうことになります。そのため、SET GLOBAL SQL_SLAVE_SKIP_COUNTERによって、4の前(あるいは後)まで明示的に飛ばしてあげる必要があります。

●スレーブ→マスターの昇格が難しくなる
　マスターを1台で運用している場合、マスターの障害時にはスレーブを昇格させる運用を取ることになります。しかしスレーブはMyISAMなので、InnoDBだった場合とは耐障害性や性能面で大きく傾向が変わる(遅くなる)可能性があります。MyISAMはテーブルロック、InnoDBは行ロックということで、多数のクライアントから並列で更新が行なわれるマスターでは、多くの場合InnoDBの方が良い性能が得られます。またInnoDBはクラスタ索引という特徴があるため、更新性能だけでなく、主キー検索についてもInnoDBの方が速くなる傾向にあります。InnoDBは検索性能も含めてすでに十分速いということも付け加えておきます。MySQL5.0以降はInnoDBのCPUスケーラビリティが大きく改善されていますし、MySQL5.4(beta)ではCPUスケーラビリティに加えてI/O性能も大きく改善されています。

　スレーブのストレージエンジンをマスターと分けるとしたら、これらのデメリットを差し置いてもメリットが上回る場合でしょう。例えばマスターをBlackholeにして、マスターの更新性能を上げるという形は良く取られます。またKickfireのようなDWH系に特化したストレージエンジンをスレーブに置いて、分析専用に使うことなども考えられます。

勉強会「MySQL Hackingの手引き」を終えて

　昨日は、グリー勉強会にて「MySQLハッキングの手引き」というテーマで発表をしました。資料とデモに使用したソースコードやビルドスクリプト等はこちらに公開しています(サンプルプログラムのコンパイルにはソースからビルドしたMySQL5.1以降が必要)。声をかけてくださったグリーの一井さんや、会場準備など諸手続きを行なってくださったグリーのスタッフの方々、参加された皆さまありがとうございました。

●参加者数の意外な多さ
　無料の勉強会とはいえ、このようなマニアックなテーマで、60名定員のところに150名を超える応募が来たというのは驚きました。相当数の方が抽選落ちしてしまったのは残念でしたが、評判が良ければ似たようなテーマでのセミナーをまたどこかで行ないたいと考えています。
　自分はMySQLのコンサルティングという、MySQLの使い手としての専門職(パフォーマンスチューニングとか運用管理とか)に従事しています。過去に発信した記事/書籍やセミナー等はほぼ例外なく使い手としての知識と経験をベースにしています。ただ、たまにですが客先等で挙がった要望リストをもとにパッチを作ってコントリビュートしたり、コンサルティング案件としてMySQL本体を拡張(あるいは拡張したコードをレビューしたり)といった開発系の仕事をすることがあります。そうした開発系の話をしたいと思っていたところに、いい機会で声をかけてもらった、というのが経緯です。こうしたテーマでのプレゼンはほとんど経験がないので、デモとスライドは手探り状態で準備したところがありました。60分予定のところが90分かかるなど時間管理もなってない状況でしたが、フィードバックを得て今後の改善につなげていきたいと思います。懇親会での反応を見ると、興味を持っている方が少なからずいるのだろうと感じました。また初対面の方が多く刺激的でした。
　使う側の立場でMySQLを見ると、簡単に使える方が良いに決まっています。ハッキングなどという手間のかかることはやらないに越したことはありません。しかし今回のデモを通じて、「個人的な興味」という枠を超えて、普通に効果的な場面が出てくる可能性があることも感じていただけたのではないかと思います。MySQLはプラグイン化の流れを推し進めており、本体の拡張に比べるとずっと手軽にプラグインを作ることができます。今回来ていた方の中にはC/C++を非常に得意としている方も少なからずいたので、kazuhookuさんのQ4Mや斯波さんのSpiderのようなゼロからストレージエンジンを作る、とはいわないまでも、実用的なUDFなどは結構出てくるかもしれないな、と期待しています。


●KVSとしてのRDBMSの可能性
　今回の勉強会で、1つの可能性として「Key Value StoreのAPIのような感覚でダイレクトにテーブルにアクセスする」という形を提示しました。1年ほど前にkazuhookuさんがポストした「MySQL (InnoDB) に直接アクセスしてタイムライン処理を高速化する話」をより簡易化したものです。一般化すると、1回のHTTPリクエストの処理にあたり、多数回の(効率の良い)SELECT文を実行しなければ結果を返すことができない、というタイプの処理を、高速化するための実装、ということになるでしょうか。この手の処理は、ストアドプロシージャを使うと、ネットワークアクセス回数を減らせるため高速化が可能です。しかしMySQLはオープンソースな上に、汎用的なストレージエンジンAPIが存在するため、C/C++でストレージエンジンAPIを直接呼び出すようなコードを書いて、それをUDFなどのプラグインから呼ぶ、といったことができます。本来、ストレージエンジンAPIは、それを実装する側(Q4M/Spiderのように、APIを実装する側)のために用意されているのですが、APIを呼ぶロジックの初期化/解放ロジックをきちんと書けば、呼ぶ側のコードを書くことも不可能ではありません。デモで示したパフォーマンス測定の数字を掲載しておきます(レコードはすべてInnoDBバッファプールにキャッシュされている状態。再現手順は資料の方に含まれています)。CPUネックの処理で、Intel Xeon X5560 Nehalem 2.80GHz*16コアのマシンでの結果です。

SQL文：
$ super-smack smack1.smack 20 1
Query Barrel Report for client c1
connect: max=15ms min=3ms avg= 5ms from 20 clients
q_per_s
113701.80
100回で1リクエスト→1137リクエスト/秒

ストアド：
$ super-smack smack1.smack 20 1
Query Barrel Report for client c1
connect: max=9ms min=3ms avg= 4ms from 20 clients
q_per_s
1771.54
1回で1リクエスト→1772リクエスト/秒

UDF：
$ super-smack smack1.smack 20 1
Query Barrel Report for client c1
connect: max=10ms min=3ms avg= 5ms from 20 clients
q_per_s
14983.40
1回で1リクエスト→14983リクエスト/秒

　ストアドよりも8.5倍も高速化したことに驚いた方も多かったのではないでしょうか。MySQLはRDBMSの中でも十分に速いとされていますが、(CPUネックの状況になれば)KVSに比べると圧倒的に遅くなってしまうことをご存知の方も多いでしょう。これは、MySQLが長いSQL文字列の構文解析はもちろんのこと、毎回の実行計画の作成(MySQLは実行計画のキャッシュ機構が無い)や文字列コピーの多用といった、CPUコストの高い処理を結構行なっているということにも大きな要因があります。こうした処理をことごとくすっ飛ばしてダイレクトにストレージエンジンにアクセスすれば、KVSほどではないにしても十分に高速なスループットを出すことができます。もちろん副作用も多く、あまり想定されていない使われ方なので前例がまるで無い(ドキュメントも無いに等しい)とか、ちょっとバグを残してしまったらmysqldごと落ちてしまうかもしれないとか、バージョン間の互換性が低いとか、実際にやってみると苦労が絶えないだろうと思いますが。。
　「RDBMSはSQLがあるから遅い」のであれば、「内部的にはSQLの実行経路を迂回した高速アクセスをすれば良い」というような裏技的な思考は個人的には好きです。今回のデモのように、クライアントから見てアクセス手段が透過的であれば、背後で何が行なわれていようとクライアント側は普通にSQL文(UDF)を呼べばいいだけなので影響がありません。RDBMSにはB+Treeインデックスがありますから、KVSによっては難しいソート済みの結果を返すことも簡単にできます。データ型の機構が強力な点(値チェックができる等)も魅力になるケースがあるでしょう。SQLを標準インターフェイスとしつつ、ごく一部の「非常に高速に処理する必要のあるクエリ」を処理するために使いやすい高速APIを用意し、しかもアプリケーションからはこれらを特別な意識をせずに使い分けることができる(UDFを明示的に呼ぶ程度でOK)、というアーキテクチャは、次の世代のRDBMSの1形態になりうるだろうと考えています。トップページなど大量のアクセスが来るページにおいて、複雑なクエリを実行しないと結果を返せない項目がある、といったケースでは面白いのではないでしょうか。
　あまり見慣れない手法でもあるので、いろいろな方と意見交換したいと思っています。ごく最近Twitterも始めましたので、気軽にフォローしてくださればと思います。