MySQL Database Monitoring

apmsaas
An extension of
DX SaaS
,
DX APM
for MySQL Databases provides visibility into the performance and availability of MySQL databases. You can proactively detect and isolate database-related performance problems and effectively communicate with database administrators for fast problem resolution.
In the Infrastructure Agent, the agent extension exists in the following location:
apmia\extensions\MySQL
3
2
Features and Benefits
DX APM
for MySQL Databases enables you to:
  • Monitor the performance of MySQL databases
  • Correlate MySQL database to application activity
  • Verify the application use of MySQL database activity capacity
  • Share critical performance and availability information with DBAs
DX APM
for MySQL Databases provides real-time visibility into critical database resources such as bytes that are sent and received, connections that are attempted, aborted, or failed, Innodb insights, database operations, resource utilization, file-per-table spaces, redo and undo logs, and transaction insights. You can see immediate time to value in the performance-triggered alerts.
DX APM
for MySQL Databases equips the application support personnel with the information to determine whether application performance issues are related to the database. By providing critical performance data, the MySQL extension facilitates collaboration with database administrators and eliminates finger-pointing. You can quickly detect, isolate, and eliminate the problems. As this product uses the
DX SaaS
platform, you can visualize, analyze, and query all the information that the MySQL extension gathers.
DX APM
for MySQL Databases monitors the following database resources:
  • Availability
  • Bytes Sent and Received
  • Connections Attempted, Aborted, and Failed
  • Innodb Insights
  • Database Operations
  • Resource Utilization
  • File-per-table Spaces
  • Redo and Undo Logs
  • Transaction Insights
DX APM
for MySQL Databases offers you the following benefits:
  • Seamless integration with
    DX SaaS
    , provides direct visibility into the MySQL database from within
    DX SaaS
  • Deeper visibility into back-end systems that affect the application availability and performance
  • Captures most important metrics, directly from MySQL performance metrics
  • Application administrators do not need to learn different tools to monitor MySQL databases
  • Constructive communication between application administrators and database administrators; an application administrator can relay critical MySQL database information in the language that the database administrator understands.
Usage Scenarios
DX SaaS
and application administrators can use
DX APM
for MySQL Databases to diagnose the following problems:
  • Problem root cause isolation:
    You can effectively triage problems, showing whether the problem is with your application, the application server, or the MySQL database.
  • MySQL Database instance availability:
    When applications are running slowly or returning errors, the extension reports availability of all monitored MySQL database instances. Administrators can rapidly determine which MySQL databases are unavailable.
  • Slow response times:
    The extension shows data to determine the database resources that are causing performance slowdown.
Functionality
DX APM
for MySQL Databases is a standalone Java application that you can install on a separate host, which is different from the host on which MySQL resides.
DX APM
for MySQL Databases queries MySQL through JDBC for its health statistics, converts the data to
DX SaaS
metrics, and sends the information to the Enterprise Manager. You can view the information using the Metric browser.
One installation of
DX APM
for MySQL Databases can monitor multiple MySQL databases on multiple hosts.
MySQL Monitoring for DX APM SaaS
You install the
DX APM
Infrastructure Agent before you configure and use the extension.
Download and Install the Infrastructure Agent
After you get access to
DX APM
. You download and extract the Infrastructure Agent containing
DX APM
MySQL Monitoring.
Follow these steps:
  1. Click
    Open
    on the
    DX APM
    box.
    The
    DX APM
    home page appears.
  2. Click
    Download Agent
    at the top of any page.
    If you already connected one agent, you can only access the
    Download Agent
    button by clicking
    Agents
    in the left pane, under
    Settings
    .
  3. On the Select Agent to Download page, select the tab corresponding to your operating system.
  4. Under the Infrastructure category, select MySQL Monitoring as the agent.
  5. (Optional) Configure the agent options according to the needs of your environment.
  6. Click the Download and Install button.
    DX SaaS
    downloads the package to your computer.
Install and Run the Infrastructure Agent
You install the Infrastructure Agent before you install MySQL Monitoring.
Follow these steps:
  1. Navigate to the
    apmia
    directory.
  2. Run the appropriate command to install the Infrastructure Agent:
    UNIX:
    ./APMIACtrl.sh install
    Windows:
    APMIACtrl.bat install
You can review the Infrastructure Agent logs in the
<Infrastructure_Agent_Home>/logs
directory.
These log files are in the logs directory:
  • AutoProbe.log
  • IntroscopeAgent.log
  • APMIAgentConsole.log
