Page History

...

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 events_statements_history_long in setup_consumers table. By default, this is not enabled and should be enabled by executing

UPDATE performance_schema.setup_consumers 
SET enabled = 'YES' 
WHERE name = 'events_statements_history_long';  

The 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 performance_schema_digests_size.

Note that, although configuring a Proxy instance is a little more complicated, the PROXY-based sniffer has several advantages over the PROCESSLIST-based, including:

1. All queries that was handled by the Proxy will be recorded by SQL DM for MySQL 'sniffer' when PROXY option is used. When PROCESSLIST option is used very fast queries may execute completely between two SHOW FULL PROCESSLIST queries and will then not be recorded.
2. You can choose to analyze queries from specific client(s)/application(s) only. Simply let (only) the clients that you want to focus on at the moment connect through the Proxy.
3. When using the PROXY option you can distribute part of the load generated by the sniffer on the machine that fits best in your deployment scenario (like on the one that has most free resources available) by deciding where to have the PROXY: The MySQL machine, the SQL DM for MySQL machine (if not the same) or quite another machine. The machine running MySQL will have no additional load due to the sniffer if the Proxy is not running on that machine.

Also note that, if more SQL DM for MySQL instances use the same PROXY they will 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 will 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 Webyog website. The MySQL Proxy program however you will 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 MySQL Proxy.

To start a Proxy instance for use with SQL DM for MySQL use the command:

• For v0.81(Alpha) and later, run the following common from the Proxy installation folder:

# 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

• For Older versions, from the Proxy installation folder, run:

# 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 will listen on port 4040. Now you can connect to the Proxy from one or more clients/applications. The Proxy will send queries to MySQL and results back to the client. But when started with the LUA script for SQL DM for MySQL sniffer it will also send information to SQL DM for MySQL that SQL DM for MySQL will use to populate the sniffer 'pseudo log'.

Once this 'pseudo 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' will be purged automatically based on the 'data retention timeframe' option set by you.

Further some filtering options are provided. Note that this filtering happens before storing to the SQL DM for MySQL database. This will prevent the sniffer database to grow out of control! The filtering options are:

• User and host: You can choose to store queries executed only by a specific combination of users and/or hosts.

• Minimum time taken: For every PROCESSLIST returned to SQL DM for MySQL, the queries will be 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 will be UPDATED, and the statement will be recorded only once.

• Queries starting with: Enter any string and only queries starting with that string will be recorded. Examples: SELECT *, UPDATE Customer_Base.

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 will never be 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 on the 'Monitor only locked queries' would only monitor those long queries that are locked.

You should note that the query sniffer will never be 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.

Deadlock settings

In transactional databases deadlocks are a classic problem, but these deadlocks are not too dangerous unless they are so frequent that you cannot run certain transactions at all. To trace the deadlocks reported by INNODB STATUS, you can enable "Deadlock Monitoring" option.

Image Added

Monitors Settings

SQL DM for MySQL provides a way of disabling an entire group of Monitors. For instance if a MySQL server is not a replication slave, then the replication group can be disabled.

Image Added

Real-time

SQL DM for MySQL provides you the option to choose the data collection mode for Real-time monitoring. Herein, you can choose between "Processlist" and "Performance schema". You can choose the "Performance schema" mode if your MySQL version is 5.6.14 and above and if performance schema is enabled, otherwise you can go with "Processlist" mode.

Image Added

Connection Settings

You can specify the connection timeout value for your server. This option simply means that SQL DM for MySQL will wait for this long to get a response from the server before it throws an error. This comes handy to avoid false positives when connection to some specific servers is slow. You can setup a larger timeout in such cases. If you have SSH Tunneling enabled to the MySQL then you can specify SSH Tunnel Connection Timeout, and SSH System Connection Timeout if System Metrics is enabled. The default value for all being 30 seconds.

Image Added