ツナワタリマイライフ

日常ネタから技術ネタ、音楽ネタまで何でも書きます。

「エキスパートのためのMySQL運用＋管理トラブルシューティングガイド」を読んだ

Infrustructure

はじめに

読んだ

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

業務ではMariaDBを使っているが、正直MariaDBとの差を明確にわかって使っているわけではない。しかし、トラブル時も調査の方針、方法などはMySQLのものも参考になると思い、購入。

ただし、全部は読んでない。自分の中にインデックスをつけるのが目的。

第1章 MySQLの概要

その名の通り概要です。MySQLのことをよく知らないひとでも安心できる、親切設計ですね。不足している方は読んでおいたほうがいいでしょう。

5.7本でもこの手の解説はありましたね。

詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)

詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)

take-she12.hatenablog.com

また、構造的な特徴について興味がある方はさらにこちらを見るといいとのこと

詳解MySQL

プラガブルになっていてストレージエンジンを変更できることや、レプリケーション機能について記載しています。ディレクトリ構造やシステムデータベースの内容も丁寧に解説されています。my.cnfの設定解説と設定例までついてるので、1章だけでも相当詳しい内容ですね。。。

タイトルに運用/管理とある通り、この章でも堅牢に運用するためのTipsが乗っています。

  1. mysql_secure_installation
  2. DNSへの問い合わせを取り除く
  3. (clientが同一ホストにいる場合)TCP/IPを使用しない 4.(クラッシュリカバリのために)バイナリログを有効にする
  4. (オーバヘッドになるような)クエリキャッシュを利用しない
  5. メモリのサイジング
  6. ディスクキャッシュに関する注意点
  7. InnoDBにおけるディスク関連のチューニング

さらにインストール時のよくあるトラブル対策まで。。。見事な1章だ、これ1冊で本になれるぐらいですね、さすが。。。

第2章 開発時のおける問題

文字化け

MySQLの文字化け/文字コード問題は私もMariaDBでぶつかったことがあります。p112の図2-1はよく覚えておきましょう。

         1     2          3,4  5
client ---> session ---> table--->filesystem
       <---         <---      <---
         6
  1. 送信するSQL文の文字コード
  2. クエリの実行に利用する文字コード
  3. データを蓄える際の文字コード
  4. テーブル名やカラム名に対する文字コード
  5. ファイル名を解決する際の文字コード
  6. クエリの実行結果に対する文字コード

それぞれで文字コードを設定可能で、異なる場合はMySQLが変換してくれるようです。しかし、統一したいですね。。。

p126、データがlatin1で格納されてしまっている問題、出会いました。。。

幸か不幸か、latin1文字コードを接続文字コードとテーブルの文字コードの両方で使用している場合、マルチバイト文字をそのまま格納することができてしまいます。(latin1はシングルバイトなので、ちょうどでコードされたような形でデータが格納されてしまいます)

MySQLのデフォルトの文字コードはサーバ側もクライアント側もともにlatin1なので、文字コードの設定が抜けていても、上記のように、問題なく日本語の入出力ができてしまいます。そのため、設定が抜けていることに気づいてもらえない、かわいそうなMySQL Serverが世の中にはたくさん存在していることでしょう。

いるやろなぁ。。。

以下の問題が発生します。

  • 称号順序が狂ってしまう(latin1_swedish_ciを用いて1バイトずつ比較されてしまう)
  • 本来の文字コードを指定して接続すると、文字化けしてしまう

特に後者の問題は、システムの刷新に伴うデータ移行などをする際に発覚することが多いものです。

まさにそれだった。。。

アプリケーションを含めて、1番最初に設定しておくべきですね。

MySQL CLI上のエラーを理解する

MySQL CLI上でのエラー対処の方法です。大前提として、3つのレベルがありますね。

  • ERROR 重大なエラー。SQL文が実行できなかったことを示します
  • WARNING 影響度が大きいエラー。SQL文は実行できたが、何らかの副作用があり、要求を完全に満たすことができなかったことを示します。
  • NOTE 補足的なメッセージ。SQL文の実行には問題ありません。

多分、log_levelの設定値で、errlogにどこまで出すかを設定できるはずですが、SHOW WARNINGSコマンドで直近(直前？)のSQLのwarningが見れるようですね。

おもしろいのが以下

SHOW WARNINGSコマンドを利用してエラーメッセージを表示する際に気をつけなければいけないのは、SHOW WARNINGSコマンドが保持する渓谷の内容は直前に実行したSQL文のものだけだということです。(最悪のパターンは、SHOW WARNINGSのスペルを間違ってしまうことです)

