MySQLカンファレンス2007 １日目 リアルタイムレポート

注：このレポートはかなりの割合でスライドの内容の写しで、ときどき話していることを絡めています。まとめがすごいんじゃなくてプレゼンスライドの内容がすごくまとまってるってことですよー

MySQLユーザーカンファレンス2007に来ています。会場カコヨス！


  • Larry社長日本語で挨拶中。がんばれー
  • ステファン・ノレーン閣下 駐日スウェーデン大使
    • アジアでMySQLカンファレンスが開かれるのは初めてだそう

Matz氏登場 - MySQLカンファレンス2007


  • こんにちは。Rubyのまつもとです。
  • MySQLに詳しくないのに、どうしても断れないコネクションを通して依頼されたので来ました（会場笑）
  • MySQLのことはわからないからオープンソースについて話すよ
  • ソフトウェアが無価値だった牧歌的な時代の話から

フリーソフトウェア

自由なソフトウェア

  • 実行する自由
  • 学習する自由
  • 修正する自由
  • 再配布する自由

is GPL.

オープンソース(1998)

マーケティング用語

  • Netscape
  • 「無償」イメージからの脱却
  • 企業におけるOSSの活用
  • 「新しい開発スタイル」

Just for Fun - それが僕には楽しかったから : Linus Torbalds

Linux

オープンソースOS

  • 趣味でスタート
  • 世界中で普及
  • 当初「おもちゃで使えない」
  • 現在「主要OS（のひとつ）」

MySQL

  • 高機能より高性能
  • OSSビジネスの成功例

LAMP

Ruby

  • 趣味でスタート
  • 技術者には高評価
  • 「仕事には使えない」

Ruby on Rails

  • 高生産性
  • 「仕事にも使えるかも」

From Java to Ruby

  • Java(の一部)を置き換える
  • 冷静な以降戦略
  • 新しい技術への熱い思い

エンタープライズOSS

昔

  • 信頼できない
  • 理解できない
  • 使えない

現在

  • 「案外、使えるかも」
  • 「普及してる」
  • 「活用してる」

未来

MySQL高可用性ソリューションの概要 - MySQLカンファレンス2007


高可用性とは？

  • 障害が発生してもシステム内のリソースが利用可能な状態に保たれること
  • カテゴリは2つ
    • ハーウェア
    • ソフトウェア
  • 連続化要請
    • 無停止サービス
    • フェイルオーバ時もサービスを中断しない
    • 非常に高水準の可用性（不必要なことも多い）
  • フォールトトレランス
  • SPOF(Single point of failure)
  • フェイルオーバ

高可用性の要件と必要考慮事項

  • SLA（サービス品質保証契約）
  • 予算と納品までのスピード
    • セキュリティ
  • ストレージの検討
  • いろいろ

スケールアップとスケールアウトの違い

MySQLはスケールアウト型

  • スケールアップ
    • åž‚ç›´çš„
    • 高価
  • スケールアウト
    • 水平的
    • OSS

レプリケーションとは？

クラスタリングとは？

MySQL Clusterの解説

  • スライド中心なので割愛
  • スライドはダウンロード公開されるはず

DRBDおよび Heartbeat による高可用性MySQL：MTVジャパン、モバイルサービス - MySQLカンファレンス2007


MTV Flux/MTV Mobile DB projectにおけるMySQL構成の話

  • Master/Slave
  • MySQL Cluster
    • MySQL社から「まだやらないほうがいい」と言われた（会場笑）

DR:BD/Heartbeat HA Master 構成

  • スケール的にはSingle Masterよりちょっといい
  • アプリに関して
    • Single masterとほぼいっしょ
    • フェイルオーバーが十分早いのでたいした影響がない
  • Heartbeatに関して
    • 設定が大切 間違えの余裕がない
    • peerの接続が特に注目点
    • 本番化する前にテスト
  • mysqld
    • ノード移管が簡単
    • プライマリとセカンダリのmysqlを同時起動しないことが大切
    • Heartbeatにおまかせ
  • DR:BD
    • 既存システムに導入の場合、ディスクのレプリケーションが必要
    • 動きを理解するため十分検証するとよい
    • アウテージがあった場合、drbdステータスの確認が必要
