MySQLトラブルシューティング技巧解説〜ビジネスを支える現場のテクニック〜

Size: px

Start display at page:

Download "MySQLトラブルシューティング技巧解説〜ビジネスを支える現場のテクニック〜"

こうたとこたに
4 years ago
Views:

<Insert Picture Here> MySQL トラブルシューティング技巧解説

Unit テクニカルアナリスト奥野幹也 2012 年 4 月 3 日 1 Copyright

2 以下の事項は弊社の一般的な製品の方向性に関する概要を説明するものですまた情報提供を唯一の目的とするものでありいかなる契約にも組み込むことはできません以下の事項はマテリアルやコード機能を提供することをコミットメント ( 確約 ) するものではないため購買決定を行う際の判断材料になさらないで下さいオラクル製品に関して記載されている機能の開発リリースおよび時期については弊社の裁量により決定されます Oracle と Java は Oracle Corporation 及びその子会社関連会社の米国及びその他の国における登録商標です文中の社名商品名等は各社の商標または登録商標である場合があります 2 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

6 SQL がエラーになる実際にエラーになった SQL を実行してみるプログラムのソースコードを見ただけでは分からないことが多い一般クエリログや long_query_time=0 にしたスロークエリログがクエリの特定に役立つ理想的にはアプリケーションがログを取っておくエラーの原因と対処は千差万別 6 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

8 期待通りの結果が返らないバグの可能性他のストレージエンジンを使って試してみる EXPLAINで実行計画を見るインデックスヒントを使って実行計画を変更してみる最適化オプションのON/OFF 問題が起きる最小のデータとクエリを突き止めるそもそもその SQL は正しいか? 複雑な SQL はシンプルなパーツに分けてみるテンポラリテーブルを使ういつから問題が起きているか? テーブルのメンテナンスやアップグレードはおこなったかアプリケーションの改修をしたか 8 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

10 MySQL サーバー内の文字コード変換 5 テーブル名やカラム名に対する文字コード 4 データを蓄える際の文字コード 2 クエリの実行に利用する文字コード 1 送信する SQL 文に対する文字コードテーブルセッションクライアント 6 ファイル名を解決する際の MySQL サーバー 3 クエリの実行結果に対する文字コード文字コードファイルシステム出展 : エキスパートのための MySQL [ 運用 + 管理 ] トラブルシューティングガイド 10 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

11 文字コードを確認する 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 11 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

12 文字化けの原因と対策接続用の文字コードは問題ないか? アプリケーションが期待している文字コードとドライバの文字コードは同じか? 端末の文字コードは問題ないか? chcp(windows)/locale(unix 系 OS) MySQL 標準クライアントの場合は default-character-set を指定ただし --skip-character-set-client-handshake がサーバで設定されていると無効 ( 要注意!) SET NAMES テーブル内のデータは問題ないか? latin1 でマルチバイト文字を格納してしまった正しい文字コードを指定して取り出すと化けるいったん binary としてバックアップ 12 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

13 テーブルの文字コードカラムごとに文字コードを指定可能 INFORMATION_SCHEMA を利用して調べられる SELECT * FROM COLUMNS WHERE CHARACTER_SET_NAME!= 'utf8'; SHOW CREATE TABLE よりも手軽 TABLES テーブルには文字コード情報はない 13 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

14 Connector/J の留意点文字コードは characterencoding プロパティで指定指定がない場合には接続先の character_set_server により決定接続後 SET NAMES をドライバが実行 --skip-character-set-client-handshake は効かない characterencoding が何であれ Java の文字列は ucs2 に変換される 14 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

15 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 charset_name Copyright 2012, Oracle and/or its affiliates. All rights reserved.

レプリケーションの仕組み同じデータに同じ更新を適用した結果は同じ更新更新 17 Copyright

18 バイナリログの種類ステートメントベース SQL 文を直接記録するデータサイズの効率が良い非決定性のステートメントは含めることができない従来からある実装行ベースデータそのものを記録するクエリの種類を問わず対応可能行ベースしか対応していないストレージエンジンもある 5.1 から利用可能 MIXED ステートメントベースと行ベースを状況に応じて使い分ける通常は SBR 非決定性のステートメントは RBR 18 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

19 SQL スレッドの停止原因スレーブ上のテーブルを更新してしまったマスターのデータからリストアスレーブのクラッシュでポジションが巻き戻ってしまったマシンがクラッシュした場合に起こりがち重複キーエラーにバイナリログ欠損マスター上で失われた情報はどこにもない!! 全て再セットアップ一時的なエラーなら mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N; mysql> START SLAVE; 19 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

