MySQL ユーザカンファレンス 2008 2nd Day

#[MySQL]MySQL ユーザカンファレンス 2008 2nd Day ##MySQL Performance Tuning #1 ###Introduction

• Through put
  • どこに問題が
  • 輻輳
• Slow queries
  • インデックスの問題
  • 書き直したほうがいいんじゃないか
  • Optimizerがどう動くのか
• Application
  • Roundtrips

##The Slow Query Log

• 遅いクエリーを記録するログ
• 問題
  • どの部分が遅いのかがわからない
  • INFORMATION_SCHEMA が出てくるときがある
• Details
  • Execution TIme
  • 実行された時間にをみる
  • User Account
  • 誰が実行しているのか
  • Query_time
  • 全実行時間
  • Lock_time
  • ロック時間
  • SET timestamp=
  • サーバで実行された timestamp
• 5.1 では
  • テーブルに保存できる
  • 再起動なしに On/Off
  • ミリ秒まで可
  • 全ログを記録可能
• Configurations
  • long-queries-not-using-indexes
  • インデックス使わないクエリー
  • min-examined-ro-limit
  • 最小行数
• mysqldumpslow
  • Slow Query の統計をとることができる
  • ログ中の統計
  • 全実行時間
  • 平均実行時間

###EXPLAIN Basics

• Optimizer のクエリ実行計画
• SELECT の前に EXPLAIN をつける
  • key_len: インデックスのバイト数
  • 表示順で結合されている
• :id カラム
  • id:1, id:2 の結合
• :select_type
  • SIMPLE
  • 通常の JOIN
  • SUBQUERY
  • DPENDENT SUBQUERY : 注意が必要
  • UNION
  • DPENDENT UNION : 注意が必要
  • PRIMARY
• :table
  • どのテーブルか
  • なければ NULL

• :type

  • 取得方法
  • NULL
  • データなし
  • system, const, eq_ref
  • 1行
  • ref,ref_of_null,range
  • 複数行
  • ALL
  • 全行
  • *_subquery

• Single Row EXPLAIN type

  • system
  • 1行のみのテーブル
  • const
  • Optimizer の中で実行される
  • UNIQUE and NOT NULL のカラムが指定されている
  • eq_ref
  • JOIN で 1:1 のとき

• Many-Row EXPLAIN type

  • ref
  • 非ユニークなマッチ
  • range
  • 範囲, BETWEEN, <= >=, in, PATTERN-SEARCH
  • index
  • フルインデックススキャン
  • LIMIT で制限かけられるが，EXPLAIN ではわからない
  • ALL
  • 完全テーブルスキャン

• :possible_keys

  • 使えそうなキー

• :key

  • 実際に使ったキー

• :key_len

  • 複合インデックスの場合のその利用範囲

• :ref

• :rows

  • 1行目はその行数を見る
  • 2行目は，1行目の結果毎に見る
  • 3行目は，2行目の結果毎に(つまり1行目 X 2行目)毎に見る
  • 見てる行数なので，最後の結果行数ではない

• :Extra

  • Using where
  • インデックスされたあとに条件がある場合
  • Using temporary
  • 内部的に作られる一時テーブルを使って
  • JOIN/GROUP BY
  • Using filesort
  • ソートするときに
  • Using index
  • 実際のテーブル行まで見ないで実行する
    • インデックスを見るだけで結果がわかる場合
  • 複合インデックスの場合には有用
  • Using index for group-by
  • DISTINCT 使ったときなどに

• mk-visual-explain

  • http://www.maatkit.org/
  • 表示を見やすくしてくれる

###Query Cache

• クエリーの結果全体を保存する
  • 同じクエリーなら，キャッシュから返すのでかなり効率的
  • ハッシュ形式で，クエリーがキーとして保存されている
• pre-parser が先頭の 3文字が SEL なら保存
• キャッシュできるもの
  • 関数 CURDATE() などはキャッシュされない
