SQL-MySQL Server日常メンテナンス:Log分析(mysqlsla)
49849 ワード
MySQL Server日常メンテナンス:Log分析(mysqlsla)
以前の記事では、mysqlreportというツールを使用してMySQLの現在の稼働状況を把握する方法について説明しました(
MySQL効能監視ツール--mysqlreport)では、MySQL Serverの健康状態やMySQL Serverの大部分がどのようなタイプのQueryを処理しているのかを理解するのに役立ちますが、もう一つの重要な質問には答えられません.特定のタイプのQueryの中で、MySQLは実際にCPUの演算時間をどのQueryに費やしているのでしょうか.この質問に答えるにはMySQLのログを分析しなければなりません.
大体、MySQLは3種類のLOGを提供しています. Binary Log:リポジトリに対する修正操作 をすべて記録する. General Log:サーバに送信されたすべてのClientを記録するQuery Slow Log:すべてのSlow Query を記録する
General Logを分析することで、MySQL Serverが最もよく実行しているQueryがどれらがあるかを明らかにすることができます.Slow Logを観察すると、MySQL Serverの効率が低下したのはどのQueryなのかを知ることができます.これらの情報を知ってから、私たちはMySQL Serverをさらに調整し、最適化する方法があります.例えば、適切なIndexでMySQLが最もよく実行するQueryの効率を高めたり、Slow Queryの原因(poor index、join...)を除去したりします.MySQL Serverのビジーレコードの状態によっては、General Log(またはSlow Log)が非常に膨大になる可能性があります.肉眼で分析するのは不可能なタスクであり、実際の方法は自分でScriptsを書いてLog分析を行うことです.自分でプログラムを開発するのに時間がかかりたくない場合は、Daniel Nichterが提供するmysqlslaを試してみてください.
公式サイト:
http://hackmysql.com/
ソフトウェアのダウンロード:
http://hackmysql.com/mysqlsla
ログ分析手順: MySQL ServerのGeneral LogとSlow Log を開く mysqlslaでログファイルを分析する 解読レポート MySQL Server Log分析
一、MySQL ServerのGeneral LogとSlow Logを開く
ログファイルを分析する前に、もちろん分析できるログファイルが必要です.MySQL ServerのGeneral LogとSlow Logを開くには、MySQL Serverのシステム設定ファイルを変更してMySQLを再起動する必要があります.
参照:
/etc/my.cnf:
[mysqld]
log=general-log
log-slow-queries=slow-log
MySQL Serverを再起動すると、MySQLのData Dirにgeneral-logとslow-logの2つのファイルが表示されるはずです.MySQL ServerのGeneral LogとSlow LogプリセットはLogotateを行わないので、自分でこの部分を処理することを覚えておいてください.そうしないと、Serverが忙しく録画している限り、General Logはすべてのハードディスク領域を使い果たす可能性があります.例えばLinuxシステムでは/etc/logrotate.dにmysqldファイルを追加し、内容は:
参照:
/var/log/mysqld.log/var/lib/mysql/general-log/var/lib/mysql/slow-log {
missingok
notifempty
sharedscripts
postrotate
/usr/bin/mysqladmin flush-logs
endscript
}
二、mysqlslaでLogファイルを分析する
mysqlslaは実はPerl Scriptsで、使い方はとても簡単で、文法は以下の通りです.
A.General Logの分析
参照:
mysqlsla -u root -p --flat --general general-log
--flat:Queryを分析するときにすべて小文字にすることを表します.つまり、大文字と小文字の違いは考慮に入れません
--General:分析するのはGeneral Logです
general-log:ここにgeneral-logファイル名を入力します.複数のログファイルがある場合は、カンマで区切ってください.たとえば、log 1、log 2...
生成されたサンプル・レポートは次のとおりです.(mysqlslaプリセットにはTop 10 Queryのみがリストされます)
PHP構文:
以前の記事では、mysqlreportというツールを使用してMySQLの現在の稼働状況を把握する方法について説明しました(
MySQL効能監視ツール--mysqlreport)では、MySQL Serverの健康状態やMySQL Serverの大部分がどのようなタイプのQueryを処理しているのかを理解するのに役立ちますが、もう一つの重要な質問には答えられません.特定のタイプのQueryの中で、MySQLは実際にCPUの演算時間をどのQueryに費やしているのでしょうか.この質問に答えるにはMySQLのログを分析しなければなりません.
大体、MySQLは3種類のLOGを提供しています.
General Logを分析することで、MySQL Serverが最もよく実行しているQueryがどれらがあるかを明らかにすることができます.Slow Logを観察すると、MySQL Serverの効率が低下したのはどのQueryなのかを知ることができます.これらの情報を知ってから、私たちはMySQL Serverをさらに調整し、最適化する方法があります.例えば、適切なIndexでMySQLが最もよく実行するQueryの効率を高めたり、Slow Queryの原因(poor index、join...)を除去したりします.MySQL Serverのビジーレコードの状態によっては、General Log(またはSlow Log)が非常に膨大になる可能性があります.肉眼で分析するのは不可能なタスクであり、実際の方法は自分でScriptsを書いてLog分析を行うことです.自分でプログラムを開発するのに時間がかかりたくない場合は、Daniel Nichterが提供するmysqlslaを試してみてください.
公式サイト:
http://hackmysql.com/
ソフトウェアのダウンロード:
http://hackmysql.com/mysqlsla
ログ分析手順:
一、MySQL ServerのGeneral LogとSlow Logを開く
ログファイルを分析する前に、もちろん分析できるログファイルが必要です.MySQL ServerのGeneral LogとSlow Logを開くには、MySQL Serverのシステム設定ファイルを変更してMySQLを再起動する必要があります.
参照:
/etc/my.cnf:
[mysqld]
log=general-log
log-slow-queries=slow-log
MySQL Serverを再起動すると、MySQLのData Dirにgeneral-logとslow-logの2つのファイルが表示されるはずです.MySQL ServerのGeneral LogとSlow LogプリセットはLogotateを行わないので、自分でこの部分を処理することを覚えておいてください.そうしないと、Serverが忙しく録画している限り、General Logはすべてのハードディスク領域を使い果たす可能性があります.例えばLinuxシステムでは/etc/logrotate.dにmysqldファイルを追加し、内容は:
参照:
/var/log/mysqld.log/var/lib/mysql/general-log/var/lib/mysql/slow-log {
missingok
notifempty
sharedscripts
postrotate
/usr/bin/mysqladmin flush-logs
endscript
}
二、mysqlslaでLogファイルを分析する
mysqlslaは実はPerl Scriptsで、使い方はとても簡単で、文法は以下の通りです.
A.General Logの分析
参照:
mysqlsla -u root -p --flat --general general-log
--flat:Queryを分析するときにすべて小文字にすることを表します.つまり、大文字と小文字の違いは考慮に入れません
--General:分析するのはGeneral Logです
general-log:ここにgeneral-logファイル名を入力します.複数のログファイルがある場合は、カンマで区切ってください.たとえば、log 1、log 2...
生成されたサンプル・レポートは次のとおりです.(mysqlslaプリセットにはTop 10 Queryのみがリストされます)
PHP構文:
<!---->Reading general log 'general-log'.
1926707 total queries, 4085 unique.
Sorting by 'c'.
__ 001 _______________________________________________________________________
Count : 291423 (15%)
Database : example
Query 1.....
__ 002 _______________________________________________________________________
Count : 271380 (14%)
Database : example
Query 2.....
__ 003 _______________________________________________________________________
Count : 193304 (10%)
Database : example
Query 3.....
__ 004 _______________________________________________________________________
Count : 172429 (9%)
Database : example
Query 4.....
__ 005 _______________________________________________________________________
Count : 132067 (7%)
Database : example
Query 5.....
__ 006 _______________________________________________________________________
Count : 112499 (6%)
Database : example
Query 6.....
__ 007 _______________________________________________________________________
Count : 84409 (4%)
Database : example
Query 7.....
__ 008 _______________________________________________________________________
Count : 65973 (3%)
Database : example
Query 8.....
__ 009 _______________________________________________________________________
Count : 40396 (2%)
Database : example
Query 9.....
__ 010 _______________________________________________________________________
Count : 38702 (2%)
Database : example
Query 10.....
そして、サーバが を しているのかを することができます.
B. Slow Log
:
mysqlsla-u root-p--flat--slow--ex--dbリポジトリ slow-log
:
--flat:Queryを するときにすべて にすることを します.つまり、 と の いは に れません
--slow: するのはSlow Logです
--ex:EXPLAINを してQueryを する
--dbリポジトリ :Slow Logは ずしもQueryが するDatabaseを するわけではありません.これにより、ServerがEXPLAINを う に が するため、ここで するDatabaseを で します.
slow-log:ここにslow-logファイル を します. のログファイルがある は、カンマで ってください. えば、log 1、log 2....
されたサンプル・レポートは のとおりです.(mysqlslaプリセットにはTop 10 Queryのみがリストされます)
PHP :<!---->Reading slow log 'slow-log'.
160 total queries, 21 unique.
Databases for Unknown: example
Sorting by 't'.
__ 001 _______________________________________________________________________
Count : 75 (46%)
Time (seconds): 1449 total, 19.32 avg, 12 to 26 max
95% of Time : 1348 total, 18.99 avg, 12 to 25 max
Lock (seconds): 445 total, 5.93 avg, 0 to 13 max
Rows sent : 247 avg, 92 to 250 max
Rows examined : 39787 avg, 27136 to 53228 max
User : example[example]@localhost/ (76%)
Database : example
Rows (EXPLAIN): 24494 produced, 24494 read
EXPLAIN :
id: 1
select_type: SIMPLE
table: SAMPLE
type: ref
possible_keys: SAMPLE
key: SAMPLE
key_len: 4
ref: const,const
rows: 24494
Extra: Using where; Using filesort
Query1 ..........
__ 002 _______________________________________________________________________
Count : 4 (2%)
Time (seconds): 659 total, 164 avg, 164 to 166 max
95% of Time : 493 total, 164 avg, 164 to 165 max
Lock (seconds): 0 total, 0 avg, 0 to 0 max
Rows sent : 53871729 avg, 53792839 to 53954842 max
Rows examined : 53871729 avg, 53792839 to 53954842 max
User : example[example]@localhost/ (23%)
Database : example
Rows (EXPLAIN): 54003520 produced, 54003520 read
EXPLAIN :
id: 1
select_type: SIMPLE
table: SAMPLE
type: ALL
possible_keys:
key:
key_len:
ref:
rows: 54003520
Extra:
Query2 ..........
__ 003 _______________________________________________________________________
Count : 30 (18%)
Time (seconds): 412 total, 13.73 avg, 6 to 29 max
95% of Time : 355 total, 12.68 avg, 6 to 28 max
Lock (seconds): 0 total, 0 avg, 0 to 0 max
Rows sent : 20475 avg, 3889 to 77241 max
Rows examined : 20475 avg, 3889 to 77241 max
User : example[example]@localhost/ (76%)
Database : example
Rows (EXPLAIN): 27060 produced, 27060 read
EXPLAIN :
id: 1
select_type: SIMPLE
table: SAMPLE
type: ref
possible_keys: SAMPLE
key: SAMPLE
key_len: 3
ref: const
rows: 27060
Extra:
Query3 ..........
__ 004 _______________________________________________________________________
Count : 20 (12%)
Time (seconds): 219 total, 10.95 avg, 10 to 12 max
95% of Time : 207 total, 10.89 avg, 10 to 12 max
Lock (seconds): 0 total, 0 avg, 0 to 0 max
Rows sent : 0 avg, 0 to 1 max
Rows examined : 1077422 avg, 1076385 to 1078557 max
User : example[example]@localhost/ (23%)
Database : example
Rows (EXPLAIN): 1078830 produced, 3236490 read
EXPLAIN :
id: 1
select_type: PRIMARY
table: SAMPLE
type: index
possible_keys: SAMPLE
key: SAMPLE
key_len: 8
ref:
rows: 1078830
Extra: Using where; Using index; Using temporary; Using filesort
id: 3
select_type: DEPENDENT SUBQUERY
table: SAMPLE
type: ref
possible_keys: SAMPLE
key: SAMPLE
key_len: 2
ref: const
rows: 1
Extra: Using where
id: 2
select_type: DEPENDENT SUBQUERY
table: SAMPLE
type: unique_subquery
possible_keys: PRIMARY,forumid
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using index; Using where
Query4 ..........
__ 005 _______________________________________________________________________
Count : 4 (2%)
Time (seconds): 75 total, 18.75 avg, 16 to 22 max
95% of Time : 53 total, 17.67 avg, 16 to 19 max
Lock (seconds): 0 total, 0 avg, 0 to 0 max
Rows sent : 1077174 avg, 1076128 to 1078321 max
Rows examined : 1077174 avg, 1076128 to 1078321 max
User : example[example]@localhost/ (23%)
Database : example
Rows (EXPLAIN): 1078830 produced, 1078830 read
EXPLAIN :
id: 1
select_type: SIMPLE
table: SAMPLE
type: ALL
possible_keys:
key:
key_len:
ref:
rows: 1078830
Extra:
Query5 ..........
__ 006 _______________________________________________________________________
Count : 4 (2%)
Time (seconds): 40 total, 10 avg, 10 to 10 max
95% of Time : 30 total, 10.00 avg, 10 to 10 max
Lock (seconds): 0 total, 0 avg, 0 to 0 max
Rows sent : 2207544 avg, 2205402 to 2209884 max
Rows examined : 2207544 avg, 2205402 to 2209884 max
User : example[example]@localhost/ (23%)
Database : example
Rows (EXPLAIN): 2211096 produced, 2211096 read
EXPLAIN :
id: 1
select_type: SIMPLE
table: SAMPLE
type: ALL
possible_keys:
key:
key_len:
ref:
rows: 2211096
Extra:
Query6 ..........
__ 007 _______________________________________________________________________
Count : 4 (2%)
Time (seconds): 36 total, 9 avg, 9 to 9 max
95% of Time : 27 total, 9.00 avg, 9 to 9 max
Lock (seconds): 0 total, 0 avg, 0 to 0 max
Rows sent : 0 avg, 0 to 0 max
Rows examined : 1827 avg, 1826 to 1828 max
User : example[example]@localhost/ (23%)
Database : example
Rows (EXPLAIN): 3656396 produced, 3658344 read
EXPLAIN :
id: 1
select_type: PRIMARY
table: SAMPLE
type: ref
possible_keys: SAMPLE
key: SAMPLE
key_len: 2
ref: const
rows: 1948
Extra: Using where
id: 2
select_type: DEPENDENT SUBQUERY
table: SAMPLE
type: ref
possible_keys: SAMPLE
key: award_id
key_len: 2
ref: const
rows: 1877
Extra: Using where
Query7 ..........
__ 008 _______________________________________________________________________
Count : 2 (1%)
Time (seconds): 36 total, 18 avg, 13 to 23 max
95% of Time : 13 total, 13.00 avg, 13 to 13 max
Lock (seconds): 6 total, 3 avg, 1 to 5 max
Rows sent : 250 avg, 250 to 250 max
Rows examined : 45386 avg, 40636 to 50136 max
User : example[example]@localhost/ (76%)
Database : example
Rows (EXPLAIN): 24494 produced, 24494 read
EXPLAIN :
id: 1
select_type: SIMPLE
table: SAMPLE
type: ref
possible_keys: SAMPLE
key: SAMPLE
key_len: 4
ref: const,const
rows: 24494
Extra: Using where; Using filesort
Query8 ..........
__ 009 _______________________________________________________________________
Count : 3 (1%)
Time (seconds): 30 total, 10 avg, 6 to 12 max
95% of Time : 18 total, 9.00 avg, 6 to 12 max
Lock (seconds): 1 total, 0.33 avg, 0 to 1 max
Rows sent : 0 avg, 0 to 0 max
Rows examined : 0 avg, 0 to 0 max
User : example[example]@localhost/ (76%)
Database : Unknown
Rows (EXPLAIN): EXPLAIN error: Not a SELECT statement.
EXPLAIN : EXPLAIN error: Not a SELECT statement.
Query9 ..........
__ 010 _______________________________________________________________________
Count : 3 (1%)
Time (seconds): 21 total, 7 avg, 6 to 8 max
95% of Time : 13 total, 6.50 avg, 6 to 7 max
Lock (seconds): 0 total, 0 avg, 0 to 0 max
Rows sent : 143522 avg, 121334 to 187844 max
Rows examined : 143522 avg, 121334 to 187844 max
User : example[example]@localhost/ (76%)
Database : example
Rows (EXPLAIN): 141085 produced, 141085 read
EXPLAIN :
id: 1
select_type: SIMPLE
table: SAMPLE
type: ref
possible_keys: SAMPLE
key: SAMPLE
key_len: 3
ref: const
rows: 141085
Extra: Using index
Query10 ..........
これらの があれば、 のステップはQueryの を い、Serverシステムパラメータとの を うことです.