MySQL ユーザコンファレンス 2008 2日目のメモ

MySQL ユーザコンファレンス2008

午後のセッション中心に聴講しました

新ストレージエンジンSpiderの紹介

  • これまでの高負荷に対応する更新系DBの分割は現実的にはアプリ側でのパーティショニングしかない
  • Spiderストレージエンジンはこれまでより簡単な更新系DBサーバの分割方式とさらに足りないものをまとめあげた
  • リモートDBに対してテーブルリンクを作成
  • 更新系DBのクラスタリングができる
  • http://spiderformysql.com/
  • speekerの方が体だけスパイダーマンっぽい格好
特徴
  • 高いスケーラビリティ
  • 連携が高速
  • Data更新の同期を担保
  • 既存システムへの導入に　クライアントアプリケーション修正が少ない
できること
アプリケーションパーティショニングの問題点
  • 複数代のマスターDB
  • 大量のDataと書きこみ要求分散
  • アプリケーションでDBにアクセス
  • 問題点
    • 異なるDBに存在するTableはJOINできない
    • 異なるDBに存在するTableの更新はアプリケーション側で担保
    • アプリエンジニアに高いDBスキル
    • DBひとつでスモールスタートからのスケールアップが大変

Spider使うと

    • 複数代の更新DBをあたかも一台のように
    • 書き込みを分散
  • DB異なっていてもJOIN OK
  • 複数のDB更新はDBで担保
  • 導入時アプリケーションの修正が少ない
    • パーティショニングを考慮していないアプリもOK
  • Proxyの様に動作する?
    • App - DB(Spider) x 1- DB(MyISAM) x 10

何ができる?

  • スレーブトリガ
    • 行レベルレプリケーションではますたーのトリガの実行結果をreplication
    • スレーブ側のトリガを実行しない
  • SpiderだったらスレーブトリガもOK
master

　|

slave(Spider) -> trigger db
  • 2ダブルタイムスタンプ
  • MySQL使用では一テーブルに着き１カラムのupdated_at
    • SpiderはSpiderで

TBL_A(Spider)　　　TBL_A(InnoDB)
cola(timestamp) -> cola(datetime)
colb(datetime)　　 colb(timestamp)
これでcola,b両方timestamp!!

  • 多段パーティショニング
    • 5.1 Table partitioning では不可能だけど　Spiderではいけるよ
  • MySQL ClusterにRangeパーティショニング
    • LINEAR KEYパーティショニングしかできないが
    • SpiderではRangeでパーティショニングができる
  • パラレルレプリケーション
    • MySQL Replcationは使用上はシリアル
    • SpiderではパラレルOK

[DB(Spider)　　　　　　　　 ]
　 |　　　　|　　　|　　　|
[master][master][master][master]
　 |　　　|　　　|　　　|　　　
[slave ][slave][slave][slave]
　 |　　　　|　　　|　　　|
[DB(Spider)　　　　　　　　 ]
DBx1 - DBx複数 - DBx複数-DBx1 が可能

  • 同期レプリケーション
    • tablA(InnoDB) -> trigger -> TableB(Spider) -> Spider -> TableA(InnoDB)
  • InnoDBでInsert delayed
  • クエリキャッシュの有効利用
    • SQLのクエリに対して(文字列)にたいして　キャッシュ
    • 複雑だとうまくキャッシュが聞かない
  • Spiderはクエリキャッシュないよ
    • Table毎にパーティショニングされているので複雑なSQLが単純化されてクエリにヒットしやすくなるよ
      • SELECT * FROM a b c -> SELECT * FROM a , SELECT * FROM b , SELECT * FROM c

インデクスを使い倒す

  • 松信さん
  • 資料はあとで　公開されます

検索処理とインデックス

  • B+Treeインデックス構造
  • インデクス検索と振るスキャン
Insertすると何が起こる？
  • 昇順InsertとランダムInsert
Disk I/O 性能を意識する
  • CPU < 10ns
  • memory < 60ns
  • HDD < 5ms
    • seek 回転
    • ここをいかに高速にするか！！
    • HDD+ライトキャッシュ＋バッテリリカバリ
  • SDD < 0.5ms
B+Tree インデクス
  • インデクスの内部ではインデクス列に対して昇順
  • インデクスのたどり方の基本は　root -> branch -> leaf -> data
  • keyに対応するRowIDからDatafile上の場所を一意に特定できる
  • I/Oの単位はブロック(InnoDBの場合は1ブロックあたり16KB)単位で読まれる
  • 1ブロックは数十のエントリ
  • 読み取られたブロックはmemoryにキャッシュ
    • root,branchは間違いなくキャッシュ
    • leaf,dataはキャッシュされない
    • leaf,data それぞれ1回づつ合計2回のHDDアクセス
