Mysqld draining memory, using way more then assigned and not giving any back

debianinnodbmariadbMySQL

This is a production server.

Whenever mysqld is restarted the memory consumption slowly builds up to about 75 to 90% of my total memory (while only 8 out of 32GB is allocated to mysqld).
Sometimes this takes 5 hours, sometimes this takes 16 hours.
Usually it sticks around that percentage for a good amount of hours.
If that was just it it would be fine, but after some time it then starts using even more memory, Until it overflows, causing my 32GB swap drive to fill up to 100% sometimes.
Now things start to rapidly slow down to a crawl with mostly timeouts. It does recover after some time to the point it will no longer timeout for a while, but the memory never frees up so its constantly on the verge of dropping out again.

Anyone have an insight on how can this be?
Why is mysqld using so much memory and not returning any free mem?
And why is it not respecting at all the buffer limits that were set?
I have played around for some time with the mariadb.conf, but even reverting all back to default doesn’t seem to have much impact on the memory usage.

Running

mysqld  Ver 10.3.27-MariaDB-0+deb10u1

top stats

mysql     20   0   33.2g  25.1g   3760 S 521.6  80.1   1122:26 mysqld

Memory/buffer specs from mysql tuner

[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 8.0G
[--] Other process memory: 1.4G                                                                                                                                                                                                               
[--] Total buffers: 7.5G global + 2.9M per thread (151 max threads)                                                                                                                                                                           
[--] P_S Max memory usage: 104M
[--] Global Buffers
[--]  +-- Key Buffer: 128.0M
[--]  +-- Max Tmp Table: 256.0M
[--] Query Cache Buffers
[--]  +-- Query Cache: OFF - DISABLED
[--]  +-- Query Cache Size: 0B
[--] Per Thread Buffers
[--]  +-- Read Buffer: 128.0K
[--]  +-- Read RND Buffer: 256.0K
[--]  +-- Sort Buffer: 2.0M
[--]  +-- Thread stack: 292.0K
[--]  +-- Join Buffer: 256.0K

Mariadb conf

skip-name-resolve
performance_schema      = ON
query_cache_type        = 0
query_cache_size        = 0   
tmp_table_size          = 256M
max_heap_table_size     = 256M
innodb_log_files_in_group       = 4
innodb_buffer_pool_size        = 7G 
innodb_status_file                      #extra reporting
innodb_file_per_table                   #enable always
innodb_flush_log_at_trx_commit  = 2     #2/0 = perf, 1 = ACID
innodb_table_locks              = 0  
innodb_lock_wait_timeout        = 60
innodb_thread_concurrency       = 24 
innodb_commit_concurrency       = 2 
innodb_log_file_size   = 384M
innodb_buffer_pool_instances = 7

Best Answer

Below is an analysis of what you have. Nothing points to how it could be over-filling memory. Please provide the evidence (top / htop / innodb_top / whatever) that shows mysqld larger than, say, 10GB. Maybe there are some clues there.

Analysis of GLOBAL STATUS and VARIABLES:

Observations:

Version: 10.3.27-MariaDB-0+deb10u1
32 GB of RAM
Uptime = 13:59:47; some GLOBAL STATUS values may not be meaningful yet.
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB. (I don't know if Aria is consuming a lot of space.)

The More Important Issues:

To free up some RAM: key_buffer_size = 20M

The dataset seems to be quite small; innodb_buffer_pool_size (now 7,516,192,768) is probably bigger than necessary.

If you are using a SSD drive and are having I/O issues, some settings can be adjusted.

autocommit was on when you captured the GLOBAL STATUS. If that is your normal mode of running things, do not forget to perform COMMIT.

You are using ENGINE=Aria a lot? (My analysis does not cover much of that.)

Several clues indicate that some queries may need optimization. Suggest turning the slowlog on with a small value for long_query_time to see which queries need the most help.

You are doing some things with InnoDB's FULLTEXT? I see that the stopwords are turned off an the word length has been shrunk. How is that going?

Details and other observations:

( innodb_buffer_pool_size ) = 7,168 / 32768M = 21.9% -- % 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_page_cleaners / innodb_buffer_pool_instances ) = 4 / 7 = 0.571 -- innodb_page_cleaners -- Recommend setting innodb_page_cleaners (now 4) to innodb_buffer_pool_instances (now 7) (Beginning to go away in 10.5)

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

( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs. -- Reads could be slugghish or spiky.

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 100,551 / 458752 = 21.9% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size (now 7516192768) is bigger than necessary?

( 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_doublewrite ) = innodb_doublewrite = OFF -- Extra I/O, but extra safety in crash. -- OFF is OK for FusionIO, Galera, Replicas, ZFS.

( innodb_log_files_in_group ) = 4 -- Number of InnoDB log files -- 2 is probably the only reasonable value. A large number may cause performance problems.

( innodb_flush_method ) = innodb_flush_method = 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_row_lock_waits ) = 10,785 / 50387 = 0.21 /sec -- How often there is a delay in getting a row lock. -- May be caused by complex queries that could be optimized.

( Innodb_row_lock_waits/Innodb_rows_inserted ) = 10,785/8926 = 120.8% -- Frequency of having to wait for a row.

( innodb_table_locks ) = innodb_table_locks = OFF -- Something to do with autocommit=0.

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

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

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster. 0 is OK for Galera.

( 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 / Created_tmp_tables ) = 9,073 / 14054 = 64.6% -- Percent of temp tables that spilled to disk -- Maybe increase tmp_table_size (now 268435456) and max_heap_table_size (now 268435456); improve indexes; avoid blobs, etc.

( Sort_merge_passes ) = 1,196,767 / 50387 = 24 /sec -- Heafty sorts -- Increase sort_buffer_size (now 2097152) and/or optimize complex queries.

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

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

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

( Connections ) = 1,584,168 / 50387 = 31 /sec -- Connections -- use pooling?

( thread_cache_size / Max_used_connections ) = 151 / 101 = 149.5% -- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

Abnormally small:

Acl_users = 4
Aria_pagecache_blocks_unused = 14,017
Handler_write = 0.19 /sec
Innodb_dblwr_pages_written = 0
aria_checkpoint_log_activity = 1.05e+6
aria_pagecache_buffer_size = 128MB
eq_range_index_dive_limit = 0
innodb_buffer_pool_chunk_size = 128MB
innodb_ft_min_token_size = 2
innodb_max_undo_log_size = 10MB
innodb_online_alter_log_max_size = 128MB
innodb_sort_buffer_size = 1.05e+6
innodb_spin_wait_delay = 4

Abnormally large:

Aria_pagecache_blocks_not_flushed = 1,688
Aria_pagecache_read_requests = 14019 /sec
Aria_pagecache_write_requests = 435 /sec
Aria_pagecache_writes = 435 /sec
Aria_transaction_log_syncs = 1,618
Com_show_slave_hosts = 0.21 /HR
Created_tmp_files = 3.8 /sec
Feature_fulltext = 22 /sec
Feature_subquery = 32 /sec
Handler_discover = 3.7 /HR
Handler_icp_attempts = 62394 /sec
Handler_icp_match = 62386 /sec
Handler_read_key = 56640 /sec
Handler_read_last = 3.4 /sec
Handler_read_next = 1378189 /sec
Handler_read_prev = 32923 /sec
Handler_read_rnd = 7044 /sec
Handler_tmp_write = 13198 /sec
Innodb_buffer_pool_read_requests = 2259173 /sec
Innodb_rows_read = 1870004 /sec
Innodb_secondary_index_triggered_cluster_reads = 210238 /sec
Rows_read = 1790121 /sec
Sort_priority_queue_sorts = 12 /sec
Sort_rows = 32610 /sec
Tc_log_page_size = 4,096
aria_sort_buffer_size = 256.0MB
innodb_commit_concurrency = 2
innodb_thread_concurrency = 24
max_heap_table_size = 256MB
max_relay_log_size = 1024MB
min(max_heap_table_size, tmp_table_size) = 256MB
performance_schema_events_stages_history_size = 20
performance_schema_events_statements_history_size = 20
performance_schema_events_waits_history_size = 20
tmp_memory_table_size = 256MB

Abnormal strings:

Innodb_have_snappy = ON
aria_recover_options = BACKUP,QUICK
ft_min_word_len = 2
ft_stopword_file = 
innodb_data_home_dir = /var/lib/mysql
innodb_fast_shutdown = 1
innodb_ft_enable_stopword = OFF
innodb_log_optimize_ddl = OFF
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT

Related Solutions

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite." It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high. Here's what makes me think this might be contributing to your problem:

When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

^{http://dev.mysql.com/doc/refman/5.6/en/memory-use.html}

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time. Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not. I tested this on one of my systems, which had 9 threads in the cache. Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem. If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed. Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test. You should not see a substantial performance penalty by setting thread_cache_size to 0. Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.

MySQL over total memory allocated usage? memory leak

i have found out why memory leak happeneds. now i have only 1GB memory and no swap.

the performance_schema cause the allocate the memory for about 400M at startup. and innodb will take up to 400M memory usage

so，because of my.cnf use

table-definition-cache= 1024
table-open-cache= 2048 
max-connections= 60

it will cause high performance_schema allocated, more information pls see

ref1

ref2

Best Answer

Related Solutions

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

MySQL over total memory allocated usage? memory leak

Related Question