mysql sql日志统计工具mysqlsla

kider

出处：http://www.dbapub.com/2009/03/mysqlsla/

mysql的slow sql日志文件是时间顺序记录每个超过阀值(long_query_time)的sql
格式如下

# Time: 090327 19:23:14
# [email=User@Host]User@Host[/email]: myppdb[myppdb] @  [172.16.130.2]
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 408
SELECT COUNT(1)  from t_evaluation_ref_seller_100 where propertymask&0×00000800 = 0 AND (seller_flag=1 and seller_uin=378617100) and seller_evaltime=’0000-00-00 00:00:00′  and UNIX_TIMESTAMP(deal_endtime)>1235560993;

如果两个语句结构一样,但只是条件值不一样, 也会显示成两个语句, 当文件中语句数量很大时,管理员不方便查看.
这里介绍一个专门针对mysql中的sql日志文件的统计工具 mysqlsla,它实际上是个perl脚本.
可以对慢速,update日志,select日志进行分析

$ mysqlsla
Please specify a log type using –log-type (-lt): general, slow, msl, binary or udl.
man mysqlsla for help or visit http://hackmysql.com/mysqlsla

使用方法如下, -lt指定日志类型, slow表示慢速日志

$ mysqlsla -lt slow db_evaluation_b-slow.log >db_evaluation_b_327.log
Report for slow logs: db_evaluation_b-slow.log
1.06k queries total, 624 unique
Sorted by ‘t_sum’
Grand Totals: Time 4.30k s, Lock 0 s, Rows sent 12.40k, Rows Examined 9.21M
______________________________________________________________________ 001 ___
Count         : 23  (2.16%)
Time          : 95 s total, 4.130435 s avg, 3 s to 10 s max  (2.21%)
  95% of Time : 77 s total, 3.666667 s avg, 3 s to 8 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 1 avg, 1 to 1 max  (0.19%)
Rows examined : 26.72k avg, 1.32k to 55.10k max  (6.68%)
Database      :
Users         :
        myppdb@ 172.23.28.154 : 100.00% (23) of query, 22.54% (240) of all users
Query abstract:
SELECT COUNT(N) FROM t_evaluation_ref_seller_650 WHERE propertymask&N = N AND buyer_flag=N AND eval_status=N AND buyer_evallevel > N
AND commodityid = ‘S’ AND buyer_evaltime >= N;
Query sample:
SELECT COUNT(1)  from t_evaluation_ref_seller_650 where propertymask&0×00000800 = 0 AND buyer_flag=1 and eval_status=2 and buyer_eva
llevel > 0 and CommodityId = ‘0A45EC1E00000000000A34EE009C0D2B’ and  buyer_evaltime >= 0;

上述表示语句总共执行了23次,占语句数量2.16%, 总费时95s, 平均4.13s
平均扫描记录数 26.72k, 全部从172.23.28.154发送过来
这个工具的手册如下

$ man mysqlsla
Reformatting mysqlsla(3pm), please wait…
mysqlsla(3)                                    User Contributed Perl Documentation                                   mysqlsla(3)

NAME
       mysqlsla - Parse, filter, analyze and sort MySQL slow, general and binary logs
SYNOPSIS
           # Basic operation: parse a MySQL slow or general log
           mysqlsla –log-type slow LOG
           mysqlsla –log-type general LOG
           # Parse output from mysqlbinlog
           # mysqlsla cannot directly parse binary logs
           mysqlbinlog LOG | mysqlsla –log-type binary -
           # Parse a microslow patched slow log
           mysqlsla –log-type msl LOG
           # Replay a replay file
           mysqlsla –replay FILE
           # Parse a user-defined log specify its format
           mysqlsla –log-type udl –udl-format FILE
DESCRIPTION
       mysqlsla parses, filters, analyzes and sorts MySQL slow, general, binary (indirectly) and microslow patched slow logs. It
       also supports user-defined logs.
       This POD/man page is only a very brief outline of usage and command line options.  For the full library of mysqlsla docu-
       mentation visit http://hackmysql.com/mysqlsla.
