1

I have a MariaDB Galera 3-Node Cluster as the DB system but since I built, it is working with the default parameters. I only changed the parameter as max_connections=3000. It has been about 6 months that the system is live with a r/w connection traffic about 100-500 connections per seccond, but the problem is one of the nodes goes down and doesn't serve until I restart it's MariaDB service. It happens 1-10 times for a week, so as you understand I am like a baby sitter for the database.

Since I am not an database expert(I am an Java developer) but it has been assigned to me to build an database system, I could not build an reliable and high performance system with default parameters. After some research, I found to update and add some parameters to the my.cnf configuration file for a better performance but I am not sure about the values. Before I update that, I want to ask there to database experts. Please share your knowledge with me.

The Database Server's properties are:

  • Ram: 8gb (Can not be higher)
  • HDD: 500GB (Can not be higher or updated as SDD)
  • CPU: 8 Core (only 1 cpu)
  • OS: Ubuntu Server 20.04
  • MariaDB 10.4.21
  • Cluster size: 3 Node, MASTER-MASTER Replication, so there are 3 masters

There is no a load balancer like HAProxy, it was not allowed to use a load balancer as an extra server. I only configured Keepalived on Node1 and Node2 and it routes all connections to the Node1 when it is available, otherwise if Node1 is not available or reachable it routes to the Node2. So Node1 is primary, Node2 is secondary servant. Node3 is not used by the users, it is pasif and I start the cluster from this node with galera_new_cluster.

All my.cnf file parameters are as the defaults. I only updated and added the parameters which mentioned below. I want help about these parameters and their values if they are good enough for my system or not:

I updated these parameters: (parameter_name = defaultValue -> updatedValueByMe)

max_connections = 100 -> 3000
innodb_buffer_pool_size = 256M -> 5G
innodb_log_buffer_size = 8m -> 128M
innodb_io_capacity = 400 -> 4000

I also added these new parameters:

[mysqld]
innodb_read_io_threads = 8
innodb_write_io_threads = 6
innodb_log_file_size = 512M
thread_handling = pool-of-threads
thread_stack = 128K
thread_pool_size = 8
thread_pool_oversubscribe = 3

[galera]
wsrep_slave_threads = 6
wsrep_provider_options = "gcache.size=1G;gcache.page_size=1G;"

I also want to share my galera configurations on Node1. It may help to understand the system better:

[galera]
binlog_format=ROW
default-storage-engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

wsrep_cluster_name="XCluster"
wsrep_cluster_address="gcomm://10.0.0.1,10.0.0.2,10.0.0.3"

wsrep_sst_method=mariabackup
wsrep_sst_auth=sstuser:sstuser

wsrep_node_address="10.0.0.1"
wsrep_node_name="Node1"

Are all values right or is there any unusual values you noticed?

Improve this question
7
  • Some more information on "goes down" would be appreciated. Like the logs for that node at the time and/or show global status, if it is available. FYI 10.4.22 with a number of Galera fixes (not necessary yours) is out any day now.
    – danblack
    Commented Nov 4, 2021 at 1:57
  • @danblack I looked at syslog but nothing found about why the db was crushed, just saying the connections are aborted, no more information. I could not find why it goes down and why there is no any useful logs about that.
    – TarabydaVllasıCafcaflıAtArabsı
    Commented Nov 4, 2021 at 5:13
  • If it crashed or when down, the systemd or mysqld_safe would restart, showing that in the logs. If its just aborted connections it might be that the maximum is reached. Consider slow query logs, it could just be bad query plans stack up on connections. You probably can't handle 3000. I've previously found the flow control on Galera extremely conservative and that tuning to avoid flow control to be a good tradeoff. Looking forward to seeing show global status after a non-trivial amount of uptime.
    – danblack
    Commented Nov 4, 2021 at 5:24
  • @danblack As I checked the connections have never reached the max size 3k. I think it is not the problem. What configurations did you do about flow control? Did it solved the problem?
    – TarabydaVllasıCafcaflıAtArabsı
    Commented Nov 4, 2021 at 6:47
  • The configuration was for a reasonably highly written cluster (with small writesets) where increasing gcs.fc-limit. Look at the factor and other related settings. With reasonable hardware like you have, pausing everything on 8/16 writesets is can be trigger happy. Look at related global status variables related to fc.
    – danblack
    Commented Nov 4, 2021 at 7:02

1 Answer 1

Reset to default
1 
max_connections = 100 -> 3000      -- probably excessive
innodb_io_capacity = 400 -> 4000   -- probably excessive for HDD
thread_stack = 128K          -- Let the default stand; it depends on the OS
wsrep_slave_threads=0        -- sounds wrong
gcache.page_size=1G          -- Do you have a rationale for this non-default value?

Run SHOW GLOBAL STATUS LIKE 'Max_used_connections'; to see how many connections it actually used (since the last restart of the node). Use this metric to help in deciding on a value for max_connections.

https://mariadb.com/kb/en/galera-cluster-system-variables/#wsrep_slave_threads says (for wsrep_slave_threads):

  • 0 is not valid
  • 1 is safe
  • you could try a small number

Leave innodb_io_capacity at 400 for HDD.

Improve this answer
3
  • thank you, you are answering all my questions :) max_used_connections is about over 2k. what should I make innodb_io_capacity for 500gb HDD? wsrep_slave_threads is about replication connections as I understand and the recommended was as many as core size, the default is 1. I dont have a sensible reason to make gcache.page_size=1G, but I saw that in google for a similar system settings, I don't know what is the optimal.
    – TarabydaVllasıCafcaflıAtArabsı
    Commented Nov 3, 2021 at 6:41
  • @TarabydaVllasıCafcaflıAtArabsı - I addressed some of your questions in my Answer; I don't have a good answer for page_size.
    – Rick James
    Commented Nov 3, 2021 at 22:22
  • page_size, had a bit of a look through the code. It looks similar to innodb_buffer_pool_chunk_size in that its the increments on which sizing changes. Unlikely to have any effect at all on performance. wsrep_slave_threads of 8 sounds like a good choice for your hardware.
    – danblack
    Commented Nov 4, 2021 at 7:09

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.