MySQLの実用的な最適化：スピードを測る

ピーター・ザイツェフ（Percona）

今日はパフォーマンスについてお話します。



MySQLの最適化に正しくアプローチする方法と、この問題に対するいくつかの実用的なアプローチを検討します。 なぜこれが重要だと思うのですか？ 実際、特定の問題が発生した場合、たとえば、「MySQLでキャッシュサイズをどのように設定する必要がありますか？」 このような質問はいつでもGoogleまたはYandexに入力して、それに対する合理的な回答を得ることができます。 しかし、MySQLの一般的、分析および最適化スキームについての回答を得るにはどうすればよいでしょうか？ これははるかに複雑です。









最初に知っておくべきことは、MySQLのパフォーマンスだけが本当に重要ではないということです。 そして、私は、笑顔の後ろから若い男を見ます。 それは重要なことです それが重要でない場合、私たちは何について話しているのですか？」







実際、ユーザー、上司にとって、まず第一に、MySQLを使用し、他のシステムを使用する可能性のあるアプリケーションのパフォーマンスが重要です。 そして、これは、アプリケーションの観点から問題を見ると、他のより広範なアプローチを発見するため、焦点を当てるのが理にかなっています。 場合によってはMySQLが使用されていないか、適切に使用されていない可能性があります。







また、アプリケーションのパフォーマンスが常に重要であることも重要です。 場合によっては、私は人々と話し、彼らは私に言う：「まあ、これは私たちのレポートです。 「そのパフォーマンスは私たちにとって重要ではありません。1、2、3分かかります。すべてが私たちに合っています。」 しかし、あなたが彼らに尋ねるならば：「みんな、もしこの報告書があなたに1ヶ月かかるなら、それはあなたに合っていますか？」 彼らは言う：「もちろん、それは機能しません。」 したがって、生産性は重要です。一部の人々の頭の中では、重要なパフォーマンスとは、ある種の要求をミリ秒または秒単位で実行できることを意味します。 多くの場合、数分や数時間かかるプロセスでもパフォーマンスが重要になるとは考えていません。 なぜこれが重要なのですか？ 多くの場合、何らかの種類のデータベースアルゴリズムを実行し、何らかの種類のクエリを作成すると、その複雑さはデータ量に対して非線形に返される可能性があるためです。 つまり データ量が10倍しか増えておらず、クエリの実行速度が100倍になっている可能性があります。 このようなブレーキはまったく期待していなかったという驚きと驚きを生み出しました。







他に何が重要ですか？ そのパフォーマンスとアプリケーションは、必ずしもMySQLに関連するとは限りません。 これにより、興味深いケースもあります。 クライアントに来て、MySQLサーバーを見ているのを覚えています。 そして、すべてがうまくいっているようで、長いクエリはなく、サーバーの負荷は10％未満です。「みんな、なぜこれが一般にMySQLであると思いますか？」私たちは常にすべてのパフォーマンスの問題に対処しています-それは常にMySQLに関係しているため、そこで掘り下げます。」 時々そうですが、場合によってはそうではありません。



また、MySQLに問題がある場合でも、多くの場合、ソリューションがMySQLに関連していない可能性があることも重要です。 例えば、あなたの多くはスフィンクスのアンドレイ・アクセノフを知っています。 特に初期の彼のビジネスの大部分は、MySQL全文検索を使用するのに長く苦労したMySQLユーザーを救うことでした。 しかし、この象は飛ぶことはありません、彼と一緒に試してはいけません、彼を働かせようとしないでください、彼はスケーリングしません。 この場合、全文検索などのタスクでは、Sphinxを使用する方がより合理的なソリューションです。







そして実際には、MySQLがあまりうまく解決できないさまざまな問題を解決できるようなツールがたくさんあります。 つまり 全文検索、Sphinx Elastic Search-別のオプションがあります。 一部のタスクでは、RedisまたはTarantoolの方がはるかに優れています。 データ分析について話している場合、ここではHadoopまたはVertica、または通常データを並列処理する一部のシステムが適しています。 MySQLは1つのサーバー上の1つのスレッドでのみクエリを実行するため、もちろん大規模なボリュームにはまったく対応していません。