21 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_*: フィルタ 21 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

22 SHOW MASTER STATUS バイナリログに関する情報を表示する File: 現在更新中のバイナリログファイル名デフォルトでは hostname から始まる連番のファイル名 Position: 現在のバイナリログポジション Binlog_Do/Ignore_DB: フィルタマスターとスレーブのバイナリログポジションを見比べる 22 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

23 遅延 Seconds_Behind_Master とは I/Oスレッドによる遅延 +SQLスレッドによる遅延 + ズレレプリケーション開始時に時計のズレを検出ネットワークが停止するとI/Oスレッドの遅延は分からない後から急激な時差を検出する場合があるレプリケーションハートビートあくまでも近似値!! I/O スレッドの遅延 = ネットワークの問題 SQL スレッドの遅延 SQL の実行に時間がかかる更新 / 削除するデータがキャッシュに存在しない 23 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

26 レプリケーション安定化計画バイナリログの欠損を防ぐ sync_binlog=1 テンポラリテーブルは使わないテンポラリテーブル作成後に SQL スレッドが停止すると再セットアップが必要になるマスターだけを豪華にし過ぎないマスターとスレーブは同じ量の更新が発生するハードウェアバッファは同じ程度に設定しておく行ベースレプリケーション 26 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

28 クラッシュを誘発するシグナルの種類 SIGSEGV プログラムが無効な ( セグメント境界を超えた ) メモリアドレスへアクセスしたバグの可能性 SIGABRT プログラムが abort(3) を呼び出したアサーション失敗バグやデータ破壊など SIGBUS 無効な物理メモリアドレスへアクセスしたメモリアライメントの問題やハードウェアの故障などメモリアライメントに問題があるのはバグメモリアライメントが必要かどうかは CPU 次第 (SPARC など ) 28 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

29 エラーログクラッシュ時にはスタックトレースなどの情報がエラーログに記録されるデフォルトではコアファイルが生成されないのでエラーログが唯一の手がかりにシグナルの種類が判別可能クラッシュ後にリカバリが行われているかの判定も重要エラーログは超重要クラッシュ時以外にも何か問題が起きたらまず見る実は標準出力をリダイレクトしたもの /datadir/hostname.err /var/log 配下にあることも 29 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

30 コア解析 GDB などのデバッグツールを使って行うコア解析はいわば検死解剖直接の死因は分かるがそれがどのように引き起こされたかまでは分からないことが多いデータ破壊によるクラッシュ < なぜデータは壊れたのか? ソースコードの理解が不可欠サポートへ依頼して頂きたい 30 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

32 データ破壊の概要特にクラッシュ時によく起きるファイルシステムキャッシュにデータが残っている間にOSごとダウンした場合などは危険データ保護の実装はストレージエンジン次第トランザクション対応のストレージエンジンはクラッシュリカバリあり InnoDB MySQL Cluster トランザクション非対応のストレージエンジンはクラッシュリカバリなし MyISAM 32 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

33 MyISAM OS や MySQL サーバーのクラッシュ時にデータが壊れるかも知れないのは仕様です対策トランザクションに対応していないため OLTP 系の処理には不向きバックアップやレプリケーションなどでデータを複製しておくデータロストが発生するのは諦める REPAIR TABLE コマンドで復旧を試みるただし確実に直るという保証はない 33 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

34 InnoDB 原因クラッシュなどによって簡単には壊れないログ (WAL) とダブルライトバッファハードウェアのエラーには耐えられないディスク交換後にリストアバグ復旧方法 innodb_force_recovery=4 または 6 問題のテーブルまたは全体を mysqldump(6 の場合は全体 ) テーブルを DROP またはデータディレクトリ初期化 innodb_fource_recovery オプションを解除して再起動リストア対策バックアップやレプリケーションで鉄壁の防御を! 34 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

36 mysqld がメモリ不足で停止する 32bit バージョンのプログラムはもう使わない!! 論理アドレス枯渇によるOut Of Memoryがよく起こる RSSが1GB 程度でも起こる割り当て / 解放の繰り返しによりフラグメンテーションが発生するから VSIZEが大きくなってしまう Linux では 3GB まで Windows では 2GB まで ulimit バッファの割り当てすぎ特にセッションごとのバッファが大きすぎる場合が多い tmp_table_size=64m などカーネルの設定など FreeBSD kern.maxdsiz HP-UX maxdsiz_64bit 36 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

