漢(オトコ)のコンピュータ道: MySQL 5.5新機能徹底解説

今年も残すところあとわずかとなった。2010年もIT業界にとっては変化の多い一年だったが、皆さんにとっては良い年だっただろうか？既に何度かMySQL 5.5の新機能については取り上げたが、ついに正式版がリリースされたということでここで改めて新機能を解説し、今年最後のエントリを締めくくろうと思う。

MySQL 5.5にはこれでもかっ！というぐらい新機能が追加されている。しかもいずれもナイスなものばかりだ。一般的には、ソフトウェアに新機能が追加されると重くなったり安定性が低下する事例が後を絶たないのだが、MySQL 5.5に関してはそのようなことは全くないので安心して利用して頂きたい！

InnoDBの大幅な改善

種々ある改善点の中でも特に目をひくのがInnoDBストレージエンジンへの改良だ。実は、InnoDBはMySQL 5.1が最初にリリースされたときから、2回アップデートが行われている。MySQL 5.1がGAになった後でいったんInnoDB Pluginバージョン1.0がリリースされたのだが、その名が示す通り既存の（ビルトインの）InnoDBと置き換えて利用するプラグイン形式のストレージエンジンであった。MySQL 5.5ではInnoDBのバージョンがInnoDB 1.1となり、ビルトインのストレージエンジンとなっている。InnoDB Plugin 1.0では性能の改善と機能追加がなされ、さらにInnoDB 1.1（MySQL 5.5）で性能に磨きがかかった格好だ。それぞれのバージョンで追加された機能および改善点は次の通り。

InnoDB Plugin 1.0（MySQL 5.1）

Fast Index Creation
新しいフォーマット
データ圧縮
INFORMATION_SCHEMAの追加
オンライン変更出来るパラメータの増加
TRUNCATE TABLEによる.ibdファイルの再作成
クラッシュリカバリ時間の短縮
グループコミット
I/Oスレッドの多重化
I/Oスループットの調整
Adaptive Hash Indexのチューニング
先読みアルゴリズムに対する変更
ダーティページのフラッシュアルゴリズム変更

InnoDB 1.1（MySQL 5.5）

複数のバッファプールインスタンス
複数のロールバックセグメント
flush_listのロックを独立
パージスレッドが独立
Change Buffering
Linuxにおけるネイティブ非同期I/Oのサポート
PERFORMANCE_SCHEMAのサポート
Windowsでの性能改善

InnoDB Plugin 1.0については、既にエントリ「InnoDB Pluginことはじめ。快適ストレージエンジン生活はじまる！」でも解説したので詳細はそちらを見て頂きたい。

ちなみに、InnoDB Plugin 1.0で追加された新しいフォーマットであるBarracudaでは「最大行サイズ8KBまで」という制限が緩和されている。今でもこの8KBの制限については非常に多くの問い合わせを受けるのだが、特にBLOB/TEXT/VARCHARを多用している場合にこの制限に引っかかりやすい。と言っても単に8KB以上の大きなサイズのカラムが使えないというわけではない。大きなサイズのBLOBやTEXTは先頭の768バイトを除いて別の領域に格納されるので、8KB以上のカラムを格納するのは問題ないのだが、先頭の768バイトは通常の領域に残るため、その部分がたくさんあると8KBの制限に引っかかることになる。例えば11個以上のBLOB/TEXT/VARCHAR（768バイト以上）を作成すると合計で8KBを超えてしまうため、この制限に引っかかってしまうのである。

ただし、この問題が顕在化するのはデータを格納したときであり、テーブルの作成は問題なく出来てしまう。しかも、データを満たんに詰めると8KBを超えるテーブル定義であっても、実際の行サイズが合計で8KBを超えないと問題が顕在化しないので潜在的な問題に気づかず、ある日突然「データが格納できない！」ということになってしまうわけだ。