• 設定するには
  • query-cache-type
  • ON/OFF
  • DEMAND(SQL_CACHE をつければ)
  • query-cache-size
  • キャッシュ容量
  • query-cache-limit
  • 結果の最大サイズ
• 統計
  • SHOW GLOBAL STATUS;
  • Com_select
    • キャッシュミス
  • Qcache_hits
    • ヒット数
  • Qcache_lowmem_pruned
    • メモリ不足でキャッシュできなかった数
  • Qcache_not_cached
    • 関数仕様などによりキャッシュされなかった
  • Qcache_free_blocks
    • 大きくなると断片化が起こっている
• 注意
  • サイズを大きすぎると，その処理に時間をとられすぎてしまって遅くなる．
  • Parser の前段で見るから
  • ものによっては削除した方がいいかもしれない

##5.1 In-Depth

• 特徴

###データウェアハウス向け

• テーブルパーティショニングがエンジンに関係なく利用可能

  • range//hash/key/list で分割
  • 1024まで分割可能
  • 保存すべきテーブルは自動的に判別してくれる
  • 判別できない場合には最後のテーブルに入る
  • 分割するには分割する計画もしっかりたてなければならない
  • 結果的に負荷の増大につながる
  • 2重にパーティショニングも可能
  • hash
  • I/O 削減にもつながる
    • パーティショニングテーブルを別ディスクに入れることで I/O を削減
  • key
  • 地域ごとに分けるなど
  • Partitioning Metadata
  • desc patitinings;
  • 分割する範囲と内容がしっかり精査されていれば，I/O(Random Access) の削減につながるので高速化される．

• Full Text/Plug-in

  • テキストパーサーを本体から取り除いて，プラグインを作成できるようになった．
  • オンタイムでプラグインのインストール・アンインストールができる．

• XML Xpath Support

  • クエリーを Xpath で記述可能になった．

• Archive Engine Enhancements

  • MyISAMよりもログなどに特化した感じのエンジン
  • 集約系はかなり早いが，1レコードピックアップは非常に遅い
  • 圧縮率の向上
  • Archive Reader
  • オンラインバックアップツール
  • ユニーク・非ユニークキーのサポート
  • ファイルコピーで他サーバで自動的にデータベースを発見してくれるようになる．

• MySQL Cluster Disk-based Data

  • メモリ以外にディスクに保存できるようになった．

• 行ペースレプリケーション

  • INSERT は Statement-base と Row-base とコストはあまり変わらない
  • UPDATE などはコストが高くなる可能性がある
  • 変更範囲の小さい場合はかなり有利になる

• MySQL Cluster Replication

  • データセンターレプリケーション

###管理を容易に

• タスクスケジューラ
  • STORED PROCUDURE的に登録しておくと，決まった時間に実行できる

###高パフォーマンス化

• ALTER/DROP INDEX/ADD INDEX => Faster
• データロードの高速化

• SHOW PROCESSLIST => desc processlist;

• mysqlslap

  • パフォーマンス・テストツール
  • 同時接続数・接続回数など，実際に近いテストを行うことも可能

##Introduction to MySQL Cluster Carrier Grade Edition ###MySQL in Telecom

• 通信事業者でも多くの企業に採用されている

###MySQL Cluster Distribution

• MySQL Cluster SE
  • Commercial
• MySQL Cluster Community
  • GPL
• MySQL Cluster CGE
  • Commercial
• Difference
  • SQL 以外のアクセスができる
  • Geographical Replication が可能
  • LDAP Interfaceがある

###Archetecture Overview

• Hight Availability
  • 同期レプリケーション
  • 通常のレプリケーションとは異なる
• Performance
  • In-Memory データベース
  • Native API がある
• Scalability
  • 容易な scale-out 性
• Geographic Replication
  • 地理的に離れたデータセンターに対してレプリケーション可能
• Reliability
• Interoperatablity
• Rapid Development

