MySQL ユーザカンファレンス 2008 1st Day

MySQL

というわけで行ってきました．かなり大勢の人が来ていたので，かなり熱い戦いカンファレンスになりました．聞いてきたセッションのほとんどが英語だったので，もう少しヒアリングできればなと，少し後悔しています．

かなり長いのですが，2日分のログを．

基調講演

  • ビジネスボリュームはかなり大きくなっている．

創設者 David の挨拶

  • History
    • 1982 First database code
    • 1995 MySQL code
    • 1996 public release
    • 2001 'Real' Company start
    • 2008 Sun acquires MySQL
  • 特徴
    • Pluggable Storage Engines
    • それぞれに特徴あるストレージエンジンを，状況に応じて使い分けることができる
      • しかし Connector/Parser などは共通なので開発者に優しい
  • Storage Engines
  • Data Warehousing Appliance
    • Kickfire
  • たくさんのイノベーションを自由に利用できるのが MySQL の特徴
  • 多くをサポート
    • 数多くの言語でサポートされている
    • 数多くのOSでもサポートされている
  • Sun CEO のブログで，買収しても何も変わらないことを明言している．
  • ユーザなどから報告されたバグリストは公開している．
    • 「隠された汚い下着」と言っているらしい．
  • MySQL = My(ミー)のSQL

MyNA

  • 日本 MySQL ユーザ会
  • 2000年発足
  • MySQL ML に入れば会員
  • 要望
    • 日本語の情報が欲しい
    • オープンソースを続けてほしい
    • MySQL 社の社員の協力を，今後もいただきたい．

スポンサー紹介

A Bried Overview of the Work Done in Shinsei Bank

  • 新生銀行
    • IT部門ではかなり革新的で注目されているらしい
    • 1000万人規模の顧客がいる
  • 裏側
  • Methodology
    • Harvard Bisuiness Review
      • atricleID=R0803J
  • 8年前に立てた原則
    • 部分的に構築するが，基本的には何も変えない
    • 既存のものだけを使い自社開発はしない
    • Open なものを使う
    • 顧客の Experience を重視
    • Virtual な組織を形成して，世界中の知恵を集結
    • 紙を極力使わないように
  • Benefits of using standard components
    • Low cost
    • Speed
    • Flexibility
    • For customers
    • For staff
    • Embedded controls
  • Methodology at a glance
    • Process と Technology に分解
    • それぞれを各エレメントに分解
    • Technology ã‚’ conponents(Software/hardware/etc...)に分解
    • すべては失敗すると仮定する
      • 人が階級しなくても稼働し続けることを重視した
  • 保証すべきもの
    • 安全性・フレキシビリティ・速度・コスト・能力
  • 3ヶ月サイクルで動く
  • 低コスト
    • ハイエンドなものはつかっておらず，低コストなものを数多く用意する
  • 各コンポーネント間で依存性を排除する
  • 古いソースコードはみていない
    • 古い技術での完成物を参考に，新しいシステムを制作する
  • リソースの制約
    • 問題を小さく分割して，各個に解決していく
  • Dell PC/Server を使っている
  • 裏側の変化
    • すべての情報が集約化されてスッキリ
  • 手法を CCL で公開
    • プレゼン資料も CCL で公開

MySQL トラブルシューティング概要

  • 開発目標
    1. 安定性
    2. パフォーマンス
    3. 使いやすさ
  • 安定性重視だが多くのトラブルが起きる
  • トラブルシューティング3種の神器
    • エラーログ
      • エラーが記録されているもの
      • 実際にはSTDOUTをリダイレクトしたもの
    • SHOW コマンド
SHOW WARNINGS
Query が失敗したときのエラー小度
SHOW GLOBAL STATUS
統計情報
SHOW GLOBAL VARIABLES
設定値を表示
SHOW INNODB STATUS
InnoDB モニタ
    • perror
      • OS エラーの意味を表示
      • MySQL Cluster なら -ndb で各種情報をみることができる
  • その他よく使うツール
    • Slow Query Log/Query.log/Binlog/OS Log
    • デバッガ
    • DTrace
      • 動作確認
    • xVM/VMWare
      • 動作確認
    • gcc
      • 動作検証プログラムの作成
レプリケーション
  • マスター側

アプリなど-->テーブル--(更新)-->バイナリログ--(マスタースレッド)-->スレーブへ

  • スレーブ側

--(I/O スレッド)-->リレーログ--(SQL スレッド)-->テーブル

