MySQLのDRBD構成におけるネットワーク遅延の影響について

今さらですが、Amazon RDSのマルチAZデプロイメントについて調べていました。マルチAZデプロイメントとは、独立した電源、空調、ネットワーク、セキュリティを備えた物理的に異なるロケーションに対して同期レプリケーションを行うことで、データベースの耐障害性を高める機能です。AZはAvailability Zoneの略です。
異なるロケーションに対する同期レプリケーションと聞くと、性能が出ないのではないかという懸念がどうしても出てきます。そこで、どの程度性能が落ちるものなのか検証を行いました。なお、すでに実際のAmazon RDSを利用して検証を行った方がいらっしゃいましたので、今回は手元の環境を利用して、ネットワーク遅延やMySQLパラメータと性能との関連性を確認していきたいと思います。

マルチAZデプロイメントの具体的なアーキテクチャは公開されていませんが、以下のような特徴があります。

このことから示唆されるのは、MySQL 5.5の準同期レプリケーションやOracle Database Enterprise EditionのData Guardは利用しておらず、もっと低いレイヤでレプリケーションを行っているということです。おそらくAmazon社はレプリケーションの仕組み自体を自社開発しているものと個人的に推測していますが、これを既存の製品で実現するには以下の方法があります。

今回はDRBDを利用しました。システム構成図を以下に示します。

KVMゲストを4つ、仮想ネットワークを2つ作成してこの図のように接続します。負荷クライアントk01sl6からのSQLアクセスは192.168.1.0/24のネットワークを経由してプライマリDBサーバk02c5に対して行われます。プライマリDBサーバk02c5とセカンダリDBサーバk03c5の間では、192.168.100.0/24と192.168.101.0/24のネットワークを経由してDRBDレプリケーションが行われます。192.168.100.0/24と192.168.101.0/24のネットワークは、ゲートウェイk05wanによって接続されています。
ゲートウェイk05wanではtcコマンドを用いてネットワーク遅延を発生させます。以下に例を示します。

k05wan# tc qdisc add dev eth1 root handle 1: netem delay 5ms
k05wan# tc qdisc add dev eth2 root handle 1: netem delay 5ms
k02c5$ ping 192.168.101.33
PING 192.168.101.33 (192.168.101.33) 56(84) bytes of data.
64 bytes from 192.168.101.33: icmp_seq=1 ttl=63 time=10.3 ms
64 bytes from 192.168.101.33: icmp_seq=2 ttl=63 time=10.4 ms
64 bytes from 192.168.101.33: icmp_seq=3 ttl=63 time=10.3 ms

netemカーネルモジュールはデバイスから送信されるパケットに対してのみ遅延を与えるものですので、WAN環境におけるネットワーク遅延をエミュレーションするにはeth1とeth2の両方に同じ値を設定する必要があります。pingの値は往復時間(Round Trip Time、以下RTT)を示しており、この場合は約10ミリ秒となります。

以下のパラメータを変化させてTPC-Cのスループットを測定します。合計で98パターンとなります。

  • sync_binlog：0、1
  • レプリケーション構成：DRBDなし、DRBD切断、RTT <1、2、4、10、20ミリ秒
  • クライアントの多重度：1、2、4、8、16、32、64

まずはsync_binlog=0の結果から見ていきましょう。

ネットワーク遅延が大きいほど性能が低下するというのはその通りなのですが、下がり方に特徴があります。DRBDなしにおけるスループットを100%としたときの相対値に描き直してみます。

クライアントの多重度が1の時はネットワーク遅延に応じた性能低下率となっていますが、クライアントの多重度を上げていくにつれて性能低下が目立たなくなっていきます。どこまでを許容するかは一概には言えませんが、ある程度の多重負荷がかかる環境においてはRTT 4ミリ秒までならまあ許容範囲かなと思います。
多重度を上げていくとなぜ性能低下が目立たなくなるのかですが、ごく簡単に説明すると、あるクライアントがDRBDのネットワーク処理で待たされている間に他のクライアントがCPUを使う別の処理を挿し込めるからです。すべてのクライアントがDRBDのネットワーク処理待ちになるような極端なワークロードでなければ、多重度を上げていけばいつかはCPUを使い切り、スループットとしては差がなくなるということになります。
次に、sync_binlog=1の結果を見てみましょう。