• Lower TCO

  • 通常のサーバ上で動作するので，TCO 削減が可能

• CGE とは？

  • Telecom に対応できるように開発されたもの

• Components of CGE

  • C++ API によって高速化されている
  • SQL Node とは別に NDB API があり，mysql の wrapper ではなく Native Code が書ける
  • Data Node では，Disk にも書き込めるようになった
  • Management Node

• Synchronous Replication

  • 2相コミットプロトコルでやりとりしている
  • 状況によって同期・非同期を使い分けている

• Goegraphic Redundancy

  • Cluster 間のレプリケーションは非同期に行われる

• 障害耐性 : Checkpoint

  • すべてのデータをディスクに書き込んでいる(Local Checkpoint)
  • すべてのデータに完全性があるかどうか(Global Checkpoint)
  • データをノードグループに分散保存するので，各ノードグループで1台壊れてもサービスを提供し続けることができる.

• データのサイズ

  • データ:2GB x 2 コピー x 1.1 = 2#1.1 = 4.4GB
  • 各サーバは 4.4GB/4 = 1.1 GB

• Native NDB API を使うと4倍高速

• 死活監視

  • Heartbeats で

###Installation Basics

• System Requirements

  • Hosts max 255 nodes

• Steps

  1. Install Management Server
  2. Install Management Client
  3. Create config.ini
  4. Initialize Management Server
  5. Install Data Node
  6. Install MySQL Server
  7. Install MySQL Client

###Evaluation Tips

• 複雑なJOINはできない

• Hardware Recommendation

  • In-Memory データベースなので，64bit CPU がよい
  • よいディスクシステムも必要

• NDB API vs. SQL

  • lowr latency and less overhead

• Distribution Awareness

  • NDB API をつかうと，アクセスすべきデータノードががわかるので，ネットワークトラフィックも軽減できる

###Benchmarks

• DBT2 Benchmark

  • OLTP

• System Configuration

  • CGE 5.1 w/patches
  • X5120 x 2
  • 4GB
  • 8 nodes

• Results

  • Dolphine Express を使うと性能が上がる？
  • 8ノードで 100000トランザクション/分まで対応可能だった

###Roadmap

• MySQL Cluster 6.2
  • Disk Data サポート
  • Geo-Replication
  • DIstribution Awareness
  • 多接続時の最適化
  • レプリケーションの最適化
• MySQL Cluster 6.3
  • LDAP Interface
  • 圧縮バックアップ，圧縮 Local ChechPoint
  • Master/Master レプリケーション
  • MySQL 5.1 の機能など
• MySQL Cluster 6.4
  • クラスターのオンライン操作
  • 8 core までの対応
  • GUI 管理ユーティリティ
  • Windows Support
• Beyond MySQL Cluster 6.4
  • MySQL 6.0/6.1 のインテグレーション
  • 外部キーサポート
  • Isolation Level の細分化など

##MySQL Performance Tuning #2

• System Configuration
  • All logging turned off
  • Query cache turned off
  • Memoby buffers “large enough”

###Logging Application

• options
  • MyISAM
  • MyISAM with INSERT DELAYED
  • InnoDB
  • Archive
• Inserts/seconds vs. Concurrent connection
  • MyISAM
  • 16で頭打ちで下がり始める
  • MyISAM with INSERT DELAYED
  • 256までスケール
  • InnoDB
  • 64で頭打ちで下がり始める
  • Archive
  • 256以上にスケール
• CPU Usage
  • MyISAM は 64 で下がり始める
  • 調べるテールブロックの競合が起き，ロック待ちが多発する
  • DELAYED
  • 64 で 頭打ちになった
  • Archive
  • 使い切っている
• Disk Utilization(every 10 COMMITs)
  • InnoDB
  • かなり高いディスク利用率
• Conclution
  • MyISAM
  • 64ぐらいでロック待ち多発
  • DELAYED
  • CPU 利用率で頭打ち
  • InnoDB
  • ディスク利用率が原因で頭打ちになる
  • Archive
  • DELETE/UPDATE できない

