MySQL high CPU usage, MySQL process high server load

MySQL high CPU usage, MySQL process high server load


 

High number of mySQL connections is one of the reason for high server load on Linux Servers. Do not restart httpd or mySQL service if there is high load on the server, because you will not be able to find the exact reason why server load was high. Most of the server administrators kill apache and restart the services to reduce the server load. This is not a permanent fix because the issue might happen after few hours. Do the below steps immediately to find out what is causing High mySQL cpu usage.

Run “top” command on the server and check the current CPU usage. Check the 3rd row in the top command output to know the current CPU usage.
 


 

A. CHECK MySQL PROCESSLIST

 
1. Log into Linux server via SSH as ‘root’ user

2. Run the command “mysqladmin proc stat” to check the mySQL process list

You can run any of the below commands to check mySQL processlist

[root@server ~]# mysqladmin proc stat

OR

[root@server ~]# mysqladmin processlist

OR

[root@server ~]# mysql

mysql> SHOW FULL PROCESSLIST;

 
An example is given below :

+——–+—————–+—————–+—————–+———+——+——-+———————–+
| Id | User | Host | db | Command | Time | State | Info |
+——–+—————–+—————–+—————–+———+——+——-+———————–+
| 294 | eximstats | localhost | eximstats | Sleep | 8 | | NULL |
| 78132 | dgftyu | localhost | dgftyu | Sleep | 0 | | NULL |
| 193997 | root | localhost | mysql | Sleep | 183 | | NULL |
| 194309 | root | localhost | NULL | Query | 0 | NULL | SHOW FULL PROCESSLIST |
| 194313 | wordpres_srvers | localhost | wordpres_srvers | Sleep | 0 | | NULL |
| 194313 | wordpres_srvers | localhost | wordpres_srvers | Sleep | 0 | | NULL |
| 194313 | wordpres_srvers | localhost | wordpres_srvers | Sleep | 0 | | NULL |
| 194313 | wordpres_srvers | localhost | wordpres_srvers | Sleep | 0 | | NULL |
| 194313 | wordpres_srvers | localhost | wordpres_srvers | Sleep | 0 | | NULL |
| 194313 | wordpres_srvers | localhost | wordpres_srvers | Sleep | 0 | | NULL |
| 194313 | wordpres_srvers | localhost | wordpres_srvers | Sleep | 0 | | NULL |
| 194313 | wordpres_srvers | localhost | wordpres_srvers | Sleep | 0 | | NULL |
| 194313 | wordpres_srvers | localhost | wordpres_srvers | Sleep | 0 | | NULL |
| 194313 | wordpres_srvers | localhost | wordpres_srvers | Sleep | 0 | | NULL |
+——–+—————–+—————–+—————–+———+——+——-+———————–+

On cPanel servers you can view mySQL processlist through WHM. Login to WHM and click on “Show MySQL Processes” under “SQL Services”

Home » SQL Services » Show MySQL Processes
 
mySQL processlist cpanel
 
On the above linux server you can see that there are many mySQL process under the database “wordpres_srvers” in sleep state. There were more than 20 processes (I have cropped the above mySQL output)
 


 

B. Check whether there is any Brute force attack or DOS attack

 
mySQL will use high server resources if there is high traffic/hits/attacks to the website. Check the total number of IP address connections to the server using netstat command. Make sure the hits to the website is from legitimate IP addresses, it might be an attack if the server load suddenly went high. Domain access logs will help us to find whether there is attack on any of the domains.

Access_log of websites can be enabled in the webserver configuration file. You must edit domain virtual host section in apache configuration file to enable access_log of the website.

1. Log into server via SSH as ‘root’ user

2. Check the access log of the website

on cPanel servers access logs of website is saved in the directory /home/user/access-logs.
 

cd /home/user/access-logs

tail -f website.com

192.168.2.5 – – [28/Feb/2017:13:57:32 +0100] “POST /administrator/index.php
192.168.2.50 – – [28/Feb/2017:13:57:32 +0100] “POST /administrator/index.php
192.168.8.55 – – [28/Feb/2017:13:57:32 +0100] “POST /administrator/index.php
192.168.8.59 – – [28/Feb/2017:13:57:32 +0100] “POST /administrator/index.php
192.168.8.52 – – [28/Feb/2017:13:57:32 +0100] “POST /administrator/index.php
192.168.8.57 – – [28/Feb/2017:13:57:32 +0100] “POST /administrator/index.php
192.168.8.61 – – [28/Feb/2017:13:57:32 +0100] “POST /administrator/index.php
192.168.8.62 – – [28/Feb/2017:13:57:32 +0100] “POST /administrator/index.php

tail -f website2.com

192.168.9.2 – – [28/Feb/2017:13:57:32 +0100] “POST /wp-login.php
192.168.9.4 – – [28/Feb/2017:13:57:32 +0100] “POST /wp-login.php
192.168.9.5 – – [28/Feb/2017:13:57:32 +0100] “POST /wp-login.php
192.168.9.8 – – [28/Feb/2017:13:57:32 +0100] “POST /wp-login.php
192.168.9.9 – – [28/Feb/2017:13:57:32 +0100] “POST /wp-login.php
192.168.9.11 – – [28/Feb/2017:13:57:32 +0100] “POST /wp-login.php

 
On the above server you can see small brute force attack on the joomla and wordpress websites. This might be the reason why mySQL is using high server resources. To stop these attacks you must install a firewall on the server, enable mod_security web application firewall and enable any free firewall plugins for the site.
 


 

C. Reduce mySQL timeout values

 
On many shared servers I have noticed persistent mySQL idle connections but there was no hits in the website logs. MySQL might have finished the query but it is still not closed. Persistent mySQL connection in sleep state might lead to high mySQL resource usage so you must modify the script and close the connection immediately after it is completed.

Reduce the interactive_timeout and wait_timeout values in mySQL settings. Do not set these timeout values to a very low value because you will frequently see timeout error on the site.

Edit /etc/my.cnf and reduce the below values
 

vi /etc/my.cnf

wait_timeout=300
interactive_timeout=300

 
Restart mysql service (or reload) after entering the above timeout values. Try increasing the values if you see any

Command to restart mySQL service : service mysql restart
 


 

D. Run mySQLtuner

 
mySQL might use high server resources if it is not tuned correctly. You can download mySQltuner from the below url and it will show the mySQL variables that should be adjusted on the server.
 

cd /usr/local/src
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl
perl mysqltuner.pl

 
Download the mySQLtuner script from githib website, set executable permission for the mySQLtuner perl script and execute the mysqltuner.pl script. It might take few minutes to run the script.
 


 

E. Enable slow query log in MySQL

 
Enable slow query log in MySQL to check which all database queries are taking long time. Coding mistakes in your website will lead high server load. Some database queries take long time to run due to coding issues and it will lead to high mySQL resource usage. The below command will show whether slow_query is enabled or not.
 

mysqladmin variables | grep slow_query

| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/hostname-slow.log

 
slow_query_log is not enabled on the above linux server. Enable slow_query_log and monitor the slow queries. From the slow query logs and you can find out the database queries that is taking long to execute.

set global slow_query_log = ‘ON’; #This will enable slow_query_log

In wordpress websites poorly coded plugins are one of the reason for high resource usage.