一見してsync_binlog=0のときとは状況が異なるということが分かると思います。多重度を上げていっても性能低下が緩和されませんし、そもそものスループットが低すぎます。おそらくRTT 2ミリ秒の段階で、もう実用に堪えないのではないかと考えられます。sync_binlog=1の課題については次のエントリで説明することにして、今回はsync_binlog=1がかなり悪いということをまず覚えていただければと思います。
同期レプリケーションにおけるネットワーク遅延の影響が分かったところで、Amazon RDSがどのようにしてこの課題に対処しているかを確認しておきます。調べたところ、以下のような仕組みになっているようです。

  • sync_binlogの設定はユーザに任されていて、デフォルトは0である (参考)
  • Availability Zone間のRTTは、1ミリ秒程度である (参考)

デフォルトがsync_binlog=0だというのは意外でした。堀内さんの資料にも書かれているようにsync_binlog=0ではフェイルオーバー時にバイナリログが欠損するので、リードレプリカが壊れてしまいます。性能とのトレードオフでこのような設計にしたのだろうということは今回の検証結果からも理解できるのですが、お客さまに対して壊れたリードレプリカは再度作り直してくださいとはなかなか言いづらいところです。ちなみにマスタはInnoDBのクラッシュリカバリによって復旧するので、innodb_flush_log_at_trx_commit=1さえ設定してあれば大丈夫です。
Availability Zone間のRTTが1ミリ秒程度だというのも寡聞にして知りませんでした。私の自宅(千葉)からさくらのVPS(大阪)までのRTTが約20ミリ秒だということを考えると、各Availability Zoneはかなり近い距離にあるのではないかと推定できます。地震のない国であれば特に問題ないと思いますが、東京リージョンはどうなっているのか少し気になるところです。

RHEL 6.3バンドル版MySQL 5.1.61がInnoDB Pluginをサポート

タイトルで本エントリの内容はほぼ終了となりますが、6月20日にRed Hat Enterprise Linux 6.3がリリースされ、ディストリビューション付属版のMySQL 5.1.61においてようやくInnoDB Pluginが有効化されました。

