MySQL 5.5 Updates · 2011-02-10 · MySQLとInnoDBのバージョン • MySQL 5.4 –...

<Insert Picture Here>

MySQL 5.5 UpdatesMikiya OkunoTechnical AnalystMySQL Global Business Unit

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

満を持して出ました！

5.1

5.4

5 .56.0 X

いくつかの機能を削除

5.0

参照 http://forge.mysql.com/wiki/Development_Cycle

MySQL 5.4/5.5で追加された機能とは？

• MySQL 5.4– InnoDBの性能改善– DTrace Probeの追加– SHOW ENGINE INNODB STATUSの拡張– 設定値のデフォルト値を変更

• MySQL 5.5– InnoDBの機能・性能改善（InnoDB Plugin）– メタデータのロック改善– Semi-Synchronous Replication– 4バイトUTF-8対応– RANGE/LIST COLUMNSパーティショニング– PERFORMANCE_SCHEMA– XML機能の拡充（LOAD XML文など）– SIGNAL/RESIGNALなど多数

6.0から予定されていた

機能？


InnoDB Improvements

MySQLとInnoDBのバージョン

• MySQL 5.4– ビルトインInnoDBをベースにして独自に性能改善。

• 後に改善の内容はInnoDB Pluginに移植された。• InnoDB Plugin 1.0

– 合併の前にInnobase OYが独自に開発。• 後にMySQL 5.1にバンドルされることに。

– MySQL 5.1.46でGA版に。• MySQL 5.5

– InnoDB Plugin 1.1がビルトインに。• InnoDB Plugin 1.0をベースに機能と性能を強化。

What's New in MySQL 5.4 InnoDB

• 性能の強化– バックグラウンドI/Oの増加– I/O負荷の調整– Google SMP patch

• 解析機能の強化– SHOW ENGINE INNODB STATUSの強化

What's New in InnoDB Plugin 1.0

• Fast Index Creation• 新ファイルフォーマット

– BLOB, TEXT, VARCHARが完全にオフロードページへ！– データ圧縮

• INFORMATION_SCHEMAの追加• 使い易さの改善

– オンライン変更出来るパラメータの増加– TRUNCATE TABLEによる.ibdファイルの再作成

etc• 性能改善

– クラッシュリカバリ時間の短縮– グループコミット

etc

新ファイルフォーマット Barracuda！

• レコード長8KB制限の緩和– BLOB、TEXT、VARCHARが完全にオフロードページに

• もともとオフロードページに格納されているが、先頭の768バイトは通常のページ内に残す必要があった。

• これらのカラムがたくさんあるとレコードサイズ制限にひっかかる。

– データ圧縮• 以前のバージョンはAntelope

– アルファベット順– Zまで既に決まっている。– trx/trx0sys.c

Fast index Creation

• セカンダリインデックスの作成が高速化！– オンラインじゃないんです・・・– インデックス作成中は参照のみ可

BEFORE! AFTER!

オリジナルのテーブル

新しい定義のテーブル

オリジナルのテーブル

new index

完全なデータコピー

new index

インデックスだけを再作成

バイナリログ利用時のグループコミットが高速化！

• InnoDB Plugin 1.0.4において、「バイナリログを有効にするとグループコミットが効かなくなる問題」が修正– 参考： InnoDB Plugin 1.0.4 - InnoDB史上極めて重要なリリ

ース - open database lifehttp://opendatabaselife.blogspot.com/2009/08/innodb-plugin-104-innodb.html

Very Fast Crash Recovery!

“Dirty” pages 1,007,907; Redo bytes: 3,050,455,773

Crash Recovery Performance: Plugin 1.0.6 vs. Plugin 1.0.7

31.70

426.30458.00

2.00 12.00 14.000.00

50.00

100.00

150.00

200.00

250.00

300.00

350.00

400.00

450.00

500.00

Operations

Min

utes Plugin 1.0.6

Plugin 1.0.7

Plugin 1.0.6 31.70 426.30 458.00

Plugin 1.0.7 2.00 12.00 14.00

scan apply total

ログサイズは何も考えずにマックスでOK！

What's New In InnoDB 1.1 / MySQL 5.5

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

性能改善！

Change Buffering