SQL スレッドが停止してしまった
  • 原因
    • メモリ不足
      • 各種バッファに割り当てるメモリが足りない -> 増やして再起動
    • スレーブ上のテーブルを更新してしまった
      • マスターデータからリストア
    • バイナリログ欠損
      • コマンドで明示的に purge したりすると発生する
      • 対象テーブルがわかっていればマスター上で dump/restore で復旧できる
    • その他一時的なエラー
SET GLBOAL SQL_SLAVE_SKIP_COUNTER = N;
START SLAVE;
I/Oスレッドが停止
  • ネットワークのエラー
    • ping で確認
    • max_allowed_packet を増やす
  • ユーザがログインできない
    • パスワードを変更したりとか
  • server_id がかぶっていないか
スレーブの遅延
  • 長時間かかるクエリはないか？
    • マスター上ではクエリ完了時に binlog に記録される
    • テーブルの更新は素早く！
  • スレーブに負荷をかけすぎていないか？
    • 参照系処理の CPU リソースがとられてしまっている
    • マスターからのクエリが SQL スレッドでなかなか実行できなくなるから
  • ネットワーク帯域は十分か？
サポートに問い合わせるときは
  • SHOW SLAVE STATUS\G
  • SHOW MASTER STATUS\G
  • SHOW MASTER LOGS\G
  • エラーログ
  • my.cnf
  • テーブル定義
レプリケーショントラブル対策
  • バイナリログ欠損を防ぐ
sync_binlog=1
innodb_flush_logs_at_trx_commit=1
    • ただし更新が遅くなる
  • テンポラリテーブルは使わない
    • クラッシュ再起動後にはなくなるから，CREATE TEMPORARY TABLE は使わない．
  • マスターとスレーブにおいて，コネクション毎のバッファは同じ程度に設定しておく
    • スレーブでクエリが実行できなくなることを防ぐため
  • スレーブを複数用意する
    • マスターがこけたとき対策
    • バイナリログを有効化
    • log_slave_update=ON
クラッシュ!!
  • SEGV が発生するのは
    • MySQL のバグ
      • NULL ã‚’ boolean に変換すると crash するというバグがあった
    • OS のバグ
    • ハードウェアの故障
  • Linux は stack trace が表示される
  • core file
    • core-file オプションが必要
MySQL サーバが起動しない!!
  • 情報源はエラーログ
    • 繰り返し発生してないか？
    • InnoDB のリカバリは完了しているか？
      • クラッシュ後の再起動に自動的にリカバリするので，バイナリログの大きさが大きいと時間がかかる
    • ファイルシステムがいっぱいではないか？
    • 設定に問題は？
      • InnoDB の設定変更は特に注意
テーブルコラプション・MyISAM
  • なぜ起きるのか
  • 復旧手順
    • CHECK TABLE/REPAIR TABLE
  • mysqlcheck
    • 起動中に実行
  • myisamcheck
    • 起動していないときに実行
    • テーブルの再作成
CREATE TABLE new_tbl KILE tbl;
INSERT INTO new_tbl SELECT * FROM tbl;
DROP TABLE tbl;
  • 予防策は
    • ない！
    • クラッシュしたらバックアップからのリストアを検討
    • 他のストレージエンジンへ移行
テーブルコラプション・InnoDB
  • 修復する機能はない
    • クラッシュ後にリカバリしなければ The END!!!
    • テーブルスペースとログファイルの不整合が起きるパターンが多い
  • 復旧方法
    • innodb_force_recovery=4 or 6
    • mysqldump!
    • テーブルをドロップ
    • innodb_force_recovery を解除して再起動
    • リストア
  • 予防策
デッドロック
SHOW INNODB STATUS
  • 統計情報を見るコマンド
  • 1分か見るたびに更新
  • SEMAPHORES : ロック情報
クエリの実行結果がおかしい
  • バグの可能性がある
    • 再現テスト
  • とにかくソースコードを解析
  • SHOW WARNINGS が重要な手がかりに
  • dtrace が役に立つ

Out of Memory

  • 32bit バージョンは使わない
  • バッファの割り当てすぎ
    • 特にセッション毎のバッファが大きすぎる場合が多い
    • read_buffer_size=64M とかは多すぎ
      • 512K 程度でよい
Sort Aborted
  • ソートバッファを割り当てるためのメモリが足りない
  • テンポラリが作れない
  • ロック待ち
  • ソート中に KILLされた シャットダウンされた
Aborted Connections
    • mysql_close() を忘れた
    • TCP/IP timeout