生産性について話すとき、何を考えていますか？ まず、応答時間について考えます。 なんで？ 利己的なユーザーを見ると、これが彼が最初に考えることだからです。 つまり ウェブサイトにアクセスするか、アプリケーションを使用していて、このアプリケーションがすぐに答えてくれたら、それだけで興味があります。 1秒あたりのクエリ、1秒あたりのリクエスト数、およびユーザーのその他のゴミは少し興味があります。







つまり まず、アプリケーションを調べて、アプリケーションごとに異なるタスクごとに異なる応答時間でユーザーに応答するようにします。 0.5秒の応答時間が良好であるという明確な数字はなく、それよりも大きいものはすべて不良です。



しかし、実際のアプリケーションについて話している場合、多くの場合、応答時間だけでなく他のことも考慮します。 最初の質問は、この応答時間を安定させることです。 つまり 非常に多くの場合、問題は応答時間が常に悪いということではなく、システムまたは外部で何かが発生し、それが悪化することがあるということです。 たとえば、多くの場合、すべてが私にとってうまくいくと不満を言う人がいますが、バックアップを開始すると、すべてが私にとって悪いものになり始めます。 そのようなことに従う必要があります。 または、ダウンロードの急増などのいくつかの要素と接続することができます。つまり、ウェブサイトが有名なテレビ番組で表示されたか、他の誰かがそれを言及したため、トラフィックが流れたため、システムは対処できません。



パフォーマンスについてよく尋ねる次の質問は、スケーラビリティです。 多くのアプリケーションは、かなり小さなシステムから始まります。 少量のデータ、少量のユーザー、彼らは成長する計画を持っています。 これらのアルゴリズム、要求、設計、使用するスキームが現在機能しているだけでなく、将来のアプリケーションでも機能していることが重要です。 また、現在のデータ量に関して、現在最も最適なアプローチはいくつかありますが、それらはスケーリングされません。



3番目のポイントは効率性であり、これも重要です。 リソースの使用という観点と、このアーキテクチャの作成に必要な人々の観点の両方の効率。 たとえば、Hadoopの興味深いオプションです。 現在、Hadoopはすでに優れていますが、伝統的に、Hadoopが始まったばかりのときは、Hadoopは何千ものノードでスケルトンでした。 しかし、たとえばMySQLとHadoopのパフォーマンスを比較すると、1つのノードのMySQLは10のHadoopと同じことができます。 また、データ量が十分に少ない場合、MySQLを使用する方がはるかに効率的であることは明らかです。 ただし、1つのサーバーに干渉しないものが必要な場合は、Hadoopの観点から、1,000または10,000ノードを起動できます。 MySQLでこれを行うことはできません。







スケーラビリティの観点から、負荷、データのサイズについてはすでに説明しましたが、インフラストラクチャの観点からのスケーラビリティも重要です。 ここで言及する価値があるものは何ですか？ これは、作業モードに応じて、1つまたは別のインフラストラクチャを使用できる場合があります。 たとえば、多くのクラウドネットワークでは、それほど簡単にスケールアップできません。 「64コア、1 TBのメモリ、4つの非常に高速なフラッシュカードを搭載したサーバーを設置して、10 TBのローカルフラッシュを受信できるようにしたい」 データセンターで働いていて、インフラストラクチャを選択できる場合は、それを入手できます。そのようなサーバーを40 GBイーサネットまたは複数のイーサネットで接続することもできます。 クラウドで作業する場合は、作業する必要がある完全に異なる制限がある場合があります。







