Chapter 18 Reports and Graphs
Prev |  | Next

## Chapter 18 Reports and Graphs

This chapter describes the Reports and Graphs available in MySQL Enterprise Monitor.

## 18.1 All Timeseries Graphs

This section describes the All Timeseries Graphs page.

### 18.1.1 Graph Controls

This section describes the controls available on the All Timeseries Graphs page.

#### Asset Selector

The Asset Selector is a treeview container on the left side of the page. It lists all the hosts to which the user has access. The hosts are organised by group.

The group displayed depend on those configured. If no groups are configured, the All group is the only group displayed, and all hosts are contained within it.

The graphs displayed depend on what is selected in the Asset Selector. The Asset Selector displays the following:

- Group

- Host

- Instance

- Network Interface

- Filesystem

- Agent (if installed on host)

Important

The Asset Selector displays only those assets to which you have access.

The Asset Selector can be filtered to display specific assets. To filter the Asset Selector, click the filter icon in the top-right corner of the Asset Selector frame. The Filter field is displayed. Enter the text to filter on. The Asset Selector displays the filtered results.

To display monitored instances only, deselect the Show All Assets checkbox.

#### Graph Filter

The graph filter enables you to display a subset of the available graphs.

Table 18.1 Timeseries Graph Filter

Name | Description
--- | ---
Graph Name | Opens a drop-down menu listing the available search types: Contains Doesn't Contain Regex Negative Regex
Value | Free text field for the search term or regular expression.
Time Range | Drop-down lists containing the time periods to apply to the graphs. The possible values are: Interval: select the duration for the overview data. If you select 1 hour, the data collected in the last hour is displayed. From/To: select a date and time range for the overview data. <xi:include href="mem-ui-database-file-io.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/> <xi:include href="mem-innodb-bufferpool-report.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
Filter | Apply the defined filter.
Save as Default | Sets the selected group and time range as the default. It is not possible to save a date range, using From/To, as the default for a group.
Reset to Default | Resets the graph display to the previously saved values.

#### Graph Manipulation

This section describes the various actions you can perform on individual graphs.

- Graph Height: slider which enables you to increase or decrease the height of the graph in pixels. This slider does not affect the values of the x or y axes, just resizes the graph.

- Export as CSV: downloads a CSV containing all data currently displayed in the selected graph.

- Export as PNG: generates a PNG image file of the selected graph. The image is displayed in a pop-up. To save the image, right-click and select Save image as....

- Move: enables you to move the selected graph to another location on the page.

- Stacked/Line: enables you to change how the graph is displayed. Line displays a line graph, while Stacked displays each data source as a solid color.

- Legend: lists the sources of information displayed in the graph. The color of the name matches the line/stack used in the graph. To display individual sources, click the required source in the Legend. To highlight individual sources in the graph, hover the cursor over the source's name.

#### Graph Query Analysis

Graph Query Analysis enables you to examine the queries which were running during specific intervals. To open the Query Analyzer for a specific range on a graph, do the following:

1. On a graph, select a range by clicking at the required start point, and dragging the cursor across the graph until you reach the required interval endpoint and release the mouse button. This selects the range.

2. Two icons are displayed in the top-right corner of the selection. An x to close the selection, and a database icon. Click the database icon to open the Query Analyzer's Browse Queries page.

   Browse Queries displays all the queries which were running during the defined time period. This enables you to drill down into potential query bottlenecks and performance hotspots and tune your queries accordingly.

   For more information on the Query Analyzer, see Section 27.3, “Query Analyzer User Interface”.

### 18.1.2 Graph Types

The following are the graph types:

- `Individual`: A single Asset has multiple data sets graphed on a chart. For example, counts of SELECT, INSERT, UPDATE, and DELETE statements on a single instance.

- `Combined`: Multiple assets have a single data set, each graphed on one chart. For example, the count of selects for each of the five MySQL instances of a group.

- `Breakout`: One (smaller) graph per Asset in a collection, showing one or more data sets on each individual graph. For example, one graph per CPU on a Host, or in a cluster.

- `Aggregate`: One graph per collection of Assets, where the data sets across all Assets are combined via an aggregation operator. For example, one graph with each of the SUM(SELECT), SUM(INSERT), SUM(UPDATE), and SUM(DELETE) across the collection. Such as the group-level Database Activity - All MySQL Instances graph.

- `Treemap`: A 2D hierarchical proportional-representation graph. See Section 18.3, “InnoDB Buffer Pool Usage” for an example.

