tsucchi’s diary(元はてなダイアリー)

はてなダイアリー(d.hatena.ne.jp/tsucchi1022)から移行したものです

MySQL ユーザコンファレンスに行ってきました (2日目:書いたのは 11/1です)

MySQL ユーザコンファレンスに行ってきました。自分が受講したセッションについてざっくりまとめます。

※あくまでも私の聞いた(つもりの)内容と私が書いたメモが元になっておりますので、実際のセッションや発言とは異なる可能性があります。

E-5 MySQL Performance Tuning 1

  • すげー人気だった。
Slow Query Log
  • 遅いクエリをログに書く機能。または書かれたログのこと。
    • SQL や実行回数所要時間などを表示する
  • クエリの詳細は見えない
  • システムテーブルもログに書いてしまうのが欠点
  • 5.1 ではさらに機能拡張された
    • ファイルだけでなく、DBのテーブルにも書ける
    • ミリ秒単位での測定(従来は秒単位)
    • ON/OFF
    • 動的な設定変更
  • mysqldumpslow
    • Slow Query Log を集計する
EXPLAIN
  • オプティマイザによる実行計画が見える
  • Extra : このパラメータが重要
    • using where : インデックスが使えていない
    • using tempolary : テンポラリテーブルが使われている
    • using filesort : ソートが行われている
    • using index : インデックスが使われている
  • http://maatkit.org/
    • Explain のログをツリー系式で表示
Query Cache
  • 同じクエリが来た場合(でキャッシュされている場合)はキャッシュから結果を返す
    • 実行計画の作成や実行をしなくて済むので速くなる
  • 同じ内容で書き方が異なるクエリは判別できない
  • insert/update が入るとキャッシュはクリアされる
  • セッションや日時などはキャッシュに入らない
  • サーバ側でオン/オフの切替えが可能
  • クエリ単位でも可能。(サーバがオンのときにオフにしたりできる)
  • 下記のパラメータが重要(だと思うんだけど、このパラメータどうやって出すんだろ??orz)
    • Con_select : キャッシュミス
    • Q_cache_hits : キャッシュヒット
  • 注意点
    • キャッシュが大きすぎると、クエリの解析に時間がかかってかえって遅くなる
      • 数GB のキャッシュとかダメ
    • サーバによっては off にした方が良いこともある
    • フラグメンテーションが起こり得る
      • freeblock が多くなったときは注意
所感

パフォーマンス関連の話しは今まであまり無かったので大変興味深く聞くことができた。

E-6 MySQL 5.1で押さえておくこと

パーティショニング
  • テーブルを複数のテーブルに分ける機能
    • 分けられたテーブルは別ファイルになるのでパフォーマンスの向上が期待できる
  • MyISAM のみサポートされていた
  • Range, Hash, Key, List などの種類がある
  • パーティションのサブパーティションも作れる
  • あまり使われないデータに対する select が速くなる
    • IO の負荷が下るから
    • 良く使うデータはキャッシュに乗るので、あまり速くならない
Full Text/Plugin
  • 独自のパーサを作れる
    • 日本語対応できるとか言っていた。ちょっと意味が分からなかった
  • 動的なプラグイン追加が可能
XML/XPath サポート
  • XML 自体は LOB として登録する
  • XPath 式を書くための extractValue() という関数が追加された
アーカイブエンジンの拡張
  • アーカイブリーダー
    • オンラインバックアップに使えるらしい
Cluster
  • ディスクデータのサポート
    • 今まではメモリに乗るデータだけだった
    • メモリはインデックスの分だけあれば良い
  • レプリケーションが可能に
    • Geographic Cluster ができるようになった
行ベースのレプリケーション
  • 以前はクエリベースのレプリケーション(バイナリログ)
  • 実際の行の値でのレプリケーションも可能に
  • insert はあまり変わらない
  • update は遅くなる
  • (オレ註: じゃあどんなメリットがあるんだっけ?)
Task Scheduler
  • Unix の cron や Windows のタスクスケジューラのような機能
  • ストアドプロシージャを指定したタイミングで流せる
DDL の改良
  • 速くなる
データのインポートの高速化
  • 並列ロードする
Process List, Slow Query Log の改良
  • テーブルにもログを登録できるようになった
パフォーマンステスト/負荷テストユーティリティー
  • mysqlslap
所感

マイナーバージョンアップの割には大きな変更も沢山あると思った。

E-7 MySQL Cluster Carrier Grade Edition 入門

  • 通称 CGE
  • テレコム企業の加入者 DB などで使われているらしい