###Track entry-time automatically

• options
  • TIMESTAMP column
  • TRIGGER
  • TIMESTAMP に NOW() など値を INSERT
• Results
  • TIMESTAMP column が最も早い
  • TRIGGER がもっとも遅い

###Intex Merge Test

• 起こりうる条件(複合インデックスでの)
  • 2つのインデックス張ったカラムの OR 接続
  • AND である場合
  • どちらかが LIKE/BETWEEN
• SELECT ip, uid, ts FROM sessions WHERE ip = ‘ip’ OR ts = ‘TS’;
  • 特定の値で SELECT
  • EXPLAIN で index_merge がでる
  • インデックスからキーを取得して，キーをマージ，そのリストからデータを取得
• SELECT ip, uid, ts FROM sessions WHERE ip = ‘ip’ UNION SELECT ip, uid, ts FROM sessions WHERE ts = ‘TS’;
  • 各結果を結合する
  • index_merge の方が高速
• SELECT ip, uid, ts FROM sessions WHERE ip = ‘ip’ AND ts = ‘TS’;
  • Intersect vs .composit index
  • 各 index から該当キーを取得，合致する部分を見つけ出し，データを取得
  • 複合インデックスサーチの方が若干高速ではあった．
• SELECT ip, uid, ts FROM sessions WHERE ip = ‘ip’ AND ts BETWEEN ‘TS’ AND ‘TS’;
  • 範囲で検索
  • インデックスを範囲から取得してソート，キーをマージして，データを取得
  • 複合インデックスの方が，2つの条件を同時に満たすキーを取得できるので，高速に検索できる

###NOW()

• options
  • NOW()
  • MONTH(NOW())
  • MONTH(‘YYYY-MM-DD’)
  • DATE_FORMAT
  • SUBSTRING
• BENCHMARK 関数は有用なツールになる

##InnoDB Index Merge

• UPDATE/INSERT でインデックスの再作成などは非常に高コスト
  • INSERT BUFFER がある
• Insert Buffer with LOAD DATA INFILE
  • インデックスがない場合に比べて
  • ユニークではないキーならそれほど変わらない
  • ユニークインデックスならかなり低下する
    • UNIQUE_CHECKS=0 とすると，ユニークチェックしないので，非常に高速化される
  • 同時接続32程度だと有意な差がなくなる
  • SHOW ENGINE INNODB STATUS;

###What’s the last payment of my customers?

• Using the SAKILA schema…
  • 1.5/sec
  • 64 Threads になると，8時間たっても終わらなかった
• Delrived Table を使う(サブクエリー)
  • 12.5/sec 程度に向上
• View を作る
  • いいと思ったが，あまり変わらない
  • 同じ操作だから変化はない
• 最終支払日のみを記録したテーブルを作る
  • 25/sec 程度に向上
  • ただし2重にテーブル更新しなくてはならない
• 複合インデックスをはると，非常に高速になる．
  • 370/sec ぐらいまで

##Who Is a Query REALLY Executed

• SHOW GLOBAK STATUS LIKE ‘handl%’;
• どのように実行されているのかの情報．
• Handler_delete
  • DELETEs
• Handler_read_first
  • 最初のインデックスから
• Handler_read_key
  • キーにマッチするものをすべて持ってくる場合
• Handler_update
  • UPDATEa
• Handler_write
  • INSERTs

###SHOW STATUS

• SHOW SESSION STATUS
  • 該当セッションのみの情報を表示できる
• Com_select/Select_scan/Handler_read_rnd_next
  • フルテーブルスキャンが行われているので
  • Rows + 1 で empty になって終わり
• Created_tmp\tables/Handler_read_key/Handler_update/Handler_write
  • テンポラリテーブルが作られる感じ
• Handler_read_rnd_next/Sort_scan/Sort_rows/Handler_read_rnd
  • テンポラリテーブルの結果について，ファイルソート(filesort)が行われている