やっぱり直前のみなんですね。

よく、ストレージエンジンのエラーとしてまとめられるのをみますが、perrorコマンドっていうのがあるんですね。はじめて知った。

ERROR 1030(HY000): Got error 139 from storage engine

に対して

shell> perror 139
MySQL error code 139: too big row　（行のサイズが大きすぎるよ！9

エラーコードの意味を教えてくれるんですね。

トランザクション

ACID特性と分離レベルについての解説がまずあります。

そして高度なトランザクション時のトラブル事例が乗っていますね。ロストアップデートとか、クラッシュ時のエラー回復など。

SQLモード

SQLに対して、どのような尺度で受け入れるのか、切り捨てるのか、エラーにするのかを決定する変数群のこと。これがバージョンによってデフォルトが違ったりするからこの概念を事前に知っておくのは重要。

アプリケーションによるエラーハンドリング

アプリケーション(client)側からみた、MySQLがエラーとなったときどのような例外にすればいいかが記載されています。重要だ。。。！

各言語ごとの補足方法、クラスも書いてあって親切だー！今のところアプリと1から書く経験に出会えていませんが、そのときは参照したい。

第3章 MySQLの状態を見る

SHOWコマンドとINFORMATION_SCHEMA

SHOWコマンドとINFORMATION_SCHEMAは同じ情報源から情報を取得してるんですね。

SHOWコマンド

SHOW VARIABLEやSHOW STATUSは普段から使ってます。特にSTATUSはどのようなものがあるか知っていたほうがいいですね。

よく見るのはConnectionでしょうか。handler〜はストレージエンジンに対しての捜査が行われた回数のようです。累積なのかな。定期的に取得することでどのぐらいの書き込み量があるのかがわかりそうです。

connectionについてはMax Used Connectionが最大風速を記録してるので便利ですね。

SHOW TABLE STATUSはテーブルごとの各種情報(例えば文字コードとか)を引き出すのに使えそうです。COLUMNS、INDEXも同様です。

レプリケーションをするしないのかかわらず、binlog出力する場合はSHOW BINARY LOGSはかなり使いますね。レプリケーション関連はMySQLのレプリケーションを使ってないので飛ばしました。

SHOW CONTRIBUTORSってコマンドもあるんだ、面白いね。

INOFORMATION_SCHEMA

すべての情報はこのテーブルにあるようですから、SHOWコマンドでとれる情報も元はこちらにあります。

細かく取得したかったり、SHOWコマンドで望み通りの形式で取得できない場合はこちらからとってくればいいでしょう。

SHOWコマンド廃止論もあるようですが、まだSHOWコマンドでしか取れない情報があるようです。

EXPLAIN

効率の悪いクエリを調べる時に使用します。今の所そのシーンに出くわさないのでスキップ。

プロファイリング

EXPLAINがクエリに実行計画であることに対して、プロファイリングはクエリがどのように(実際に)実行され、どのようなリソースが消費されているかがわかるツールです。

変数profilingで有効にし、クエリを実行したあと、SHOW PROFILEで実行できます。簡単ですね。

これもEXPLAINと合わせ、性能改善等でクエリを調べる時にまた見返したいと思います。

MySQLのログ

大事な6つのログ。復習しておきましょう。

  • エラーログ
  • バイナリログ
  • 一般クエリログ(generallog)
  • スロークエリログ
  • トレースファイル(MySQL server本体をデバッグするときに利用)
  • ストレージエンジンが作成するログファイル
    • 上記のログと混同しない、WAL概念におけるログ。

InnoDBモニタ

これまではMySQLレイヤでの情報収集方法でした。InnoDB(ストレージエンジン)レイヤではより詳しく情報を取得できます。

SHOW INNODB STATUSコマンドを実行したことがない人は、いないでしょう。

はい。

本当によく見るし、負荷があがってきたり、待たされてるクエリが増えるとエラーログに急に吐き出してドキッとするよね。

しかしこの本に詳しく解説があったとは、もっとはやく知っていたかった。次トラブル起きた時にみよう。。。

テーブルモニタ、テーブルスペースモニタも、特定のテーブルで問題が発生した時に役立ちそうですね。

システムの状態を調べる

MySQLではなく、OSレベルでの調査コマンドについて解説されています。親切だ。。。！

システムの状態を知る

  • uname(OSのタイプ)
  • df -h(ディスク情報)
  • ps(プロセス)
  • stat/file/fuser/lsof/strings/nm(ファイルやディレクトリ)