InnoDB Plugin 1.0のBarracudaフォーマットでは、通常の領域の残るのがヘッダの20バイトだけであり、データは全て別の領域に格納されるようになった。従って、8KB÷20=400個のBLOBを利用しなければ8KBの制限に引っかかることはない。実質的に問題が解消したと言っても良いだろう。ただし、Barracudaは既存の（過去のバージョンの）InnoDBとファイルの互換性がなくなるし、利用するときはinnodb_file_per_tableオプションを利用しなければならないので注意が必要だ。使い方は先程紹介したエントリに載っているので、そちらを参照して頂きたい。

InnoDB Plugin 1.0では大幅な性能改善が行われている。主に行われた改善はロックの効率化によるCPUスケーラビリティの向上と、クラッシュリカバリの高速化だ。クラッシュリカバリにおいては、なんと30倍もの高速化を実現している。（参考： InnoDB recovery is now faster…much faster!）文字通りリカバリ時間の短縮になるし、HA化されたシステムではフェイルオーバーの高速化が期待できる。もし今までリカバリ時間の増大を気にしてログファイルサイズを抑えていた（参考：InnoDBのログとテーブルスペースの関係）のであれば、思い切って2GBなどの大きな値を指定しても良いだろう。

InnoDB 1.1で行われた改良は性能改善のものがほとんどだ。バッファプールやロールバックセグメントを複数に分割しているのはロックの競合を避けるためだ。パージスレッドは重い処理であるにも関わらず、他のバックグラウンド処理と同じスレッドで実行されていたので、パージスレッドに時間が掛かるとバックグラウンドスレッド全体の処理が遅れてしまうという問題を引き起こしていた。InnoDB 1.1ではパージが独立したスレッドで実行されるようになったため、パージ処理が他のバックグラウンド処理の足を引っ張ってしまうということがなくなった。

最も特筆すべきはChange Bufferingの導入である。これは以前のバージョンでは挿入バッファと呼ばれていた機能であり、その名のとおりINSERTの性能を向上させるためのバッファであった。（ユニークでないセカンダリインデックスがバッファ内に存在しない場合、セカンダリインデックスへの更新を溜めておくためのバッファである。）もちろん挿入バッファは新たに行を挿入するINSERTに対してしか有効ではなかった。InnoDB 1.1のChanbe BufferingはこれをUPDATEやDELETE、さらにはパージ処理でも有効にしたものである。「ソーシャルゲームのためのMySQL入門 - Technology of DeNA」でdeleteはクッソ重いと揶揄されているが、InnoDB 1.1でChange bufferingを使えばマシになるだろう。設定値はinnodb_change_buffering=allだ。以前の動作に戻したければinnodb_change_buffering=insertsと設定すれば良い。

Windows上での性能改善というのも見逃せない。Web系以外ではWindowsで使われることも割と多いので、Windows上での性能も大切である。どの程度向上しているかということについては、InnoDB開発チームのブログエントリ「MySQL 5.5: InnoDB Performance Improvements on Windows - Transactions on InnoDB」を見て頂くと一目瞭然である。

ロックの改善

InnoDBはCPUスケーラビリティを大きく向上させたが、肝心のMySQL本体でボトルネックが生じては意味がない。そこで、MySQL 5.5では徹底的にロックの見直しが行われ、不要なロックの削除やより粒度の細かいロックへの置き換えが行われている。特に問題となっていたLOCK_openというテーブルをopenするときに必ず獲得されるロックは、Metadata Lockというテーブル名からハッシュで一意的にMDLロックオブジェクトを特定する仕組みで分散が計られている。

CPUスケーラビリティが改善した影響は、特にRead-onlyの負荷に対して効果がある。8月に48コアのマシンを使ってベンチマークを取る機会があったのだが、その結果は実に見事なものであった。ベンチマーク結果は「残暑なんて吹き飛ばすぐらい熱いベンチマークをやろうぜ！！」というエントリに掲載しているのでぜひ見ていただきたい。以下のグラフはRead-onlyの結果の抜粋である。

もちろん全て同じ48コアのマシン、同じOS、同じ設定で測定した結果である。バージョンが違うだけでこれほどまでに性能が違ってくるのである。MySQLは性能面でも着実に進化を続けているのだ！

準同期レプリケーション

