MySQLサーバ内の「イベント」ごとの処理時間を記録 【データ型】 ・ソート処理用のメモリサイズ ・SQLチューニング(個別最適化) ・桁数の多くない数値型に対してはtinyint,smallint,mediumintなどを利用する 性能統計情報の仕組み 2.JOINの工夫 必ずしも正確性・信頼性等を保証するものではありません。 パフォーマンススキーマをより便利に使うためのビュー、プロシージャ、ファンクションのセット ・joinで利用する列は同じデータ型にする 統計情報が変わることで実行計画が変わることは、データベースとしては柔軟な判断ができるわけです。 しかし、オプティマイザがどうしても最適な判断をしてくれない場合に、SPM(SQL Plan Management)を利用して 実行計画を固定する 方法があります。 ・増やしすぎるとサーバメモリを多く消費するためハードウェアのスペックと相談 ・デフォルトは9, query_cache_size 最近遊びでテーブル作成をしたりしていたのでこの辺りの情報をしっかり自分でも触ってパフォーマンスの改善を自身で感じられるようになっていきたいです。, Cloud基板でアクセスログ解析を行いユーザのサイト回遊情報等を分析しています。 ★自身の環境のメモリサイズを鑑みて有効に使えているか確認する 価格:1,800円+税  ⇒全然使っていない・・・スレッド/クエリキャッシュを使用してみる Copyright © 2004-2020 Impress Corporation. ・テーブル、インデックス、スキーマの統計、レイテンシ、待ち時間, performance_schema/sys_schemaで実行できるクエリサンプル, 上記にあげた確認方法の中でもDBサーバ設定にボトルネックがある場合、下記の値を参照・編集することで解決につながる可能性が高い。, max_connections