よく遭遇する次のポイント。 実際のアプリケーションを開発するとき、生産性が重要になることがよくありますが、これが私たちが注意しなければならない唯一のものではありません。 たとえば、セキュリティ。 通常、セキュリティ、トラフィック暗号化はパフォーマンスを改善しませんが、それどころか、これらは我慢しなければならないパフォーマンスを低下させる可能性があります。



管理性も別のポイントです。 つまり 多くの場合、最適化されたコードと構造-あなたは彼のイチジクを後で理解するでしょう。 多くの場合、これによりコードが複雑になりすぎると、パフォーマンスを最適化したくないということがわかります。 また、他にも考慮すべきことがあります。 たとえば、多くの場合、データの変更をログに記録する必要がある監査のニーズがある場合があります。これにより、パフォーマンスの点で何かを犠牲にする追加機能が追加されます。







言及する価値がある次のポイント。 それは生産性が無限に改善できることだということです。 可能な限り最適化されたシステムはなく、多くのお金と時間を費やして、生産性を少なくとも数パーセント向上させることはまだ不可能です。 これは、いつ停止するかを常に知る必要があることを意味します。そうしないと、リソースを完全に無駄にします。 つまり、必要な効率の種類、システムパフォーマンスなどを何らかの方法で決定する必要があります。 そして、それに達したら、やめることができます。







次の瞬間。 すでにMySQLに渡しています。 システムの応答時間が重要であると述べました。 次に、一般にMySQLがユーザーに対して行うことを見てみましょう。 また、非常に簡単になります。MySQLのリクエストを実行し、選択、更新、挿入し、しばらくしてから応答します。 また、MySQLのパフォーマンスに関連するすべてのことは、クエリに対するこの応答によって特徴付けられます。







当然のことながら、さまざまな負荷、さまざまなデータ量などがあります。 しかし、これらの要求を見て、応答時間を見て、最適化の観点からこれに焦点を当てると、取得できる結果が得られます。



MySQLの最適化について話しているのはいつですか？ このスライドに載っていないことを1つ言っておく価値があるでしょう。 それは、最適な質問が何かを最適化することであるという事実に関するものです-これはそれを行うことではありません。 明らかに、これは誰からもよく聞かれますが、MySQLの観点から興味深いのは、ほとんどの人に来たら、アプリケーションを取得し、MySQLログをオンにしてページをロードすることです。 ほとんどの場合、繰り返されるいくつかのクエリが見つかります。 2番目の要求は役に立たないため、簡単に削除できることを知っています。 多くの場合、データベースからデータが要求され、アプリケーションで使用されていないことがわかります。 しかし、なぜですか？ -「まあ、ここで、このクラスはそこで初期化されます。万が一のためにデータベースからすべてを持ち上げますが、実際には使用しません。」 多くの場合、アプリケーションでは、不要なヒープヒープが作成されます。 しかし、最適化に焦点を当てるとき、私たちはしばしば何に取り組んでいますか？ 最初の非常に簡単な瞬間、質問をより速く実行したい場合、他の瞬間、より少ないリソース、たとえばより少ないメモリを使用したい場合、または圧縮の助けを借りて、システムを最適化してディスク領域をより少なくしたい場合。 または、ハードウェア、データサイズなどの点でスケーラビリティが向上しています。







最適化と一般的なアプローチについて話す場合、多くの場合2つのことを検討します。 1つはトランザクションの最適化です。 たとえば、アプリケーションがあり、誰かが次のように言っています。「ご存知のように、検索機能またはアーカイブの速度が低下します。必要以上に動作が遅くなります。」 この関数、それがMySQLで行うクエリを具体的に見て、最適化することができます。 別の選択肢は、アプリケーション全体を最適化するということです。 次に、MySQLに到達するすべてのクエリを調べ、どのクエリが最も遅く、どのリソースを最も多く使用するかを確認し、それらを最適化すると、システムが一般的に速く動作することは明らかです。 しかし同時に、2番目のアプローチを使用する場合、ブレーキングプロセスがどれだけ最適化されるかについての保証はありません。なぜなら、それは多くの負荷を作成しない可能性があるからです。これは、おそらく上司が見ている何らかの分析です1日に1回、負荷の0.01％ですが、それでも非常に重要です。







