How to Prevent High MariaDB Memory Usage on Windows

Question

I am running MariaDB 10.5 and I am having trouble with it using too much memory on my Windows 10 machine. I have 16GB of RAM, and MariaDB ends up using around 8GB.

SHOW VARIABLES; https://pastebin.com/zCuQw2Ds

I have attempted to change innodb_buffer_pool_size to smaller or larger values. However, the memory usage is the same.

• The program using the database saves around 2k - 5k records into one table once per second (1 query)
• Around every 5 seconds, a transaction runs which deletes 10k different records from the same table and inserts 10 records into another table.

All of these queries run in under 1 second.

Is the data being inserted also being cached somehow? If so, how do I limit that behavior?

Also, here is SHOW GLOBAL STATUS output: https://pastebin.com/ubCMY6Me

How can I prevent the high memory usage?

Answer 1

Hmmm... I don't see "high" memory usage. Instead, I see "low" usage.

Analysis of GLOBAL STATUS and VARIABLES:

Observations:

• Version: 10.5.9-MariaDB
• 16 GB of RAM
• Uptime = 1d 22:31:42
• Are you sure this was a SHOW GLOBAL STATUS ?
• You are running on Windows.
• Running 64-bit version
• You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

key_buffer_size = 20M           -- save some RAM
innodb_buffer_pool_size = 11G   -- more caching (mostly saves I/O)
table_open_cache = 3000
query_cache_size = 0            -- to turn off

Why is innodb_lru_scan_depth so high (1536)?

There were a lot of rows deleted; do you think there is a problem with DELETEs? (There may be techniques to make them more efficient.)

Details and other observations:

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

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 128M / 0.70)) / 16384M = 5.0% -- Most of available ram should be made available for caching. -- http://mysql.rjweb.org/doc.php/memory

( Table_open_cache_misses ) = 543,542 / 167502 = 3.2 /sec -- May need to increase table_open_cache (now 2000)

( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 543,542 / (274450 + 543542) = 66.4% -- Effectiveness of table_open_cache. -- Increase table_open_cache (now 2000) and check table_open_cache_instances (now 8).

( innodb_buffer_pool_size ) = 128M -- InnoDB Data + Index cache -- 128M (an old default) is woefully small.

( innodb_buffer_pool_size ) = 128M / 16384M = 0.78% -- % 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 ) = 1,536 -- "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_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_buffer_pool_read_ahead_evicted / Innodb_buffer_pool_read_ahead ) = 7,541 / 158 = 4772.8% -- Utility of read_ahead. -- Turn off innodb_random_read_ahead (now OFF).

( Innodb_dblwr_pages_written/Innodb_pages_written ) = 73,383/706683 = 10.4% -- Seems like these values should be equal?

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

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 24,911,135,232 / (167502 / 3600) / 1 / 96M = 5.32 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 167,502 / 60 * 96M / 24911135232 = 11.3 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 100663296). (Cannot change in AWS.)

( innodb_flush_method ) = innodb_flush_method = unbuffered -- 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

( default_tmp_storage_engine ) = default_tmp_storage_engine =

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

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = OFF -- 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).

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

( join_buffer_size ) = 262,144 / 16384M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc. -- If large, decrease join_buffer_size (now 262144) to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 16384M = 11.6% -- Byte limit on FULLTEXT resultset. (Possibly not preallocated, but grows?) -- Lower the setting.

( character_set_server ) = character_set_server = utf8 -- Charset problems may be helped by setting character_set_server (now utf8) to utf8mb4. That is the future default.

( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10%

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

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (79156 + 8 + 43352 + 0) / 67922 = 1.8 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 22104 - 0 ) / ( 22104 + 0 ) = 100.0% -- Are you closing your prepared statements? -- Add Closes.

( Com_stmt_prepare - Com_stmt_close ) = 22,104 - 0 = 22,104 -- How many prepared statements have not been closed. -- CLOSE prepared statements

( Com_stmt_close / Com_stmt_prepare ) = 0 / 22104 = 0 -- Prepared statements should be Closed. -- Check whether all Prepared statements are "Closed".

