July 17, 2014 by Bill Karwin
水曜日に「MySQLを開発する上でよく起こる(そして致命的な)ミスをどのように回避するか」をPercona MySQL webinarsで発表した。お見逃の際は、ビデオとスライドを見る為に登録すればまだご覧にいただける。
参加いただいた皆様、そしてとりわけすばらしい質問をしていただきありがたく思っている。セッションの最中は時間の許す限り回答したが、ここで全ての質問に対する完全な回答をする。
Q. ディスクの帯域も無限ではない
まさにその通り！
オンラインセミナーではSELECT *などの列のワイルドカードを使った場合にネットワークの帯域に影響を与えることについてディスカッションを行ったが、これはディスクの動作にも影響を与える。Varchar, Blob, あるいはTextカラムはデータベースの追加のページに格納されることがあり、これらのカラムが不必要に含まれている場合はストレージエンジンに多くのシークおよびページの読込みを不必要に発生させる。
Innodbのstringの格納については、詳細はPeter Zaitsev氏のブログ Blob Storage in Innodbをご覧いただきたい。
Q. 単一クエリで何テーブルまでJOINできるか？最適なJOIN数はいくつか？
MySQLにはクエリに対して63のテーブルしか参照できないという制約がある。この制約が可能なJOIN操作の数および、UNION数を制約する。実際のところ、JOINやUNIONがどのテーブルも参照していなければ、この制約を超えることができ、つまり、1行でその結果得られるテーブルを生成できる。
テーブルをJOINしすぎると(もしくは、同一テーブルに自己結合を複数回すると)、63テーブルの参照に達するはるかに前に、現実のスケール制限に直面するだろう。この現実における制約は、テーブルの長さ、データ型、JOINクエリの書き方のタイプ、そして物理サーバの性能など様々な要素に依存する。これはみなにお伝え出来る決まりきった制限ではない。
単一のクエリでたくさんテーブルを参照する必要があると思った場合、データベースの設計や、クエリの設計に戻って再考する必要があるだろう。 「JOIN数の上限はいくつか？」といったタイプの質問は、キー/バリューテーブル、あるいは実体属性テーブルと呼ばれるテーブルを使おうとしており、行から列に属性をピボットさせようとしているときによく見かける。これは様々な理由からブロークンな設計で、複数回のJOINはそれに伴う1つの問題にすぎない。
Q. 1テーブルにインデックスはいくつまでつくれるか？制限はあるか？最適なインデックス数は？
MySQLの全てのストレージエンジンは最低でも各テーブルに対して、16個はサポートしている。
インデックス数の最適値については、特に気は配らない(最大値16より小さい限りは)。クエリに対して正しいインデックスがはられているようにする。テーブルに対して、例えば8とか10といった恣意的な上限を設けると、必要なインデックスが足りないままクエリを発行することになるので、必要なインデックスを維持するよりも、クエリをそのまま実行する為の不必要なロスの方がコストが高くつく。
とはいえ、クエリの種類が多岐にわたり、全ての起こりうるケースに対して最適なインデックスを持つすべがないケースがある。マルチカラムインデックスをもっているとして、違う順のマルチカラムインデックスを考えると、カラム数nのテーブルに対しては、nの階乗通りのインデックスをとりえる。
Q. 3カラムを持ったテーブルがあるとする: id(int), user_id(int), day(date)。そして同じuser_idが高確率で毎日「存在する」とする。データを"where user_id=some_id"で読取り(とても高いスループットで)、全てのエントリをcronで"where sent_date = '2014-01-01'"で消去している。大体1日あたり600万レコードの削除がありとてもつらい。dayカラムでパーティショニングすると削除は速くなるだろうか？もしそうであるとするとどのくらい速くなり、どのくらいSELECTが遅くなるだろうか？全てのエントリを削除せず、過去の特定の日を例えば" WHERE day = '1 week ago'"のように消したい
日付でのレンジパーティショニングでALTER TABLE…DROP PARTITIONが出来るようになり、指定した日付全てのデータを非常に高速に削除することが出来る。DROP PARTITIONの性能は、各パーティションは物理的には別テーブルであるかのように格納しているため、DROP TABLEに近い性能を発揮する。
“where user_id = ?”での検索はパーティショニングプルーニングを活用できないが、user_idでのインデックスは利用できる。そして古いパーティションを削除すればより小さいテーブルを検索することになるので良いトレードオフとなりえる。
Q. オプティマイザがテーブルスキャンよりインデックスを参照するには、20%の選択性がしきい値となっているが、これはチューニング可能か？
いいえ、これはチューニングできず、クエリオプティマイザの固定的なふるまいである。ある値を検索し、オプティマイザが20%より多くの行が検索対象に含まれると判断した場合、インデックスをバイパスして単にテーブルスキャンをする。
同じ理由で本の目次に本当にありふれた単語は含まない。なぜならば単語が記載されるページリストが長くなりすぎ、本の後ろから、リスト化されたそれぞれのページまでページを前後にめくるのが、単に本を読むより大変になるからだ。
20%の数値はおおよその値であることも、忘れないでいただきたい。お手元の実行結果は変わるかもしれない。これはソースコード内のマジックナンバー的しきい値ではなく、今まで観測した傾向にすぎない。
Q. 人工的なテストデータの生成に関していえば、perl言語では簡単にスクリプトが書けるように思う
はい。1テーブルに関しては簡単にできる。ただし、テーブルはそれぞれ異なり、たくさんのアプリケーションの数百のテーブルに関してテストデータを作らなければいけない。データの値の分布を、テスト毎に変えたいという要望もあるであろう。
テストデータ生成をあるケースに対して書くのは簡単なので、1回きりのタスクとしてそれなりにできる。多くのケースで利用できる汎用的なテストデータの生成ツールを書くのは、もっと骨がおれる。
プレゼンテーションで引用されたURLを、プレゼンテーション資料の中から探さなくても良いようにいただけないだろうか？
オープンソースメッセージキュー:
MySQLのパフォーマンスに関するブログ記事:
オープンソースのテストデータ生成ツール:
ウェブアプリケーションへの負荷ツール:
クエリログリプレイ負荷ツール:
業務ルール実装に関する記事:
- Real-World Rules Engines
- The Enterprise Rules Engine (a warning)
- Drools, the business logic integration platform
Q. MySQLのクエリキャッシュの一番いい使い方を教えて欲しい
1回の書込みに対して、複数回読込まれる場合どんなキャッシュでも有効である。どの程度効果があるか見積もる為、クエリキャッシュの読み書きの平均比率を見積もりたくなる。
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
QCache_hits(クエリキャッシュからクエリの結果が読まれた場合)の値と、QCache_inserts(所望の結果がキャッシュに存在せず、実行の上キャッシュに結果を保存した場合)の値の比率を確認してほしい。1000%以上(すなわちインサートに対して10:1のヒット)だと良いと思う。
比率が低い場合、例えば1:1または100%より低い場合は、クエリキャッシュを無効にすることを検討してほしい。なぜなら、受けられる恩恵よりもコストの方がかかってしまうからである。
これは1つの目安にすぎず、なぜならば先ほど述べた計算は単なる平均にすぎない為である。クエリキャッシュで実行されるクエリが非常に時間がかかるものであれば、キャッシュを利用すればヒット数が少なくても大きな利点となり得る。確実に判断するには、クエリキャッシュを有効化したり無効化したり、キャッシュサイズを変えたりしたときの、みなさんの環境、アプリケーションでの負荷テストの全体的なパフォーマンスを比較するしかない。
Q. インデックスをはりすぎていることがパフォーマンスに影響を与えていることはどのように検知できるだろうか？
インデックスはINSERT, UPDATE, DELETEしたときに同期的にアップデートする必要があるため、インデックス生成を敬遠する人もいれば、インデックス生成は書込みに悪影響を与えるが、読込みによいと一般化する人もいる。これらは両方とも正しくない。 実際のところ、DML操作はインデックスをリアルタイムでアップデートしない。InnoDBはチェンジバッファと呼ばれる機能をもっており、これによりインデックスのアップデートが遅延される。このチェンジバッファは徐々にインデックスにマージされる。このようにしてInnoDBはトラフィックの大きなスパイクをスループットにそれほど影響を与えずに扱うことができる。チェンジバッファがどの程度マージされないでいるかを下記のように確認することが出来る。
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_ibuf_size';
インデックスは書込みに悪影響を与えるというのも正確ではない。UPDATEとDELETE文は、変更を特定の行に対して実施する為に、通常はWHERE句をもっている。この条件は、インデックスを対象行を減らす為に使っており、SELECT句と全く同様である。UPDATE文とDELETE文では、インデックスの利用はより重要となる。なぜならそのようにしなければ、変えようとしてる行をロック出来ていることを保証するため、多くの行をロックする必要が発生する為である。
一般的にいえば、既にあるインデックスの数のみに基づいてインデックスをはるのをさけてはならず、インデックスが発行するクエリで使われるかどうかを確認するようにしてほしい。そして使わないインデックスはドロップしていただきたい。対処について記載した過去のブログ投稿をいくつか紹介しよう。
オンラインセミナに参加していただいたことに重ねてお礼申し上げる。いくつかTipsを紹介しよう。
- 北アメリカとヨーロッパで開催されるPercona Training講習を確認
- Percona LiveのPerconaおよびMySQLのコミュニティーへ参加
- Perconaのオンラインセミナーをみる
