0

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?

2
  • For discussing the slow query, please provide SHOW CREATE TABLE and the actual SELECT. For discussing the settings, please provide SHOW GLOBAL STATUS after the server has been running at least a day.
    – Rick James
    May 18, 2021 at 17:12
  • @RickJames Thanks for your reply! The server hasn't been running for 24 hrs yet, but the memory issue crops up pretty quickly, so here is the output in that state: pastebin.com/ubCMY6Me
    – Dakota
    May 19, 2021 at 12:11

1 Answer 1

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

Your Answer

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

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