ツナワタリマイライフ

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

「実践ハイパフォーマンスMySQL」を読んだ

Infrustructure

はじめに

正確には全部読んでない。MySQLのパフォーマンス問題に悩まされたことがあったので買った。

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

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

全部は読んでないが、内容の復讐に書き留めておく。

1章 MySQLのアーキテクチャと歴史

この章はその名の通り基本的なことなので読むべきだろう。

MySQLの論理的なアーキテクチャはよくみる図だ。

また、トランザクションに関して、ACID特性、ロック、分離レベルの説明もある。RDBMSを扱う上で重要な概念なので何度学んでもよい。

マルチバージョン並行性制御、MVCCについて。ストレージエンジンによって実装は異なるが、行ロック＋ひとひねりとのこと。

InnoDBでは行ごとに、行が作成されたタイミングと、期限切れ（削除された）タイミングを記録し、このイベントが発生したときのシステムバージョン番号を格納する。すなわち、上記2つのイベントが起きるごとにバージョンはインクリメントされることになる。

このバージョンを使って、分離レベルを実現する。

InnoDBストレージエンジンについては、よほどの理由がない限りない限りInnoDBを使うべきだし、他のストレージエンジンを調べるよりInnoDBを詳しく調べたほうがよいと言っている。

MySQLマニュアルの「InnoDBトランザクションモデルとロック」セクションをぜひ読んでおこう、とのこと。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.2 InnoDB のトランザクションモデルおよびロック

2章 MySQLのベンチマーク

本書で語るパフォーマンスを図る上で最も重要なベンチマーク。ベンチマークの方法、考え方が記載されているので必読。

なぜベンチマークをするのか？ハードウェアの変化のため、アプリケーションの変化のため、成長を予測するため。いろいろあるが、いかなるときでもベンチマークをとるようにしておかないと、いざ変化が起きた時に計測できない。

ベンチマーク指標には以下のものがある

まぁもっともわかりやすく使われるのはスループットだろう。

また、誤ったベンチマークの例として、

  • データセット・サイズが実アプリケーションと合っていない
  • マルチユーザーのアプリケーションに対してシングルユーザでのテスト
  • ループを使って同じクエリを実行する
  • リブート直後等で、ウォームアップが加味されていない
  • ベンチマーク時間が短い

などなど、なるべく実際の使用用途に近い形にしてあげないといけない。これはなかなか難しいだろう。

パフォーマンスを定期取得することになるが、必ず自動化しよう。何度も実行されるからだね。

データベースそのものをテストする、単一コンポーネントツールの紹介がされている。

  • mysqlslap
  • MySQL Benchmark Suite(sql-bench)
  • Super Smack
  • Database Test Suite
  • PerconaのTPCC-MySQL
  • SysBench

いろいろあるが私はSysBenchを選択した。mysqlslapは付属してて楽なんだけど、client側が落ちることが多く。。。

本書がSysBenchを推していたこともある。

3章 サーバのパフォーマンスのプロファイリング

この章な流し読みしたが、サーバ・アプリケーション・SQLクエリそれぞれのプロファイリングの方法であり、実際にパフォーマンスが劣化している、うまく性能が出ていないときにとる手段である。

そのような場面になったとき必ず読み返したい章。

4章 スキーマとデータ型の最適化

今回、業務ではクエリを変えることはできないので、この章は読み飛ばした。実際スキーマからアプリケーションを設計するときには一読したい。

5章 インデックスによるパフォーマンスの向上

残念ながらインデックスをどう作るか、まで込み入った世界に入っていないのでこの章も飛ばし。インデックスについてかなり詳しく載っている。

6章 クエリのパフォーマンスの最適化

4章と同じく、クエリは変えられなかったので飛ばし。

7章 MySQLの高度な機能

飛ばし！あることだけ知っておこう！

8章 サーバ設定の最適化

my.cnfで設定できるパラメタの説明および効果についての章で、この章は読みました。

学ぶべきは「最適な設定が見つかるまで設定変更を繰り返すことでサーバを「チューニング」することが期待されている、あるいは期待されていると思っているかもしれない。通常、私たちはほとんどのユーザにそうした方法を勧めない。調査と作業に追われるわりには、期待できる見返りはほんのわずかで、時間を無駄にする可能性がある」

そのあとでの述べているが「チューニング」をしてはならないと言っている。言葉も使うべきではないとも。「設定」「最適化」「調整」がよい。あてずっぽうで値を変化して試してみる、という行為は無意味で効果が薄いし、インターネット上の情報を鵜呑みにしてもいけない。