システムリソースを調べる

  • free(空きメモリ)
  • vmstat(メモリ使用情報)
  • top(CPU/メモリ使用状況)
  • ipstat(ディスクI/O状況)
  • netstat(NICごとの統計情報)
  • sar(システムの統計情報を詳細に調べる)

ネットワークを調べる

プロセスのメタデータを格納する/procディレクトリ

  • /proc/cpuinfo(CPU情報)
  • /proc/meminfo(メモリ使用状況)
  • /proc/sys/fs/file-max(システム全体で同時にopenできるファイル数)
  • /proc/sys/fs/file-nr(現在openされているファイル数)
  • /proc/sys/kernel/threads-max(システム全体で同時に作成することができるスレッド数)

上記はシステム全体ですが、これらはプロセスごとに調べることができます。知っておくべきですね。

第4章 DTrace

はじめて知りました。Solaris、Mac OS X、Free BSDで使える、システム追跡ツールのようです。今のところLinuxメインなので使う予定はありませんが、存在は知っておきます。

D言語もはじめて知った。。。！

https://ja.wikipedia.org/wiki/D言語

第5章 運用中に起きる諸問題

レプリケーション

MySQLのレプリケーションに関する問題なので、(現在使ってないこともあり)スキップ。

MySQL単体のトラブルより、レプリケーションのトラブルのほうが多そうです。トラブル事例は役に立ちそう。