また下記コマンドにても参照・編集が可能。, 今回はパフォーマンスに焦点を置いているため、性能統計情報分析のために用意されているパフォーマンススキーマ/sysスキーマにフォーカスする。, ・パフォーマンス・スキーマ mysqlでのsqlチューニングについて(explainの見方) 検証:パーティショニングテーブルの挙動
・複数列貼ることで参照速度を改善することができる EXPLAINは、SQLの実行計画に関する情報を取得するためのステートメントです。実行計画とは「どのインデックスを使って(あるいはインデックスを使わずにテーブルスキャンで)クエリーを処理するか」をMySQLが判断した結果のことです。「インデックスはちゃんと使われているだろうか」「インデックスでどこまでクエリーを効率的に処理できているだろうか」という疑問が湧いた時には、「とりあえずEXPLAINで」となりますよね。, EXPLAINのマニュアルはこちらに、EXPLAIN の出力結果のカラムの意味についてはこちらに記載があります。, EXPLAINはMySQLのオプティマイザーがどの実行計画を選んだかを表示させるステートメントです。possible_keysからは「MySQLはこのクエリーに対してsome_indexまたはanother_indexが使えると判断した」、keyと key_lenからは「実際に使ったのはanother_indexで利用したのキー長さは34バイト」、rowsからは「実行計画上ではこのクエリーは9026812(約900万)行を検査する」、Extra: Using indexからは「テーブルそのものからデータを読み取らず、インデックスだけから読み取るデータで完結する」といったことがEXPLAINの出力結果から読み取れます。, しかし、another_indexが本当にこのクエリーにとって最良の選択なのかはEXPLAINから読み取ることはできません。ひょっとしたらsome_indexの方が速い可能性は十分あります。あるいはpossible_keysに表示されていない(MySQLが「利用できない」と判断した)インデックスの方が効率が良い可能性もないわけではありません(体感ではほぼないが実際にそのようなケースも存在する)。, また、実行計画上は約900万行を検査することになっていますが、「実際に何行検査したのか」は読み取れません。統計情報と実際のデータの分布に乖離がある場合、この値もまた乖離することになります。最後に、これは「EXPLAINを実行した時点で実行計画がこのように選択された」という情報であり、今後統計情報の変化により変更される可能性があります。, 泥臭い方法ですが、実際に実行してみるのが一番です。USE INDEXおよびIGNORE INDEX構文を使うことで、そのクエリーに利用するインデックスを指定できます。, USE INDEXはpossible_keysの値を書き換える(上書きする)ためのキーワードです。インデックスはコンマ区切りで複数指定可能で「USE INDEXで指定したインデックスの中から利用するインデックスを決定せよ」というような意味合いになります(通常は指定するインデックスが1つで「このインデックスを利用してクエリーを処理せよ」という意味合いで利用するキーワード)。, IGNORE INDEXはその逆で、MySQLが選んだpossible_keysから特定のインデックスを除外します。「possible_keysの選定はMySQLに任せるが、IGNORE INDEXで指定したインデックスは利用してはいけない」というような意味合いになります(よってテーブル上の全てのインデックスをIGNORE INDEXに指定すると強制的にテーブルスキャンにできる)。possible_keysが3つ以上ある場合で、MySQLが次にどのインデックスを選ぼうとするのかを知りたい時などに利用できます。, 上記の例ではUSE INDEX(some_index)でsome_indexを選ばせた場合に1割程度遅くなったということで、「MySQLの選んだanother_indexは正しかった」ということが判りました。実際に実行して試してみる場合、(元のクエリーも試してみるクエリーも)必ずクエリーを複数回実行することを忘れないでください。, クエリーの実行速度はバッファプールに大きく依存します。多くの場合、1回目のクエリーはバッファプールが温まっておらず非常に低速です。通常のトラフィックで多く利用されているインデックスは(それが正しいものであれ間違っているものであれ)どの時間帯でもバッファプールに多く乗っている可能性が高く、1回目のクエリー同士を比較してしまうと結果はバッファプールの偏りに依存することがあるからです。, そのクエリーがどれだけ行を読み込んだのかはHandler_%ステータス変数で確認できます。ステータス変数はSHOW STATUSステートメントで参照できるMySQL内部のステータスです(多くはカウンター)。ステータス変数の多くは「セッションスコープ(接続中のスレッドのみのステータス変数)」と「グローバルスコープ(MySQLの起動から現在まで全てのステータス変数の累計)」があり、それぞれSHOW SESSION STATUS,SHOW GLOBAL STATUSステートメントで表示できます(SESSIONまたはGLOBALキーワードを省略した場合はセッションスコープの値が出力される)。, ステータス変数には多くの種類がありますが、ここではHandler_で始まるステータス変数を確認しましょう。, FLUSH STATUSは「セッションスコープのステータス変数をクリアする(クリアされないものもある)」ステートメントです。まれに「グローバルスコープでステータス変数をクリアする」と誤解されているケースがありますが、セッションスコープ限定です。余計な値が混じらないようにSHOW SESSION STATUSの前に実行してみました。各ステータス変数の詳細はリファレンスマニュアルに説明を譲りますが、ここからはインデックスを利用した行の読み取りを意味するHandler_read_nextがおよそ48万回コールされていることが分かります。統計情報をもとにオプティマイザーが「行の検査が必要」と判断した行数は90万行なので、見積もりと実際の間には40万行の乖離があったことになります。, このような事態はなぜ発生するのでしょうか。端的には統計情報が「サンプリング値」をもとに作成されることが原因です。オプティマイザーは統計情報をもとに実行計画を計算しますので、入力値となる統計情報(=サンプリング値)が間違っている(実際のデータの分布と著しく乖離している)場合、出力である実行計画もまた間違っているものとなる可能性があります。「統計情報が間違っている」といえばANALYZE TABLEステートメントです。とりあえず実行してみましょう。, まず 1. 下記が主にデータベースのパフォーマンスを測定するための指標となっている。, ・スループット の通り、インデックスのサイズが大きくなれば大きくなるほどInnoDBの統計情報の誤差は大きくなります。MySQL 5.6とそれ以降では、1インデックスあたりのサンプリングページ数をinnodb_stats_persistent_sample_pagesオプションで指定、またはCREATE TABLEやALTER TABLEでテーブルごとに指定できるようになったためこれを大きくすることも手ですが、サンプリングのページ数が増えれば統計情報の再作成処理も重くなりますので、最適な値を見つける必要があるでしょう。, Handler_%ステータス変数とEXPLAINのrowsが大きく乖離している場合、「ベースとなる統計情報が間違っているためオプティマイザーが導き出した実行計画もまた間違っている」可能性があることを考慮してください(ただし、オプティマイザーの精度が上がっても同じインデックスを利用している限りクエリーそのものの速度は向上しないことに注意)。, EXPLAINの見方を説明している時によく聞かれる質問として「Extraに"Using index"(ほかにも"Using intersect"や"Using temporary"など)が出ていますがこれは直した方が良いですか?」というものがあります。正直これはケースバイケースで、全てのケースを説明するわけにはいきません。まずはマニュアルのEXPLAINの追加情報を参照してください。意味的なものはここにほぼ網羅されています。Extra列の出力しうる表示はたくさんありますが、ここでは比較的よく目にする3つに絞って説明したいと思います。, "Using filesort"は、行のフェッチと評価のあとに追加でクイックソートが発生していることを示します。この時のクエリーの処理シーケンスは以下のようになっています。, MySQLのソートは(filesortと出力されているが、必ずしもテンポラリーファイルを使用するとは限らない)クイックソートです。クイックソートの平均計算時間が示す通り、ソート処理はソート対象の行が多くなれば多くなるほど(線形以上に)遅くなっていきます。また、インデックスを利用したソートの無効化(インデックスが既にソート済みの状態で並べられているため、追加のソートが必要ない状態)はLIMIT句での最適化が効きますが、クイックソートが実行される場合にはこの最適化が効きません。WHERE句で絞り込んだ結果が十分小さい場合はこれが出力されても特に問題にはならないでしょう。絞り込んだ結果がどんどん大きくなる(例えばユーザーコンテンツなどは時間経過とともにどんどん増えていくのが常)場合は注意が必要です。, MySQLのインデックスはほぼB+Treeです。MySQLのB+Treeインデックスのリーフには「テーブル内での行の位置」が記録されています(MyISAMであれば.MYDファイルの先頭からオフセットバイト数、InnoDBであればクラスターインデックスの値が記録されている)。そのため、インデックスを利用した行フェッチを行う際は、以下の3ステップで行われます。, Using indexが示すのはインデックス上に書かれた情報だけで(インデックスは「ソート済みのデータの複製(サブセット)」でありインデックスを作成したカラムの値を含む)、要求された情報の取り出しが終了したため 2. explainステートメントとは explainは、sqlの実行計画に関する情報を取得するためのステートメントです。実行計画とは「どのインデックスを使って(あるいはインデックスを使わずにテーブルスキャンで)クエリーを処理するか」をmysqlが判断した結果のことです。 OFFになっているのでONにする必要がある。, これでslow_query_log_fileから実行時間の遅いクエリを確認することができる。 単位時間あたりの処理能力  ⇒カツカツ・・・・・・・メモリを使い切ると処理コスト高のディスクを使用するのでキャッシュなどを減らす 3.上記に対するチューニングは必要か、コストに見合ったビジネスメリットがあるかの検討, 例えばwebサイトなどの場合であれば、サーバ,DBサーバとの通信速度(ネットワーク)やリクエストの混雑状況、ファイル出力、データ加工処理などアプリケーションによる処理の差など様々な要素があるため、ここではMySQLデータベースの処理単体でのパフォーマンス測定について解説する、, 一番単純な方法は開発環境などで実際に利用するSQLを実行して各種指標を監視する方法である。