Enable in DX APM SaaS Infrastructure Agent
You can enable the MySQL extension in the
DX APM
Infrastructure Agent.
Follow these steps:
  1. Navigate to the following location to open the
    Extensions.profile
    file:
    <Infrastructure_Agent_Home>\extensions\
  2. When you start the
    DX APM
    Infrastructure Agent, the
    Extensions.profile
    file populates the extension name as the value of the
    introscope.agent.extensions.bundles.boot.load
    property.
    If you do not find the extension name as the value, you need to manually enter the extension name as follows:
    introscope.agent.extensions.bundles.boot.load=MySQL
  3. Navigate to the following location to open the
    bundle.properties
    file:
    <Infrastructure_Agent_Home>\extensions\MySQL
  4. In the
    bundle.properties
    file, define the following properties:
Property Name
Description
introscope.agent.dbmonitor.mysql.profiles
Specifies the list of the MySQL server instance names that the MySQL monitors or Infrastructure Agent monitors.
Sample Value:
When you use this property to define the profile name as “
MySQL_Server1
”. Then, ensure that you add the same profile name in the property as follows:
introscope.agent.restmon.profile.list=
MySQL_Server1
,
MySQL_Server2
Here,
MySQL_profile
that is mentioned in the following property names represents "
MySQL_Server1
” that you define in the profile list.
introscope.agent.dbmonitor.mysql.profiles.{MySQL_profile#}.instanceName
Specifies the name of the MySQL instance that Infrastructure Agent monitors.
introscope.agent.dbmonitor.mysql.queryInterval
Specifies the interval between any two consecutive execution cycles of running all queries in MySQL.
introscope.agent.dbmonitor.mysql.profiles.{MySQL_profile#}.hostName
Specifies the name of the host on which MySQL is installed that Infrastructure Agent monitors.
Note:
Ensure that the hostname that is specified in the Infrastructure Agent is same as the hostname that is specified in the application that uses a MySQL database.
This ensures that the correlation of MySQL database to application occurs successfully.
Sample Values:
introscope.agent.dbmonitor.mysql.profiles.MySQL_Server1.hostName=host1
introscope.agent.dbmonitor.mysql.profiles.MySQL_Server2.hostName=host2
introscope.agent.dbmonitor.mysql.profiles.{MySQL_profile#}.port
Specifies the <port> which MySQL is running that Infrastructure Agent monitors.
Sample Values:
introscope.agent.dbmonitor.mysql.profiles.MySQL_Server1.port=80
introscope.agent.dbmonitor.mysql.profiles.{MySQL_profile#}.userName
Specifies the username that is used to access MySQL.
introscope.agent.dbmonitor.mysql.profiles.{MySQL_profile#}.password
Specifies the password for the username credentials that is used to access MySQL.
DX APM
supports the password encryption and the password is encrypted when the Infrastructure Agent starts.
introscope.agent.dbmonitor.mysql.profiles.{MySQL_profile#}.version
(Optional) Specifies the version of schema to be used for a particular version of MySQL.
For MySQL version, which is equal to
MySQL 5.6.XX
, you need to edit the
bundle.properties
file and provide the value of
5_6x
for this property as follows:
introscope.agent.dbmonitor.mysql.profiles.db1.version=5_6x
Configure MySQL Database Permissions
To monitor MySQL database, ensure that you have the following access privileges on all databases:
SELECT
,
PROCESS
, and
SHOW DATABASES
You can create a user with appropriate access privileges such as
SELECT
,
PROCESS
, and
SHOW DATABASES
.
To create a user, run the following command in the
mysqld
console. Here,
host
represents the host name or IP address of the system, which executes the MySQL database monitoring and
password
is used to authenticate a user.
GRANT SELECT,PROCESS,SHOW DATABASES on *.* to 'monitoruser'@'host' identified by 'password';
FLUSH privileges;
All permissions are READ-ONLY. You need to configure the access privileges for
all
databases, else metrics such as
DB Size Used in MB
,
Number of Databases
would report incorrect data in the
Metric View
.
However, if you do not want to grant access permissions to
all
permissions, you can grant permissions on
information_schema
and
performance_schema
for the metrics to be reflected appropriately in the
Metric View
.
MySQL Metrics
This section lists all the metrics for the respective Monitored Groups that you can collect using MySQL in the
Metric View.
In the
Metric View
, the MySQL server instance is displayed under the folder,
*SuperDomain*|
<DX APM SaaS Installation>
|Infrastructure|Agent|MySQL Databases|<Host Name>|<Schema Name>
as follows:
Metric Name
Description
Availability
Represents the availability of the database. The following values represent the availability status:
  • 0 - Database is down
  • 1 - Database is up
DB Size Used in MB
Represents the size of the MySQL database in MB.
Number of Databases
Represents the number of active databases.
Uptime (days)
Represents the time in days since the MySQL database server is up.
Client-Server:Bytes Received Per Interval
The number of bytes that the MySQL database server receives from all clients.
Client-Server:Bytes Sent Per Interval
The number of bytes that the MySQL database has sent to all clients.
Connections:Connection Refusal Rate
The percentage of connections from the clients that the database has refused.
Connections:Successful Attempts Per Interval
Represents the total number of connection attempts from a client to the MySQL database in a given interval.
Connections:Total Aborted Per Interval
The number of connections that are aborted because the client died without closing the connection properly in a given interval.
Connections:Total Attempts Per Interval
The number of successful and unsuccessful connection attempts from a client to the MySQL server in a given interval.
Connections:Total Failed Per Interval
The number of failed connection attempts from a client to the MySQL server in a given interval.
Efficiency|Cache|Key Cache:Read Hits (%)
The ratio of the number of key reads to the number of key read requests.
Efficiency|Cache|Key Cache:Read Requests Per Interval
The number key block read requests in a given interval.
Efficiency|Cache|Key Cache:Reads from Disk Per Interval
The number of key blocks that is read from a disk in a given interval.
Efficiency|Cache|Key Cache:Write Requests Per Interval
The number of key block write requests in a given interval.
Efficiency|Cache|Key Cache:Writes to Disk Per Interval
The number of key block that is written to a disk in a given interval.
Efficiency|Index:Joins
The ratio of the number of joins that perform table scans (since the tables do not use indexes) to the number of joins that used a range search on a reference table.
Efficiency|Index:Single Table
The number of joins that used ranges on the first table to the number of joins that performed a full scan of the first table.
Efficiency|InnoDB:Write Requests Waited (%)
The percentage of write requests which had to wait.
Efficiency|InnoDB|Row Locks:Average time to acquire a row lock (ms)
The average time that the operations have spent to acquire a row lock.
Efficiency|InnoDB|Row Locks:Row Lock Acquiring Time ms (Total)
The total time spent in acquiring row locks for InnoDB tables.
Efficiency|InnoDB|Row Locks:Waiting
The number of row locks currently being waited for by operations on InnoDB tables.
Efficiency|InnoDB|Waits for a Row Lock Per Interval
The number of operations that have waited for a row lock in a given interval.
Efficiency|Query:Slow Query Log
Specifies whether Slow Query Log is enabled or not.
Efficiency|Query:Slow Query Threshold (Seconds)
Specifies the minimum time for a query to be considered as slow.
Efficiency|Query:Slow query rate (%)
The percentage of queries that are slow.
Efficiency|Sort Buffer:Range Sorts
The number of sorts done using ranges.
Efficiency|Sort Buffer:Scan Sorts
The number of sorts that are done by scanning the table.
Efficiency|Sort Buffer:Temp Files Created
The temporary files that are created because of insufficient sort buffer size.
Efficiency|Table Locks:Lock Requests Waited (%)
The percentage of lock requests that had to wait.
Efficiency|Table Locks:Locks Acquired Immediately
The Table locks that are acquired immediately.
Efficiency|Table Locks:Table Lock Waits Per Interval
The Table lock waits in a given interval.
Efficiency|Threads:Slow Launch Threads
The number of slow launch threads.
InnoDB:Bytes Read Per Interval
The amount of data in bytes that the MySQL database server reads in a given interval.
InnoDB:Bytes Written Per Interval
The amount of data in bytes that is written to the MySQL database server in a given interval.
InnoDB:Cache Hit Ratio (%)
The number of failed connection attempts from a client to the MySQL database server.
InnoDB:InnoDB Log to Buffer Ratio (%)
Represents the ratio of the InnoDB Log Buffer with the Buffer Pool.
InnoDB:Rows Deleted Per Interval
The number of rows that are deleted from the InnoDB tables.
InnoDB:Rows Inserted Per Interval
The number of rows that are inserted into the InnoDB tables.
InnoDB:Rows Queried Per Interval
The number of rows that are read from the InnoDB tables.
InnoDB:Rows Updated Per Interval
The number of rows that are updated in the InnoDB tables.
InnoDB:fsync Operations Per Interval
The number of fsync() operations in a given interval. The frequency of fsync() calls is controlled using the
innodb_flush_method
configuration setting option.
Log|Binary Log Cache:Temp Files Used Per Interval
Represents the number of statements, which includes both transnational and non-transnational, that have used a temporary file.
Log|Binary Log Cache:Temp Files Used Percentage
The percentage of transactions that have used a temp file.
Log|Binary Log Cache:Used Per Interval
Represents the total number of statements, which includes both transnational and non-transnational, that have used the binary log cache.
Log|Redo Log:Bytes Written Per Interval
The number of bytes that is written to the InnoDB redo log files.
Log|Redo Log:Number of Pending fsync() Operations
The number of pending fsync() operations for the InnoDB redo log files.
Log|Redo Log:Pending Writes
The number of pending writes to the InnoDB redo log files.
Log|Redo Log:Physical Writes Per Interval
The number of physical writes to the InnoDB redo log file.
Log|Redo Log:Write Requests Per Interval
The number of write requests for the InnoDB redo log.
Log|Redo Log:Write Requests Waited (%)
Percentage of cache writes that had to wait.
Log|Redo Log:fsync() Writes Per Interval
The number of fsync() writes done to the InnoDB redo log files in a given interval.
Log|Undo Tablespaces:Number of Rollback Segments
Specifies the number of rollback segments that are allocated to each undo tablespace and the global temporary tablespace for transactions that generate undo records.
Operations:Read-Write Ratio (%)
Represents the ratio of the read statements that is executed to the write statements, in a given interval.
Operations:Statements Executed Per Interval
The total number of statements that all clients have sent to the MySQL database server.
Operations:Total Deletes Per Interval
The number of DELETE statements that are executed in a given interval.
Operations:Total Inserts Per Interval
The number of INSERT statements that are executed in a given interval.
Operations:Total Replaces Per Interval
The number of REPLACE statements that are executed in a given interval.
Operations:Total Selects Per Interval
The number of SELECT statements that are  executed in a given interval.
Operations:Total Updates Per Interval
The number of UPDATE statements that are executed in a given interval.
Resource Utilization:Connection Usage Rate (%)
The percentage of maximum allowed connections in use in MySQL server database.
Resource Utilization:InnoDB Buffer Pool Free
The number of free pages that exist in an InnoDB buffer pool.
Resource Utilization:InnoDB Buffer Pool Total
The total size of the InnoDB buffer pool.
Resource Utilization:InnoDB Buffer Pool Used (%)
The percentage of the InnoDB Buffer Pool that the MySQL database server had used.
Resource Utilization:Key Cache Total
The size of the buffer that the MySQL server had used for index blocks .
Resource Utilization:Key Cache Unused
The size in bytes of blocks in the key cache.
Resource Utilization:Key Cache Used (%)
The percentage of the Key Cache that the MySQL server had used.
Resource Utilization:Max Connection Usage Rate (%)
Highest percentage of maximum allowed connections since last restart of MySQL database server.
Resource Utilization:Max Memory Per Thread (KB)
Represents the configured maximum memory per thread buffers in KiloBytes.
Resource Utilization:Max Simultaneous Used Connections
Represents the maximum number of client connections that have been in use simultaneously in the MySQL database server.
Resource Utilization:Opened Files Per Interval
The number of files that have been opened with my_open() (a mysys library function) in the MySQL database server.
Resource Utilization:Slow Queries Per Interval
The number of queries that have taken more than the metric value of
slow query threshold
in seconds.
Resource Utilization:Threads Created (count)
The number of threads that are created to handle the client connections in MySQL database server.
Resource Utilization:Threads Re-used (%)
The percentage of connections for which the threads are re-used from a cache.
Resource Utilization:Total Active Threads
The number of threads that are not sleeping.
Resource Utilization:Total Cached Threads
The number of threads in a thread cache.
Resource Utilization:Total Connected Threads
The number of currently open client connections.
Resource Utilization:Total Size of Shared Buffers(KB)
The total number of the configured shared buffers size.
Tables:Disk Temporary Tables (%)
The percentage of the temp tables that are created in a disk.
Tables:Disk Temporary Tables Created Per Interval
The number of internal on-disk temporary tables that the MySQL database server creates while executing statements.
Tables:Full Table Scan Rate
The percentage of the rows that the MySQL database had accessed via full table scan.
Tables:Joins performing Full Table Scan Per Interval
The number of times a full table that the MySQL database has read for a SELECT.
Tables:Joins performing Table Scan (No Indexing)
The number of joins that perform table scans since the table do not use indexes.
Tables:Opened
The number of tables that have been opened. If Opened_tables is big.
Tables:Temporary Tables Created Per Interval
The number of internal temporary tables that the MySQL database server creates while executing statements.
Tablespaces|File-Per-Table Tablespaces:Enabled
If this metric is set to true, InnoDB creates tables in the file-per-table tablespaces. If this metric is set to false, InnoDB creates tables in the system tablespace.
Tablespaces|System Tablespace:InnoDB Data File Path
Defines the name of the InnoDB data file path.
Transactions:Committed Per Interval
Committed transactions in a given interval.
Transactions:Rollback (%)
Represents the percentage of the ratio of Rollback to commit ratio.
Transactions:Rolled Back Per Interval
Rolled back transactions in a given interval.
Transactions:Total Per Interval
Represents the total transactions, which includes both committed and rolled back in a given interval.
MySQL Attributes
This section lists all the attributes for the respective Monitored Groups that you can collect using MySQL in the
Metric View.
Attribute Name
Description
agent
Represents the agent with which the MySQL extension is associated.
layer
Defines the layer in APM terminology such as APM Infrastructure Layer, Infrastructure Layer, or Application Layer.
Hostname
Represents the name of the host on which MySQL is running. The hostname is independent of any properties in the
bundle.properties
file.
Database Connection
Represents the connection string from the client to the MySQL database server.
Database Host Name
Represents the hostname based on the configuration details that you provide. If you configure the host name as "localhost" in the
bundle.properties
file, this attribute represents a localhost. Else, this attribute represents a proper host name.
Database Name
Represents the
instancename
or
db-name
used for the JDBC connection. This attribute is also defined in the
bundle.properties
file.
Database Version
Represents the version of the MySQL database server.
Database OS
Represents the OS of the system or host in which the MySQL database server is installed.
Database Port
Specifies the port on which MySQL is running.
Database Type
Specifies the type of the database. For example, MYSQL_DB, SQL_SERVER_DB, and so on.
MySQL Alerts
Alerts are available against the metrics - Availability. You can create alert on any metric and configure the caution and danger values based on the user-specific requirement.
Alert Name
Description
Default Caution Value
Default Danger Value
All Instance Availability
Alert is triggered if Connection to Database instance is unavailable.
0
0
Viewing Additional Metrics of MySQL Version 5.6.XX
To view additional customized metrics for MySQL version, which is equal to
MySQL 5.6.XX
in the
Metric View
, you need to add the customized MySQL metric details by editing the
schema5_6x.json
file.
Example - View Additional Metrics of MySQL Version 5.6.XX
Consider a use case where you want to view a customized metric,
system_time_zone
, under the
General
category in the
Metric View
for MySQL version, which is equal to
MySQL 5.6.XX
.
To add and view the metric,
system_time_zone
, under the
General
category perform the following tasks.
Follow these steps:
  1. Navigate to the folder path,
    APMInstallationFolder\apmia\extensions\MySQL\config
  2. In the
    config
    folder, open the
    schema5_6x.json
    file.
  3. In the
    schema5_6x.json
    file, add the following code snippet:
{ "query":"show global variables where variable_name like 'system_time_zone'", "metrics":[ { "metricPath":[ { "name":"General:System Time Zone" } ], "metricType":"StringEvent", "metricValue":"$.resultSet[?(@.VARIABLE_NAME == 'system_time_zone')].VARIABLE_VALUE" } ], "calculatedMetrics":[ ] },
Viewing Additional Metrics of MySQL 5.7.XX, MySQL 6.X, and MySQL 7.X
To view additional customized metrics for MySQL version, which is equal to
MySQL 5.7.XX
,
MySQL 6.X
, or
7.X
in the
Metric View
, you need to add the customized MySQL metric details by editing the
schema.json
file.
Example - View Additional Metrics of MySQL 5.7.XX, MySQL 6.X, and MySQL 7.X
Consider a use case where you want to view a customized metric,
system_time_zone
, under the
General
category in the
Metric View
for MySQL version, which is greater than or equal to
MySQL 5.7.XX
,
MySQL 6.X
, or
MySQL 7.X
.
To add and view the metric,
system_time_zone
, under the
General
category perform the following tasks.
Follow these steps:
  1. Navigate to the folder path,
    APMInstallationFolder\apmia\extensions\MySQL\config
  2. In the
    config
    folder, open the
    schema.json
    file.
  3. In the
    schema.json
    file, add the following code snippet:
{ "query":"show global variables where variable_name like 'system_time_zone'", "metrics":[ { "metricPath":[ { "name":"General:System Time Zone" } ], "metricType":"StringEvent", "metricValue":"$.resultSet[?(@.Variable_name == 'system_time_zone')].Value" } ], "calculatedMetrics":[ ] },