MariaDB high CPU usage in OpenStack Pike

Testing OpenStack Pike after Packstack based deployment I realised MySQL daemon was utilizing 100% CPU resources without any specific reason, but I had never faced such problem before in previous OpenStack releases. The problem also has never appeared during my TripleO based OpenStack Pike deployments, so looks like it’s strictly Packstack related issue.

The situation heppened on few bare metal installations on pretty powerful servers. Restarting mariadb service was helpful just for the first few minutes after service restart, then the problem would happen again and again, what resulted in frequent Horizon Dashboard and Keystone inaccessibility and partial Controller unavailability.

[root@controller ~(keystone_admin)]# top
top - 16:31:58 up 13 days,  1:18,  3 users,  load average: 1.67, 1.21, 1.20
Tasks: 1483 total,   2 running, 1480 sleeping,   0 stopped,   1 zombie
%Cpu(s):  1.2 us,  1.9 sy,  0.0 ni, 96.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 26386312+total, 18289169+free, 64736888 used, 16234536 buff/cache
KiB Swap:  4194300 total,  4194300 free,        0 used. 19755446+avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
46256 mysql     20   0 24.771g 211408  12428 R 100.7  0.1   1:55.39 mysqld
 9514 rabbitmq  20   0 16.982g 725156   4200 S   9.2  0.3 181:33.33 beam.smp
28476 ceilome+  20   0 5352952 130320   2780 S   5.6  0.0 105:43.52 ceilometer-agen
12713 cinder    20   0  455364 103428   8692 S   2.0  0.0  37:23.62 cinder-backup
12932 cinder    20   0  629604 126172   2576 S   2.0  0.0  40:17.05 cinder-volume
13764 heat      20   0  448968  97120   8760 S   1.6  0.0  26:14.85 heat-engine
...

At the time of MariaDB high CPU utilization, in MariaDB log file (/var/log/mariadb.log) I could find the following errors:

[root@controller ~(keystone_admin)]# cat /var/log/mariadb/mariadb.log | grep descriptor
2018-01-02 12:09:20 140325927078080 [ERROR] Error in accept: Bad file descriptor
2018-01-02 12:09:20 140325927078080 [ERROR] Error in accept: Bad file descriptor
2018-01-02 12:09:20 140325927078080 [ERROR] Error in accept: Bad file descriptor
2018-01-02 12:09:20 140325927078080 [ERROR] Error in accept: Bad file descriptor
...

It turned out that problem of high CPU usage is a result of too many files open at a time on the Controller’s node (where MariaDB is installed) and the amount of open files exceeds system default limits.

The Fix

To fix the issue we need to increase the limits in three areas on the Controller’s operating system: per-user limits, file descriptor limits, MariaDB open files limits.

Per-user Limits

Increase limits of open files, i.e. up to 600k.
Edit /etc/security/limits.conf file and add the following lines at the bottom of the file:

*         hard    nofile      600000
*         soft    nofile      600000
root      hard    nofile      600000
root      soft    nofile      600000

Logout, login and verify new limits:

[root@controller ~]# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 23141
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 600000
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 23141
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

File Descriptor Limits

Verify current file descriptor limits on Controller’s node:

[root@controller ~]# cat /proc/sys/fs/file-max
586456

Looks like 586456 is a default value for CentOS 7, let’s increase it up to 2000000.
Edit /etc/sysctl.conf file and add the following line:

fs.file-max = 2000000

Load new setting from /etc/sysctl.conf:

[root@controller ~]# sysctl -p

Verify new parameter’s value:

[root@controller ~]# cat /proc/sys/fs/file-max
2000000

MariaDB Limits

To increase MariaDB/MySQL limits on CentOS 7 we need to create .conf file in /etc/systemd/system/mariadb.service.d/ directory:

[root@controller ~]# mkdir /etc/systemd/system/mariadb.service.d/
[root@controller ~]# touch /etc/systemd/system/mariadb.service.d/limits.conf

Now edit limits.conf file and add the following lines:

[Service]
LimitNOFILE=600000

Reload systemd service files:

[root@controller ~]# systemctl daemon-reload

Restart MariaDB service:

[root@controller ~]# systemctl restart mariadb

Now login to MariaDB (on Controller node):

[root@controller system]# mysql -u root

Verify new limits in MariaDB:

MariaDB [(none)]> show variables like 'open_files_limit';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| open_files_limit | 600000 |
+------------------+--------+
1 row in set (0.00 sec)

The above tuning should prevent further MariaDB file descriptor errors, MySQL daemon high CPU utilization issues and bring back normal OpenStack Keystone/Horizon operation.