1

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

3
0

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.

6
  • And show us one of the slower queries, together with SHOW CREATE TABLE. – Rick James Jan 18 at 22:53
  • out of curiosity, what is making you think there's slow queries? I'll look into the temp tables. – David Beleznay Jan 18 at 23:01
  • @DavidBeleznay When temptable is created on disk it is way slower than memory based table. Temptable usually has no proper indexes and 128M+ will be proceeded by filesort and linear serch that are slow. Therefore I suspect that you have some queries slower than usual. Set long_query_time to 2 or even 1 second to see the slowest queries you have. – Kondybas Jan 18 at 23:04
  • I've adjusted the max_heap_table_size to 16M, and long_query_time to 2 and nothing has appeared in the slow_query_log. – David Beleznay Jan 18 at 23:56
  • @DavidBeleznay Is your load distributed evenly over the time? You had an incident in a day of activity, lets wait another day with a new settings. Also refer to the information_schema.threads for memory used value. – Kondybas Jan 19 at 0:17
0

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
13
  • table_open_cache for unknown reason is used by prepared statements infrastructure. When lower than 2-3k it can cause a mysterious errors if p/statements are used extensively. – Kondybas Jan 18 at 23:50
  • @rick-james what generates this report? – David Beleznay Jan 19 at 0:02
  • @DavidBeleznay - It is a script that I have. I've been struggling to put it online for self-service. But there are too many inconsistent recommendations that I have yet to dis-ambiguate. (There are over 200 tests; the ones that are "ok" are left out of this report.) I used to do the analysis manually, but it got too tedious to keep dividing by Uptime (or other values) to come up with metrics of interest. Anyway, I sometimes learn things as I review the results. – Rick James Jan 19 at 0:43
  • @rick-james thanks for replying. technically we would be swapping at this point. we're running our usually 8Gb machine on a 32Gb machine and are using 16Gb of it ( and still going up. ) really i think my question is just what goes into the event_name category memory/sql/Prepared_statement::main_mem_root in the performance schema. that's where all my ram is being used apparently. – David Beleznay Jan 19 at 0:54
  • @DavidBeleznay - Hmmmm... You are ahead of me on that. Perhaps you have found something that deserves a bug report. bugs.mysql.com – Rick James Jan 19 at 2:09
0

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.

9
  • dow_15_telemtry - this isn't changeable fast. our (possibly misguided) architecture has the idea of titles, with per_product and cross_product tables, where per_product has a set of identical schemas with title specific data in them. the idea was if we needed to scale, we could move a more heavily used title to it's own database server, but that's never happened This is actually the only cluster that has more than 1 title active on it, other clusters are just 1 title per cluster. – David Beleznay Jan 21 at 18:58
  • in the specific case of telemetry, client data comes in with different payloads, and is forwarded to amazon kinesis, the table (was) is used as a temporary queue. there isn't much data coming in via this method anymore ( more modern clients do this differently), this probably wouldn't be changed unless it was a bottleneck. – David Beleznay Jan 21 at 18:59
  • re: limit ?,? - 95% of the time i'd say the limit ends up being 0,X ( where X is the limit we want, e.g. 1000 ). It's only pagination in a couple cases where the clients could hit any host. the server if it's ever doing a query itself, it does remember – David Beleznay Jan 21 at 18:59
  • composite index. we do not, but match is only active matches, there's typically < 1000 rows in here ( on this server ) – David Beleznay Jan 21 at 19:00
  • re: disk-based temp tables. no.. but from that query you pointed out, i can see we're doing sub-selects. I'm wondering if that's a pattern in the code / sql generator. I did turn the slow query time down to 1, but nothing new has appeared in the slow-query.log. looking at the stats for those prepared statements ( if i'm reading them right , none of them seemed to go to disk? ) I'll have to see if there's an easy way to get those somehow. – David Beleznay Jan 21 at 19:05
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.