-- updated the question because it was easy to initially assume I was asking for mysql tuning tips, although some of the tips did help me narrow down the problem --

We recently upgraded from mysql 5.6 to mysql 8.0 on a few servers, one of the servers was fine, and has had no problems, but it has significantly less load than one of our other servers which has been running out of memory.

Our server launches, then grabs 300 connections, and keeps them open with a C3P0 pool to the mysql server.

Over 4 days, this particular server accumulated 3500+ opened prepared statements, some of them taking more than 300 Mb in memory/sql/Prepared_statement::main_mem_root It's normal for our server profile to have the connections / prepared statements open, and hasn't been a problem on mysql 5.6 or 5.7

looking that up at mysql https://dev.mysql.com/doc/dev/mysql-server/latest/classPrepared__statement.html#a999eb56b28a5202c0fb1f5df96db3c74

I can see it's somehow related to prepared statements, but 'allocate parsed tree elements (instances of Item, SELECT_LEX and other classes).' doesn't tell me much, is it caching results? is that how it's growing ?

we're using connector/j 8.0.18 I've looked at the release notes for 8.0.18 -> 8.0.23 and there isn't any obvious memory leak fixes

our connection parameters include

cachePrepStmts", "true");
useServerPrepStmts", "true");

We were running these servers on AWS on MySQL 5.6 with the same overridden parameters on 8GB of RAM, when we upgraded to MySQL 8.0.21 we started running out of RAM in about 1 day. We grew the server to 32Gb but didn't change the parameters. It's gone over 15 GB used and still going up.

We're pretty sure it's related to the per connection thread memory, but not sure why.

Looking at memory_by_thread_by_current_bytes we have connections with over 200Mb

The server is running 8.0.21 on AWS RDS m4.large

I've audited the code and all resultsets are being closed. the prepared statements are in code as well, but the cache behavior above is keeping them open.

normally the innodb buffer pool is 4GB, but we dropped it to 3GB when we were on the 8GB machine for a bit more room.

show global status / memory_summary_by_thread_by_event_name / MySQL tuner run / show engine innodb status / show variables / sys.memory_by_thread_by_current_bytes / sys.memory_global_by_current_bytes https://gist.github.com/DaveB93/138f6bac254fee5bbbbb7ce2af7c2fef

-- update --

we 'fixed' this by connecting to our application via JMX and changing the C3P0 connection pool settings to "maxIdleTimeExcessConnections" to 300 (up from 0)

This cleaned up all of the long lived connections, and freed up 10GB of ram, This doesn't seem like the long term solution I want though.

-- updated the question because it was easy to initially assume I was asking for mysql tuning tips, although some of the tips did help me narrow down the problem --

-- further update. After doing only a partial upgrade on some servers, we found the problem is occurring on MySQL 5.7 as well, so guessing it was some sort of change in behaviour between 5.6 and 5.7. It's still a bit weird to us how it didn't cause a problem in MySQL 5.6 --

We recently upgraded from MySQL 5.6 to MySQL 8.0 on a few servers, one of the servers was fine, and has had no problems, but it has significantly less load than one of our other servers which has been running out of memory.

Our server launches, then grabs 300 connections, and keeps them open with a C3P0 pool to the mysql server.

Over 4 days, this particular server accumulated 3500+ opened prepared statements, some of them taking more than 300 Mb in memory/sql/Prepared_statement::main_mem_root It's normal for our server profile to have the connections / prepared statements open, and hasn't been a problem on mysql 5.6 or 5.7

looking that up at mysql https://dev.mysql.com/doc/dev/mysql-server/latest/classPrepared__statement.html#a999eb56b28a5202c0fb1f5df96db3c74

I can see it's somehow related to prepared statements, but 'allocate parsed tree elements (instances of Item, SELECT_LEX and other classes).' doesn't tell me much, is it caching results? is that how it's growing ?

we're using connector/j 8.0.18 I've looked at the release notes for 8.0.18 -> 8.0.23 and there isn't any obvious memory leak fixes

our connection parameters include

cachePrepStmts", "true");
useServerPrepStmts", "true");

We were running these servers on AWS on MySQL 5.6 with the same overridden parameters on 8GB of RAM, when we upgraded to MySQL 8.0.21 we started running out of RAM in about 1 day. We grew the server to 32Gb but didn't change the parameters. It's gone over 15 GB used and still going up.

We're pretty sure it's related to the per connection thread memory, but not sure why.

Looking at memory_by_thread_by_current_bytes we have connections with over 200Mb

The server is running 8.0.21 on AWS RDS m4.large

I've audited the code and all resultsets are being closed. the prepared statements are in code as well, but the cache behavior above is keeping them open.

normally the innodb buffer pool is 4GB, but we dropped it to 3GB when we were on the 8GB machine for a bit more room.

show global status / memory_summary_by_thread_by_event_name / MySQL tuner run / show engine innodb status / show variables / sys.memory_by_thread_by_current_bytes / sys.memory_global_by_current_bytes https://gist.github.com/DaveB93/138f6bac254fee5bbbbb7ce2af7c2fef

-- update --

we 'fixed' this by connecting to our application via JMX and changing the C3P0 connection pool settings to "maxIdleTimeExcessConnections" to 300 (up from 0)

This cleaned up all of the long lived connections, and freed up 10GB of ram, This doesn't seem like the long term solution I want though.

-- updated the question because it was easy to initially assume I was asking for mysql tuning tips, although some of the tips did help me narrow down the problem --

We recently upgraded from mysql 5.6 to mysql 8.0 on a few servers, one of the servers was fine, and has had no problems, but it has significantly less load than one of our other servers which has been running out of memory.

Our server launches, then grabs 300 connections, and keeps them open with a C3P0 pool to the mysql server.

Over 4 days, this particular server accumulated 3500+ opened prepared statements, some of them taking more than 300 Mb in memory/sql/Prepared_statement::main_mem_root It's normal for our server profile to have the connections / prepared statements open, and hasn't been a problem on mysql 5.6 or 5.7

looking that up at mysql https://dev.mysql.com/doc/dev/mysql-server/latest/classPrepared__statement.html#a999eb56b28a5202c0fb1f5df96db3c74

I can see it's somehow related to prepared statements, but 'allocate parsed tree elements (instances of Item, SELECT_LEX and other classes).' doesn't tell me much, is it caching results? is that how it's growing ?

we're using connector/j 8.0.18 I've looked at the release notes for 8.0.18 -> 8.0.23 and there isn't any obvious memory leak fixes

our connection parameters include

cachePrepStmts", "true");
useServerPrepStmts", "true");

We were running these servers on AWS on MySQL 5.6 with the same overridden parameters on 8GB of RAM, when we upgraded to MySQL 8.0.21 we started running out of RAM in about 1 day. We grew the server to 32Gb but didn't change the parameters. It's gone over 15 GB used and still going up.

We're pretty sure it's related to the per connection thread memory, but not sure why.

Looking at memory_by_thread_by_current_bytes we have connections with over 200Mb

The server is running 8.0.21 on AWS RDS

normally the innodb buffer pool is 4GB, but we dropped it to 3GB when we were on the 8GB machine for a bit more room.

show global status / memory_summary_by_thread_by_event_name / MySQL tuner run / show engine innodb status / show variables / sys.memory_by_thread_by_current_bytes / sys.memory_global_by_current_bytes https://gist.github.com/DaveB93/138f6bac254fee5bbbbb7ce2af7c2fef

-- update -- we 'fixed' this by connecting to our application via JMX and changing the C3P0 connection pool settings to "maxIdleTimeExcessConnections" to 300 (up from 0)

This cleaned up all of the long lived connections, and freed up 10GB of ram, This doesn't seem like the long term solution I want though.

-- updated the question because it was easy to initially assume I was asking for mysql tuning tips, although some of the tips did help me narrow down the problem --

We recently upgraded from mysql 5.6 to mysql 8.0 on a few servers, one of the servers was fine, and has had no problems, but it has significantly less load than one of our other servers which has been running out of memory.

Our server launches, then grabs 300 connections, and keeps them open with a C3P0 pool to the mysql server.

Over 4 days, this particular server accumulated 3500+ opened prepared statements, some of them taking more than 300 Mb in memory/sql/Prepared_statement::main_mem_root It's normal for our server profile to have the connections / prepared statements open, and hasn't been a problem on mysql 5.6 or 5.7

looking that up at mysql https://dev.mysql.com/doc/dev/mysql-server/latest/classPrepared__statement.html#a999eb56b28a5202c0fb1f5df96db3c74

I can see it's somehow related to prepared statements, but 'allocate parsed tree elements (instances of Item, SELECT_LEX and other classes).' doesn't tell me much, is it caching results? is that how it's growing ?

we're using connector/j 8.0.18 I've looked at the release notes for 8.0.18 -> 8.0.23 and there isn't any obvious memory leak fixes

our connection parameters include

cachePrepStmts", "true");
useServerPrepStmts", "true");

We were running these servers on AWS on MySQL 5.6 with the same overridden parameters on 8GB of RAM, when we upgraded to MySQL 8.0.21 we started running out of RAM in about 1 day. We grew the server to 32Gb but didn't change the parameters. It's gone over 15 GB used and still going up.

We're pretty sure it's related to the per connection thread memory, but not sure why.

Looking at memory_by_thread_by_current_bytes we have connections with over 200Mb

The server is running 8.0.21 on AWS RDS m4.large

I've audited the code and all resultsets are being closed. the prepared statements are in code as well, but the cache behavior above is keeping them open.

normally the innodb buffer pool is 4GB, but we dropped it to 3GB when we were on the 8GB machine for a bit more room.

show global status / memory_summary_by_thread_by_event_name / MySQL tuner run / show engine innodb status / show variables / sys.memory_by_thread_by_current_bytes / sys.memory_global_by_current_bytes https://gist.github.com/DaveB93/138f6bac254fee5bbbbb7ce2af7c2fef

-- update --

we 'fixed' this by connecting to our application via JMX and changing the C3P0 connection pool settings to "maxIdleTimeExcessConnections" to 300 (up from 0)

This cleaned up all of the long lived connections, and freed up 10GB of ram, This doesn't seem like the long term solution I want though.

We were running these servers on AWS on MySQL 5.6 with the same overridden parameters on 8GB of RAM, when we upgraded to MySQL 8.0.21 we started running out of RAM in about 1 day. We grew the server to 32Gb but didn't change the parameters. It's gone over 15 GB used and still going up.

We're pretty sure it's related to the per connection thread memory, but not sure why. From looking at MySQL tuner it looks like the variables that control per thread memory are:

  read_buffer_size      1048576
  read_rnd_buffer_size  4194304
  sort_buffer_size      1048576
  thread_stack          262144
  max_allowed_packet    4194304
  join_buffer_size      262144