よかったこと
  • ダウンタイムが少なかった
  • サポートがあった(MySQL / LINBIT)
  • 設定はMySQL Community版にも使える
  • 他の構成にも使える(multi-master,master/slave)
悪かったこと
  • 勉強や検証に時間をかける必要がある
  • 設定やソフトの関連性が複雑
  • mysqldプロセスの再起動はシンプルではない
  • 運用の関係者も設定や動きを理解しないといけない

MySQL パフォーマンスチューニング&ベンチマーク - MySQLカンファレンス2007

http://www.mysql-ucj2007.jp/details/e13.html

Agenda

  • An introduction to Benchmarking
  • data structures
  • query optimisation and query cache
  • etc..

Why Benchmark?

  • Allows tracking of performance over time
    • application
    • SQL snippet
    • ..
  • You get load and stress information
  • ever wondered if for the job InnoDB or MyISAM would be better? Or if running on Linux or FreeBSD made more sense?

The Good Scientists Guide to Benchmarking

  • The scientific method suggests changing only one variable at a time
  • The scientific method suggests repetition, more than once to verify results. If results vary greatly, think about taking averages
    • Repeat, rinse , repeat , rinse!
    • 少なくとも3回は。

The Good Scientists Guide to Benchmarking II

  • Isolate your environment
  • Use a different MySQL instance
  • Savle all configurations!

Benchmarking Tools

  • super-smack
    • Flexible tool for measuring SQL script performance
  • mysqlslap (like ab; in MySQL 5.1)
  • MyBench
  • SysBench
    • For raw comparisons of different MySQL versions/platforms
  • Apache Bench

Benchmarking Tools II

  • SHOW commands in MySQL
    • SHOW PROCESSLIST | STATUS | INNODB STATUS
    • SHOW PROFILE - in 5.0.37 and above, Community Contribution, Linux only
  • EXPLAIN and the Slow Query Log
  • MyTop
  • vmstat/ps/top/gprof/oprofile (and contents of procinfo)

Slow Query Log

  • log_slow_queries=/var/lib/mysql/slow-queries.log
  • long_query_time=2
  • Then, use mysqldumpslow
  • In 5.1, you can log these details ...

-
-

EXPLAIN basics

  • Provides the execution plan chosen by the MySQL optimiser for a...

「オンラインで読んで」

EXPLAIN columns

  • select_type
  • table
  • ...

「オンラインで読んで」

Scans and seeks

  • A seek, jumps into a random place to fetch needed data.
  • A scan will jump to the start of the data...

When do you get a full table scan?

  • No WHERE conditions
  • No index on any field in WHERE condition
  • When your range returns a large number of rows, i.e. too many records in WHERE condition
  • Pre-MySQL 5, using OR in a WHERE clause
  • SELECT * FROM

Subqueries

  • Don't use them; replace with a JOIN
    • unique_subquery: results are known to be distinct
    • index_subquery: otherwise
  • Co-related subqueries are worse
    • ...

Indexes

  • Covering index ...
  • Remember that when using InnoDB, use a small PK value
  • Alwaays, add a Primary Key

Good Schema Practice

  • Use small data types
    • Is a BIGINT really required?
  • Small data types allow more index and data recoreds to fit into a single block of memory
  • Normalise first, de-normalise later
    • Generally, 3NF works pretty well

正規化しすぎない。

Storing IP addresses

  • IP addresses always become an INT UNSIGNED
  • Each subnet corresponds to an 8-byte division of the underlying INT UNSIGNED
  • From string to int? Use INET_ATON()
  • From into to ...

Query Cache

  • スライド
    • たくさんのストレージエンジンがある
  • Understand your applications read/write ratio for most effective use
  • Compromise between CPU usage and read performance
  • Remember that the bigger your query cache, you may not see better performance, even if your application is read heavy

Query Cache Invalidation

キャッシュの無効化

  • Coarse invalidation designed to prevent CPU overuse
  • ...
  • Thusm any modification to any table referenced in the SELECT will invali...

Choosing a Storage Engine

  • MySQLs strong point: many engines MySQLはたくさんエンジンがあるよ
  • Use InnoDB for most operations (esp. OLTP), except:
    • big, read only tables
    • high volume streaming tables(logging)
    • specialised needs (have special engines)
  • Tune InnoDB wisely

