Search code examples
mysqlconnectionamazon-rds

MySQL 8.0.34 - managing persistent connection memory usage on AWS RDS from Backend Systems

01.26.2024 Update Below 01.25.2024 Update Below 01.24.2024 Update Below

Server Details Here: Server Details

We have an issue where some old (bad) legacy code of our backend system is keeping MySQL connections open for days and using them for tons of different things, which is eating the freeable memory of our system.

Is there any way to control the amount of memory a connection is allowed to "eat" if it's a long-term reused connection (some of them hold 200MB+ each)? Is there a way to have a connection release its memory hold without severing the connection?

Trying to find a solution while the backend team addresses writing better connection management code (they're not supposed to hold connections open indefinitely).

Here's a picture of the connection RAM. It's NUTS.

RAM usage of our Read Replica

A core part of this issue is I can't find 100% accurate and definitive information on exactly what parameters define the memory limit of a connection. One site says one grouping, and another has ... another. One site says @@tmp_table_size is connection based, and another says it's "global". I can't believe how much conflicting information is out there... so right now I can't even figure out what parameters define the max memory usage of a connection. Any confirmed details on this would be greatly appreciated.

Here's the values of the parameters I've been told control the memory limits of each connection.

@@max_allowed_packet: 64MB
@@sort_buffer_size: 3.81MB
@@read_buffer_size: 256KB
@@read_rnd_buffer_size: 512KB
@@join_buffer_size: 256KB
@@net_buffer_length: 16KB
@@tmp_table_size: 16777216 (107.50MB)
@@max_heap_table_size: 16777216 (16MB)

What's more confusing is here's a more recent snapshot showing threads in excess of 300MB nearing 400MB. How is this even possible?

2nd capture

Here's another shot showing sys.processlist.

sys.processlist

I honestly don't know how to dig into this to understand what's going on...

Here's a snapshot of a thread that's using 132MB. Note this is after we rebooted the server so it's not near the 400MB level. Notice anything strange?

enter image description here

Here's the SQL I use to get the memory details for the threads and processes:

  -- Memory used by the processes.
SELECT
    th.THREAD_ID,
    th.PROCESSLIST_ID,
    th.PROCESSLIST_USER,
    th.PROCESSLIST_HOST,
    th.PROCESSLIST_DB,
    th.PROCESSLIST_COMMAND,
    th.PROCESSLIST_TIME,
    ev.EVENT_NAME,
    FORMAT_BYTES(ev.CURRENT_NUMBER_OF_BYTES_USED)
FROM
  performance_schema.threads th
  JOIN performance_schema.memory_summary_by_thread_by_event_name ev
  ON th.THREAD_ID = ev.THREAD_ID
WHERE
  ev.EVENT_NAME LIKE 'memory/%' and th.PROCESSLIST_USER IS NOT NULL AND th.PROCESSLIST_USER <> 'rdsrepladmin' AND th.PROCESSLIST_USER <> 'empoweradmin43'
ORDER BY
ev.CURRENT_NUMBER_OF_BYTES_USED DESC, th.PROCESSLIST_TIME DESC;
Solution

  • Rate Per Second = RPS or RPhr

    Suggestions to consider for AWS RDS Parameters Group,

    Static variables (requires stop/start of instance),

    innodb_buffer_pool_instances=2  # from 8 until you have more cores - if ever
innodb_open_files=40000  # from 4000 to reduce opened_tables RPhr of 18,127
table_open_cache_instances=2  # from 8 you only have 2 cores

    Dynamic variables, temptable_max_mmap=268435456 # from 1G since you only have 8G available temptable_max_ram=268435456 # from 1G since you only have 8G available table_open_cache=40000 # from 4000 to reduce match innodb_open_files

    Observation, threads_connected is showing 60 in show global status results. This is a symptom that Java code to connect, process, close is not closing the connection and leaving resources in use when the processing is done. Suggest search for "java mysql connect process close tutorial" and review more than one article to release resources.

    There are many more opportunities to adjust Global Variables for performance improvement.