MySQLの10の重要なパフォーマンスのヒント

すべてのリレーショナルデータベースと同様に、MySQLは複雑な獣であることが判明する可能性があり、アプリケーションを急いで停止させ、ビジネスを停止させる可能性があります。

真実は、よくある間違いがほとんどのMySQLパフォーマンスの問題の根底にあるということです。MySQLサーバーが最高速度で動作し、安定した一貫したパフォーマンスを提供できるようにするには、ワークロードの微妙な点や構成トラップによって隠されることが多いこれらの間違いを排除することが重要です。

幸いなことに、多くのMySQLパフォーマンスの問題には同様の解決策があり、MySQLのトラブルシューティングと調整が管理可能なタスクになっています。

MySQLから優れたパフォーマンスを引き出すための10のヒントを次に示します。

MySQLパフォーマンスのヒント1:ワークロードのプロファイル

サーバーがどのように時間を費やしているかを理解する最良の方法は、サーバーのワークロードをプロファイルすることです。ワークロードをプロファイリングすることで、さらに調整するために最もコストのかかるクエリを公開できます。ここで、時間は最も重要なメトリックです。サーバーに対してクエリを発行するときは、完了するまでの時間以外はほとんど気にしないからです。

ワークロードのプロファイルを作成する最良の方法は、MySQL EnterpriseMonitorのクエリアナライザーやPerconaToolkitのpt-query-digestなどのツールを使用することです。これらのツールは、サーバーが実行するクエリをキャプチャし、応答時間の降順で並べ替えられたタスクのテーブルを返します。最も高価で時間のかかるタスクを即座に一番上に表示して、どこに集中するかを確認できます。

ワークロードプロファイリングツールは、同様のクエリをグループ化して、低速のクエリと、高速であるが何度も実行されるクエリを確認できるようにします。

MySQLパフォーマンスのヒント2:4つの基本的なリソースを理解する

データベースサーバーが機能するには、CPU、メモリ、ディスク、ネットワークの4つの基本的なリソースが必要です。これらのいずれかが弱い、不安定である、または過負荷である場合、データベースサーバーのパフォーマンスが低下する可能性が非常に高くなります。

基本的なリソースを理解することは、ハードウェアの選択と問題のトラブルシューティングという2つの特定の領域で重要です。

MySQLのハードウェアを選択するときは、すべての点でパフォーマンスの高いコンポーネントを確認してください。同様に重要なのは、それらを互いに適度にバランスさせることです。多くの場合、組織は高速のCPUとディスクを備えているが、メモリが不足しているサーバーを選択します。場合によっては、特にディスクにバインドされているワークロードでは、メモリを追加することがパフォーマンスを桁違いに向上させる安価な方法です。これは直感に反するように思えるかもしれませんが、多くの場合、サーバーの作業データセットを保持するのに十分なメモリがないため、ディスクが過剰に使用されています。

このバランスのもう1つの良い例は、CPUに関係しています。ほとんどの場合、MySQLは高速CPUでうまく機能します。これは、各クエリが単一のスレッドで実行され、CPU間で並列化できないためです。

トラブルシューティングに関しては、4つのリソースすべてのパフォーマンスと使用率を確認し、パフォーマンスが低いのか、単に多くの作業を要求されているのかを慎重に判断してください。この知識は、問題を迅速に解決するのに役立ちます。

MySQLパフォーマンスのヒント3:MySQLをキューとして使用しないでください

キューおよびキューのようなアクセスパターンは、知らないうちにアプリケーションに侵入する可能性があります。たとえば、特定のワーカープロセスがアクションを実行する前にアイテムを要求できるようにアイテムのステータスを設定すると、無意識のうちにキューが作成されます。電子メールを未送信としてマークし、送信してから、送信済みとしてマークするのが一般的な例です。

キューは、2つの主な理由で問題を引き起こします。キューは、ワークロードをシリアル化してタスクを並行して実行できないようにします。また、多くの場合、仕掛品と、はるか昔に処理されたジョブの履歴データを含むテーブルが作成されます。どちらもアプリケーションにレイテンシを追加し、MySQLにロードします。

MySQLパフォーマンスのヒント4:結果を最も安いものから順にフィルタリングする

MySQLを最適化するための優れた方法は、最初に安価で不正確な作業を行い、次に、結果として得られるより小さなデータセットに対してハードで正確な作業を行うことです。

たとえば、地理的なポイントの特定の半径内で何かを探しているとします。多くのプログラマーのツールボックスの最初のツールは、球の表面に沿った距離を計算するための大円(Haversine)式です。この手法の問題は、数式に多くの三角関数操作が必要であり、CPUに非常に負荷がかかることです。大円計算は実行が遅くなる傾向があり、マシンのCPU使用率が急上昇します。

大円の数式を適用する前に、レコードを合計の小さなサブセットに切り詰め、結果のセットを正確な円にトリミングします。円を含む正方形(正確または不正確)は、これを行う簡単な方法です。そうすれば、広場の外の世界がこれらの高価な三角関数のすべてに見舞われることはありません。

MySQLパフォーマンスのヒント5:2つのスケーラビリティの死の罠を知る

スケーラビリティは、あなたが信じているほど曖昧ではありません。実際、方程式として表されるスケーラビリティの正確な数学的定義があります。これらの方程式は、システムが適切にスケーリングされない理由を浮き彫りにします。

システムのスケーラビリティ特性を表現および定量化するのに便利な定義であるUniversalScalabilityLawを取り上げます。シリアル化とクロストークという2つの基本的なコストの観点からスケーリングの問題を説明します。

シリアル化された何かが発生するために停止しなければならない並列プロセスは、本質的にスケーラビリティが制限されています。同様に、並列プロセスが作業を調整するために常に互いにチャットする必要がある場合、それらは互いに制限します。

シリアル化とクロストークを回避すると、アプリケーションの拡張性が大幅に向上します。これはMySQLの内部に何を変換しますか?さまざまですが、いくつかの例では、行の排他ロックを回避しています。上記のポイント3のキューは、この理由でスケーリングが不十分になる傾向があります。

MySQLパフォーマンスのヒントNo.6:構成にあまり焦点を当てないでください

DBAは、構成の調整に膨大な時間を費やす傾向があります。結果は通常大きな改善ではなく、時には非常にダメージを与えることさえあります。絶えずクラッシュし、メモリが不足し、ワークロードがもう少し激しくなるとパフォーマンスが低下する「最適化された」サーバーをたくさん見ました。

MySQLに同梱されているデフォルトは、万能型ではなく、ひどく時代遅れですが、すべてを構成する必要はありません。基本を正しく理解し、必要な場合にのみ他の設定を変更することをお勧めします。ほとんどの場合、約10個のオプションを正しく設定することで、サーバーのピークパフォーマンスの95%を得ることができます。これが当てはまらないいくつかの状況は、あなたの状況に特有のエッジケースになるでしょう。

ほとんどの場合、サーバーの「調整」ツールは、特定のケースでは意味をなさないガイドラインを提供する傾向があるため、お勧めしません。キャッシュヒット率やメモリ消費式など、危険で不正確なアドバイスがコード化されているものもあります。これらは決して正しくなく、時間が経つにつれてさらに正しくなくなりました。

MySQLパフォーマンスのヒントNo.7:ページネーションクエリに注意してください

ページネーションを行うアプリケーションは、サーバーをひざまずく傾向があります。次のページに移動するためのリンク、これらのアプリケーション通常、グループとソートインデックスを使用し、彼らは採用することができない方法でと、あなたの結果のページを表示するにLIMITしてoffsetその原因作業発生の多くを実行するサーバー、次に、行を破棄します。

多くの場合、最適化はユーザーインターフェイス自体にあります。結果に正確なページ数と各ページへのリンクを個別に表示する代わりに、次のページへのリンクを表示することができます。また、最初のページから離れすぎてページに移動するのを防ぐこともできます。

クエリ側では、LIMITwithを使用する代わりに、offset必要以上に1行を選択できます。ユーザーが「次のページ」リンクをクリックすると、その最後の行を次の結果セットの開始点として指定できます。たとえば、ユーザーが行101から120のページを表示した場合、行121も選択します。次のページをレンダリングするには、121以上の行(制限21)をサーバーに照会します。

MySQLパフォーマンスのヒントNo.8:統計を熱心に保存し、しぶしぶ警告する