また、レプリケーションを堅牢にするためのテクニックは、他のレプリケーション(クラスタ)システムを使っていても同様のことが言えそうです。羅列しておきます。

  • マルチマスターレプリケーションを利用しない(そもそも安全じゃないよ)
  • スレーブを更新しない(当たり前ですね。。。)
  • 適切なモードを選ぶ(レプリケーションモードのこと。行(ROW)ベースかSTATEMENTベースかMIXか。ROWが好ましいかな。
  • テーブルにPRIMARY KEYを付ける(RowBaseのReplicationでは必須)
  • バイナリログを同期する(ディスクとbinlogの同期)
  • ハードウェアと設定、バージョンを合わせる(マスタだけ豪華にするのは好ましくない)
  • スレーブを複数用意する
  • 一度に大量の更新をしない
  • 負荷分散に対応した接続方法を利用する
  • 堅牢なネットワークを利用する(信頼性の高いネットワーク)
  • テンポラリテーブルを使わない
  • 監視する

クラッシュ

MySQLのクラッシュが起きてしまった時の対処について。workaroundとして回避するか、ソースコードを修正するの2択がある。

(OSやハードではなく、)MySQL自身の問題によってmysqldがクラッシュしてしまった場合、その原因の99.999%はMySQLのバグによるものです。

つまり使用方法によって起きることはほぼないってことですかね。

特定にはOSと、MySQLのソースコードレベルの深い知識が必要そうです。

  • シグナル
  • スタックとレース
  • コア解析(GDB)

クラッシュリカバリ

InnoDBのクラッシュリカバリだけ見ておきます。

InnoDBのWALの仕組みの解説から。こういうところが新設だよね。

InnoDB(MySQL)を含む多くのRDBMSではWALの仕組みが搭載されています。ログとテーブルスペース両方に書き込むことによって、少なくともいずれかには書き込まれている状態にすることでクラッシュ時にも自動でリカバリする仕組みです。すばらしい。

さらに堅牢にするためのダブルライトバッファ、MVCCのためのロールバックセグメンとなど、クラッシュリカバリの説明の章ですがInnoDBの基本的な用語と動きが丁寧に解説されています。素晴らしい。

テーブルスペースが壊れていないかの確認のためにダブルライトバッファがあり、リカバリのためにログがあるって感じですかね。

テーブルの破損

万が一破損してしまった場合の復旧の手引き。REPAIR TABLEやinnodb_force_recoveryの紹介があります。

p373にあるバージョンアップよる照合順序の修正が怖いですね。mysql_upgradeか、ALTER TABLEで救えるのか。

性能の低下

まずスロークエリログで遅いクエリを特定したのち、改善計画について説明されています。頼もしい。

  • ハードウェアの増強(スケールアップ)
  • レプリケーションによる負荷分散(スケールアウト)
  • Shardingによる負荷分散
  • memchaced

ハードウェア障害

コンピュータの代表的なパーツが壊れたときの対処法を以下に紹介します。

ネットワーク、CPU、メモリ、ディスクとそれぞれ壊れた時の対処法が載っています。

第6章 堅牢な運用を実現するために

バックアップとリストア

これ超大事ですね。基本的な使い方を押さえておかないと、結構難しいのです。--single-transactionをつけないでロックになっちゃって痛い目にあったこともあります。。。

mysqldumpだと、リストアに時間かかるのが難点。フルダンプ＋binlogによる差分dumpを使ってうまくリストアするのがよさそうです。ロールフォワードリカバリーは便利。もっとはやく知っておきたかった。

ちなみにPerconaのXtra Backupはmysqldumpより早いらしい。いいなあ。表6-1の比較表もうれしいですね！

High Availability

p410 表6-2がクラスタソフトウェアの巨大な比較表です。すげえ。。。HAクラスタにもデータをシェアすつタイプと、シェアしない、シェードナッシングタイプがあっていろいろですね。

ネットワークの冗長化についてもMySQLレベルの話ではないですが記載されています、(Link Aggregation) 確かに、接続ネットワークが切れては話になりませんしね。

あとは使ったことないですがMySQL Clusterですね。確か有償？

セキュリティ

商用で使う場合はセキュリティが必須ですね。不正ログインを防ぐように適切な権限設定をしたり、SSLで暗号化したり、SQLインジェクション対策したり。重要。

アップグレード

これも誰しもが運用中ぶちあたる問題ですね。これももっとはやく見ておきたかった。。。大事な文言があります。

よく「新しいバージョンにはバグ修正によって新たなバグが含まれていることがあるのでアップグレードはしない」というシステム管理者がいますが、これはナンセンスです。もし、新しいバージョンでいくつかのバグが修正されていることがすでにわかっているならば、道のバグの可能性を含んでいる新しいバージョンよりも、既知のバグが確実に修正されている新しいバージョンのほうが安定していると考えるべきです。(p471)

すべてのオープンソース・ソフトウェアに通じる話ですね。

どのようなときにアップグレードすべきかは大事な考え方ですねー。

アップグレードの概要(計画)はぜひ参考にしてスケジュールを組みたいところです。すばらしい。

MySQL(MariaDB)のupgradeが骨が折れる作業であることは、身をもって体験しました。。。

アップグレードをしても安全かどうか？アプリケーションや運用への変更はないかどうか？ということを知るためには、そのアップグレードによってどのような変更が起きるのかを把握する必要があります。そのためにはMySQLの変更履歴に目を通して、現在のバージョンから最新のバージョンに至るまでに行われている「非互換の変更(Incompatible Change )」や「既知の問題(Known Issue)」について目を通さなければなりません。現在のバージョンとアップグレードしたいバージョンに開きがあると、そのような変更点が多く、確認には少し骨が折れるかもしれません。

骨折れる。

アップグレード作業のところには

アップグレード作業の本質はパッケージの入れ替えです。パッケージの入れ替え自体は至極単純な作業ですので5分とかからないでしょう。しかし、作業の安全性を高める目的やバージョン間での仕様変更の影響により、前後でその諸々の作業をしなければなりません。

ダウンタイムを最小限にするための、レプリケーションを利用したアップグレード(p482)では、原則が記載されています。

  • 原則1：低いバージョンのマスターから高いバージョンのスレーブへのレプリケーションは可能。逆は不可。
  • 原則2：メジャーバージョンの差は1つまで

結論は、商用と同じ環境で、upgradeテストとその後のアプリケーションのQAテストをしろ、に限りますね。

ストレージエンジンに非互換があり、SQLでは問題ないにせよ性能が劣化することがあるので、やはり1バージョンずつ、そしてmysqldumpによるdump/restoreが現実的だと思います。1秒も落とせないサービスにMySQLを使うのは難しいですね。

第7章 ソースコードのビルド

ソースコードのビルド方法と、QAテストの方法が乗っています。ビルドをする予定は当面なさそうなのでスキップしておいて、QAテストのところをみておきます。

MySQL Test Frameworkというものが付属されてるんですね。知らなかった。。。いずれにしてもこれはMySQLのコードに手を入れるときに使うものですね。

ベンチマークツールはそうでないひとも使う機会があるでしょう。mysqlslap、sysbench、DBT-2が詳解されています。このへんは実践ハイパフォーマンスMySQLのほうが詳しいでしょう。

実践ハイパフォーマンスMySQL 第3版

実践ハイパフォーマンスMySQL 第3版

take-she12.hatenablog.com

おわりに

さすが奥野先生、インデックスつけるためにパラっと見るだけのつもりが、半日かかった、本当に詳しい、そして頼もしい本です。MySQLはオープンソースで、信頼性の高いデータベースですが、その運用には確実にスキルが必要です。この本をそばに置いておけばトラブル時にかなり役にたつと思います。