mysqlsla来分析MYSQL的性能及索引

 
— 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

        root@localhost : 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         : 

        cacti@localhost : 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         : 

        cacti@localhost : 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         : 

        cacti@localhost : 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         : 

        cacti@localhost : 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         : 

        cacti@localhost : 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         : 

        cacti@localhost : 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 ___

 

详情请点击：http://www.yuandingsoft.com/resource/Mysql/2012-10-24/2692.html