InnoDB
  • primary key
pk Data
0 xxxxxxxxxxxxxxxxxxx
1 yyyyyyyyyyyyyyyyyyy
2 zzzzzzzzzzzzzzzzzzz

　　.
　　.
　　.

  • non primary key
key1 pk
0 1
3 2
2 0

　　.
　　.
　　.

  • MyISAM >> InnoDBといわれるがプライマリーキー検索だとInnoDBも速いよ
  • それ以外はnon primary key -> primary key の二段階になるので遅い
  • 可能な限りプライマリーキー検索すべき
  • プライマリーキーは小さく
B+Tree インデクス(Range検索)
  • branch -> leaf -> data disk I/Oがどれくらい発生するか
  • leaf はブロック単位で読むので隣接するDataはI/Oが少なくなる
  • data file は行番号がばらばらなのでI/Oは多い
  • leaf 1 data file 3,4 のDisk I/O
インデクスが速くならない例
  • SELECT * FROM tbl WHERE key < 1000000
  • data file がランダムアクセスされる
FillTable scan
  • ランダムアクセスが激しい場合Optimizerが勝手に選ぶ
  • 上から諄々に読む
    • SELECT * FROM tbl WHERE key < 1000000でランダムアクセスするよりまし
    • ブロックには複数のDataがはいってるのでDataよりブロックの方がｇすくない
    • ブロック複数読み込み昨日があるので　さらに効率的
  • SELECT * FROM tbl WHERE key < 1000000
  • data file がランダムアクセスされる
Covering インデクス(インデクスだけ読む)
  • SELECT key1 FROM tbl WHERE key1 IN (1,2,3)
    • data fileにアクセスする必要ない
    • indexだけよめばよい
    • SELECT * FROM tblより インデクスに入っているキーだけ読むのは速い
      • SELECT * FROM tblが悪いと言われるのはこういうことから！！！！
    • 範囲が広範囲でも速いよ
Covering インデクスされてるか知る方法
  • explainでUsing indexというのが出てきたらCovering インデクス
  • typeがrangeやindexの場合Using indexを積極活用すべき
Multi column
  • SELECT * FROM tbl WHERE keypart1=2 AND keypart = 3
    • 読み取るleafブロック数は１個ですむ
index marge
  • SELECT * FROM tbl WHERE key1 = 2 AND key2 =3
    • key1,2で別index
    • key1で検索
    • key2で検索
    • テーブルを両方見比べるのでI/O増加！！
    • multi column indexの方がぜんぜん速いよ
    • とある事例では10倍以上高速になった
Multi column 使えない場合
  • SELECT * FROM tbl WHERE keypart2 = 3
  • SELECT * FROM tbl WHERE keypart1 = 1 or keypart2=3
  • OR検索とかindex margeの方がいい
Sort Indenndex
  • インデックスはすでにソートされているため、インデックス対象列がソートされていると速い
Sort Indenndex(2)
  • SELECT * FROM tbl WHERE key1 < 30 ORDER by col2
    • order by の列がindexじゃないと結果を並べ替える
      • 遅い
    • Explain Using filesortソートindex使われてないよ
  • ソートレコードが多いほどだめ
Sort Indenndex(3)
  • SELECT * FROM tbl WHERE key1 < 30 ORDER by col2
  • ２つの別々のインデックスがあっても使われるのはどちらか
  • 例
ORDER BY LIMIT N の落とし穴
  • SELECT * FROM tbl　WHERE cond < 1000 ORDER BY keyX LIIMT 20
  • A:condがindexとして使われ、条件をみたしたレコードをソートし上位20件を取得
    • type=range,key=cond,Usingfilesort
    • このパターンが最適な場合：cond < 10000がほとんどない場合
  • B:condがindexとして使われ、条件をみたしたレコードをソートし上位20件を取得
    • type=index,key=keyX
    • このパターンが最適な場合：cond < 10000が大量にある
  • C:condにindexがなくfull Table scan のち20件を取得
    • type=ALL,key=NULL,Usingfilesort
    • このパターンが最適な場合：cond のindexが使えずBでもない

適切な実行計画が得られない場合 FORCE INDEX,IGNORE INDEXで調整してみるべし

  • 全部のインデクスなめるよりfull table scanの方がまし
  • DBT1というベンチマークで適切な実行計画が選択されなかった
INSERT するとなにがおこる？
  • leaf がfull -> leaf がnewされて突っ込まれる
昇順INSERT
  • 虫食いになりにくい
  • 1ブロックが効率的に使われる
  • サイズが小さい、キャッシュされやすい
ランダムINSERT
  • 虫食いになりやすい
  • 1ブロックが非効率
    • 1ブロックあたりのエントリ数が少ない
  • ブロック数がおおくなる
  • サイズが増える、キャッシュされにくい
昇順INSERT vs ランダムINSERT
  • 1000万件のレコードが入っている状態で100万件入れていく
index数1個 昇順 ランダム
100万件insert 37.88 56.72
index size 161MB 335MB
index数3個 昇順 ランダム
100万件insert 52.26 184
index size 483MB 1G
  • 簡単に断片化がおきる
InnoDBだとAUTO_INCRIMENTが黄金解？
  • 5.0までは一瞬だがTable lockをかけるので同時接続数が多いとパフォーマンスが劇的に落ちる
  • 5.1はこの問題を解決して軽減された
昇順InnoDBのためのアーキテクチャ
  • App -> 緩衝材 -> DB
  • Appの更新を一旦緩衝材に保存して緩衝材からまとめて本体DBを更新
緩衝材:インデックスが無いテーブル
  • すぐinsertできる
  • insertしたらまとめてSELECT -> 本体DBで
緩衝材:memcached
  • memcacheにいったんいれる
  • insertしたらまとめて検索 -> 本体DBで
  • memcachedは本体DBに保存されるまでに消えてしまわないように注意！
緩衝材:Q4M
blackhole strage engine
  • 更新時はbinlogに書かれるだけ
    • 非常に更新が軽い
  • slaveで本体に書かれる
  • Insertの順番がばらばらになるとslaveで実行するとき昇順indexにならない？
BadPractice
  • indexの数が多すぎると更新性能が落ちる
    • ご利用は計画的に
  • index自体が大きすぎる(URL等の長い文字列をindexにする)
    • スペースを消費してパフォーマンス悪化
    • 結局昇順にならないので意味ない?
    • Prefix indexとかつかうべき
  • Data型の不正比較
    • varchar とintとか
    • index使われないので意味無い
  • multi column index で一列目を指定しない
  • memoryストレージエンジン
    • hash indexなので範囲検索できない！！！
    • MyISAMにしたほうがいいｙ
    • この場合using btreeすると解決
  • 負荷試験でのData準備の欠如
    • test data が10ユーザしかいない
      • Data量が少ないとキャッシュがきいてるんで性能がよく見える
    • 全ユーザが同じ商品を選択
      • Dataに偏りがあるとキャッシュがきいてるんで性能がよく見える
    • 本番のアクセスパターンとまったく違うテストは付加テストの意味なし
まとめ
  • ランダム アクセスを最小化
  • explainでUsing indexが出るようにする
  • data型,Dataサイズを守る
  • 昇順INSERT

フルマネジメントホスティングシステム

株式会社アルティネット会社概要
  • 設立 1999
  • 規模 28名

MySQL EnterpriseUnlimitedとは

  • MySQL Gold相当の製品、サービスが無制限に利用可能

MySQL EnterpriseUnlimitedなぜ導入

  • コスト
    • Basic $599/1year
    • Gold　$2999/1year
    • 翌年度のライセンス数が予測しづらい
    • Communityを利用していた顧客に30万円/serverのコスト増を求めにくい
  • 技術
    • サポートなしなので自力で解決しないといけない
      • 人材不足
      • ノウハウの一極集中
  • 管理
    • CommunityとEnterpriseの混在
    • ライセンスの管理
    • 保守
    • Community版はサポートが受けられない
    • 運用、監視に一定のルーを適用できない
    • いつも試行錯誤

でMySQL EnterpriseUnlimitedで解決しましたか？

  • サーバ増加がコスト増にならない
    • 翌年のコスト計画が立てやすい
    • 1顧客あたりの費用負担が少なくなった
  • 管理
    • CommunityからEnterpriseの統一
  • 保守
    • 全案件がSunの手厚いサポートを受けられるようになった
    • 監視をNetworkチームに一本化できた
  • 技術
    • NetworkチームエンジニアがDBAとしてのスキルを飛躍的に向上させた
    • MySQLに対する習熟意欲が向上
    • Networkチームから開発陣へのフィードバックが的確

MySQL EnterpriseUnlimited導入での困難点

  • 英語の契約書を翻訳して方務に渡す
  • 技術的には..
    • rpm -Uvh MySQL-*
    • ./mysqlserviceagent-installer.bin
    • これだけ

まとめ

  • 全顧客にEnterpriseerver
  • コスト減
  • Sunのサポートを受けられる

導入事例

  • 従前
  • 従後
    • ヒートチャート(サーバ的にピンチの点)
      • 日々上記の赤丸を消していけば運用はOK
    • アドバイザリにしたがっていけば基本ＯＫ

問題解決サポート

  • MySQL Enterprise Monitorトラブルシューティング
    • 月曜15:42(連絡)->月曜17:40(Sunの人が再現性確認)->火曜日12:40(解決パッチが送られる)
    • とにかくスピーディーに進むよ