0

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  
2
0

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
3
  • Thank you for this quick and in depth analysis! It did explain alot. Having played with the settings a bit further with your new input. I was able to get memory usage stable below 20gb. I don't think all is optimal yet, but no more overflows and timeouts for the past day! – dotSpokane Apr 17 at 2:20
  • btw we don't use aria at all. Is there a valid reason there are so many aria stats like Aria_pagecache_writes = 435 /sec? – dotSpokane Apr 17 at 2:22
  • @dotSpokane - Well, I don't know everything; I'm curious about that one, too. Ah, here's the answer: "This storage engine has been used for the MariaDB system tables since the 10.4 version." That makes MariaDB 10.4 more robust than even MySQL 5.7. Thanks; I learned something. – Rick James Apr 17 at 2:49

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.