8.2.12. MySQL resource usage

Resource usage statistics are available in the "MySQL" section on the "Load" tab:

At the top of the tab, the following buttons are displayed:

• Process list
• Slow queries
• InnoDB
• Variables
• Status

Next, buttons for viewing query execution statistics are displayed.

At the bottom, statistics on resource usage for the last week are displayed:

• Memory usage (MySQL and file system cache).
• CPU usage.

With the help of statistics you can monitor the load on the instance and determine the moment when you need to optimize databases, queries to them, upgrade the plan or configure replication.

To view the list of active processes, click "Process list".

The list displays the active processes of all users of all databases in the instance:

Queries that take 10 seconds or more to complete are highlighted in red, 1 second or more — in yellow. The ❌ button forcibly terminates the query.

Queries that take more than 5 seconds to complete are considered slow. The system automatically logs such queries so that they can be analyzed.

The "Slow queries" button opens a menu where you can:

• Download log in CSV format.
• Clear log.

A large number of slow queries can negatively affect the performance of sites that use instance databases and signal that you should consider optimizing the database, queries to it or the logic of the site scripts.

The "InnoDB" button displays a window with the results of the SHOW INNODB STATUS query — the main data in table form and the entire text of the query result:

The "Variables" button displays a window with a list of all global variables and their values:

A link to the official documentation with a detailed description is available next to each variable.

The "Status" button displays a window with the results of the SHOW STATUS query — a list of MySQL server status variables and their values (see official documentation):

A link to the official documentation with a detailed description is available next to each variable.

Viewing query execution statistics works based on MySQL Performance Schema and allows you to view for all databases or for a selected database the following information:

• "Popular tables" — a list of tables with the number of queries and wait time statistics for each of them.
• Lists of queries by various criteria with detailed information on each of them (number, average execution time, number of rows scanned, number of rows sent, maximum memory consumption, execution interval, query template and example):
  • "Popular queries".
  • "Queries with highest change of rows".
  • "Queries with highest number of rows sent".
  • "Queries that create temporary tables".
  • "Queries with full range scanning".
  • "Queries with highest memory consumption".
  • "Queries by execution time".
  • "Queries with highest row scans".
  • "Queries that create temporary tables on disk".
  • "Queries that scan entire tables for JOIN".
  • "Queries without index usage".
• "Popular triggers" — information about triggers.

Example of statistics for popular tables:

Example of statistics for popular queries: