Hello all. I hope I'm at the right place to ask this question. I opened a bug here: https://jira.mariadb.org/browse/MDEV-28969, however I was told to use this mailing list.
We have 4 MariaDB servers in a Galera Cluster and it happens that a server has to be restarted (be it for a crash which I have to open a bug for) or maintenance. When that happens, the restarted server is causing huge slow down on the whole cluster, and it lasts for 10 to 30 minutes at the very least! And by huge, I mean huge, we end up with 500 to 800 pending queries on all servers as you can see on attached screenshots I've attached the configuration of any server for reference in case this is the source of the issue. Any way to solve this would be greatly appreciated. Regards, 3C.
# # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] # # * Basic Settings # user = mysql pid-file = /run/mysqld/mysqld.pid basedir = /usr datadir = /mnt/nvme/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql lc-messages = en_US skip-external-locking # Broken reverse DNS slows down connections considerably and name resolve is # safe to skip if there are no "host by domain name" access grants #skip-name-resolve # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 0.0.0.0 # # * Fine Tuning # #key_buffer_size = 128M #max_allowed_packet = 1G #thread_stack = 192K #thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched #myisam_recover_options = BACKUP #max_connections = 100 #table_cache = 64 # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # Recommend only changing this at runtime for short testing periods if needed! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # When running under systemd, error logging goes via stdout/stderr to journald # and when running legacy init error logging goes to syslog due to # /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf # Enable this if you want to have error logging into a separate file log_error = /var/log/mysql/error.log # Enable the slow query log to see queries with especially long duration #slow_query_log_file = /var/log/mysql/mariadb-slow.log #long_query_time = 10 #log_slow_verbosity = query_plan,explain #log-queries-not-using-indexes #min_examined_row_limit = 1000 # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 #max_binlog_size = 100M # # * SSL/TLS # # For documentation, please read # https://mariadb.com/kb/en/securing-connections-for-client-and-server/ #ssl-ca = /etc/mysql/cacert.pem #ssl-cert = /etc/mysql/server-cert.pem #ssl-key = /etc/mysql/server-key.pem #require-secure-transport = on # # * Character sets # # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full # utf8 4-byte character set. See also client.cnf character-set-server = utf8mb4 collation-server = utf8mb4_general_ci # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # Most important is to give InnoDB 80 % of the system RAM for buffer use: # https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size #innodb_buffer_pool_size = 8G # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.5 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.5] [mysql] default-character-set=utf8mb4 [mariadb] max_connections = 5000 analyze_sample_percentage = 0 back_log = 2048 binlog_format = MIXED bulk_insert_buffer_size = 67108864 init-connect = 'SET NAMES utf8mb4' concurrent_insert = ALWAYS eq_range_index_dive_limit = 0 histogram_size = 0 histogram_type = SINGLE_PREC_HB host_cache_size = 279 ignore_db_dirs = lost+found innodb_read_io_threads = 24 innodb_write_io_threads = 24 innodb_adaptive_hash_index = ON innodb_adaptive_max_sleep_delay= 150000 innodb_autoinc_lock_mode = 1 innodb_background_scrub_data_check_interval = 3600 innodb_background_scrub_data_interval = 604800 innodb_buffer_pool_instances = 8 innodb_checksum_algorithm = crc32 innodb_concurrency_tickets = 5000 innodb_flush_method = O_DIRECT innodb_flush_neighbors = 0 innodb_instant_alter_column_allowed = add_last innodb_tmpdir = /var/lib/mysql key_buffer_size = 268435456 key_cache_segments = 4 long_query_time = 0.100000 max_heap_table_size = 52428800 optimizer_search_depth = 0 query_cache_size = 268435456 query_cache_type = ON slave_parallel_mode = conservative slave_transaction_retry_errors = 1213,1205 skip_name_resolve = ON slow_launch_time = 1 slow_query_log = ON slow_query_log_file = /var/log/mysql/mysql-slow.log table_open_cache = 16384 table_open_cache_instances = 16 tcp_nodelay = ON wsrep_sst_method = mariabackup wsrep_sst_auth = mariabackup:xxxxx wsrep_gtid_mode = ON wsrep_gtid_domain_id = 300 log_slave_updates = ON gtid_domain_id = 1003 bind-address = 0.0.0.0 server-id = 3 binlog-format = row expire_logs_days = 1 max_binlog_size = 1024M log_bin = /var/lib/mysql/mysql-bin log_bin_index = /var/lib/mysql/mysql-bin.index innodb_buffer_pool_instances = 64 innodb_buffer_pool_size = 206158430208 innodb_log_buffer_size = 268435456 innodb_log_file_size = 1073741824 innodb_flush_log_at_trx_commit = 2 slave-skip-errors = 1062,1032 [galera] wsrep_cluster_name = "MariaDB Galera Cluster" wsrep_provider = /usr/lib/galera/libgalera_smm.so binlog_format = row default_storage_engine = InnoDB innodb_autoinc_lock_mode = 2 wsrep_slave_threads = 48 bind-address = 0.0.0.0 wsrep_node_address = "xxxxx" wsrep_on = ON wsrep_cluster_address = "gcomm://xxxxx"
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
