mysqlsla来分析MYSQL的性能及索引

kider

出处：http://hi.baidu.com/linux_life/blog/item/41435f318de02da05edf0e94.html

— Slow log: mysqlsla -lt slow slow.log
— General log: mysqlsla -lt general general.log
— Binary log: mysqlbinlog bin.log | mysqlsla -lt binary


hackmysql.com推出的一款MySQL的日志分析工具
整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等.
格式说明如下:
总查询次数 (queries total), 去重后的sql数量 (unique)
输出报表的内容排序(sorted by)
最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.
Count, sql的执行次数及占总的slow log数量的百分比.
Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.
95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.
Lock Time, 等待锁的时间.
95% of Lock , 95%的慢sql等待锁时间.
Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.
Rows examined, 扫描的行数量.
Database, 属于哪个数据库
Users, 哪个用户,IP, 占到所有用户执行的sql百分比
Query abstract, 抽象后的sql语句
Query sample, sql语句
除了以上的输出, 官方还提供了很多定制化参数, 是一款不可多得的好工具.

在之前的文章中已有提过如何利用 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

二、以 mysqlsla 分析 Log 档案

    mysqlsla 其实是一支 Perl Scripts，使用方式很间单，语法如下：
    A. 分析 General Log
       mysqlsla -lt slow slow.log -R print-unique -mf "db=foo" -sf "+SELECT"
   
   
   
    然后您就可以大致上了解 Server 都在处理些什么东西。

    B. 分析 Slow Log
    引用:
mysqlsla --user=root --password=bjzayh -ex --socket=/tmp/mysql.sock -lt slow slow.log
mysqlsla --user=root --password=bjzayh -ex --socket=/tmp/mysql.sock -lt general /data/mysql/test.log
    引用:

    --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）
[root@test mysql]# mysqlsla --user=root --password=bjzayh -ex --socket=/tmp/mysql.sock -lt general /data/mysql/test.log
Report for general logs: /data/mysql/test.log
16.75k queries total, 519 unique
Sorted by 'c_sum'

______________________________________________________________________ 001 ___
Count         : 5.63k (33.63%)
Connection ID : 281
Database      :
Users         :
        root@192.168.0.145 : 65.15% (3670) of query, 24.05% (4028) of all users
        @ : 20.22% (1139) of query, 6.84% (1145) of all users
        root@192.168.0.55 : 14.52% (818) of query, 5.67% (950) of all users
        [email=root@localhost]root@localhost[/email] : 0.11% (6) of query, 5.91% (990) of all users
EXPLAIN       : Not a SELECT statement
Query abstract:
SET autocommit=N
Query sample:
SET autocommit=1
______________________________________________________________________ 002 ___
Count         : 2.92k (17.43%)
Connection ID : 424
Database      : cacti
Users         :
        [email=cacti@localhost]cacti@localhost[/email] : 100.00% (2920) of query, 57.46% (9625) of all users
EXPLAIN       : Not a SELECT statement
Query abstract:
INSERT INTO poller_output (local_data_id, rrd_name, time, output) VALUES (N, 'S', 'S', 'S')1
Query sample:
insert into poller_output (local_data_id, rrd_name, time, output) values (8, 'users', '2009-03-10 10:30:01', '1')
______________________________________________________________________ 003 ___
Count         : 2.92k (17.43%)
Connection ID : 423
Database      : cacti
Users         :
        [email=cacti@localhost]cacti@localhost[/email] : 100.00% (2920) of query, 57.46% (9625) of all users
EXPLAIN       : Not a SELECT statement
Query abstract:
DELETE FROM poller_output WHERE local_data_id='S' AND rrd_name='S' AND time='S'
Query sample:
delete from poller_output where local_data_id='18' and rrd_name='mem_free' and time='2009-03-10 10:30:01'
______________________________________________________________________ 004 ___
Count         : 980 (5.85%)
Connection ID : 423
Database      : cacti
Users         :
        [email=cacti@localhost]cacti@localhost[/email] : 100.00% (980) of query, 57.46% (9625) of all users
EXPLAIN       : 1 produced, 1 read
        id: 1
        select_type: SIMPLE
        table: settings
        type: const
        possible_keys: PRIMARY
        key: PRIMARY
        key_len: 152
        ref: const
        rows: 1
        Extra:
