SQL DM for MySQL query sniffer is a functionality that records a pseudo server log and stores it in the SQL DM for MySQL embedded database. With Sniffer enabled, SQL DM for MySQL can populate the pseudo server log in three different ways at the intervals you specify:
For MySQL 5.6.14 and above you can use Performance schema(if Performance Schema is enabled), Proxy and Processlist for query analysis. If using MySQL version less than 5.6.14 then you can use Processlist mode.
Performance Schema on MySQL contains queries executed on server along with other information
SQL DM for MySQL uses performance schema statement digest
table(events_statements_summary_by_digest) to get the above information and is dependent on the
setup_consumers table. By default, this is enabled. If not, it can be enabled by executing the following:
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'statements_digest';
Example query is available in
events_statements_history_long table and has to be enabled and is dependent on the
setup_consumers table. By default, this is not enabled and should be enabled by executing the following:
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_history_long';
performance_schema.events_statements_summary_by_digest table size is dependent on
performance_schema_digests_size global variable. By default, this size is set to 5000 rows. Once it reaches this limit you may lose the queries. SQL DM for MySQL provides an option to truncate the performance schema digest table when it reaches 80% of
Although configuring a Proxy instance is a little more complicated, the PROXY-based sniffer has several advantages over the PROCESSLIST-based, including:
Also note that, if more SQL DM for MySQL instances use the same PROXY they use the same data collected, when the Proxy Sniffing is enabled by the first SQL DM for MySQL instance. To work with SQL DM for MySQL sniffer the MySQL Proxy instance must be started with the name of a LUA script called MONyog.LUA (LUA is a scripting/programming language) as argument and is distributed with SQL DM for MySQL. You can find it in the MONyog program folder after installing (Windows and Linux RPM) or unpacking (Linux .tar.gz) the SQL DM for MySQL program package as downloaded from the IDERA website. The MySQL Proxy program however you need to download from MySQL website (we cannot include it for license reasons). SQL DM for MySQL works with Proxy versions from 0.61 to 0.81(latest currently) with the exception of 0.7x versions for windows and Mac due to a bug in those specific builds. For more information on Proxy, see .
To start a Proxy instance for use with SQL DM for MySQL use the command:
# mysql-proxy --proxy-backend-addresses=192.168.x.x:3305 \ --proxy-address=192.168.y.y:4045 \ --admin-username=root \ --admin-password=root \ --admin-lua-script=MONyog.lua \ --proxy-lua-script=MONyog.lua
# mysql-proxy --proxy-backend-addresses=192.168.x.x:3305 \ --proxy-address=192.168.y.y:4045 \ --proxy-lua-script=MONyog.lua
(It is assumed that the 'MONyog.LUA' was copied to the folder where the PROXY binary is). Also note that, if no port is specified the PROXY listens on port 4040. Now, you can connect to the Proxy from one or more clients/applications. The Proxy sends queries to MySQL and the results back to the client. But when started with the LUA script for SQL DM for MySQL sniffer it also sends information to SQL DM for MySQL that SQL DM for MySQL uses to populate the sniffer 'pseudo log'.
Once this 'seudo log has been recorded (in either of the three ways described: Performance Schema, PROCESSLIST or PROXY-based) the SQL DM for MySQL log analysis functionalities can operate on the pseudo log as well as the real logs. The data recorded in the pseudo log is purged automatically based on the data retention timeframe option set by you.
Further some filtering options are provided. This filtering happens before storing to the SQL DM for MySQL database. This prevents the sniffer database to grow out of control. The filtering options are as follow:
PROCESSLISTreturned to SQL DM for MySQL, the queries are recorded in the embedded database only if they have been executing for a time greater than the specified minimum execution time. Furthermore, if a query with the same structure and details (like process ID) as one already recorded is encountered, the embedded database is UPDATED, and the statement is recorded only once.
This setting should be somewhat larger than the sample interval (and also consider the latency of the connection). If set lower it would not really make much sense.
Also note that in
PROCESSLIST Sniffer we have an option Long Running Query Options where you can monitor the long running queries by notifying or killing a query which takes more than a time specified by you. You can also specify users whose queries will be ignored (i.e. queries by such user are never killed by SQL DM for MySQL) and never raise an alert even if they take a longer than the time specified under 'LONG RUNNING QUERY TIME' you specified.
Clicking Monitor only locked queries would only monitor those long queries that are locked.
You should note that the query sniffer is not a complete 'general log'. Very fast statements may or may not be recorded as they may or may not finish executing between two PROCESSLISTs generated. The time interval between subsequent data collections for the 'pseudo log' depends on the connection to the MySQL server.