また、一般的な最適化の観点から、リクエストを確認し、それらに優先順位を付け、さらに最適化することは明らかです。







クエリに関して。 私が言ったように、最初の質問はクエリの観点からです：これらのクエリを避けることができますか？ 多くの場合、一部のクエリは役に立たないためです。 2番目の質問は、これらのクエリを変更して、作業量を減らすことができるかどうかです。 less workはどういう意味ですか？ これは、スキャンする行数が少ないこと、ディスクから読み取るデータが少ないことなどを意味します。 -これらすべてを、単純な単語を「作業」と呼びます。



どのような例がありますか？ インデックスなしで実行するクエリがあり、多くの作業を行います。 インデックスを追加しました-クエリの動作が少なくなります。 インデックスが追加されたり、MySQLの設定が変更されるほど単純な場合。 それ以外の場合、より複雑なものになる可能性があります。たとえば、クエリが迅速に実行されるようにインデックスを追加できない貧しいデータスキームがある場合があります。 次に、クエリインデックスだけでなく、データスキームを変更する必要があります。 キャッシングなど、他のアプローチを適用する必要がある場合があります。 データをキャッシュすることができ、データを毎秒何回もシャベルする代わりに、何らかのサマリーテーブル、つまりすべての結果を生成するテーブルを作成できます。







次の最適化の質問：何が重要ですか？ まず、クエリの平均実行時間だけを調べるべきではありません。 リクエストの平均実行時間が問題になることはめったにないので、まず第一に、問題はいくつかから始まります。極端なことではなく、その条件に近いと言いたいです。 たとえば、Flickr（比較的古いが、写真の大規模なホスティング会社）の人々が、APIを介して友人が100万枚の写真をアップロードしたときに問題が発生したと言ったことを覚えています。 1,000、100枚の画像のカテゴリでは、100万枚の画像が機能しなくなったため、通常のユーザーで機能したものが正常であることは明らかです。 平均的なリクエスト、平均的なユーザーの観点からは、これは思われないでしょう。



次のポイントは、いくつかの問題が周期的に発生するため、時間の経過に伴う傾向も確認したいということです。これは、毎月の請求が原因であるか、週に一度古いデータを削除することが原因であり、問題。



繰り返しになりますが、リクエストを見るときは、将来について考える必要があります。 特に開発システムで。 開発者は、テストのために構成内のいくつかの小さなデータベースでクエリをテストし、MySQLで説明をあまり読みません。 この場合、リクエストが本番用にレイアウトされるか、少し遅れて、データが急激に増加し始めると、これらすべてが壊れます。 説明を読んで、これらのクエリが将来どのように機能するかについて考えることを学ぶ必要があります。







次の瞬間。 使用するクエリを減らしたいことは明らかです。 同じ数の行を分析しても、使用できるクエリが少ないほど良いです。 リクエストごとにネットワーク応答時間を追加するため、リクエストの解析、権限のチェックなどのためのオーバーヘッドが追加されます。 また、できるだけ少ないデータを読み取り、変更したいと考えています。 つまり たとえば、制限10の要求を満たすことができる場合、これはすべてのデータを読み取り、アプリケーションの最初の数行を読み取り、残りを破棄するよりもはるかに優れています。



同じことは、データベース内の列、アプリケーションが実際に使用する列のみを読み取る方が良いということです。 これらの場合、MySQLは他の追加の最適化を使用して、クエリをより効率的に実行できるためです。



さらに。 クエリの実行中に処理できるデータが少ないほど優れています。 繰り返しますが、各ユーザーに対して毎回何らかの複雑な集計を行う重いクエリは、大規模な集計にはあまり適していません。キャッシュ付きのテーブルとして作成する方法などを確認することをお勧めします。



