米大統領選で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でもこうした重要なシステムを安定稼動できる(逆に無ければ商用製品でも困難)、ということを示す良いキーノートだったと思います。