監視と警告は不可欠ですが、一般的な監視システムはどうなりますか?誤検知の送信を開始し、システム管理者はノイズを防ぐために電子メールフィルタリングルールを設定します。すぐにあなたの監視システムは完全に役に立たなくなります。

モニタリングについては、指標の取得とアラートの2つの方法で考えるのが好きです。システムで何が変更されたかを把握しようとしているときにそれらを持って喜んでいるので、可能な限りすべてのメトリックをキャプチャして保存することが非常に重要です。いつの日か、奇妙な問題が発生し、グラフをポイントしてサーバーのワークロードの変化を表示する機能が気に入るはずです。

対照的に、あまりにも多くの警告を発する傾向があります。バッファヒット率や1秒あたりに作成される一時テーブルの数などについて警告することがよくあります。問題は、そのような比率に適切なしきい値がないことです。適切なしきい値は、サーバーごとに異なるだけでなく、ワークロードの変化に応じて時間ごとに異なります。

その結果、明確で実行可能な問題を示す条件でのみ、慎重に警告してください。低いバッファヒット率は実用的ではなく、実際の問題を示すものでもありませんが、接続の試行に応答しないサーバーは、解決する必要のある実際の問題です。

MySQLパフォーマンスのヒントNo.9:インデックス作成の3つのルールを学ぶ

インデックスのしくみやサーバーによるインデックスの使用方法について混乱する方法はたくさんあるため、インデックス作成はおそらくデータベースで最も誤解されているトピックです。何が起こっているのかを本当に理解するには、多くの努力が必要です。

インデックスは、適切に設計されている場合、データベースサーバーで3つの重要な目的を果たします。

  1. インデックスを使用すると、サーバーは単一の行ではなく、隣接する行のグループを見つけることができます。多くの人は、インデックスの目的は個々の行を見つけることであると考えていますが、単一の行を見つけるとランダムなディスク操作が発生し、処理が遅くなります。行を1つずつ見つけるよりも、すべてまたはほとんどが興味深い行のグループを見つける方がはるかに優れています。
  2. インデックスを使用すると、サーバーは行を目的の順序で読み取ることにより、並べ替えを回避できます。並べ替えにはコストがかかります。希望する順序で行を読み取る方がはるかに高速です。
  3. インデックスを使用すると、サーバーはインデックスのみからのクエリ全体を満たすことができ、テーブルにアクセスする必要がまったくなくなります。これは、カバーインデックスまたはインデックスのみのクエリとしてさまざまに知られています。

これらの3つの機会を活用するようにインデックスとクエリを設計できれば、クエリを数桁速くすることができます。

MySQLパフォーマンスのヒントNo.10:同僚の専門知識を活用する

一人で行こうとしないでください。あなたが問題について困惑していて、あなたにとって論理的で賢明であると思われることをしているなら、それは素晴らしいことです。これは20回のうち約19回機能します。それ以外の場合は、試行しているソリューションが非常に理にかなっているように見えるため、非常にコストと時間がかかるウサギの穴を掘り下げます。

MySQL関連のリソースのネットワークを構築します。これはツールセットやトラブルシューティングガイドを超えています。メーリングリスト、フォーラム、Q&AWebサイトなどに潜んでいる非常に知識のある人々がいます。会議、見本市、およびローカルユーザーグループイベントは、洞察を得て、ピンチであなたを助けることができる仲間との関係を構築するための貴重な機会を提供します。

これらのヒントを補完するツールをお探しの方は、MySQL用のPercona構成ウィザード、MySQL用のPerconaクエリアドバイザー、およびPercona監視プラグインを確認できます。(注:最初の2つのリンクにアクセスするには、Perconaアカウントを作成する必要があります。無料です。)構成ウィザードを使用すると、付属のサンプルファイルよりも優れた新しいサーバーのベースラインmy.cnfファイルを生成できます。サーバ。クエリアドバイザはSQLを分析して、ページネーションクエリ(No. 7)などの潜在的に悪いパターンを検出するのに役立ちます。Percona Monitoring Pluginsは、統計を熱心に保存し、しぶしぶ警告するのに役立つ監視およびグラフ化プラグインのセットです(No.8)。これらのツールはすべて無料で入手できます。