準同期（またはSemi-Synchronous）レプリケーションは、MySQL 5.5の新機能の目玉である。性能改善も大きな目玉であるが、運用の安全性・堅牢性を高める準同期レプリケーションも断じて見逃せない。

準同期レプリケーションとは如何なるものだろうか？ご存知の通り、MySQLには全ての更新を記録するバイナリログという機能があり、その内容をマスターからスレーブへ転送することでレプリケーションを行う仕組みになっている。スレーブは2つのスレッドで構成されていて、ひとつはバイナリログを断続的に受け取りリレーログへ記録するI/Oスレッド、そしてもうひとつはリレーログをテーブルのデータに反映するSQLスレッドである。

MySQL 5.1のレプリケーションでは、これらのスレッドは完全にマスター上で実行中のトランザクションとは独立して動いている。つまり非同期である。ところが、MySQL 5.5では次の図で示すように「トランザクションのCOMMITの応答がクライアントへ返る前にスレーブへバイナリログが到達している」ということを保証するモードが追加された。これが準同期レプリケーションである。

準同期レプリケーションではトランザクションがCOMMITする前にI/Oスレッドがマスターからバイナリログを受け取ってackを返す。つまりI/Oスレッドはマスターと同期している。だが、その更新を実際に適用する、すなわちリレーログをSQLスレッドがテーブルへ反映するのは非同期で行われる。つまり、I/Oスレッドは同期処理、SQLスレッドは非同期処理、従って準同期というわけなのだ。

準同期レプリケーションについては、過去のエントリ最強のMySQL HA化手法 - Semi-Synchronous Replicationで詳しく解説している。I/Oスレッドが同期しているということは、マスターがクラッシュしてもスレーブ上にデータが残っているということを意味する。従ってHAとしてバリバリ使えるのだ！スレーブは常に最新の状態になっていて、いつでもクライアントからデータを参照することが可能である。他のHAソリューションのようにクラッシュリカバリが必要ない。フェイルオーバーは一瞬で出来る！！これ即ち最強なのである。

ちなみに、ちょっと注意が必要な運用ではあるが性能を向上させる方法がある。準同期レプリケーションを応用すると、マスター上で「COMMIT時にInnoDBのログをディスクへsyncしない」という割り切った運用をすることが出来るのだ。ディスクへsyncしない場合、マシンがクラッシュするとその直前でCOMMITした内容はInnoDBログには残らない。そこで準同期レプリケーションの出番なのだが、スレーブにデータが到達していれば、マスターがクラッシュしてデータを失っても、スレーブへフェイルオーバーすれば良いだけなので実害はないのである。ログをsyncしないと何が嬉しいのか？それは当然性能の向上である。以下はSATAの3.5インチHDDを搭載するマシンでのベンチマーク結果である。ディスクが低速であるせいもあるが、syncをしないだけでこれほどまでに差が出てくるのだ。

ただし、この運用はスレーブがしっかりと動いていることが前提であるので、監視の作り込みなどが必要になるだろう。

レプリケーションのその他の改善

準同期レプリケーションは確かにとても魅力的な機能であるが、MySQL 5.5におけるレプリケーションの改善はそれだけに留まらない。その改善された内容たるや、まさに出血大サービスと言わんばかりのボリュームである。

