<Insert Picture Here>
MySQLトラブルシューティング技巧解説
〜ビジネスを支える現場のテクニック〜
日本オラクル株式会社 MySQL Global Business Unit
テクニカルアナリスト 奥野幹也
以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。
また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことは
できません。以下の事項は、マテリアルやコード、機能を提供することをコミットメン
ト（確約）するものではないため、購買決定を行う際の判断材料になさらないで下さ
い。オラクル製品に関して記載されている機能の開発、リリースおよび時期につい
ては、弊社の裁量により決定されます。
OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。
DBAの3つの課題
安定性
パフォーマンス
作業効率
トラブルシューティングが重要
早く
確実に
未然に
SQLがエラーになる
実際にエラーになったSQLを実行してみる。
プログラムのソースコードを見ただけでは分からないことが多い。
一般クエリログやlong_query_time=0にしたスロークエリログが
クエリの特定に役立つ
理想的にはアプリケーションがログを取っておく。
エラーの原因と対処は千差万別
一般クエリログ
MySQLサーバーに対して投げられたリクエストを全て
記録する。
実際に実行されたクエリを見ることができるので、
アプリケーションのデバッグに役立つ。
動的に有効化可能
出力先はファイルまたはテーブル
期待通りの結果が返らない
バグの可能性
他のストレージエンジンを使って試してみる
EXPLAINで実行計画を見る
インデックスヒントを使って実行計画を変更してみる
最適化オプションのON/OFF
問題が起きる最小のデータとクエリを突き止める
そもそもそのSQLは正しいか？
複雑なSQLはシンプルなパーツに分けてみる
テンポラリテーブルを使う
いつから問題が起きているか？
テーブルのメンテナンスやアップグレードはおこなったか
アプリケーションの改修をしたか
<Insert Picture Here>
MySQLサーバー内の文字コード変換
MySQLサーバー
テーブル
セッション
クライアント
①送信する
SQL文に対する
文字コード
③クエリの
実行結果に対する
文字コード
②クエリの実行
に利用する
文字コード
④データを
蓄える際の
文字コード
⑤テーブル名や
カラム名に対する
文字コード
⑥ファイル名を
解決する際の
文字コード
ファイルシステム
出展：エキスパートのための MySQL
［運用＋管理］トラブルシューティングガイド
文字コードを確認する
SHOW [GLOBAL] STATUS LIKE 'char%';
(1) character_set_client
(2) character_set_connection
(3) character_set_results
(4) character_set_server/character_set_database
(5) character_set_system
(6) character_set_filesystem
文字化けの原因と対策
接続用の文字コードは問題ないか？
アプリケーションが期待している文字コードとドライバの文字コードは同じか？
端末の文字コードは問題ないか？
chcp（Windows）/locale（UNIX系OS）
MySQL標準クライアントの場合は—default-character-setを指定。
ただし--skip-character-set-client-handshakeがサーバで設定されていると
無効。（要注意！）
SET NAMES
テーブル内のデータは問題ないか？
latin1でマルチバイト文字を格納してしまった。
正しい文字コードを指定して取り出すと化ける。
いったんbinaryとしてバックアップ。
テーブルの文字コード
カラムごとに文字コードを指定可能
INFORMATION_SCHEMAを利用して調べられる
SELECT * FROM COLUMNS WHERE
CHARACTER_SET_NAME != 'utf8';
SHOW CREATE TABLEよりも手軽
Connector/Jの留意点
文字コードはcharacterEncodingプロパティで指定
指定がない場合には接続先のcharacter_set_serverにより決定。
接続後SET NAMESをドライバが実行。
--skip-character-set-client-handshakeは効かない
characterEncodingが何であれJavaの文字列はucs2に
変換される。
LOAD DATA INFILE/SELECT … INTO OUTFILE
LOAD DATA INFILE
ファイルの文字コードがcharacter_set_databaseになっていることを
期待している
SET character_set_database = charset_name
テーブルの文字コードと同じ場合にはbinaryを指定する
mysqlimportを使う
SELECT … INTO OUTFILE
デフォルトでは文字コード変換しない
LOAD DATAで読むときはbinaryを指定すると良い
SELECT … INTO OUTOFILE 'file_name' CHARACTER SET
レプリケーションの仕組み
更新
更新
バイナリログの種類
ステートメントベース
SQL文を直接記録する
データサイズの効率が良い
非決定性のステートメントは
含めることができない
従来からある実装
行ベース
データそのものを記録する
クエリの種類を問わず対応可
能
行ベースしか対応していない
ストレージエンジンもある
5.1から利用可能
MIXED
ステートメントベースと行ベースを状況に応じて使い分ける
通常はSBR、非決定性のステートメントはRBR
SQLスレッドの停止
原因
スレーブ上のテーブルを更新してしまった。
マスターのデータからリストア
スレーブのクラッシュでポジションが巻き戻ってしまった
マシンがクラッシュした場合に起こりがち
重複キーエラーに
バイナリログ欠損
マスター上で失われた情報はどこにもない！！
全て再セットアップ
一時的なエラーなら
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N;
mysql> START SLAVE;
I/Oスレッドの停止
原因
ネットワークのエラー。
max_allowed_packetが足りない。
ユーザがログイン出来ない。
ユーザのパスワードを変更してしまった？
エラーログを調査。
SHOW SLAVE STATUS
スレーブの状態を確認する
Slave_IO_Running: I/Oスレッドが動作中かどうか
Slave_IO_State: I/Oスレッドのステータス
Slave_SQL_Running: SQLスレッドが動作中かどうか
Master_Log_File: I/Oスレッドのバイナリログファイル名
Read_Master_Log_Pos: I/Oスレッドのポジション
Relay_Master_Log_File: SQLスレッドのバイナリログファイル名
Exec_Master_Log_Pos: SQLスレッドのポジション
Seconds_Behind_Master: 遅延（後述）
Last_Error: 最後に発生したエラー
Last_Errno: 最後に発生したエラーの番号
Skip_Counter: エラーを無視する回数
Replicate_*: フィルタ
SHOW MASTER STATUS
バイナリログに関する情報を表示する
File: 現在更新中のバイナリログファイル名
デフォルトでは hostname.000001 から始まる連番の
ファイル名
Position: 現在のバイナリログポジション
Binlog_Do/Ignore_DB: フィルタ
マスターとスレーブのバイナリログポジションを見比べる
遅延
Seconds_Behind_Masterとは
I/Oスレッドによる遅延+SQLスレッドによる遅延+ズレ
レプリケーション開始時に時計のズレを検出
ネットワークが停止するとI/Oスレッドの遅延は分からない
後から急激な時差を検出する場合がある
レプリケーションハートビート
あくまでも近似値！！
I/Oスレッドの遅延＝ネットワークの問題
SQLスレッドの遅延
SQLの実行に時間がかかる
更新/削除するデータがキャッシュに存在しない
レプリケーションモニター
モニターエージェントを仕込むだけでレプリケーションの
トポロジを認識
細かい設定不要！
マスター/スレーブ、1:N、カスケード、マルチマスター etc
様々な監視項目
死活、遅延、バイナリログポジション、エラー
ひとめで直感的に問題を認識可能
レプリケーション安定化計画
バイナリログの欠損を防ぐ。
sync_binlog=1
テンポラリテーブルは使わない。
テンポラリテーブル作成後にSQLスレッドが停止すると、再セットアップ
が必要になる。
マスターだけを豪華にし過ぎない
マスターとスレーブは同じ量の更新が発生する。
ハードウェア、バッファは同じ程度に設定しておく。
行ベースレプリケーション
クラッシュを誘発するシグナルの種類
SIGSEGV
プログラムが無効な（セグメント境界を超えた）メモリアドレスへアクセスした。
バグの可能性
SIGABRT
プログラムがabort(3)を呼び出した。
アサーション失敗。バグやデータ破壊など。
SIGBUS
無効な物理メモリアドレスへアクセスした。
メモリアライメントの問題やハードウェアの故障など。
メモリアライメントに問題があるのはバグ
メモリアライメントが必要かどうかはCPU次第（SPARCなど）
エラーログ
クラッシュ時にはスタックトレースなどの情報がエラーログに記録される
デフォルトではコアファイルが生成されないので、エラーログが唯一の手が
かりに。
シグナルの種類が判別可能。
クラッシュ後にリカバリが行われているかの判定も重要。
エラーログは超重要
クラッシュ時以外にも何か問題が起きたらまず見る。
実は標準出力をリダイレクトしたもの。
/datadir/hostname.err
/var/log 配下にあることも。
コア解析
GDBなどのデバッグツールを使って行う
コア解析はいわば検死解剖
直接の死因は分かるがそれがどのように引き起こされたか
までは分からないことが多い
データ破壊によるクラッシュ＜なぜデータは壊れたのか？
ソースコードの理解が不可欠
サポートへ依頼して頂きたい。
データ破壊の概要
特にクラッシュ時によく起きる
ファイルシステムキャッシュにデータが残っている間にOSごとダウンした
場合などは危険
データ保護の実装はストレージエンジン次第
トランザクション対応のストレージエンジンは
クラッシュリカバリあり
InnoDB
MySQL Cluster
トランザクション非対応のストレージエンジンは
クラッシュリカバリなし
MyISAM
MyISAM
OSやMySQLサーバーのクラッシュ時にデータが
壊れるかも知れないのは仕様です。
トランザクションに対応していないため。
OLTP系の処理には不向き。
対策
バックアップやレプリケーションなどでデータを複製しておく。
データロストが発生するのは諦める。
REPAIR TABLEコマンドで復旧を試みる。
ただし、確実に直るという保証はない。
InnoDB
原因
クラッシュなどによって簡単には壊れない
ログ（WAL）とダブルライトバッファ
ハードウェアのエラーには耐えられない
ディスク交換後にリストア
バグ
復旧方法
innodb_force_recovery=4 または 6
問題のテーブルまたは全体をmysqldump（6の場合は全体）
テーブルをDROPまたはデータディレクトリ初期化
innodb_fource_recoveryオプションを解除して再起動。
リストア
対策
バックアップやレプリケーションで鉄壁の防御を！
mysqldがメモリ不足で停止する
32bitバージョンのプログラムはもう使わない！！
論理アドレス枯渇によるOut Of Memoryがよく起こる。
RSSが1GB程度でも起こる。
割り当て/解放の繰り返しによりフラグメンテーションが発生するから
VSIZEが大きくなってしまう。
Linuxでは3GBまで、Windowsでは2GBまで。
ulimit
バッファの割り当てすぎ。
特にセッションごとのバッファが大きすぎる場合が多い。tmp_table_size=64M
など。
カーネルの設定など
FreeBSD … kern.maxdsiz
HP-UX … maxdsiz_64bit
OOM Killer
Linux上でメモリが足りなくなった時にプロセスを終了させる。
エラーメッセージがなく突然mysqldが終了している場合に疑って
みるべし。
OOM Killerが発動したことはsyslogに記録が残る
夜間バッチなどでmysqldまたはそれ以外のプロセスが大量にメモリを
消費していないか？
echo -17 > /proc/pid/oom_score_adj
デッドロックは障害ではない！
デッドロック対策
デッドロックは起きるもの。根絶することはできない。
InnoDBはデッドロック検出機能あり
片方のトランザクションを強制的にロールバック
トランザクションをリトライ
発生したら黙ってリトライ。リトライのロジックは必須
レコードへアクセスする順序を工夫する
主キーの順でアクセスするなど
デッドロックが頻発するケースではテーブルロックも有用
Lock wait timeout
ロックを長時間獲得出来なかった時に生じる現象
デッドロックではない
UPDATEやDELETEに時間がかかり、ロックを待っていた他のトラン
ザクションがタイムアウトする
InnoDBのデッドロック検出機能は100%ではない
NDBにはデッドロック検出機能はないので必ずLock-wait-timeoutに
なる。（見極めが難しい）
対策
リトライ！！
大量のレコードを一度に更新する処理を改める
バッチ系の処理はこまめに分けて更新するなど
テーブルロックをかけてから実行する
クエリが遅い
適切なインデックスが使われていない
テーブルスキャンが発生している
複合インデックス
クエリの効率がよくない
サブクエリではなくJOINを
クエリが複雑になるのはテーブル設計がよくない兆候かも？
クエリのチューニング
EXPLAINから実行計画を読み取る
クエリの構造、インデックス、レコードアクセスタイプ、行数
インデックスを付け替える
オプティマイザの挙動を理解する
アルゴリズム：NLJ、BNLJ、BKAJ、ICP、ECPなど
ヒント：使用するインデックスの指定、JOINの順序固定
スロークエリログ
実行に時間がかかったクエリを記録する
long_query_timeで閾値を指定
long_query_time=0で全てのクエリを記録
log_queries_not_using_indexesでインデックスを使っていな
いクエリを全て記録
出力先はログまたはテーブル
log_output=TABLE
ログファイルはmysqldumpslowコマンドで解析
クエリアナライザ
クエリの統計情報を取得
実行時間、実行回数、フェッチした行数など
特定の区間の統計情報を表示可能
負荷が高いクエリが一目瞭然
モニターサーバー内にデータを収集
ログ不要
MySQL ProxyまたはConnectorで情報を収集
グラフとの比較
CPU負荷が上がっている時間帯のグラフをドラッグすると・・・
サーバーが全体的に遅い
システムリソースが不足している兆候かも
CPU、メモリ、I/O
メモリを増やしてI/Oを減らすか高速なディスクに変更してI/Oを
高速化するか
ボトルネックの特定が重要
Sort Aborted
ソートバッファを割り当てるためのメモリが足りない。
テンポラリファイルが用いられる場合にtmpdirの空きが足りない。
トランザクションがロック待ちタイムアウトやデッドロックが理由で
ロールバックしてしまった。
ソート中にスレッドがKILLされた。
ソート中にサーバをシャットダウンした。
対象のテーブルから何らかのエラーが返された。
ソート中のサブクエリを含むクエリを中断してしまった。
接続の問題
Aborted Clients
接続中のクライアントがい
きなり切断した
ネットワークの問題
mysql_close()の呼び忘
れ
タイムアウトなど
Aborted_Connects
クライアントが接続しようと試み
たが失敗した
パスワードが間違ってる
データベースへアクセスする権
限がない
不正アクセスかも？
table is full
ストレージエンジンにより対応が異なる。
MyISAM
ファイルシステムが2GB以上のファイルシステムに対応していな
い。（一部の古いシステムにおいて）
データファイルの内部ポインタサイズが小さすぎる。（デフォルトで
は256TBまで対応）
InnoDB
テーブルスペースを使い切ってしまった。
ファイルシステムに空き領域がなく自動拡張できない。
NDB
IndexMemory、DataMemoryなどを使い切ってしまった。
SHOWコマンド
レプリケーション情報
SHOW SLAVE STATUS
SHOW MASTER STATUS
InnoDBの情報
SHOW ENGINE INNODB STATUS
統計情報
SHOW GLOBAL STATUS
テーブル定義
SHOW CREATE TABLE
INFORMATION_SCHEMA
SHOWコマンドをも凌駕する情報量
バージョンを追うごとに内容が充実
SHOWコマンドでは得られない情報多数
SELECTでアクセス可能
必要な情報を得るため柔軟に絞り込みが可能
JOIN、サブクエリを利用可能
GROUP BYによる統計
CASE句による柔軟な出力
よく使うSELECTにビューを定義可能
作業の効率化
応用はあなた次第！
ログ
エラーログ
問題が起きたらまず見るべき
クライアントへ返されたエラーは記録されない
クライアント側でログを持っておくと便利
一般クエリログ
MySQLサーバーへ送られたリクエストが記録される
アプリケーションのデバッグ用
スロークエリログ
時間がかかったクエリが記録される
バイナリログ
レプリケーションやPITリカバリに利用される
デバッグ版を利用する
デバッグ版の入手方法
バイナリ版ならmysqld-debug
ソースコード
〜 5.1 configure … --with-debug
5.5 〜 cmake … -DWITH_DEBUG
DBUGパッケージによるトレースが可能
my.cnfにてdubugオプションを記述
mysqldのコードパスが一目瞭然
アサーションが通常版よりも多い
OSの情報
統計情報（UNIX系OS）
mpstat、vmstat、iostat、top
最近話題のdstat
/procファイルシステム
システムの情報を一括で取得
Windows
アクセサリ＞システム＞システム情報
systeminfoコマンド
OSX
system_profiler
Oracle Solaris
Oracle Explorer Data Collector
