MySQL 5.4 詳細(当社比)情報 - sakaikの日々雑感～(T)編

　MySQL 5.4 について以下のURLにかなり詳しい情報が掲載されています。もちろん MySQL 5.4.0-beta 時点での情報ですので「予定は未定」的なものも多いのですが、このフェーズでこれだけのドキュメントがまとめられていることに、先行きの明るさを感じます。
http://dev.mysql.com/doc/mysql-5.4-features/en/index.html

　で、私エイゴヨメマセーン。　理解した(と自分が思った)とたんにもう忘れているのでメモしないと理解できないのです。
ということで、超訳というか、いやすでに訳じゃないです、妄想ですが、こんなことが書いてありますというメモを作っているので折角なのでここに書いておきます。　書いてないことまで書いてありますので注意。ちゃんとした情報を知りたい方は原文に当たってください。　明らかに意味違うよってところはコメント等で教えていただけると幸いです。あまり細かいこと気にしていないので「いいじゃんそれくらい」って対応することもあるかもしれませんがご了承を。。
　あと一応下記、ドキュメントの全セクション名が書いてありますが飽きたら放置しますので全超訳は期待しないでくださいませ。

MySQL 5.4 の特徴のまとめ

(http://dev.mysql.com/doc/mysql-5.4-features/en/index.html の超超訳)

要するに：
- MySQL 5.4の特徴について書くよ。MySQL 5.4はMySQL5.1をベースにしてるよ。ちっちゃいけどインパクトのでかい修正をしてるんだ。MySQLサーバのスケーラビリティとパフォーマンスを高めるためのね。

1. MySQL 5.4 について最初に言っておくか

Sun の MySQLパフォーマンスとスケーラビリティを高めようプロジェクトはInnoDB, Falcom, MySQL Cluster のパフォーマンスとスケーラビリティを高めるために頑張るよ。とりあえず InnoDB を頑張ってる。

MySQL 5.4はMySQL 5.1をベースにして、サーバのパフォーマンス、えーと特にスケーラビリティを高めようとしてるんだ。ハードウェアの性能を最大限に引き出すよ。いまベータ版。
ざっとこんな感じ。詳細は別のセクションにずらずら書いてるよ。

最重要：マルチコアでスケールするよ！　最近CPUクロックをアップするよりもコア数を増やす方向だよね。クロックアップしてくれたらDBも速くなるのにさ、別の方法で高速化を工夫しなきゃいけないんだ。MySQL 5.4 ではSMPを上手に使って、MySQLが持ってた多コアCPUでのボトルネックを抹殺しちゃおうと思うんだ。

ポイントはInnoDB の特にロックやメモリ管理やスレッドコンカレンシ（同時実行）だね。

InnoDB I/O サブシステムが I/Oキャパをもっと効果的にフルに使えるようにするよ！
Solaris上での運用をもっとラクにするよ！ (訳注：もちろん！）
実行やパフォーマンス情報をとりやすくするよ。DTrace 使えるようにするとか、SHOW ENGINE INNODB STATUS 出力を拡張したり、status の変数を追加したりね。
16とか64CPUで 4GB以上のメモリのために "ハコから出してポン"な初期設定がもっといい値になってるよ。

MySQL 5.4 は、「MySQL のスケーラビリティとパフォーマンスを高めるプロジェクト」のメンバだけじゃなくてMySQLコミュニティの他のエンジニア達のパッチとかも含んた結果なんだ。
次のセクションでは、MySQL 5.4 が MySQL 5.1 とどう違うかとか利用可能なシステムとかについて話すよ。

2. 動作プラットフォームとインストール／アップグレードのこと

　MySQL 5.4 シリーズの最初のバージョンは MySQL 5.4.0 だよ。
MySQL 5.4.0 のバイナリは以下の３つのプラットフォーム用だけだよ。
-Solaris 10-x86_64
-Solaris10-SPARC
-Linux x86_64-glibc23.
ソースからコンパイルするなら BUILD/build_mccge.sh を使って。詳しくは --help で。

MySQL 5.4.0 を使うための手順は基本的に MySQL 5.1 シリーズの場合と同じだよ。詳しくは 5.1 のマニュアル（http://dev.mysql.com/doc/）を見てね。
すでに動作している MySQL 5.1 から MySQL 5.4 に上げようと思ってるなら InnoDB のログファイルの設定変更のせいで問題があるかも。MySQL 5.4.0 では、デフォルトの innodb_log_files_in_group の値が２から３に変更になっているんだ。あと innodb_log_file_size のデフォルトも 5MB から 128MB になってるし、この最小値も 1MB から 32MB に変更になってるよ。
　バージョンを上げる方法を伝授しようか。２つ方法があるよ。

1. なるべく今の設定値のまま動かす方法

　もし現在 MySQL 5.1 で innodb_log_files_in_group=2 （または無指定でデフォルトで動作している場合）は、以下のように my.cnf にこの値を明示する。 innodb_log_file_size は最低値が32MBなので、これ以上の値で動いていた場合は以下のように(おそらくそのままで)OK。32MB以下の場合はこの方法は使えない(拡張しなきゃいけないので）

[mysqld]
innodb_log_files_in_group=2
innodb_log_file_size=64M

2. 古い設定は捨て去って新たに 5.4 を動かす方法

　しがらみに囚われずにできるよ。トランザクションが半端にならないようにちゃんとシャットダウンさせてね。innodb_fast_shutdown 値の設定によって落とし方は変わるよ：

- innodb_fast_shutdown が２以外の場合：

　MySQLサーバを停めて、エラーがないことを確認してね。古いログファイルを安全な場所にコピーしておくといいよ（なんかうまくいかなくて戻すときのために）。古いログファイルを消してmy.cnf に新しい設定を書いて、MySQL 5.4.0 にして起動。ログファイルがなかったら(my.cnfの設定に従って)作ってくれるよ。

- innodb_fast_shutdown が２の場合：

　現在動いているMySQLサーバをシャットダウンして、innodb_fast_shutdown の値を１にして再起動。で、また落として↑で書いたようにチェックしたり修正したりする。その後 innodb_fast_shutdown の値を２に戻して MySQL 5.4.0 を起動したらいいよ。

3. スケーラビリティの改良

　MySQL 5.4 ではマルチコアシステムでのSMPでパフォーマンスが上がるよ！InnoDBのロックとかメモリ管理とかスレッドコンカレンシーをいじってるんだ。Googleさんがつくってくれたパッチをベースにしてるよ。
http://code.google.com/p/google-mysql-tools/wiki/SmpPerformance

　MySQL 5.4 は InnoDB で RWロックのパフォーマンスを良くするGoogleのSMPパッチが入ってる。このパッチはmutexよりもアトミックな命令を使ってRW-lockを実装してるんだ。あと、InnoDBのメモリアロケーションが（訳注：えーとよくわかんないんだけど、mallocとかmtalloc とかtcmallocとかでいいのを使うってことだと思うんだ^^;）。
　MySQL 5.4 は InnoDB スレッドコンカレンシーをハンドルすることでlock-freeな新機構も実装するんだ。
time-base のね。スケーラビリティの面で mutex よりも yield and sleepなんだ（なんだ？）。innodb_thread_concurrency_timer_based変数をいじるとどっちのコンカレンシー手法を使うか決められるよ。デフォルトでは enabled(つまり新しい方法を使う)だ。 disable にすると、今までと同じように mutexベースの方法を使えるよ。
　使用中のスレッド数によって time-baseがいいかどうか分かれるよ。256の場合にtime-baseにすると約15%パフォーマンスが上がるし、スレッド数が少ない場合(コア数の2倍以下)だとパフォーマンスが約1%落ちるんだ。

　RWロックの再実装とlock-freeスレッドコンカレンシーメソッドには、アトミックな命令を使うんだ。新しい status 変数として Innodb_have_sync_atomic っていうのがアトミックな命令が使えるかどうかを表しているよ。 Innodb_have_sync_atomic が OFF だと innodb_thread_concurrency_timer_based をenable にしても何も起きないよ。

　あと、その他のstatus値として Innodb_heap_enabled ってのを作ったんだけど、これは built-in な InnoDB メモリマネジャーが使われているかどうかを表しているんだ。（ON なら使われている、OFFなら使われていない）

4. InnoDB I/O サブシステムの変更

5. Solaris サポートの拡張

6. 診断とモニタリングの能力向上

　MySQL 5.4 では実行とかパフォーマンスとかの情報とよりお友達になれるよ。診断方法の改善には Dtrace とか SHOW ENGINE INNODB STATUS 出力結果の改善とか、新しい status 値とかがあるよ。

DTrace サポート

　MySQL 6.0に入れ込んであった DTrace サポートを持ってきたよ。 DTrace は Solaris, Mac OS X, FreeBSD で動くんだ。 MySQL での DTraceに関する情報は http://dev.mysql.com/doc/refman/6.0/en/dba-dtrace-server.html を見てもらいたい。

SHOW ENGINE INNODB STATUS 出力結果の拡張

　SHOW ENGINE INNODB STATUS がもっといろんな情報を教えてくれるようになるよ。Googleさんのパッチのおかげです。

http://code.google.com/p/google-mysql-tools/wiki/InnodbStatus
http://code.google.com/p/google-mysql-tools/wiki/NewShowInnodbStatus

変更点は以下の通り。
(1)BACKGROUND THREAD セクションが加わったよ


















-








BACKGROUND THREAD

















-








srv_master_thread loops: 12 1_second, 12 sleeps, 0 10_second, 2 background,
  2 flush
srv_master_thread log flush: 12 sync, 15 async
srv_wait_thread_mics 0 microseconds, 0.0 seconds
spinlock delay for 6 delay 20 rounds is 15 mics

srv_master_thread の行はメインのバックグラウンドスレッドで処理が為されたことを表していて、spinlock の行はスピンロックが発生してスリープになるまでのマイクロセカンドを表している。

(2)SEMAPHORES セクションが加わったよ
　SEMAPHORESセクションはmutexでのOS待ちごとに何回のスピンロックが回ったかを示している。


















-








SEMAPHORES

















-








...
Spin rounds per wait: 0.00 mutex, 20.00 RW-shared, 0.00 RW-excl

(3)FILE I/O セクションが加わったよ
　FILE I/O セクションは個別の I/O スレッドの詳細情報を表す。読み書きのページ数、それらの操作でのシステムコールの回数、完了までのミリセカンド（全体と、リクエストごとの平均）などの情報が含まれる。














-






FILE I/O













-






I/O thread 0 state: waiting for i/o request (insert buffer thread)
  reads 0 writes 0 requests 0 io secs 0.000000 io msecs/request 0.000000
  max_io_wait 0.000000
I/O thread 1 state: waiting for i/o request (log thread)
  reads 0 writes 3 requests 3 io secs 0.006906 io msecs/request 2.302000
  max_io_wait 6.861000
I/O thread 2 state: waiting for i/o request (read thread)
  reads 33 writes 0 requests 2 io secs 0.005925 io msecs/request 2.962500
  max_io_wait 5.895000
...
Summary of background IO slot status: 0 issued, 0 done, 0 claimed, sleep set 0

新しい status 変数

　Innodb_have_sync_atomic と Innodb_heap_enabled の２つの status 値が加わったよ。MySQL 5.4 の SMP 強化のために特別にネ！　セクション３の「3. スケーラビリティの改良」を見てよ。
　Innodb_wake_ups は起こるべきでなかった wakeup の回数を表しているよ。つまり mutex で折角wakeupしたのに条件があわなくて結局そのまま sleep しちゃったものの数さ。

7. 変数とかオプションとか

　MySQL 5.4 ではいくつかの変数とオプションが追加されたり変更されたりしたよ。「ハコからだしてポン」な状態でも十分よく動くようにね。16CPUのx86 とか 64CPUのCMTとかで。
　（ちゅうい：MySQLの旧バージョンから MySQL 5.4 に上げるときにはinnodb_log_file_size値とinnodb_log_files_in_groupが変わってるから気をつけて。「2. 動作プラットフォームとインストール／アップグレードのこと」を読んでね。）

　新しいシステム変数はすべてグローバル変数だよ。セッション値は持たないよ。あと、実行中に変更できない（ダイナミックじゃない）からね。値を変更するためにはサーバ起動時にセットしてなきゃいけないよ（訳注：再起動が必要、って意味で捉えていいかな）。
SHOW VARIABLES で ON/OFF な変数は、サーバ起動時には 1/0 に相当するよ。

新しいシステム変数の仲間たち：
- innodb_extra_dirty_writes:
  - デフォルトenable。enable の時に、サーバがアイドル状態の時に dirtyバッファページがフラッシュされる。仮にdirtyページの割合がinnodb_max_dirty_pages_pctで決められる最大パーセンテージよりも少なかったとしても行われる。
- innodb_io_capacity:
  - サーバが行うことができる1秒あたりの I/O操作の最大値(IOPS)。デフォルト200。
- innodb_max_merged_io:
  - バックグラウンドI/Oスレッドから大きなI/O をマージする（？）I/Oリクエストの最大値。デフォルト64。（訳注：すいません意味不明）
- innodb_read_io_threads, innodb_write_io_threads:
  - 先読みリクエストやdirtyページをバッファキャッシュからディスクに書込むために使われるバックグラウンド I/O スレッドの数。デフォルト8。
  - →MySQL 5.4.3-beta で、デフォルトが 4 に下げられた
- innodb_thread_concurrency_timer_based:
  - enableの時、新しいロック機構 (lock-free, timer-based) をスレッドコンカレンシーの制御に使う。disable の場合は旧来の mutex-based 手法が使われる。新コンカレンシー手法はアトミック機構の有無による。Innodb_have_sync_atomic変数が OFF ならこの innodb_thread_concurrency_timer_based は何の効果も及ぼさない。

既存変数が変更となった者たち：
- innodb_additional_mem_pool_size:
  - デフォルト値が 1MB から 8MB に変更。最小値が 512KB から 2MB に変更。
  - →MySQL 5.4.2-beta で、最小値が 512KBに戻された
- innodb_autoextend_increment:
  - デフォルト値が 8MB から 64MB に変更。
  - →MySQL 5.4.3-beta で、再度 8MB に戻された
- innodb_buffer_pool_size:
  - デフォルト値が 8 MB to 1GB に変更。最小値が 1MB から 64MB に変更。
  - →MySQL 5.4.2-beta で、最小値が 5MB に下げられた
  - →MySQL 5.4.3-beta で、デフォルト値が 128MB に下げられた
- innodb_file_io_threads:
  - 廃止。（innodb_read_io_threads と innodb_write_io_threads に変更)。
- innodb_log_buffer_size:
  - デフォルト値が 1MB から 16MB に変更。最小値が 256KB から 2MB に変更。
  - →MySQL 5.4.2-beta で、最小値が 256KB に戻された
  - →MySQL 5.4.3-beta で、デフォルト値が 8MB に下げられた
- innodb_log_file_size
  - デフォルト値が 5MB から 128MB に変更。最小値が 1MB から 32MB に変更。
  - →MySQL 5.4.2-beta で、最小値が 1MB に戻された
  - →MySQL 5.4.3-beta で、デフォルト値が再度 5MBに戻された
- innodb_log_files_in_group:
  - デフォルト値が 2 から 3 に変更。
  - →MySQL 5.4.3-beta で、再び 2 に戻された
- innodb_max_dirty_pages_pct:
  - デフォルト値が 90 から 75 に変更。最大値が 100 から 99 に変更。dirtyバッファプールを完全に受け付けないように。（訳注：よくわかんない。。to never allow a completely dirty buffer pool.）
innodb_thread_concurrency:
- - デフォルト値が 8 から 0 に変更。つまりコンカレンシーは従来の 8 から無限大(0) となる。
- table_definition_cache:
  - デフォルト値が 64 から 400 に変更。
- table_open_cache:
  - デフォルト値が 64 から 400 に変更。

新しい status 変数：
- Innodb_have_sync_atomic:
  - アトミック機構の有無をあらわす
  - →MySQL 5.4.2-beta で、名前を Innodb_have_atomic_builtins に変更
- Innodb_heap_enabled:
  - InnoDB の buil-in メモリマネージャが使われているかをあらわす、（ON が使われている、OFFは使われていない）
  - →MySQL 5.4.2-beta で、廃止
- Innodb_wake_ups:
  - 起こるべきでなかった wakeupsの数。これらは条件がまだ揃わないためにまたsleepになってしまったような mutexのwakeupだ。
  - →MySQL 5.4.2-beta で、廃止
- Innodb_sync_spin_loops
  - →MySQL 5.4.2-beta で、デフォルト値が 20から30に変更