Cluster のエディション
  • Community(GPL)
  • SE(商用)
  • CGE(商用)
SE とCGE の違い
レプリケーションとの違い
Cluster の要素
  • SQL Node : SQL の実行
  • NDB API : C/C++ API
  • Data Node : データを保持
  • Management Node : 管理
チェックポイント
  • クラッシュでデータを失わないために定期的にディスクに書いている
    • 2 つ以上のノードに同じデータを持たせる
  • Local Checkpoint と Global Checkpoint がある
Cluster のサイジング
  • データサイズ × レプリカ数 × 1.1 = Total Memory
  • Total Memory / ノード数 = 1ノードあたりのメモリ
    • 例) 2GB × 2レプリカ × 1.1 = 4.4GB
    • 例) 4.4GB / 4 = 1.1GB (1ノードあたりに必要なメモリ)
Cluster を使うかどうか決める前に考慮すること
  • Windows 版はない
  • メモリは十分か
  • JOIN が多いか?
    • JOIN が遅いため
    • バージョン6.3で改善予定
  • 外部キーは無いか?
    • 使えないため
    • 6.4 で対応予定
  • Full-Text-Search は?
    • サポートしないため
Distribution Awareness
  • NDB を使うときは、どのノードにデータがあるか事前に分かっていると速い
    • Cluster がそれを面倒見る機能のことを言っていたかも。ちゃんと聞き取れなかった
所感

クラスタ自体の説明が比較的多かったので、その点が良かった。

E-8 MySQL Performance Tuning 2

  • すげー大入り。ぎりぎりで入れました。
  • マイクロベンチマークを通して MySQL の傾向を見る
ベンチマークの前に
  • 性能に影響あるものを切る
    • ロギング
    • クエリキャッシュ
    • メモリバッファを適切なサイズに
#1 Logging Application
  • 各種ストレージエンジンで、ログ書きの比較
  • 結果
    • MyISAM, InnoDBサチる
      • MyISAM は 16コネクションまではスケール
      • InnoDB は 64コネクションまではスケール
    • Insert Delayed は 64コネクションまでスケール
    • Archive はスケール
  • CPU ネック?
    • MyISAM は Insert時のテーブルレベルロックがネックか
    • Insert Delayed の場合も同
    • Archive は目一杯 CPU を使う
  • Disk ネック?
    • InnoDB は 64コネクションでピーク。他と比べても常に利用率が高い
      • commit 時の同期化がネック
    • 他のエンジンでは低い

Archive はパフォーマンスは良かったが、本当にこれでアプリケーションの用件を満たすか検討は必要。

インデックス
  • インデックスマージは Union より速い
  • コンポジットインデックスはインデックスマージより速い
    • でも結構良い勝負
  • コンポジットインデックスはソートより速い
    • すごく差が出た

※もちろんこのことが常に成り立つわけではない

日付
  • 各種日付関数の性能を比較
    • NOW
    • MONTH(Now())
    • MONTH('YYYY-MM-DD')
    • DATE_FORMAT
    • SUBSTRING
  • 結果
    • 意外と差がでなかった
  • BENCHMARK() を使うと差が出る
  • 日付を何度も使うなら、BENCHMARK で速いのを使った方が良い
InnoDB のインデックス
  • データのリロードを短くしたい
  • インデックスを使うと insert/update が遅くなる
  • insert buffer を使う?
  • 下記で性能を比較
    • インデックス無し
    • insert buffer
    • ユニーク index
    • ユニーク index で、ユニークのチェックを外す
  • 結果
    • インデックス無しが速い
    • insert buffer も結構速い
    • ユニーク index は遅い
    • チェックを外すと insert buffer と同じくらい
      • でもチェックを外すのは危険。データのリロード時以外はやめた方が良い
Max(date)をサブクエリに持つような SQL
  • データが多いと遅くなる
  • ベンチマーク環境では 64スレッドだとクエリが実行できないくらい遅くなった
  • いくつかの解決策がある
  • Derivered Table を使う
    • From 句に id と date が入るような一時テーブルを入れる
    • かなり速くなる
    • クエリはちょっと読みにくい
  • Derivered Table と同じ意味の View を作成する
    • パフォーマンスは Derivered Table と同等
    • クエリはかなり読みやすくなる
  • View をテーブルにする
    • View よりさらに速くなる
    • ただしトリガやバッチを用意してテーブルにデータを入れる必要がある
SHOW STATUS