リレーログ、master.info、relay-log.infoをディスクへ同期
リレーログの自動修復
レプリケーションハートビート（定期的にマスターの応答を確認）
リレーログの内容を表示するコマンドの追加
マスターごとにイベントをフィルタ(Circularで役立つ機能）
テーブル定義が異なるマスターとスレーブで自動的に型変換
トランザクション非対応テーブルと対応テーブルの混在

一つ目のリレーログ、master.info、relay-log.infoをディスクへ同期する機能は、地味だが効果が期待できる機能である。MySQL 5.1のレプリケーションにおいては、スレーブ上ではこれら3種類のファイルをディスクへ一切同期しない。そのため、スレーブがクラッシュするとかなりの高確率でこれらのファイルがダメージを受けることになる。そうなるると、レプリケーションがどこまで進んだかが分からなくなり、スレーブを再セットアップしなければならない羽目になってしまうのだ。だが、MySQL 5.5ではこれらのファイルを同期するオプションが追加されたため、同期を有効にしておけばスレーブの同期が崩れることはないので、スレーブのクラッシュ時に再セットアップする機会は激減することになるだろう。

ファイルをディスクへ同期すると、当然安全性は高まるが性能は低下する。前のセクションでは、準同期レプリケーションを応用してCOMMIT時にInnoDBログをディスクへsyncさせないことで高速化するという手法を紹介したが、こちらは逆にディスクへの同期が増えるので性能が低下する、即ちスレーブの処理が遅れる可能性があるということに注意しよう。安全性と性能はトレードオフなのである。

FLUSH LOGS

DBAにとって嬉しい機能のひとつに、FLUSH LOGSコマンドでFLUSHするログを個別に指定できるようになったということがある。これまでは全てのログを一括してFLUSHするしかなかったため、「スロークエリログだけをローテートしたいのにバイナリログまで一緒に巻き込んじゃう！！」というような問題があった。これからは、

mysql> FLUSH SLOW LOGS;

というようにログファイルの種類を指定することが可能になった。非常に地味であるが確実にストレスをひとつ減らしてくれる機能であると言えよう。指定できるログの種類は次のとおり。

BINARY
ENGINE
ERROR
GENERAL
RELAY
SLOW

ENGINEというのを指定すると、InnoDBにチェックポイントを行わせることが出来る。MySQL 5.1ではストレージエンジンのログをFLUSHすることはなかったので、こちらはMySQL 5.5で新たに追加された機能となる。

4バイトUTF-8対応

日本人待望の機能の登場である！

MySQL 5.1のUTF-8では、3バイトで収まるもの、すなわち基本多言語面しか対応していなかった。現在では、UTF-8で有効な文字には4バイトのものがあり、それらは追加面と呼ばれているのだが、MySQL 5.1のUTF-8ではそれを扱うことが出来なかった。（どうしても取り扱い対場合にはVARCHARなどの文字列型の使用をやめ、BINARY型のカラムを使うという回避方法があったが、至極面倒であったと思う。）MySQL 5.5では新たに4バイトUTF-8に対応した！どのような方式で対応するかということについては、実は開発者の間で非常に長い議論が行われたのだが、もっとも大きな争点となったのはデフォルトのutf8文字コードの挙動を変更するかどうかという点であった。そして、デフォルトのutf8を置き換えるという案は過去のデータと互換性がなくなるという理由で却下され、utf8mb4という文字コードが新たに追加されることとなった。（逆にデフォルトを置き換えてutf8mb3を追加するという案もあった。その名残として、utf8mb3という文字コードが残っているが、これはutf8へのエイリアスとなっている。）

MySQL 5.5で4バイトUTF-8を利用したい場合には、次のようにテーブルを作成しよう。

mysql> CREATE TABLE t (create definition) ENGINE e CHARACTER SET utf8mb4;

UTF-8は仕様上6バイトまで扱うことが出来るフォーマットなのだが、実際には4バイト目までしか使われていない。従ってutf8mb4を使っておけば当面困ることはないだろう。MySQLで常にutf8mb4を使いたいという人は、character_set_serverにutf8mb4を設定しておこう。

4バイトUTF-8を利用する上で注意したいのは、文字列にインデックスをはる場合の制限である。InnoDBでは767バイトまでの長さのインデックスしかサポートしない。3バイトUTF-8では767÷3=255（小数点切り捨て）文字までのインデックスを作成することが出来た。しかし当然4バイトUTF-8だとインデックスに使用できる文字数は減ってしまう。767÷4=191文字までなので覚えておこう。

COLUMNS PARTITIONING

大規模なデータを扱うときに便利なパーティショニングにも嬉しい改良が加えられている。従来のRANGEパーティショニングでは評価式が必ずINT型の結果を返すようにする必要があった。具体的に言うと、日付型のカラムを用いる場合には次のようにTO_DAYS()関数を用いるなどという対処が必要であった。

create table t1 (
  id int unsigned not null auto_increment,
  mydate date not null,
  primary key(id,mydate)
) partition by range (to_days(mydate)) (
  partition p1 values less than (to_days('2000-04-01')),
  partition p2 values less than (to_days('2001-08-01')),
  partition p3 values less than (to_days('2004-11-01')),
  partition p4 values less than (to_days('2008-01-01')),
  partition p5 values less than (to_days('2010-03-01')),
  partition p6 values less than (to_days('2011-04-01')),
  partition p7 values less than (maxvalue)
);

ここでの問題点としては、当然TO_DAYS()を使うのが面倒であるということもあるが、もっと問題なのはSHOW CREATE TABLEでテーブル定義を確認したとき、TO_DAYS()による記述が消失して、TO_DAYS()の計算結果である数値だけが残ってしまうということだ。

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mydate` date NOT NULL,
  PRIMARY KEY (`id`,`mydate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(mydate))