PBXがブログとかにいい。

  • MyISAM
    • Good for logging, auditing, data warehousing
  • Archive
    • Very fast insert and table scan performance めちゃ速いよ
    • Read only. Good for archiving, audit logging
    • 内部的にgzipしてるよ
  • Memory

Quick InnoDB Tuning Tips

  • innodb_file_per_table
  • innodb_buffer_pool_size = (memory * 0.80)
  • innodb_flush_log_at_trx_commit
  • innodb_log_file_size - keep it high, however recovery time increases (4GB が最大)

Real World MySQL Use (RWMU)

  • Run many servers
    • Your serious application cannot run on "the server"
  • "Shared nothing" architecture
    • make no single point of contention in the system
    • Scales well, just by adding cheap nodes
    • If it works for Google, it will work for you!

RWMU: State and Session Information

  • Dont keep state within the application server
  • Key to being stateless: session data
  • Cookies are best validated by checksums and timestamps(encrypting is a waste of CPU cycles)

RWMU: Caching

  • 動的コンテンツには向かない
  • Cache full pages, all in application, and include the cookie
  • Use mod_cache, squid , and the Expires header to control cache times
  • A novel way: cache partial pages!
    • pre-generate static page snippets, then bind them in with dynamic content ...

RWMU : Data Partitioning

  • replication is great for read heavy applications
  • Write intensive applications should look at partitioning
  • Partition with a global master server in mind
    • Give out global PKs and cache heavily (memcached)
    • ...
  • Consider the notion of summary databases

RWMU: Blobs

  • Large binary object storage is interesting
    • Image data is best kept in the filesystem, just use metadata in DB to reference server and path to filename
    • Try the Amazon S3 storage engine?
  • ...

RWMU: Misc.tips

  • Unicode - use it Unicodeはもう当たり前
    • ブログで使われる言語は日本語が最も多いから
  • Use UTC for time
    • Think about replication across geographical boundaries
  • sql_mode might as well be strict
  • Keep configuration in version control
    • Then consider puppet or slack for management of various servers

Resources

  • MySQL Forge and the Forge Wiki
  • MySQL Performance Blog
  • Planet MySQL
  • #mysql-dev on irc.freenode.net
    • chat with developers, and knowledgeable community members

services

  • http://www.mysql.com/training/
  • 日本語における認定が今月末(2007å¹´9月末)から始まる

質疑応答

  • Email me: colin@mysql.com
  • Cathe me on IRC, at #mysql-dev: ccharles

新ストレージエンジン Falcon のアーキテクチャ詳細技術解説 - MySQLカンファレンス2007


MySQLアーキテクチャの解説

  • ストレージエンジンがプラガブル
  • 5.1では共有ライブラリ化して動的に組み込めるようになる
  • InnoDBのかわりにfalconを使う

Falconとは

  • MySQL ABにより現在開発中の、トランザクション対応のストレージエンジン
  • 開発中で完成してない。alpha版
  • Jim Starkey氏を中心に開発
  • InnoDBをほぼ全ての点で上回ることを目指している
  • まもなくベータ版が登場。
  • MySQL 6.0で安定版を搭載予定
  • 思想：現代的なハードウェア環境をフル活用できるRDBMSを目指す
    • マルチコアCPU
    • 大容量メモリ
    • 低速なディスク、RAID

InnoDBとの主な差異

ほかのストレージエンジンとの比較

図

Falconのアーキテクチャ概略

  • 4つのメモリ領域に分けて開設 Record Cache, Page Cache, Log Cache, System Cache
  • できるだけ多くのレコードをRecord Cacheに乗るようにしてある
  • Record CacheはPage Cacheよりもオーバーヘッドが少ない

プロセス/スレッド

  • MySQLはマルチスレッド型アーキテクチャ
    • 接続1個に対してスレッドを1個割り当てて動作
  • Falcon専属の、バックグラウンドで動作するスレッドがある
    • チェックポイントの実行
    • キャッシュ管理（古いレコードの退避など）
  • mutexをラッピングして排他制御 (SyncObjects)
    • Read/Writeロックを導入
    • Readは他のReadをブロックしない（※行レベルロックとは別物）
    • 同時実効性を大きく高める効果がある
    • マルチCPUコア環境で重要な技術

ファイルの読み書き

