I am running MariaDB 10.5 on I am having trouble with MariaDB 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 small or large values. The memory usage is the same.
The program using the DB 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
Any help is appreciated, thanks!
Best Answer
Hmmm... I don't see "high" memory usage. Instead, I see "low" usage.
Observations:
The More Important Issues:
Why is
innodb_lru_scan_depthso 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:
Abnormally large:
Abnormal strings: