Mysql causing high CPU load and causing server/domain to go down

[BookMarc]

Hello Everyone,

I have a dedicated server with a few domains.

CENTOS 6.10
Server version: 5.7.28 MySQL Community Server (GPL)

[root@host mysql]# rpm -qa |egrep -i 'maria|mysql'
mysql-community-libs-5.7.28-1.el6.x86_64
mysql-community-devel-5.7.28-1.el6.x86_64
ea-php70-php-mysqlnd-7.0.33-10.10.3.cpanel.x86_64
cpanel-perl-528-MySQL-Diff-0.60-1.cp1178.noarch
cpanel-mariadb-connector-3.0.3-3.cp1174.x86_64
mysql-community-server-5.7.28-1.el6.x86_64
cpanel-perl-528-DBD-mysql-4.048-1.cp1178.x86_64
mysql-community-common-5.7.28-1.el6.x86_64
cpanel-mysql-5.6.43-2.cp1182.x86_64
cpanel-mysql-libs-5.6.43-2.cp1182.x86_64
ea-php55-php-mysqlnd-5.5.38-47.49.3.cpanel.x86_64
ea-php56-php-mysqlnd-5.6.40-9.9.3.cpanel.x86_64
mysql-community-libs-compat-5.7.28-1.el6.x86_64
cpanel-mariadb-connector-devel-3.0.3-3.cp1174.x86_64
mysql57-community-release-el6-11.noarch
ea-php71-php-mysqlnd-7.1.33-1.1.2.cpanel.x86_64
mysql-community-client-5.7.28-1.el6.x86_64


======== The below mysql stuff consumes over 200% of the CPU. and is causing the server and domains to slow down and worse totally unavailable

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=host.xxxxxxxxxxxx.com.err --open-files-limit=10000 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

==============

Please could someone help me point me in the right direction?

Regards,

 

[kernow]

Perhaps your 'open files limit' of 10000 is too low? login to MySQL from a terminal and run

SHOW GLOBAL STATUS LIKE 'Open_files';

To check the number of currently open files. If its maxed out then adjust the limit in my.cnf and restart MySQL.
You might want to consider upgrading both MySQL and your php versions

 

[cPanelLauren]

Hello,

I'd also take a look at the following thread: Tutorial - Troubleshooting high server loads on Linux servers

 

[sahostking]

run the following:

watch mysqladmin processlist

and monitor to see if its one db causing the issue using too much resources (CPU)

alternatively ensure you have limites in place in your /etc/my.cnf file for per user limits for eg.

max_connections = 250
max_user_connections = 30

This ensures that no website can use 30 connections at any given time. Up it or lower it depending on what is required. Your choice.

But something usually causes high cpu so find out which site is maybe being attacked - usually wordpress sites are hit to their wp-admin page sometimes that could cause this aswell. In that case ensure you have good modsec rulesets in place.

 

[RoseHosting]

Download mysqltuner.pl and run it, you can use it to optimize your existing MySQL configuration.