何を見ることができますか？ どのような興味深い指標がありますか？ 1つ目は、クライアントに送信した量に関してMySQLが分析したデータの量を確認できることです。 このメトリックは、MySQLから簡単に取得できます。 たとえば、スロークエリログの各クエリでは、データはRows_sentおよびRows_examinedです。 それらの間の関係は、私たちが見たいものです。 比率が1〜10の場合、アプリケーション全体は通常最適化されています。 MySQLから送信する各行で100、さらには1,000を処理する場合、おそらく最適化の機会が多くあります。



次のポイントは、アプリケーションが使用した量に関して、MySQLから実際に送信したデータ量を確認することです。 ここで、残念ながら、MySQLがデータを提供したため、MySQLが提供するメトリックはありません。 しかし、多くの場合、少なくとも多くのデータを返す最も深刻なクエリについては、自分で確認できます。 つまり アプリケーションごとに100万行を読む場合、慎重に検討する必要がありますが、すべてそこで使用していますか？ 1ページに100万個の要素が表示されることはほとんどありません。 たぶんそれらは何らかの形で集約されており、この場合、データベースでこの作業を行う方が良い場合がよくあります。







次の瞬間。 既に述べたように、単にインデックスを追加するだけでクエリを最適化できるとは限らないため、多くの場合、スキーマをより劇的に変更する必要があるため、回路の最適化とクエリの最適化を一緒に見るのが理にかなっています。 そして、2つのことがあります。

1. これらは小さなスキームの最適化です-インデックスを追加し、列のタイプなどを変更し、MySQLのストレージを変更します。
2. 設計とアーキテクチャ全体の大きな変更も必要になることがありますが、通常ははるかに深刻な計画が必要です。 つまり 回路の完全なリエンジニアリングを行う場合、かなりの時間がかかります。

. , . .



. , , . , , IR , «» , , , . , . , , . , Partitioning & Sharding, , .. Google .







, – , , , MySQL-, MySQL-. .







, , , ? -, , , . MySQL, , , - , , - – , .



, , - , MySQL. , .



, , , , . , , , , , , «, , MySQL » ..



, , .







. , , - , , , , , .







? :







. , – Intel. C MySQL , . ? MySQL , , , , . . turboboost, , , -, . , turboboost, , – , .







. , . , , . – , . - . , . , , , , , , , , , , - . , , , , .



:







. つまり – RAID, , – FusionIO. ? , , .. , 5 . , . : working set , . - , , , . , , , .







? , . , , , MySQL OLT , ??, .. , , .







MySQL- , , , MySQL . , . , , , , , MySQL- application- . (network hops), . , , application- , , , – , , « «» - ». – , , .







. MySQL Linux. - MySQL - ? FreeBSD, Windows server. MySQL - Linux. , , , . , , , , , , – , , .







Linux. , MySQL- . , - Google, Facebook – 3-5% , , , . – , , EXT4 XFS. ,

, . .







– MySQL. ? MySQL . , .. , , MySQL , , , MySQL. MySQL 5.7, . , , , .. MySQL, , , Percona Server 5.7, .







MySQL-. , MySQL- . つまり MySQL . , : «-! 400 ». , , 5-10-20, ,

. -, .







, , Google, , .







– .







:

• -, , , , , . つまり , , -, , , , . .
  
  
  
  
• , , , , , , - . , - . , - , , MySQL , , , . - , , , , , , , , , - , , Linux - . , , .

. , , – , , MySQL – - . , - , , , - .







, , .







, . MySQL performance Schema Graphite.



– http://bit.ly/1KQSNWC







, , MySQL, , - , , , , , , 10 .











, . . , .











– http://www.meetup.com/moscowmysql/







– http://bit.ly/PL16Call

» pz@percona.com

— HighLoad++ .



" - " . 16 , 9. , :

• Postgres vs Mongo / (PostgreSQL Professional);
• , , : / (Percona);
• MySQL: / (Percona);
• highload / .

!