MaxScale 21.06 Top Filter
Overview
The top filter is a filter module for MariaDB MaxScale that monitors every SQL statement that passes through the filter. It measures the duration of that statement, the time between the statement being sent and the first result being returned. The top N times are kept, along with the SQL text itself and a list sorted on the execution times of the query is written to a file upon closure of the client session.
Configuration
Example minimal configuration:
Filter Parameters
The top filter has one mandatory parameter, filebase
, and a number of optional
parameters.
filebase
Type: string
Mandatory: Yes
Dynamic: Yes
The basename of the output file created for each session. The session ID is added to the filename for each file written. This is a mandatory parameter.
The filebase may also be set as the filter, the mechanism to set the filebase via the filter option is superseded by the parameter. If both are set the parameter setting will be used and the filter option ignored.
count
Type: number
Mandatory: No
Dynamic: Yes
Default:
10
The number of SQL statements to store and report upon.
match
Type: regex
Mandatory: No
Dynamic: Yes
Default: None
Limits the queries logged by the filter.
exclude
Type: regex
Mandatory: No
Dynamic: Yes
Default: None
Limits the queries logged by the filter.
options
Type: enum
Mandatory: No
Dynamic: No
Values:
ignorecase
,case
,extended
Default:
case
Regular expression options
for match
and exclude
.
source
Type: string
Mandatory: No
Dynamic: Yes
Default: None
Defines an address that is used to match against the address from which the client connection to MariaDB MaxScale originates. Only sessions that originate from this address will be logged.
user
Type: string
Mandatory: No
Dynamic: Yes
Default: None
Defines a username that is used to match against the user from which the client connection to MariaDB MaxScale originates. Only sessions that are connected using this username will result in results being generated.
Examples
Example 1 - Heavily Contended Table
You have an order system and believe the updates of the PRODUCTS table is causing some performance issues for the rest of your application. You would like to know which of the many updates in your application is causing the issue.
Add a filter with the following definition:
Note the exclude entry, this is to prevent updates to the PRODUCTS_STOCK table from being included in the report.
Example 2 - One Application Server is Slow
One of your applications servers is slower than the rest, you believe it is related to database access but you are not sure what is taking the time.
Add a filter with the following definition:
In order to produce a comparison with an unaffected application server you can also add a second filter as a control.
In the service definition add both filters
You will then have two sets of logs files written, one which profiles the top 20 queries of the slow application server and another that gives you the top 20 queries of your control application server. These two sets of files can then be compared to determine what if anything is different between the two.
Output Report
The following is an example report for a number of fictitious queries executed against the employees example database available for MySQL.
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?