this adds up to 10.5 Mb. Am I missing a variable here?

Is there another query I should be running to figure out what is taking all this ram on the threads?

select * from sys.memory_by_thread_by_current_bytes;

thread_id   `user`  current_count_used  current_allocated   current_avg_alloc   current_max_alloc   total_allocated
315 [email protected]  1880    209.77 MiB  114.26 KiB  204.10 MiB  21.51 GiB
336 [email protected]  2288    207.75 MiB  92.98 KiB   204.10 MiB  23.38 GiB
358 [email protected]  1847    207.35 MiB  114.96 KiB  204.01 MiB  19.66 GiB
272 [email protected]  1347    205.59 MiB  156.29 KiB  204.77 MiB  24.91 GiB
328 [email protected]  1825    204.55 MiB  114.77 KiB  204.14 MiB  23.84 GiB
276 [email protected]  1486    204.08 MiB  140.63 KiB  201.70 MiB  21.31 GiB
313 [email protected]  975 203.05 MiB  213.26 KiB  203.56 MiB  19.70 GiB
258 [email protected]  1244    202.76 MiB  166.91 KiB  201.56 MiB  19.58 GiB
288 [email protected]  1431    201.49 MiB  144.19 KiB  201.48 MiB  19.39 GiB
248 [email protected]  1265    175.83 MiB  142.33 KiB  175.15 MiB  17.04 GiB
284 [email protected]  978 174.08 MiB  182.27 KiB  175.34 MiB  18.25 GiB
343 [email protected]  2038    143.98 MiB  72.34 KiB   139.14 MiB  16.84 GiB
329 [email protected]  1971    142.77 MiB  74.17 KiB   138.54 MiB  17.93 GiB
355 [email protected]  1646    141.74 MiB  88.18 KiB   136.37 MiB  15.04 GiB
327 [email protected]  1805    139.95 MiB  79.39 KiB   136.15 MiB  15.95 GiB
330 [email protected]  2001    139.72 MiB  71.50 KiB   136.08 MiB  14.69 GiB
351 [email protected]  2129    139.45 MiB  67.07 KiB   136.57 MiB  16.34 GiB
316 [email protected]  1598    139.01 MiB  89.08 KiB   136.46 MiB  17.56 GiB
293 [email protected]  1238    138.29 MiB  114.39 KiB  136.24 MiB  15.53 GiB
345 [email protected]  1196    137.95 MiB  118.11 KiB  137.40 MiB  15.27 GiB
347 [email protected]  1650    137.85 MiB  85.55 KiB   136.63 MiB  14.20 GiB
360 [email protected]  1163    137.65 MiB  121.20 KiB  136.24 MiB  13.23 GiB
323 [email protected]  1267    137.60 MiB  111.21 KiB  136.91 MiB  14.59 GiB
270 [email protected]  1198    136.89 MiB  117.01 KiB  134.72 MiB  13.55 GiB
278 [email protected]  1003    136.76 MiB  139.62 KiB  136.33 MiB  16.33 GiB
259 [email protected]  991 136.74 MiB  141.29 KiB  136.31 MiB  16.77 GiB
266 [email protected]  984 136.69 MiB  142.25 KiB  136.33 MiB  15.35 GiB
348 [email protected]  1031    136.69 MiB  135.76 KiB  136.15 MiB  14.13 GiB
346 [email protected]  1588    136.21 MiB  87.83 KiB   136.69 MiB  17.14 GiB
314 [email protected]  1012    136.05 MiB  137.66 KiB  136.37 MiB  15.07 GiB
279 [email protected]  904 135.81 MiB  153.83 KiB  134.27 MiB  12.29 GiB
312 [email protected]  992 135.76 MiB  140.14 KiB  136.15 MiB  15.62 GiB
290 [email protected]  970 135.70 MiB  143.25 KiB  134.70 MiB  13.54 GiB
305 [email protected]  1123    135.44 MiB  123.50 KiB  136.21 MiB  14.96 GiB
311 [email protected]  1108    134.94 MiB  124.71 KiB  136.33 MiB  16.61 GiB
302 [email protected]  1006    134.80 MiB  137.21 KiB  134.81 MiB  12.75 GiB
243 [email protected]  848 132.64 MiB  160.16 KiB  134.30 MiB  12.67 GiB
334 [email protected]  1779    121.58 MiB  69.98 KiB   118.86 MiB  12.28 GiB

-- trimmed, but current allocated adds up to 10GB which matches memory/sql/Prepared_statement::main_mem_root --

