tpcc-mysqlにおける初期データロードのチューニング

ピンポイントチューニング講座です。tpcc-mysqlを使ったことのある方はご存知かと思いますが、最初にtpcc_loadコマンドで行う初期データのロードがかなり遅いです。実は簡単にチューニングする方法があるので、今回はこれをご紹介したいと思います。
tpcc-mysqlについて復習しておきたい方は、以下のエントリを先にご覧ください。

TPC-CのER図を見ると、ほとんどのテーブルに倉庫ID(Warehouse ID)を示すカラムがあることが分かります。

TPC-Cでは倉庫テーブルのレコード件数がデータベース全体の規模を決めるスケールファクタになっていて、これを増やすとそれぞれのテーブルに倉庫IDが2のデータ、倉庫IDが3のデータ、…とデータが積み重なっていく形になっています。ここでそれぞれの倉庫IDのデータは互いに独立しているので、並列化をかけることができます。

商品テーブルのみスケールファクタと関係なく10万レコードで固定されているため、まず最初に商品テーブルのデータロードを行います。そのあと論理CPUの数だけ並列にデータロードを実行します。tpcc_loadにはデータの一部分だけをロードするオプションが用意されていますので、これを利用することができます。


クアッドコアの環境でスケールファクタ1,000、およそ100GBのデータロードを行ってみました。


並列度が低い場合はTurbo Boostが効くのでクアッドコアで4倍とまではいきませんが、きちんと性能が伸びていることが分かると思います。100GBのデータロードに2時間20分なら、まあ待てるかなといったところです。
余談ですがデータ・ウェアハウス向け製品ではデータロードの性能は毎時10TBに達するそうで、MySQLなどとはまるで別世界の話になっています。すごいですね。

データベース負荷テストツールまとめ(5)

というわけで、JPOUG> SET EVENTS 20120721 | Japan Oracle User Groupに参加して発表をしてきました。通常の勉強会と比べて発表者と聴講者の一体感を増すための工夫がなされていて、とても良かったと思います。有限コーヒーかと思ったら無限ビールだったのも驚きです。JPOUGの運営メンバのみなさま、会場を提供してくださった日本オラクルのみなさま、当日お越しいただいたみなさま、どうもありがとうございました。
私のセッションでは、データベース負荷テストツールまとめ(5)と題して過去4回分のまとめと自作ツールの紹介をさせていただきました。JdbcRunnerはOracle Database、MySQLPostgreSQLの間でTPC-BとTPC-Cの性能比較ができる唯一のオープンソースソフトウェアですので、いろいろ試してみていただければと思います。試した結果をブログなどで公開していただけると作者が喜びます。

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

Java並行処理プログラミング

一冊だけ書籍の紹介をさせてください。

Java並行処理プログラミング ―その「基盤」と「最新API」を究める―

Java並行処理プログラミング ―その「基盤」と「最新API」を究める―

この本はJavaでマルチスレッドプログラミングをするなら必ず読んでおくべきと言われている名著なのですが、2006年に出版されたあとしばらく品切れで入手困難な状態が続いていました。私はこの本をずっと探し続けていて、市の図書館でようやく見つけたときは本当にうれしかったです。この本は素晴らしい本で、どのくらい素晴らしいかと言うと、図書館で借りて読んだあと途中まで作っていたJdbcRunnerのソースコードを全部捨てて書き直したくらいです。この本が手に入らなかったらJdbcRunnerは完成しませんでした。2009年に復刊ドットコムでめでたく復刊され、今では普通に手に入れることができます。

MySQL 5.6におけるsync_binlog=1の改善について&勉強会のお知らせ

2012年6月のエントリの続きです。前回は同期レプリケーションによるネットワーク遅延のある環境において、MySQLの性能がどの程度低下するのかということを確認しました。その中でも特にsync_binlogが1に設定されている場合、性能が大きく低下するということが分かりました。参考としてAmazon RDSのマルチAZデプロイメントにおいては、性能と信頼性のトレードオフを考慮した結果、sync_binlogがデフォルトで0に設定されているということを調査しました。
タイトルでネタバレしていますが、MySQLの次期バージョン、MySQL 5.6でこのsync_binlog=1の性能が大きく改善します。前回と同じ負荷テストをMySQL 5.5.25からMySQL 5.6.6-labsに差し替えて行った結果を、以下に示します。