table is full
  • MyISAM
    • 内部ポインタのサイズが小さすぎる
      • テーブル再作成
  • InnoDB
    • テーブルスペースを使い効いてしまった
      • テーブルスペースの拡張
  • ndb
    • メモリが足りない
ログインできない
  • パスワードが違う
    • root でリセット
  • 4.0 以前から 4.1 移行へ接続
    • サーバ側で -old-password を用いる
    • 場ジョンアップする
  • Can't create a new thread
    • スレッド数の上限(OS)
    • スタック不足
  • Connection refused
文字化け
バックアップ
  • 終わらない
    • 巨大なテーブルを mysqldump でバックアップ
    • メモリ不足
      • --quick オプションで
  • 適切なバックアップを
リストアなど
  • リストアが終わらない
    • 5.0.52 〜 5.0.54 では CLI が極端に遅い
  • 設定が違う
    • -max-allowed-packet
    • -innodb-buffer-pool-size
    • -key-buffer-size
  • 時間見積もりが甘かった
    • 何とかは買うする
      • バイナリログを無効化
      • doublewrite を無効化
      • trx_commit ã‚’2に
MySQL Enterprise Monitor
  • エージェントがサーバへ到達できない
    • これが 99% らしい
MuSQL Cluster
  • SQL ノードがクラスタに接続できない
    • ネットワーク
    • connect-string
    • ノードIDなど
    • ndb_mgm -e SHOW
    • SHOW ENGINE NDB STATUS
  • クラッシュしたら
    • まずはデータノードを再起動
  • クラスタ全体が停止
パフォーマンス系の問題
SHOW GLOBAL STATUS
  • Select_*
    • 遅いクエリに関する情報
ワンポイントアドバイス
  • 仕様を理解する
    • 正常・異常の区別
  • 再現してみる
  • ソースコードを読む
  • OSに詳しくなる

Memcached and MySQL

  • Server
    • slab allocator
    • libevent based
    • simple protocol
    • Hash Table
    • 他のサーバのことは知らない
  • Client
  • Consistent Hash
  • 特徴
    • memcached はデータベースじゃないからダンプできない
    • 冗長的ではない
    • サーバではフェイルオーバーじゃない
    • 認証機能はない
  • Examples
    • key:value の巨大ハッシュとして扱える
  • limit
    • Key Size(250 bytes)
    • Data Size ! Mega byte
  • Tools
    • Protocol
      • Telne
      • Text baseだから
  • CPU 使用率はかなり低い
  • Future
    • Binary Protocol
    • Multiple Engine Support
      • Char based
      • Durable
      • Queue
      • Highly Threaded

MySQL Enterprise ツールのご紹介

Workbench
  • MySQL Query Browser
  • MySQL Administrator
    • 管理用
  • MySQL Migration Tooklit
    • 移行ツールキット
  • MySQL Workbench
    • Entity/Relation Designer
    • データ・データベースを文書化する
  • 特徴
    • 複雑なデータベース構造を可視化
    • モデル化することで関係性をわかりやすく表示する
  • Forward Engineering
    • E/R図からSQLへ変換
    • データベースへ接続して直接実行可能
  • Reverse Engineering
    • 実在するデータベースの E/R 図に
    • 変更を適用可能
  • Database Compare
    • データベースの比較
  • Database Synchronization
    • データベースの同期化
    • オブジェクトレベルでの同期
  • Documentation
    • 文書化．設計書・仕様書など．
  • Roadmap
Enterprise
  • Subscription 形式
    • Database
    • Monitoring
      • Enterprise Monitor
    • Support
      • コンサル
Enterprise Monitor
  • 特徴
    • MySQL Server と Replication を自動探知
    • [NEW] Problem Query Detection, Analysis and Tuning
  • MySQL Enterprise Advisor
    • 設定やセキュリティ，アップデートやカスタマイズなどの管理機能
    • Replication の遅延やメモリの使用率などの統計・監視
    • Schema/Performance などの解析とチューニング
      • 予期しない変更・更新などの検知
問題の解決に向けて
  • Common Pain Points
    • データの利用側面と利用率の見込みの甘さ
  • Slow Query Log
    • 遅いクエリーの検出
    • インデックス使っているかどうかを調べられる
  • SHOW PROCESSLIST
    • on-time のプロセス一覧が見れる
  • EXPLAIN
    • インデックスがどのように使われているのかがわかる
