・桁数の多くない数値型に対してはtinyint,smallint,mediumintなどを利用する この記事では、クエリを実行する・explainを実行するといった超基本的な操作について、Workbench上でどう行えばいいか、スクリーンショットを用いて解説します。 使用する環境・バージョン. All rights reserved. MySQL 5.6.22; スロークエリとは? 実行が遅いQuery。事業や実行する処理にもよるが、2秒以上かかっていたら大体遅い部類と言って良い。 ... 実行計画. 並列処理が出来る件数が増えるほどこのスループットの値が向上する, ・レスポンスタイム(レイテンシー) まずはどのような設定がされているのか確認が必要。 と 3. ・ソート処理用のメモリサイズ tcpdumpコマンドでパケットの通信を計測する方法や、MySQL Proxyで監視する方法がある。 DBのパフォーマンスチューニングでは大きく分けて2種類のアプローチがある。, ・DBチューニング(全体最適化) OS : MacOS Mojave ver 10.14.5; MySQL : Ver 8.0.18 for osx10.14 on x86_64 (Homebrew) システム変数はmy.cnfやmy.iniにて参照・編集可能になっている Copyright © 2004-2020 Impress Corporation. MySQLサーバ内の「イベント」ごとの処理時間を記録 の通り、インデックスのサイズが大きくなれば大きくなるほど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. 必ずしも正確性・信頼性等を保証するものではありません。 下記が主にデータベースのパフォーマンスを測定するための指標となっている。, ・スループット ・スレッドをキャッシュする数 ・mysqlslap 主なプロセスとしては下記などがある。 データベースをはじめとするサービスのパフォーマンスは必ずハードウェアのスペックに依存するため、メモリやCPUの追加、ディスクのSSD化などに柔軟に対応できる必要がある。 実際にインデックスとして参照されたカラムになる。 ・サーバが許容可能なコネクションの数 ・レスポンスタイムが長い場合にもこちらを増やすことで改善につながる可能性がある プログラムより運動が好きです。. An Impress Group Company. ISBN:978-4-295-00029-7 テーブルにインデックスが貼られていたとしてもこの値に含まれなければ使用できていないということになる。, 1.インデックスが使えていないクエリはインデックスがないか検討 主にスループットを向上させる テーブルの構成やクエリの最適化によってクエリ実行速度の向上を目指す, MySQLサーバの設定はシステム変数で定義されている。 ・可能なところはNOT NULLを宣言, 【インデックス】 ・ここで設定されているサイズを超えるものはディスクによって行われる  ⇒全然使っていない・・・スレッド/クエリキャッシュを使用してみる Why not register and get more from Qiita? 1処理自体にどのくらい時間がかかるか, ・スケーラビリティ ・スループットが低い場合にはここを増やすことでパフォーマンス改善につながる ・MySQL5.6でデフォルトが2MB⇒256KBに縮小されたらしい, テーブル定義においてもパフォーマンスを考える上で非常に大切になってくる。 オプティマイザーは統計情報をインプットにして実行計画を選択しています。統計情報が変わることで実行計画が変わることは、データベースとしては柔軟な判断ができるわけです。, しかし、オプティマイザがどうしても最適な判断をしてくれない場合に、SPM(SQL Plan Management)を利用して実行計画を固定する方法があります。, なお前身となる機能にプランスタビリティという機能は昔から存在していましたが、大幅に機能が拡張されたものです。, 今回は普通ならIndex Scanされる状況でもFull Scanに固定します。作成方法も以下のように4つほどあります。, 今回はカーソル・キャッシュ(共有プールに残った実行計画)から計画ベースライン(実行計画を固定する情報)を作成します。, 簡単に説明すると、SQLを実行して最適な実行計画が選択されたときの情報で実行計画を固定する方法です。, デフォルトはこの状態です。optimizer_use_sql_plan_baselines TRUE:承認されたベースラインを使用する, ACCEPTEDがYESになることで同じSQLが実行されるとfull scanになります。すこしでもSQL文が異なると適用されませんので注意が必要です。, Full scanのままですね。また最終行に先ほどの計画ベースラインを適用したことが記載されています。, 実際はテスト環境で計画ベースラインを作成したものを本番環境に移行する方法が行われています。その方法は別途記事にしたいと思います。, 前回の記事は、計画ベースライン(実行計画を固定する情報)を作成して実際に実行計画を固定してみました。今回はテスト環境で作成した計画ベースラインを本番環境に移行する方法を記事にします。, 今回は、ログスイッチが多発したケースについてAWRレポートを確認していきたいと思います。, AWRレポートのディクショナリキャッシュ統計・ライブラリキャッシュ統計 ・メモリ統計, ASHレポートの見方は、AWR以上にドキュメントが薄いため読み方が分かりづらいです。今回はデータベースに負荷をかけた結果からASHにどのように表示されるか見ていきます。, ソフトウェアベンダーでITコンサルタントとして働いています。製造業のお客様を中心に、業務アプリケーションのデリバリーを担当しています。これまでの経験をフィードバックしていきます。, 当サイトのすべてのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、情報が古くなったりすることもあります。 発行:インプレス, 本書は、インプレスの“オープンソース技術の実践活用メディア”Think ITの連載記事『MySQLマイスターに学べ! 即効クエリチューニング』の内容をまとめて書籍化したものです。言語やフレームワークに依存せずMySQL側から解決へアプローチするための方法を紹介しています。特定のケースにマッチする改善の手法よりも、繰り返し使われる計測の手法にフォーカスを当てて説明していきます。, GMOメディア株式会社のDBAで日本MySQLユーザ会員。Oracle ACE(MySQL)、MySQL 5.7 Community Contributor Award 2015受賞。ぬいぐるみとイルカが好きなおじさん。, 「OSSfm」は“オープンソース技術の実践活用メディア”であるThink ITがお届けするポッドキャストです。. ★テーブルで利用する列の型は、インプットを想定して適切な型にする テーブルのアクセスに利用可能なインデックスの候補として挙げられるキーを表示してくれる ・デフォルトは9, query_cache_size SHOW INDEX FROM table_nameにてインデックスが参照できるのでそこにないキーを指定された場合はインデックス検討の余地があると言える。, key  ・取得される行数が少ないテーブルから順番にjoinするのが基本 What is going on with this article? 【データ型】  ⇒カツカツ・・・・・・・メモリを使い切ると処理コスト高のディスクを使用するのでキャッシュなどを減らす ★自身の環境のメモリサイズを鑑みて有効に使えているか確認する mysqlサーバの設定はシステム変数で定義されている。 まずはどのような設定がされているのか確認が必要。 ... 実行計画. 1/10に日本oracle社主催のMySQL 8.0入門セミナー講演資料 (チューニング基礎編、SQLチューニング編)に行ってきました。 ・ソースコードでの位置 ・こちらもメモリを利用するため増やしすぎ注意 性能統計情報の仕組み 発売日:2016年11月29日発売 統計情報が変わることで実行計画が変わることは、データベースとしては柔軟な判断ができるわけです。 しかし、オプティマイザがどうしても最適な判断をしてくれない場合に、SPM(SQL Plan Management)を利用して 実行計画を固定する 方法があります。 上にいくほど処理が軽く、下に行くほど高コストとなっている。 ・テーブル、インデックス、スキーマの統計、レイテンシ、待ち時間, performance_schema/sys_schemaで実行できるクエリサンプル, 上記にあげた確認方法の中でもDBサーバ設定にボトルネックがある場合、下記の値を参照・編集することで解決につながる可能性が高い。, max_connections コンテナ領域で存在感を強める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であれば十分な精度だが、サイズが大きくなるに従って精度が悪く(=統計情報が間違いやすく)なる。. 本記事では上記イベントのレポートをしていきます。, ※こちらのイベントではタイトルにもある通りMySQL8.0を対象としており、上記リンクからダウンロード可能な資料や本記事で解説するコマンド等はMySQL5.7以前のものではサポートされていない場合がございます。, 実際にパフォーマンスの向上方法を解説する前にデータベースにおけるパフォーマンスとは一体何を指し示しているかを整理する必要がある。 ・SysBench, では実際にパフォーマンスチューニングを行っていく。 explain の追加情報(en/5.7) 参考リンク. ・インデックススキャン ・I/O量の多いファイルや処理、コストの高いSQL文、ロック情報 ・JOINの順番 主にレスポンスタイムを向上させる 実処理が実行される前のプロセスの準備時間にもキューに含まれるが、大量ののリクエストが発生した場合など現環境のスループットでは一度に処理できない際に待ち時間として増加される。当然、レスポンスタイムも増加する。, このキューイングの仕組みを理解した上で、どの指標が低いのか、そしてネットワーク、処理、I/O、テーブルロックなどどこにボトルネックがあるかによって効果的な方法が異なるので、これらを知ることがパフォーマンスチューニングの第一歩になる。, またパフォーマンスチューニングはどのようなものであってもコストがかかるため、そのパフォーマンス向上の必要性や費用対効果などもしっかり検討したうえで実施をしていく必要がある。, 以上のことをまとめるとパフォーマンスチューニングには実対応の前に下記のことを検討実施していく必要がある。, 1.パフォーマンスの測定(パフォーマンス状況の確認) SHOW FULL PROCESSLISTコマンドを使えば現在実行中のクエリの実行時間やデータ量、ステータスなどが参照できる。, 実行計画とはSQLを処理する際の処理の手順となっている。 Azure×コミュニティ「Azure Rock Star Community Day」イベントレポート, MySQL 8.0入門セミナー講演資料 (チューニング基礎編、SQLチューニング編), you can read useful information later efficiently. の通り、トラフィックの流れている環境であればバックグラウンドで統計情報の再作成が頻繁に行われているため、強制的に統計情報を再作成するANALYZE TABLEを実行しても大きな変化はなかったと考えられます。閾値にギリギリで届かないような更新量でない限りはANALYZE TABLEの実行によって大きく実行計画が変わることはまれです(ただしInnoDBに限る。MyISAMの場合はバックグラウンドで統計情報を再作成する機能は存在しないため、ANALYZE TABLEを実行するまで統計情報は古いまま)。, そして 2. ※単にハードウェアを良いものにすればパフォーマンスが上がりますが本記事の趣旨と異なるので以降では解説しない, パフォーマンス測定の指標ではないが、パフォーマンスチューニングに当たって非常に重要な仕組みなので解説していく。, キューイングとは複数のリクエストが発生したときに順番通り処理が実行されるように管理される待ち行列のことである。レスポンスタイムはこの待ち時間+実行時間で計算される。 OFFになっているのでONにする必要がある。, これでslow_query_log_fileから実行時間の遅いクエリを確認することができる。 また下記コマンドにても参照・編集が可能。, 今回はパフォーマンスに焦点を置いているため、性能統計情報分析のために用意されているパフォーマンススキーマ/sysスキーマにフォーカスする。, ・パフォーマンス・スキーマ 単位時間あたりの処理能力 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.  ・3テーブル以上のjoinは結果セットが少量になるテーブルからjoin, 今回このセミナーに参加して、インデックスやjoinについては知っていたもののEXPLAINコマンドの便利さや、サーバ側のパラメータ設定などについては目からうろこだったので非常に勉強になりました。 ・複数列貼ることで参照速度を改善することができる 8.8.2 explain 出力フォーマット; mysqlのexplainを徹底解説!! ・joinで利用する列は同じデータ型にする ・増やしすぎるとサーバメモリを多く消費するためハードウェアのスペックと相談 後半のSQLチューニング章で紹介するEXPLAINコマンドやSHOW FULL PROCESSLISTコマンドを利用することでクエリ自体のステータスや対象テーブルの状況なども確認できるので併せて利用すると効率的。, その他ツールを利用する場合、下記などがある。 Help us understand the problem. mysqlでのsqlチューニングについて(explainの見方) 検証:パーティショニングテーブルの挙動 typeには下記の種類があり、そのクエリの意味を説明してくれる。 価格:1,800円+税 上記の例ではselect *で指定をしているためALLになっている。, possible_keys