Query abstract:
SELECT value FROM settings WHERE name='S'
Query sample:
select value from settings where name='log_verbosity'
______________________________________________________________________ 005 ___
Count         : 960 (5.73%)
Connection ID : 425
Database      : cacti
Users         :
        [email=cacti@localhost]cacti@localhost[/email] : 100.00% (960) of query, 57.46% (9625) of all users
EXPLAIN       : 1 produced, 1 read
        id: 1
        select_type: SIMPLE
        table: host_snmp_cache
        type: ref
        possible_keys: PRIMARY,host_id,snmp_index
        key: host_id
        key_len: 155
        ref: const,const
        rows: 8
        Extra: Using where
Query abstract:
SELECT field_value FROM host_snmp_cache WHERE host_id=N AND field_name='S' AND snmp_index='S'
Query sample:
select field_value from host_snmp_cache where host_id=2 and field_name='hrStorageAllocationUnits' and snmp_index='1'
______________________________________________________________________ 006 ___
Count         : 512 (3.06%)
Connection ID : 423
Database      : cacti
Users         :
        [email=cacti@localhost]cacti@localhost[/email] : 100.00% (512) of query, 57.46% (9625) of all users
EXPLAIN       : 1 produced, 1 read
        id: 1
        select_type: SIMPLE
        table: plugin_hooks
        type: ref
        possible_keys: hook,status
        key: hook
        key_len: 194
        ref: const
        rows: 1
        Extra: Using where
Query abstract:
SELECT name, file, function FROM plugin_hooks WHERE status = N AND hook = 'S'
Query sample:
SELECT name, file, function FROM plugin_hooks WHERE status = 1 AND hook = 'config_arrays'
______________________________________________________________________ 007 ___
Count         : 191 (1.14%)
Connection ID : 423
Database      : cacti
Users         :
        [email=cacti@localhost]cacti@localhost[/email] : 100.00% (191) of query, 57.46% (9625) of all users
EXPLAIN       : 1 produced, 1 read
        id: 1
        select_type: SIMPLE
        table: poller_time
        type: system
        possible_keys:
        key:
        key_len:
        ref:
        rows: 1
        Extra:
Query abstract:
SELECT poller_id,end_time FROM poller_time WHERE poller_id=N
Query sample:
select poller_id,end_time from poller_time where poller_id=0
______________________________________________________________________ 008 ___
Count         : 181 (1.08%)
Connection ID : 423
Database      : cacti
Users         :
        [email=cacti@localhost]cacti@localhost[/email] : 100.00% (181) of query, 57.46% (9625) of all users
EXPLAIN       : 1 produced, 1 read
        id: 1
        select_type: SIMPLE
        table:
        type:
        possible_keys:
        key:
        key_len:
        ref:
        rows:
        Extra: Impossible WHERE noticed after reading const tables
Query abstract:
SELECT poller_output.output, poller_output.time, poller_output.local_data_id, poller_item.rrd_path, poller_item.rrd_name, poller_item.rrd_num FROM (poller_output,poller_item) WHERE (poller_output.local_data_id=poller_item.local_data_id AND poller_output.rrd_name=poller_item.rrd_name) LIMIT N
Query sample:
select poller_output.output, poller_output.time, poller_output.local_data_id, poller_item.rrd_path, poller_item.rrd_name, poller_item.rrd_num from (poller_output,poller_item) where (poller_output.local_data_id=poller_item.local_data_id and poller_output.rrd_name=poller_item.rrd_name) LIMIT 10000
______________________________________________________________________ 009 ___
Count         : 180 (1.07%)
Connection ID : 424
Database      : cacti
Users         :
        [email=cacti@localhost]cacti@localhost[/email] : 100.00% (180) of query, 57.46% (9625) of all users
EXPLAIN       : Not a SELECT statement
Query abstract:
UPDATE poller_reindex SET assert_value='S' WHERE host_id='S' AND data_query_id='S' AND arg1='S'
Query sample:
update poller_reindex set assert_value='23113189' where host_id='2' and data_query_id='1' and arg1='.1.3.6.1.2.1.1.3.0'
______________________________________________________________________ 010 ___
Count         : 104 (0.62%)
Connection ID : 509
Database      :
Users         :
        [email=root@localhost]root@localhost[/email] : 99.04% (103) of query, 5.91% (990) of all users
        root@192.168.0.35 : 0.96% (1) of query, 0.08% (13) of all users
EXPLAIN       : Not a SELECT statement
Query abstract:
USE cacti
Query sample:
use `cacti`
[root@test mysql]#
有了这些信息后，下一步就是要进行 Query 的最佳化，与 Server 系统参数的调校。