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 – - . , - , , , - .







, , .







, . 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. , :




!



All Articles