SHOW GLOBAL/SESSION STATUS like 'handler%' とすると、クエリの実行計画が(なんとなく)見える。EXPLAIN よりも詳しい情報が得られる。

  • やり方
  1. . コネクションをクリーンにする
  2. . クエリを投げる
  3. . show session status
所感

やや早足の説明だったこともあって、完全には理解できませんでしたが、ベンチマークを通じてMySQL の内部構造も何となく見えて来て面白かった。

E-9 MySQLデータベースレプリケーションを理解する

レプリケーションとは
クラスタとの違い
クラスタと似ている点
  • Read のスケールアウトが可能
    • 沢山のスレーブを作って、クライアントの Read をスレーブにたいして行うようにする

以降はいろいろなレプリケーション構成の紹介

Read Replication
  • マスタ1
  • スレーブが複数
  • マスタに書き込み
  • スレーブから読み込み
  • 良い点
    • Reader の切替えをしやすい
  • 悪い点
    • 非同期
    • 単一障害点がある
Application Cluster
  • マスタが1つ
  • スレーブも1つ
  • マスタに書き込み。スレーブから読み込み
  • マスタが InnoDB, スレーブが MyISAM
  • トランザクションが確保できる
  • slashdot で使われている
  • バックアップ時はスレーブの IO を止めて、スレーブ側で取るのが良い
Logging
  • マスタ1つ
  • スレーブも1つ
  • マスタに書き込み。スレーブから読み込み
  • マスタはブラックホールエンジン
    • マスタに対する実際の書き込みはログだけ
    • データ領域はスレーブにのみ入る
    • これにより write が速くなる
Writing Cluster
  • マルチマスタ構成
    • マスタは Active-Active も Active-Standby もいける
  • 読み込み用にスレーブを置いても良い
  • 主キーやリレーションの組み方が難しくなる
Split Master
  • 2つのマスタを置く
  • レプリケーションはしない
  • データを共有ディスクに置く(DRDBでも良い)
  • Heartbeat などを使ってフェイルオーバーする
Star Cluster
  • 中央に書き込み用のマスタ
  • 周辺に星型に配置
  • スクリプトを書いて、バイナリログを直接読み書きしないと構成できない
  • 中央への負荷が大きい
  • 中央が落ちても動作する
    • というより、「中央をつけたり外したりしたい」というのが顧客の要望だったらしい
  • 主キーの管理が難しい
Circular
  • 円上に配置
  • 各ノードはマスタでありスレーブ
  • 主キーの管理が大変
  • 結構パフォーマンスは良い
  • バックアップが比較的容易
    • どこかのノードで IO を止めてバックアップすればよいから
    • サービスを止めたくないなら、円をつなぎかえて、外したノードからバックアップを取れば良い
  • 構成をちゃんと分かっていないと管理が難しくなる
Memcached Cluster
所感

「本当にこんな構成を使うのか?」と思えるようなものまでありました。それだけ柔軟なクラスタ構成を作れるということだと思います。

J-10 インデックスを使いこなす

はじめに
  • IO は遅い
    • できるだけ IO が発生しないようにするのがチューニングのポイント
  • インデックスは B+Tree
    • リーフに ROWID が入るようなイメージ
    • エンジンによって多少実装は変わる
    • 一度読んだブロックはメモリに入るので、これをうまく使うのもポイント
InnoDB のインデックス
  • クラスタインデックス
    • 主キーのインデックス
    • キーと残りの列の値を持っている
  • セカンダリインデックス
    • インデックスのキーとそれに対応する主キーを持っている
    • 主キーを見て、クラスタインデックスを引く

主キーを小さくすること、なるべく主キーでアクセスするのがポイント。

Between や IN で発生する IO
  • インデックスが使えれば、リーフの読み込みは一回(あるいは多くても数回)
  • データファイルはマッチする範囲数分
  • マッチする行数が多すぎるとき
    • インデックスの効果よりも、データファイル読み込みのランダムアクセスのオーバーヘッドが勝る
      • Full Table Scan に切替える(MySQL が勝手に)
    • 一般に、テーブル全体の 10〜15% がマッチすると Full Table Scan の方が速いと言われている
Covering Index
  • インデックスだけしか使わない検索
  • キーだけをうまく書くとできる(ことがある。もちろんテーブル構造によると思う)
  • リーフブロックだけ読み、データブロックを読まないので速い
  • EXPLAIN したとき、Extra に「Using Index」と出る
    • もし出ていなくて、type が 「Range」や「Index」なら狙っていきたい所
