<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Thread_Statistics and High Memory Usage

Published on
114 Points
114 Views
Last Modified:
Percona
Percona is the only company that delivers enterprise-class software, support, consulting and managed services.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.

In this article, we’ll look at how using thread_statistics can cause high memory usage.


I was recently working on a high memory usage issue for one of our clients, and made some interesting discoveries: high memory usage with no bounds. It was really tricky to diagnose.


Below, I am going to show you how to identify that having thread_statistics enabled causes high memory usage on busy systems with many threads.


Part 1: Issue Background


I had a server with 55.0G of available memory. Percona Server for MySQL version:

So in our case, this shouldn’t be more than ~12.5G.


After MySQL Server has been restarted, it allocated about 7G. After running for a week, it reached 44G:

I checked everything that could be related to the high memory usage (for example, operating system settings such as Transparent Huge Pages (THP), etc.). But I still didn’t find the cause (THP was disabled on the server). I asked my teammates if they had any ideas.


Part 2: Team Is on Rescue


After brainstorming and reviewing the status, metrics and profiles again and again, my colleague (Yves Trudeau) pointed out that User Statistics is enabled on the server.

User Statistics adds several INFORMATION_SCHEMA tables, several commands, and theuserstat variable. The tables and commands can be used to better understand different server activity, and to identify the different load sources. Check out the documentation for more information.

Since we saw many threads running, it was a good option to verify this as the cause of the issue.


Part 3: Cause Verification – Did It Really Eat Our Memory?


I decided to apply some calculations, and the following test cases to verify the cause:

  1. Looking at the THREAD_STATISTICS table in the INFORMATION_SCHEMA, we can see that for each connection there is a row like the following:
  2. We have 22 columns, each of them BIGINT, which gives us ~ 176 bytes per row.

Let’s calculate how many rows we have in this table at this time, and check once again in an hour:As we can see, memory usage drops to the approximate value of 2G that we had calculated earlier!

That was the root cause of the high memory usage in this case.

Conclusion


User Statistics (basically Thread_Statistics) is a great feature that allows us to identify load sources and better understand server activity. At the same time, though, it can be dangerous (from the memory usage point of view) to use as a permanent monitoring solution due to no limitations on memory usage.

As a reminder, thread_statistics is NOT enabled by default when you enable User_Statistics. If you have enabled Thread_Statistics for monitoring purposes, please don’t forget to pay attention to it.


As a next step, we are considering submitting a feature request to implement some default limits that can prevent Out of Memory issues on busy systems.


0
Comment
Author:Percona

By clicking you agree to the Terms of Use and Privacy Policy.
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Join & Write a Comment

This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month