0

I am running mariaDB 10.3.29 in a 3 node Galera Cluster on 3 VMs with 14G RAM each. Despite configuring to best knowledge, it continues to eat more and more RAM and after around 3 months the machines start swapping and I restart the DB process.

I pasted the server status and variables to a pastebin, taken from node3 after a running time of about 2-3 weeks now. https://pastebin.com/aqLcfqbS

So far I noticed a quite high open_table value.... If some expert has a valuable hint or advice, I would be really happy! I can provide more infos if asked.

Thanks!

1
  • I suspect the bug MDEV-19287 and you are on the right track. Raise your open_table_cache and table_definition_cache by a few 2-4 times its value and see if the leak diminishes.
    – danblack
    Sep 9, 2021 at 11:50

1 Answer 1

1

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
1
  • Thanks for taking a look! I will adjust some variables as recommended and forward info to the app developer, then observe further. If the problem still persists, I guess I will have to move to 10.5..... Sep 10, 2021 at 10:02

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.