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オプティマイザのコスト計算アルゴリズム

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

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

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

よろしくお願いします。

Linux KVM上のWindows XPにおけるMySQLの性能

小ネタです。
Visual Studioの英語環境が必要になってLinux KVM上にWindows XPの環境を構築していたのですが、ついでなのでMySQLの性能測定をしてみました。

今回はCPU性能が見たかったので、SysBenchを読み取り専用にしてテストをしました。レコード件数をデフォルトの1万件から100万件に増やしていますが、容量としては200MB程度のためすべてのデータがメモリ上に収まる設定です。負荷クライアントはサーバとは別のマシンを用意して、Gigabit Ethernetで接続しています。
結果がこちら。

パラメータとしてホストまたはゲストに割り当てたCPUの数、それからゲストの場合はCPU PinningというCPU割り当てを固定する機能のありなしを変化させています。CPU Pinningは以下のようにvirt-managerから設定することが可能で、すべての仮想CPUがすべての物理CPUで動作するデフォルトの状態をunpin、仮想CPUと物理CPUを一対一にひも付けたものをpinとしています。

残念ながらWindowsゲストの性能は伸びませんでした。タスクマネージャを見ると負荷のほとんどをカーネルが占めており、ネットワーク処理がボトルネックになっているようでした。今回最新版のvirtioドライバを利用していますが、負荷低減効果はあまり大きくないように見受けられます。
またゲストに割り当てるCPUの数を増やすと、unpinの場合極端に性能が落ちることが分かりました。pinすればある程度性能は回復しますが、「Linux KVMにおいてゲストに割り当てるCPUの数はホスト - 1個までにするべき」という定説を裏付ける結果と言えます。
Linuxゲストはホストに対する性能低下率が約2割ということで、まずまず優秀な結果だと思います。ただし、ディスクI/Oを伴うワークロードではこれよりも大きく性能が低下すると推測しています。
ホストの結果を見ると、MySQL自体は4 CPUコアまできれいにスケールしていることが分かります。Core i5-2400SはTurbo Boostによって1コア時3.30GHz、2コア時3.20GHz、3コア時2.80GHz、4コア時2.60GHzまでブーストがかかっており、それを加味すると完全に比例している計算になります。MySQL 5.5ではこのくらいは当たり前といったところでしょうね。

MySQLの古いバージョンを集めました

ときどきMySQL 4.0など古いバージョンの調査をすることがあるのですが、そのたびにファイルが見つからず困っていたので一箇所に集めておきました。

プロダクトライフサイクルの終了したメジャーバージョンについて、最終マイナーバージョンのソースコードおよびWindowsとLinuxのバイナリパッケージをダウンロードできるようにしてあります。ディスク容量に限りがあるため、Linuxについてはその当時のRed Hat Enterprise Linux最新版向けでかつ64ビット版のものを優先的に格納しています。
今後これらの古いバージョンに対して新たな不具合や脆弱性が見つかっても、開発元による修正は行われません。そのため本番環境での利用は避け、ソースコードの調査や旧システムからの移行準備にのみ利用されることを推奨いたします。GPL v2で頒布しているものですので、これらのファイルを利用したことによるいかなる損害についても私や開発元が責任を負うものではありません。
実はもう一つお願いがありまして、MySQL 3.23.58のMaxパッケージだけがどうしても見つかりません…。ファイル名はMySQL-Max-3.23.58-1.i386.rpmだと思います。もしお持ちの方がいらっしゃいましたらご提供いただけると幸いです。ディストリビューション付属のものではなくMySQL AB純正のものを探しています。以下のようにしてVendorとBuild Hostの項目をご確認ください。

$ rpm -qip MySQL-3.23.58-1.i386.rpm
Name        : MySQL                        Relocations: (not relocatable)
Version     : 3.23.58                           Vendor: MySQL AB
Release     : 1                             Build Date: 2003年09月11日 15時35分09秒
Install Date: (not installed)               Build Host: build.mysql.com
Group       : Applications/Databases        Source RPM: MySQL-3.23.58-1.src.rpm
Size        : 23105941                         License: GPL / LGPL
Signature   : DSA/SHA1, 2003年09月15日 18時21分17秒, Key ID 8c718d3b5072e1f5
Packager    : Lenz Grimmer 
URL         : http://www.mysql.com/
Summary     : MySQL: a very fast and reliable SQL database engine

よろしくお願いいたします。

2012/02/07 追記

人力検索はてなに捜索願いを出したところ、半日足らずで無事入手することができました。

id:windofjulyさん、どうもありがとうございました。

mysqldumpの--order-by-primaryオプションについて

TIPSです。このようなテーブルがありまして、

CREATE TABLE `link` (
  `id1` int(11) NOT NULL DEFAULT '0',
  `id2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id1`,`id2`),
  KEY `ix1` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

データは以下のような感じで、このときは2,900万レコードありました。

+---------+---------+
| id1     | id2     |
+---------+---------+
|       5 |      69 |
|       5 |    1022 |
|       5 |    1487 |
…
|    1081 | 2021414 |
|    1081 | 2087813 |
|    1082 |      11 |
|    1082 |     225 |
…
| 2494267 |  158887 |
| 2494267 |  178845 |
| 2494267 |  631394 |
+---------+---------+

