I have implemented a cluster include three nodes: two stored nodes and one garbd (also haproxy - load balancer).
Some information about servers: SSD 480G Raid1 Memory 32G Swap 16G 2xCPU, number of cores: 24 Maria Galera Cluster 10.0.14
mysqld process consume CPU percent so much 229.3% - 300%
The highes CPU load 20-30.
When CPU load grow up, there is some log like that.
*** Victim TRANSACTION: TRANSACTION 17362851, ACTIVE 5 sec mysql tables in use 1, locked 1 809 lock struct(s), heap size 95784, 149905 row lock(s), undo log entries 19493 MySQL thread id 182155, OS thread handle 0x7fc587451700, query id 1566816, query end *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 180 page no 764 n bits 288 index `PRIMARY` of table `xxx`.`yyy` trx id 17362851 lock_mode X 141128 10:36:45 [Note] WSREP: cluster conflict due to high priority abort for threads: 141128 10:36:45 [Note] WSREP: Winning thread: THD: 22, mode: applier, state: executing, conflict: no conflict, seqno: 1447458 SQL: (null) 141128 10:36:45 [Note] WSREP: Victim thread: THD: 182155, mode: local, state: committing, conflict: no conflict, seqno: -1
I don't know why galera get cert fail because I am using master-slave. There is only node receiving writeset from clients.
Some solution I am applying:
set wsrep_auto_increment_control=OFF on two nodes set query_cache_size=1G Decrease wsrep_slave_thread from 48 to 24
Now, my CPU load is in range 2.00-3.00 I am waiting and observing. What can I do for better performance. I am mangaing use thread pool for decrease CPU percent usage of mysqld.