前回のMySQL 5.5.25と異なり、sync_binlog=1においても多重度に応じたスループットの伸びが確認できると思います。代表的なところで往復遅延時間(Round Trip Time、以下RTT)が4ミリ秒の場合におけるMySQL 5.5.25、5.6.6-labsの結果を並べてみると、以下のようになります。

素晴らしいですね。

バイナリログのグループ・コミット

この性能改善は、バイナリログに対するグループ・コミットという新機能によってもたらされています。グループ・コミットとは、複数のクライアントからのコミット要求をまとめて処理する機能です。
グループ・コミットがない場合、複数のクライアントからのコミット要求は以下のように順番に処理されます。DBMSとしてACIDを満たすため、バイナリログへの書き込みができるスレッドは同時に一つだけとなっています。

今回の環境はディスクに同期書き込み(pwrite(2)+fsync(2))をするところでDRBDによるネットワーク遅延の影響を受け、ここにRTT分の時間が加算されます。RTT 4ミリ秒の場合はコミットに最低でも4ミリ秒かかるため、どんなに頑張っても1秒間に250回しかコミットできないということになります。実際にはこれに合わせてInnoDBログにも同期書き込みをする必要があるため、1秒間にコミットできる回数はさらに減ります。
グループ・コミットがある場合、複数のクライアントからのコミット要求は以下のようにまとめて処理されます。

先に来たコミット要求を処理している間に次のコミット要求が来た場合、ディスクへの同期書き込みを両方まとめて行います。バイナリログへの書き込みができるスレッドが同時に一つだけという点は変わらないのですが、一度のfsync(2)で複数のコミット要求を処理することにより、1秒間にコミットできる回数を大幅に増やすことができます。

グループ・コミットのこれまでの経緯

グループ・コミットはACIDを備えたDBMSであれば基本的にサポートしていることが望ましい機能です。本機能がMySQL 5.6でようやくサポートされるにいたった経緯を確認しておきしょう。
まずInnoDBストレージエンジンの方ですが、こちらは2001年にリリースされたMySQL 3.23の時点ですでにグループ・コミットをサポートしていました。
2005年にリリースされたMySQL 5.0において、バイナリログとInnoDBログの整合性を取るための機能改善が行われました。それまではMySQLサーバがクラッシュした際にタイミングによってバイナリログとInnoDBログの整合性が取れなくなり、レプリケーションスレーブが壊れてしまうことがありました。この機能改善はInnoDB側に2フェーズコミットを行わせるという方針で実装されたのですが、これによってバイナリログ有効時にInnoDBのグループ・コミットが効かなくなるという新たな問題が引き起こされました。

2010年にリリースされたMySQL 5.1+InnoDB Pluginにおいて、sync_binlogが0の場合に限り再びInnoDBのグループ・コミットが効くようになりました。sync_binlogが0の場合は本来の目的であるバイナリログとInnoDBログの整合性を取ることはできないものの、レプリケーションを活用したスケールアウト構成においてマスタの性能を大幅に上げることができるようになりました。

同じく2010年にリリースされたMySQL 5.5において、準同期レプリケーションが導入されました。準同期レプリケーションはグループ・コミットと直接の関係はないのですが、これとMHAを組み合わせることでsync_binlogが0の場合でもマスタ障害によるデータロストやレプリケーションスレーブの不整合を防ぐことができるようになりました。ここまできてようやく性能と信頼性の両方を担保することができるようになったのです。