( Com__biggest ) = Com__biggest = Com_stmt_execute -- Which of the "Com_" metrics is biggest. -- Normally it is Com_select (now 143366). If something else, then it may be a sloppy platform, or may be something else.

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

( Max_used_connections / max_connections ) = 19 / 151 = 12.6% -- Peak % of connections -- Since several memory factors can expand based on max_connections (now 151), it is good not to have that setting too high.

( Aborted_clients / Connections ) = 263 / 306 = 85.9% -- Threads bumped due to timeout -- Increase wait_timeout (now 28800); be nice, use disconnect

( Aborted_connects / Connections ) = 136 / 306 = 44.4% -- Perhaps a hacker is trying to break in? (Attempts to connect)

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

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

Abnormally small:

Aria_pagecache_write_requests = 1.5 /HR
Com_set_option = 1.2 /HR
Com_show_fields = 0
Created_tmp_files = 0.086 /HR
Handler_icp_attempts = 0.34 /HR
Handler_icp_match = 0.34 /HR
Handler_read_rnd = 33 /HR
Handler_tmp_update = 0
Handler_tmp_write = 0.13 /sec
Handler_update = 0.17 /HR
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_buffer_pool_pages_total = 8,065
Innodb_ibuf_merged_inserts = 0
Innodb_ibuf_merges = 0
Innodb_log_writes / Innodb_log_write_requests = 0.35%
Innodb_max_trx_id = 688,304
Innodb_mem_adaptive_hash = 0
Innodb_mem_dictionary = 892,392
Innodb_num_open_files = 17
Innodb_rows_updated = 0.17 /HR
Innodb_secondary_index_triggered_cluster_reads = 0.41 /HR
Memory_used_initial = 3.52e+7
Rows_tmp_read = 0.13 /sec
Sort_priority_queue_sorts = 0
Sort_rows = 33 /HR
Sort_scan = 1.8 /HR
Table_locks_immediate = 3.2 /HR
aria_checkpoint_log_activity = 1.05e+6
aria_pagecache_buffer_size = 128MB
innodb_adaptive_max_sleep_delay = 0
innodb_background_scrub_data_check_interval = 0
innodb_background_scrub_data_interval = 0
innodb_buffer_pool_chunk_size = 128MB
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
lock_wait_timeout = 86,400
query_cache_limit = 1.05e+6

Abnormally large:

Com_create_index = 0.021 /HR
Com_drop_index = 0.064 /HR
Com_rename_table = 0.064 /HR
Com_show_open_tables = 0.13 /HR
Handler_delete = 447 /sec
Handler_discover = 1.7 /HR
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 2.27
Innodb_buffer_pool_pages_lru_flushed = 1,606
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 35029.2%
Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 673
Innodb_dblwr_pages_written / Innodb_dblwr_writes = 71.4
Innodb_os_log_pending_writes = 1.84e+19
Innodb_rows_deleted = 447 /sec
Innodb_rows_deleted + Innodb_rows_inserted = 1274 /sec
Innodb_rows_inserted = 826 /sec
Memory_used = 42.6%
Open_streams = 4
Prepared_stmt_count = 967
Select_range / Com_select = 71.8%
Threadpool_threads = 4
aria_sort_buffer_size = 256.0MB
innodb_lru_scan_depth / innodb_io_capacity = 7.68
max_relay_log_size = 1024MB
performance_schema_max_cond_classes = 90
performance_schema_max_statement_classes = 222

Abnormal strings:

aria_recover_options = BACKUP,QUICK
binlog_row_metadata = NO_LOG
disconnect_on_expired_password = OFF
have_crypt = NO
histogram_type = DOUBLE_PREC_HB
innodb_fast_shutdown = 1
innodb_log_optimize_ddl = OFF
log_slow_admin_statements = ON
lower_case_file_system = ON
lower_case_table_names = 1
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
opt_s__optimize_join_buffer_size = on
optimizer_trace = enabled=off
slave_parallel_mode = optimistic
thread_handling = pool-of-threads
time_zone = +00:00
use_stat_tables = PREFERABLY_FOR_QUERIES
version_compile_machine = x64