What are the MySQL Agent Properties (4226053)

When an agent connects to the Foglight Management Server, it is provided a set of properties that is then used to configure its correct running state.

Default Agent properties are installed with the Foglight Cartridge for MySQL. However, the user will typically edit the default agent properties. Agent properties may apply only to a specific agent instance, or may be applicable across multiple agents.

Open Foglight.

1. From the navigation pane, select: Dashboards | Administration | Agents | Agent Status. The Agent Status screen appears.
2. Deploy the MySQL agent package.
3. Create a MySQL agent instance.
4. Click on a MySQL agent type row. The selected row is highlighted with a yellow background.
5. Click the Edit Properties button.
6. Select Modify the default properties for this agent.
7. Edit the agent properties for the MySQL agent instance:
   • "Setting Connection Parameters (mandatory)"  (below) 
   • "Setting Collection Intervals (optional)"  (below)
8. Click the Activate button.

Setting Connection Parameters (mandatory)

• Database Host – Host where MySQL server is running. Default is “localhost”. (e.g. <hostname> or <ip address>)
• Database Port – Port the MySQL database is running. Default is 3306.
• Database Name – Name of a valid MySQL database that the user is allowed to connect to. Note: This is for connection purposes only. All non-system databases will still be monitored. The ‘mysql’ database is usually an acceptable entry.
• Database User – User that can connect to the MySQL server being monitored.
• Database Password – Password of the user that can connect to the MySQL database being monitored.
• SSLMode – Sets the level of security to use for encrypting a connection to the MySQL server. The following values are allowed: "DISABLED" - Establish unencrypted connections; "PREFERRED" - (default) Establish encrypted connections if the server enabled them, otherwise fall back to unencrypted connections; "REQUIRED" - Establish secure connections if the server enabled them, fail otherwise; "VERIFY_CA" - Like "REQUIRED" but additionally verify the server TLS certificate against the configured Certificate Authority (CA) certificates; "VERIFY_IDENTITY" - Like "VERIFY_CA", but additionally verify that the server certificate matches the host to which the connection is attempted. For more information on enabling SSL, refer to the Configuring an SSL Connection KB article 4232477.
• Allow Public Key Remote Retrieval – Allows the client to retrieve the public key from the server in order to secure the connection. This must be enabled if an SSL connection is not configured and the public key is not available locally.
• Local Public Key File Path – The file path to the public key for the MySQL server that the agent is attempting to connect with. The public key must be locally accessible by the agent, which is hosted on the FglAM machine. This can be used instead of allowing public key retrieval, preventing possible MITM interception.
• Use Cleartext – Enables the cleartext authentication method that sends the unhashed password to the MySQL server. It is recommended that SSL be enabled if this is done.

Setting Administration (optional)

• Enable Administration – Set to true in order to use the MySQL Administration Panel for this agent. See the MySQL Administration Panel for more information. Default is false.
• Enable Explain Plans – Set to true in order to enable Explain Plan requests from the Administration Panel and Statement Digests pages. Default is false.
• Admin User – User that will be employed only to perform administrative actions on the monitored MySQL server. This user must have the privileges to perform the requested actions or the action will fail.
• Admin Password – Password of the user that will be employed to perform administrative actions on the server

Setting Replication Parameters (optional)

• Monitor Master Status? – Set to true to monitor the Master replication status on the MySQL server.
• Monitor Slave Status? – Set to true to monitor the Slave replication status on the MySQL server.
• Monitor Replication Slave? – Set to true to monitor the Slave replication status (and only this) on the replication slave server, of which the parameters below pertain to.
• Database Host – Host where MySQL Replication Slave Server is running.
• Database Port – Port the Slave Connection is running on.  Database Name – Name of MySQL Replication Server Database to be monitored.
• Database User – User that can connect to the MySQL Replication Server database being monitored.
• Database Password – Password of the user that can connect to the MySQL Replication Server database being monitored.
• Use SSL – Requires the connection to the MySQL server to use SSL/TLS. For more information on enabling SSL, refer to the Configuring an SSL Connection KB article 4232477.

Setting Statements Parameters (optional)

• Statements – If the MySQL server version is 5.6.5+ and the performance_schema engine is enabled, the agent will collect statement digest information at this interval. Default set to 300 seconds
• # of Top Statements – Number of statements that will be collected, ordered by the Sort By property. Default is 1000
• Sort By – Ordering parameter for statement collection limit. Default is Total Executions

Setting Collection Intervals (optional)

The Collection Interval fields in the agent properties are used to set the sample frequencies. Default collection frequencies (in seconds) have already been set for each table below.

