0

I'm having issues recently with my galera cluster getting desync after multiple lock wait timeouts, is there any way I can improve my galera cluster configuration to prevent more lock wait timeouts from happening?

Here's each of my galera nodes run on 4-cores CPU and 64GB of memory, and I feel like I'm not utilizing those resources properly.

Here's my current MariaDB my.cnf

[mysqld]
datadir=/var/lib/mysql-data
socket=/var/lib/mysql-data/mysql.sock

proxy-protocol-networks=10.10.10.10, 10.10.10.20, 10.10.10.30

innodb_lock_wait_timeout=60
innodb_io_capacity=2000
innodb_buffer_pool_size=52G
innodb_buffer_pool_instances=52
innodb_log_buffer_size=256M
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=2
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_rollback_on_timeout=1

innodb_read_io_threads=8
innodb_write_io_threads=4
innodb_purge_threads=1

thread_handling = pool-of-threads
thread_stack = 192K
thread_cache_size = 4
thread_pool_size = 8
thread_pool_oversubscribe = 3

performance_schema = ON
event_scheduler = ON

max_allowed_packet=256M
max_connections=3000

We have at least 20 apps using the cluster and I think my current configuration is not cutting out for it. Any tips on what variables to increase?

4
  • How far apart (ping time) are the nodes? Let's see a query involved in the timeout. (These, and possible other, questions will help us figure out what direction to look.)
    – Rick James
    Jul 23, 2022 at 13:54
  • Hi @RickJames, I pinged the other nodes from the first node and got these results rtt min/avg/max/mdev = 0.148/0.230/0.548/0.099 ms, rtt min/avg/max/mdev = 0.162/0.321/0.703/0.155 ms. The last query was an from event scheduler [mall_traffic.populate_traffic_data] Lock wait timeout exceeded; try restarting transaction
    – Glen G
    Jul 25, 2022 at 0:46
  • OK, it is not network latency. Now we need to see what is inside the transaction. lock_wait_timeout has an excessively high default if 50 seconds; I do not recommend raising it -- instead work on speeding up the transaction.
    – Rick James
    Jul 26, 2022 at 16:09
  • I'm having suspicions that it was related to the events occurring on each node together, I have disabled the events on the 2/3 of the nodes and let a single node handle the events instead. I'm still monitoring the performance if it occurs again. Though I'm not sure if there are limitations regarding events when using the galera cluster.
    – Glen G
    Jul 28, 2022 at 1:44

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.