Examining MySQL Memory Usage with Performance Schema

OTUS expert – Vladislav Rodin invites everyone to free demo-lesson on the topic: “MySQL indexes: best practices and pitfalls”… The lesson was specially prepared for super-intensive “DBMS in highly loaded systems”

And we traditionally share with you a useful translation on the topic.


Understanding how MySQL uses memory is key to tuning your system for optimal performance, as well as troubleshooting abnormal memory usage, such as when you have a MySQL server using much more memory than you would expect based on configuration parameters.

At the dawn of MySQL, understanding all the subtleties and nuances of memory use was difficult and involved a lot of guesswork. Perhaps some queries require a large temporary table or large memory allocations for stored user variables? Are there any stored procedures that are taking up unexpectedly large amounts of memory? Anything could be the cause of MySQL’s overuse of memory, but you had no easy ways to figure out and be sure what.

This all changed in MySQL 5.7, which added the Performance Schema memory tracking toolkit, and MySQL 8.0 enabled this toolkit by default, so you can get this data from almost any running instance.

If you’re looking for information about current memory usage, the Sys schema provides a fantastic set of information:

mysql> select event_name,current_alloc from sys.memory_global_by_current_bytes limit 10;
+---------------------------------------------------------------+---------------+
| event_name                                                    | current_alloc |
+---------------------------------------------------------------+---------------+
| memory/innodb/buf_buf_pool                                    | 262.12 MiB    |
| memory/temptable/physical_ram                                 | 64.00 MiB     |
| memory/performance_schema/events_statements_summary_by_digest | 39.67 MiB     |
| memory/sql/TABLE                                              | 33.32 MiB     |
| memory/innodb/ut0link_buf                                     | 24.00 MiB     |
| memory/innodb/lock0lock                                       | 20.51 MiB     |
| memory/innodb/memory                                          | 17.79 MiB     |
| memory/innodb/buf0dblwr                                       | 17.08 MiB     |
| memory/innodb/ut0new                                          | 16.08 MiB     |
| memory/performance_schema/events_statements_history_long      | 13.89 MiB     |
+---------------------------------------------------------------+---------------+
10 rows in set (0.01 sec)

This view displays the current total allocated memory. You can also dig deeper by looking at the memory allocated to connections from different hosts:

mysql> select host,current_allocated from memory_by_host_by_current_bytes;
+-------------------------------+-------------------+
| host                          | current_allocated |
+-------------------------------+-------------------+
| localhost                     | 1.19 GiB          |
| background                    | 101.28 MiB        |
| li1317-164.members.linode.com | 49.61 MiB         |
| li1319-234.members.linode.com | 27.90 MiB         |
| li1316-24.members.linode.com  | 27.00 MiB         |
+-------------------------------+-------------------+
5 rows in set (0.02 sec)

Or even check memory allocation for a given thread_id, which can be very useful for diagnosing transactions or queries that require large amounts of memory:

mysql> select thread_id,user,current_allocated from memory_by_thread_by_current_bytes limit 5;
+-----------+------------------------------------+-------------------+
| thread_id | user                               | current_allocated |
+-----------+------------------------------------+-------------------+
|        44 | innodb/srv_worker_thread           | 1.99 MiB          |
|        48 | innodb/srv_worker_thread           | 1.16 MiB          |
|     54322 | root@localhost                     | 1.10 MiB          |
|        46 | innodb/srv_worker_thread           | 777.29 KiB        |
|     43881 | app1@li1317-164.members.linode.com | 274.84 KiB        |
+-----------+------------------------------------+-------------------+
5 rows in set (0.43 sec)

Studying current statistics is not very useful in diagnosing past incidents, or even in answering the question whether it is normal that a particular object takes up so much memory, or is this already excessive. A trackable story is better for this … that’s what it is for Percona Monitoring and Management (PMM).

Unfortunately, starting with PMM 2.11, we do not include the Performance Schema memory tools in the release. However, it is quite easy to add it using Custom Queries.

MySQL custom queries

Custom Queries is a great feature that allows you to get statistics from your local MySQL instance using standard SQL queries and makes them available along with other metrics collected by the MySQL exporter. This can be used for Performance Schema tables, Information Schema tables, or even queries against your own schema to expose data that you deem relevant to your application. For more information read our article Custom Queries in Percona Monitoring and Management

You can set up custom queries to read memory usage statistics from the MySQL Performance Schema as follows …

cd /usr/local/percona/pmm2/collectors/custom-queries/mysql/medium-resolution
wget https://raw.githubusercontent.com/Percona-Lab/pmm-custom-queries/master/mysql/ps-memory-summary.yml

when installing dashboard for monitoring MySQL memory usage (MySQL Memory Usage Details dashboard) from Grafana.com. Let’s see what you get by installing this dashboard.

First, we have a block that has nothing to do with the information we get from the Performance Schema, but provides some useful information for interpreting this information:

We can see how long the MySQL system and process has been running, how much memory the system has and how much is available whenever any aggressive swap operation occurs, etc., all of which helps us know when MySQL is currently uses too much memory … or too little. We can output the number of current connections, which is a known factor in high memory usage, along with a couple of other metrics that can affect memory consumption.

The MySQL Memory Usage Summary shows how much memory various objects hold, as well as the total amount of measured memory allocated by the MySQL process.

With hundreds of different places where memory can be allocated, not even the largest memory consumers can accumulate quite a lot of overhead:

When memory is allocated by host and user, these views only cover memory that can be assigned to specific connections. But it’s a great tool to find out if there are hosts, users, or applications that are significant memory consumers.

You can also view information about memory usage by specific accounts (a specific user from a specific host). That’s all for dashboards, as specific connection IDs (and thread IDs) tend to change so quickly that they aren’t particularly useful in long-term capture.

You may have also noticed that I have disabled from the user’s summary pmm @ localhostbecause it looks like this user’s workload is causing a memory accounting error and unrealistic numbers are being reported (this is also something you should keep in mind to avoid blindly trusting the data you see).

Finally, you can look at the memory allocation across different subsystems, which can be quite useful for advanced analysis when only the global top consumers do not provide enough information.

What do you think? This would be helpful to you, or you would like to see more additional visualization before we consider including it in Percona Monitoring and Management?


Sign up for a free lesson.


Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *