MySQLコンファレンス2008 2日目に参加してきました。

MySQLコンファレンス2008に参加してきました。同時通訳というものをはじめて経験したけど、けっこう理解しにくいものですね。ふつうに英語を聞いていたほうがよかったかも(英語もよく理解できないけど)。ところどころ、頭が混乱してしまってよくわからなかったところがありました。
仕事があったので2日目だけの参加でした。

ぼくが聞いてぼくなりに理解した内容だったり(またはよく理解できていない内容だったり)するので、事実とは異なるところもあるかもしれません。

1.MySQL Performance Tuning 1
ボトルネックとなる箇所をさぐることが大切
スロークエリログ
　・基本
　・十分詳細ではない
　・どこが非効率なのかまではわからない
　・時刻(時間帯による環境の影響は?)、ユーザ(どのアプリ?)を確認
　・実行時間、問題のクエリを確認する
　・mysqldumpslowでスロークエリログの統計をとれる(5.1で若干のバグあり。修正予定)
　5.1からの新機能
　　・テーブルにもログをはける
　　・マイクロ秒単位で記録
　　・long-query-timeをゼロにできる(すべて記録)
Explain
　・select_typeでの DEPENDENT は注意
　・typeでの index はインデックスをすべてチェックしている(limitで回避できるかも)
　extra
　　・using where : インデックスだけでなく他の条件でも
　　・using temporary : 前のステップのrowがtemporaryに入る
　　・using filesort : 取得中のrowをsortしている。実際にファイルを使っているわけではない。
　　・using index : インデックスのみを使用
　mk-visual-expla
　　・このツールでExplainをビジュアル化
　　・www.maatkit.orgを参照
クエリキャッシュ
　・クエリをハッシュ化してキーとしている
　・そのため同じ結果が返るクエリでも一文字異なるだけど別物として扱われる
　・DATE()など、毎回結果の異なる関数などが使われているとキャッシュされない
　・show global statusの com_select(キャッシュミスの数)とQcache_hits(ヒットした数)
　・キャッシュサイズを大きくしすぎると頻繁にパージが走る
　・パージが走っているときは他の動作がすべてとまる(パフォーマンス低下)
　・クエリキャッシュ自体がボトルネックになる可能性も

2.MySQL 5.1で押さえておくこと
テーブルのパーティショニング
　・パーティションの最大数は1024
　・アプリ側で意識する必要はない
　・ハッシュパーティショニングはプライマリキーを対象にする(それ以外を使うとパフォーマンスが落ちる)
　・リストパーティショニングはマップされていないものは最後に入る
　・階層化も可能
　・InnoDBのバッファプールが適切に働いている環境ではあまり効果はない
　・desc partitionsで詳細情報
XMLとXpathの採用
　・SQLの中でXPathが使える
　・XML自体はblobとして格納
　・特別な型があるわけではなく、XMLであることを使用者が意識しておく必要がある
MySQL Cluster
　・ディスクベースも可能に
　・クラスタまるごとレプリケーション可(別のデータセンタとか)
行ベースのレプリケーション
　・ステートメントベースと比較して、insertのコストは一緒
　・updateで対象行が増えるほど行ベースはコストが高くなる
　・行が多いと予測されるときはステートメントベースに設定すればいい
　・InnoDBのパフォーマンスがあがる(ロックが走らない)
　・クエリの対象行が少なければ採用したほうがよい
Eventの採用
　・タスクスケジュール
alter table と add(drop) indexの高速化
興味があったらdev.mysql.comへ

3.@Niftyブログサービス「ココログ」PostgreSQLからMySQLへのマイグレーション事例
概要
　・six apartが開発
　・月間PVは数億
　・ユーザ数はもうすぐ80万
　・ユーザは無料会員のほうが多いが、実際に記事を書いているのは有料会員のほうが多い
　・mysql5.0 + InnoDB
　・memcached
　・The Schwartzでジョブ管理
PostgreSql時代
　・MySQL採用前はずっと1DB
　・DBが各アプリと密結合
　・DBリソースのコントロールが難しくなってきた
　・memcached、API経由とすることで結合度を下げた
　・データ100GB以上(うち40%はインデックス)
　・VACUUM処理が必要
　・文字コードによって不良データとして扱われ、insertはできるのにdump-restoreができない)
DB Partitioningへ
　・DBを複数台用意し、意味によってデータを振り分け。
　・移行作業に数ヶ月かかった
MySQLに移行して
　・レプリケーションはバックアップのために使用
　・クラスタはHA cluster使用
　・ディスクはFiberChannelでSANに
　・order by なしの結果がPostgreSqlと違う
今後
　・コメント、トラックバックなどはジョブにより反映するように

4.MySQL Performance Tuning 2
ベンチマーク
　・計測時はログをすべて止める
　・クエリキャッシュもオフに
　・メモリバッファは十分大きくする
MyIsam、MyIsam with INSERT DELAYED、InnoDB、Archiveのinsertの性能をテスト
　・MyIsamは64connection以上で低下
　・INSERT DELAYEDは64から横ばいに
　・InnoDBも64以上で低下
　・Archiveは256connectionでも上昇
　チューニングポイントを探る
　　CPUの状態を確認
　　　・MyIsamではやはり64connection以上で下がっている
　　　・他のEngineはOK
　　　・理由はMyIsamのテーブルレベルのロック
　　　・ArchiveはいつでもCPU性能をフルに引き出す
　　ディスク使用率を確認
　　　・InnoDBは利用率が高く、64connectionでピーク
　　　・他Engineは少ない
　　　・InnoDBはトランザクション処理があるため
時刻を記録する処理の性能をテスト
　・timestampカラムの使用が一番はやい
　・triggerを使用する方法がもっとも遅い
Index Mergeの性能をテスト
　OR条件のクエリ
　　・Index Mergeとそれぞれの条件のクエリのunionで比較
　　・クエリのunionよりもIndex Mergeのほうがはやい
　AND条件
　　・Index Mergeとマルチカラムインデックスの比較
　　・マルチカラムインデックスのほうがはやい
　　・柔軟性はIndex Mergeのほうがある
自己結合なクエリ
　・where句でサブクエリ　→　かなり遅い
　・サブクエリの結果とjoin　→　はやくなる
　・viewを使う　→　joinの場合とかわりなし
　・実テーブルを作ってjoin →　もっとはやい
Explainよりもくわしくクエリを確認
　・新しいコネクションでクエリを実行後に show session status like ‘hand%’;

5.MySQLデータベースレプリケーションを理解する
一般的なレプリケーションの動きの説明
MySQL Cluster
　・shared nothingなど
　・メモリ上で動く(5.1からはディスクベース)
　さまざなクラスタ構成の説明

6.インデックスを使いこなす
・資料公開予定
・ディスクI/Oを意識することが大切(CPUやメモリに比べて遅いため)
・SSDがディスクの10倍程度の速度があるので今後に期待
インデックスの処理
　・ルート->ブランチ->リーフ->データ
　・ブロック単位で読み込まれる
　・ルート、ブランチは必ずキャッシュされると考える
　・実際にI/Oが発生するのはリーフとデータファイルの読み込み
InnoDBのインデックスは
　・主キーのインデックステーブルはキーとすべてのカラム値をもっている
　・セカンダリキーは主キーへの参照をもっている
　・MyIsamと比較してインデックスまわりの処理はInnoDBのほうがはやいことが多い
　・できるだけ主キーのみで、しかも小さいサイズのものにする
範囲選択
　・リーフから複数のエントリをとってデータファイルを読む
　・ディスクI/Oはブロック単位なので、近接しているデータなら一度のreadでOK
　・データファイルは順不同なのでエントリの数だけreadが必要
　・通常、readの数は1+Nになる
　・大量のデータを取得するときはデータファイルのI/Oが大きくなりすぎるので、フルテーブルスキャンになる
　　・インデックスを使用するよりもはやい
　　・データファイルをブロック単位で読み込むのでI/Oが減るから
　　・先読み機能があればもっとI/Oは少なくなる
　　・10%～15%以上のデータを取得するときはフルテーブルスキャンのほうがはやい
Covering Index
　・インデックステーブルだけを使用するクエリ(Explain で Using Indexと表示)
　・リーフだけ読めばいいので相当はやくなる。I/Oが一回ですむことも多い
　・発生条件は、select句とwhere句がすべてインデックス(select * はだめ)
　・マルチカラムインデックスをねらう
　・Explainで type が rangeやindexのときは、Covering Indexをねらうのがチューニングのポイント
マルチカラムインデックスは
　・それぞれのキーがAND条件のとき使われる
　・I/Oは二回ですむ
　・Coverring Indexにするために不要なカラムもあえてインデックスにする
Index Merge
　・それぞれのリーフの検索結果をマージしている
　・I/Oは三回以上。
　・マージのオーパヘッドもあり
　・AND検索ならマルチカラムインデックスがいい
　・でもIndexMergeはORでも使える
where order by
　・テーブルの状態とどこのキーが使われるかの組み合わせで結果が大きく変わる
　・MySQLで最適なものが選択できる保証がない
　・FORCE INDEXやIGNORE INDEXを使って最適化する
インデックスは昇順になるように
　・キーが昇順になるようにinsertすればリーフの断片化が起こりにくい
　・リーフの断片化が起こるとキャッシュされにくくなったり、I/Oが増える結果に
　　・selectの性能にも影響を及ぼす
　・キーはできうるならばauto incrementがよい
　　・InnoDBはロックが発生するため負荷が高くなると性能が劣化していた(MySQL5.1で解決)
　・緩衝サーバを用意してindexなしでそこにinsertし、昇順にした上で正規のサーバにinsertする方法もある
　・Q4Mなど