【アーキテクチャ性能分析(1)】MySQL性能ボトルネック調査の位置づけ
リード:
OSレベルのチェック確認
まず、現在どのプロセスが負荷が高いのか、これらのプロセスカードがどこにあるのか、ボトルネックが何なのかを確認します.通常、サーバ上で最もボトルネックになりやすいのは、ディスクI/Oサブシステムです.これは、通常、読み書き速度が最も遅いためです.現在のPCIe SSDでも、ランダムI/Oの読み書き速度はメモリほど速くありません.もちろん、ディスクI/Oが遅くなる原因もいろいろありますが、どれが原因なのか確認する必要があります.
第一歩は、一般的に全体の負荷を見てみましょう.負荷が高いと、すべてのプロセスが遅くなるに違いありません.
負荷データを表示するコマンドwまたはsar-q 1を実行します.
sar-qの観察結果:
load averageは、現在のCPUでどれだけのタスクがキューに並んで待っているかを意味します.待つほど負荷が高くなり、データベースを走るサーバでは、一般的にload値が5を超えると、すでに高いと言えます.
loadの高さを引き起こす原因はいくつかあります.
一部のプロセス/サービスはより多くのCPUリソースを消費する(サービスはより多くの要求に応答するか、またはいくつかの応用ボトルネックがある).
比較的深刻なswapが発生した(使用可能な物理メモリが不足している).
比較的深刻な割り込みが発生する(SSDやネットワークの原因で割り込みが発生する);
ディスクI/Oが比較的遅い(CPUがディスクI/O要求をずっと待つことになる);
ボトルネックがどのサブシステムにあるかを判断するには、次のコマンドを実行します.
明らかに、前の2つのmysqldプロセスが全体的な負荷を高めた.また,Cpu(s)行の統計結果からも分かるように,%usと%waの値が高く,現在の大きなボトルネックはユーザプロセスで消費されるCPUおよびディスクI/O待ちにある可能性があることを示している.まずディスクI/Oの状況を分析します.
sar-dを実行してディスクI/Oが本当に大きいかどうかを確認します.
iotop(注意バージョン)を再利用して、どのプロセスで最も多くのディスクI/Oリソースが消費されているかを確認します.
ポート番号が3320のインスタンスで消費されるディスクI/Oリソースが多いことがわかりますが、このインスタンスでどのようなクエリーが走っているかを見てみましょう.
MySQLレベルチェック確認
まず、現在実行されているクエリーを見てみましょう.
スロークエリがまだ完了していないことが多く、slow query logからもこのようなSQLが発生する頻度が高いことがわかります.
これは非常に非効率なSQLの書き方で、プライマリ・キー全体をスキャンする必要がありますが、実際には最大値を取得するだけで、slow query logから次のように見えます.
Rows_sent: 1 Rows_examined:5502460は毎回500万行以上のデータをスキャンしますが、最大値を1つ読むだけで、効率が非常に低いです.
分析を経て、このSQLは少し簡単な改造をして1桁のミリ秒級で完成することができて、もとは150-180秒で完成することができて、N次方を昇格させました.
改造の方法は,クエリ結果を逆順序で並べ替え,最初のレコードを取得すればよい.従来のやり方では、結果を順に並べ替えて、最後のレコードを取ります.
MySQLが持参したコマンドで調べることもできます
スローSQLとmysqlの現在のデータベース接続数の表示方法は次のとおりです.command:queryは実行状態 command:sleepはアイドル状態 現在の実行時間が50 msを超えるスローSQLのクエリ
すべての接続を表示KILLから接続 KILL 256115
クエリこのデータベースに接続されているサーバのリクエスト数
ブロックされたプロセスを表示し、killを削除します. KILL 811478
（ / ）， ， MySQL 。
OSレベルのチェック確認
まず、現在どのプロセスが負荷が高いのか、これらのプロセスカードがどこにあるのか、ボトルネックが何なのかを確認します.通常、サーバ上で最もボトルネックになりやすいのは、ディスクI/Oサブシステムです.これは、通常、読み書き速度が最も遅いためです.現在のPCIe SSDでも、ランダムI/Oの読み書き速度はメモリほど速くありません.もちろん、ディスクI/Oが遅くなる原因もいろいろありますが、どれが原因なのか確認する必要があります.
第一歩は、一般的に全体の負荷を見てみましょう.負荷が高いと、すべてのプロセスが遅くなるに違いありません.
負荷データを表示するコマンドwまたはsar-q 1を実行します.
[ly@imysql.com:~ ]# w
11:52:58 up 702 days, 56 min, 1 user, load average: 7.20, 6.70, 6.47
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
root pts/0 1.xx.xx.xx 11:51 0.00s 0.03s 0.00s wsar-qの観察結果:
[ly@imysql.com:~ ]# sar -q 1
Linux 2.6.32-431.el6.x86_64 (ly.imysql.com) 01/13/2016 _x86_64_ (24 CPU)
02:51:18 PM runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15 blocked
02:51:19 PM 4 2305 6.41 6.98 7.12 3
02:51:20 PM 2 2301 6.41 6.98 7.12 4
02:51:21 PM 0 2300 6.41 6.98 7.12 5
02:51:22 PM 6 2301 6.41 6.98 7.12 8
02:51:23 PM 2 2290 6.41 6.98 7.12 8load averageは、現在のCPUでどれだけのタスクがキューに並んで待っているかを意味します.待つほど負荷が高くなり、データベースを走るサーバでは、一般的にload値が5を超えると、すでに高いと言えます.
loadの高さを引き起こす原因はいくつかあります.
一部のプロセス/サービスはより多くのCPUリソースを消費する(サービスはより多くの要求に応答するか、またはいくつかの応用ボトルネックがある).
比較的深刻なswapが発生した(使用可能な物理メモリが不足している).
比較的深刻な割り込みが発生する(SSDやネットワークの原因で割り込みが発生する);
ディスクI/Oが比較的遅い(CPUがディスクI/O要求をずっと待つことになる);
ボトルネックがどのサブシステムにあるかを判断するには、次のコマンドを実行します.
[ly@imysql.com:~ ]# top
top - 11:53:04 up 702 days, 56 min, 1 user, load average: 7.18, 6.70, 6.47
Tasks: 576 total, 1 running, 575 sleeping, 0 stopped, 0 zombie
Cpu(s): 7.7%us, 3.4%sy, 0.0%ni, 77.6%id, 11.0%wa, 0.0%hi, 0.3%si, 0.0%st
Mem: 49374024k total, 32018844k used, 17355180k free, 115416k buffers
Swap: 16777208k total, 117612k used, 16659596k free, 5689020k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14165 mysql 20 0 8822m 3.1g 4672 S 162.3 6.6 89839:59 mysqld
40610 mysql 20 0 25.6g 14g 8336 S 121.7 31.5 282809:08 mysqld
49023 mysql 20 0 16.9g 5.1g 4772 S 4.6 10.8 34940:09 mysqld明らかに、前の2つのmysqldプロセスが全体的な負荷を高めた.また,Cpu(s)行の統計結果からも分かるように,%usと%waの値が高く,現在の大きなボトルネックはユーザプロセスで消費されるCPUおよびディスクI/O待ちにある可能性があることを示している.まずディスクI/Oの状況を分析します.
sar-dを実行してディスクI/Oが本当に大きいかどうかを確認します.
[ly@imysql.com:~ ]# sar -d 1
Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com) 01/13/2016 _x86_64_ (24 CPU)
11:54:32 AM dev8-0 5338.00 162784.00 1394.00 30.76 5.24 0.98 0.19 100.00
11:54:33 AM dev8-0 5134.00 148032.00 32365.00 35.14 6.93 1.34 0.19 100.10
11:54:34 AM dev8-0 5233.00 161376.00 996.00 31.03 9.77 1.88 0.19 100.00
11:54:35 AM dev8-0 4566.00 139232.00 1166.00 30.75 5.37 1.18 0.22 100.00
11:54:36 AM dev8-0 4665.00 145920.00 630.00 31.41 5.94 1.27 0.21 100.00
11:54:37 AM dev8-0 4994.00 156544.00 546.00 31.46 7.07 1.42 0.20 100.00iotop(注意バージョン)を再利用して、どのプロセスで最も多くのディスクI/Oリソースが消費されているかを確認します.
[ly@imysql.com:~ ]# iotop
Total DISK READ: 60.38 M/s | Total DISK WRITE: 640.34 K/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
16397 be/4 mysql 8.92 M/s 0.00 B/s 0.00 % 94.77 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
7295 be/4 mysql 10.98 M/s 0.00 B/s 0.00 % 93.59 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
14295 be/4 mysql 10.50 M/s 0.00 B/s 0.00 % 93.57 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
14288 be/4 mysql 14.30 M/s 0.00 B/s 0.00 % 91.86 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
14292 be/4 mysql 14.37 M/s 0.00 B/s 0.00 % 91.23 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320ポート番号が3320のインスタンスで消費されるディスクI/Oリソースが多いことがわかりますが、このインスタンスでどのようなクエリーが走っているかを見てみましょう.
MySQLレベルチェック確認
まず、現在実行されているクエリーを見てみましょう.
[ly@imysql.com(db)]> mysqladmin pr|grep -v Sleep
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| Id |User| Host | db |Command|Time | State | Info |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| 25 | x | 10.x:8519 | db | Query | 68 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404612 order by Fvideoid) t1 |
| 26 | x | 10.x:8520 | db | Query | 65 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>484915 order by Fvideoid) t1 |
| 28 | x | 10.x:8522 | db | Query | 130 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404641 order by Fvideoid) t1 |
| 27 | x | 10.x:8521 | db | Query | 167 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324157 order by Fvideoid) t1 |
| 36 | x | 10.x:8727 | db | Query | 174 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324346 order by Fvideoid) t1 |スロークエリがまだ完了していないことが多く、slow query logからもこのようなSQLが発生する頻度が高いことがわかります.
これは非常に非効率なSQLの書き方で、プライマリ・キー全体をスキャンする必要がありますが、実際には最大値を取得するだけで、slow query logから次のように見えます.
Rows_sent: 1 Rows_examined:5502460は毎回500万行以上のデータをスキャンしますが、最大値を1つ読むだけで、効率が非常に低いです.
分析を経て、このSQLは少し簡単な改造をして1桁のミリ秒級で完成することができて、もとは150-180秒で完成することができて、N次方を昇格させました.
改造の方法は,クエリ結果を逆順序で並べ替え,最初のレコードを取得すればよい.従来のやり方では、結果を順に並べ替えて、最後のレコードを取ります.
MySQLが持参したコマンドで調べることもできます
スローSQLとmysqlの現在のデータベース接続数の表示方法は次のとおりです.
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 50 AND command = 'query' ORDER BY state DESC LIMIT 0,10000すべての接続を表示
SHOW FULL PROCESSLISTクエリこのデータベースに接続されているサーバのリクエスト数
SELECT hostid,COUNT(hostid) FROM
(SELECT SUBSTRING_INDEX(HOST,':',1) AS hostid FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 5 AND command = 'sleep' LIMIT 0,10000) AS hosttable GROUP BY hostid ORDER BY COUNT(hostid) DESCブロックされたプロセスを表示し、killを削除します.
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command !='Sleep' LIMIT 100000