(PARTITION p1 VALUES LESS THAN (730576) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (731063) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (732251) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (733407) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (734197) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (734593) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

これは非常に解りづらく、管理上デメリットになるだろう。FROM_DAYS()で日付に変換しなおすことはできるが、面倒である。

MySQL 5.5で追加されたRANGE COLUMNSまたはLIST COLUMNSパーティショニングでは、この問題点が解消されている。カラムの値を直接使ってパーティショニングすることが出来るので、テーブル定義は次のように記述することが出来るようになったのだ。

create table t2 (
  id int unsigned not null auto_increment,
  mydate date not null,
  primary key(id,mydate)
) partition by range columns (mydate) (
  partition p1 values less than ('2000-04-01'),
  partition p2 values less than ('2001-08-01'),
  partition p3 values less than ('2004-11-01'),
  partition p4 values less than ('2008-01-01'),
  partition p5 values less than ('2010-03-01'),
  partition p6 values less than ('2011-04-01'),
  partition p7 values less than (maxvalue)
);

当然次のようにSHOW CREATE TABLEには日付そのものが表示される。

CREATE TABLE `t2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mydate` date NOT NULL,
  PRIMARY KEY (`id`,`mydate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(mydate)
(PARTITION p1 VALUES LESS THAN ('2000-04-01') ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN ('2001-08-01') ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN ('2004-11-01') ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN ('2008-01-01') ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN ('2010-03-01') ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN ('2011-04-01') ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

解りやすい！

ところで、COLUMNSが複数形になっていることからも分かるように、COLUMNSパーティショニングでは複数のカラムを組み合わせることが出来る。例えば次のような具合に。

create table t3 (
  id int unsigned not null auto_increment,
  myyear smallint not null,
  mymonth tinyint unsigned not null,
  primary key(id,myyear,mymonth)
) partition by range columns (myyear,mymonth) (
  partition p1 values less than (2000, 4),
  partition p2 values less than (2001, 8),
  partition p3 values less than (2004, 11),
  partition p4 values less than (2008, 2),
  partition p5 values less than (2010, 3),
  partition p6 values less than (2011, 4),
  partition p7 values less than (maxvalue, maxvalue)
);

この例でも分かる通り、COLUMNSパーティショニングでは一つ目のカラムが先に評価され、それでもパーティションが決まらなかった場合には2つ目のカラムが評価されることになる。日付の年、月や数値の整数部分、小数部分というように、どちらを先に評価すれば良いかが分かっている場合に使えるテクニックだろう。（年月をわざわざ分けて指定することは少ないと思うが。）

mysqldump

小粒だが痒いところに手が届く感じの変更がこちらだ。これまで、マスター上でダンプを採取する場合には--master-dataというオプションを利用して、バイナリログのファイル名とポジションを出力することが出来た。だが、スレーブ上でレプリケーションの情報を出力するための、同様のオプションはこれまで存在しなかった。そのため、自前でFLUSH TABLES WITH READ LOCKおよびSHOW SLAVE STATUSコマンドを使って情報を採取するしかなかったのだが、MySQL 5.5では次の3つのオプションが追加され、mysqldumpコマンドひとつでスレーブの情報を出力できるようになっている。

--dump-slave: スレーブからダンプをとった場合、スレーブが参照しているマスターの情報をCHANGE MASTERとしてダンプに含める。
--apply-slave-statements: STOP SLAVEおよびSTART SLAVEコマンドを、CHANGE MASTERの前後に追加する。
--include-master-host-port: CHANGE MASTERコマンドにマスターのホスト名とポートを含める。

LOAD XMLコマンド

これは実のところMySQL 5.1の時に実装が間に合わなかった機能である。

MySQLにデータを取り込むには、LOAD DATAコマンドがもっぱら用いられるが、こちらはCSV（カンマ区切り）データを読み込むためのコマンドである。MySQL 5.5にはLOAD XMLというコマンドが追加され、XMLのデータを取り込むことが出来るようになっている。LOAD XLで取り込むことが出来るXMLファイルを生成する最も簡単な方法は、mysqldumpコマンドを-Xオプション付きで使うことである。アプリケーションが生成したXMLデータを取り込む場合や、mysqldumpでデータをXMLで取り出し、加工してからロードするというような使い方が出来るだろう。

詳細についてはリファレンスマニュアルを参照のこと。

DTrace対応

MySQL 5.5ではDTraceのProbeが有効になっている。DTraceの使い方については、拙著エキスパートのためのMySQL[運用+管理]トラブルシューティングガイドにて章をまるごと割いて解説しているので、是非そちらを参照して頂きたい。DTraceを使うと、必要なときに詳細に内部の挙動を知ることが出来る。トラブルを解析するエンジニアにとって非常に心強い味方なのである。

なお、MySQLの公式ページで配布されているMySQL 5.5.8のSolaris版バイナリではDTraceが有効になっていないようである。恐らくビルド時にオプションを間違えてしまったのだと思われる。ソースコードから自分でコンパイルすると使えるので、Solaris上で今直ぐDTraceを試したい！という人はコンパイルして使おう。ちなみに、Solaris Studioを使わないとDTraceが有効にならないので注意しよう。

SIGNAL/RESIGNAL

SIGNALおよびRESIGNALが導入されたことにより、ストアドプログラム（プロシージャやトリガ）においてエラーハンドリングの記述がより柔軟にできるようになった。SIGNALは、ストアドプログラム内の処理で異常が見つかった場合など、クライアント側で例外処理が必要だと判断した場合に、自ら例外を発生させるための文法である。以下はBEFORE INSERTトリガに仕掛けられたSIGNALの使用例だ。

delimiter //
create trigger t4_bi before insert on t4
for each row begin
  if NEW.a >= 100 then
    signal SQLSTATE VALUE '42000'
      set MYSQL_ERRNO = 123,
          MESSAGE_TEXT = 'The column `a` value should be less than 100.';
  end if;
end;//
delimiter ;

この例では、挿入した行のaというカラムの値が100以上の場合例外を発生させている。もし100以上の値をINSERTした場合処理は失敗し、クライアントはエラー番号123、SQLSTATE 42000のエラーを受け取ることになる。

RESIGNALはHANDLER内でもう一度SIGNALを発生させるための文法である。詳細は割愛するので、必要な人はリファレンスマニュアルを参照して欲しい。

なお、SIGNALおよびRESIGNALはSQL/2003標準で定められた文法である。

PERFORMANCE_SCHEMA

PERFORMANCE_SCHEMAストレージエンジン（以下P_S）という機能が追加された。これは、DTraceのように観測点をmysqldの内部に埋め込んで情報を収集するというものだが、DTraceと違ってどのOSでも利用可能である。もちろんLinuxでもFreeBSDでもWindowsでも！観測点は予めソースコードに組み込まれたところに限られるので、DTraceでいうところのSDT（Statically Defined Tracing）プロバイダのイメージに近い。DTraceでは観測点のことをProbeと呼んだが、P_SではInstrumentという名称になっている。

P_Sはストレージエンジンとして実装されているのだが、観測点が収集したデータはいったんP_Sストレージエンジン内に蓄えられる。ストレージエンジンであるからにはテーブルの実態が存在するのだが、P_S用のテーブルは予め定義が決まっており、mysql_install_dbコマンドを実行時に作成される。MySQL 5.1からアップグレードした場合には、mysql_upgradeコマンドを実行するとテーブルが作成される。

テーブルが作成されていてもデフォルトではP_Sは有効になっていない。P_Sを有効にするには次のようにmy.cnfでオプションを設定する必要がある。

[mysqld]
performance_schema

このオプションが有効になっていると自動的にP_Sストレージエンジン内に統計情報が蓄えられる。MySQL 5.5.8では次のようなテーブルがperformance_schemaデータベース内に存在する。これらはすべてP_Sストレージエンジンのテーブルである。

mysql [localhost] {msandbox} (performance_schema) > show tables;
+----------------------------------------------+
| Tables_in_performance_schema                 |
+----------------------------------------------+
| cond_instances                               |
| events_waits_current                         |
| events_waits_history                         |
| events_waits_history_long                    |
| events_waits_summary_by_instance             |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name    |
| file_instances                               |
| file_summary_by_event_name                   |
| file_summary_by_instance                     |
| mutex_instances                              |
| performance_timers                           |
| rwlock_instances                             |
| setup_consumers                              |
| setup_instruments                            |
| setup_timers                                 |
| threads                                      |
+----------------------------------------------+
17 rows in set (0.00 sec)

試しにthreadsテーブルのデータを参照してみよう。

mysql [localhost] {msandbox} (performance_schema) > select * from threads;
+-----------+----------------+----------------------------------------+
| THREAD_ID | PROCESSLIST_ID | NAME                                   |
+-----------+----------------+----------------------------------------+
|        13 |              0 | thread/innodb/srv_error_monitor_thread |
|         0 |              0 | thread/sql/main                        |
|        12 |              0 | thread/innodb/srv_lock_timeout_thread  |
|        10 |              0 | thread/innodb/io_handler_thread        |
|        17 |              1 | thread/sql/one_connection              |
|        15 |              0 | thread/innodb/srv_master_thread        |
|         1 |              0 | thread/innodb/io_handler_thread        |
|         5 |              0 | thread/innodb/io_handler_thread        |
|         8 |              0 | thread/innodb/io_handler_thread        |
|         4 |              0 | thread/innodb/io_handler_thread        |
|         9 |              0 | thread/innodb/io_handler_thread        |
|        16 |              0 | thread/sql/signal_handler              |
|        14 |              0 | thread/innodb/srv_monitor_thread       |
|         7 |              0 | thread/innodb/io_handler_thread        |
|         6 |              0 | thread/innodb/io_handler_thread        |
|         2 |              0 | thread/innodb/io_handler_thread        |
|         3 |              0 | thread/innodb/io_handler_thread        |
+-----------+----------------+----------------------------------------+
17 rows in set (0.00 sec)

mysqld内で動いているバックグラウンドスレッドの一覧が表示されるが、このような情報は通常のコマンドでは見ることが出来ない。これがP_Sの真価である！また、テーブル名からも分かるように、performance_schemaデータベースには、ロックやファイルアクセスに関する統計情報を扱うテーブルが存在する。mysqldの動作を詳細に調査したい場合や、ボトルネックを発見したい場合などに使うと良いだろう。

最強にデータを収集しまくるP_Sであるが、当然有効化した場合にはオーバーヘッドが生じることになる。本番環境で常にONにしておくという使い方はおすすめ出来ない。

参照： Chapter 21. MySQL Performance Schema - MySQL 5.5 Reference Manual

認証および監査のプラグイン化

柔軟なユーザー管理を行うため、MySQL 5.5では認証の仕組みがプラグイン化されている。現時点では組み込まれているプラグインは従来の認証の仕組みを単にプラグイン化しただけのものであるが、既にインターフェイスは出来ているので、プラグインを作成することで認証の仕組みを変更することが可能だ。外部の認証を用いるにあたり、PROXYユーザーという概念が導入されている。以下はリファレンスマニュアルからの抜粋である。

CREATE USER ''@'' IDENTIFIED WITH ldap_plugin AS 'O=Oracle, OU=MySQL';
CREATE USER 'developer' IDENTIFIED BY 'test';
CREATE USER 'manager' IDENTIFIED BY 'test2';
GRANT PROXY ON 'manager' TO ''@'';
GRANT PROXY ON 'developer' TO ''@'';

LDAP認証をするldap_pluginがあると仮定する。（実際にはそのような実装はない。）CREATE USER ... IDENTIFIED WITH構文を用い、まずはプラグインで認証をするユーザーを作成する。上記の例では全てのユーザーに該当するユーザー名（''@''）が指定されている。この状態で例えばnippondanjiというユーザーがログインを試みると、ldap_pluginによって認証が行われる。正しいパスワードを入力して認証に成功すると、プラグインはPROXYユーザー名をmysqldに返すようになっている。上記ではmanagerおよびdeveloperという2つのPROXYユーザーが作成されているので、ldap_pluginはいずれかのユーザー名を返さなければならない。（PROXYユーザーが存在しなければログインに失敗する。）ログインに成功し、ldap_pluginがdeveloperというユーザー名を返したと仮定しよう。すると、以降はdeveloperユーザーに対して与えられた権限で各種操作を行えるというわけである。これがPROXYユーザーの仕組みだ。

大きな組織であればユーザー管理をしっかりやっているところも多いと思う。既に運用中のユーザー認証を使いたい場合には、ぜひマニュアルやソースコードを参照し、認証プラグインの作成にチャレンジして頂きたい！

また、現時点ではプラグインの実装は存在しないが、監査のためのプラグインインターフェイスも用意されている。こちらはマニュアルにプラグインの書き方が紹介されているので、こちらもニーズのある人はぜひチャレンジして頂きたい！

オプションの変更

MySQL 5.5ではオプション名が変更されたり、廃止になったオプションが存在する。当然ながら機能追加によって増えたオプションもあるが、変更されたり廃止になったりしたオプションは「過去のバージョンで使っていた設定ファイルがそのまま使えない！」という問題を引き起こすので注意が必要である。特にmysqldでは--default-character-setが廃止され、--character-set-serverおよび--collation-serverに変更されている。（さらにデータベースごと、接続ごとに文字コードが設定できるようになっている。）

廃止、変更になったオプションはMyNAの坂井さんがまとめてくださっているので、そちらを是非参照して頂きたい。

[mysql]MySQL 5.5.3-m3 で廃止になった変数やオプションなどを整理しました - sakaikの日々雑感～(T)編

ビルドシステムの変更

MySQL 5.5ではビルドシステムがAutotoolsからCMakeに変更されている。オプション名などが色々変わってしまっているので、慣れないうちは対話式でオプションをON/OFFできる-iオプションを利用すると便利である。

shell> tar xf mysql-5.5.8.tar.gz
shell> cd mysql-5.5.8
shell> cmake . -i
shell> make -j 4
shell> make install
shell> sudo mv sfw_target /usr/local/mysql

まとめ

ここ数年はMySQLにとってかなり大きな変化が訪れ、MySQLの進化が止まってしまうのではないかという懸念があったように思う。だが、MySQL 5.5は劇的なまでの進化を遂げ、多くの機能が追加されたにも関わらず性能が犠牲になることはなく、返って性能が向上しているというのが大きなウリですらあるのだ！これから新たにサーバーをインストールする場合、過去のバージョンに拘る理由はまったくないと言えるだろう。ぜひこの新しいバージョンであるMySQL 5.5を使ってみて頂きたい。

それでは皆さん良いお年を！

ちょっと硬派なコンピュータフリークのBlogです。

2010-12-30

MySQL 5.5新機能徹底解説

InnoDBの大幅な改善

ロックの改善

準同期レプリケーション

レプリケーションのその他の改善

FLUSH LOGS

4バイトUTF-8対応

COLUMNS PARTITIONING

mysqldump

LOAD XMLコマンド

DTrace対応

SIGNAL/RESIGNAL

PERFORMANCE_SCHEMA

認証および監査のプラグイン化

オプションの変更

ビルドシステムの変更

まとめ

2 コメント:

コメントを投稿

漢（オトコ）のコンピュータ道@Facebook

漢の入魂著書

漢のアーカイブ

More info...

注意事項