Uneven CPU load on cluster

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

I have 3 identical VMs in a PXC load balanced using round robing through HAProxy. All nodes get about the same number of connections, but CPU load is considerably higher on one of them.

Node1: (First node - others connected to it)


top - 23:24:32 up 3 days, 4:41, 1 user, load average: 0.03, 0.12, 0.13
Tasks: 91 total, 1 running, 90 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.5%us, 0.5%sy, 0.0%ni, 98.9%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 7150256k total, 7017600k used, 132656k free, 139904k buffers
Swap: 3906556k total, 0k used, 3906556k free, 3446316k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
23924 mysql 20 0 10.4g 3.2g 58m S 8.6 46.3 589:17.90 mysqld

Node2:


top - 23:25:25 up 3 days, 1:39, 1 user, load average: 1.12, 1.18, 1.12
Tasks: 91 total, 1 running, 90 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 7150256k total, 7033116k used, 117140k free, 140468k buffers
Swap: 3906556k total, 0k used, 3906556k free, 5344364k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7633 mysql 20 0 8905m 1.3g 54m S 2.0 19.3 5:53.03 mysqld

Node3:


top - 23:25:53 up 2 days, 6:15, 1 user, load average: 0.15, 0.15, 0.14
Tasks: 91 total, 1 running, 90 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 7150256k total, 4575792k used, 2574464k free, 154580k buffers
Swap: 3906556k total, 0k used, 3906556k free, 2929832k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
18168 mysql 20 0 8270m 1.3g 57m S 2.0 19.4 5:09.12 mysqld

I counted number of connections with netstat and it shows approximately the same # for all nodes.

netstat -a|grep sql|wc -l

I’m not even sure where to begin. Any ideas?

2

Not all connections are created equally – the most likely thing is you have one connection doing a lot of extra work and it happens to be connected to node2. Are you using persistent or non-persistent connections? If you fail node2 out of haproxy and kick the connections off, does the load stay on node2, go to another node [, or vanish]?

3

Makes sense. If that node is removed, load goes to another node. I’m not sure how our CMS software handles mysql connections, but judging by number of sessions in HAProxy, they are definitely recycled.