Query Analyzer
  • 特徴
    • リアルタイムに「問題あるクエリー」の検出ができる
    • 悪いクエリーの検出
    • クエリーの EXPLAIN
    • Enterprise Monitor に統合される
  • MySQL Proxy 上で動いている
    • Proxy を通すことによるパフォーマンスの劣化があるが，一応ドキュメント化されている
  • 表示する情報
    • 各クエリーの情報を一覧で表示可能
      • 実行回数
      • 最短時間
      • 最速時間
      • 一定期間内の実行時間総計
        • どんなクエリーがデータベース内で時間を消費しているのかがわかる
    • Slow Query Log/SHOW PROCESSLIST に依存しないので，サーバ再起動など必要ない
Q&A
  • 将来的には Workbench に統合される計画のようだ

Life-X における MySQL 導入事例紹介

ソニーマーケティング概要
  • ソニー商品の販売と促進
  • 新規ビジネス開発部門がある
Life-X とは？
  • ネットとデバイスが融合する時代へ向けた，新しい使い方・情報共有の仕方の提案
    • テレビやPSPなどのデバイスを製造しているメーカーならではの展開
Life-X 構築について
  • 更新・参照ともに多いので，分散が大変らしい

Backup Best Practices BoF Tokyo

基本的なバックアップ
  • 前提として
    • binlog と データ本体の HDD は分けてる
    • binlog は on にしておく
  • どこかでフルバックアップをとる
  • binlog とは
    • COMMITのタイミングで変更点まるごと書き出される
    • 各書き込み毎に位置(position)が割り当てられる
    • バックアップ時に master_log_pos をとっておけば，どこまでバックアップしたかがわかる
  1. おおっと！(HDD全損)
  2. HDD新調
  3. バックアップからリストアしても，バックアップ後の更新は反映されない
    • バックアップのポジションがわかるかが重要
  4. mysqlbinlog をつかうと，あるログファイルのあるポジションからの SQL を取り出すことができる
$ mysqlbinlog --start-position=3356424 binlog.000039 | mysql
バックアップの方法
  • バックアップの種類
    • Warm/Hot/Cold
    • 対応ストレージエンジン
    • 論理(Statement-base)/物理(ファイルコピー的)
    • 有償/ç„¡å„Ÿ
  • SQL 分ペース
    • 特定のデータだけをバックアップなど可能
  • mysqldump
    • InnoDB だと SINGLE TRANSACTION にすることで HOT Backup が可能
  • OS のファイルコピー
    • /var/db/mysql/data などをコピーしてバックアップ
  • mysqlhotcopy
    • すべてのテーブルにロックをかけてファイルコピーするスクリプト
    • Memory/InnoDB/NDB 以外はおおむね対応(表領域を使わないエンジン)
  • MySQL Administrator
    • GUI で Warm Backup
  • レプリケーション
    • バックアップスレーブとして
  • スナップショット
  • InnDB Hot Backup
    • InnoDB/MyISAM のみだが HOT バックアップできる有償ソフトウェア
  • Zmanda Recovery Manager
    • GUI でバックアップができる MySQL Enterprise のオプション
  • MySQL Parallel Dump
MySQL 6.0 のデモ
  • 幻の MySQL 5.2.4
    • BACKUP DATABASE [database names] TO 'FILE_PATH';
    • RESTORE FROM 'FILE_PATH';
  • バックアップ処理を Driver でやるか Engine 側でやるのかも議論がある
  • MySQL 6.0.6 では MyISAM ã‚‚ Hot Backup 可能
  • バックアップ中に DDL(ALTER TABLE など) は実行できない
    • MySQL 6.1 では実行可能になる予定
InnoDB Hot Backup vs. mysqldump --single-transaction --master-data
  • InnoDB Hot Backup
    1. ファイルをごっそりコピー
    2. バックアップ開始時点での InnoDB 内部ログの位置を記録
    3. ファイルコピー終了時点での InnoDB 内部ログの位置から，開始〜終了までの redolog を抜き出して一緒にバックアップ
    4. リストアするときは，ファイルコピーをしてから redolog を適用する
  • mysqldump
    • --single-transaction --master-data をつけると，TRANSACTION が走り，その中で SELECT が実行されるのでバックアップ開始時点でのデータであることが保証される．
    • mysqldump -> mysqld -> FileSystem -> mysqld -> mysqldump となり，オーバーヘッドがある
    • RESTORE のときは SQL ã‚’ parse したりするので重い

展示セッションにて

  • SPIDER ENGINE
    • α版だが，内容を聞く限りではかなり期待大
    • GPLで公開なので，実験に使うのはいいかもしれない．