Difference between current_allocation s total_allocation in MySQL sys.memory_by_thread_by_current_bytes

Question

I'm trying to debug a memory leak in MySQL 5.7.37 and running the following query gives me insights about the threads running and the memory allocation for each thread.

select * from sys.memory_by_thread_by_current_bytes

The output shows some of the threads having the total_allocated value in GiB. However, the current_allocated value is still in MiB. Some of the values I see are as follows:

• current_count_used => 1783
• current_allocated => 262.46MiB
• current_avg_alloc => 150.74KiB
• current_max_alloc => 259.49MiB
• total_alloc => 16.07GiB

How could I interpret the above data? Does that mean the total_allocated memory is reserved from the RAM for this thread?

Answer 1

As it is mentioned in the documentation:
current_allocated - The current number of allocated bytes that have not been freed yet for the thread

total_allocated - The total memory allocation in bytes for the thread.

Additional queries may give you some more information

select * from sys.memory_global_total;
select thd_id,conn_id, current_memory from sys.session;
select * from memory_summary_by_thread_by_event_name where thread_id= "ID of the thread with high memory usage" order by CURRENT_NUMBER_OF_BYTES_USED;
select thread_id, user, current_allocated from memory_by_thread_by_current_bytes;

Common causes for high memory usage can be:

• Multiple Statements in One Query
• Buffer pool issues (can be because of "cold" database (fresh start), a lot of dirty pages)
• Temporary table issues
• other issues

For more details you can check this page.