select * from sys.memory_global_by_current_bytes mgbcb ;
event_name  current_count   current_alloc   current_avg_alloc   high_count  high_alloc  high_avg_alloc
memory/sql/Prepared_statement::main_mem_root    13648   10.53 GiB   808.94 KiB  13654   10.53 GiB   808.59 KiB
memory/innodb/buf_buf_pool  24  3.07 GiB    131.00 MiB  24  3.07 GiB    131.00 MiB
memory/mysys/KEY_CACHE  3   256.00 MiB  85.33 MiB   3   256.00 MiB  85.33 MiB
memory/sql/TABLE    24753   136.76 MiB  5.66 KiB    24882   141.67 MiB  5.83 KiB
memory/innodb/hash0hash 32  83.70 MiB   2.62 MiB    32  83.70 MiB   2.62 MiB
memory/innodb/memory    26670   64.23 MiB   2.47 KiB    36774   129.14 MiB  3.60 KiB
memory/performance_schema/table_handles 5   45.31 MiB   9.06 MiB    5   45.31 MiB   9.06 MiB
memory/performance_schema/events_statements_summary_by_digest   1   39.67 MiB   39.67 MiB   1   39.67 MiB   39.67 MiB
memory/innodb/row0sel   1197    33.61 MiB   28.75 KiB   2744    140.74 MiB  52.52 KiB
memory/innodb/buf0dblwr 840 30.15 MiB   36.76 KiB   840 36.54 MiB   44.55 KiB
memory/innodb/ut0link_buf   2   24.00 MiB   12.00 MiB   2   24.00 MiB   12.00 MiB
memory/innodb/lock0lock 9799    21.24 MiB   2.22 KiB    9799    21.24 MiB   2.22 KiB
memory/performance_schema/events_statements_summary_by_thread_by_event_name 2   19.59 MiB   9.79 MiB    2   19.59 MiB   9.79 MiB
memory/performance_schema/memory_summary_by_thread_by_event_name    2   15.82 MiB   7.91 MiB    2   15.82 MiB   7.91 MiB
memory/sql/NET::buff    299 14.04 MiB   48.10 KiB   419 16.57 MiB   40.49 KiB
memory/mysqld_openssl/openssl_malloc    18492   13.92 MiB    789 bytes  56162   26.89 MiB    502 bytes
memory/performance_schema/events_statements_history_long    1   13.89 MiB   13.89 MiB   1   13.89 MiB   13.89 MiB
memory/sql/TABLE_SHARE::mem_root    3532    13.59 MiB   3.94 KiB    3532    13.59 MiB   3.94 KiB
memory/mysys/IO_CACHE   1011    10.53 MiB   10.66 KiB   1013    10.54 MiB   10.66 KiB
memory/performance_schema/events_statements_summary_by_digest.digest_text   1   9.77 MiB    9.77 MiB    1   9.77 MiB    9.77 MiB
memory/performance_schema/events_statements_history_long.digest_text    1   9.77 MiB    9.77 MiB    1   9.77 MiB    9.77 MiB
memory/performance_schema/events_statements_history_long.sql_text   1   9.77 MiB    9.77 MiB    1   9.77 MiB    9.77 MiB
memory/performance_schema/events_waits_summary_by_thread_by_event_name  2   8.66 MiB    4.33 MiB    2   8.66 MiB    4.33 MiB
memory/innodb/ut0new    6   8.08 MiB    1.35 MiB    6   8.08 MiB    1.35 MiB
memory/innodb/ha_innodb 4390    7.27 MiB    1.70 KiB    5749    13.49 MiB   2.40 KiB
.. trimmed ..
memory/sql/errmsgs  1   331.87 KiB  331.87 KiB  1   331.87 KiB  331.87 KiB
memory/vio/vio  298 311.97 KiB  1.05 KiB    306 320.34 KiB  1.05 KiB
memory/innodb/ut0rbt    3067    307.37 KiB   102 bytes  12010   1.08 MiB      94 bytes
memory/innodb/mem0mem   4262    295.59 KiB    71 bytes  4415    302.14 KiB    70 bytes
memory/innodb/read0read 1025    288.28 KiB   288 bytes  1025    288.28 KiB   288 bytes
memory/performance_schema/file_handle   1   256.00 KiB  256.00 KiB  1   256.00 KiB  256.00 KiB
memory/performance_schema/session_connect_attrs 2   256.00 KiB  128.00 KiB  2   256.00 KiB  128.00 KiB
memory/mysys/lf_dynarray    62  250.92 KiB  4.05 KiB    62  250.92 KiB  4.05 KiB
memory/mysys/lf_slist   6992    218.50 KiB    32 bytes  6992    218.50 KiB    32 bytes
memory/sql/plugin_mem_root  66  201.02 KiB  3.05 KiB    66  201.02 KiB  3.05 KiB
memory/innodb/trx0i_s   2   200.19 KiB  100.09 KiB  2   200.19 KiB  100.09 KiB
memory/sql/THD::Session_tracker 3978    198.72 KiB    51 bytes  4083    204.00 KiB    51 bytes
memory/innodb/trx0undo  509 178.95 KiB   360 bytes  46613   16.00 MiB    360 bytes
memory/sql/Prepared_statement_map   3742    160.14 KiB    43 bytes  3745    175.16 KiB    47 bytes
trimmed.

MySQL tuner run (on the 32Gb database, but told it to assume 8Gb ram)
 >>  MySQLTuner 1.7.20 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/