シリアルログファイル、データファイル

  • シリアルログファイルはREDOログに相当。ただし、サイズは固定ではない
  • 未コミットの情報はデータファイルに書かれない
  • コミットした情報は最終的にデータファイルに反映される
  • Falconの「グループコミット」機能はきわめて高性能

表領域（テーブルスペース）

  • Oracleの秤量域と類似
  • 任意のファイルを割り当てられるため、I/O分散が可能
  • Falconのほかに、T.1以降でMySQL Clusterが...

インデックス構成

  • ルート -> ブランチ -> Leaf
  • クラスタ索引/セカンダリ索引という区別はない。全部この形
  • RecordNumberは現在4バイト固定（安定版までに変更予定）
  • ゆえにインデックスサイズは（クラスタ索引に比べて）さほど大きくならない
  • ページはI/Oの最小単位。サイズは可変（2KB-32KBとなっているが安定版までにどうなるかはわからない）

インデックス値の圧縮

  • 接尾辞の圧縮
    • 数値型：末尾のゼロを圧縮
    • 文字列型：末尾の空白を圧縮
  • 接頭辞の圧縮
    • 各ページの先頭のインデックス値は圧縮しない
    • 2番目以降のインデックス値は、開始何倍とが先頭のインデックス値と一致するかを調べ、その分を圧縮する
  • うまくいくと60%ぐらい圧縮できる
  • 圧縮処理をするので当然CPUは使っちゃう。on/off切り替え可能にするか検討中

インデックスアクセラレータ

  • commit時にインデックスを反映する
  • rollbackのオーバーヘッドが減る
  • 多少検索処理がオーバーヘッドになる
  • 以前ベンチマークしたときはボトルネックになっていたが今は大分改善された

レコードの取得

  • インデックスを検索し、レコード番号を取得
  • レコード番号から実際のレコードを取得
  • メインのレコードの取得はキャッシュから行われる

データ型と消費サイズ

  • GIS型を含む全MySQLデータ型をサポート
  • 整数型を含む全データ型が可変長として扱われる
  • 消費サイズはデータ型ではなく、実際の値に応じて変わる
  • どのデータ型であろうと値に応じて圧縮する

FalconはTEXT/BLOB型を別メモリ領域に管理する

AUTO_INCREMENTの動作の違い

  • FalconはInnoDBと違う。
  • FalconはMyISAMと同じ。
  • FalconとMyISAMは最後にInsertされた値をもとにする
  • InnoDBはmax関数でとれた値をもとにする

トランザクション、ロック、MVCC

  • 行レベルロッキング
  • SELECTはロックをかけない。更新系処理と競合しない。InnoDBも同じ
    • ブロックするSELECT FOR UPDATEもサポート
  • AUTO_INCREMENTの割り当てにテーブルロックをかけない
    • 重くなるので。5.1で改善される
  • 分離レベルとしてRead Commited, Repeatable Readをサポート
    • Serializableもサポート予定
  • ロックエスカレーションは発生しない。InnoDBも同じ
  • デッドロックの検知は自動で行う
  • ロックをかけないカラム、インデックスの追加/削除
  • ロストアップデートの自動検知
  • Next Keyロッキングは発生しない

ロストアップデートの自動検知

  • ロストアップデートとは
  • Falconの動作を、設定でInnoDBと同じにすることもできる（デフォルトで同一にする方向）

Next-Key Lockingとは (InnoDBのロック制御)

  • InnoDBログファイルとバイナリログの不整合をっふせぐために必要な実装
  • Insert INTO t1 .. SELECT .. FROM t2
    • t2に対して共有ロックをかける
  • UPDATE t1 SET xx WHERE non_index_column=x;
    • フルテーブルスキャンになる。スキャンしたレコード全体に対して排他ロックをかける
  • UPDATE t1 SET xx WHERE non_unique_index_column=x;
    • 当該インデックスと、その前後に対して排他ロックをかける
  • セッション１:INSERT INTO t1 SELECT * FROM t2
  • セッション２:INSERT INTO t2 VALUES(...)
    • セッション2がセッション1よりも前に終わったとする
    • バイナリログの中身は、順番が逆転する
  • Next-Key Lockingはこれを防ぐための実装

FalconではNext Key Lockingは発生しない

  • 同時実効性の低下は5.1で回避される

参考資料