The InnoDB storage engine is built-in for all architectures. This update adds InnoDB Plugin, the InnoDB storage engine as a plug-in for the 32-bit x86, AMD64, and Intel 64 architectures. The plug-in offers additional features and better performance than when using the built-in InnoDB storage engine. Refer to the MySQL documentation, linked to in the References section, for information about enabling the plug-in. (BZ#740224)

RHEL 6バンドル版のMySQL 5.1はこれまでInnoDB Pluginが無効化された状態でリリースされていました。そのため2011年7月のエントリでは、InnoDB Pluginを有効化するためにMySQLをSource RPMからリビルドする手順をご紹介しました。その後Red Hat Bugzillaにフィーチャーリクエストを行ったところ、時間はかかりましたが今回のRHEL 6.3にてめでたくInnoDB Pluginが有効化される運びとなりました。

もちろんオラクル公式版のMySQL 5.5を利用できる環境であれば、そちらを利用されることをおすすめいたします。ただ、大人の事情で外部パッケージの導入ができない場合もあろうかと思います。そのような場合は、次善の策として今回のアップデートをご検討いただければ幸いです。CentOSやScientific Linuxといったクローンディストリビューションについても、ほどなく対応がなされるものと思います。
InnoDB Pluginの優位性については、もう2年も前になりますが2010年4月のエントリにまとめてあります。設定方法やベンチマーク結果もご紹介していますので、ぜひご覧ください。

dstat2graphs - dstatのログをグラフ化するツール

dstatが便利ですね。

私は特に--outputオプションでCSV形式のログファイルを出力できるところが気に入っています。本番環境ではきちんとした監視ツールを使うと思いますが、開発・検証環境で手早くOSリソース情報を可視化できるので重宝しています。
それでもExcelやCalcでグラフを描くというのは何度も繰り返すと面倒なもので、

探したのですが見つからなかったので、連休を利用して作ってみました。


いくつか注意点があります。

  • dstat -tvfn --output log.csv 1しか受け付けないという割り切った作りです。
  • dbstudy.infoで提供しているサービスには、ファイルサイズ4MBまでの制限を設けています。
  • アクセス制御機能はありませんので、機密性の高いデータはアップロードしないでください。

ファイルサイズや機密性を気にせず使いたい場合は、GitHubからソースファイルをダウンロードしてご利用ください。修正済みBSDライセンスです。

グラフの描画にはRRDtoolとPerlのRRDsモジュールを利用しています。これらのパッケージは、Red Hat Enterprise Linux 6系ではディストリビューションに付属しています(rrdtool、rrdtool-perl)。Red Hat Enterprise Linux 5系の場合はEPELリポジトリからインストールすることができます。
最初はGD::Graphで簡単に仕上げるつもりだったのですが、今回まとまった時間が取れたのでいろいろ勉強してみました。

RRDtoolについては、以下のウェブサイトの資料が大変役に立ちました。どうもありがとうございます。

jQueryとTwitter Bootstrapについては、ドットインストールという学習サイトが大変役に立ちました。

これくらいの小さなツールは作っていて楽しいですね。

Oracle OpenWorld Unconference with MySQL 復習

というわけで、Oracle OpenWorld Unconferenceに参加して発表をしてきました。直前までデータ取りをしていてやはり睡眠不足だったのですが、とても楽しかったです。JPOUGのみなさま、日本オラクルのみなさま、当日お越しいただいたみなさま、どうもありがとうございました。

Oracle Database経験者がMySQLの設計思想を知っていろいろ考える会

私のセッションでは、Oracle Database、MySQLそれぞれに対してコネクションプール、およびパラメータのバインド機構が性能にどのような影響を与えるか、という切り口で話をさせていただきました。各製品に詳しい方にとっては当たり前の話なのですが、どちらかが未経験という方にとっては新鮮に感じられたのではないかと思います。資料を公開しておきます。

先のエントリでお断りしたとおり、Oracle Databaseに関する負荷テストの結果は公開しません。ただ、昨年行われたOracle DBA & Developer Days 2011という開発者向けイベントの資料に大変よく似たグラフがありましたので、ご紹介いたします。

MySQL Connector/Jの新規接続チューニング

MySQLはコネクションプールを使わなくてもそれほど性能が低下しないという話をしたのですが、実はJavaの場合はかなり低下します。これはJDBCドライバのConnector/Jが新規接続時にいくつかのSQLを発行してしまっているためです。この件についてConnector/J開発者のMark Matthews氏が、新規接続時のSQL発行数を削減する方法をブログで解説しています。

まとめると、以下のようになります。今回の負荷テストはこれらのパラメータを設定した上で実施しています。

  • MySQLサーバに、sql_mode=STRICT_TRANS_TABLESを設定
  • JDBC接続プロパティに、cacheServerConfiguration=trueを設定
  • JDBC接続プロパティに、useLocalSessionState=trueを設定
  • JDBC接続プロパティに、characterSetResults=UTF-8を設定

リンク集

当日発表された方のブログエントリです。

JPOUGではこれからもこうしたイベントを随時開催していくとのことですので、ご興味があればぜひご参加ください。

Oracle Database経験者がMySQLの設計思想を知っていろいろ考える会

本日4月4日(水)より、Oracle OpenWorld Tokyo 2012が開催されます。
塩原さんにお誘いいただいて、3日目、4月6日(金)のOracle OpenWorld Unconference presented by JPOUGで発表をさせていただくことになりました。

今回は20分枠ということもあってあまり込み入った話はしませんが、宣伝もかねて資料の前半部分を公開しておきます。

なおOracle Technology Network Developer License Termsに従い、本ブログにおいてOracle Databaseに関する負荷テストの結果を公開することはありません。詳しい内容を知りたい方は、ぜひUnconferenceへお越しください。

MySQL InnoDB Deep Talk #1 復習

というわけで、InnoDB Deep Talk #1に参加して発表をしてきました。準備のために今月ちょっと睡眠不足でしたが、かなり刺激になったので参加してよかったです。いちいさんを始め運営のみなさま、雨の降るなか参加されたみなさまどうもありがとうございました。

書籍の紹介

当日木村さんと初めてお会いして、今月発売になった書籍「プロになるための データベース技術入門　〜MySQLforWindows困ったときに役立つ開発・運用ガイド」を献本いただきました。どうもありがとうございます！

プロになるための データベース技術入門　?MySQLforWindows困ったときに役立つ開発・運用ガイド

プロになるための データベース技術入門　?MySQLforWindows困ったときに役立つ開発・運用ガイド

先に発売された松信さんの本「Webエンジニアのための データベース技術[実践]入門 (Software Design plus)」と比べると、松信さんの本は比較的レイヤの低いインフラ寄りの内容、木村さんの本はレイヤの高いアプリケーション寄りの内容となっています。タイトルは似ていますが内容は意外なほど重複していないので、両方あわせて読まれることをおすすめいたします。以下、私が特にいいなと思ったところをご紹介します。

  • MySQL 5.5をメインターゲットとした初めての書籍だと思います。
  • 3.3章「Windows PowerShell」ではコマンドラインからConnector/Netを経由してMySQLにアクセスする手法が紹介されていて、大変興味深いです。
  • 4.1章「トランザクションとロック」はInnoDBの若干クセのあるロック方式にも触れられていて、貴重な資料だと思います。
  • 4.3章「文字コード」は24ページを割いて詳細に解説されており、cp932における重複コードの存在など高度なトピックも扱われています。
  • 5.1章「ストアドルーチンの利用」はMySQLのストアドルーチンに関する数少ない情報源ではないかと思います。付録C「SQL/PSMミニリファレンス」も便利です。
  • 5.4章「プランとヒント」は当日ライトニングトークをしていただいた内容で、私の発表の前提知識にもなるかと思います。

ありがたいことに書籍から私のブログにいくつか参照がありましたので、パーマリンクを切らさないよう維持していきたいと思います。

MySQL 5.1におけるInnoDBのレンジ分析

勉強会のときに指摘を受けたのですが、InnoDBのレンジ分析について、プレゼンテーション資料の21ページ目から25ページ目までのアルゴリズムは昔のバージョンにはなかったものです。このように9ページを上限として実際にINDEX RANGE SCANを行う仕組みが導入されたのはMySQL 5.5からで、MySQL 5.1まではINDEX RANGE SCANは行われていませんでした。なお、MySQL 5.1でInnoDB Pluginを利用した場合もINDEX RANGE SCANは行われていません。
MySQL 5.1の場合、読み取りレコード数の推定は下限値が含まれるリーフページと上限値が含まれるリーフページ、それからルートページの情報のみを利用して行います。

ルートページにおいて下限値へのポインタと上限値へのポインタの間にあるエントリ数をn_rows_on_prev_level、それぞれのリーフページに含まれるインデックスエントリの数をn_recs_Nとして、読み取りレコード数の推定値は以下の式で表されます。

読み取りレコード数の推定値
＝(検索範囲内のリーフページ数)×(リーフページあたりの推定レコード数)×2
＝(検索範囲内のリーフページ数)×(下限値が含まれるリーフページのインデックスエントリ数＋上限値が〃)÷2×2
＝n_rows_on_prev_level×(n_recs_1＋n_recs_2)÷2×2
＝28×(348＋348)÷2×2
＝19,488
mysql> EXPLAIN SELECT * FROM item WHERE i_id BETWEEN 10001 AND 20000;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | item  | range | PRIMARY       | PRIMARY | 4       | NULL | 19488 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

最大11ページを利用するMySQL 5.5以降の推定アルゴリズムに比べると、3ページしか利用しないこのアルゴリズムでは見積もり精度が低くなってしまうことが分かると思います。読み込んだリーフページがたまたま隙間だらけだった場合、推定値は実際の値から大きくかけ離れたものになります。このアルゴリズムであれば、補正係数2の存在も納得できないことはないです。
以下のバグレポートで、MySQL 5.1までのアルゴリズムでは読み取りレコード数の推定値が200倍もずれることがあるという議論がなされています。その後、MySQL 5.5でINDEX RANGE SCANを行う新しいアルゴリズムが導入されました。

MySQLのSQLオプティマイザについては、引き続き調べていきたいと思います。

MySQL SQLオプティマイザのコスト計算アルゴリズム

いちいさんにお誘いいただいて、勉強会で発表をすることになりました。

おそらく初見では内容が難しいと思いますので、先に資料を公開しておきます。

プレゼンテーション資料からリンクしているウェブサイトの一覧です。

よろしくお願いします。