3

I'm currently running a MySQL server (version 5.7.42) in production, and I've noticed a gradual increase in memory usage throughout the day. I suspect there might be a memory leak issue causing this behavior.

Here are some details about my server configuration:

MySQL version: 5.7.42
Server specs: 4 vCPUs, 8 GB of RAM
MySQL data directory: SSD (no IOPS limit applied)
SO: Ubuntu 22.04

I'm using the Prometheus exporter to monitor the server, and the memory usage keeps growing gradually over time.

Before upgrading to version 5.7.42, I was using version 5.7.22, and I didn't encounter this issue with memory usage. I'm wondering why this problem has arisen after the upgrade.

To investigate the issue further, I tried reproducing the problem in a similar environment using Ubuntu 18.04, as the MySQL 5.7.42 repositories are not officially compatible with Ubuntu 22 bu I obtained the same results.

It significantly worsens when the automysqlbackup process is triggered during the early morning hours. Also I'm using the --quick and --single-transaction options in process to avoid locking issues and improve the performance.

This is the actual configuration that I'm using:

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0
default-character-set = utf8mb4

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

max_connections        = 200
max_allowed_packet     = 128M
interactive_timeout    = 600
wait_timeout           = 600

table_definition_cache = 4096
key_buffer_size        = 128M
max_heap_table_size    = 256M
tmp_table_size         = 256M
join_buffer_size       = 4M
thread_cache_size      = 16

log_error              = /var/log/mysql/mysql.err
log_error_verbosity    = 2
long_query_time        = 5
slow-query-log         = on
slow_query_log_file    = /var/log/mysql/mysql-slow.log


innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 3072M
innodb_log_file_size = 256M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M
innodb_file_per_table= ON
innodb_stats_on_metadata = OFF
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
query_cache_type = 0
query_cache_size = 0
group_concat_max_len = 50000

innodb_file_per_table=true
open_files_limit = 40000
table_open_cache = 19000
innodb_large_prefix

character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
sql_mode=""
explicit_defaults_for_timestamp = 1
log_error = /var/log/mysql/error.log

Is there a specific configuration setting I should be looking at or any additional steps I can take to diagnose and resolve this memory leak issue? Any insights, suggestions, or troubleshooting steps would be greatly appreciated.

Thank you in advance for your help!

EDIT 1

After the nightly backup process today was the first time that the server did not reach 100% memory usage.The process starts at 1:00.

Today's mysql server memory used

Last week mysql server memory used

max_heap_table_size and tmp_table_size variables were modified to 1% of the server's available memory as recommended.

It will become clear tomorrow if this was the main reason in case the memory usage graph still retains these values and does not shoot back to 100% after the backup process.

Also I attach SHOW VARIABLES output: https://pastebin.com/ZXkGsfwD

EDIT 2

SHOW GLOBAL STATUS output: https://pastebin.com/J9pdN9fM I will update tomorrow as there will be more traffic on the server during the day.

0

3 Answers 3

2

See if any of these apply:

----- 2022-04-26 5.7.38 General Availability -- -- -----

A memory leak occurred if mysqldump was used on more than one table with the --order-by-primary option. The memory allocated for sorting each table’s rows is now freed after every table, rather than only once. (Bug #30042589, Bug #96178)

----- 2020-08-24 Perc 5.7.31-34 -- Bugs Fixed -- -----

PS-7203: Fixed audit plugin memory leak on replicas when opening tables

----- 2020-05-20 Perc 5.7.30-33 -- Bugs Fixed -- -----

PS-5844: Fix a memory leak after ‘innodb.alter_crash’ in ‘prepare_inplace_alter_table_dict()’ (Upstream #96472)

----- 2020-04-27 5.7.30 General Availability -- -- -----

The Event Scheduler had a memory leak. (Bug #30628268)

----- 2020-02-05 Perc 5.7.29-32 -- Bugs Fixed -- -----

PS-5843: A memory leak could occur after “group_replication.gr_majority_loss_restart”. (Upstream #96471)

----- 2020-01-13 5.7.29 General Availability -- -- Replication -----

A memory leak could occur when a failed replication group member tried to rejoin a minority group and was disallowed from doing so. (Bug #30162547, Bug #96471)

----- 2019-07-22 5.7.27 General Availability -- -- -----

The parser could leak memory for certain multiple-statement queries. (Bug #29419820)

----- 2019-07-22 5.7.27 General Availability -- -- -----

audit_log filtering operations could leak memory. (Bug #29201747)

----- 2019-04-25 5.7.26 General Availability -- -- InnoDB -----

Memory leaks discovered in the innochecksum utility were removed. (Bug #28917614, Bug #93164)

----- 2019-01-21 5.7.25 General Availability -- -- InnoDB -----

A dangling pointer caused a memory leak. (Bug #28693568)

----- 2019-01-21 5.7.25 General Availability -- -- -----

A memory leak was caused by GET_LOCK() calls with a zero timeout that failed due to concurrent connections holding the same user-level lock. (Bug #28714367)

----- 2019-01-21 5.7.25 General Availability -- -- -----

mysqlpump did not free all allocated resources when it encountered an error, resulting in memory leaks. (Bug #28538971, Bug #92131)

----- 2018-11-21 Perc 5.7.23-25 -- Bugs Fixed -- -----

#5049: A severe memory leak regression in the RocksDB Block Cache

----- 2018-07-27 5.7.23 General Availability -- -- -----

A memory leak in the pfs-t unit test was fixed. Thanks to Yura Sorokin for the patch. (Bug #27440735, Bug #89384)

(I scraped those from the Changelogs and/or Release notes.)

4
  • you actually got all info from dev.mysql.com/doc/search/?d=145&p=1&q=memory+leak, and made it into an answer? 😉
    – Luuk
    May 27, 2023 at 16:58
  • 1
    @Luuk - I have a crawler that collects such from 3 places (including MariaDB). A tradeoff between parsing in advance and parsing when querying.
    – Rick James
    May 27, 2023 at 17:27
  • Thanks @RickJames for the comment, I am using the latest available version 5.7 so I understand that all these bugs should be fixed.
    – George
    May 27, 2023 at 21:36
  • @George - Hmmm. I guess my analysis is backwards.
    – Rick James
    May 27, 2023 at 21:57
0

(Analysis of chart)

  • The steep decline at 23:40 is probably a restart or some kind of flush. Neither should (normally) be necessary.*
  • The line between those 23:40 and 00:05 smells like "normal" activity -- some of the data is probed somewhat randomly. The "buffer_pool" is gradually being filled. The steep rise at 00:05 sounds like reading all the data (eg mysqldump).
  • Or the rise could be from ALTERing a huge table or SELECTing from a huge table. Do you have one table that contains most of the data?
  • The 'flat' line the rest of the time is also normal -- the buffer_pool is full and churning based on the needed data. This is may or may not be of concern. (GLOBAL STATUS may have some clues.)
  • The flat line is not perfectly flat because of smaller caches and malloc activity. Again, "normal".
  • Puzzle: One graph flatines at about 60%, the other at 100%. Please explain why they are not the same.
  • Did a configuration setting (eg, innodb_buffer_pool_size) change on about 5/27?

With 8GB of RAM and innodb_buffer_pool_size=3G, I would expect MySQL usage to never go over about 50%. Thanks for the VARIABLES, but where are the GLOBAL STATUS values?

4
  • Automysqldump starts exactly at 1:20 CEST. Most of the databases on this server are from Moodle educational platforms so the capacity is mainly focused on a single table (mdl_logstore_standard_log). On 5/27 I only modified the following variables max_heap_table_size and tmp_table_size. The first graph is the memory usage of the server last night. Its the first time that this consumption occurs on the server because previous days as you can see in the second graph, as soon as the automysqlbackup process starts the memory consumption soars to 100% and does not release memory after hours.
    – George
    May 28, 2023 at 21:02
  • SHOW GLOBAL STATUS added to the post. Thanks for your time Rick.
    – George
    May 28, 2023 at 21:12
  • How big is mdl_logstore_standard_log? SHOW TABLE STATUS LIKE 'mdl_logstore_standard_log'; Can you provide SHOW CREATE TABLE mdl_logstore_standard_log;
    – Rick James
    May 28, 2023 at 22:57
  • More memory was used after decreasing max_heap_table_size and tmp_table_size?? Maybe I am confused by what the values used to be and when they were changed.
    – Rick James
    May 28, 2023 at 22:59
0

Analysis of GLOBAL STATUS and VARIABLES:

Observations:

  • Version: 5.7.42-log
  • 8 GB of RAM
  • Uptime = 23:29:29; some GLOBAL STATUS values may not be meaningful yet.
  • 813 QPS

The More Important Issues:

Suggested configuration changes:

table_open_cache = 5000
key_buffer_size = 20M
long_query_time = 1.0  -- and turn on the slowlog

What is going on with "savepoints"?

7.2 CREATE INDEXes per hour -- Why so often?

Handler_write = 12860 /sec -- Lots of temp table for complex queries?

How much disk space do the database(s) occupy? Are there other apps running and occupying memory?

24% ROLLBACK seems rather high. What's up?

Use the slolog to locate the queries using tmp disk tables and other things flagged below. SlowLog

If changing max_heap_table_size and tmp_table_size caused memory to go up, then lower those values. But, also, the slowlog should help us find which queries are using those settings and maybe better indexes or query reformulation will decrease the temp table usage.

Details and other observations:

( Key_blocks_used * 1024 / key_buffer_size ) = 3 * 1024 / 128M = 0.00% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size (now 134217728) to avoid unnecessary memory usage.

( Opened_tables ) = 9812983 / 84569 = 116 /sec -- Frequency of opening Tables -- increase table_open_cache (now 2395)

( Table_open_cache_overflows ) = 9788244 / 84569 = 115 /sec -- May need to increase table_open_cache (now 2395)

( Table_open_cache_misses ) = 9812766 / 84569 = 116 /sec -- May need to increase table_open_cache (now 2395)

( Opened_tables / Uptime / table_open_cache_instances ) = 9,812,983 / 84569 / 16 = 7.25 -- A metric for table_open_cache_instances -- Increase table_open_cache_instances (now 16) to cut back on contention for the table_open_cache (now 2395).

( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 9,812,766 / (69719036 + 9812766) = 12.3% -- Effectiveness of table_open_cache. -- Increase table_open_cache (now 2395) and check table_open_cache_instances (now 16).

( innodb_buffer_pool_size ) = 3,072 / 8192M = 37.5% -- % of RAM used for InnoDB buffer_pool -- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)

( 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_lru_scan_depth is a very poorly named variable. A better name would be innodb_free_page_target_per_buffer_pool. It is a number of pages InnoDB tries to keep free in each buffer pool instance to speed up read and page creation operations. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((2106801 + 9919043) ) / 84569 = 142 /sec -- InnoDB I/O -- Increase innodb_buffer_pool_size (now 3221225472)?

( Innodb_buffer_pool_pages_flushed ) = 9919043 / 84569 = 117 /sec -- Writes (flushes) -- Increase innodb_buffer_pool_size (now 3221225472)?

( Innodb_log_writes ) = 4002464 / 84569 = 47 /sec

( innodb_flush_method ) = innodb_flush_method = O_DIRECT_NO_FSYNC -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( Innodb_dblwr_writes ) = 456541 / 84569 = 5.4 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them. -- (Symptom of other issues)

( innodb_flush_neighbors ) = innodb_flush_neighbors = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Whether to use the adapative hash (AHI). -- ON for mostly readonly; OFF for DDL-heavy

( innodb_flush_log_at_trx_commit ) = 2 -- 1 = secure; 2 = faster -- (You decide) Use 1, along with sync_binlog (now 1)=1 for the greatest level of fault tolerance. 0 is best for speed. 2 is a compromise between 0 and 1.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.

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

( min( tmp_table_size, max_heap_table_size ) ) = (min( 82M, 82M )) / 8192M = 1.0% -- Percent of RAM to allocate when needing MEMORY table (per table), or temp table inside a SELECT (per temp table per some SELECTs). Too high may lead to swapping. -- Decrease tmp_table_size (now 85983232) and max_heap_table_size (now 85983232) to, say, 1% of ram.

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 8192M = 23.3% -- Byte limit on FULLTEXT resultset. (It grows as needed.) -- Lower the setting.

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

( Created_tmp_tables ) = 5555402 / 84569 = 66 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 1101804 / 84569 = 13 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size (now 85983232) and max_heap_table_size (now 85983232). 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.

( tmp_table_size ) = 82M -- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size (now 85983232) to avoid running out of RAM. Perhaps no more than 64M.

( Com_rollback / (Com_commit + Com_rollback) ) = 10,962 / (34366 + 10962) = 24.2% -- Rollback : Commit ratio -- Rollbacks are costly; change app logic

( Com_show_variables ) = 147376 / 84569 = 1.7 /sec -- SHOW VARIABLES ... -- Why are you requesting the VARIABLES so often?

( Sort_merge_passes ) = 35299 / 84569 = 0.42 /sec -- Heafty sorts -- Increase sort_buffer_size (now 262144) and/or optimize complex queries.

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

( back_log ) = 90 -- (Autosized as of 5.6.6; based on max_connections) -- Raising to min(150, max_connections (now 200)) may help when doing lots of connections.

( Aborted_connects ) = 19,728 / 84569 = 0.23 /sec -- Could not get a connection, or hacker ? (Attempts to connect) -- Raise max_connections (now 200)

( Connections ) = 436748 / 84569 = 5.2 /sec -- Connections -- Increase wait_timeout (now 600); use pooling?

Abnormally small:

Open_files = 1
interactive_timeout = 600

Abnormally large:

Com_create_index = 7.2 /HR
Com_release_savepoint = 1 /HR
Com_rollback_to_savepoint = 0.095 /sec
Com_savepoint = 1 /HR
Com_show_create_db = 1 /HR
Com_show_create_func = 0.94 /HR
Com_show_create_trigger = 0.085 /HR
Com_show_events = 1 /HR
Com_show_table_status = 0.095 /sec
Com_show_triggers = 0.095 /sec
Created_tmp_files = 0.83 /sec
Handler_savepoint = 1 /HR
Handler_savepoint_rollback = 0.095 /sec
Handler_write = 12860 /sec
Open_table_definitions = 3,798
Performance_schema_digest_lost = 455,163
Select_range_check = 30 /HR
Ssl_session_cache_misses = 1

Abnormal strings:

innodb_fast_shutdown = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
sql_slave_skip_counter = 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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