Sorry, nothing obvious that would cause it to start swapping. Since 10.4 and 10.5 are GA, I recommend upgrading.
Analysis of GLOBAL STATUS and VARIABLES:
Observations:
- Version: 10.3.29-MariaDB-0+deb10u1-log
- 14 GB of RAM
- Uptime = 11d 17:07:01
The More Important Issues:
The 4G buffer_pool is lower than it normally should be for 14GB of RAM. So, I am very surprised that you are experiencing swapping.
It seems that nearly all transactions are being ROLLBACK'd. Could this be the case?
Lower thread_cache_size to 20.
Com_show_... is performed a huge number of times. Are you using some 3rd party package that can't remember what the schema is? Or maybe a monitoring package that is working too hard?
Details and other observations:
( Key_blocks_used * 1024 / key_buffer_size ) = 36 * 1024 / 128M = 0.03% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size (now 134217728) to avoid unnecessary memory usage.
( table_open_cache ) = 4,096 -- Number of table descriptors to cache
-- Several hundred is usually good.
( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 301,840 / (4230136 + 301840) = 6.7% -- Effectiveness of table_open_cache.
-- Increase table_open_cache (now 4096) and check table_open_cache_instances (now 3).
( innodb_buffer_pool_size ) = 4,096 / 14336M = 28.6% -- % 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_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 26,641,836,544 / (1012021 / 3600) / 2 / 512M = 0.0883 -- Ratio
-- (see minutes)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,012,021 / 60 * 512M / 26641836544 = 339 -- 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 536870912). (Cannot change in AWS.)
( 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
( Com_rollback ) = 328,443 / 1012021 = 0.32 /sec -- ROLLBACKs in InnoDB.
-- An excessive frequency of rollbacks may indicate inefficient app logic.
( default_tmp_storage_engine ) = default_tmp_storage_engine =
( Innodb_row_lock_time_avg ) = 1,078 -- Avg time to lock a row (millisec)
-- Possibly conflicting queries; possibly table scans.
( Innodb_row_lock_time_max ) = 17,935 -- Max time to lock a row (millisec)
-- Possibly conflicting queries; possibly table scans.
( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( innodb_strict_mode ) = innodb_strict_mode = OFF -- Catches some subtle errors earlier.
-- OFF leaves some warnings as warnings; ON makes them errors.
( 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_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- 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). 10.5.0 decided to default OFF.
( 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.
( innodb_ft_result_cache_limit ) = 2,000,000,000 / 14336M = 13.3% -- Byte limit on FULLTEXT resultset. (Possibly not preallocated, but grows?)
-- Lower the setting.
( (Com_show_create_table + Com_show_fields) / Questions ) = (299306 + 7279) / 7834983 = 3.9% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( Com_rollback / (Com_commit + Com_rollback) ) = 328,443 / (5 + 328443) = 100.0% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (167260 + 830335 + 8880 + 0) / 5 = 201,295 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.
( long_query_time ) = 60 -- 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 ) = 15 / 151 = 9.9% -- 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.
( thread_cache_size / Max_used_connections ) = 151 / 15 = 1006.7%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.
Abnormally small:
Handler_write = 0.33 /sec
Table_locks_immediate = 0.89 /HR
eq_range_index_dive_limit = 0
innodb_spin_wait_delay = 4
wsrep_cert_index_size = 129
wsrep_local_index = 0
Abnormally large:
Acl_database_grants = 350
Acl_table_grants = 63
Acl_users = 145
Com_alter_db = 0.0036 /HR
Com_create_user = 0.0036 /HR
Com_show_profiles = 0.52 /HR
Com_show_status = 0.77 /sec
Handler_discover = 4.4 /HR
Handler_tmp_update = 1122 /sec
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 1.45
Innodb_instant_alter_column = 307
Innodb_num_open_files = 4,096
Innodb_pages0_read = 12,045
Open_table_definitions = 9,000
Select_full_range_join / Com_select = 0.63%
Tc_log_max_pages_used = 5
Tc_log_page_size = 4,096
Transactions_multi_engine = 0.95 /sec
table_definition_cache = 9,000
wsrep_applier_thread_count = 16
wsrep_cluster_size = 3
wsrep_commit_oool = 6.3e-5
wsrep_local_bf_aborts = 11
wsrep_slave_threads = 16
wsrep_thread_count = 17
Abnormal strings:
Innodb_have_snappy = ON
aria_recover_options = BACKUP,QUICK
innodb_fast_shutdown = 1
log_slow_admin_statements = ON
log_slow_verbosity = query_plan
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
wsrep_cluster_status = Primary
wsrep_connected = ON
wsrep_node_address = node3
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_provider_name = Galera
wsrep_provider_vendor = Codership Oy
wsrep_provider_version = 3.25(rddf9876)
wsrep_ready = ON
wsrep_sst_method = mariabackup