• 挿入バッファ（Insert Buffering）をUPDATE、DELETE、パージにも拡張。

• ユニークでないセカンダリインデックスがキャッシュにない場合、インデックスへの更新を一時的にバッファへ保留。

データファイル

InnoDBバッファプール

セカンダリセカンダリインデックスインデックス

データのフェッチはとても時間が掛かる！

Change buffer

更新

後でマージ！


Locking Improvements

ロックの改善！！• プロファイリング結果からボトルネックになっているロックを特定– LOCK_open

• メタデータの排他処理をMDLに置き換え– LOCK_alarm

• ソケットのタイムアウト（SO_[SND|RCV]TIMEO）をサポートしているプラットフォームでは削除

– LOCK_thread_count• 現在はクエリ実行時に1〜２回のロック• 5.5では接続時とSHOWコマンド実行時だけに

– LOCK_grant, THR_LOCK_lock, THR_LOCK_charset• 不要な排他ロックであったため削除

– クエリキャッシュ関係– バイナリログ関係

sysbench read only

1 4 8 16 32 48 64 96 128 256 512 10240

2000

4000

6000

8000

10000

12000

4.1.22 ro5.0.91 ro5.1.49 ro5.1.49-plugin ro5.5.5 ro

AMD Opteron 48 core @ Fedora 13


Semi-Synchronous Replication

準同期レプリケーション図解

マスタースレーブ

アプリケーション

StorageEngine

Binlog

ConnectionThread

Relaylog

StorageEngine

1. commit write

2. update binlog3. update storage engine

4-2. send updates

4-1. send_ok()

5. apply updates(asynchronous)5-1. ack

5-2. apply updates(asynchronous)

6. send_ok()

4. send updates

準同期レプリケーションの応用COMMIT時のディスク同期なし

Normal Semi-Sync Read-Only0

100

200

300

400

500

600

ディスク同期ありディスク同期なし

sysbenchMySQL 5.5.5-m3Athlon 64 2 Core7200rpm SATAGigabit Ether単位: TPS


Other Replication Improvements

MySQL 5.5のレプリケーションの主な改善点

• リレーログ、master.info、relay-log.infoをディスクへ同期• リレーログの自動修復• レプリケーションハートビート（定期的にマスターの応答を確認）

• リレーログの内容を表示するコマンドの追加• マスターごとにイベントをフィルタ(Circularで役立つ機能）

• テーブル定義が異なるマスターとスレーブで自動的に型変換

• トランザクション非対応テーブルと対応テーブルの混在


Flush Logs

FLUSH LOGSで個別のログが指定可能に！

• mysql> FLUSH BINARY LOGS;• mysql> FLUSH ENGINE LOGS;• mysql> FLUSH ERROR LOGS;• mysql> FLUSH GENERAL LOGS;• mysql> FLUSH RELAY LOGS;• mysql> FLUSH SLOW LOGS;


mysqldump Improvements

mysqldumpの新しいオプション → スレーブの情報採取が容易に！

• --dump-slave ... スレーブからダンプをとった場合、スレーブが参照しているマスターの情報をCHANGE MASTERとしてダンプに含める。

• --apply-slave-statements ... STOP SLAVEおよびSTART SLAVEコマンドを、CHANGE MASTERの前後に追加する。

• --include-master-host-port ... CHANGE MASTERコマンドにマスターのホスト名とポートを含める。


4 bytes UTF-8

UTF-8の符号体系

byte 文字種 5.1 5.51 ASCII ○ ○2 ラテン、ギリシャ、アラビア等 ○ ○3 CJK（BMP）、インド系諸文字等 ○ ○4 古代文字、3に含まれない漢字 × ○ New!!

第3・第4水準漢字の一部

4バイトUTF-8の使い方

• MySQL 5.1– utf8 … 3バイト

• MySQL 5.5– utf8 … 3バイト– utf8mb3 … utf8へのエイリアス

– utf8mb4 … 4バイト


Columns Partitioning

日付カラムの値を直接書けるようになった！〜 BEFORE 〜

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));

日付カラムの値を直接書けるようになった！〜 AFTER 〜

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));

ちなみに従来の仕様だと・・・

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()で日付を表示→ 面倒！

複数のカラムの値を組み合わせ！

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));


Authentication and Audit Plugin

