`

SQL - MySQL Server 日常維護: Log 分析(mysqlsla)

阅读更多
MySQL Server 日常維護: Log 分析(mysqlsla)

在之前的文章中已有提過如何利用 mysqlreport 這套工具來掌握 MySQL 目前的運作狀況(MySQL 效能監控工具--mysqlreport),它可以協助我們瞭解 MySQL Server 的健康狀況以及 MySQL Server 大部份時間在處理什麼類型的 Query,但還有一個關鍵的問題沒有回答,就是在那些特定類型的 Query 中,MySQL 實際上到底是把 CPU 運算時間花在哪些 Query 上?若想要回答這個問題就必須要分析 MySQL 的 Log 才能得知。

大致上來說,MySQL 提供三大類的 LOG:
  1. Binary Log:記錄所有對於資料庫的修改操作
  2. General Log:記錄所有 Client 發送到 Server 的 Query
  3. Slow Log:記錄所有的 Slow Query

藉由分析 General Log,我們可以清楚的得知 MySQL Server 最常執行的 Query 有哪些;觀察 Slow Log 則可以讓我們得知到底是哪些 Query 造成 MySQL Server 效能低落。知道了這些資訊以後我們才有辦法對 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

Log 分析步驟:
  1. 開啟 MySQL Server 的 General Log 與 Slow Log
  2. 以 mysqlsla 分析 Log 檔案
  3. 解讀報表



MySQL Server Log 分析

一、開啟 MySQL Server 的 General Log 與 Slow Log

要分析 Log 檔案之前,你當然要先有可以分析的 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 這二個檔案。請注意,MySQL Server 的 General Log 與 Slow Log 預設不會進行 Logrotate,因此您要記得自己處理這個部份,不然的話只要你的 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 檔案請使用逗號分隔,例如 log1,log2...

所產生的範例報表如下:(mysqlsla 預設只會列出 Top 10 Query)
PHP 語法:
<!---->Reading general log 'general-log'.
1926707 total queries4085 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.
.... 
<!---->

然後您就可以大致上瞭解 Server 都在處理些什麼東西。



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 檔案請使用逗號分隔,例如 log1,log2...。

所產生的範例報表如下:(mysqlsla 預設只會列出 Top 10 Query)
PHP 語法:
<!---->Reading slow log 'slow-log'.
160 total queries21 unique.
Databases for Unknownexample 
Sorting by 
't'.

__ 001 _______________________________________________________________________

Count         
75 (46%)
Time (seconds): 1449 total19.32 avg12 to 26 max
95
of Time   1348 total18.99 avg12 to 25 max
Lock 
(seconds): 445 total5.93 avg0 to 13 max
Rows sent     
247 avg92 to 250 max
Rows examined 
39787 avg27136 to 53228 max
User          
example[example]@localhost/ (76%)
Database      example
Rows 
(EXPLAIN): 24494 produced24494 read
EXPLAIN       

id1
select_type
SIMPLE
table
SAMPLE
type
ref
possible_keys
SAMPLE
key
SAMPLE
key_len
4
ref
: const,const
rows24494
Extra
Using whereUsing filesort


Query1 
..........

__ 002 _______________________________________________________________________

Count         
(2%)
Time (seconds): 659 total164 avg164 to 166 max
95
of Time   493 total164 avg164 to 165 max
Lock 
(seconds): 0 total0 avg0 to 0 max
Rows sent     
53871729 avg53792839 to 53954842 max
Rows examined 
53871729 avg53792839 to 53954842 max
User          
example[example]@localhost/ (23%)
Database      example
Rows 
(EXPLAIN): 54003520 produced54003520 read
EXPLAIN       

id1
select_type
SIMPLE
table
SAMPLE
type
ALL
possible_keys

key
key_len
ref
rows54003520
Extra



Query2 ..........

__ 003 _______________________________________________________________________

Count         
30 (18%)
Time (seconds): 412 total13.73 avg6 to 29 max
95
of Time   355 total12.68 avg6 to 28 max
Lock 
(seconds): 0 total0 avg0 to 0 max
Rows sent     
20475 avg3889 to 77241 max
Rows examined 
20475 avg3889 to 77241 max
User          
example[example]@localhost/ (76%)
Database      example
Rows 
(EXPLAIN): 27060 produced27060 read
EXPLAIN       

id1
select_type
SIMPLE
table
SAMPLE
type
ref
possible_keys
SAMPLE
key
SAMPLE
key_len
3
ref
: const
rows27060
Extra



Query3 ..........

__ 004 _______________________________________________________________________

Count         
20 (12%)
Time (seconds): 219 total10.95 avg10 to 12 max
95
of Time   207 total10.89 avg10 to 12 max
Lock 
(seconds): 0 total0 avg0 to 0 max
Rows sent     
0 avg0 to 1 max
Rows examined 
1077422 avg1076385 to 1078557 max
User          
example[example]@localhost/ (23%)
Database      example
Rows 
(EXPLAIN): 1078830 produced3236490 read
EXPLAIN       

id1
select_type
PRIMARY
table
SAMPLE
type
index
possible_keys
SAMPLE
key
SAMPLE
key_len
8
ref

rows1078830
Extra
Using whereUsing indexUsing temporaryUsing filesort

id
3
select_type
DEPENDENT SUBQUERY
table
SAMPLE
type
ref
possible_keys
SAMPLE
key
SAMPLE
key_len
2
ref
: const
rows1
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 indexUsing where


Query4 
..........

__ 005 _______________________________________________________________________

Count         
(2%)
Time (seconds): 75 total18.75 avg16 to 22 max
95
of Time   53 total17.67 avg16 to 19 max
Lock 
(seconds): 0 total0 avg0 to 0 max
Rows sent     
1077174 avg1076128 to 1078321 max
Rows examined 
1077174 avg1076128 to 1078321 max
User          
example[example]@localhost/ (23%)
Database      example
Rows 
(EXPLAIN): 1078830 produced1078830 read
EXPLAIN       

id1
select_type
SIMPLE
table
SAMPLE
type
ALL
possible_keys

key
key_len
ref
rows1078830
Extra



Query5 ..........

__ 006 _______________________________________________________________________

Count         
(2%)
Time (seconds): 40 total10 avg10 to 10 max
95
of Time   30 total10.00 avg10 to 10 max
Lock 
(seconds): 0 total0 avg0 to 0 max
Rows sent     
2207544 avg2205402 to 2209884 max
Rows examined 
2207544 avg2205402 to 2209884 max
User          
example[example]@localhost/ (23%)
Database      example
Rows 
(EXPLAIN): 2211096 produced2211096 read
EXPLAIN       

id1
select_type
SIMPLE
table
SAMPLE
type
ALL
possible_keys

key
key_len
ref
rows2211096
Extra



Query6 ..........

__ 007 _______________________________________________________________________

Count         
(2%)
Time (seconds): 36 total9 avg9 to 9 max
95
of Time   27 total9.00 avg9 to 9 max
Lock 
(seconds): 0 total0 avg0 to 0 max
Rows sent     
0 avg0 to 0 max
Rows examined 
1827 avg1826 to 1828 max
User          
example[example]@localhost/ (23%)
Database      example
Rows 
(EXPLAIN): 3656396 produced3658344 read
EXPLAIN       

id1
select_type
PRIMARY
table
SAMPLE
type
ref
possible_keys
SAMPLE
key
SAMPLE
key_len
2
ref
: const
rows1948
Extra
Using where

id
2
select_type
DEPENDENT SUBQUERY
table
SAMPLE
type
ref
possible_keys
SAMPLE
key
award_id
key_len
2
ref
: const
rows1877
Extra
Using where


Query7 
..........

__ 008 _______________________________________________________________________

Count         
(1%)
Time (seconds): 36 total18 avg13 to 23 max
95
of Time   13 total13.00 avg13 to 13 max
Lock 
(seconds): 6 total3 avg1 to 5 max
Rows sent     
250 avg250 to 250 max
Rows examined 
45386 avg40636 to 50136 max
User          
example[example]@localhost/ (76%)
Database      example
Rows 
(EXPLAIN): 24494 produced24494 read
EXPLAIN       

id1
select_type
SIMPLE
table
SAMPLE
type
ref
possible_keys
SAMPLE
key
SAMPLE
key_len
4
ref
: const,const
rows24494
Extra
Using whereUsing filesort


Query8 
..........

__ 009 _______________________________________________________________________

Count         
(1%)
Time (seconds): 30 total10 avg6 to 12 max
95
of Time   18 total9.00 avg6 to 12 max
Lock 
(seconds): 1 total0.33 avg0 to 1 max
Rows sent     
0 avg0 to 0 max
Rows examined 
0 avg0 to 0 max
User          
example[example]@localhost/ (76%)
Database      Unknown
Rows 
(EXPLAIN): EXPLAIN errorNot a SELECT statement.
EXPLAIN       EXPLAIN errorNot a SELECT statement.

Query9 ..........

__ 010 _______________________________________________________________________

Count         
(1%)
Time (seconds): 21 total7 avg6 to 8 max
95
of Time   13 total6.50 avg6 to 7 max
Lock 
(seconds): 0 total0 avg0 to 0 max
Rows sent     
143522 avg121334 to 187844 max
Rows examined 
143522 avg121334 to 187844 max
User          
example[example]@localhost/ (76%)
Database      example
Rows 
(EXPLAIN): 141085 produced141085 read
EXPLAIN       

id1
select_type
SIMPLE
table
SAMPLE
type
ref
possible_keys
SAMPLE
key
SAMPLE
key_len
3
ref
: const
rows141085
Extra
Using index


Query10 
.......... 
<!---->
有了這些資訊後,下一步就是要進行 Query 的最佳化,與 Server 系統參數的調校。
分享到:
评论

相关推荐

    五款常用mysql slow log分析工具

    标题:五款常用mysql slow log分析工具 描述:本文对五款常用的MySQL慢查询日志分析工具进行深入解析,旨在帮助DBA、开发者和运维人员更有效地定位和优化数据库性能瓶颈。 ### MySQL Slow Log概念 MySQL慢查询日志...

    mysqlsla.rar

    在日常数据库维护中,定期使用MySQLSLA分析慢查询日志,可以帮助我们持续监控数据库性能,及时发现并解决潜在问题,从而提升系统整体的响应速度和用户体验。 总结来说,MySQLSLA是一款强大的MySQL慢查询日志分析...

    mysqlsla-2.03

    - 下载`mysqlsla-2.03.tar.tar`文件,并使用`tar -xvf mysqlsla-2.03.tar.tar`解压。 - 进入解压后的目录,运行`./configure`来检查依赖项并配置编译环境。 - 使用`make`命令进行编译,然后使用`sudo make ...

    MySQL日志分析(包括工具)

    查询日志(`log`)记录了所有客户端发送到MySQL服务器的SQL语句,包括SELECT语句。虽然它能提供详尽的数据操作记录,但由于可能会对性能造成一定影响,因此通常建议在非必要情况下关闭。 - **记录内容**:所有SQL...

    mysqlsla2.03

    mysqlsla(SQL SLA,Service Level Agreement for MySQL)是一个命令行工具,用于分析MySQL慢查询日志。它能提供丰富的统计信息,包括查询执行时间分布、最耗时的查询、锁定时间等。安装mysqlsla通常通过以下步骤: ...

    mysqlsla安装

    除了`mysqldumpslow`,MySQLSLA(MySQL Slow Log Analyser)是一个更强大的分析工具,它是用Perl编写的。在安装MySQLSLA之前,需要确保系统已经安装了Perl。在大多数Linux发行版中,可以通过`yum install perl`或`...

    mysqlsla-2.0.3

    MySQLSLA(MySQL Slow Log Analyzer)是用于分析MySQL数据库慢查询日志的工具,它能够帮助DBA(数据库管理员)优化数据库性能。标题中的“mysqlsla-2.0.3”指的是该软件的特定版本,2.0.3,这通常意味着它包含了前一...

    五款常用mysql slow log分析工具的比较分析

    比较的五款常用工具mysqldumpslow, mysqlsla, myprofi, mysql-explain-slow-log, mysqllogfilter mysqldumpslow, mysql官方提供的慢查询日志分析工具. 输出图表如下: 主要功能是, 统计不同慢sql的 出现次数(Count...

    mysql慢查询日志的使用

    MySQL慢查询日志是MySQL提供的一种日志记录方式,用于记录所有执行时间超过指定阈值(默认10秒)的SQL语句,帮助开发者和DBA定位数据库性能瓶颈,优化SQL查询效率。 #### 开启与配置 1. **开启慢查询日志:** - ...

    五款常用mysqlslowlog分析工具的比较.pdf

    MySQL Slow Query Log是一种重要的性能监控工具,用于记录执行时间超过预设阈值的SQL查询,帮助DBA识别和优化性能瓶颈。本篇文章将对比五款常用的MySQL Slow Log分析工具,以便选择最适合您需求的工具。 1. **...

    MySQL日志分析软件mysqlsla的安装和使用教程

    [root@localhost tmp]# wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz --19:45:45-- http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz Resolving hackmysql.com... 64.13.232.157 Connecting to ...

    mysqlsla慢查询分析工具使用笔记

    总之,MySQLsla是一款强大的MySQL慢查询分析工具,它提供了一种有效的方式来监控和优化数据库性能,对于数据库管理员来说,它是日常维护和性能调优的重要助手。通过熟练掌握其使用方法,可以显著提升数据库系统的...

    知数堂MySQL DBA面试秘籍.zip

    《知数堂MySQL DBA面试秘籍》是一份专为MySQL数据库管理员(DBA)...通过《知数堂MySQL DBA面试秘籍》,读者不仅可以为面试做好充分准备,还能在实际工作中提升MySQL数据库的管理和维护能力,成为一名出色的MySQL DBA。

    MySQL 日志分析工具

    Count, sql的执行次数及占总的slow log数量的百分比. Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比. 95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间. Lock ...

Global site tag (gtag.js) - Google Analytics