• 内部をしっかり知っていれば，わかることなんだろう．

##MySQL Replication を理解する

• Instance
  • MySQL の process
• Schema
  • オブジェクトのコンテナ
  • テーブルとか

###通常のレプリケーション

• Slave
  • IO Threads
  • SQL Threads

###Clustering != Replication

• 実時間 = 同期的ではない
• フォールトトレラントではない
• 2相コミットではない

###Replication == Clustering

• 参照のスケールアウトが可能

###MySQL Cluster

• 同期的であり，完全性が保証されている

###Common Paradigms

• Read Replication
  • 参照のスケールアウト
  • 同期的ではない
  • One Point Failure がある
  • だが簡単に参照のスケールアウトができる
• Application Cluster
  • InnoDB Master -> MyISAM Slave
  • Slashdot は検索用データベースを分割することで，メインデータベースから負荷を分散した．
  • 特定用途向けにデータベースサーバを立てた感じ
  • Blackhole エンジンによって，Binlog にだけ書き込むことで，I/O を削減できる
• Master -> Slave -> Slave
  • I/O を低減できる
• Writing Cluster
  • Multi Master
  • Master-Master Replication
  • No single point of failure
  • 主キーやコンフリクトに備える必要がある
• Split Master
  • 共有ディスク/DRBD
• Star Cluster
  • やはり主キー問題がある
• Circular
  • 循環参照
• Federated
  • リモートサーバのテーブルをローカルのように参照できる
• Memchached Clustering

###どう考えるべきか

• 何が遅くて，何を早くしたいのか
• パーティショニングも考える
• 同期的ではないことを前提としたアプリケーション設計
  • パフォーマンスを優先するならば，同期的ではないことを念頭に置くべきかもしれない

##インデックスを使いこなす

• 現場で使えるMySQL の作者
• この内容も DB マガジンに掲載予定

###検索処理とインデックス

• CPU/メモリに対して，HDD のアクセス時間は1000倍近い差がある
  • ディスク I/O が性能阻害要因
• B+Treeインデックスの構造とI/O
  • インデックスは 16KB 単位のブロックで読む
  • ブランチとルートはキャッシュされるが，リーフとデータで2回のディスクアクセスが発生する
• InnoDB のインデックス構造
  • クラスタインデックス
  • 主キーとそれ以外のデータの組
    • データが大きくなるとインデックス自体が大きくなる
  • セカンダリインデックス
  • PK との組み合わせ
  • 主キー検索に関しては1回で読めるので早い
  • 主キーは可能な限り小さくする
• B+Treeインデックスの構造とI/O(範囲検索)
  • リーフは1回でいいが，データは数回読む必要がある
• インデックスの副作用
  • 選択範囲が多すぎるとフルテーブルスキャンを採用してしまう
  • インデックスは使用せずにデータをシーケンシャルスキャンする
  • ブロック単位でのリードなので，1件毎のランダムアクセスよりも効率がよくなる
  • 隣接ブロックも読み込むので，さらに効率よくなる
  • 全体の 10～15% 以上だとフルテーブルスキャンになる
• Covering Index
  • インデックスだけを読む検索
  • データファイルにアクセスせずに，インデックスのみを調べるだけで処理が完結する場合
  • SELECT primary_key from TABLE;
  • WHERE/SELECT のすべてのカラムがインデックスに含まれていること
  • EXPLAIN の Extra: が Using index になっていれば，Covering Index になっている
• マルチカラムインデックス
  • キーのすべてが AND 条件で使われている
• インデックスマージ
  • ディスクリード回数は，インデックス x 2 + データで3回で，さらにマージ処理が加わるので，マルチカラムインデックスよりも遅い場合がある
• マルチカラムインデックスが使えない場合
  • WHERE 条件に先頭列が指定されていない場合
  • OR 条件の場合
  • インデックスマージの方がいい
