High CPU Usage on Fog Server

[Sebastian Roth]

@DJslimD1k After setting the config did you restart the MySQL/MariaDB?

[george1421]

@DJslimD1k Run the following command on the mysql server console and post the results SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;

[DJslimD1k]

@george1421 Still no luck. Here is the screenshot you requested.

[george1421]

@DJslimD1k Ok this specifically is the problem.

So your current FOG server OS is centos 8? We need to find which file the stanza of [mysqld] is located and add in

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

and then restart mysql/mariadb. When we get it right those parameters should be gone from the query.

[george1421]

@DJslimD1k First/accepted answer here: https://stackoverflow.com/questions/16603947/mysql-mode-on-linux-centos As to the location where that parameter needs to be set.

[george1421]

@DJslimD1k I have a Centos 8 fog server in my home lab and this is what I had to do.

Edit this file
/etc/my.cnf.d/mysql-server.cnf

Add this in just below the [mysqld] section header. NOTE: This is a different sql-mode values than I gave you before. Those did not seem to work on the mysqld that centos uses. I queried the values that mysqld used and then just removed the date invalid and zero switches.

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

The config file should look like this:

[mysqld]
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

Restart mysqld
systemctl restart mysqld

Then query using mysql untility to ensure that the date settings are removed as in the picture from the previous post. Once that is done you should be able to convert the remaining tables to innodb format.

[DJslimD1k]

@george1421 I had to change the permissions of that file in order for me to add the text into that file. Now when I log into fog, I get this message.

This is the result.

[george1421]

@DJslimD1k Well its because you changed the permissions on that file. You should have edited it with root user or sudo edited the file. Change the mode back to 755 sudo chmod 755 /etc/my.cnf.d/mysql-server.cnf Then restart mysql server.

[DJslimD1k]

@george1421 I believe I got it! The overall processor usage has dropped, but I still see a lot of php-fpm in the “top” view in terminal. Thank you so much for your help!

I do have the access control plugin installed on our fog server. I figured that’s what the remaining MyISAM are from.

[george1421]

@DJslimD1k the mysql value is still pretty high. Higher than I might expect. But switching from isam to innodb helps because isam uses table locking on update and innodb uses row locking. With a lot of clients polling that table locking method becomes a road block.

I didn’t look back in the tread, but didn’t your fog server have 10 vCPUs? If so I would drop those back to 4 and see where performance settles out to. Overcommiting on vCPU is worse then not providing enough vCPUs to handle the load.

Having that many php-fpm workers just means that your server is busy with requests. There is some performance tuning you can do with the php-fpm settings on a busy network by switching from dynamic to allocation to static allocation.