mysqldumpコマンドでバックアップすると、11秒で444MBのダンプファイルが出力されました。

$ time mysqldump -u xxx -pxxx xxx link > link.sql

real    0m11.391s
user    0m8.527s
sys     0m0.529s

$ ls -lh
-rw-rw-r-- 1 taira taira 444M  1月 22 18:39 2012 link.sql

ところが、mysqlコマンドでリストアしようとすると異常に遅い。

$ time mysql -u xxx -pxxx xxx < link.sql

real    94m23.951s
user    0m11.661s
sys     0m0.171s

わずか444MBのリストアに1時間半というのは尋常でない値で、どうしてだろうと思ってダンプファイルを見てみるとデータが主キーでソートされていないのです。

--
-- Dumping data for table `link`
--

LOCK TABLES `link` WRITE;
/*!40000 ALTER TABLE `link` DISABLE KEYS */;
INSERT INTO `link` VALUES (1104,5),(1690,5),(6932,5),(7679,5),(8419,5),(10247,5)
,(11909,5),(13663,5),(13957,5),(16421,5),(51108,5),(56051,5),(68176,5),(73209,5)
,(74134,5),(80094,5),(80243,5),(90949,5),(105566,5),(141127,5),(146550,5),(14655
9,5),(147375,5),(164871,5),(172267,5),(197350,5),(214173,5),(256744,5),(260201,5
),(295368,5),(363992,5),(467450,5),(535783,5),(619395,5),(651702,5),(688537,5),(
704851,5),(768590,5),(797017,5),(832366,5),(913097,5),(1072902,5),(1073296,5),(1
073968,5),(1074097,5),(1076529,5),(1076588,5),(1076596,5),(1099565,5),(1119078,5
),(1134413,5),(1206043,5),(1217529,5),(1221166,5),(1284145,5),(1366678,5),(14288
06,5),(1520730,5),(1534350,5),(1601227,5),(1605124,5),(1643972,5),(1764627,5),(1
790257,5),(1806513,5),(2002342,5),(2092470,5),(2303850,5),(2323218,5),(2448464,5
),(2459478,5),(2474731,5),(11,10),(58,10),(64,10),(68,10),(69,10),(91,10),(92,10
),(102,10),(110,10),(115,10),(139,10),(159,10),(161,10),(312,10),(326,10),(1034,
…

主キーでソートされていないデータのINSERTが遅いというのは、クラスタインデックスを利用しているInnoDBの宿命のようなものです。それにしても、なぜ主キーでソートされていないのでしょうか。
調べた結果、以下のことが分かりました。

  • mysqldumpは単純に「SELECT /*!40001 SQL_NO_CACHE */ * FROM `link`」というSQLを実行しているだけである。
  • 単純な全レコード取得に対して、MySQLがテーブルフルスキャン(InnoDBでは主キーのフルスキャンと同義)を選ぶとは限らない。
mysql> EXPLAIN SELECT * FROM link FORCE INDEX (PRIMARY);
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | link  | index | NULL          | PRIMARY | 8       | NULL | 29403733 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM link;
+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | link  | index | NULL          | ix1  | 4       | NULL | 29403733 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

今回id2カラムに対してix1というインデックスが作成されていて、InnoDBのセカンダリインデックスはリーフブロックの各インデックスエントリに主キーを含むのでこれは事実上カバーリングインデックスになっています。key_lenの値を見ると主キーの場合は8であるのに対してix1の場合は4となっており、MySQLからするとどうやらix1の方が効率的に見えてしまうようなのです。結果としてダンプファイルのデータは主キーでソートされていない状態で出力され、リストアの性能が大きく低下することになります。mysqldumpが悪いのかそれともmysqldが悪いのかは判断が難しいところですが、とにかくこれは不具合だと思いましたので報告しておきました(Bug#64097)。MySQL 5.5.20と5.1.61で確認済みです。
幸いにも簡単な回避方法があって、mysqldumpに--order-by-primaryオプションを付与することで「SELECT /*!40001 SQL_NO_CACHE */ * FROM `link` ORDER BY `id1`,`id2`」と明示的に主キーでソートしてくれるようになります。InnoDBのテーブルに対してmysqldumpを実行する際は、当面--order-by-primaryオプションを付与しておいた方が安全ではないかというのが私の見解です。
おまけとして、innodb_buffer_pool_sizeの設定とダンプファイルのデータが主キーでソートされているかどうかによって、リストア時間がどのように変化するのかを調べてみました。

innodb_buffer_pool_sizeがテーブルサイズに比べて大きい場合は、ダンプファイルが主キーでソートされていなくてもそれほど問題はありません。一方innodb_buffer_pool_sizeがテーブルサイズに比べて小さい場合は、リストア時間に大きな差が現れます。innodb_buffer_pool_sizeが小さく主キーでソートされていないパターンでは、リストア中ずっとストレージに対するランダムアクセスが続いていました。今回はストレージにPLEXTOR SSD M2Pシリーズ PX-256M2Pを使用してこの性能なので、普通のHDDだとさらに長い時間がかかることになります。