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

BookMarc

Registered
Apr 7, 2005
3
0
151
Hello Everyone,

I have a dedicated server with a few domains.

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

[[email protected] 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

Well-Known Member
Jul 23, 2004
970
35
178
cPanel Access Level
Root Administrator
Perhaps your 'open files limit' of 10000 is too low? login to MySQL from a terminal and run
Code:
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 ;)
 

sahostking

Well-Known Member
May 15, 2012
343
5
68
Cape Town, South Africa
cPanel Access Level
Root Administrator
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

Member
PartnerNOC
Jan 3, 2003
8
1
153
Download mysqltuner.pl and run it, you can use it to optimize your existing MySQL configuration.