Page History

...

SQL DM for MySQL monitors MySQL replicas by issuing a "SHOW SLAVE STATUS" on the slaves. SQL DM for MySQL can also auto-register slaves, given the master details.

Replication Slave

Select "Yes" if you want the toggle switch for Is this a replication slave? to monitor MySQL replication. This option requires that the MySQL user has "Super" or "Replication Client" global privilege.

...

SQL DM for MySQL retrieves (completely or partially) the General query log and the Slow query log from the MySQL servers it connects to, and analyzes them. Here, we see how to set up details for the connection, so that log analysis is available with SQL DM for MySQL. You have to set up details for the general query log and the slow query log independently. Enabling slow query log  Slow Query Log 'log queries not using indexes' instead needs SUPER privilege. Refer to the MySQL documentation on how to enable and configure logging. MySQL server logs can be written to files on the server machine or to tables in the MySQL database itself.

...

First, lets consider the situation where server logs are stored as files on the server machine. This is the most common situation and the only one available with MySQL servers before version 5.1. First time you configure a server with this option, click the 'Fetch query log details' button. The MySQL server 'knows' (it is stored in server variables) what logs are enabled and how logging is configured. Click 'Test Path' to verify that the path SQL DM for MySQL will connect and verify the existence of the file (but not its content).

...

If the log files can be accessed from a shared drive, over a network, or from a network enabled file system (like NFS on Linux), then SQL DM for MySQL can access them as if they were local files. No additional SSH/SFTP configuration is required in this case: the operating system takes care of the file transfer transparently.

When 'via SFTP' option is chosen, then SSH server details as defined in 'SSH server details settings' are used to read the file from the remote system. Note that the SSH user must have read access to the log files!

...

This option is supported by MySQL from version 5.1. Also, SQL DM for MySQL supports when this option is available. Here, you click the 'Fetch Log Details From MySQL' button. When this option is used there is no file path to configure and no SSH details to consider. SQL DM for MySQL can retrieve the server log by sending simple SELECT statements. Only the MySQL user used by SQL DM for MySQL to connect to MySQL must have SELECT privileges to the tables.

...

This works exactly the same way as the MySQL Error log and MySQL Query Log. On enabling audit log monitoring, SQL DM for MySQL fetches the path from the server and displays it in the File Path box. Note that, we get the path from the variable "server_audit_file_path", and by default it just returns the audit log file name. In such cases, you have to manually enter the path for the audit log (by default, the path is same as datadir path).

Next, depending on where you have the MySQL server running, select an appropriate option for "Read file fromFile From". If the server is on the same machine as SQL DM for MySQL, choose "Local path". If it is on a remote machine, choose "Via SFTP" and give the corresponding SSH details. If the server is a RDS/Aurora server, then choose "RDS/Aurora (Using API)".

Sniffer Settings

SQL DM for MySQL's 'query sniffer' is a functionality that records a 'pseudo server log' and stores it in the SQL DM for MySQL embedded database. With 'query snifferSniffer' enabled, SQL DM for MySQL can populate the 'pseudo server log' in three different ways at the intervals you specify:

...

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

...

(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 listens on port 4040. Now you can connect to the Proxy from one or more clients/applications. The Proxy will send 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 will also send sends information to SQL DM for MySQL that SQL DM for MySQL will use uses 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 is 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 prevents the sniffer database to grow out of control! . The filtering options are:

...

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

• Queries starting with: Enter any string and only queries starting with that string will be are 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 are 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.

...

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

...

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 Monitoringselect the toggle switch to "Enable deadlock monitoring" option.

Monitors Settings

...

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

Connection Settings

You can specify the connection timeout MySQL Connection Timeout value for your server. This option simply means that SQL DM for MySQL will wait waits 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 a SSH Tunnel Connection Timeout, and a SSH System Connection Timeout if System Metrics is enabled. The default value for all being 30 seconds.

...