内部論理と設定の意味を理解したものが、特定の目的に対して変更をすべきだ、というごもっともな内容が語られています。デフォルトでいい感じにしてくれてるから下手にいじるなってことですね。

MySQLがこれほど細かく設定できるようになっていることはかえって弱点である。というのも、実際にはほとんどの設定にデフォルト値が適しており、たいていは設定せずに忘れてしまったほうがよいにもかかわらず、設定に時間をかけるべきであるかのように思えてしまうからだ。

実際に本書が必要と掲げるパラメタは20数個。基本的なものもあるので、「チューニングパラメタ」っぽいものは10もない。このぐらいで十分なんだなーと思う。

  • innodb_buffer_pool_size
  • innodb_log_file_size
  • innodb_file_per_table
  • innodb_flush_method
  • key_buffer_size
  • tmp_table_size
  • max_heap_table_size
  • query_cache_type
  • query_cache_size
  • max_connections
  • thread_cache_size
  • table_cache_size
  • open_files_limit

こんなもんだ。

中でもメモリをどう扱うかが重要だ。

その他安全性、健全性のための設定もある。目を通しておくといい。

とはいえやはり重要なのは

の2つでたいていの問題は解決するし、してはならないことは

  • サーバを「チューニング」してはならない
  • 比率、公式、または「チューニングスクリプト」を設定オプションの基準値として使用してはならない
  • インターネットで見知らぬ人のアドバイスを信用しない
  • SHOW STATUSんカウンタで悪く見える値を探さない

この4つのならないはとても重要で、いいこと書いてると思います。

9章 オペレーティングシステムとハードウェアの最適化

重要ですが、今回OSやハードレイヤを変更するわけではないので飛ばしました。近年はSSDもあるから、そのへん書かれてますね。あとはRAIDとかSANとかNASとか。ストレージを含むハードから選定することになったらこの章を読むべきですね。

10章 レプリケーション

MySQLのレプリケーションを使ってないので飛ばしました。

どんなレプリケーション方法があってどんな風にやるかがかなり詳細に書かれています。

11章 MySQLのスケーリング

規模拡大のしやすさ、スケーラビリティについて。シャーディング、スケールアップ、スケールアウト。負荷分散手法について。ここも飛ばしました。

12章 高可用性

ダウンタイムを最小限にする方法について。SPOFの回避、バックアップ、リストア時間の計測。

同期(双方向)レプリケーションが安心ですね。(MySQL Cluster、Percona XtraDB Cluster)

13章 クラウドでのMySQL

この本が出たのは第3版で2013年、その後ずいぶんクラウドのMySQLも進化してるので、本書執筆当時と状況は代わってきてるでしょうね。

どちらかというと若干クラウドには懐疑的な論調で進んでいる。まぁ、クラウドがなんでも解決してくれるんだ！最強だ！ってのは幻想ですから、そりゃそうだろうけども、

クラウド、特にDBaaSではCPU、メモリ、ディスクが十分でなく、パフォーマンスが十分でない可能性があるのでしっかりベンチをとること。

それに引き換えメンテナンスをしなくてもいいし、自動でスケールアウトすることを考えると十分選択肢に入ると思う。

aws.amazon.com

aws.amazon.com

14章 アプリケーションレベルの最適化

アプリを触れないので飛ばした。アプリ側で見直せることがあるのではないか。キャッシュ。

15章 バックアップとリカバリ

パフォーマンスに閉じない、一般的だけど重要なバックアップとリカバリについて。

面白いのが

バックアップの都市伝説その1：レプリカはバックアップである

間違いも伝搬してしまうのでバックアップじゃないですよね。

論理/物理バックアップ、オンライン/オフラインバックアップについて。

あとはInnoDBのクラッシュリカバリについても。

MySQLのバックアップとリストアをしないひとはいないと思うので全員必読の上で自身のバックアップポリシーの再確認をしたほうがいいですね。

16章 MySQLユーザーのためのツール

MySQL WorkBench便利そうだなーいれてみようかな。

監視ツールがいろいろ紹介されているのも助かる。

  • 監視し、異常を検知
  • メトリクスを計測

の2種類必要だ。

  • Nagios
  • Zabbix
  • Zenoss
  • Hyperic HQ
  • OpenNMS
  • Groundwork Open source

可視化観点だと

おわりに

分厚い本書、今後MySQLで壁にぶつかったときに何らかの形で役に立ってくれるはずである。どのセクションも非常に詳しく記載されていて、心強い。中でも8章の考え方は肝に銘じておこう。ちなみに付録も驚きの充実度。良い本です。職場の本棚に置いておきます。