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
MySQL Dashboard

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:

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

Prepare the device to start monitoring

To monitor MySQL:

  1. Associate appropriate database credentials to the discovered database.
  2. 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 TypeTemplate Name
AgentMySQL Global Performance Statistics
MySQL InnoDB Statistics
MySQL Variable Statistics
GatewayAdvanced 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

MetricMetric Display NameUnitDescription
mysql.innodb.row.lock.waitsMySQL Innodb Row Lock waitscountTotal number of times the InnoDB table operations are kept on hold due to a row lock.
mysql.aborted.clientsMySQL Aborted ClientscountThe number of connected aborted when the client missed to close the connection appropriately.
mysql.aborted.connectsMySQL Aborted ConnectscountThe number of failed attempts to connect to the MySQL server.
mysql.active.transactionsMySQL Active TransactionscountNumber of active transactions
mysql.bytes.receivedMySQL Bytes Receivedbytespersec(bps)The number of bytes received from all clients.
mysql.bytes.sentMySQL Bytes Sentbytespersec(bps)The number of bytes sent to all clients.
mysql.connected.threads.countMySQL Connected Threads count-The number of MySQL connected threads.
mysql.created.threadsMySQL Created ThreadscountThe number of threads created to handle connections.
mysql.createdatabase.commandsMySQL Createdatabase Commandspersecond(psec)The number of times CREATE DATABASE command is executed
mysql.createtable.commandsMySQL CreateTable Commandspersecond(psec)The number of times CREATE TABLE command is executed
mysql.createuser.commandsMySQL Createuser Commandspersecond(psec)The number of times CREATE USER command is executed
mysql.delete.commandsMySQL Delete Commandpersecond(psec)The number of times Delete command is executed
mysql.disk.temporarytables.createdMySQL Disk Temporary Tables Createdpersecond(psec)The rate of temporary tables created in seconds.
mysql.dropdatabase.commandsMySQL DropDatabase Commandspersecond(psec)The number of times DROP DATABASE command is executed
mysql.droptable.commandsMySQL DropTable Commandpersecond(psec)The number of times DROP TABLE command is executed
mysql.dropuser.commandsMySQL Dropuser Commandspersecond(psec)The number of times DROP USER command is executed.
mysql.innodb.bufferpool.dirtypagesMysql Innodb Bufferpool DirtypagescountThe current number of dirty pages in the InnoDB buffer pool.
mysql.innodb.bufferpool.pagesfreeMySQL Innodb Buffer Pool Pages freecountThe number of free pages in the InnoDB Buffer Pool.
mysql.innodb.bufferpool.pagestotalMysql Innodb Bufferpool PagestotalcountThe total number of pages in the InnoDB Buffer Pool.
mysql.innodb.bufferpool.utilizationMySQL Innodb Bufferpool Utilizationpercentage(%)The utilization of the InnoDB Buffer Pool.
mysql.innodb.buffer_pool_readsMySQL Innodb Bufferpool ReadscountThe number of logical reads that InnoDB reads directly from the disk and not from the buffer pool.
mysql.innodb.buffer_pool_read_requestsMySQL Innodb Bufferpool Read RequestscountThe number of reads added to the InnoDB buffer pool.
mysql.innodb.buffer_pool_write_requestsMySQL Innodb Bufferpool Write RequestscountThe number of writes added to the InnoDB buffer pool.
mysql.innodb.data.pending.readsMySQL Innodb Data Pending ReadscountThe current number of pending reads.
mysql.innodb.data.pending.writesMySQL Innodb Data Pending WritescountThe total number of data writes.
mysql.innodb.data.readsMySQL Innodb Data ReadscountThe total number of data reads (OS file reads).
mysql.innodb.data.writesMySQL InnoDB Data WritescountThe total number of data writes.
mysql.innodb.row.lock.timemysql Innodb Row Lock Timemilliseconds (ms)The total time spent in acquiring row locks for InnoDB tables in milliseconds.
mysql.innodb.row.lock.waitsMySQL Innodb Row Lock WaitscountThe number of times operations on InnoDB tables waits for a row lock.
mysql.insert.commandsMySQL Insert Commandpersecond(psec)The number of times Insert command is executed.
mysql.internal.connection.errorsMySQL Internal Connection ErrorscountThe 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_errorsMySQL Max Connections Connection ErrorscountThe number of connections rejected due to internal server errors.
mysql.open.filesMySQL Open Filescountfunction cannot be counted.
mysql.open.tablesMySQL Open TablescountThe number of tables opened with my_open().
mysql.performance.table_locks_immediateMySQL Performance Table Locks ImmediatecountThe number of times a table lock request is granted immediately.
mysql.rollback.commandsMySQL Rollback Commandpersecond(psec)The number of times RollBack command is executed
mysql.running.threadsMySQL Running ThreadscountThe number of threads that are not sleeping.
mysql.select.commandsMySQL Select Commandpersecond(psec)The number of times SELECT command is executed.
mysql.server.connectionsMySQL Server ConnectionscountThe number of connection attempts to the MySQL server.
mysql.slow.queriesMySQL Slow QueriesThe total number of slow queries that exceeded the long_query_time.
mysql.ssl.client.connectsMySQL SSL Client ConnectscountThe number of SSL connection attempts to an SSL-enabled master.
mysql.streams.openMySQL Streams OpencountThe number of streams that are open (used mainly for logging).
mysql.table.locks_immediateMySQL Table Locks ImmediatecountThe number of times a table lock request is granted immediately.
mysql.temporary.files.createdMySQL Temporary Files Createdpersecond(psec)The rate of temporary files created in seconds.
mysql.threads.createdMySQL Threads CreatedcountThe number of threads created to handle connections. Increase the thread_cache_size value, when the Threads_created increases.
mysql.threads.stacksizeMySQL 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.commandsMySQL Update Commandspersecond (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 ParameterDescription
Connection Time OutThe 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 TypeTo 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 PortThe 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 NameThe 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.

MySQL

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:

  1. Verify server and gateway connectivity using Ping.
ping <IP Address>

Example

ping 12.26.105.173
  1. Verify Telnet-to-server listening port connectivity.
telnet <IP Address> <Port>

Example

telnet 172.26.105.173 1433
  1. 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”