マルチカラムインデックス
  • 複数のキーからなるインデックス
  • 同じインデックスである必要がある
    • key1 AND key2 みたいなパターンが例
インデックスマージ
  • 別のインデックス同士の AND など
    • key1 AND key2
  • 各キーのマッチしたものの ROWID をマージする
  • 一般的にはマルチカラムインデックスよりは遅い
マルチカラムインデックスが使えない場合
  • key1 AND key2 であるが、key2 のインデックスしか使えない
  • OR 検索

インデックスマージは使える

ソート
  • キーでソートする場合、ソートがいらないので速い
  • キー以外のソートでは結果をソートテーブルに移すのでその分遅い
    • EXPLAIN の Extra に「using filesort」と出る
    • ちなみに、ソートバッファに収まれば実はメモリソートである
  • key1 < 30 order by key2
    • key2 にインデックスがあっても使われるか分からない
    • どちらかのインデックスが使われる(オプティマイザが判断)
ORDER BY LIMIT N
select * from tbl where cond < 1000 order by keyX limit 20

のようなクエリを考える。cond にも keyX にもインデックスがあるとする。

  • A. cond のインデックスを使う
    • type=range, key=cond, using filesort
  • B. keyX のインデックスを使う
    • type=index, key=keyX
  • C. フルテーブルスキャン

どれが選ばれるかは分からない。パフォーマンスは以下のようになる

  • A. cond を満たすレコードが多いと遅い
    • ソートがいるから
  • B. cond を満たすレコードが少ないと遅い
    • 19レコードとかだと最悪。インデックスつきの全データ読み込みになるから
  • C. cond インデックスが使えず Bでない

必要なパフォーマンスが出ない場合は、Force Index や Ignore Index で調整する。

Index がある場合の Insert
  • リーフに入らない場合は新しいリーフを追加する
  • 昇順 Insert とランダム Insert がある
  • 昇順 Insert
    • 末尾にデータが入る
    • 入り切らない場合は新しいリーフの先頭に追加
    • 虫食いになりにくく、効率的
  • ランダム Insert
    • ランダムにデータが入る
    • 入り切らない場合は、末尾への挿入ではないので、ブロックを半分にする
    • 虫食になりやすいので、非効率

マイクロベンチマークを取ったところ、ランダムは時間、空間効率ともに悪かった。

InnoDB の Auto Increment
  • テーブルロックがかかる
  • クライアント数が増えるときつい
  • バージョン 5.1 からは負荷が減るようになった
Buffer Insert
  • インサートの負荷が高いときに使える構成
  • アプリは Buffer DB のインスタンスに書き込み
  • Buffer DB がキーを昇順に入れ換えてから、本体 DB に書き込む
  • Buffer DB のテーブルにはインデックスを張らない

いくつか応用がある

  • Buffer DB を memcached に置き換える
  • Buffer DB を Q4M に置き換える
    • 速くなる
    • 反映はバッチ
    • 検索は本体 DB
      • 検索用に memcached を用意する構成もアリ
  • Buffer DB にブラックホールエンジンを使う
    • 速くなる
    • 昇順インサートができなくなる
インデックスに関する Bad Practice
  • インデックスが多い
    • 全カラムとか
    • 更新が遅くなる
    • インデックスサイズが大きくなる
    • インデックスの利用効率が下がるので、検索も遅くなる
  • インデックスが長い
    • URI や UUID
    • インデックスサイズが大きくなる
    • 先頭数バイトを使うとか、CRC32 で 4バイトハッシュに変換する
      • CRC32 は URI ではとくに有効
  • 型が合わない
    • 例) varchar_column = 1
    • インデックスが使えない
  • マルチカラムインデックスで、1列目が使えない
    • 例) 1列目で範囲検索、2列目以降で '='
  • MEMORY ストレージエンジンで範囲検索
    • MEMORY はインデックスのデフォルトが HASH なので、範囲検索できない
    • B+Tree に変更する
  • テストデータが少ない
    • 10ユーザしかいない
    • 10万ユーザいるが、実際にテストで使うのが 10ユーザ
    • 全ユーザが同じ商品を注文
    • キャッシュに乗ってしまうので、本番稼働時と動きが変わる可能性がある
まとめ
  • ランダムアクセスを減らす
  • EXPLAIN を見る
  • 型とサイズは合わせる
  • できるだけ昇順インサート
所感

大変分かりやすいセッションだった。こちらを先に見ることができたら、パフォーマンスチューニングの話しももっと理解できたと思うので、その点が残念だった。