• Blocked Transactions – Default set to 60 seconds.
• Buffer Pool (i.e. Innodb_Buffer_Pool) – Default set to 300 seconds.
• Configuration Monitoring - Default set to 300 seconds.
• Connection_Status – Default set to 60 seconds.
• Database Information – Default set to 300 seconds.
• Database Stats – Default set to 300 seconds.
• Failed Logins - If the MySQL server version is 5.6.0+ and the performance_schema engine is enabled, the agent will collect information on failed login attempts at this interval, shown on the Connections dashboard. Default set to 300 seconds.
• Galera – Default set to 60 seconds.
• Handler – Default set to 300 seconds.
• Index Structure - Default set to 3600 seconds.
• Index/Table-level Compression – Default set to 1800 seconds.
• InnoDB Compression – Default set to 300 seconds.
• InnoDB Engine (i.e. Innodb_Storage_Engine) – Default set to 300 seconds.
• Joins – Default set to 300 seconds.
• Key Buffer – Default set to 300 seconds.
• Network Interface – Default set to 300 seconds.
• Query Cache – Default set to 300 seconds.
• Replication – Default set to 300 seconds.
• Sort Buffer – Default set to 300 seconds.
• Tables – Default set to 1800 seconds.
• Table Locks – Default set to 60 seconds.
• Thread Pool – Default set to 300 seconds.
• Top Sessions – Default set to 300 seconds.
• Transaction Log (i.e. Innodb_Transaction_Log) – Default set to 1800 seconds.
• Users – Default set to 3600 seconds.

Group Replication/InnoDB Cluster Monitoring

• Enable Monitoring – Enable this collection by setting to True.
• Collection Interval (sec) – Frequency of collection sample, 120 seconds by default.
• Member Host Aliases – This property list can be used to alter the host names of other members in the InnoDB cluster collected from this monitored server for display purposes and/or to reconcile host names if a different name is collected from other monitored member servers.

Setting Slow Query Log Monitoring Parameters (optional)

Click To See Full Image.

• Monitor Slow Query Log - Enable this collection by setting to True.
• File Access – If the MySQL Agent is running locally on the same machine as the MySQL server it is monitoring, you may select Local. The other option, Remote_SSH, allows an SSH connection to a remote server in order to retrieve the slow query log.
• Collection Period – How often collection occurs, in seconds. It is recommended that this setting be at least 30-60 minutes or more. Currently, unique queries can only be aggregated within their own collection period, so a lengthier collection period will provide a more accurate analysis of a unique query without having to navigate through too many data collections.
• Line Limit – Restricts the amount of lines read from the slow query log during each collection. Unless a truly exceptional amount of data is being generated to the slow query log (in which case the long_query_time parameter should probably be increased), this parameter should remain at 0. A value of -1 will cause the agent to read all previous data during its first collection rather than marking the current file position and reading from that point during its next collection.
• File Path – File path to slow query log directory.
• File Name – File name of slow query log. Optionally, for Local collection only, a regex string may be used and the most recently modified file matching the regex string name will be used.

If using remote file access, the following values must be provided

• Remote Hostname or IP – A hostname or IP address valid from the MySQL Agent’s location.
• SSH User – An SSH user with access to the slow query log’s location and file permissions to perform a read action.
• SSH Password – The password for the provided SSH user.

Additional Options (optional)

• Enable Dynamic Memory Allocation – Allows the agent to request more memory from the FglAM when monitoring a server with over 10,000 tables. Default is false.
• Agent Host Name – If a hostname alias is being used for monitoring purposes that is different than the one provided by the MySQL server, provide that here. If using the Infrastructure cartridge for OS monitoring, this name should match any alias provided for OS monitoring in order to link the data correctly.
• Server Time Zone Override – Allows the client to override the server time zone, usually for the purpose of correcting a java-incompatible time zone id. A list of acceptable time zone IDs can be found here .
• Client Time Zone Override – Allows the client to override the time zone used for time-based performance data. A list of acceptable time zone IDs can be found here .
• Exclude DB Table/Index Collections – Table and index collections for individual databases can be disabled here. This will decrease resource usage by the agent and used space in the FMS repository. By default, the list is populated with system databases.
• Skip Startup Connection Test – When the agent is first activated, it performs a connection test to ensure that a connection can be made to the MySQL server. If it cannot, it is assumed there is a persistent failure due to incorrect configuration or other issues which need to be resolved and the agent will show as failed until fixed and restarted. In cases like a restart when the agent and the MySQL server are located on the same machine and you expect the agent to activate before the server is accessible, you may want to disable this. If you wish to skip the connection test, set to true.
• Minimum TRX Block Time (sec) – This is the minimum wait time for a transaction to be considered deadlocked and thus valid for inclusion in the Blocked Transactions collection. The default time is 15 seconds.
• Include Views in Table Collection – Option to include Views in the table collection. Set to false by default.
• Use Alternate MariaDB JDBC Driver – Changes driver used by agent from MySQL/J connector to MariaDB driver. This driver can be used for MariaDB or MySQL and is useful as a workaround for a current bug with the MySQL/J connector with MySQL servers located on RHEL servers.
• Enable MariaDB trustServerCertificate – Enables a driver option available with the MariaDB driver that causes the client to trust the identity of the server it is connecting to without requiring a valid certificate. This can be enabled to work around certificate issues with little risk since no sensitive data will be sent over this connection to the target server.