• ソート処理とインデックス
  • インデックスがソート対象だと，ソート処理の必要がない
  • ORDER BY がインデックス対象でないと，データを取得した後にソート処理している
  • Extra: Using filesort の場合
  • 両方インデックスに入っていても，片一方が範囲であれば，ソート処理が発生する
  • ソート対象のキーが使われていると，Using filesort は起きないが，フルテーブルスキャンが発生する

• ORDER BY LIMIT N の落とし穴

  • 以下のどれかが使われる
  1. 条件にあるインデックスが使われ，ソートして N 件を取得する
  • type=range, key=条件, Using filesort)
  • 条件を満たすレコードが少ないといい
  1. ソート対象のインデックスが使われ，レコードを上からなめて N 件になったら返す
  • レコードが大量にあれば， LIMIT の条件を満たす可能性が高くなる
  1. フルテーブルスキャンが使われる
  • 条件でインデックスが使えず，2番目の条件も満たさない場合
  • FORCE INDEX/IGNORE INDEX ヒントでコントロールしてみる

• DBT1 ベンチマーク結果

  • インデックスを適切に張れば，8コアまではスケールアウトする

###更新処理とインデックス

• INSERT すると何が起こるのか
  • リーフブロックがいっぱいになると，新しいリーフブロックが割り当てられる
• 昇順INSERT
  • インデックスのブロックがフルに使われ，効率がよくキャッシュされやすい
• ランダムINSERT
  • リーフブロックに空きを作るように INSERT されていくので，虫食い状態になりやすく，キャッシュが有効に働かなくなる
  • ベンチマーク
  • 1000件レコードに 100万件 INSERT
  • インデックス1個
    • 時間1.8倍
    • 大きさ2倍
  • インデックス3個
    • 時間3倍ちょっと
    • 大きさ2倍ちょっと
• InnoDB と AUTO_INCREMENT
  • 5.0 では AUTO_INCREMENT 時にテーブルロックをかけるために，同時接続数の増加に対してスケーラビリティが劇的に落ちる
  • 5.1 ではかなり軽減される
• 昇順INSERTのためのアーキテクチャ
  • Buffering insert
  • インデックスの無いテーブル
  • 緩衝材サーバに INSERT -> SELECT + Sort + Bulk update + DELETE
    • リアルタイム性が求められるとダメ
  • キャッシュサーバ
  • memcached にストアして，その結果をバッチジョブなのでまとめて本サーバに送る
  • キューを使う(Q4M Storage Engine)
  • memcached の代わりに Q4M を使う
  • 実稼働サービスがある
• Blackhome Engine
  • テーブルデータが空のエンジン
  • binlog によってスレーブで反映する

###プラクティス

• インデックスが多すぎる

  • インデックスが多すぎると
  • 更新性能が落ちる
  • サイズが増える
  • キャッシュが働かなくなる
  • 必要なものだけに張る

• インデックスが大きすぎる

  • スペースを消費してパフォーマンスが悪化
  • URL/UUID
  • 先頭 N バイトだけにする
  • ハッシュ値をインデックスにするなど

• データ型の不正比較

  • varchar_column にインデックスがあっても，=1 とかならインデックスが使われない
  • 型をあわせる

• 無意味なマルチカラムインデックス

  • 意図的にマルチカラムインデックスを使えるように条件を追加するなど

• MEMORY ENGINE は ハッシュインデックスを作るので，btree などで意図的にインデックスタイプを指定する

• テストパターン・データの準備の欠如

  • 実データとかけ離れていると，ちゃんと性能評価できないので，最適化もできない
  • データに偏りがあると，インデックス戦略も変わるので，注意が必要

###まとめ

• ランダムアクセス回数を最小化すること
• EXPLAIN を使い，実行計画を知る
• データ型，サイズなどはしっかりあわせる

##スタンプラリー抽選会

• デュークタオル当たりました:)

comments powered by Disqus