Monitoring MySQL

Introduction

MySQL is an open-source relational database management system (RDBMS) based on Structured Query Language (SQL). While monitoring MySQL, the DBAs can:

Create custom SQL metrics and apply to the database.
Track and analyze the monitoring metrics.
Check the availability of the database.

Prerequisites

Database and port(3306) can be accessed by the gateway.
serveradmin privilege to monitor the database. For more information, see Monitoring Privileges.
A gateway management profile mapped to a resource.
Create credentials with type as Database before assigning a template to a resource. The Port, Connection Time Out, and Is Secure values are not used while creating credentials.

Supported version

MySQL Server 2008 or higher.
Java Database Connectivity(JDBC): 8.0.17.

Database discovery and monitoring

The administrator can deploy an agent or gateway to support MySQL server agent-based or agentless monitoring respectively.

Discovery using an agent

The agent auto-discovers the MySQL database and collects the following metric information:

MySQL-Engine information
MySQL-Recommendations
MySQL-Database information
MySQL-Performance information
MySQL-Slave information
MySQL-Server information
MySQL-Variable information
MySQL-Status information

You can also apply agent-based templates to initiate monitoring.

Discovery using the gateway

The gateway establishes a connection to the database using Java Database Connectivity (JDBC) Java API and collects monitoring metrics using SQL queries. To monitor the MySQL database, install gateway version 5.0.0 or later.

Use one of the following mechanisms to discover MySQL servers to add them to your inventory:

WMI
SSH
SNMP

Optionally, add a database server manually to the infrastructure inventory.

To add a resource manually to the inventory:

Select Infrastructure.
Click the Settings > Resource > Add.
From the Add Device window, provide the required details such as Device Type and Management Profile.
Click Save.

Prepare the device to start monitoring

To monitor MySQL:

Associate appropriate database credentials to the discovered database.
Assign one or more database-specific global monitors or templates on the device.
Optionally, create custom metrics or monitors using SQL queries and assign them to the database.

To track monitoring of MySQL database in your cloud environment, see Monitoring Cloud Database using Gateway.

Global templates and metrics

Global templates

MySQL Global Templates
Collector Type	Template Name
Agent	MySQL Global Performance Statistics
	MySQL InnoDB Statistics
	MySQL Variable Statistics
Gateway	Advanced Cloud Database Template for MySQL
	Advanced Cloud MySQL Database Command Template
	Advanced Cloud MySQL Database Connection Template
	Advanced Cloud MySQL Database innoDB Template
	Advanced Cloud MySQL Database Miscellaneous Template
	Advanced Cloud MySQL Database Network Template
	Advanced Cloud MySQL Database Table Template
	Advanced Cloud MySQL Database Threads Template
	G2 MySQL Database Command Template
	G2 MySQL Database Connection Template
	G2 MySQL Database innoDB Template
	G2 MySQL Database Miscellaneous Template
	G2 MySQL Database Network Template
	G2 MySQL Database Table Template
	G2 MySQL Database Threads Template

Global Metrics