[--] Assuming 8000 MB of physical memory
[--] Assuming 100 MB of swap space
[OK] Currently running supported MySQL version 8.0.21

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 77.7G (Tables: 1646)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 19h 22m 24s (27M q [113.270 qps], 1K conn, TX: 28G, RX: 7G)
[--] Reads / Writes: 73% / 27%
[--] Binary logging is enabled (GTID MODE: OFF_PERMISSIVE)
[--] Physical Memory     : 7.8G
[--] Max MySQL memory    : 6.5G
[--] Other process memory: 0B
[--] Total buffers: 3.3G global + 10.5M per thread (320 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 6.4G (81.89% of installed RAM)
[OK] Maximum possible memory usage: 6.5G (83.73% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/27M)
[!!] Highest connection usage: 95%  (306/320)
[OK] Aborted connections: 0.00%  (0/1176)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 687K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 85% (249K on disk / 290K total)
[OK] Thread cache hit rate: 73% (306 created / 1K connections)
[!!] Table cache hit rate: 4% (4K open / 98K opened)
[OK] table_definition_cache(2000) is upper than number of tables(1977)
[OK] Open file limit used: 0% (107/65K)
[OK] Table locks acquired immediately: 100% (18K immediate / 18K locks)
[OK] Binlog cache memory access: 99.99% (7214683 Memory / 7215088 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 32
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 3.0G/77.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (16.6666666666667 %): 256.0M * 2/3.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 4
[--] Number of InnoDB Buffer Pool Chunk : 24 for 4 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.87% (495469607 hits/ 496112175 total)
[!!] InnoDB Write Log efficiency: 72.33% (61616573 hits/ 85189070 total)
[OK] InnoDB log waits: 0.00% (0 waits / 23572497 writes)

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Reduce or eliminate persistent connections to reduce connection usage
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: 
    Beware that open_files_limit (65535) variable
    should be greater than table_open_cache (4096)
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: 
Variables to adjust:
    max_connections (> 320)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    tmp_table_size (> 2M)
    max_heap_table_size (> 128M)
    table_open_cache (> 4096)
    innodb_buffer_pool_size (>= 77.7G) if possible.
    innodb_log_file_size should be (=384M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances(=3)

show variables;

activate_all_roles_on_login OFF
autocommit  ON
back_log    320
big_tables  OFF
bind_address    *
binlog_cache_size   32768
binlog_checksum NONE
binlog_direct_non_transactional_updates OFF
binlog_encryption   OFF
binlog_error_action ABORT_SERVER
binlog_expire_logs_seconds  2592000
binlog_format   ROW
binlog_group_commit_sync_delay  0
binlog_group_commit_sync_no_delay_count 0
binlog_gtid_simple_recovery ON
binlog_max_flush_queue_time 0
binlog_order_commits    ON
binlog_rotate_encryption_master_key_at_startup  OFF
binlog_row_event_max_size   8192
binlog_row_image    FULL
binlog_row_metadata MINIMAL
binlog_row_value_options    
binlog_rows_query_log_events    OFF
binlog_stmt_cache_size  32768
binlog_transaction_compression  OFF
binlog_transaction_compression_level_zstd   3
binlog_transaction_dependency_history_size  25000
binlog_transaction_dependency_tracking  COMMIT_ORDER
bulk_insert_buffer_size 8388608
character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  utf8mb4
character_set_filesystem    binary
character_set_results   
character_set_server    utf8mb4
character_set_system    utf8
check_proxy_users   OFF
collation_connection    utf8mb4_0900_ai_ci
collation_database  utf8mb4_unicode_ci
collation_server    utf8mb4_unicode_ci
completion_type NO_CHAIN
concurrent_insert   AUTO
connect_timeout 10
core_file   OFF
create_admin_listener_thread    OFF
cte_max_recursion_depth 1000
default_collation_for_utf8mb4   utf8mb4_0900_ai_ci
default_storage_engine  InnoDB
default_table_encryption    OFF
default_tmp_storage_engine  InnoDB
default_week_format 0
delay_key_write ON
delayed_insert_limit    100
delayed_insert_timeout  300
delayed_queue_size  1000
disabled_storage_engines    
disconnect_on_expired_password  ON
div_precision_increment 4
end_markers_in_json OFF
enforce_gtid_consistency    OFF
eq_range_index_dive_limit   200
error_count 0
event_scheduler ON
expire_logs_days    0
explicit_defaults_for_timestamp ON
flush   OFF
flush_time  0
foreign_key_checks  ON
ft_boolean_syntax   + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit    20
ft_stopword_file    (built-in)
generated_random_password_length    20
group_concat_max_len    1024
group_replication_consistency   EVENTUAL
gtid_executed   
gtid_executed_compression_period    1000
gtid_mode   OFF_PERMISSIVE
gtid_next   AUTOMATIC
gtid_owned  
gtid_purged 
have_compress   YES
have_dynamic_loading    YES
have_geometry   YES
have_openssl    YES
have_profiling  YES
have_query_cache    NO
have_rtree_keys YES
have_ssl    YES
have_statement_timeout  YES
histogram_generation_max_mem_size   20000000
host_cache_size 448
identity    0
immediate_server_version    999999
information_schema_stats_expiry 86400
innodb_adaptive_flushing    ON
innodb_adaptive_flushing_lwm    10
innodb_adaptive_hash_index  ON
innodb_adaptive_hash_index_parts    8
innodb_adaptive_max_sleep_delay 150000
innodb_api_bk_commit_interval   5
innodb_api_disable_rowlock  OFF
innodb_api_enable_binlog    OFF
innodb_api_enable_mdl   OFF
innodb_api_trx_level    0
innodb_autoextend_increment 64
innodb_autoinc_lock_mode    2
innodb_buffer_pool_chunk_size   134217728
innodb_buffer_pool_dump_at_shutdown ON
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 25
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_in_core_file ON
innodb_buffer_pool_instances    4
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  ON
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 3221225472
innodb_change_buffer_max_size   25
innodb_change_buffering all
innodb_checksum_algorithm   crc32
innodb_cmp_per_index_enabled    OFF
innodb_commit_concurrency   0
innodb_compression_failure_threshold_pct    5
innodb_compression_level    6
innodb_compression_pad_pct_max  50
innodb_concurrency_tickets  5000
innodb_data_file_path   ibdata1:12M:autoextend
innodb_deadlock_detect  ON
innodb_dedicated_server OFF
innodb_default_row_format   dynamic
innodb_disable_sort_file_cache  OFF
innodb_doublewrite  ON
innodb_doublewrite_batch_size   16
innodb_doublewrite_files    2
innodb_doublewrite_pages    32
innodb_fast_shutdown    1
innodb_file_per_table   ON
innodb_fill_factor  100
innodb_flush_log_at_timeout 1
innodb_flush_log_at_trx_commit  2
innodb_flush_method O_DIRECT
innodb_flush_neighbors  0
innodb_flush_sync   ON
innodb_flushing_avg_loops   30
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_fsync_threshold  0
innodb_ft_aux_table 
innodb_ft_cache_size    8000000
innodb_ft_enable_diag_print OFF
innodb_ft_enable_stopword   ON
innodb_ft_max_token_size    84
innodb_ft_min_token_size    3
innodb_ft_num_word_optimize 2000
innodb_ft_result_cache_limit    2000000000
innodb_ft_server_stopword_table 
innodb_ft_sort_pll_degree   2
innodb_ft_total_cache_size  640000000
innodb_ft_user_stopword_table   
innodb_idle_flush_pct   100
innodb_io_capacity  200
innodb_io_capacity_max  2000
innodb_lock_wait_timeout    5
innodb_log_buffer_size  8388608
innodb_log_checksums    ON
innodb_log_compressed_pages ON
innodb_log_file_size    268435456
innodb_log_files_in_group   2
innodb_log_spin_cpu_abs_lwm 80
innodb_log_spin_cpu_pct_hwm 50
innodb_log_wait_for_flush_spin_hwm  400
innodb_log_write_ahead_size 8192
innodb_lru_scan_depth   1024
innodb_max_dirty_pages_pct  90.000000
innodb_max_dirty_pages_pct_lwm  10.000000
innodb_max_purge_lag    0
innodb_max_purge_lag_delay  0
innodb_max_undo_log_size    1073741824
innodb_monitor_disable  
innodb_monitor_enable   
innodb_monitor_reset    
innodb_monitor_reset_all    
innodb_numa_interleave  OFF
innodb_old_blocks_pct   37
innodb_old_blocks_time  1000
innodb_online_alter_log_max_size    134217728
innodb_open_files   4096
innodb_optimize_fulltext_only   OFF
innodb_page_cleaners    4
innodb_page_size    16384
innodb_parallel_read_threads    4
innodb_print_all_deadlocks  OFF
innodb_print_ddl_logs   OFF
innodb_purge_batch_size 300
innodb_purge_rseg_truncate_frequency    128
innodb_purge_threads    1
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads  16
innodb_read_only    OFF
innodb_redo_log_archive_dirs    
innodb_redo_log_encrypt OFF
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_sort_buffer_size 1048576
innodb_spin_wait_delay  6
innodb_spin_wait_pause_multiplier   50
innodb_stats_auto_recalc    ON
innodb_stats_include_delete_marked  OFF
innodb_stats_method nulls_equal
innodb_stats_on_metadata    OFF
innodb_stats_persistent ON
innodb_stats_persistent_sample_pages    20
innodb_stats_transient_sample_pages 8
innodb_status_output    OFF
innodb_status_output_locks  OFF
innodb_strict_mode  ON
innodb_sync_array_size  1
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_temp_data_file_path  ibtmp1:12M:autoextend
innodb_thread_concurrency   32
innodb_thread_sleep_delay   0
innodb_undo_log_encrypt OFF
innodb_undo_log_truncate    ON
innodb_undo_tablespaces 2
innodb_use_native_aio   ON
innodb_validate_tablespace_paths    ON
innodb_version  8.0.21
innodb_write_io_threads 16
insert_id   0
interactive_timeout 28800
internal_tmp_mem_storage_engine TempTable
join_buffer_size    262144
keep_files_on_create    OFF
key_buffer_size 268435456
key_cache_age_threshold 300
key_cache_block_size    1024
key_cache_division_limit    100
keyring_operations  ON
large_files_support ON
large_page_size 0
large_pages OFF
last_insert_id  0
lc_messages en_US
lc_time_names   en_US
license GPL
local_infile    ON
lock_wait_timeout   31536000
locked_in_memory    OFF
log_bin ON
log_bin_basename    /rdsdbdata/log/binlog/mysql-bin-changelog
log_bin_index   /rdsdbdata/log/binlog/mysql-bin-changelog.index
log_bin_trust_function_creators OFF
log_bin_use_v1_row_events   OFF
log_error   /rdsdbdata/log/error/mysql-error.log
log_error_services  log_filter_internal; log_sink_internal
log_error_suppression_list  
log_error_verbosity 2
log_output  TABLE
log_queries_not_using_indexes   OFF
log_raw OFF
log_slave_updates   ON
log_slow_admin_statements   OFF
log_slow_extra  OFF
log_slow_slave_statements   OFF
log_statements_unsafe_for_binlog    OFF
log_throttle_queries_not_using_indexes  0
log_timestamps  UTC
long_query_time 5.000000
low_priority_updates    OFF
lower_case_file_system  OFF
lower_case_table_names  0
mandatory_roles 
master_info_repository  TABLE
master_verify_checksum  OFF
max_allowed_packet  4194304
max_binlog_cache_size   18446744073709547520
max_binlog_size 134217728
max_binlog_stmt_cache_size  18446744073709547520
max_connect_errors  100
max_connections 320
max_delayed_threads 20
max_digest_length   1024
max_error_count 1024
max_execution_time  0
max_heap_table_size 134217728
max_insert_delayed_threads  20
max_join_size   18446744073709551615
max_length_for_sort_data    4096
max_points_in_geometry  65536
max_prepared_stmt_count 16382
max_relay_log_size  0
max_seeks_for_key   18446744073709551615
max_sort_length 1024
max_sp_recursion_depth  0
max_user_connections    0
max_write_lock_count    18446744073709551615
min_examined_row_limit  0
myisam_data_pointer_size    6
myisam_max_sort_file_size   9223372036853727232
myisam_mmap_size    18446744073709551615
myisam_recover_options  OFF
myisam_repair_threads   1
myisam_sort_buffer_size 8388608
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
mysql_native_password_proxy_users   OFF
net_buffer_length   16384
net_read_timeout    30
net_retry_count 10
net_write_timeout   60
new OFF
ngram_token_size    2
offline_mode    OFF
old OFF
old_alter_table OFF
open_files_limit    65535
optimizer_prune_level   1
optimizer_search_depth  62
optimizer_switch    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on
optimizer_trace enabled=off,one_line=off
optimizer_trace_features    greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
optimizer_trace_limit   1
optimizer_trace_max_mem_size    1048576
optimizer_trace_offset  -1
original_commit_timestamp   36028797018963968
original_server_version 999999
parser_max_mem_size 18446744073709551615
partial_revokes OFF
password_history    0
password_require_current    OFF
password_reuse_interval 0
performance_schema  ON
performance_schema_accounts_size    -1
performance_schema_digests_size 10000
performance_schema_error_size   0
performance_schema_events_stages_history_long_size  10000
performance_schema_events_stages_history_size   10
performance_schema_events_statements_history_long_size  10000
performance_schema_events_statements_history_size   10
performance_schema_events_transactions_history_long_size    10000
performance_schema_events_transactions_history_size 10
performance_schema_events_waits_history_long_size   10000
performance_schema_events_waits_history_size    10
performance_schema_hosts_size   -1
performance_schema_max_cond_classes 100
performance_schema_max_cond_instances   -1
performance_schema_max_digest_length    1024
performance_schema_max_digest_sample_age    60
performance_schema_max_file_classes 80
performance_schema_max_file_handles 32768
performance_schema_max_file_instances   -1
performance_schema_max_index_stat   -1
performance_schema_max_memory_classes   450
performance_schema_max_metadata_locks   -1
performance_schema_max_mutex_classes    300
performance_schema_max_mutex_instances  -1
performance_schema_max_prepared_statements_instances    -1
performance_schema_max_program_instances    -1
performance_schema_max_rwlock_classes   60
performance_schema_max_rwlock_instances -1
performance_schema_max_socket_classes   10
performance_schema_max_socket_instances -1
performance_schema_max_sql_text_length  1024
performance_schema_max_stage_classes    175
performance_schema_max_statement_classes    218
performance_schema_max_statement_stack  10
performance_schema_max_table_handles    -1
performance_schema_max_table_instances  -1
performance_schema_max_table_lock_stat  -1
performance_schema_max_thread_classes   100
performance_schema_max_thread_instances -1
performance_schema_session_connect_attrs_size   512
performance_schema_setup_actors_size    -1
performance_schema_setup_objects_size   -1
performance_schema_users_size   -1
persist_only_admin_x509_subject 
persisted_globals_load  OFF
port    3306
preload_buffer_size 32768
print_identified_with_as_hex    OFF
profiling   OFF
profiling_history_size  15
protocol_compression_algorithms zlib,zstd,uncompressed
protocol_version    10
proxy_user  
pseudo_slave_mode   OFF
pseudo_thread_id    1164
query_alloc_block_size  8192
query_prealloc_size 8192
rand_seed1  0
rand_seed2  0
range_alloc_block_size  4096
range_optimizer_max_mem_size    8388608
rbr_exec_mode   STRICT
read_buffer_size    1048576
read_only   OFF
read_rnd_buffer_size    4194304
regexp_stack_limit  8000000
regexp_time_limit   32
relay_log   /rdsdbdata/log/relaylog/relaylog
relay_log_basename  /rdsdbdata/log/relaylog/relaylog
relay_log_index /rdsdbdata/log/relaylog/relaylog.index
relay_log_info_file relay-log.info
relay_log_info_repository   TABLE
relay_log_purge ON
relay_log_recovery  ON
relay_log_space_limit   0
report_host 
report_password 
report_port 3306
report_user 
require_row_format  OFF
require_secure_transport    OFF
resultset_metadata  FULL
rpl_read_size   8192
rpl_stop_slave_timeout  31536000
schema_definition_cache 256
secondary_engine_cost_threshold 100000.000000
secure_file_priv    /tmp/
server_id   658899241
server_id_bits  32
server_uuid b2a6c6b9-d398-11e6-b6a4-12306f56ae16
session_track_gtids OFF
session_track_schema    ON
session_track_state_change  OFF
session_track_system_variables  time_zone,autocommit,character_set_client,character_set_results,character_set_connection
session_track_transaction_info  OFF
sha256_password_auto_generate_rsa_keys  ON
sha256_password_private_key_path    private_key.pem
sha256_password_proxy_users OFF
sha256_password_public_key_path public_key.pem
show_create_table_skip_secondary_engine OFF
show_create_table_verbosity OFF
show_old_temporals  OFF
skip_external_locking   ON
skip_name_resolve   OFF
skip_networking OFF
skip_show_database  OFF
slave_allow_batching    OFF
slave_checkpoint_group  512
slave_checkpoint_period 300
slave_compressed_protocol   OFF
slave_exec_mode IDEMPOTENT
slave_max_allowed_packet    1073741824
slave_net_timeout   60
slave_parallel_type DATABASE
slave_parallel_workers  0
slave_pending_jobs_size_max 134217728
slave_preserve_commit_order OFF
slave_rows_search_algorithms    INDEX_SCAN,HASH_SCAN
slave_skip_errors   OFF
slave_sql_verify_checksum   ON
slave_transaction_retries   10
slave_type_conversions  
slow_launch_time    2
slow_query_log  ON
slow_query_log_file /rdsdbdata/log/slowquery/mysql-slowquery.log
socket  /tmp/mysql.sock
sort_buffer_size    1048576
sql_auto_is_null    OFF
sql_big_selects ON
sql_buffer_result   OFF
sql_log_bin ON
sql_log_off OFF
sql_mode    STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
sql_notes   ON
sql_quote_show_create   ON
sql_require_primary_key OFF
sql_safe_updates    OFF
sql_select_limit    20000
sql_slave_skip_counter  0
sql_warnings    OFF
ssl_ca  /rdsdbdata/rds-metadata/ca-cert.pem
ssl_capath  
ssl_cert    /rdsdbdata/rds-metadata/server-cert.pem
ssl_cipher  ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA:AES128-SHA:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA:DHE-DSS-AES128-SHA:DHE-DSS-AES256-SHA
ssl_crl 
ssl_crlpath 
ssl_fips_mode   OFF
ssl_key /rdsdbdata/rds-metadata/server-key.pem
stored_program_cache    256
stored_program_definition_cache 256
super_read_only OFF
sync_binlog 1
sync_master_info    10000
sync_relay_log  10000
sync_relay_log_info 10000
system_time_zone    UTC
table_definition_cache  2000
table_encryption_privilege_check    OFF
table_open_cache    4096
table_open_cache_instances  16
tablespace_definition_cache 256
temptable_max_ram   1073741824
temptable_use_mmap  ON
thread_cache_size   11
thread_handling one-thread-per-connection
thread_stack    262144
time_zone   UTC
timestamp   1610991021.519301
tls_ciphersuites    
tls_version TLSv1,TLSv1.1,TLSv1.2,TLSv1.3
tmp_table_size  2097152
transaction_alloc_block_size    8192
transaction_allow_batching  OFF
transaction_isolation   REPEATABLE-READ
transaction_prealloc_size   4096
transaction_read_only   OFF
transaction_write_set_extraction    XXHASH64
unique_checks   ON
updatable_views_with_limit  YES
use_secondary_engine    ON
version 8.0.21
version_comment Source distribution
version_compile_machine aarch64
version_compile_os  Linux
version_compile_zlib    1.2.11
wait_timeout    28800
warning_count   0
windowing_use_high_precision    ON

-- updated the question because it was easy to initially assume I was asking for mysql tuning tips, although some of the tips did help me narrow down the problem --

Over 4 days, this particular server accumulated 3500+ opened prepared statements, some of them taking more than 300 Mb in memory/sql/Prepared_statement::main_mem_root It's normal for our server profile to have the connections / prepared statements open, and hasn't been a problem on mysql 5.6 or 5.7

looking that up at mysql https://dev.mysql.com/doc/dev/mysql-server/latest/classPrepared__statement.html#a999eb56b28a5202c0fb1f5df96db3c74

I can see it's somehow related to prepared statements, but 'allocate parsed tree elements (instances of Item, SELECT_LEX and other classes).' doesn't tell me much, is it caching results? is that how it's growing ?

we're using connector/j 8.0.18 I've looked at the release notes for 8.0.18 -> 8.0.23 and there isn't any obvious memory leak fixes

our connection parameters include

cachePrepStmts", "true");
useServerPrepStmts", "true");

We were running these servers on AWS on MySQL 5.6 with the same overridden parameters on 8GB of RAM, when we upgraded to MySQL 8.0.21 we started running out of RAM in about 1 day. We grew the server to 32Gb but didn't change the parameters. It's gone over 15 GB used and still going up.

We're pretty sure it's related to the per connection thread memory, but not sure why.

show global status / memory_summary_by_thread_by_event_name / MySQL tuner run / show engine innodb status / show variables / sys.memory_by_thread_by_current_bytes / sys.memory_global_by_current_bytes https://gist.github.com/DaveB93/138f6bac254fee5bbbbb7ce2af7c2fef

-- update -- we 'fixed' this by connecting to our application via JMX and changing the C3P0 connection pool settings to "maxIdleTimeExcessConnections" to 300 (up from 0)

This cleaned up all of the long lived connections, and freed up 10GB of ram, This doesn't seem like the long term solution I want though.