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
SHOW CREATE TABLE
and the actualSELECT
. For discussing the settings, please provideSHOW GLOBAL STATUS
after the server has been running at least a day.