High CPU usage on MariaDB Galera nodes

Question

I have setup 3 cluster mariaDB using Galera for my web services which load balanced with haproxy.

The cluster are working great and sync each other but when visitor on my website reach more than 3000 user, the CPU for each node are peak to high.

top - 09:19:03 up 17:36,  1 user,  load average: 3.58, 3.82, 3.85
Tasks: 169 total,   1 running, 168 sleeping,   0 stopped,   0 zombie
%Cpu(s): 17.8 us,  3.5 sy,  0.0 ni, 77.5 id,  0.3 wa,  0.0 hi,  0.5 si,  0.6 st
KiB Mem : 12300340 total,  9661112 free,   551008 used,  2088220 buff/cache
KiB Swap:   524284 total,   524284 free,        0 used. 11554364 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                
 4906 mysql     20   0 1370720 556596 153360 S 130.3  4.5   1052:00 mysqld                                                                                                                 
 7037 root      20   0  144480   8572   7160 S   0.7  0.1   0:00.08 sshd                                                                                                                   
   34 root      20   0       0      0      0 S   0.3  0.0   0:04.96 ksoftirqd/5                                                                                                            
 2102 root       0 -20       0      0      0 S   0.3  0.0   0:04.95 kworker/4:1H                                                                                                           
 3651 root      20   0  246492  20868  20156 S   0.3  0.2   0:04.33 rsyslogd

All 3 node's are same spec which is :

192GB DISK
6 CPU Cores
12 Gig RAM

What tweak do I need to implement now to handle this query request more efficently? The select request are accessing couple of billion rows in my database table which requested by more than 2000 at same time. Each query result are unique.

This output does not show a system with excessively high CPU usage. — Michael Hampton, Dec 19 '16 at 09:35
Maybe you should optimize the sql query or plan to use a cache system (memcached, reddit, etc). — lg., Dec 20 '16 at 08:28

score 0 · Answer 1 · answered Nov 19 '21 at 11:42

I have the same issues.

Check for 'slow queries'. That is priority 1. Don't think there are none, there is always something to find.
use SSD disks
Here are my settings (Most imporant once)

[galera]
innodb_autoinc_lock_mode=2
wsrep_provider_options=gcache.size=512M;gcache.recover=yes

wsrep_causal_reads=ON
wsrep_slave_threads=24
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=1
query_cache_size=512M

[mysqld]
transaction-isolation = READ-COMMITTED
max_allowed_packet=200M
max_allowed_packet=200M
innodb_buffer_pool_size=1G
skip_name_resolve
table_open_cache=500

High CPU usage on MariaDB Galera nodes

1 Answers1