mysqlsla v2 supports complete MySQL log filtering. There are two classes of filters: meta-property and SQL statement. Since mysqlsla parses slow, general and binary MySQL logs, the number of native log filters is large.

Furthermore, anticipating the rising importance of MySQL Proxy and other proxies, mysqlsla v2 also supports parsing and filtering UDL: user-defined logs of varying formats providing various SQL statement meta-properties.

Therefore, the number of log filters is almost limitless. With such an abundance of information it is necessary to filter out what is extraneous to find easily and quickly what is desired. mysqlsla v2 has been redesigned to accomplish this task.

This document provides first an overview of how mysqlsla v2 handles log filtering: Meta-Property Filter, SQL Statement Filter, Setting the Filters. Then, the list of all intrinsically supported Meta-Property Names provided by slow, general and binary MySQL logs is given. Finally, a brief overview is made concerning how mysqlsla v2 handles User-Defined Log Filtering.

The majority of the information presented here applies equally to any script that implements MySQL::Log::ParseFilter. Features specific to mysqlsla are noted.

Each SQL statement has a multitude of basic meta-properties: execution time, connection ID, number of rows sent in result set, etc. From these basic meta-properties, additional meta-properties can be calculated such as average execution time and maximum number of rows sent.

Each type of MySQL log provides different meta-properties. Therefore, which meta-properties are available to mysqlsla is determined by which type of log is being parsed. The four different types of MySQL logs that mysqlsla can parse (slow, general, binary, udl) consitute the four groups of available meta-properties, with some overlap. The group of meta-properties available in slow logs is sub-divided into three groups: regular slow logs, microslow (msl) patched slow logs, and msl patched slow logs with InnoDB values.

The meta-property filter is set with the meta-filter (-mf) command line option.

(MySQL::Log::ParseFilter set_meta_filter())

The SQL statement filter is a positive/negative filter allowing only (postive) or excluding only (negative) certain types of SQL statements: SELECT, UPDATE, SHOW, ALTER, etc. By default, mysqlsla accepts every type of SQL statement, even potentially harmful ones such as DROP, unless otherwise exluded or allowed by the SQL statement filter.

The SQL statement filter is set with the statement-filter (-sf) command line option.

(MySQL::Log::ParseFilter set_statement_filter())

The meta-property filter takes a comma-separated lists of filter condtions in the form: [meta][op][value]. [meta] is a meta-property name from the long list given below. [op] is either =, > or <. And [value] is the value gainst which the value for [meta] from the log must be true according to [op]. [value] is numeric or text depending on [meta]. For text values [op] can only be =.

All filter condtions must pass for the SQL statement to be saved. Multiple condtions for the same [meta-property name] can be given, even if they are redundant or contraditory. This allows for "range conditions" in some cases. For example, with a slow log and filter condtions "t>10,t<100" only SQL statements with a time value between 10 and 100 (exclusive) will be saved. However, in the example with a general log and filter condtions "cid<2000,cid>5000,cid<6000" no SQL statements will be saved because there is no number which passes all three condtions. (At least not in bivalent logic which is currently the only type of logic supported by mysqlsla.)

The statement filter takes a comma-separated list of SQL statements types in the form: [+-][TYPE],[TYPE]. The [+-] is only included once in front of the first [TYPE]. It indicate if the filter is positive or negative: a positive filter means save only SQL statements of the given [TYPES]s; a negative filter means remove the given [TYPE]s and save the rest. If no [+-] is given, the default is negative.

For example, to save only USE and SELECT statements: "+USE,SELECT". Or, to remove SET statements: "SET".

These are all the meta-property names that mysqlsla v2 and MySQL::Log::ParseFilter recognize from slow, general, binary and msl log types. The list is divided according to each log type and then grouped according to basic meta-properties. For example, the first basic meta-property for all log types is count which has two meta-property names: c_sum and c_sum_p.

Filter condtions for non-existent meta-properties are simply ignored. Therefore, accidently using a slow log meta-property with a general log will have no effect.

There are a number of exceptions noted below certain meta-property names.

All Logs

Slow Logs

General Logs

Binary Logs

User-defined log filtering works nearly identically to basic log filtering. The obvious difference of course is the random nature and availability of meta-properties. But since the user-defined logs are user-defined, you should know already what meta-properties are available: those which you defined in the udl format file.

Let's imagine a simple UDL that provides a meta-property called t_sending_data: time sending data, the same value available through the MySQL Query Profiler. Furthermore, the udl has this meta-property defined as a full aggregate value (nf) and we are running mysqlsla with the the nth-percent command line option.

After creating an appropriate udl format to define the UDL log format and specifying this UDL format using the udl-format option, mysqlsla will parse the UDL and make available the following meta-property names:

Those meta-properties act like the meta-properties from the basic log types: unless otherwise noted they can be used as filter conditions with the meta-property filter; they can be the the value given to sort; and they are accessible and formattable in the standard report.

Concerning the standard report (which applies only to mysqlsla), you will surely need to either update the default standard report for udl, which is extremely basic, or create your own and set it with the report-format command line option. For more information on standard report formats, read mysqlsla v2 Reports.

Overall, there are no notable limitations on user-defined logs. In fact, mysqlsla could be rewritten to treat slow and binary logs as user-defined logs.