How to troubleshoot high load in Linux web hosting servers?

[Hugo E.]

I have a VPS 2 Cores and 4GB of RAM but sometimes my websites got high load, I installed ConfigServer Security and Firewall (CSF) on my CentOS 6 but doesn't have any IPs to blocks. I doubted some attacked into my apache or mysql server which cause high load on whole VPS.

I typed top on my SSH and its result as follows

[root@hugo ~]# top
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 6539 mysql     20   0 1773m 491m 7004 S 47.2  6.4   4352:42 mysqld
11131 apache    20   0  314m  23m  13m S  8.3  0.3   0:01.21 httpd
10343 hugo  20   0  320m  25m  11m R  3.3  0.3   0:02.53 httpd
11157 hugo  20   0  320m  24m  10m S  3.3  0.3   0:00.49 httpd
10344 hugo  20   0  320m  25m  11m R  3.0  0.3   0:02.43 httpd
11135 hugo  20   0  318m  23m  10m S  2.7  0.3   0:00.45 httpd
 9494 apache    20   0  317m  38m  26m S  2.3  0.5   0:04.48 httpd
 9938 apache    20   0  413m  26m  13m S  2.3  0.3   0:02.60 httpd
 9973 hugo  20   0  320m  38m  24m R  2.3  0.5   0:03.00 httpd
10036 apache    20   0  318m  27m  14m S  2.3  0.4   0:02.55 httpd
10321 hugo  20   0  319m  32m  18m R  2.3  0.4   0:02.55 httpd
10621 hugo  20   0  320m  32m  18m R  2.3  0.4   0:01.93 httpd
10636 hugo  20   0  320m  26m  12m R  2.3  0.4   0:01.90 httpd
10638 hugo  20   0  319m  30m  17m R  2.3  0.4   0:01.67 httpd
10871 hugo  20   0  320m  26m  12m R  2.3  0.4   0:01.05 httpd
10876 hugo  20   0  319m  26m  12m R  2.3  0.3   0:01.08 httpd
11136 hugo  20   0  319m  26m  12m R  2.3  0.3   0:00.57 httpd
11156 hugo  20   0  319m  27m  13m R  2.3  0.4   0:00.48 httpd
11279 hugo  20   0  319m  21m 8036 R  2.3  0.3   0:00.11 httpd
 8899 hugo  20   0  320m  40m  25m R  2.0  0.5   0:05.37 httpd
 9846 hugo  20   0  320m  31m  17m R  2.0  0.4   0:03.26 httpd

Can anyone tell me how to solve high load problem on my Linux server or how to track it?

 

[Rob Whisonant]

Here is a good article that goes through the steps to locate the problem. See if [URLnf="https://bobcares.com/blog/how-to-troubleshoot-high-load-in-linux-web-hosting-servers/"]this[/URLnf] helps.

 

[virtubox]

To monitor you server properly, you can install netdata :

yum install zlib-devel libuuid-devel libmnl-devel gcc make git autoconf autoconf-archive autogen automake pkgconfig curl
yum install jq nodejs

# download it - the directory 'netdata' will be created
git clone https://github.com/firehol/netdata.git --depth=1
cd netdata

# build it, install it, start it
./netdata-installer.sh

You will have access to a clean monitoring dashboard at http://Your-server-IP:19999

 

[webdesign]

webdesign

This could monitor hosting server but could not solve high load problem as the OP is asking.
I suppose that @Hugo needs to find error log files to analyze what made his server high load.

 

[virtubox]

virtubox

Yes, but that's a first step to see if there is another process with high-load, because php should use more CPU than MySQL.
But you will have to import mysql slow-log and to analyze them to see what is the cause of your issue.

 

[Rob Whisonant]

What scripts that access mySQL are you running on the server?

Also have you tried just restarting mySQL?

 

[UWH-David]

Considering the highest load is coming from mySQL, why not use mtop? You can go a step further with Perl mySQL Tuner. If it is a particular script eating up mySQL make sure you have caching enabled where possible. opcache or memcached at the PHP level can also be a useful option.

 

[Hugo E.]

Yes, but that's a first step to see if there is another process with high-load, because php should use more CPU than MySQL.
But you will have to import mysql slow-log and to analyze them to see what is the cause of your issue.

What scripts that access mySQL are you running on the server?

Also have you tried just restarting mySQL?

Considering the highest load is coming from mySQL, why not use mtop? You can go a step further with Perl mySQL Tuner. If it is a particular script eating up mySQL make sure you have caching enabled where possible. opcache or memcached at the PHP level can also be a useful option.

Yes I installed mysql slow queries log and knew there is a query made mysql run slowly, query execute time over 12 seconds, but I could not fix that query because it is getting results from multi rows. I guess If i change to mariadb, it can handle the problem?

 

[virtubox]

Mariadb is faster than MySQL but it will not fix your problem. You have to use a caching system to make less requests to MySQL. Do you use a custom php script or a CMS ?

 

[Rob Whisonant]

Rob Whisonant

You may know the answer to this. Would increasing the RAM help him speed mySQL queries up?

 

[virtubox]

Only if you increase the mysql cache size. But MySQL is not an optimized database software and it's for this reason we have to use a caching system with almost all our softwares.

 

[Hugo E.]

You may know the answer to this. Would increasing the RAM help him speed mySQL queries up?

I don't think so, RAM doesn't help solving queries problems.

Mariadb is faster than MySQL but it will not fix your problem. You have to use a caching system to make less requests to MySQL. Do you use a custom php script or a CMS ?

Yes I use a custom script for my site. I am contacting my web developer but he has no idea to solve it since it was his codes.
It was a query to choose some records from thousands of records in the database with join commands between tables in a database, it caused high load for my MySQL server and httpd.

 

[UWH-David]

UWH-David

MariaDB is always an excellent choice due to speed and backward compatibility. Have you tried memcached? Have you tuned your my.cnf file? That will help tremoundsly in the interim:
http://mysqltuner.com/