MySQL 5.6は現在開発マイルストーンが5.6.5まで、実験版が5.6.6-labsまでリリースされています。この5.6.6-labsにおいてついにバイナリログのグループ・コミットが実装されたという次第です。バイナリログのグループ・コミットによってsync_binlog=1における性能が大幅に改善され、共有ストレージやDRBDを用いたクラスタ構成においても、レプリケーションと組み合わせた際の性能と信頼性を両方担保することができるようになります。Amazon RDSでレプリケーションスレーブが壊れて困ったことのある方は、MySQL 5.6の正式版がリリースされてAmazon RDSが対応するまでの辛抱です。

他のDBMSにおけるグループ・コミットのサポート状況

Oracle Databaseははるか昔からグループ・コミットをサポートしていました。ウェブで調べられる範囲では、Oracle 7の時点ですでにサポートされていたことを以下のマニュアルから確認することができます。Oracle 7がリリースされたのは1992年で、いまから20年前のことです。PC-9801FAが初めてi486SXを搭載し、Windows 3.1がリリースされ、Linuxカーネルが0.96だったころの話です。

PostgreSQLは2001年にリリースされたPostgreSQL 7.1でWAL(Write Ahead Logging)が導入され、このときにグループ・コミットもサポートされました。しかし内部の排他制御の設計が適切でなく、これまであまり性能が出ていませんでした。commit_delayというパラメータによってグループ・コミットを発生しやすくすることもできるのですが、こちらの効果も限定的なものでした。
PostgreSQLは次のPostgreSQL 9.2でグループ・コミットの設計が見直され、きちんと性能が出るように改善が図られています。PostgreSQL 9.2は現在ベータ2までリリースされており、順調に行けば2012年中に正式版がリリースされる見込みです。

Oracle Databaseから遅れること20年、MySQLPostgreSQLもようやく…といったところです。

勉強会のお知らせ

話は変わって、2012年7月21日(土)にJPOUG、日本オラクルユーザ会主催のイベントが催されます。

Oracle OpenWorld Unconferenceに引き続きセッション枠をいただいたので、私は「データベース負荷テストツールまとめ(5)」と題して今回のエントリなどで使ったツールの紹介をする予定です。事前に過去4回分をざっと読み返していただくと、当日の話が分かりやすくなると思います。DBサーバのハードウェア、ソフトウェアを調査される方やクラウドサービスの性能評価をされる方、それからパフォーマンス・チューニングの教材を探している方などにおすすめのセッションです。

イベントとしてはOracle Databaseの話が中心となりますが、木村さんMySQLの話、小田さんと林さんがDBエンジニアのキャリアについての話をされるなど、Oracle Database以外の話もいろいろ聞けると思います。オラクル青山センターですのでコーヒーもたくさん飲めます。

dateコマンドのオプションについて

前回のエントリで当初「date -s "`LANG=C date`"」というコマンドをご紹介していたのですが、このオプションでは誤動作する場合があるとのご指摘をいただきました。具体的には、環境変数LC_ALLまたはLC_TIMEが設定されている場合に、誤った時刻が設定されてしまいます。

# LANG=C date
Wed Jul  4 02:47:29 JST 2012
# export LC_ALL=ja_JP.UTF-8
# LANG=C date
2012年  7月  4日 水曜日 02:47:38 JST

この状態でdate -sコマンドを投入すると、文字列の解析に失敗して常に午後8時となります。

# export LC_ALL=ja_JP.UTF-8
# date -s "`LANG=C date`"
2012年  7月  4日 水曜日 20:12:00 JST

ロケールの設定はLC_ALL→LC_TIME→LANGという優先順位になっているので、以下のように書くのが正解でした。大変失礼いたしました。

# date -s "`LC_ALL=C date`"

前回のエントリはロケールに依存しない書き方「date `date +'%m%d%H%M%C%y.%S'`」に修正しました。CPU使用率の高騰についてはもう片付いたころかと思いますが、Twitterでいろいろ教えていただいたので、dateコマンドのオプションの書き方をいくつかご紹介しておきます。

