You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

System Metrics (applicable for Linux based systems)

In the event that you would like SQL DM for MySQL to use SSH when communicating with this server, you can configure it from this tab. SQL DM for MySQL disables SSH communications by default. In order to use it, you need to click the Enable System Metrics switch. Doing so provides a series of configuration options needed to enable SSH on the server.

Please refer to System Privileges for further details of privileges needed for this feature.

If SSH tunneling to MySQL is configured successfully for this registration you can use those same details here too, provided that SSH tunnel user has enough privileges.

Using SSH connections

To create a SSH connection you will be asked for:

  • 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, don’t 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.

Data collection options

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

Define the collection interval for every server as you want.

You can also define the time interval between two successive retrievals of data. What is appropriate will depend - but for production systems a setting between 2 and 10 minutes is a good place to start.

Data retention time frame

SQL DM for MySQL is designed for storing large amounts of data for long periods of time. Data collected before the specified timeframe is purged automatically. Timeframe may be specified in seconds, minutes, hours and days for particular server.

Base time

For calculation of uptime-based counters the current value of each status variable will be compared with either of those,

  • server status variable 'uptime'
  • server status variable 'uptime_since_flush_status'
  • SQL DM for MySQL 'base time' setting

If SQL DM for MySQL 'base time' setting is defined and server status variable uptime_since_flush_status is available then, uptime_since_flush_status will be used, if it is not available then base time will be used.

The reason for this implementation is that if FLUSH STATUS is executed with a MySQL server, the server status variables will be reset to the same value as would be after a server restart. There is one important exception however and that is the 'uptime' status variable itself. This single status variable is not affected by FLUSH STATUS.

So, to get true uptime-based counters in SQL DM for MySQL with servers that do not support the uptime_since_flush_status variable you will need to define a 'base time' in SQL DM for MySQL greater than or equal to the time where FLUSH STATUS was executed last time.

But also if uptime and/or uptime_since_flush_status is large ('old') you may use 'base time' setting to analyze uptime-based counters on an interval defined by you. For instance, if the server has been running for months you may choose to analyze uptime-base counters based on data collected from a specific time only as you have defined it.

Also, note that if the 'base time' is smaller than uptime (or uptime_since_flush_status if available), then 'base time' setting will be ignored. Using a 'base time' larger than 'uptime' and/or uptime_since_flush_status, then base time would be considered. If a base time is in future, then most recent collection time would be considered (similar to Delta).

Replication Settings

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 to monitor MySQL replication. This option requires that the MySQL user has "Super" or "Replication Client" global privilege.

Automatic registering of slaves

This feature of SQL DM for MySQL saves you time from registering each slave individually. In order to ask SQL DM for MySQL to auto-register all slaves, you would need to 'check' the option to "Auto-Register slaves" in the 'Advanced settings' tab while registering a slave. If in case 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 will have to manually go 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 a case, while registering Server A if you check 'Auto-Register Slaves', then it would register 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 will shoot a "SHOW FULL PROCESSLIST" on the master, and check for all the slaves connected. (It will assume the MySQL and the SSH details of the slaves are same as that of the master.) To view replication topology click on the Replication tab.

Galera

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 will have to manually go 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 on “Save”. SQL DM for MySQL will then register the detected nodes and you will be redirected to Servers page where you can see all the registered nodes.

MySQL error log settings

The MySQL error log is quintessential in determining the health of the server. You can enable error log monitoring to allow SQL DM for MySQL to keep an eye on your MySQL Error Log, and notify you of important information that may save you many sleepless night.

Enable error log monitoring:

Select "Yes" if you want to enable error log monitoring.

Read file from:

There are 3 ways of accessing the log files: Select "Local path" if the logs are in the machine where SQL DM for MySQL is running, or if they can be accessed by SQL DM for MySQL on a shared network drive. Choose "Via SFTP" if you have configured SQL DM for MySQL to use SSH. Select "RDS/Aurora (Using API)" if your server is a RDS/Aurora instance. In case of RDS/Aurora (Using API) for file based logging, four additional fields have to be filled, which are:

  • DB instance identifier: A unique name to identify your RDS/Aurora instance.
  • Instance region: The region in which your instance is hosted, for e.g: us-east-1
  • Access key ID: It is a 20 character long key ID which can be created from the AWS Management console. It is used to make programmatic request to AWS.
  • Secret access key: It is 40 character long and can be created 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.

Fetch error log details:

SQL DM for MySQL can automatically get the path of the error log from the MySQL server. Just click on the fetch button, and SQL DM for MySQL will do the rest for you.

File path:

If you choose to enter the error log file path manually, you may do so here.

Test path:

Click on this button to check if SQL DM for MySQL can access the file specified in the File path.

MySQL query log settings

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 will see how to set up details for the connection, so that log analysis will be available with SQL DM for MySQL. You will have to set up details for the general query log and the slow query log independently. Enabling 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.

The MySQL server (since version 5.0) has an option to log (in the slow log) queries that do not use an index. Such queries need not be slow if there are only a few hundred or few thousand records in the table(s) involved. But they are 'potentially slow' and should be identified if they access tables,which will continue to grow. You can enable and disable this from here too (SQL DM for MySQL will send the appropriate SET of statements to MySQL) Note: Only DML and DDL queries are recorded in the slow query log.

Logs written to files:

First, we will 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 you should 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).

The log files can be accessed from the local file system (if SQL DM for MySQL and MySQL is running on the same computer) or by using SFTP (if SQL DM for MySQL and MySQL is running on different computers) or by using RDS/Aurora (Using API) if you are using a RDS/Aurora instance. You should note that you must use the file and path syntax of the machine where the logs are.

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

Logs written to MySQL tables:

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.

Audit Log Settings

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 will 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 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 sniffer' enabled, SQL DM for MySQL can populate the 'pseudo server log' in three different ways at the intervals you specify:

  • by utilizing Performance Schema tables (events_statements_summary_by_digest, events_statements_history_long) and collecting snapshots at regular intervals.
  • by sending the query SHOW FULL PROCESSLIST to the MySQL server.
  • 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.

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

  • number of rows sent and examined
  • number of temporary tables created on disk
  • number of temporary tables created on memory
  • number of joins performed and the type of join
  • whether sorting happened and the type of sort
  • whether index used
  • 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

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



  • No labels