.mysqlsla CONFIG FILE
       Reading “~/.mysqlsla” is the very first thing mysqlsla does when it starts.  Command line options can be set in this file
       (one per line). Example:
       atomic-statements
       statement-filter=+UPDATE,INSERT
       Notice: no leading dashes (- or –), no quotations marks (”), and the form “option=value” when “option” requires a value.
       These options are overriden by those given on the real command line.
COMMAND LINE OPTIONS
       “–log-type (-lt) TYPE LOGS”
           Parse MySQL LOGS of TYPE. Default none. TYPE must be either slow, general, binary, msl or udl.  LOGS is a space-sepa-
           rated list of MySQL log files.
           This is the most important/frequently mysqlsla option and is almost always required because mysqlsla must be told
           which type of MySQL to parse.
           Slow and general log types are what you expect. msl logs are microslow patched slow logs.  udl means user-defined
           log.
           Binary logs are a special case. mysqlsla cannot read MySQL binary log directly. You must first “decode” the binary
           log using mysqlbinlog without the –short-form option. It is only the text output from mysqlbinlog that mysqlsla can
           read.
           If you want to use the –short-form option with mysqlbinlog you must then use TYPE udl with mysqlsla.
           LOGS can also be - to make mysqlsla read from STDIN. This can be used to pipe the output of another program into
           mysqlsla.
       “–abstract-in (-Ai) N”
           Abstract IN () clauses further by grouping in groups of N. Disabled by default.
       “–abstract-values (-Av)”
           Abstract VALUES () clauses further by removing the number of condensed value sets. Disabled by default.
       “–atomic-statements”
           Treat multi-statment groups atomically when filtering. Disabled by default.
       “–avg (-n) N”
           Average query timing over N executions. Default 1.
       “–databases (-db) (-D) DATABASES”
           Try EXPLAINing queries which have no known database using DATABASES. Default none. DATABASES is a comma-separated
           list of database names (without spaces). Only used when option explain is used too.
       “–db-inheritance”
           Allow queries to inherit the last database specified in the log. Disabled by default.
       “–debug”
           Enable a flood of debugging information from both mysqlsla and MySQL:og:arseFilter. Disabled by default. Use with
           caution.
       “–dist”
           Calculate distribution frequency of values. Disabled by default. Requires an appropriate standard report format line.
       “–dist-min-percent (-dmin) N”
           Do not display dist percents less than N. Default 5.
       “–dist-top (-dtop) N”
           Display at most N dist percentages. Default 10.
       “–dont-save-meta-values”
           Do not save meta-property values from log. Disabled by default (meta-property values are saved).
       “–explain (-ex)”
           EXPLAIN each query. Disabled by default. Requires an appropriate standard report format line.
       “–flush-qc”
           Flush query cache before query execution timing. Disabled by default.
       “–grep PATTERN”
           grep statements for PATTERN and keep only those which match. Default none.
       “–help (-?)”
           Tells you to see what you are reading right now.
       “–host ADDRESS”
           Connect to MySQL at host ADDRESS. Default localhost if no socket is available.
       “–meta-filter (-mf) CONDTIONS”
           Set meta-properties filter using CONDITIONS. Default none. CONDITIONS is a comma-separated list of meta-property con-
           ditions (without spaces) in the form: [meta][op][value].
           [meta] refers to a meta-property name, the list of which is long: see mysqlsla v2 Filters.
           [op] is either > < or =. [value] is the value, numeric or string, against which the value for [meta] from the log
           must be true according to [op].
           For string-based [meta], like db or host, [op] can only be =.
       “–microsecond-symbol (-us) STRING”
           Use STRING to denote microsecond values. Default s.
       “–no-mycnf”
           Do not read ~/.my.cnf when initializing. Does not apply to Windows servers.
       “–nthp-min-values (nthpm) N”
           Do not calculate Nth percent values if there are less than N values. Default 10.
       “–nth-percent (-nthp) N”
           Calculate Nth percent values. Disabled by default or 95 if used but no N is given. Requires an appropriate standard
           report format line.
       “–password PASS”
           Use PASS as MySQL user password. If PASS is omitted, the password will be prompted for (on STDERR).
       “–percent”
           Display a basic percentage complete indictor while timing all queries for the time-all report.  Disabled by default.
       “–port PORT”
           Connect to MySQL on PORT. Default none (relies on system default which will be 3306).
       “–post-analyses-replay FILE”
           Save a post-analyses replay as FILE.
       “–post-parse-replay FILE”
           Save a post-parse replay as FILE.
       “–post-sort-replay FILE”
           Save a post-sort replay as FILE.
       “–replay FILE”
           Load unique queries from replay FILE. Default none.
       “–report-format (-rf) FILE”
           Use FILE to format the standard report. Default internal report format.
       “–reports (-R) REPORTS”
           Print REPORTS. Default standard. REPORTS is a comma-separated list of report names (without spaces).
           Available reports are: standard, time-all, print-unique, print-all, dump.
           WARNING: A safety SQL statement filter of “+SELECT,USE” is automatically set when using time-each-query or the time-
           all report. Overriding the safety SQL statement filter by explicitly setting another with statement-filter can permit
           real changes to databases. Use with caution!
       “–save-all-values”
           Save extra “all values” for some meta-properties. Disabled by default.
       “–silent”
           Do not print any reports. Disabled by default. Debug messages will still be printed.
       “–socket SOCKET”
           Connect to MySQL through SOCKET. Default none (relies on system default which is compiled into the MySQL client
           library).
       “–sort META”
           Sort queries according to META. Default t_sum for slow and msl logs, c_sum for all others.  META is any meta-property
           name.
       “–statement-filter (-sf) CONDTIONS”
           Set SQL statement filter using CONDITIONS. Default none. CONDITIONS is a comma-separated list of SQL statement types
           in the form: [+-][TYPE],[TYPE],etc.
           The [+-] is given only once before the first [TYPE]. A + indicates a positive filter: keep only SQL statements of
           [TYPE]. A - indicates a negative filter: remove only SQL statements of [TYPE].  If neither is given, - is default.
           [TYPE] is a SQL statement type: SELECT, CREATE, DROP, UPDATE, INSERT, etc.
       “–time-each-query (-te)”
           Time each query by actually executing it on the MySQL server. Disabled by default. Requires an appropriate standard
           report format line.
           WARNING: A safety SQL statement filter of “+SELECT,USE” is automatically set when using time-each-query or the time-
           all report. Overriding the safety SQL statement filter by explicitly setting another with statement-filter can permit
           real changes to databases. Use with caution!
       “–top N”
           After sorting display only the top N queries. Default 10.
       “–udl-format (-uf) FILE”
           Use FILE to define the format of the user-defined log (udl) instead of the default. Default is “;\n” record separator
           and no headers.
       “–user USER”
           Connect to MySQL as USER. Default user of mysqlsla process.
CUSTOM REPORT FORMATS
       The standard report is the human-readable report which shows all the numbers and values calculated from the log. If no
       other report is specified, it is the default report.
       mysqlsla automatically formats the standard report according to a report format depending on the log type being parsed.
       Therefore, the standard report for general logs is different from slow logs and binary logs, etc. mysqlsla has, inter-
       nally, basic report formats for every log type, but a custom report format can be explicitly set by using the
       “–report-format” option.
       Read http://hackmysql.com/mysqlsla_reports for more information on creating and customizing report formats.
BUGS
       I follow the zero known bugs release policy in releasing new versions of mysqlsla.  Certainly, however, bugs still exist
       somewhere. So when you find one, contact me through the web site at http://hackmysql.com/contact.
SEE ALSO
       http://hackmysql.com/mysqlsla
AUTHOR
       Daniel Nichter (http://hackmysql.com/)
COPYRIGHT AND LICENSE
       Copyright 2007-2008 Daniel Nichter
       This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License
       as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.
       This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied war-
       ranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
       The GNU General Public License is available at: http://www.gnu.org/copyleft/gpl.html

perl v5.8.8                                                2008-07-13                                                mysqlsla(3)