MySQLユーザコンファレンス2008 2日目(10/31)メモ

mysql mysqlcon2008

1日目のメモはこちらです。-> MySQLユーザコンファレンス2008 1日目(10/30)メモ

MySQL Performance Tuning 1 (10:00 ~ 10:50)

queryのスループットについて
slow query logを調査
  • インデックスを活用する
  • クエリを書き直す
  • オプティマイザの問題
applicationの問題
  • app-db間のラウンドトリップ
  • 1回のSQLで出来るものを、複数回query投げていると遅くなる
スロークエリログについて
  • 名前の通り遅いクエリをロギングする
  • mysqlに組み込まれている機能
  • スロークエリ = 最適化の余地があるクエリ
  • でもスロークエリログだけでは問題を解決できない
    • そのクエリのどの部分が遅いのかはわからない
    • 表示されたくないメタデータも表示されてしまう
mysql 5.1で拡張される機能
  • fileだけでなくて、tableにもロギングできる
  • 稼動中にロギングのon/offの変更が出来る
  • 単位がマイクロセカンドになる (以前は秒単位)
  • しきい値(long-query-time)を0秒にして、全クエリログを取ることもできる
mysqldumpslow
  • スロークエリログの統計を取れるスクリプト
    • mysqlに付属
    • 同じクエリのグループ化
  • mysql 5.1のものにはbugがある => 次のバージョンで修正
EXPLAIN文
  • クエリのどこが悪いかを調べるために使う
  • 使い方 - クエリの前にEXPLAINをつけるだけ
  • select_type
    • simple: selectだけのクエリ
    • subquery: UNION、JOINとかを使うクエリ
  • type
    • single_row
    • system: テーブルにひとつの行だけのとき
    • const
    • eq_ref
    • ref
    • range
    • index: Full index scan
    • all: Full table scan
  • extra
    • Using where
    • Using temporary
    • Using filesort
    • Using index
    • Using index for group-by
mk-virual-explain
  • EXPLAINの結果がツリー状に表示される
  • http://www.maatkit.org
    • Part of the Maatkit toolkit
  • このツールはフォローするのが難しいねーって言ってた
  • 午後のセッションでさらに掘り下げるよ
query cache
  • クエリと、その結果をキャッシュする
    • 同じクエリが投げられたら、キャッシュしてる結果をクライアントに返す
  • ハッシュ構造
    • クエリがキー、結果が値
  • 結果が同じ(ex:whereの条件の論理的な意味が同じ)でも、クエリが違えばクエリキャッシュは有効にならない
    • sql1: where a = 5 OR a = 10
    • sql2: where a IN(5,10)
  • 実行するたびに結果が変わる関数もキャッシュされない
    • DATEとか
Configuration Options
  • query-cache-type
    • NO_SQL_CACHEと明示的に指定したらキャッシュしない
  • query-cache-size
  • query-cache-limit
show global status
  • Com_select
    • キャッシュにhitしなかったクエリ数
  • Qcache_hits
    • キャッシュにhitしたクエリ数
  • 他にも色々パラメータがある
    • show global status like 'Qcache_%';
query cacheには注意も必要
  • 非常に大きいサイズにしてしまうと、その分キャッシュのパージに時間がかかる
    • パージ中はキャッシュできないので、実質止まるようになる？(と言ってた気がする。でも止まるの意味がよくわからない)
    • キャッシュサイズ 12GByteとかやめてね！ Hahahaha
  • 負荷の高いサーバの場合、クエリキャッシュをdisableにしたほうが逆に性能あがるかも
    • クエリがキャッシュにhitするか都度チェックするため？？

MySQL で押さえておくこと (11:00 ~ 11:50)

Table/Index Partitioning
  • テーブルを別々の領域に分ける
  • I/O負荷が下がる
  • Partitioningの種類
    • range,list,hash,keyなど
  • 特定の条件で振り分ける
  • Partitioningの例
    • mysql> desc partitions;
    • 800万行のデータに対して、パーティションの有無でのselect結果の比較
    • 38.30sec => 3.88sec 90%の性能改善
Full Text/Plug-in Enhancements
  • ParserをPlug-inで追加できる。
    • SHOW PLUGIN
    • UNINSTALL PLUGIN
    • INSTALL PLUGIN
XML Xpath Support
Archive Engine Enhancements
  • Faster I/O operations
  • Lower Memory
  • Autoincrement column support
    • Unique key support
    • Non-unique key support
  • Archive Reader tool
MySQL Cluster Disk-Based Data
  • 今までのクラスタはメモリベースだった
    • (データもインデックスもメモリに置く必要があった？)
  • 5.1ではこれを拡張して、データはディスクに書けるになった。
    • メモリに置くのはインデックスだけ
