mysqlsla

mysqlsla parses, filters, analyzes and sorts MySQL slow, general, binary and microslow patched logs in order to create a customizable report of the queries and their meta-property values.

Since these reports are customizable, they can be used for human consumption or be fed into other scripts to further analyze the queries. For example, to profile with (a script from Baron Schwartz's ) every unique SELECT statement using database foo from a slow log:

         mysqlsla -lt slow slow.log -R print-unique -mf "db=foo" -sf "+SELECT" | \
         mk-query-profiler -separate -database foo

 

In brief, mysqlsla is a liaison allowing other scripts easy access to queries from a MySQL log. For a quick introduction to what mysqlsla is capable of doing, take a glance at the .

The mysqlsla Library

» : Covers installation, basic usage, reports, special topics and advancing

» Documentation: Complete list of command line options, limitations, future features and bugs

» : Overview of mysqlsla v2 log filters

» : Creating and customizing mysqlsla v2 standard reports

» : Working with mysqlsla v2 log replays

» : Creating mysqlsla v2 and MySQL::Log::ParseFilter user-defined logs

Download mysqlsla

Current version: mysqlsla-2.03 ()

• Installer + man page:
• Script only: wget

v1.x Retired

Looking for mysqlsla v1.7a or 1.8-DEBUG? The v1 branch has been retired and is no longer supported. The older versions can still be accessed at

FAQ：

1. 安装工具后，运行时出现如下提示：

# mysqlsla -lt slow slow.log
Can't locate DBI.pm in @INC (@INC contains: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.8/i386-linux-thread-multi /usr/lib/perl5/5.8.8 .) at /usr/local/bin/mysqlsla line 2095.
BEGIN failed--compilation aborted at /usr/local/bin/mysqlsla line 2095.

原因:在于相关模块没有安装

解决办法：

# perl -MCPAN -e 'install DBI'

2. 它的基本用法：

# mysqlsla -lt slow slow.log -sf ”+SELECT” -db tuniu –top 100 –sort t_su

主要参数含义：

lt：表示日志类型，有slow, general, binary, msl, udl。
sf：[+-][TYPE]，[TYPE]有SELECT, CREATE, DROP, UPDATE, INSERT，例如"+SELECT,INSERT"，不出现的默认是-，即不包括。
db：要处理哪个库的日志。
top：表示取按规则排序的前多少条。
sort：按某种规则排序，t_sum按总时间排序， c_sum按总次数排序。 

Count         : 2.92k (17.43%) //出现的次数和占所有慢查的比例（按排序规则算）
Connection ID : 424
Database      : cacti //哪个数据库的SQL
Users         :
      cacti@localhost : 100.00% (2920) of query, 57.46% (9625) of all users //哪个用户产生的SQL
EXPLAIN       : Not a SELECT statement //如果启用-ex，会产生EXPLAIN。
Query abstract: //抽象过的SQL
  INSERT INTO poller_output (local_data_id, rrd_name, time, output) VALUES (N, 'S', 'S', 'S')
Query sample: //一条例子
  insert into poller_output (local_data_id, rrd_name, time, output) values (8, 'users', '2009-12-10 10:30:01', '1')

根据这些结果，我们可以判断哪些SQL是高并发的，优先调优。利用例子可以进行执行计划的调优。

# mysqlsla -lt slow localhost-slow.log
Report for slow logs: localhost-slow.log
9.91M queries total, 616 unique
Sorted by 't_sum'
Grand Totals: Time 313.59T s, Lock 9.60k s, Rows sent 700.72M, Rows Examined 68.89G


______________________________________________________________________ 001 ___
Count         : 51  (0.00%)
Time          : 129127208516157 s total, 2531906049336.41 s avg, 1.064828 s to 18446744073709.5 s max  (41.18%)
  95% of Time : 73786976295028.5 s total, 1537228672813.09 s avg, 1.064828 s to 18446744073709.4 s max
Lock Time (s) : 2.588 ms total, 51 μs avg, 22 μs to 908 μs max  (0.00%)
  95% of Lock : 1.3 ms total, 27 μs avg, 22 μs to 81 μs max
Rows sent     : 1 avg, 1 to 1 max  (0.00%)
Rows examined : 60 avg, 5 to 75 max  (0.00%)
Database      :
Users         :
        root@localhost  : 100.00% (51) of query, 98.10% (9722158) of all users

Query abstract:
SET timestamp=N; SELECT COUNT(*) AS cnt FROM gamebuilding WHERE uid=N AND cityid=N AND btype=N;

Query sample:
SET timestamp=1268411402;
SELECT count(*) as cnt from gamebuilding WHERE uid=88103 and cityid=351190 and btype=2;

______________________________________________________________________ 002 ___
Count         : 2.17M  (21.89%)
Time          : 55340232247643.3 s total, 25505314.313493 s avg, 506 μs to 18446744073709.4 s max  (17.65%)
  95% of Time : 24059.277324 s total, 11.672 ms avg, 506 μs to 15.87 ms max
Lock Time (s) : 81.729776 s total, 38 μs avg, 29 μs to 3.309488 s max  (0.85%)
  95% of Lock : 73.710435 s total, 36 μs avg, 29 μs to 38 μs max
Rows sent     : 100 avg, 0 to 100 max  (30.84%)
Rows examined : 18.41k avg, 100 to 28.81k max  (57.97%)
Database      :
Users         :
        root@localhost  : 100.00% (2169753) of query, 98.10% (9722158) of all users

Query abstract:
SET timestamp=N; SELECT id,uid,cityid,people_x,people_c_x,people_y,people_b,people_count,people_count_sx,people_count_bh,salary,gold,gold_cl,goldadd,rate FROM pools ORDER BY id LIMIT N,N;

Query sample:
SET timestamp=1268038801;
SELECT id,uid,cityid,people_x,people_c_x,people_y,people_b,people_count,people_count_sx,people_count_bh,salary,gold,gold_cl,goldadd,rate FROM pools order by id limit 0,100;

格式说明如下:
总查询次数 (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语句

3.perl安装不全导致的Can’t locate Time/HiRes.pm in @INC错误的处理方法

---

最近要测试mysql日志分析工具，安装完成后遇到了perl脚本库不全问题，如下所示：

# mysqlsla
Can’t locate Time/HiRes.pm in @INC (@INC contains: /usr/lib/perl5/5.8.5/i386-linux-thread-multi /usr/lib/perl5/5.8.5 /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.2/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.1/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl/5.8.4 /usr/lib/perl5/site_perl/5.8.3 /usr/lib/perl5/site_perl/5.8.2 /usr/lib/perl5/site_perl/5.8.1 /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.2/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.1/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl/5.8.4 /usr/lib/perl5/vendor_perl/5.8.3 /usr/lib/perl5/vendor_perl/5.8.2 /usr/lib/perl5/vendor_perl/5.8.1 /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl .) at /usr/local/bin/mysqlsla line 2092.
BEGIN failed–compilation aborted at /usr/local/bin/mysqlsla line 2092.

对于没有接触过perl的人来说，这个提示很难看懂，借助n年前的开发经验，这可能是引用的模块找不到引起的。

其实.pm文件是就是perl的“类库”文件，里面保存了模块化的代码。下面的代码可以解决这个问题。

# perl -MCPAN -e 'install Time::HiRes'

perl在5.0之后引入了模块化的概念，实现面向对象的编程。CPAN（Comprehensive　Perl　Archive　Network）是internet上Perl模块最大的集散地，包含了现今公布的几乎所有的perl模块，通过上面的例子，可以获知利用这个格式，可以到CPAN上安装需要的模块。

安装前需要先联上线，并且您需要取得root权限。　
初次运行CPAN时需要做一些设置，如果您的机器是直接与internet相联（拨号上网、专线，etc.），那么一路回车就行了，只需要在最后选一个离您最近的CPAN镜像站点。例如我选的是位于国内的中国自由软件库 　。否则，如果您的机器位于防火墙之后，还需要设置ftp代理或http代理。　

类似的还有：

# perl -MCPAN -e 'install Time::HiRes'
# perl -MCPAN -e 'install File::Tail'
# perl -MCPAN -e 'install Date::Parse'
# perl -MCPAN -e 'install Net::Netmask'

参考帖子：