I have 3 nodes Galera cluster with MariaDB 10.4.13. Each node have 32GB RAM, and 2GB Swap. After my mysql tuning about 1 month ago each node memory almost full, but I think it is ok. But the last few days Swap size reached maximum and does not go down. My my.cnf looks like this:
####Slow logging
slow_query_log_file=/var/lib/mysql/mysql-slow.log
long_query_time=2
slow_query_log=ON
log_queries_not_using_indexes=ON
############ INNODB OPTIONS
innodb_buffer_pool_size=24000M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_data_file_path=ibdata1:100M:autoextend
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_doublewrite=1
innodb_log_file_size=6144M
innodb_log_buffer_size=96M
innodb_buffer_pool_instances=24
innodb_log_files_in_group=2
innodb_thread_concurrency=0
#### innodb_file_format = barracuda
innodb_flush_method = O_DIRECT
#### innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode=2
######## avoid statistics update when doing e.g show tables
innodb_stats_on_metadata=0
default_storage_engine=innodb
innodb_strict_mode = 0
#### OTHER THINGS, BUFFERS ETC
#### key_buffer_size = 24M
tmp_table_size = 1024M
max_heap_table_size = 1024M
max_allowed_packet = 512M
#### sort_buffer_size = 256K
#### read_buffer_size = 256K
#### read_rnd_buffer_size = 512K
#### myisam_sort_buffer_size = 8M
skip_name_resolve
memlock=0
sysdate_is_now=1
max_connections=500
thread_cache_size=512
query_cache_type = 1
query_cache_size = 512M
query_cache_limit=512K
join_buffer_size = 1M
table_open_cache = 116925
open_files_limit = 233850
table_definition_cache = 58863
table_open_cache_instances = 8
lower_case_table_names=0
With this configuration, I wanted MariaDB to use maximum, as long as it is not critical. I wanted to review this configuration, and maybe disable query_cache part, and also adjust InnoDB values. Please give me some recommendations, and also let me know if the swap size is good enough, or maybe need to disable mysql to use swap at all.
Sorry, I don't see much that is exciting here:
Analysis of GLOBAL STATUS and VARIABLES:
Observations:
The More Important Issues:
Lower to the suggested value:
table_open_cache = 10000
tmp_table_size = 200M
max_heap_table_size = 200M
query_cache_size = 0 -- the high value you have can cause mysterious slowdowns
max_connections = 200
thread_cache_size = 20
The I/O setting are pretty for HDD drive; do you have SSD?
There are a lot of SHOW commands -- more than one per second. Perhaps some monitoring tool is excessively agressive?
Why so many GRANTs?
Is this in a Galera cluster?
Details and other observations:
( Key_blocks_used * 1024 / key_buffer_size ) = 48 * 1024 / 128M = 0.04% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size (now 134217728) to avoid unnecessary memory usage.
( table_open_cache ) = 116,660 -- Number of table descriptors to cache
-- Several hundred is usually good.
( Open_tables / table_open_cache ) = 4,439 / 116660 = 3.8% -- Cache usage (open tables + tmp tables)
-- Optionally lower table_open_cache (now 116660)
( innodb_buffer_pool_instances ) = 24 -- For large RAM, consider using 1-16 buffer pool instances, not allowing less than 1GB each. Also, not more than, say, twice the number of CPU cores.
-- Recommend no more than 16. (Beginning to go away in 10.5)
( innodb_lru_scan_depth * innodb_buffer_pool_instances ) = 1,024 * 24 = 24,576 -- A metric of CPU usage.
-- Lower either number.
( 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 / 24 = 0.167 -- innodb_page_cleaners
-- Recommend setting innodb_page_cleaners (now 4) to innodb_buffer_pool_instances (now 24)
(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 ) = 1,065,507 / 1538880 = 69.2% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size (now 25769803776) 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_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 7,641,841,664 / 24576M = 29.7% -- Percent of buffer pool taken up by data
-- A small percent may indicate that the buffer_pool is unnecessarily big.
( innodb_log_buffer_size ) = 96M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions.
-- Adjust innodb_log_buffer_size (now 100663296).
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 141,581 / 60 * 6144M / 2470192128 = 6,154 -- 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 6442450944). (Cannot change in AWS.)
( 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.
( 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.
( 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.
( min( tmp_table_size, max_heap_table_size ) ) = (min( 1024M, 1024M )) / 32768M = 3.1% -- Percent of RAM to allocate when needing MEMORY table (per table), or temp table inside a SELECT (per temp table per some SELECTs). Too high may lead to swapping.
-- Decrease tmp_table_size (now 1073741824) and max_heap_table_size (now 1073741824) to, say, 1% of ram.
( character_set_server ) = character_set_server = latin1
-- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( query_cache_size ) = 512M -- Size of QC
-- Too small = not of much use. Too large = too much overhead. Recommend either 0 or no more than 50M.
( Qcache_hits / (Qcache_hits + Com_select) ) = 8,821 / (8821 + 5602645) = 0.16% -- Hit ratio -- SELECTs that used QC
-- Consider turning off the query cache.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (512M - 48787272) / 224183 / 16384 = 0.133 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size (now 16384)
( tmp_table_size ) = 1024M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size (now 1073741824) to avoid running out of RAM. Perhaps no more than 64M.
( Com_admin_commands / Queries ) = 888,691 / 6680823 = 13.3% -- Percent of queries that are "admin" commands.
-- What's going on?
( Slow_queries / Questions ) = 438,188 / 6557866 = 6.7% -- Frequency (% of all queries)
-- Find slow queries; check indexes.
( log_queries_not_using_indexes ) = log_queries_not_using_indexes = ON -- Whether to include such in slowlog.
-- This clutters the slowlog; turn it off so you can see the real slow queries. And decrease long_query_time (now 2) to catch most interesting queries.
( Uptime_since_flush_status ) = 451 = 7m 31s -- How long (in seconds) since FLUSH STATUS (or server startup).
-- GLOBAL STATUS has not been gathered long enough to get reliable suggestions for many of the issues. Fix what you can, then come back in a several hours.
( Max_used_connections / max_connections ) = 25 / 500 = 5.0% -- Peak % of connections
-- Since several memory factors can expand based on max_connections (now 500), it is good not to have that setting too high.
( thread_cache_size / Max_used_connections ) = 500 / 25 = 2000.0%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.
Abnormally small:
Innodb_dblwr_pages_written / Innodb_dblwr_writes = 2.28
aria_checkpoint_log_activity = 1.05e+6
aria_pagecache_buffer_size = 128MB
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
performance_schema_max_mutex_classes = 0
query_cache_limit = 524,288
Abnormally large:
Acl_column_grants = 216
Acl_database_grants = 385
Acl_table_grants = 1,877
Innodb_buffer_pool_pages_free = 1.07e+6
Innodb_num_open_files = 9,073
Memory_used_initial = 8.16e+8
Open_table_definitions = 4,278
Open_tables = 4,439
Performance_schema_file_instances_lost = 1,732
Performance_schema_mutex_classes_lost = 190
Performance_schema_table_handles_lost = 570
Qcache_free_blocks = 9,122
Qcache_total_blocks = 457,808
Tc_log_page_size = 4,096
Uptime - Uptime_since_flush_status = 141,130
aria_sort_buffer_size = 256.0MB
auto_increment_offset = 3
gtid_domain_id = 12,000
innodb_open_files = 116,660
max_heap_table_size = 1024MB
max_relay_log_size = 1024MB
min(max_heap_table_size, tmp_table_size) = 1024MB
performance_schema_events_stages_history_size = 20
performance_schema_events_statements_history_size = 20
performance_schema_events_waits_history_size = 20
performance_schema_max_cond_classes = 90
table_definition_cache = 58,863
table_open_cache / max_connections = 233
tmp_memory_table_size = 1024MB
wsrep_cluster_size = 3
wsrep_gtid_domain_id = 12,000
wsrep_local_bf_aborts = 107
wsrep_slave_threads = 32
wsrep_thread_count = 33
Abnormal strings:
aria_recover_options = BACKUP,QUICK
disconnect_on_expired_password = OFF
gtid_ignore_duplicates = ON
gtid_strict_mode = ON
histogram_type = DOUBLE_PREC_HB
innodb_fast_shutdown = 1
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
opt_s__optimize_join_buffer_size = on
optimizer_trace = enabled=off
use_stat_tables = PREFERABLY_FOR_QUERIES
wsrep_cluster_status = Primary
wsrep_connected = ON
wsrep_debug = NONE
wsrep_gtid_mode = ON
wsrep_load_data_splitting = OFF
wsrep_provider = /usr/lib64/galera-4/libgalera_smm.so
wsrep_provider_name = Galera
wsrep_provider_options = base_dir = /var/lib/mysql/; base_host = FIRST_NODE_IP; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 2; evs.version = 1; evs.view_forget_timeout = P1D; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = galera.cache; gcache.page_size = 128M; gcache.recover = yes; gcache.size = 1024M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.listen_addr = tcp://0.0.0.0:4567; gmcast.mcast_addr = ; gmcast.mcast_ttl = 1; gmcast.peer_timeout = PT3S; gmcast.segment = 0; gmcast.time_wait = PT5S; gmcast.version = 0; ist.recv_addr = FIRST_NODE_IP; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.linger = PT20S; pc.npvo = false; pc.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 10; socket.checksum = 2; socket.recv_buf_size = auto; socket.send_buf_size = auto;
wsrep_provider_vendor = Codership Oy
wsrep_provider_version = 26.4.4(r4599)
wsrep_replicate_myisam = ON
wsrep_sst_auth = ********
wsrep_sst_method = mariabackup
wsrep_start_position = 353e0616-cb37-11ea-b614-be241cab877e:39442474