Row-Based Replication
  • 行ベースレプリケーションに対応
  • 今までのステートメントベースのバイナリログによるレプリケーションでも90％は大丈夫
    • でも、Non-Deterministicなクエリを使うと駄目
  • Insertの場合はステートメントベースでも、行ベースでも速度はあまり変わらない
  • Updateの場合は行ベースの場合、コストが高くなる
    • 全部 or 大量の行をupdateする場合は駄目？
    • primary insertで少しの行だけupdateする場合はよい
  • InnoDBだと行ベースレプリケーションで性能がよくなる
    • とあるロック処理がないから、らしい
Easier Manageability
  • Event
  • タスクプロシージャ(ストアドプロシージャ)
  • 特定の時間にOPTIMIZEを実行とか出来る
    • linuxでいうcronが、MySQL内で出来るということ
  • ストアドプロシージャがうまく書けなかったらクビになるかもしれない。けど、皆さんなら大丈夫ですよね Hahahaha
High Performance
  • Faster Alter Table/Faster Add-Drop Index
その他

MySQL Performance Tuning 2 (14:00 ~ 14:50)

大盛況。人数多すぎて制限かかった？ 時系列とは異なりますが、午前のセッション繋がりなので、こちらを先に書きます。後半は私の脳みそのスタミナ不足により内容に追いつけませんでした。

benchmark環境
  • ロギングは全て止める
  • クエリキャッシュもoff
  • 十分に大きなメモリ
  • ベンチマークツールはsuper-smack
  • 同時connectionを1から256まで増やしてテスト
評価対象のENGINE
結果(指標はqps?)
  • MyISAM: connection 16以降でスケールしない
  • MyISAM with insert delayed: connection 64以降でスケールしない
  • InnoDB: connection 64以降でスケールしない
  • Archive: connection 256までスケールする
CPU利用率
  • MyISAM: connection 64以降でCPU利用率が落ちた
    • 接続数増加によるテーブルロックが原因
  • Archive: CPUをうまく利用する
Disk利用率
  • InnoDB: 利用率は常に60％以上
  • 他のENGINE: 30％程度
まとめ
  • Archiveは性能は良いが、用途が限られる
    • update,deleteできない
    • 検索用のINDEXが使えない
TIME-ENTRY
  • timestampが一番良い
  • TRIGGERが一番悪い
INDEX Merge Test
InnoDB INDEX Merge
status
  • SHOW GLOBAL STATUS LIKE 'handl%';
  • SHOW SESSION STATUS LIKE 'handl%';
  • SHOW ENGINE INNODB STATUS\G

@Nifty ココログ PostgreSQLからMySQLへのマイグレーション考察 (13:00 ~ 13:50)

cocologとは
  • Niftyが運営 / 富士通グループ
  • おそらく日本の大手ISPで最初のblogサービス
    • TypePad, Movable Type by SixApart
    • 数億PV/月 (日本では10番以内ぐらい？ 中〜大規模)
  • ユーザ数 2008/04時点で70万人、もうすぐ80万人
  • UI
    • TypePadとほぼ同じ
    • TypePadのバージョンがあがればcocologもバージョンあがる
ユーザ数、記事数
  • ユーザ数は無料会員のほうが多い
  • が、投稿記事数は接続会員(ISPユーザの有料会員)のほうが多い
現在のシステム構成
cocologの歴史
  • Phase1
    • DB: PostgreSQLのサーバ１つ
    • 10サーバ
    • TypePadで静的コンテンツをはいて、Webサーバにうｐ
  • Phase2
    • 50サーバ
    • デザイン、テンプレートなどでサーバが増えていく
    • appとDBとが密結合
    • Typepadのバージョンupにあわせて、スキーマ変更、SQL変更が必要になる
  • Phase3
    • memcached採用
    • 携帯に対応
    • 200サーバ
    • このころからPostgreSQLは腫れ物のように
    • 統計情報を取得することすらガクブル
  • Now
    • 150サーバ
    • MySQLに移行
DBP: Database Partitioning
  • 全情報を1サーバにもっていたものを、ユーザごとに振り分け
    • ユーザごとの共通DB、ユーザごとのDBなどに
  • TheSchwartzにjobを投げて、1ユーザずつPostgreSQLからMySQLに移行
Postgresql => MySQLで困ったことなど、苦労話
  • Postgresql時代
    • DBサーバ。上位スペックの機種に買い換えて耐えていた
    • 最終形態: Redhat AS4, 3.3GHz 2Core * 4, mem 16G
  • Postgresql 7.4 => 8.1
    • Data size 100GB
    • 巨大なインデックス (40%)
    • 7.4 => 8.1にして早くなった
  • 文字コードで困った(UNICODE圏のSPAM)
    • 8.1のrestore時にはじかれた