maria-discuss team mailing list archive

[Cédric Counotte <cedric.counotte@xxxxxxxxxx>]

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"

Attachment: maria_db_sample_queue.png
Description: maria_db_sample_queue.png