Monitor MySQL memory usage – Part 1 (Performance Schema)

On my day-to-day activities, one of the most classic cases that I get is:

I think that my database has a memory leak. Where MySQL is spending so much memory?

So, saying that, I intend to launch a blog post series that might help tackle this issue. For those who are using MySQL 5.7 things are much easier. MySQL brings memory instruments where it is possible to monitor memory usage in detail.

In my experience, most cases are bad parametrization or heavy queries running. Only a tiny percentage are memory leak bugs de facto. However, they do exist and they might require a deeper analysis. For now, let’s focus on how to track memory usage with Performance Schema.

It is possible to see that MySQL 5.7 brings a huge improvement in the number of memory instruments:

Comparing with MySQL 5.6:

The next set of queries is designed to provide four different views of MySQL memory usage: global, per host, per thread and finally, per user. First, to start monitoring memory usage, it is necessary to enable the instrumentation:

Next, the queries to monitor memory usage:

And finally, if even with memory instruments you couldn’t tackle what is going on, stay tuned. Next blog post, I will show how to use Valgrind.

References:

References:

https://www.percona.com/blog/2019/08/14/mysql-8-and-mysql-5-7-memory-consumption-on-small-devices/

https://www.percona.com/blog/2017/07/11/thread_statistics-and-high-memory-usage/

https://www.fromdual.com/who-else-is-using-my-memory-file-system-cache-analysis

Bugs:

https://jira.percona.com/browse/PS-1096

https://bugs.launchpad.net/percona-server/+bug/1620152

https://bugs.mysql.com/bug.php?id=91710

Leave a Reply