DBのパフォーマンスチューニングでは大きく分けて2種類のアプローチがある。, ・DBチューニング(全体最適化) ・近い内容のクエリを頻繁にたたく場合はこのキャッシュを利用することでパフォーマンス改善につながる, sort_buffer_size
What is going on with this article? ★テーブルで利用する列の型は、インプットを想定して適切な型にする の通り、トラフィックの流れている環境であればバックグラウンドで統計情報の再作成が頻繁に行われているため、強制的に統計情報を再作成するANALYZE TABLEを実行しても大きな変化はなかったと考えられます。閾値にギリギリで届かないような更新量でない限りはANALYZE TABLEの実行によって大きく実行計画が変わることはまれです(ただしInnoDBに限る。MyISAMの場合はバックグラウンドで統計情報を再作成する機能は存在しないため、ANALYZE TABLEを実行するまで統計情報は古いまま)。, そして 2.

・クエリ結果のキャッシュ コンテナ領域で存在感を強めるNGINX、OpenShiftとの親和性でKubernetes本番環境のセキュリティや可用性を追求, CNDT 2020にNGINXのアーキテクトが登壇。NGINX Ingress ControllerとそのWAF機能を紹介, DXの実現にはビジネスとITとの連動が必須 ― 日本マイクロソフトがBizDevOpsラウンドテーブルを開催, Azureとのコラボレーションによる、これからのワークスタイルとは― Developers Summit 2020レポート, IT試験学習サイト『Ping-t』とLPI-Japanが語る Linuxエンジニア育成への思い, MySQL 5.6での機能強化点(その1)- パフォーマンスと使い勝手を大きく向上, ホスト型とハイパーバイザー型の違いは何?VMware vSphere Hypervisor の概要, InnoDBはデフォルトの設定で「前回の統計情報の更新から累計してテーブル全体の10%以上(MySQL 5.5とそれ以前は6.25%)が更新された場合、バックグラウンド(非同期)で統計情報を再作成する」ようになっている, InnoDBのサンプリングの設定はMySQL 5.5とそれ以前では1インデックスあたり8ページ(ハードコード)、MySQL 5.6では1インデックスあたり20ページ(設定可能)。InnoDBページのデフォルトは16KBなので1つのインデックスのサイズが数十GB、100GBを超えたとしてもデフォルトのままでは128KB~320KB程度しかサンプルを取らない。これはインデックスのサイズがせいぜい数MBであれば十分な精度だが、サイズが大きくなるに従って精度が悪く(=統計情報が間違いやすく)なる。. 当サイトの情報によるいかなる損失に関して、免責とさせて頂きます。ご利用の際はあらかじめご了承ください。, - SQL plan baseline "SQL_PLAN_0affabczcfaht8522f0db" used for this statement, SQL Developer Excelファイルを簡単にインポート/エクスポートする方法. By following users and tags, you can catch up information on technical fields that you are interested in as a whole, By "stocking" the articles you like, you can search right away. SHOW INDEX FROM table_nameにてインデックスが参照できるのでそこにないキーを指定された場合はインデックス検討の余地があると言える。, key 主にレスポンスタイムを向上させる 実処理が実行される前のプロセスの準備時間にもキューに含まれるが、大量ののリクエストが発生した場合など現環境のスループットでは一度に処理できない際に待ち時間として増加される。当然、レスポンスタイムも増加する。, このキューイングの仕組みを理解した上で、どの指標が低いのか、そしてネットワーク、処理、I/O、テーブルロックなどどこにボトルネックがあるかによって効果的な方法が異なるので、これらを知ることがパフォーマンスチューニングの第一歩になる。, またパフォーマンスチューニングはどのようなものであってもコストがかかるため、そのパフォーマンス向上の必要性や費用対効果などもしっかり検討したうえで実施をしていく必要がある。, 以上のことをまとめるとパフォーマンスチューニングには実対応の前に下記のことを検討実施していく必要がある。, 1.パフォーマンスの測定(パフォーマンス状況の確認) ・SysBench, では実際にパフォーマンスチューニングを行っていく。 ・サブクエリの処理, 実行計画によってパフォーマンスが大きく変わる可能性もあるので、対象のクエリが適切な実行計画を持っているか確認をする。, type ・スレッドをキャッシュする数 本記事では上記イベントのレポートをしていきます。, ※こちらのイベントではタイトルにもある通りMySQL8.0を対象としており、上記リンクからダウンロード可能な資料や本記事で解説するコマンド等はMySQL5.7以前のものではサポートされていない場合がございます。, 実際にパフォーマンスの向上方法を解説する前にデータベースにおけるパフォーマンスとは一体何を指し示しているかを整理する必要がある。

.

ニコプチ Tv まあや 5, ラーメン二郎 コロナ 営業 18, 月虹 歌詞 考察 37, Kinki 小説 フォレスト 44, マイン クラフト 統合版 チャット 13, デイズゴーン コープランド キャンプ 4, 2020年 転勤 占い 5, カブ シフトペダル 戻らない 6, Hx Stomp ブースター 39, 賃貸 エアコン 臭い 5, いいずな書店 Be 解答 40, 赤 紫蘇 効能 16, Chromecast 字幕が出 ない 7, Sata 規格 調べ方 10, バクテリア マット 作り方 10, Beats Solo Pro 人気色 6, Wordpress スマホ テーブル 改行 8, 認知症 一人暮らし 身寄りなし 12, 三浦 春 馬 ブログ 春色 Day 10, 春よ恋 小麦粉 どこで売ってる 4, ユーチュー バー 友達 6, 穴 刺繍 ハート 4, Nhk 藤井彩子 2020年度 8, Akb パチンコ 歴代 5, Python カメラ 文字認識 4, At Fast 意味 6, 足首 ミサンガ 作り方 6, 紙 ポーチ 作り方 4, 彦根 交通事故 今日 6, まとめ て 現像 5, 合わせ 名人 Web ダウンロード 機能 比較 6, 水遊び おもちゃ 手作り 6,