37 OOM Killer Linux 上でメモリが足りなくなった時にプロセスを終了させるエラーメッセージがなく突然 mysqld が終了している場合に疑ってみるべし OOM Killer が発動したことは syslog に記録が残る夜間バッチなどで mysqld またはそれ以外のプロセスが大量にメモリを消費していないか? echo -17 > /proc/pid/oom_score_adj 37 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

40 デッドロック対策デッドロックは起きるもの根絶することはできない InnoDBはデッドロック検出機能あり片方のトランザクションを強制的にロールバックトランザクションをリトライ発生したら黙ってリトライリトライのロジックは必須レコードへアクセスする順序を工夫する主キーの順でアクセスするなどデッドロックが頻発するケースではテーブルロックも有用 40 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

41 Lock wait timeout ロックを長時間獲得出来なかった時に生じる現象デッドロックではない UPDATE や DELETE に時間がかかりロックを待っていた他のトランザクションがタイムアウトする InnoDB のデッドロック検出機能は 100% ではない NDB にはデッドロック検出機能はないので必ず Lock-wait-timeout になる ( 見極めが難しい ) 対策リトライ!! 大量のレコードを一度に更新する処理を改めるバッチ系の処理はこまめに分けて更新するなどテーブルロックをかけてから実行する Lock wait timeoutは発生しないが処理が待たされる 41 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

43 クエリが遅い適切なインデックスが使われていないテーブルスキャンが発生している複合インデックスクエリの効率がよくないサブクエリではなく JOIN をクエリが複雑になるのはテーブル設計がよくない兆候かも? クエリのチューニング EXPLAINから実行計画を読み取るクエリの構造インデックスレコードアクセスタイプ行数インデックスを付け替えるオプティマイザの挙動を理解するアルゴリズム :NLJ BNLJ BKAJ ICP ECPなどヒント : 使用するインデックスの指定 JOINの順序固定 43 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

44 スロークエリログ実行に時間がかかったクエリを記録する long_query_time で閾値を指定 long_query_time=0 で全てのクエリを記録 log_queries_not_using_indexes でインデックスを使っていないクエリを全て記録出力先はログまたはテーブル log_output=table ログファイルは mysqldumpslow コマンドで解析 44 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

45 クエリアナライザクエリの統計情報を取得実行時間実行回数フェッチした行数など特定の区間の統計情報を表示可能負荷が高いクエリが一目瞭然モニターサーバー内にデータを収集ログ不要 MySQL Proxy または Connector で情報を収集グラフとの比較 CPU 負荷が上がっている時間帯のグラフをドラッグすると 45 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

50 Sort Aborted ソートバッファを割り当てるためのメモリが足りないテンポラリファイルが用いられる場合に tmpdir の空きが足りないトランザクションがロック待ちタイムアウトやデッドロックが理由でロールバックしてしまったソート中にスレッドが KILL されたソート中にサーバをシャットダウンした対象のテーブルから何らかのエラーが返されたソート中のサブクエリを含むクエリを中断してしまった 50 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

51 接続の問題 Aborted Clients 接続中のクライアントがいきなり切断したネットワークの問題 mysql_close() の呼び忘れタイムアウトなど Aborted_Connects クライアントが接続しようと試みたが失敗したパスワードが間違ってるデータベースへアクセスする権限がない不正アクセスかも? 51 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

52 table is full ストレージエンジンにより対応が異なる MyISAM ファイルシステムが2GB 以上のファイルシステムに対応していない ( 一部の古いシステムにおいて ) データファイルの内部ポインタサイズが小さすぎる ( デフォルトでは256TBまで対応 ) InnoDB テーブルスペースを使い切ってしまったファイルシステムに空き領域がなく自動拡張できない NDB IndexMemory DataMemoryなどを使い切ってしまった ndb_mgm> ALL REPORT MemoryUsage 52 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

54 SHOW コマンドレプリケーション情報 SHOW SLAVE STATUS SHOW MASTER STATUS InnoDB の情報 SHOW ENGINE INNODB STATUS 統計情報 SHOW GLOBAL STATUS テーブル定義 SHOW CREATE TABLE etc Copyright 2012, Oracle and/or its affiliates. All rights reserved.

55 INFORMATION_SCHEMA SHOW コマンドをも凌駕する情報量バージョンを追うごとに内容が充実 SHOWコマンドでは得られない情報多数 SELECT でアクセス可能必要な情報を得るため柔軟に絞り込みが可能 JOIN サブクエリを利用可能 GROUP BYによる統計 CASE 句による柔軟な出力よく使う SELECT にビューを定義可能作業の効率化応用はあなた次第! 55 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

56 ログエラーログ問題が起きたらまず見るべきクライアントへ返されたエラーは記録されないクライアント側でログを持っておくと便利一般クエリログ MySQL サーバーへ送られたリクエストが記録されるアプリケーションのデバッグ用スロークエリログ時間がかかったクエリが記録されるバイナリログレプリケーションや PIT リカバリに利用される 56 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

57 デバッグ版を利用するデバッグ版の入手方法バイナリ版ならmysqld-debug ソースコード 5.1 configure --with-debug 5.5 cmake -DWITH_DEBUG DBUG パッケージによるトレースが可能 my.cnfにてdubugオプションを記述 mysqldのコードパスが一目瞭然アサーションが通常版よりも多い 57 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

58 OS の情報統計情報 (UNIX 系 OS) mpstat vmstat iostat top 最近話題のdstat /proc ファイルシステムシステムの情報を一括で取得 Windows アクセサリ>システム>システム情報 systeminfoコマンド OSX system_profiler Oracle Solaris Oracle Explorer Data Collector Linux sosreport 58 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

59 MySQL Enterprise Monitor MySQL 専用の商用監視ツール 200 を超えるアドバイザルールが総合的にヘルスチェックメール SNMP による通知システムリソースや使用状況をグラフで表示パフォーマンス解析の強い味方最強のモニタリング機能レプリケーションモニタークエリアナライザ 59 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

60 商用サポート解析のプロが責任をもってお答えします!! 自ら解析できる場合でも時間の節約にサブスクリプションの一貫としてサポートを受けられる商用ツールが利用可能 (Enterprise Edition) MySQL Enterprise Monitor MySQL Enterprise Backup ThreadPoolプラグイン認証プラグイン 24 時間 365 日のサポートただし日本語は平日 9 時 5 時のみ ( 緊急時は英語で問い合わせ ) 60 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

62 運用のベストプラクティスバックアップはしっかり計画的に! 本番投入前に必ずアプリケーションのテストを負荷に見合ったハードウェアを選定要件に合致した高可用性構成をチョイススタンドアロン?HA? レプリケーション?MySQL Cluster? ディザスタリカバリは必要か? もしもの時の保険 = サポートセキュリティは万全に監視は抜かりなくトラブルキャパシティ性能全部まとめて MySQL Enterprise Monitor はいかが? メンテナンスは無理のない計画 + 周到な準備を 62 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

63 参考 : 対策とダウンタイムの目安対策要件ダウンタイムの目安バックアップからのリストアバックアップの取得データ量次第だが数時間クラッシュリカバリ HA クラスタのフェイルオーバー MySQL Cluster のフェイルオーバーサーバー 1 台 InnoDB の利用サーバー 2 台共有ストレージサーバー 3 台以上ネットワーク冗長化数分数十分数分数十分数秒レプリケーションのフェイルオーバーサーバー 2 台以上数秒程度 63 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

66 テーブルのメンテナンステーブル定義変更 ALTER TABLE は更新がブロックされる基本はテーブルを全件新しい型のテーブルにコピー古いデータの破棄 InnoDB Plugin/MySQL 5.5 の InnoDB ではインデックス部分だけを再構築 MySQL Cluster はオンラインスキーマ変更に対応 RANGE パーティショニング DELETE を使う場合はこまめに COMMIT 66 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

67 アップグレード失敗しないためには周到な準備を! アップグレード後の動作確認作業時間の見積もり本番環境と同じデータを使って手順を確認レプリケーションの活用でダウンタイム縮小スレーブのバージョン > マスターのバージョンメジャーバージョンの差はひとつまで手順スレーブをひとつずつアップグレード後スレーブのひとつをマスターに昇格旧マスターをアップグレード必要があれば旧マスターへ切り戻し 67 Copyright 2012, Oracle and/or its affiliates. All rights reserved.

<Insert Picture Here> 71 Copyright 2012,

Microsoft PowerPoint - MySQL-backup.ppt

Microsoft PowerPoint - MySQL-backup.ppt MySQL バックアップリカバリ概要オープンソースコンピテンシコンピテンシセンター日本ヒューレットパッカードヒューレットパッカード株式会社 2006 年 12 月 6 日 2006 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice

MySQLトラブルシューティング技巧解説 〜ビジネスを支える現場のテクニック〜

MySQLトラブルシューティング技巧解説〜ビジネスを支える現場のテクニック〜