MySQL運転状態及びチューニング(二)

11952 ワード
mysql
1、MySQLサーバー構成情報の表示
mysql> show variables;

2,MySQLサーバーが実行する各種状態値を表示する
mysql> show global status;

3、遅いクエリ
mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | OFF   |
| slow_launch_time | 2     |
+------------------+-------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 279   |
+---------------------+-------+

構成では、レコードの遅いクエリーが閉じられています(開くことが望ましい、最適化しやすい、スロークエリーを開く)、2秒を超えると遅いクエリーになります.合計279件の遅いクエリーがあります.
4、接続数
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+

mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 498   |
+----------------------+-------+

設定した最大接続数は500で、応答する接続数は498 max_です.used_connections/max_connections*100%=99.6%(理想値≒85%)
5, key_buffer_size key_buffer_sizeはMyISAMテーブルのパフォーマンスに最も影響を与えるパラメータですが、データベースの多くはInnodbです.
mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 67108864 |
+-----------------+----------+

mysql> show global status like 'key_read%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Key_read_requests | 25629497 |
| Key_reads         | 66071    |
+-------------------+----------+

合計256294997個のインデックス読み取り要求があり、66071個の要求がメモリに直接ハードディスクからインデックスを読み取ることができず、インデックスがキャッシュにヒットしない確率を計算した:key_cache_miss_rate ＝ Key_reads/Key_read_requests*100%=0.27%適宜key_を大きくする必要があるbuffer_size 
mysql> show global status like 'key_blocks_u%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 10285 |
| Key_blocks_used   | 47705 |
+-------------------+-------+

Key_blocks_unusedは未使用のキャッシュクラスタ数を表し、Key_blocks_usedは、使用した最大blocks数Key_を表すblocks_used/(Key_blocks_unused+Key_blocks_used)*100%≒18%(理想値≒80%)
6、テンポラリ・テーブル
mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 4184337 |
| Created_tmp_files       | 4124    |
| Created_tmp_tables      | 4215028 |
+-------------------------+---------+

テンポラリ・テーブルを作成するたびにCreated_tmp_tablesが追加され、ディスクにテンポラリ・テーブルが作成された場合、Created_tmp_disk_tablesも増えたtmp_filesはMySQLサービスによって作成された一時ファイルの数を表します:Created_tmp_disk_tables/Created_tmp_tables*100%=99%(理想値<=25%)
mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_heap_table_size | 134217728 |
| tmp_table_size      | 134217728 |
+---------------------+-----------+

tmp_を追加する必要がありますtable_size
7,オープンテーブルの場合
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 1024  |
| Opened_tables | 1465  |
+---------------+-------+

Open_tablesはオープンテーブルの数を表し、Opened_tablesはオープンしたテーブルの数を表し、Opened_tablesの数が大きすぎて、構成中のtable_を説明しますCache(5.1.3以降この値はtable_open_cacheと呼ばれます)の値が小さすぎる可能性があります.サーバtable_を調べてみましょう.Cache値
mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 1024  |
+---------------+-------+

Open_tables/Opened_tables*100%=69%理想値(>=85%)Open_tables/table_Cache*100%=100%理想値(<=95%)
8、プロセス使用状況
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 31    |
| Threads_connected | 239   |
| Threads_created   | 2914  |
| Threads_running   | 4     |
+-------------------+-------+

MySQLサーバプロファイルでthread_を設定した場合cache_Sizeは、クライアントが切断されると、サーバがクライアントを処理するスレッドがキャッシュされ、破棄ではなく次のクライアントに応答します(キャッシュ数が上限に達していない場合).Threads_createdは作成したスレッドの数を表し、Threads_が見つかった場合createdの値が大きすぎると、MySQLサーバがスレッドを作成していることを示します.これも比較的リソースを消費しているので、プロファイルのthread_を適切に増やすことができます.cache_size値、クエリーサーバthread_cache_size構成:
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 32    |
+-------------------+-------+

9,クエリーキャッシュ(query cache)
mysql> show global status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2226     |
| Qcache_free_memory      | 10794944 |
| Qcache_hits             | 5385458  |
| Qcache_inserts          | 1806301  |
| Qcache_lowmem_prunes    | 433101   |
| Qcache_not_cached       | 4429464  |
| Qcache_queries_in_cache | 7168     |
| Qcache_total_blocks     | 16820    |
+-------------------------+----------+

Qcache_free_blocks:キャッシュ内の隣接するメモリブロックの数.数が多いと破片がある可能性があります.FLUSH QUERY CACHEでは、キャッシュ内のフラグメントが整理され、空きブロックが得られます.Qcache_free_memory:キャッシュ内の空きメモリ.Qcache_hits:クエリーがキャッシュにヒットするたびにQcache_が大きくなります.inserts:クエリーを挿入するたびに大きくなります.ヒット回数を挿入回数で割ると不的中率になります.Qcache_lowmem_prunes:キャッシュにメモリ不足が発生し、より多くのクエリーにスペースを提供するためにクリーンアップする必要がある回数.この数字は長い間見たほうがいい.この数字が増加している場合は、破片が非常に深刻であるか、メモリが少ない可能性があります.(上のfree_blocksとfree_memoryはどのような状況に属しているかを教えてくれます)Qcache_not_Cached:キャッシュに適さないクエリの数は、通常、これらのクエリがSELECT文ではないか、now()などの関数を使用しているためです.Qcache_queries_in_Cache:現在キャッシュされているクエリー(および応答)の数.Qcache_total_blocks:キャッシュ内のブロックの数.サーバを調べてquery_についてCacheの構成:
mysql> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 33554432 |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

各フィールドの説明:query_cache_limit:このサイズを超えるクエリはquery_をキャッシュしません.cache_min_res_unit:キャッシュブロックの最小サイズquery_cache_size:クエリーキャッシュサイズquery_cache_type:キャッシュタイプ、どのクエリーをキャッシュするかを決定します.例ではselect sqlをキャッシュしないことを示します.no_Cacheクエリーquery_cache_wlock_invalidate:他のクライアントがMyISAMテーブルを書き込み操作している場合、query cacheにクエリがある場合、cache結果を返すか、書き込み操作が完了してからテーブルを読み取り、結果を取得します.query_cache_min_res_unitの構成は「両刃の剣」で、デフォルトは4 KBで、設定値が大きいことはビッグデータクエリーにメリットがありますが、クエリーがビッグデータクエリーであれば、メモリの破片や無駄になりやすいです.クエリキャッシュフラグメント率=Qcache_free_blocks/Qcache_total_blocks*100%クエリー・キャッシュ・フラグメント率が20%を超える場合は、FLUSH QUERY CACHEでキャッシュ・フラグメントを整理するか、query_を小さくしてみましょう.cache_min_res_unit、もしあなたのクエリーが小さなデータ量であれば.クエリー・キャッシュ使用率=(query_cache_size–Qcache_free_memory)/query_cache_size*100%クエリーキャッシュ使用率が25%以下であればquery_cache_size設定が大きすぎて、適切に減らすことができます.クエリー・キャッシュ使用率が80%以上、Qcache_lowmem_prunes>50ならquery_cache_sizeは少し小さいかもしれませんが、破片が多すぎます.クエリキャッシュヒット率=(Qcache_hits–Qcache_inserts)/Qcache_hits*100%サンプルサーバクエリーキャッシュフラグメント率=20.46%、クエリーキャッシュ利用率=62.26%、クエリーキャッシュヒット率=1.94%、ヒット率が悪く、書き込み操作が頻繁である可能性があり、フラグメントもある可能性があります.
10、ソートの使用状況
mysql> show global status like 'sort%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Sort_merge_passes | 2136     |
| Sort_range        | 81888    |
| Sort_rows         | 35918141 |
| Sort_scan         | 55269    |
+-------------------+----------+

Sort_merge_passesには2つのステップが含まれています.MySQLではまずメモリのソートを試み、使用するメモリサイズはシステム変数Sort_buffer_sizeは、サイズが足りなければすべてのレコードをメモリに読み込むと、MySQLはメモリでソートした結果をテンポラリファイルに保存し、MySQLがすべてのレコードを見つけてから、テンポラリファイルのレコードをソートすることにします.これを再ソートするとSort_が増加しますmerge_passes.実際、MySQLは別の一時ファイルで再ソートの結果を保存するので、通常はSort_merge_passesが増加した数値は、一時ファイルの数の2倍です.テンポラリファイルを使用しているので、速度が遅くなる可能性があります.Sort_を増やします.buffer_sizeはSort_を減らすmerge_passesと一時ファイルの作成回数.だが盲目的に増加したbuffer_sizeは必ずしもスピードを上げることはできません.How fast can you sort data with MySQLを参照してください.(導入元http://qroom.blogspot.com/2007/09/mysql-select-sort.html)を追加し、read_を追加rnd_buffer_size(3.2.3はrecord_rnd_buffer_size)の値は、ソートの操作にも少しメリットがあります.http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is- read_rnd_buffer_size/
11.ファイルオープン数(open_files)
mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 821   |
+---------------+-------+

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+

適切な設定:Open_files/open_files_Limit*100%<=75%正常
12. 表ロックの状況
mysql> show global status like 'table_locks%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 4257944 |
| Table_locks_waited    | 25182   |
+-----------------------+---------+

Table_locks_immediateは直ちにテーブルロックを解除する数を表し、Table_locks_waitedは、Table_locks_immediate/Table_locks_waited>5000は、InnoDBエンジンを採用することが望ましい、InnoDBは行ロック、MyISAMは表ロックであるため、高同時書き込みのアプリケーションInnoDBに対しては効果的である.
13.テーブルスキャン状況
mysql> show global status like 'handler_read%';
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Handler_read_first    | 108763    |
| Handler_read_key      | 92813521  |
| Handler_read_next     | 486650793 |
| Handler_read_prev     | 688726    |
| Handler_read_rnd      | 9321362   |
| Handler_read_rnd_next | 153086384 |
+-----------------------+-----------+

各フィールドの説明を参照http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html,
呼び出しサーバが完了したクエリー要求の回数:
mysql> show global status like 'com_select';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_select    | 2693147 |
+---------------+---------+

表スキャンレートの計算:表スキャンレート=Handler_read_rnd_next/Com_selectテーブルスキャン率が4000を超えると、テーブルスキャンが多すぎることを示し、インデックスが作成されずread_が増加する可能性があります.buffer_size値にはいくつかのメリットがありますが、8 MBを超えないほうがいいです.
MySQL ALTER構文の使い方&操作インデックスとフィールド
宏の妙用