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.
mysqld Ver 10.3.27-MariaDB-0+deb10u1
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
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
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:
- 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
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.
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
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
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