現在、開発者は引き続き LAMP (Linux®、Apache、MySQL、および PHP/Perl) アーキテクチャを使用してアプリケーションを開発および展開しています。ただし、アプリケーションは他の人が作成したため、サーバー管理者はアプリケーション自体をほとんど制御できないことがよくあります。この 3 部構成のシリーズでは、アプリケーションのパフォーマンスに影響を与える可能性があるサーバー構成の多くの問題について説明します。この記事は、このシリーズの 3 番目で最後の部分であり、効率を最大化するためのデータベース層のチューニングに焦点を当てます。
MySQL のチューニングに関して、
MySQL サーバーの実行速度を高速化するには、効率が低い順に次の 3 つの方法があります。
問題のあるハードウェアを交換します。 MySQL プロセスの設定を調整します。 クエリを最適化します。
主にデータベースが多くのリソースを消費する可能性があるため、問題のあるハードウェアの交換が最初に考慮されることがよくあります。しかし、この解決策はまだ限界があります。実際、多くの場合、中央処理装置 (CPU) またはディスクの速度を 2 倍にし、メモリを 4 ～ 8 倍に増やすことができます。
2 番目の方法は、MySQL サーバー (mysqld とも呼ばれます) を調整することです。このプロセスをチューニングするということは、メモリを適切に割り当て、mysqld にどのような種類の負荷がかかるかを認識させることを意味します。ディスク操作の高速化は、必要なディスク アクセスの数を減らすことほど重要ではありません。同様に、MySQL プロセスが正しく動作することを保証するということは、一時ディスク テーブルの操作やファイルの開閉などのバックグラウンド タスクよりもクエリの処理に多くの時間を費やすことを意味します。 mysqld のチューニングがこの記事の焦点です。
最善のアプローチは、クエリが最適化されていることを確認することです。これは、適切なインデックスがテーブルに適用され、MySQL の機能を最大限に活用する方法でクエリが作成されることを意味します。この記事ではクエリのチューニング (多くの書籍で取り上げられているトピック) については説明しませんが、チューニングが必要な可能性のあるクエリを報告するように mysqld を構成します。
これらのタスクには順序が割り当てられていますが、クエリを適切に調整するには、ハードウェアと mysqld の設定に注意を払う必要があります。マシンが遅くても問題はありません。非常に高速なマシンが、適切に設計されたクエリを実行すると、mysqld が多くのビジーな作業に占有され、クエリを処理できなくなるため、高負荷により障害が発生するのを見てきました。
遅いクエリのロギング
SQL サーバーでは、データ テーブルがディスクに保存されます。インデックスは、テーブル全体を検索することなく、テーブル内の特定のデータ行を検索する方法をサーバーに提供します。テーブル全体を検索する必要がある場合、それはテーブル スキャンと呼ばれます。一般に、テーブル内のデータのサブセットのみを取得したい場合があるため、テーブル全体のスキャンは大量のディスク I/O を無駄にし、したがって多くの時間を無駄にします。データを結合する必要がある場合、結合の両側にある複数のデータ行を比較する必要があるため、この問題はさらに悪化します。
もちろん、テーブル スキャンが常に問題を引き起こすわけではありません。場合によっては、データのサブセットを選択するよりもテーブル全体を読み取る方が効率的です (これらの決定には、サーバー プロセスのクエリ プランナーが使用されます)。インデックスが非効率的に使用されているか、まったく使用できない場合、クエリの速度が低下し、サーバーの負荷とテーブルのサイズが増加するにつれて、この問題はさらに深刻になります。指定された時間範囲よりも実行に時間がかかるクエリは、低速クエリと呼ばれます。
これらの遅いクエリを適切な名前のスロー クエリ ログに記録するように mysqld を設定できます。管理者はこのログを確認して、アプリケーションのどの部分をさらに調査する必要があるかを判断します。リスト 1 は、低速クエリ ロギングを有効にするために my.cnf で行う必要がある構成を示しています。
リスト 1. MySQL スロークエリログの有効化
[mysqld]; 低速クエリ ログを有効にします。デフォルトは 10 秒です。log-slow-queries は、long_query_time 未満の場合でもインデックスを使用しないログ クエリを実行します。インデックスを使用しないクエリ
これら 3 つの設定は、実行に 5 秒以上かかり、インデックスを使用しないクエリをログに記録するために一緒に使用されます。 log-queries-not-using-indexes に関する警告に注意してください。MySQL 4.1 以降を使用する必要があります。スロー クエリ ログは MySQL データ ディレクトリに保存され、hostname-slow.log という名前が付けられます。別の名前またはパスを使用したい場合は、my.cnf で log-slow-queries = /new/path/to/file を使用してこれを実現できます。
スロークエリログの読み取りには、mysqldumpslow コマンドを使用するのが最適です。ログ ファイルへのパスを指定すると、スロー クエリのソートされたリストと、ログ ファイル内での発生回数を確認できます。非常に便利な機能は、mysqldumpslow が結果を比較する前にユーザー指定のデータを削除するため、同じクエリに対する複数の呼び出しが 1 つとしてカウントされることです。これは、最も多くの作業を必要とするクエリを特定するのに役立ちます。
クエリのキャッシュ
多くの LAMP アプリケーションはデータベースに大きく依存していますが、同じクエリを繰り返し実行します。クエリが実行されるたびに、データベースは同じジョブを実行する必要があります。つまり、クエリを分析し、実行方法を決定し、ディスクから情報をロードし、結果をクライアントに返します。 MySQL にはクエリ キャッシュと呼ばれる機能があり、クエリ結果 (後で使用されます) をメモリに保存します。多くの場合、これによりパフォーマンスが大幅に向上します。ただし、問題は、クエリ キャッシュがデフォルトで無効になっていることです。
/etc/my.conf に query_cache_size = 32M を追加して、32MB のクエリ キャッシュを有効にします。
クエリ キャッシュの監視
クエリ キャッシュを有効にした後、それが効果的に使用されているかどうかを理解することが重要です。 MySQL には、キャッシュ内で何が起こっているかを理解するために確認できる変数がいくつかあります。リスト 2 はキャッシュのステータスを示しています。
リスト 2. クエリ キャッシュ統計の表示
mysql> 'qcache%' のようなステータスを表示;+-----------------------+------------+ | 変数名 |+----------------------+-----------+| 5216 || Qcache_free_memory || Qcache_hits 360210433 || Qcache_total_blocks || ----- ---+----------------+8 行セット (0.00 秒)
これらの項目の説明を表 1 に示します。
表 1. MySQL クエリ キャッシュ変数 変数名の説明
Qcache_free_blocks キャッシュ内の隣接するメモリ ブロックの数。数値が大きい場合は、断片が存在する可能性があることを示します。 FLUSH QUERY CACHE は、キャッシュをデフラグして空きブロックを取得します。
Qcache_free_memory キャッシュ内の空きメモリ。
Qcache_hits は、クエリがキャッシュにヒットするたびに増加します。
Qcache_inserts は、クエリが挿入されるたびに増加します。ミス率はヒット数を挿入数で割ったもので、この値を 1 から引くとヒット率が得られます。上の例では、クエリの約 87% がキャッシュにヒットします。
Qcache_lowmem_prunes キャッシュがメモリ不足になり、より多くのクエリのためのスペースを確保するためにキャッシュをパージする必要があった回数。この数値は長期間にわたって表示するのが最適です。数値が増加している場合は、深刻な断片化またはメモリ不足を示している可能性があります。 (上記の free_blocks と free_memory は、どちらの場合であるかを示します)。
Qcache_not_cached 通常は SELECT ステートメントではないため、キャッシュに適さないクエリの数。
Qcache_queries_in_cache 現在キャッシュされているクエリ (および応答) の数。
Qcache_total_blocks キャッシュ内のブロックの数。
多くの場合、これらの変数を数秒間隔で表示することで違いがわかり、キャッシュが効率的に使用されているかどうかを判断するのに役立ちます。 FLUSH STATUS を実行するといくつかのカウンタをリセットできます。これは、サーバーがしばらく実行されている場合に非常に役立ちます。
非常に大規模なクエリ キャッシュを使用し、すべてをキャッシュすることを期待するのは非常に誘惑的です。 mysqld はメモリが少なくなったときにプルーニングを実行するなど、キャッシュのメンテナンスを実行する必要があるため、サーバーがキャッシュを管理しようとして行き詰まってしまう可能性があります。一般に、FLUSH QUERY CACHE に時間がかかる場合、キャッシュが大きすぎます。
制限の強制
mysqld で制限を強制して、システム負荷によってリソースが枯渇しないようにすることができます。リスト 3 は、my.cnf 内のリソース関連の重要な設定をいくつか示しています。
リスト 3. MySQL リソース設定
set-variable=max_connections=500set-variable=wait_timeout=10max_connect_errors = 100
最大接続数は最初の行で管理されます。 Apache の MaxClients と同様に、サービスで許可された数の接続のみが確立されるようにするという考え方です。現在サーバー上で確立されている接続の最大数を確認するには、SHOW STATUS LIKE 'max_used_connections' を実行します。
行 2 は、mysqld に 10 秒以上アイドル状態の接続を終了するように指示します。 LAMP アプリケーションでは、データベースへの接続にかかる時間は、通常、Web サーバーがリクエストを処理するのにかかる時間になります。場合によっては、負荷が重すぎると接続がハングし、接続テーブルのスペースが占有されてしまいます。複数の対話型ユーザーがいる場合、またはデータベースへの永続的な接続を使用している場合、この値を低く設定することはお勧めできません。
最後の行は安全な方法です。ホストでサーバーへの接続に問題が発生し、諦めるまでに何度も再試行すると、ホストはロックされ、FLUSH HOSTS が完了するまで実行できなくなります。デフォルトでは、10 回の失敗でロックアウトが発生します。この値を 100 に変更すると、サーバーが問題から回復するのに十分な時間が与えられます。 100 回再試行しても接続を確立できない場合は、より高い値を使用してもあまり役に立たず、まったく接続できない可能性があります。
バッファとキャッシュ
MySQL は 100 を超える調整可能な設定をサポートしていますが、幸いなことに、いくつかをマスターすればほとんどのニーズを満たします。これらの設定の正しい値を見つけるには、SHOW STATUS コマンドを使用してステータス変数を表示し、mysqld が期待どおりに動作しているかどうかを判断できます。バッファとキャッシュに割り当てられるメモリは、システムで利用可能なメモリを超えることはできないため、通常、チューニングにはある程度の妥協が必要です。
MySQL 調整可能設定は、mysqld プロセス全体または個々のクライアント セッションに適用できます。
サーバー側の設定
各テーブルはディスク上のファイルとして表すことができ、最初にファイルを開いてから読み取る必要があります。ファイルからデータを読み取るプロセスを高速化するために、mysqld はこれらの開いているファイルを /etc/mysqld.conf の table_cache で指定された最大数までキャッシュします。リスト 4 は、テーブルを開くことに関連するアクティビティを表示する方法を示しています。
リスト 4. テーブルを開くアクティビティの表示
mysql> SHOW STATUS LIKE 'open%tables';+------+------+ 値 |+------ -------+------+| 開いたテーブル数 || 195セット内の +2 行 (0.00 秒)
リスト 4 は、現在 5,000 のテーブルが開いており、キャッシュ内に使用可能なファイル記述子がないため 195 のテーブルを開く必要があることを示しています (統計が以前にクリアされているため、開いているテーブルは 5,000 個しかない可能性があります)。 。 SHOW STATUS コマンドを再実行すると Opened_tables が急激に増加する場合は、キャッシュ ヒット率が不十分であることを示します。 Open_tables が table_cache 設定よりもはるかに小さい場合、値は大きすぎます (ただし、拡大の余地があることは決して悪いことではありません)。たとえば、テーブルのキャッシュを調整するには、table_cache = 5000 を使用します。
テーブル キャッシュと同様に、スレッド用のキャッシュもあります。 mysqld は、接続を受信するときに必要に応じてスレッドを生成します。接続がすぐに変化する負荷の高いサーバーでは、後で使用するためにスレッドをキャッシュすると、最初の接続が高速化されます。
リスト 5 は、十分なスレッドがキャッシュされているかどうかを判断する方法を示しています。
リスト 5. スレッド使用量統計の表示
mysql> SHOW STATUS LIKE 'threads%';+--------+--------+| 変数名 |+---- ---------------+--------+| スレッド数 27 || スレッド数 3 || ----------+----------+4 行セット (0.00 秒)
ここで重要な値は Threads_created です。この値は、mysqld が新しいスレッドを作成する必要があるたびに増加します。 SHOW STATUS コマンドを連続して実行するときにこの数が急速に増加する場合は、スレッド キャッシュを増やすことを試みる必要があります。たとえば、my.cnf で thread_cache = 40 を使用すると、これを実現できます。
キー バッファには、MyISAM テーブルのインデックス ブロックが保持されます。理想的には、これらのブロックに対するリクエストはディスクではなくメモリから送信される必要があります。リスト 6 は、ディスクから読み取られたブロック数とメモリから読み取られたブロック数を確認する方法を示しています。
リスト 6. キーワードの効率の決定
mysql> '%key_read%' のようなステータスを表示;+--------+-----------+| 変数名 |+ -------+----------+| キー読み取りリクエスト || 98247 |+------- -----------+----------+2 行セット (0.00 秒)
Key_reads はディスクにヒットするリクエストの数を表し、Key_read_requests は合計数です。ミス率は、ディスクにヒットした読み取りリクエストの数を読み取りリクエストの総数で割ったものです。この場合、1,000 リクエストごとにメモリ内で約 0.6 ミスが発生します。ディスク ヒット数が 1,000 リクエストあたり 1 件を超える場合は、キーワード バッファを増やすことを検討する必要があります。たとえば、key_buffer = 384M はバッファを 384MB に設定します。
一時テーブルは、さらなる処理 (GROUP BY 句など) の前にデータを一時テーブルに保存する必要がある、より高度なクエリで使用できます。一時テーブルはメモリ内に作成されるのが理想的です。ただし、一時テーブルが大きくなりすぎる場合は、ディスクに書き込む必要があります。リスト 7 は、一時テーブルの作成に関する統計を示しています。
リスト 7. 一時テーブルの使用を決定する
mysql> SHOW STATUS LIKE 'created_tmp%';+--------------------------+----------+| 値| +----------------------+------+| 作成された_tmp_tables || 作成された_tmp_tables || | +---------------------+----------+3 行セット (0.00 秒)
Created_tmp_tables は一時テーブルが使用されるたびに増加します。また、ディスクベースのテーブルの場合は Created_tmp_disk_tables も増加します。この比率は関係するクエリによって異なるため、厳密なルールはありません。 Created_tmp_disk_tables を時間の経過とともに監視すると、作成されたディスク テーブルの割合が表示され、セットアップの効率を判断できます。 tmp_table_size と max_heap_table_size は両方とも一時テーブルの最大サイズを制御するため、両方の値が my.cnf に設定されていることを確認してください。
セッションごとの設定
次の設定は各セッションに固有です。これらのオプションに存在する可能性のある接続の数を掛けると、大量のメモリが必要になるため、これらの数値を設定するときは十分に注意してください。コードを使用してセッション内でこれらの数値を変更することも、すべてのセッションの my.cnf でこれらの設定を変更することもできます。
MySQL はソートが必要な場合、ディスクから読み取られるデータの行を保持するソート バッファを割り当てます。並べ替えるデータが大きすぎる場合は、データをディスク上の一時ファイルに保存して、再度並べ替える必要があります。 sort_merge_passes ステータス変数が大きい場合、これはディスク アクティビティを示しています。リスト 8 は、ソートに関連するいくつかのステータス カウンター情報を示しています。
リスト 8. ソート統計の表示
mysql> SHOW STATUS LIKE "sort%";+--------+--------+| 変数名 |+--- ----------+----------+| ソート範囲 || ソート範囲 2066532 |+-- ----+--------+4 行セット (0.00 秒)
sort_merge_passes が大きい場合は、sort_buffer_size に注意する必要があることを意味します。たとえば、sort_buffer_size = 4M はソート バッファを 4MB に設定します。
MySQL はテーブルを読み取るためにメモリも割り当てます。理想的には、インデックスは必要な行のみを読み取るのに十分な情報を提供しますが、場合によってはクエリ (設計が適切でないか、データの性質により) でテーブルから大量のデータを読み取る必要があります。この動作を理解するには、実行された SELECT ステートメントの数と、(インデックスを介して直接アクセスするのではなく) テーブル内の次のデータ行を読み取る必要があった回数を知る必要があります。この機能を実現するコマンドをリスト 9 に示します。
リスト 9. テーブル スキャン率の決定
mysql> SHOW STATUS LIKE "com_select";+------+--------+| 変数名 |+------- -------+----------+| Com_select |+----------+----------+1 行 (0.00 秒) mysql> SHOW STATUS LIKE "handler_read_rnd_next";+------------+----------- 変数名 | |+----------------------+-----------+| ハンドラー_read_rnd_next |+- ----- ----------+----------+1 行 (0.00 秒)
Handler_read_rnd_next / Com_select の結果、テーブル スキャン率は 521:1 になります。値が 4000 を超える場合は、read_buffer_size を確認する必要があります (例: read_buffer_size = 4M)。この数が 800 万を超える場合は、これらのクエリの調整について開発者と話し合う時期が来ています。
3 必須ツール
SHOW STATUS コマンドは特定の設定を理解する場合に非常に役立ちますが、mysqld によって提供される大量のデータを解釈するためのツールも必要になります。私が必須だと思うツールが 3 つあります。これらのツールへのリンクは「リソース」セクションにあります。
ほとんどのシステム管理者は、タスクによって消費される CPU とメモリの継続的に更新されるビューを提供する top コマンドに精通しています。 mytop は top をエミュレートし、接続されているすべてのクライアントとそれらのクライアントが実行しているクエリのビューを提供します。 mytop は、キーワード バッファとクエリ キャッシュの効率に関するライブ データと履歴データ、実行中のクエリに関する統計も提供します。これは、システム内で何が起こっているか (たとえば 10 秒以内) を確認するのに便利なツールです。サーバーの健全性情報を表示したり、問題の原因となっている接続を表示したりできます。
mysqlard は MySQL サーバーに接続されているデーモンで、5 分ごとにデータを収集し、バックグラウンドでラウンド ロビン データベースに保存する役割を果たします。テーブル キャッシュの使用状況、キーワードの効率、接続されているクライアント、一時テーブルの使用状況などのデータを表示する Web ページがあります。 mytop はサーバーの正常性情報のスナップショットを提供しますが、mysqlard は長期的な正常性情報を提供します。おまけに、mysqlard は収集した情報の一部を使用して、サーバーを調整する方法についていくつかの提案を提供します。
SHOW STATUS 情報を収集するためのもう 1 つのツールは、mysqlreport です。サーバーのあらゆる側面を分析する必要があるため、そのレポートは mysqlard よりもはるかに複雑です。これは、状態変数に対して適切な計算を実行して、どの問題を修正する必要があるかを判断するのに役立つため、サーバーをチューニングするための優れたツールです。
結論
この記事では、MySQL のチューニングに関する基本的な知識を紹介し、LAMP コンポーネントのチューニングに関するこの 3 部構成のシリーズを締めくくりました。チューニングには主に、コンポーネントがどのように動作するかを理解し、適切に動作しているかどうかを判断し、調整を加え、再評価することが含まれます。 Linux、Apache、PHP、MySQL などの各コンポーネントにはさまざまな要件があります。各コンポーネントを個別に理解することは、アプリケーションの速度を低下させる可能性があるボトルネックを軽減するのに役立ちます。