## 18.2 Database File I/O and Lock Waits

This chapter describes the Database File I/O and Lock Waits reports. These reports identify I/O hot spots and lock wait contention in your application using the `sys` schema, thereby enabling you to tune the performance of your queries.

Important

The Database File I/O requires the MySQL `sys` schema, which is supported on MySQL 5.6 and 5.7, only.

### 18.2.1 sys Schema

The `sys` schema is a set of views, stored procedures, and functions, which provide access to the instrumentation data of the Performance Schema.

The `sys` schema is installed by default in MySQL 5.7, but must be installed manually in earlier versions of MySQL.

On the Database File I/O and Lock Waits pages, if a compatible MySQL instance is selected, but `sys` schema is not installed, MySQL Enterprise Monitor prompts you to install it. To install `sys` schema, click Install MySQL sys schema. If the selected instance is incompatible, a message is displayed informing you that it is not possible to run these reports against the selected schema.

Important

If your instance already contains a schema named `sys`, you must rename it before installing MySQL `sys` schema.

For information on how to install sys schema from the command line, see the installation instructions within the github repository: sys schema on GitHub.

### 18.2.2 Database File I/O Graphs and Reports

This section describes the Database File I/O reports and graphs.

Each tab contains the following common elements:

- Show n Entries: Number of entries to show per page.

- Search: search the contents of the page.

- Show/Hide Columns: enables you to change the column set displayed on the page by selecting or deselecting the columns.

- Page Navigation: buttons enabling you to navigate the pages of the report.

For more information on the data retrieved in these reports, see the XREFTO REFMAN.

#### I/O By File

Shows the top global I/O consumers by latency, and by file. The data is retrieved from `sys.x$io_global_by_file_by_latency`, and sorted by total latency by default.

Figure 18.1 Database File I/O By File

#### I/O By Wait Type

Shows the top global I/O consumers by latency. The data is retrieved from `sys.x$io_global_by_wait_by_latency`, and sorted by total latency, by default.

This report is a combination of report and graphs. The graphs can be redrawn based on a time range. To change the time range, select one of the range buttons. Values range from 1 hour to 1 week.

Figure 18.2 Database File I/O By Wait Type Report

The following shows a subset of the graphs available on the I/O By Wait Type tab:

Figure 18.3 Database File I/O By Wait Type Graphs

#### I/O By Thread

Shows the top I/O consumers by thread, ordered by total latency. The data is retrieved from `sys.x$io_by_thread_by_latency`, and sorted by latency, by default.

Figure 18.4 Database File I/O By Thread

### 18.2.3 Lock Waits Report

To open the Lock Waits reports, select Lock Waits from the Reports & Graphs menu.

#### InnoDB Row Lock Waits

This report retrieves data on InnoDB row locks from `sys.x$innodb_lock_waits`.

#### Table Metadata Lock Waits

Important

Table Metadata Lock Waits is supported on MySQL 5.7 only. This report relies on instrumentation introduced in MySQL 5.7.

This report retrieves data on MySQL 5.7 table metadata locks from `sys.x$schema_table_lock_waits`.

## 18.3 InnoDB Buffer Pool Usage

The InnoDB Buffer Pool Usage Report displays the amount of space used in the InnoDB buffer pool and how the space is used. The report is displayed in grid format. Each block in the grid represents a particular type of data stored in the buffer pool. Click a block to display more details.

For more information on the InnoDB Buffer Pool, see InnoDB Buffer Pool Configuration and The InnoDB Buffer Pool

Important

This report requires the `INFORMATION_SCHEMA.INNODB_BUFFER_PAGE` table, which is available in MySQL Server version 5.5.28 or higher.

### Running the InnoDB Buffer Pool Usage Report

To run the usage report, do the following:

1. Navigate to the Reports & Graphs drop-down menu.

2. Select InnoDB Buffer Pool Usage.

   The Generate Report page is displayed.

   This page displays a warning about the table and resource requirements of the report generation process and prompts you to select a MySQL Server to run the report against.

   Important

   The report can take some time to return results. If no data is returned within 2 minutes, the report times out and an error is displayed.

3. Select the MySQL server from the asset tree.

   The Generate Report page is displayed.

4. Click Generate Report.

   The Loading buffer pool report progress message is displayed.

   Note

   If you click Reload while the report is generating, the report generation process is cancelled and restarted. If you navigate away from the progress page, the report generation process is cancelled.

5. The report is displayed.

Prev | Up | Next
Chapter 17 Replication Dashboard | Home | Chapter 19 Advisors