Metric	Metric Display Name	Unit	Description
mysql.innodb.row.lock.waits	MySQL Innodb Row Lock waits	count	Total number of times the InnoDB table operations are kept on hold due to a row lock.
mysql.aborted.clients	MySQL Aborted Clients	count	The number of connected aborted when the client missed to close the connection appropriately.
mysql.aborted.connects	MySQL Aborted Connects	count	The number of failed attempts to connect to the MySQL server.
mysql.active.transactions	MySQL Active Transactions	count	Number of active transactions
mysql.bytes.received	MySQL Bytes Received	bytespersec(bps)	The number of bytes received from all clients.
mysql.bytes.sent	MySQL Bytes Sent	bytespersec(bps)	The number of bytes sent to all clients.
mysql.connected.threads.count	MySQL Connected Threads count	-	The number of MySQL connected threads.
mysql.created.threads	MySQL Created Threads	count	The number of threads created to handle connections.
mysql.createdatabase.commands	MySQL Createdatabase Commands	persecond(psec)	The number of times CREATE DATABASE command is executed
mysql.createtable.commands	MySQL CreateTable Commands	persecond(psec)	The number of times CREATE TABLE command is executed
mysql.createuser.commands	MySQL Createuser Commands	persecond(psec)	The number of times CREATE USER command is executed
mysql.delete.commands	MySQL Delete Command	persecond(psec)	The number of times Delete command is executed
mysql.disk.temporarytables.created	MySQL Disk Temporary Tables Created	persecond(psec)	The rate of temporary tables created in seconds.
mysql.dropdatabase.commands	MySQL DropDatabase Commands	persecond(psec)	The number of times DROP DATABASE command is executed
mysql.droptable.commands	MySQL DropTable Command	persecond(psec)	The number of times DROP TABLE command is executed
mysql.dropuser.commands	MySQL Dropuser Commands	persecond(psec)	The number of times DROP USER command is executed.
mysql.innodb.bufferpool.dirtypages	Mysql Innodb Bufferpool Dirtypages	count	The current number of dirty pages in the InnoDB buffer pool.
mysql.innodb.bufferpool.pagesfree	MySQL Innodb Buffer Pool Pages free	count	The number of free pages in the InnoDB Buffer Pool.
mysql.innodb.bufferpool.pagestotal	Mysql Innodb Bufferpool Pagestotal	count	The total number of pages in the InnoDB Buffer Pool.
mysql.innodb.bufferpool.utilization	MySQL Innodb Bufferpool Utilization	percentage(%)	The utilization of the InnoDB Buffer Pool.
mysql.innodb.buffer_pool_reads	MySQL Innodb Bufferpool Reads	count	The number of logical reads that InnoDB reads directly from the disk and not from the buffer pool.
mysql.innodb.buffer_pool_read_requests	MySQL Innodb Bufferpool Read Requests	count	The number of reads added to the InnoDB buffer pool.
mysql.innodb.buffer_pool_write_requests	MySQL Innodb Bufferpool Write Requests	count	The number of writes added to the InnoDB buffer pool.
mysql.innodb.data.pending.reads	MySQL Innodb Data Pending Reads	count	The current number of pending reads.
mysql.innodb.data.pending.writes	MySQL Innodb Data Pending Writes	count	The total number of data writes.
mysql.innodb.data.reads	MySQL Innodb Data Reads	count	The total number of data reads (OS file reads).
mysql.innodb.data.writes	MySQL InnoDB Data Writes	count	The total number of data writes.
mysql.innodb.row.lock.time	mysql Innodb Row Lock Time	milliseconds (ms)	The total time spent in acquiring row locks for InnoDB tables in milliseconds.
mysql.innodb.row.lock.waits	MySQL Innodb Row Lock Waits	count	The number of times operations on InnoDB tables waits for a row lock.
mysql.insert.commands	MySQL Insert Command	persecond(psec)	The number of times Insert command is executed.
mysql.internal.connection.errors	MySQL Internal Connection Errors	count	The number of connections rejected due to internal errors in the server such as failure to start a new thread or an out-of-memory condition.
mysql.max.connections.connection_errors	MySQL Max Connections Connection Errors	count	The number of connections rejected due to internal server errors.
mysql.open.files	MySQL Open Files	count	function cannot be counted.
mysql.open.tables	MySQL Open Tables	count	The number of tables opened with my_open().
mysql.performance.table_locks_immediate	MySQL Performance Table Locks Immediate	count	The number of times a table lock request is granted immediately.
mysql.rollback.commands	MySQL Rollback Command	persecond(psec)	The number of times RollBack command is executed
mysql.running.threads	MySQL Running Threads	count	The number of threads that are not sleeping.
mysql.select.commands	MySQL Select Command	persecond(psec)	The number of times SELECT command is executed.
mysql.server.connections	MySQL Server Connections	count	The number of connection attempts to the MySQL server.
mysql.slow.queries	MySQL Slow Queries		The total number of slow queries that exceeded the long_query_time.
mysql.ssl.client.connects	MySQL SSL Client Connects	count	The number of SSL connection attempts to an SSL-enabled master.
mysql.streams.open	MySQL Streams Open	count	The number of streams that are open (used mainly for logging).
mysql.table.locks_immediate	MySQL Table Locks Immediate	count	The number of times a table lock request is granted immediately.
mysql.temporary.files.created	MySQL Temporary Files Created	persecond(psec)	The rate of temporary files created in seconds.
mysql.threads.created	MySQL Threads Created	count	The number of threads created to handle connections. Increase the thread_cache_size value, when the Threads_created increases.
mysql.threads.stacksize	MySQL Threads Stacksize	-	The stack size for each thread. Majority of the limits detected by the crash-me test are dependent on this value.
mysql.update.commands	MySQL Update Commands	persecond (psec)	The number of times UPDATE command is executed.

