Oracle MySQL Developer Day – Osaka
http://www.oracle.com/webapps/events/ns/EventsDetail.jsp?p_eventId=141276&src=7328803&src=7328803&Act=69
大阪で、MySQLのイベントなんて珍しいな、と思って行ってきた。
勉強会で見かけるメンバーは、誰もいなかったｗ
スーツ率高し。
Mac率が低くてびっくりした。
勉強会ならMac9割だけど、この日はMac3割。
MBAは50人中3台ぐらい(僕含む)！脅威の少なさ！
お弁当が出るセミナーも初めてだ。
おいしかった。
あとタリーズのコーヒーが飲めたり、モロゾフの焼き菓子があったり…
ではレポート。
MySQL レプリケーション&スケーラビリティ
MySQLは非同期レプリケーションが基本。
マスタはバイナリログを書き込むだけ。
スレーブはマスタに接続し、バイナリログを読んでデータを更新する。
マスタに負荷が増えることなく、スケールする。
MySQLのレプリケーションはeasyな作りと言える。
同期レプリケーションである必要はあるか?
同期は処理が遅いし、実現するためには高いオプションを購入しなければならない。
binlogとはバイナリログをのこと。
レプリケーションとバックアップのロールフォワードに使用するファイルである。
マスタのバイナリログを読んで、スレーブは自分のローカルにリレーログを作る。
リレーログの中身はバイナリログと同じ。
リレーログはIOスレッドで読み込む。シングルスレッドで動作する。
なお、SQLはSQLスレッドで実行する。
マスタの処理と非同期でスレーブにレプリケーションする。
レプリケーションは、非同期/準同期/同期の3種類がある。
準同期にして、リレーログに書き込んだことを確認している状況で、
マスタに障害があったとき、どのスレーブをマスタにするか。
スレーブはリレーログからまだSQLを実行していない。
この機能はMySQL5.5のプラグインとして提供している。
バイナリログのフォーマットは、文ベースではSQL文をそのまま書き出す。
ではSQLでランダム関数を使ってinsertしたときは?
MySQL3.xから、そうした場合に対応できるようになっている。
now関数なども大丈夫。
文ベースと行ベースを合わせたMixedがある。
MySQLのデフォルトは文ベース。
新規ではMixedにするのがよい。
バイナリログはマスタ障害があるとデータをロストする可能性がある。
スレーブは任意のタイミングでサーバを止められる。
そのためコールドバックアップできる。
スレーブはいつでも切断、再接続できる。
MySQLでは、フルバックアップするとそれ以前のバイナリログを自動的に削除する。
InnoDBのみredoログがある。
デフォルトでは、スレーブにおいて、マスタから取得したSQL文はバイナリログに記録しない。
設定で変更できるため、あるマスタにとってのスレーブが、別のスレーブにとってのマスタになることができる。
運用と管理
データベースメタデータのスキーマが、ANSIで定義されている。
- information_schema
- performance_schema
dtraceの代わりもできる。
MySQL WorkBench Standard EditonとMySQL Enterprise BackUpは有償である。
Enterprise BackUpはホットバックアップできる。
Connector/JからMonitorに直接送れるので、プロキシを使う必要がない。
MySQL Cluster
HA(高可用性)なアーキテクチャ。
書き込みを大量に処理する。
秒間5万トランザクションでも処理する。
シェアードナッシング。共有ディスクを使わない。
自動フェールオーバー時間が1秒以下である。
自動シャーディングする。自動的にデータをパーティショニングするということ。
地理的にまたいだフェイルオーバーもできる。
もとは通信インフラ用にエリクソンが開発した。
最近ではWebアプリケーションでも使われている。
NoSQLのインタフェースも備えている。
データノードを増やすことで、書き込みの増大に対応する。
オンラインから無停止で実行できる。
ロードバランスも自動的に行う。
データノードが増えれば自動的に振り分ける。
ノードで並列処理をする。
アプリケーションからは透過的に扱える。
データセンタ間のクラスタレプリケーションなど、クラスタ間でもレプリケーションできる。
マスタマスタ構成のレプリケーションでは、データの矛盾を検知できる。
デフォルトではインメモリデータベースとなる。
ディスクに書くことも設定できる。
MySQL Cluster 7.2がDMR(Development Millestone Release)となった。
アップグレードしたときは、analyze table [table_name]する。
クラスタノードとして、memcachedを追加できる。
MySQLとmemcachedをまとめて扱える。組み合わせなくてよい。
クラスタのレプリケーションも、MySQL Serverのレプリケーションがベース。
同期レプリケーションもできる。
地理的に離れたデータセンタにデータノードを配置し、1つの大きなクラスタにできる。
実例としては、2つのデータセンタが700km離れているシステムが運用中である。
JDBCは遅い。
Cluster JPAライブラリもある。
テーブル間にリレーションがない場合、Cluster Jで高速にアクセスできる。
最速はC++のNDB APIである。
パフォーマンスチューニング
MySQLは従来コア数を増やしても性能が上がらないRDBMSだった。
5.5以降は性能が上がる。
ハードウェアはx86の64bitがほとんどである。
メモリを多く積めるのも理由である。
ディスクはHDDが多く、速ければ速いほどよい。
RAID 1 + 0にする。
RAID 5は効率良く見えるが、更新が遅くなるため、推奨しない。
SSDやFusion-ioもよい。
NICは2重化する。
マスタよりスレーブのほうが高いマシンパワーを必要とする。
スレーブはシングルスレッドで処理するため。
実例では、スレーブがSSD、マスタがHDDというところもある。
パラメータ。
mysql> show variables like 'auto%' -> ; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | +--------------------------+-------+ 4 rows in set (0.01 sec) mysql> show status like 'innodb_buf%'; +-----------------------------------+-------+ | Variable_name | Value | +-----------------------------------+-------+ | Innodb_buffer_pool_pages_data | 20 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 0 | | Innodb_buffer_pool_pages_free | 492 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_total | 512 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_ahead_seq | 0 | | Innodb_buffer_pool_read_requests | 146 | | Innodb_buffer_pool_reads | 13 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 0 | +-----------------------------------+-------+ 12 rows in set (0.00 sec)
show global statusで全コネクションの情報を取得できる。
ストレージエンジン
ストレージエンジンは「テーブルごと」に変えられる。
異なるストレージエンジン間でもテーブルをJOINできる。
MyISAM
参照性能が高い。
トランザクションをサポートしていない。
ロールフォワードもロールバックリカバリもできない。
5.1までのデフォルトストレージエンジンだった。
バッチ処理などに使われる。
テーブルロックする。
データファイルの大きさが、生データとあまり変わらない。
concurrent insertができる。
全文検索機能があるが、日本語に対応していない。
Rツリーを使った空間情報インデックスがある。
InnoDBのチューニング
innodb_buffer_pool_sizeを変える。
メモリの70、80%を設定する。
メモリにキャッシュさせる。
ダイレクトIOで書き込む設定も試すとよい。
キャッシュが大きいほど、ディスクIOが減り、パフォーマンスが向上する。
コネクションプーリングができるなら、コネクションの設定はしなくてよい。
接続ごとのパラメータは、各パラメータを大きくしない。
多くの接続を生成したときに、膨大な量になるため。
ソートの処理が多い場合、SORT_BUFFER_SIZEを大きくする。
Query Cacheは、使い方を間違えると、マイナスの影響がある。
select文が全く同じっ場合のみ、キャッシュを使う。
複数キーのインデックスは、左からマッチさせていく。
長い値を持つ列は、始めの10文字だけインデックス化させることもできる。
prefix index。
インデックスは16バイト以下の列がよい。
スロークエリログはオンにしておく。
デフォルトはオフになっている。
閾値は10秒がデフォルトである。
log_queries_not_using_indexesは、性能に影響が少ない。
MySQLでは、INを使うとJOINするより速い。
union allは使わない。union、distinctを使う。
長い文字列はcompressする。
