Mysql 8.0.21 long lived prepared statements have a memory leak ? or are we doing something weird

Question

-- 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.

Answer 1

The possible reason for RAM consumption can be here:
[!!] Temporary tables created on disk: 85% (249K on disk / 290K total)
When automatic temporary table is created it is always created with ENGINE=MEMORY first. If it doesn't fit the max_heap_table_size = 134217728 limit, it will be transferred on disk for further proceeding. Temptables are associated with session/connection so each thread can create a number of 128MB large temptables in the RAM - in addition to the buffers allocated for reads/joins/sorts.

I suggest to decrease max_heap_table_size to some reasonable value like 16M as far as in 85% cases temptables don't fit 128M anyway. Another suggestion is to optimize your queries having longest execution times (due to the slow on-disk temptables operations). Slow queries log is a good starting point.

Answer 2

MySQLTuner is pessimestic when it comes to "per-thread memory". If you are not swapping, then you are not in trouble.

Anyway, here is another attack on the settings:

Observations:

• Version: 8.0.21
• 8 GB of RAM
• Uptime = 3d 00:45:32
• You are not running on Windows.
• Running 64-bit version
• You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Decrease key_buffer_size to 10M for 8.0.

As already mentioned, look at some of the queries.

If the disk is SSD, then some of the io_capacity, etc, settings can be increased.

table_open_cache is bigger than necessary; decrease to 3000. (There is no metric to say what value is optimal.)

min(tmp_table_size, max_heap_table_size) is only 2M; this is not as bad as what Tuner complained about.

Details and other observations:

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) ) = ((256M - 1.2 * 0 * 1024)) / 8192M = 3.1% -- Percent of RAM wasted in key_buffer. -- Decrease key_buffer_size (now 268435456).

( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 256M = 0 -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size (now 268435456) to avoid unnecessary memory usage.

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((256M / 0.20 + 3072M / 0.70)) / 8192M = 69.2% -- Most of available ram should be made available for caching. -- http://mysql.rjweb.org/doc.php/memory

( table_open_cache ) = 4,096 -- Number of table descriptors to cache -- Several hundred is usually good.

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain -- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)

( Innodb_log_writes ) = 25,846,932 / 261932 = 99 /sec

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( Created_tmp_disk_tables ) = 269,432 / 261932 = 1 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size (now 2097152) and max_heap_table_size (now 134217728). Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM. Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Created_tmp_tables ) = 269,432 / 313876 = 85.8% -- Percent of temp tables that spilled to disk -- Maybe increase tmp_table_size (now 2097152) and max_heap_table_size (now 134217728); improve indexes; avoid blobs, etc.

( Select_scan ) = 636,670 / 261932 = 2.4 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 3135 - 0 ) / ( 3135 + 0 ) = 100.0% -- Are you closing your prepared statements? -- Add Closes.

( Com_stmt_close / Com_stmt_prepare ) = 0 / 3135 = 0 -- Prepared statements should be Closed. -- Check whether all Prepared statements are "Closed".

( Com_admin_commands / Queries ) = 1,968,489 / 33689117 = 5.8% -- Percent of queries that are "admin" commands. -- What's going on?

( long_query_time ) = 5 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show. -- It can be helpful in the slowlog to see writes that could be interfering with Replica reads.

Abnormally small:

Com_show_fields = 0
min(max_heap_table_size, tmp_table_size) = 2MB

Abnormally large:

Com_alter_user = 0.014 /HR
Com_flush = 12 /HR
Com_purge_before_date = 12 /HR
Com_show_create_func = 0.3 /HR
Com_show_open_tables = 0.014 /HR
Com_show_slave_hosts = 0.055 /HR
Com_stmt_reset = 5.6 /sec
Innodb_data_pending_fsyncs = 6,688
Innodb_system_rows_deleted = 0.069 /HR
Innodb_system_rows_inserted = 0.069 /HR
Innodb_system_rows_updated = 62 /HR
Prepared_stmt_count = 3,131
Ssl_accepts = 300
Ssl_finished_accepts = 300
Ssl_session_cache_overflows = 172
Ssl_used_session_cache_entries = 128
Threads_connected = 304
back_log / max_connections = 100.0%
innodb_thread_concurrency = 32
max_error_count = 1,024
max_length_for_sort_data = 4,096
optimizer_trace_offset = --1
performance_schema_max_cond_classes = 100
performance_schema_max_mutex_classes = 300
performance_schema_max_rwlock_classes = 60
performance_schema_max_stage_classes = 175
performance_schema_max_statement_classes = 218
performance_schema_max_thread_classes = 100

Abnormal strings:

event_scheduler = ON
gtid_mode = OFF_PERMISSIVE
innodb_fast_shutdown = 1
log_output = TABLE
log_statements_unsafe_for_binlog = OFF
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
relay_log_recovery = ON
slave_exec_mode = IDEMPOTENT
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
time_zone = UTC
version_compile_machine = aarch64

Answer 3

dow_15_telemetry -- This sounds like one of a set of identical tables. That is usually a poor schema design. Please elaborate on what is going on. We may want to discuss an improvement in that area.

If, say, there are 20 'identical' tables, then there may be

• 20 times as many 'different' prepared statements that could be consolidated into 1.
• 20 times as many "tables" to "open" -- leading to table_open_cache (an other settings) needing to be somewhat bigger than otherwise.

Other things...

• If LIMIT ?, ? is "pagination", then "remembering where you left off" may be faster.

• Do you have composite indexes to help with

    WHERE  `dow_14_perproduct`.`match`.state = ?
      AND  `dow_14_perproduct`.`match`.visible = ?
      AND  `dow_14_perproduct`.`match`.matchtype_id = ?
  
    WHERE  `type`=?
      AND  `typeid`=? 
  

• Any idea which queries need disk-based temp tables?

• What is a sample value used in CONVERT(?,DATETIME)? If that is a datetime that is living in a VARCHAR, then changing the datatype may have multiple benefits.

Answer 4

After upgrading one of the servers only 5.6 -> 5.7 we discovered the problem is still occurring, so something changed between 5.6 and 5.7 or we've had a small version of this problem for years, and are only now discovering it.

The answer seems to be, as of mysql 5.7+ you need to close your prepared statements eventually or the memory they use continually grows. Somewhere in memory/sql/Prepared_statement::main_mem_root ( but I haven't gone to the code to verify where the memory is going. )

we 'fixed' this by changing our C3P0 connection pool settings for maxIdleTimeExcessConnections to 300 (up from 0)

This cleaned up all of the long lived connections, and freed up the ram. Memory is now within predictable ranges. We still need to do some soak tests under load to see if we can blow things up if the connections never get closed still.