Create custom metrics

To create a metric using SQL query for MySQL monitoring, navigate to Monitoring > Metrics > Create Metric. For example, to create a metric to check the active transactions use:

SELECT count(*) from performance_schema.events_transactions_current where state='ACTIVE'\n

For more information, see Manage Metrics.

Assign templates from setup

Assign MySQL templates to one or more resources for a selected client and modify the configuration parameters while assigning templates. For more information, see Assign Templates from Setup.

Assign templates at the device level

Applying MySQL templates at the device level helps you to assign one or more templates to a specific resource. You can change the configuration parameter default values while assigning the templates. For more information, see Assign Templates at the Device Level.

Template configuration parameters:


Configuration Parameter	Description
Connection Time Out	The maximum time to connect to the database. The driver waits for the specified time before timing out due to connection failure. The default time is 15000 milliseconds(ms).
Service Transport Type	To configure the database at a secure end-point. The default type is In-secure. The connection is Secure when the data sent between the client and server is encrypted.
Service Port	The port number where the database is running. The connection is made to the specified port if you specify the port and the database name. The default port is 3306.
DB Instance Name	The name of the database to connect to. The default name is root. If you have multiple database names with different ports, use the following syntax: `DBname1:Port1, DBname2:Port2,..` (Comma-separated). Important! Create separate credential sets on the resource if databases consist of different users. For example, if DBname 1 has credential set A and DBname 2 has credential set B, then create separate credentials sets on the resource. If you have multiple database names with the same port, use the following syntax: `DBname1, DBname2,..` (Comma-separated).

Assign template from device management policies

Device management policies help manage resources. You can assign monitoring templates, knowledge base articles, and custom attributes using device management policies. The device management policy can be applied to one or a set of resources. For more information, see Create Policies.

View resource metrics

The gateway monitors the application using the assigned templates and displays the results in graphical format. To view resource metrics, click the desired database resource name > resource Details > Metrics.

Troubleshooting

Verify the following if the graphs are not plotted correctly:

The resource is accessible and Telnet is established for the IP address and port.
Valid credentials are used for the resource.

If you experience MySQL monitoring issues, follow these troubleshooting steps:

Verify server and gateway connectivity using Ping.

ping <IP Address>

Example

ping 12.26.105.173

Verify Telnet-to-server listening port connectivity.

telnet <IP Address> <Port>

Example

telnet 172.26.105.173 1433

Execute the following commands from GCLI to verify the database connectivity:

gcli

db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <ReadTimeout> <Secure Flag> <Query>

Example

db mysql 172.26.105.173 mysql Pass@123 5432 root 15000 10000 insecure "show global status like 'innodb_row_lock_waits"

From gateway version 5.3.0, use the following format to execute the command along with the result-set:

db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <Read Timeout> <Secure Flag> <ResultSet?: Yes/No> <Query>

Example

db mssql 172.26.105.173 mysqlPass@123 5432 root 15000 10000 insecure Yes "show global status like 'innodb_row_lock_waits”