認証のプラグイン化

• 外部の仕組みを用いて認証が可能。– ただし現在はプラグイン化しただけ– 利用可能なのは従来と同じ動作をするプラグインのみ– ぜひプラグイン作成にチャレンジを！

• 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 ''@'';

認証のプラグイン化〜図解〜

クライアント

mysqld

認証プラグイン

権限テーブル

1. login attempts

外部認証機構

2. credential

3. proxyuser name

4. granted as proxy user


Performance Schema

PERFORMANCE_SCHEMA概要

• 略称はP_S• DTraceに似た情報収集と表示の仕組み。• プロファイリングに利用可能。

– 情報は相当マニアックなので開発者向けかも。• P_Sのアーキテクチャー

– ストレージエンジンとして実装– ソースコード中の随所にしかけられた「Instrument」から情報を収集。

– 統計情報はP_S内に保管される。– P_Sストレージエンジンを通じて、SELECTによりデータを取得。• 各種集計関数が利用可能

– P_Sの操作はP_SテーブルをUPDATEすることで行う。

PERFORMANCE_SCHEMAの仕組み

mysqld内の随所に仕掛けられたInstruments

PERFORMANCESCHEMA

Storage Engine集計

SELECT ... FROM PERFORMANCE_SCHEMA.EVENTS_WAITS_CURRENT WHERE ...;

SQLでアクセス行データ

PERFORMANCE_SCHEMAの使用例mysql> select * from performance_schema.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)


XML Improvements

LOAD XML INFILE

• mysqlコマンドやmysqldumpコマンドで生成したXMLをインポートできる。– エクスポート

• shell> mysql --xml -e "SELECT * FROM world.city" > city-dump.xml

– インポート• mysql> USE world;• mysql> CREATE TABLE city ...• mysql> LOAD XML INFILE 'city-dump.xml' INTO TABLE city;


Signal and Resignal

SIGNAL: ストアドプログラム中で意図的に例外を発生

delimiter //CREATE TRIGGER t1_bi BEFORE INSERT ON t1FOR 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 ;


Option Changes

廃止・変更になったオプション

• サーバー側の--default-character-set– --character-set-serverを使用– クライアント側では変わらず--default-character-set

• --master-*– レプリケーションはCHANGE MASTER TOで設定

• --enable-locking/--skip-locking– --external-locking/--skip-external-lockingを使用

• --log-bin-trust-routine-creators– --log-bin-trust-function-creatorsを使用既存のmy.cnfでサーバーが起動しなくなったらエラーログをチェック！

参照： http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html

廃止・変更になったコマンド

• SHOW INNODB STATUS– SHOW ENGINE INNODB STATUSを使用

• SHOW TABLE TYPES– SHOW ENGINESを使用

• SHOW PLUGIN– SHOW PLUGINSを使用

• BACKUP TABLE/RESTORE TABLE– 廃止

参照： http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html


Build System Changes

CMakeの採用

• ビルドツールがGNU AutotoolsからCMakeへ– BEFORE: ./configure --prefix=/usr ...– AFTER: cmake . -DCMAKE_INSTALL_PREFIX=/usr ...

• Windowsでも使える！• 慣れないうちは cmake . -i がオススメ• 参照

http://forge.mysql.com/wiki/Autotools_to_CMake_Transition_Guide

Conclusion

• 高速化＋高機能化– InnoDBの高速化、機能追加– 日本人待望の4バイトUTF-8– 準同期レプリケーション– パーティショニングの改善などなど

• おすすめです！！

まずは使ってみてください。

Thank You!

宣伝

• 新書籍「MySQL Cluster構築＆運用バイブル」（仮題）– MySQL CLusterのお供にぜひどうぞ！

• もくじ1.MySQL Clusterのコンセプト2.インストール3.基本操作4.MySQL Clusterを用いた開発5.パフォーマンス6.ディスクオブジェクトの利用7.Cluster Replication8.監視9.メンテナンス10.典型的なトラブルと対処法

MySQL 5.5 Updates · 2011-02-10 · MySQLとInnoDBのバージョン • MySQL 5.4 –...

Documents

Transcript of MySQL 5.5 Updates · 2011-02-10 · MySQLとInnoDBのバージョン • MySQL 5.4 –...