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