Page History

...

To create a SSH connection you need the following details:

• SSH Host: Host of the machine on which SSH server is running.
• SSH Port: Port on which SSH server is listening. By default, it is 22.
• SSH Username: Username to access the SSH server (Note: not the MySQL server).
• Authentication type: Specify the type of authentication to use. This can be either key based or password based.
• If you have specified Authentication type as Password - Provide the password.
• If you have specified, Authentication type as Key - You should note that SQL DM for MySQL only supports ''OpenSSH standard key format'' for key based authentication in SSH connections.
• Private Key: Paste the content of your private key file. Again, do not specify the path to your private key file.
• Passphrase: Enter the passphrase for your private key file (if any). This can be left blank, if no passphrase was given for the private key.

...

If you want to collect data from the server, you need to select "Enable Data Collection", so that SQL DM for MySQL collect collects and store stores various MySQL and OS metrics.

...

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.

...

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

...

This feature of SQL DM for MySQL saves you time from registering each slave individually. In order for SQL DM for MySQL to auto-register all slaves, select the toggle switch to enable "enable  Auto-Register slaves" in the ' Advanced settings ' tab while registering a slave. If a master is already registered, click ' Edit Server' , and check the ' Auto-Register slaves ' in the ' Advanced settings ' tab. The MySQL and the SSH details of the slaves are assumed to be the same as that of the ' master'. In case the slave details are different from that of the master, you have to manually edit that server and change details.

The auto-registering of slaves is extended to multiple levels of replication. For instance, lets say Server A is a Master that has Server B as the slave. And Server B has Server C as its slave. In such case, while registering Server A, if you check 'Auto-Register Slaves', then it registers A, B, and C provided the MySQL and the SSH details of the A is same as that of B.

How does SQL DM for MySQL auto-register all slaves of a given master? SQL DM for MySQL shoots a " SHOW FULL PROCESSLIST " on the master, and checks for all the slaves connected. (It assumes that MySQL and the SSH details of the slaves are the same as that of the master.) To view replication topology click the Replication tab.

...

Use this option to Auto register all the Galera nodes of your cluster with SQL DM for MySQL. The MySQL and the SSH details of the nodes are assumed to be the same as that of the node on which you are enabling this option. In case the other node details are different from that of the node on which you are enabling this option, you need to manually edit that server, and change details. You can do a “Test ” to check if SQL DM for MySQL is able to connect to the other nodes. If the “Test” gives a successful message then you can go ahead and click “Save”. SQL DM for MySQL will then register registers the detected nodes and redirect redirects you to the Servers page where you can see all the registered nodes.

...

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 connects and verify verifies 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!.

When RDS/Aurora (Using API) option is chosen, make sure that you have the required Access credentials with you to fetch the log files. The Access credentials can be generated from the AWS Management Console. You can refer to the documentation, on how to generate credential keys here: Getting Your Access Key ID and Secret Access Key

Note that by By default, MONyog(SQL DM for MySQL) service runs under Local System Account. If you have Slow query or General query logs in a Mapped Network Drive, SQL DM for MySQL will is not be able to reach it. You need to use UNC notation for SQL DM for MySQL to be able to access them. See FAQ 31 for details.

...

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.

...

Next, depending on where you have the MySQL server running, select an appropriate option for "Read File 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 'Sniffer' enabled, SQL DM for MySQL can populate the ' pseudo server log ' in three different ways at the intervals you specify:

• by By utilizing Performance Schema tables (events_statements_summary_by_digest, events_statements_history_long) and collecting snapshots at regular intervals.
• by By sending the query SHOW FULL PROCESSLIST to the MySQL server.
• or Or by connecting to a running instance of the MySQL-Proxy program that is used by one or more clients to connect to a MySQL server.

...

Performance Schema on MySQL contains queries executed on server along with other information

• number Number of rows sent and examined
• number Number of temporary tables created on disk
• number Number of temporary tables created on memory
• number Number of joins performed and the type of join
• whether Whether sorting happened and the type of sort
• whether Whether index used
• whether Whether good index used

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 statements_digest in setup_consumers table. By default, this is enabled. If not, it can be enabled by executing 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 events_statements_history_long in 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';  

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 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 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 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 'pseudo 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. Note that this 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:

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

...

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

...