# date `date +'%m%d%H%M%C%y.%S'`
# date -s "`LC_ALL=C date`"
# date -s now
# date -s today
# date -s '1 sec -1 sec'
# date -s 'tomorrow yesterday'
# date -s 'next week last day -96 hours -1440 minutes -86400 seconds'

nowが短くて覚えやすいですね。dateコマンドはオプションをBisonで構文解析しているそうで、かなり自由に書くことができます。

うるう秒のあとにMySQLなどのCPU使用率が高騰する件について

2012年7月1日のうるう秒のあとに、MySQLJavaなどのCPU使用率が高騰する事象が報告されています。

                 CPU     %user     %nice   %system   %iowait    %steal     %idle
08時30分01秒     all      0.02      0.00      0.02      0.04      0.00     99.91
08時40分01秒     all      0.02      0.00      0.02      0.08      0.00     99.88
08時50分01秒     all      0.02      0.00      0.02      0.03      0.00     99.92
09時00分01秒     all      0.11      0.00      0.13      0.04      0.00     99.72
09時10分01秒     all     23.02      0.00     29.09      0.11      0.00     47.78
09時20分01秒     all     23.11      0.00     29.08      0.06      0.00     47.75
09時30分01秒     all     22.85      0.00     28.73      0.03      0.00     48.38
09時40分01秒     all     23.19      0.00     29.59      0.11      0.00     47.12
09時50分01秒     all     23.28      0.00     29.11      0.04      0.00     47.57
top - 03:51:01 up 31 days,  2:49,  2 users,  load average: 1.85, 1.68, 1.64
Tasks: 115 total,   1 running, 114 sleeping,   0 stopped,   0 zombie
Cpu(s): 23.9%us, 22.7%sy,  0.0%ni, 48.3%id,  0.0%wa,  0.2%hi,  4.9%si,  0.0%st
Mem:   2054808k total,  1318072k used,   736736k free,   168900k buffers
Swap:  4194296k total,    11192k used,  4183104k free,   830472k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 1393 mysql     20   0  717m  45m 5988 S 91.3  2.3   1003:34 /usr/sbin/mysqld --basedir=/usr --
 1601 taira     20   0 1399m 108m 9636 S 87.7  5.4   1001:11 java -server -Xmx256m -Xms256m wik
    9 root      20   0     0    0    0 S 42.2  0.0 204:13.15 [ksoftirqd/1]
    4 root      20   0     0    0    0 S  6.3  0.0 219:36.78 [ksoftirqd/0]
   13 root      20   0     0    0    0 S  1.3  0.0 203:36.27 [ksoftirqd/2]
   17 root      20   0     0    0    0 S  0.3  0.0   3:03.04 [events/2]
13372 taira     20   0 15020 1204  932 R  0.3  0.1   0:00.04 top -c
    1 root      20   0 19204 1408 1220 S  0.0  0.1   0:00.61 /sbin/init

MySQLが被害を受けているので調べていたのですが、原因はLinuxカーネルの不具合とのことです。

サーバを再起動するか、以下のコマンドを投入して日付を再設定することで回復します。

# date `date +'%m%d%H%M%C%y.%S'`

影響を受けるシステムはRed Hat Enterprise Linux 6、Ubuntu 12.04など比較的新しいカーネルを搭載したものに限られます。Red Hat Enterprise Linux 5は影響を受けません。また事象の発生は上位のNTPサーバがLeap Indicatorを送信してくる場合に限られ、ntpdを稼動させていない場合は影響を受けません。7月1日のsyslogに以下のようなログが記録されているかどうかをご確認ください。

Jul  1 08:59:59 sbridge kernel: Clock: inserting leap second 23:59:60 UTC

MySQLの場合、MySQL 5.5においてsrv_lock_timeout_threadとsrv_error_monitor_threadというバックグラウンドスレッドがこの不具合により暴走します。MySQL 5.1についてはこれらのバックグラウンドスレッドがfutex(2)を利用していないため、CPU使用率の高騰は発生しませんでした。

日曜だったのが不幸中の幸いでしょうか。対応された方はお疲れさまでした。

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