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.