In this blog post, 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.
I had a server with 55.0G of available memory. Percona Server for MySQL version:
|
1 |
Version | 5.6.35-80.0-log Percona Server (GPL), Release 80.0, Revision f113994f31<br> Built On | debian-linux-gnu x86_64 |
We have calculated approximately how much memory MySQL can use in a worst case scenario for max_connections=250:
|
1 |
mysql> select ((@@key_buffer_size+@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@innodb_additional_mem_pool_size+@@net_buffer_length+@@query_cache_size)/1024/1024/1024)+((@@sort_buffer_size+@@myisam_sort_buffer_size+@@read_buffer_size+@@join_buffer_size+@@read_rnd_buffer_size+@@thread_stack)/1024/1024/1024*250);<br>+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+<br>| ((@@key_buffer_size+@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@innodb_additional_mem_pool_size+@@net_buffer_length+@@query_cache_size)/1024/1024/1024)+((@@sort_buffer_size+@@myisam_sort_buffer_size+@@read_buffer_size+@@join_buffer_size+@@read_rnd |<br>+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+<br>| 12.445816040039 |<br>+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+<br>1 row in set (0.00 sec) |
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:
|
1 |
# Memory #####################################################<br> Total | 55.0G<br> Free | 8.2G<br> Used | physical = 46.8G, swap allocated = 0.0, swap used = 0.0, virtual = 46.8G<br> Shared | 560.0k<br> Buffers | 206.5M<br> Caches | 1.0G<br> Dirty | 7392 kB<br> UsedRSS | 45.3G<br> Swappiness | 60<br> DirtyPolicy | 20, 10<br> DirtyStatus | 0, 0 |
|
1 |
# Top Processes ##############################################<br> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND<br> 2074 mysql 20 0 53.091g 0.044t 8952 S 126.0 81.7 34919:49 mysqld |
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.
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_SCHEMAtables, several commands, and the userstat 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.
|
1 |
mysql> show global variables like 'user%';<br>+---------------+-------+<br>| Variable_name | Value |<br>+---------------+-------+<br>| userstat | ON |<br>+---------------+-------+<br><br>mysql> show global variables like 'thread_statistics%';<br>+-------------------------------+---------------------------+<br>| Variable_name | Value |<br>+-------------------------------+---------------------------+<br>| thread_statistics | ON |<br>+-------------------------------+---------------------------+ |
Since we saw many threads running, it was a good option to verify this as the cause of the issue.
I decided to apply some calculations, and the following test cases to verify the cause:
|
1 |
mysql> select * from THREAD_STATISTICS limit 1G<br>*************************** 1. row ***************************<br>THREAD_ID: 3566<br>TOTAL_CONNECTIONS: 1<br>CONCURRENT_CONNECTIONS: 0<br>CONNECTED_TIME: 30<br>BUSY_TIME: 0<br>CPU_TIME: 0<br>BYTES_RECEIVED: 495<br>BYTES_SENT: 0<br>BINLOG_BYTES_WRITTEN: 0<br>ROWS_FETCHED: 27<br>ROWS_UPDATED: 0<br>TABLE_ROWS_READ: 0<br>SELECT_COMMANDS: 11<br>UPDATE_COMMANDS: 0<br>OTHER_COMMANDS: 0<br>COMMIT_TRANSACTIONS: 0<br>ROLLBACK_TRANSACTIONS: 0<br>DENIED_CONNECTIONS: 0<br>LOST_CONNECTIONS: 0<br>ACCESS_DENIED: 0<br>EMPTY_QUERIES: 0<br>TOTAL_SSL_CONNECTIONS: 1<br>1 row in set (0,00 sec) |
|
1 |
mysql> select count(*) from information_schema.thread_statistics; <br>+----------+ <br>| count(*) | <br>+----------+ <br>| 7864343 | <br>+----------+ <br>1 row in set (15.35 sec) |
In an hour:
|
1 |
mysql> select count(*) from information_schema.thread_statistics; <br>+----------+ <br>| count(*) | <br>+----------+ <br>| 12190801 | <br>+----------+ <br>1 row in set (24.46 sec) |
|
1 |
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND<br>2096 mysql 20 0 12.164g 0.010t 16036 S 173.4 18.9 2274:51 mysqld |
|
1 |
mysql> flush thread_statistics;<br><br>mysql> select count(*) from information_schema.thread_statistics;<br>+----------+<br>| count(*) |<br>+----------+<br>| 0 |<br>+----------+<br>1 row in set (00.00 sec) |
|
1 |
ID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2096 mysql 20 0 12.164g 7.855g 16036 S 99.7 14.3 2286:24 mysqld |
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.
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.
Resources
RELATED POSTS
