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に戻すことが可能です。追加インストールやインストールし直し等が必要ないという手軽さが魅力です。
2009年10月27日火曜日
2009年10月26日月曜日
Okyuu.comからインタビューを受けました
先日カカクコムさんが運営しているサイト「Okyuu.com」からインタビューを受け、その記事が公開されました。
私は幼少の頃から技術者魂全開、というキャラとは程遠く、気づいたらこの業界でDB技術系の仕事をしていた、という程度なのですが、趣味と仕事が完全に一致したという恵まれた方はむしろ少数派で、多くの方は生活のために(それと多少の興味と一致して)この業界で仕事をしているのだと思います。この業界はコードを書くのが趣味でなければ生きていけない、というプレッシャーをひしひしと感じさせますが、そうでない人にとっても活躍の場はあるのだ、という安心感を持っていただけると幸いです。
インタビュー記事について、少しだけ補足をしたいと思います。
・お金を払うユーザーを大切にするのは当然ですが、無償で使うユーザーを無視しているわけではありません。そもそも無償で使うユーザーを無視するようではOSSビジネスは成立しません。多くの利用者が試し、時に地雷を踏みそれを修復するサイクルを繰り返すことで、ようやくお金を払っても良いかなという品質に達すると考えています。誰も使っていないプロダクト(地雷を踏むリスクが高い)にお金を払うユーザーは、昨今では非常に少ないのではないでしょうか。
・第3者企業がOSS製品をさんざん利用した挙句、ちょっと機能を追加してクローズドソースにして「自社製品です」と言い張って販売するのは、私は「邪道」のビジネスだと考えています。もちろん程度問題で、ユーティリティライブラリ程度なら全然いいと思いますが、コアの機能がもろにOSSを使っているのなら、それを拡張してクローズドソースにして販売するくらいなら本家にフィードバックしろよ、と思うわけです。
・OSSに貢献する道は、パッチを書くだけではありません。たとえば新バージョンのバグを見つけてバグレポート上で再現手順を報告するというのも立派な貢献です。こうしたバグ報告によって製品の品質は徐々に上がっていきます。その意味では、会社としてはお金を出せないしパッチを書くほどのスキルは無いけどOSSに貢献したい、というような方は、できるだけ新しいバージョンを積極的に使って、バグを見つけて報告するのが良いのではないかと思います。ただし、セキュリティ上の脆弱性に関しては、バグフィックスよりも先にBlog等で発信して一般に認知されたりすると、攻撃の対象になりかねないので気をつけて頂きたいと思います(MySQLのバグレポートでは、脆弱性の問題については、報告を受けた後に一般ユーザーが閲覧できないように権限設定されます)。
私は幼少の頃から技術者魂全開、というキャラとは程遠く、気づいたらこの業界でDB技術系の仕事をしていた、という程度なのですが、趣味と仕事が完全に一致したという恵まれた方はむしろ少数派で、多くの方は生活のために(それと多少の興味と一致して)この業界で仕事をしているのだと思います。この業界はコードを書くのが趣味でなければ生きていけない、というプレッシャーをひしひしと感じさせますが、そうでない人にとっても活躍の場はあるのだ、という安心感を持っていただけると幸いです。
インタビュー記事について、少しだけ補足をしたいと思います。
・お金を払うユーザーを大切にするのは当然ですが、無償で使うユーザーを無視しているわけではありません。そもそも無償で使うユーザーを無視するようではOSSビジネスは成立しません。多くの利用者が試し、時に地雷を踏みそれを修復するサイクルを繰り返すことで、ようやくお金を払っても良いかなという品質に達すると考えています。誰も使っていないプロダクト(地雷を踏むリスクが高い)にお金を払うユーザーは、昨今では非常に少ないのではないでしょうか。
・第3者企業がOSS製品をさんざん利用した挙句、ちょっと機能を追加してクローズドソースにして「自社製品です」と言い張って販売するのは、私は「邪道」のビジネスだと考えています。もちろん程度問題で、ユーティリティライブラリ程度なら全然いいと思いますが、コアの機能がもろにOSSを使っているのなら、それを拡張してクローズドソースにして販売するくらいなら本家にフィードバックしろよ、と思うわけです。
・OSSに貢献する道は、パッチを書くだけではありません。たとえば新バージョンのバグを見つけてバグレポート上で再現手順を報告するというのも立派な貢献です。こうしたバグ報告によって製品の品質は徐々に上がっていきます。その意味では、会社としてはお金を出せないしパッチを書くほどのスキルは無いけどOSSに貢献したい、というような方は、できるだけ新しいバージョンを積極的に使って、バグを見つけて報告するのが良いのではないかと思います。ただし、セキュリティ上の脆弱性に関しては、バグフィックスよりも先にBlog等で発信して一般に認知されたりすると、攻撃の対象になりかねないので気をつけて頂きたいと思います(MySQLのバグレポートでは、脆弱性の問題については、報告を受けた後に一般ユーザーが閲覧できないように権限設定されます)。
2009年10月15日木曜日
スワップサイズをゼロにしてはいけない
先月発売された書籍「Linux-DBシステム構築/運用入門」は、なかなか上々の売れ行きとなっているようです。Amazonではしばらく「1-2ヶ月待ち」の状態が続いてしまっていたのですが、最近になってようやく解消され、容易に入手できるようになっているようです。Amazonの在庫切れ問題がひと段落したところで、これからは書籍のサポート的な情報を書いていくことにします。
まず、本書を購入された皆さまありがとうございました。結構な数の方がBlogやTwitter等で、この本をほめてくださっていることに大変感謝しています。まだ本自体の認知度が低い(存在自体を知らない顧客も多い)ので、普及活動をしつつ、これからも読者の期待に応えられる記事を書いていきたいと思っています。
最初は、よく見かけることの多い「メモリ管理」の話題を取り上げようと思います。第12章では、メモリ管理とスワップ領域に関する解説をしています。64ビット機と数十GBクラスの大容量メモリを搭載するのが現在のトレンドになりました。ディスクI/Oの速度はメモリアクセスに比べて極端に落ちますから(第10章参照)、できるだけ多くのデータをメモリ上に置いて高速化をはかるというのがDBチューニングの定石です。多くの場合、メモリを増設することで参照性能だけでなくINSERTなどの更新性能も上がるというのは第9章などで触れている通りです。またダイレクトI/Oを使ってキャッシュ効率を上げることも効果的です。InnoDBならinnodb_flush_method=O_DIRECTを指定すれば良いです。
メモリ管理の設定でよく見るのが、スワップサイズをゼロにしているケースです。スワップサイズをゼロにすれば、スワップが発生しませんが、これは問題があります。本章で書いているように、プロセスが実メモリを使い切ってしまった場合に、スワップできないのでOOM Killerによって殺されてしまいます。また、このときにハングアップ状態がしばらく続き、挙句の果てに異常終了してしまうためです。単に再起動したとしても、ダイレクトI/Oであればプロセス空間のキャッシュだけでなく、ファイルシステムキャッシュ上に何も載っていない状態からスタートするため、ディスクI/Oが多発して性能が一気に低下します。もちろんDRBD等によるアクティブ/スタンバイ構成でフェイルオーバーするような場合も、フェイルオーバー先にはキャッシュに何も乗っていないため、同じように性能が落ちます。スワップサイズが小さい場合も、それを使い切れば同様にOOM Killerに殺されてしまいます。このため、「スワップサイズをきちんと(物理メモリの半分くらい)取り、ダイレクトI/Oを使い、プロセスよりもファイルシステムキャッシュが優先的にスワップされるようにvm.swappinessをゼロにする」という方法を紹介したというわけです。もちろん、スワップが発生するというのはディスクI/Oが頻発するという意味なので、それを避けるように実メモリの範囲内におさまるようにチューニングするのが大切です。
なお、MyISAMテーブルなど、ダイレクトI/Oをサポートしていないストレージエンジンでは、RDBMSのプロセスサイズが小さくなり、ファイルシステムキャッシュのサイズが大きくなる傾向にあります。この場合、プロセスだけでメモリ空間を使い切る(OOM Killerによって殺される)ことはまず無いでしょう。
まず、本書を購入された皆さまありがとうございました。結構な数の方がBlogやTwitter等で、この本をほめてくださっていることに大変感謝しています。まだ本自体の認知度が低い(存在自体を知らない顧客も多い)ので、普及活動をしつつ、これからも読者の期待に応えられる記事を書いていきたいと思っています。
最初は、よく見かけることの多い「メモリ管理」の話題を取り上げようと思います。第12章では、メモリ管理とスワップ領域に関する解説をしています。64ビット機と数十GBクラスの大容量メモリを搭載するのが現在のトレンドになりました。ディスクI/Oの速度はメモリアクセスに比べて極端に落ちますから(第10章参照)、できるだけ多くのデータをメモリ上に置いて高速化をはかるというのがDBチューニングの定石です。多くの場合、メモリを増設することで参照性能だけでなくINSERTなどの更新性能も上がるというのは第9章などで触れている通りです。またダイレクトI/Oを使ってキャッシュ効率を上げることも効果的です。InnoDBならinnodb_flush_method=O_DIRECTを指定すれば良いです。
メモリ管理の設定でよく見るのが、スワップサイズをゼロにしているケースです。スワップサイズをゼロにすれば、スワップが発生しませんが、これは問題があります。本章で書いているように、プロセスが実メモリを使い切ってしまった場合に、スワップできないのでOOM Killerによって殺されてしまいます。また、このときにハングアップ状態がしばらく続き、挙句の果てに異常終了してしまうためです。単に再起動したとしても、ダイレクトI/Oであればプロセス空間のキャッシュだけでなく、ファイルシステムキャッシュ上に何も載っていない状態からスタートするため、ディスクI/Oが多発して性能が一気に低下します。もちろんDRBD等によるアクティブ/スタンバイ構成でフェイルオーバーするような場合も、フェイルオーバー先にはキャッシュに何も乗っていないため、同じように性能が落ちます。スワップサイズが小さい場合も、それを使い切れば同様にOOM Killerに殺されてしまいます。このため、「スワップサイズをきちんと(物理メモリの半分くらい)取り、ダイレクトI/Oを使い、プロセスよりもファイルシステムキャッシュが優先的にスワップされるようにvm.swappinessをゼロにする」という方法を紹介したというわけです。もちろん、スワップが発生するというのはディスクI/Oが頻発するという意味なので、それを避けるように実メモリの範囲内におさまるようにチューニングするのが大切です。
なお、MyISAMテーブルなど、ダイレクトI/Oをサポートしていないストレージエンジンでは、RDBMSのプロセスサイズが小さくなり、ファイルシステムキャッシュのサイズが大きくなる傾向にあります。この場合、プロセスだけでメモリ空間を使い切る(OOM Killerによって殺される)ことはまず無いでしょう。
2009年9月12日土曜日
新書籍「Linux-DBシステム構築/運用入門」
Linux上で「高速で、落ちない」DBサーバーを構築するための技術解説をした書籍を出版します。タイトルはストレートに「Linux-DBシステム構築/運用入門」です。
9月17日発売ですが、ジュンク堂など一部の書店ではすでに入荷しているそうなので、見かけたらぜひ読んでみてください。章構成は以下の通りです。
第1章 論理ボリュームマネージャ(LVM)を活用する
第2章 Heartbeatによるクラスタ環境の構築
第3章 DRBDによるネットワークミラーリング(前編)
第4章 DRBDによるネットワークミラーリング(後編)
第5章 高可用DBサーバーの構築
第6章 現場で使われる高可用構成
第7章 DBサーバーのパフォーマンス概論
第8章 インデックスのチューニング(前編)
第9章 インデックスのチューニング(後編)
第10章 DBサーバーのハードウェア選定
第11章 SSDの効果とアプリケーションへの影響
第12章 メモリ管理とスワップ領域の制御
第13章 ファイルシステムとI/Oスケジューラ
第14章 Linux-DBサーバーにおける負荷テストの心得
本書籍は、DBマガジンでの連載記事「Linux-DBサーバー構築入門」と一部の特集記事がベースになっています。連載中に書くことのできなかった、SSDの章とメモリの章については新規に書き下ろしています。それ以外の章についても、書籍としての一貫性や、最近のトレンドに追従できているかといった観点から、大幅に加筆修正を行いました。
大きく分けて、HA構成編(1章-6章)とパフォーマンス編(7章-14章)の2本立てとなっています。Linux上にRDBMSをインストールして使うのは当たり前に行われているので、そこから一歩進んで、HA構成をいかに安価に構築し、かつパフォーマンスを出していくかという点に焦点を当てています。
主に想定している読者としては、Linux上でのRDBMSのインストール方法を知っているという前提知識があって、高可用環境の構築や高パフォーマンスのための技術を学びたいと考えている方になります。相当丁寧に書いたつもりなので、「入門」のタイトル通り、これから学ぼうと考えている方に読んでほしいです。一方で随所で踏み込んだ解説をしているので、中～上級者の方にも得るものがあると思います。
●HA構成編の内容
HA構成編では、LVMについて軽く流した後、もっとも基本形と言えるアクティブ/スタンバイ構成から解説を始めています。まず理論をしっかり理解するという観点から、アクティブ/スタンバイ構成を正しく組むためのハードウェア設計や、監視経路の整備、Heartbeat、DRBD、monといった各ソフトウェアの存在意義の解説をしています。その上で、具体的なアクティブ/スタンバイ構成の組み方や、パラメータ設定上のポイント、フェイルオーバーテストの方法、パフォーマンス上の考慮ポイントなどをカバーしています。HA環境はただ構築して終わりではなく、その後も使い続けるものですから、トラブル時に迅速に原因を切り分けて復旧するためにも、より本質的な理解を得たいところです。特にDRBDについては、dopdやActivity Log、スプリットブレイン自動検知機能の存在意義や動作原理など、ほとんど語られることが無いけれども重要な点も解説しています。
最近のトレンドでは、ハードウェアのうち1台が無駄になってしまい、かつダウンタイムが長くなってしまうアクティブ/スタンバイ構成から一歩進んで、どちらもアクティブ状態になるアクティブ/アクティブ構成を採用するケースが増えてきています。アクティブ/アクティブ構成については、本書ではMySQLを例にとって、レプリケーション、MySQL Clusterなどによってどんな構成が取れるかを示しています。一般的に推奨されないと考えられているマスター/マスター型のレプリケーションが使われるのはなぜか、データセンター間のレプリケーション構成は取れるのかといった点から、次期バージョンで期待を集めているセミ同期レプリケーションの可能性と注意事項などもカバーしています。第6章はMySQLに特化した章になっていますが、ほかの章についてはできるだけMySQLに依存しないように配慮しています。
特にアクティブ/スタンバイ構成とアクティブ/アクティブ構成は、ソフトウェアの構成要素がまるで変わってくるので、特に理解が難しいポイントでもあるのですが、全体像を理解できるように配慮したつもりです。
●パフォーマンス編の内容
パフォーマンス編では、「アプリケーション全体のアーキテクチャ」「RDBMS単体チューニング(主にインデックス設計)」「Linux/ハードウェア周り」「負荷テスト上のポイント」という観点から記述しました。
目標スループットを達成する上で「1台のDBサーバーでは十分でないケース」が増えてきています。このときの対処手段としては、レプリケーション構成を用いたり、アプリケーションパーティショニングを組んだり、memcachedのようなキャッシュサーバを導入するといった様々な方法があります。これらは、それぞれ向き/不向きがありますが、本書を読むことでその特徴がより深く理解できるのではないかと思います。
インデックスについては2章構成としており、前半は参照性能を最大化するためのインデックス設計、後半はINSERT性能のダウンを緩和するためのさまざまなテクニックをカバーしています。インデックスがあると更新性能が落ちるのはよく知られた話ですが、それがどの程度になるのか、データ量が増えると更新性能が落ちるのはなぜか、またハードウェア環境やテーブルパーティショニングなどによってどのように変わるのか、といった定量的な指標を示しており、参考になることでしょう。
Linuxという観点ではメモリ制御/スワップ、メモリアロケータ、I/Oスケジューラ、ファイルシステムといった、パフォーマンス上重要なポイントをカバーしています。スワップサイズをゼロにすると何がまずいのか、接続パラメータにメモリ領域を割り当てすぎるとどんな副作用があるのか、cfq/deadline/noop/anticipatoryという4種類のI/Oスケジューラにはどんな違いがあるのか、といった、DB管理者であればひととおりおさえておきたい点をきっちりカバーしたつもりです。
ハードウェアという観点では、HDDとRAID構成上のポイントやライトキャッシュの重要性、そしてSSDについてカバーしています。SSDには1章を丸々割いており、SSD製品選定上のポイントや、さまざまな条件によるHDDとSSDの性能差の紹介、ファイルによって適正がどう変わるかを見ていきます。そして、SSDが今後普及することで、RDBMS自体やアプリケーションにどのような影響があるかを考察していきます。SSDの章は、先月発売のDBマガジン誌の記事とほぼ同様なのですが、本書では字数制限が基本的に無いので、DBマガジン誌では書ききれなかった内容についても触れています。
最後に、これらの要素技術を踏まえていかに実戦に活かすかという意味で負荷テストのケーススタディを紹介しています。
MySQLを使われている方はもちろんのこと、そうでない方でもLinux上でDB構築/運用をする方であれば、必ずや得るものがあるだろう思います。
本Blogでは、発売後しばらくの間、本書の内容を補完する意味でのサポート記事をいくつか投稿する予定です。
9月17日発売ですが、ジュンク堂など一部の書店ではすでに入荷しているそうなので、見かけたらぜひ読んでみてください。章構成は以下の通りです。
第1章 論理ボリュームマネージャ(LVM)を活用する
第2章 Heartbeatによるクラスタ環境の構築
第3章 DRBDによるネットワークミラーリング(前編)
第4章 DRBDによるネットワークミラーリング(後編)
第5章 高可用DBサーバーの構築
第6章 現場で使われる高可用構成
第7章 DBサーバーのパフォーマンス概論
第8章 インデックスのチューニング(前編)
第9章 インデックスのチューニング(後編)
第10章 DBサーバーのハードウェア選定
第11章 SSDの効果とアプリケーションへの影響
第12章 メモリ管理とスワップ領域の制御
第13章 ファイルシステムとI/Oスケジューラ
第14章 Linux-DBサーバーにおける負荷テストの心得
本書籍は、DBマガジンでの連載記事「Linux-DBサーバー構築入門」と一部の特集記事がベースになっています。連載中に書くことのできなかった、SSDの章とメモリの章については新規に書き下ろしています。それ以外の章についても、書籍としての一貫性や、最近のトレンドに追従できているかといった観点から、大幅に加筆修正を行いました。
大きく分けて、HA構成編(1章-6章)とパフォーマンス編(7章-14章)の2本立てとなっています。Linux上にRDBMSをインストールして使うのは当たり前に行われているので、そこから一歩進んで、HA構成をいかに安価に構築し、かつパフォーマンスを出していくかという点に焦点を当てています。
主に想定している読者としては、Linux上でのRDBMSのインストール方法を知っているという前提知識があって、高可用環境の構築や高パフォーマンスのための技術を学びたいと考えている方になります。相当丁寧に書いたつもりなので、「入門」のタイトル通り、これから学ぼうと考えている方に読んでほしいです。一方で随所で踏み込んだ解説をしているので、中～上級者の方にも得るものがあると思います。
●HA構成編の内容
HA構成編では、LVMについて軽く流した後、もっとも基本形と言えるアクティブ/スタンバイ構成から解説を始めています。まず理論をしっかり理解するという観点から、アクティブ/スタンバイ構成を正しく組むためのハードウェア設計や、監視経路の整備、Heartbeat、DRBD、monといった各ソフトウェアの存在意義の解説をしています。その上で、具体的なアクティブ/スタンバイ構成の組み方や、パラメータ設定上のポイント、フェイルオーバーテストの方法、パフォーマンス上の考慮ポイントなどをカバーしています。HA環境はただ構築して終わりではなく、その後も使い続けるものですから、トラブル時に迅速に原因を切り分けて復旧するためにも、より本質的な理解を得たいところです。特にDRBDについては、dopdやActivity Log、スプリットブレイン自動検知機能の存在意義や動作原理など、ほとんど語られることが無いけれども重要な点も解説しています。
最近のトレンドでは、ハードウェアのうち1台が無駄になってしまい、かつダウンタイムが長くなってしまうアクティブ/スタンバイ構成から一歩進んで、どちらもアクティブ状態になるアクティブ/アクティブ構成を採用するケースが増えてきています。アクティブ/アクティブ構成については、本書ではMySQLを例にとって、レプリケーション、MySQL Clusterなどによってどんな構成が取れるかを示しています。一般的に推奨されないと考えられているマスター/マスター型のレプリケーションが使われるのはなぜか、データセンター間のレプリケーション構成は取れるのかといった点から、次期バージョンで期待を集めているセミ同期レプリケーションの可能性と注意事項などもカバーしています。第6章はMySQLに特化した章になっていますが、ほかの章についてはできるだけMySQLに依存しないように配慮しています。
特にアクティブ/スタンバイ構成とアクティブ/アクティブ構成は、ソフトウェアの構成要素がまるで変わってくるので、特に理解が難しいポイントでもあるのですが、全体像を理解できるように配慮したつもりです。
●パフォーマンス編の内容
パフォーマンス編では、「アプリケーション全体のアーキテクチャ」「RDBMS単体チューニング(主にインデックス設計)」「Linux/ハードウェア周り」「負荷テスト上のポイント」という観点から記述しました。
目標スループットを達成する上で「1台のDBサーバーでは十分でないケース」が増えてきています。このときの対処手段としては、レプリケーション構成を用いたり、アプリケーションパーティショニングを組んだり、memcachedのようなキャッシュサーバを導入するといった様々な方法があります。これらは、それぞれ向き/不向きがありますが、本書を読むことでその特徴がより深く理解できるのではないかと思います。
インデックスについては2章構成としており、前半は参照性能を最大化するためのインデックス設計、後半はINSERT性能のダウンを緩和するためのさまざまなテクニックをカバーしています。インデックスがあると更新性能が落ちるのはよく知られた話ですが、それがどの程度になるのか、データ量が増えると更新性能が落ちるのはなぜか、またハードウェア環境やテーブルパーティショニングなどによってどのように変わるのか、といった定量的な指標を示しており、参考になることでしょう。
Linuxという観点ではメモリ制御/スワップ、メモリアロケータ、I/Oスケジューラ、ファイルシステムといった、パフォーマンス上重要なポイントをカバーしています。スワップサイズをゼロにすると何がまずいのか、接続パラメータにメモリ領域を割り当てすぎるとどんな副作用があるのか、cfq/deadline/noop/anticipatoryという4種類のI/Oスケジューラにはどんな違いがあるのか、といった、DB管理者であればひととおりおさえておきたい点をきっちりカバーしたつもりです。
ハードウェアという観点では、HDDとRAID構成上のポイントやライトキャッシュの重要性、そしてSSDについてカバーしています。SSDには1章を丸々割いており、SSD製品選定上のポイントや、さまざまな条件によるHDDとSSDの性能差の紹介、ファイルによって適正がどう変わるかを見ていきます。そして、SSDが今後普及することで、RDBMS自体やアプリケーションにどのような影響があるかを考察していきます。SSDの章は、先月発売のDBマガジン誌の記事とほぼ同様なのですが、本書では字数制限が基本的に無いので、DBマガジン誌では書ききれなかった内容についても触れています。
最後に、これらの要素技術を踏まえていかに実戦に活かすかという意味で負荷テストのケーススタディを紹介しています。
MySQLを使われている方はもちろんのこと、そうでない方でもLinux上でDB構築/運用をする方であれば、必ずや得るものがあるだろう思います。
本Blogでは、発売後しばらくの間、本書の内容を補完する意味でのサポート記事をいくつか投稿する予定です。
2009年8月27日木曜日
米大統領選で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でもこうした重要なシステムを安定稼動できる(逆に無ければ商用製品でも困難)、ということを示す良いキーノートだったと思います。
本当はカンファレンス終了後にすぐちゃんとしたレポートを書いて公開する予定だったのですが、その週に起こった草なぎ剛逮捕とか、そのほかの出来事にすっかり気を取られて放置していました。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でもこうした重要なシステムを安定稼動できる(逆に無ければ商用製品でも困難)、ということを示す良いキーノートだったと思います。
2009年8月20日木曜日
正しいベンチマークをするための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を使用)。
しかし、データがメモリにおさまらなくなればディスクアクセスの割合が多くなるため、こんな値を出すことは不可能になります。範囲検索の場合はランダムアクセスの割合も増えるため、ディスク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万クエリ級をさばくことが可能(すべてメモリにキャッシュされている状態での主キー検索の場合)で、これはリモートアクセスした状態のインメモリデータベースと比べても大きく見劣りしないでしょう。
ベンチマークは悪意を持って行われることはむしろ少数派で、ほとんどの場合は得られた結果をそのまま発信しているだけなので、結果を発信した人を責めるのは良くないと考えています(私自身も間違えることは良くあります)。むしろ積極的な情報交換があった方が良いでしょう。ただ結果の変動要因が非常に多いので、それを読む側の人にもスキルが求められます。ベンチマークは簡単ではないのです。
ここでは、私が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万クエリ級をさばくことが可能(すべてメモリにキャッシュされている状態での主キー検索の場合)で、これはリモートアクセスした状態のインメモリデータベースと比べても大きく見劣りしないでしょう。
ベンチマークは悪意を持って行われることはむしろ少数派で、ほとんどの場合は得られた結果をそのまま発信しているだけなので、結果を発信した人を責めるのは良くないと考えています(私自身も間違えることは良くあります)。むしろ積極的な情報交換があった方が良いでしょう。ただ結果の変動要因が非常に多いので、それを読む側の人にもスキルが求められます。ベンチマークは簡単ではないのです。
2009年8月18日火曜日
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ベンチマークの結果を示します。
MySQLの比較対象バージョンは5.0.83と5.1.37。my.cnfパラメータはWebアプリ向けにしました。log-binでバイナリログを有効化。innodb_flush_log_at_trx_commitは2にしてコミット時同期書き込みではなく「コミット時ファイルシステムキャッシュに書き込み」に変更(1にしていない理由は先週取り上げたグループコミットの性能問題を緩和するため)。max_connectionsを大きめにとってあります。
グラフの縦軸は計10万件をINSERTするのに要した時間です。見てのとおり、MySQL5.0では接続数の増加に対してINSERT性能が加速度的に低下しています。200接続のときは10万件のINSERTに150秒を要しており、秒間660件程度しかINSERTできていません。ディスクI/Oがボトルネックになっているわけでもないのに、660というのは低すぎる値です。さらに上図のグラフは同時接続数200の時点でグラフが切れていますが、それ以上のときは以下のようなエラーで止まってしまいました。
メッセージを見るとデッドロックのように見えますが、実際にはもちろん(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に対して以下の処理をするケースを考えてみます。
この場合、2)のAUTO_INCREMENTに何の値が入るかは、1)で何レコードINSERTされるかが確定しないと分かりません。そのため、1)の完了を待つ必要があります。バイナリログには以下のように記録されます。
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)→4)の順に実行した場合、テーブルロックをINSERT完了まで保持しないと、AUTO_INCREMENT値の採番順序とコミット順序(バイナリログへの記録順序)がずれる可能性が高くなります。しかし、実はずれてしまっても問題は起きません。バイナリログには以下のように、次にどのAUTO_INCREMENT値をセットするかの情報も一緒に記録されるからです。例えば以下のような感じで記録されます。
このため、バイナリログへの記録の順番が逆転しても最終結果は同じになります。そもそも、テーブルロック自体がINSERTの終了時点で解放されてしまうので、以下のような処理をすればMySQL5.0でも逆転現象は起きます。
この場合、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ロックのかけ方が以下のように変更されました。
番号が重複してはいけないので、採番処理自体はクリティカルセクションの中に置く必要がありますが、それ以外は並列で動作できます。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テーブルロックをかけることなどが分かるでしょう。
簡単に言えば、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テーブルロックをかけることなどが分かるでしょう。
登録:
投稿 (Atom)
