How to track high load on my VPS

Hugo E. · Sep 30, 2016

I have a VPS 2 CPUs, 4 GB of RAM and install csf firewall on my vps. It didn't show any problems in csf firewall but my sites are getting high load.

I tried to see with top command and I got the results.

Code:

[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

How can I track high load on my VPS? please share essential SSH commands that help me find out which is making my VPS high load.

Thanks in advance.
Hugo E.

LJSHost · Sep 30, 2016

Hi Hugo

Top is an excellent choice for server monitoring in the results displayed
you can see mysqld (the MySQL) demon is using 47.2% of your cpu's and %6.4 of your RAM.

You can get a more realtime out from top you can run it with the -d switch
top -d 1 (this will refresh the output every second)

So far you have verified the mysql server is causing the load, lets dig a little deeper.

You can indentify what is causing by logging into your mysql server

mysql -u root -p

Use the command

SHOW PROCESSLIST

You will get an output similar to

+----+-------+----------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+----------------+------+---------+------+-------+------------------+
| 27 | root | 127.0.0.1:1704 | NULL | Query | 0 | NULL | show processlist |
| 40 | admin | 127.0.0.1:1725 | db1 | Sleep | 0 | | NULL |
| 41 | root | 127.0.0.1:1726 | db1 | Sleep | 1 | | NULL |
+----+-------+----------------+------+---------+------+-------+------------------+
3 rows in set (0.11 sec)

MySQL in your example has constant load as a query is running which is causing this. Take a look in the ' Time ' field. This
display how long each process has been running in seconds. Most query's should execute with a few seconds so if you can see
a process with value such as 1923 this process has been running way to long and will be causing load.

You can use the following command from the mysql console to kill a process

KILL 41; (this will kill process id 41)

Now check the server load in top again and check if MySQL CPU usage is lower.

Finding out what is causing load on a system is a lengthy subject with many different commands and tools you can use.
I hope this helps with your specific example.

LJSHost · Sep 30, 2016

You will see an improvement switching to MariaDB however I would identify the cause of your problem first.

You should be safe to kill a MySQL process that has been running for a long time.
I assume your database does not have hundreds of tables and a million of records with hundreds of queries every second. If it does not if a query has been running for a long time it' normally means something has gone wrong with it.

MySQL keeps a log of all slow queries in /var/log/mysql/slow-queries.log check your log file location in /etc/my.cnf to confirm.

To enable do the following: Version 5.1.6 and above

1. Enter the MySQL console and run the following command:
set global slow_query_log = 'ON';

2. Enable any other desired options. Here are some common examples:

Log details for queries expected to retrieve all rows instead of using an index:
set global log_queries_not_using_indexes = 'ON'

Set the path to the slow query log:
set global slow_query_log_file ='/var/log/mysql/slow-query.log';

Set the amount of time a query needs to run before being logged:
set global long_query_time = '20';
(default is 10 seconds)

3. Confirm the changes are active by entering the MySQL shell and running the following command:
show variables like '%slow%';

fwh · Oct 1, 2016

As LJSHost suggested, you can enable slow_query_log and set the path for slow_query_log_file directly though MySQL commands

Also, you can add directly to your MySQL configuration file

Put these two lines under section [mysqld]

Code:

slow_query_log=1   #(1 = on, 0 = off)
slow_query_log_file=/var/lib/mysql/mysql-slow-queries.log
long_query_time=5

and restart mysqld

You might want to change log name and its path depends on your server.

In other ways, you can see common MySQL errors log on some control panels

cPanel

Code:

MySQL error log	-> /var/lib/mysql/{SERVER_NAME}.err
MySQL slow query log (if enabled in my.cnf) ->/var/log/slowqueries

Directadmin

Code:

/var/lib/mysql/server.hostname.com.err

Plesk

Code:

/var/log/mysqld.log

How to track high load on my VPS

Hugo E.

Member

LJSHost

Well-known member

Hugo E.

Member

LJSHost

Well-known member

fwh

Administrator

Latest posts New threads

Latest posts

New threads

Latest Hosting Offers New Reviews

Latest hosting offers

Popular Contributors - Past 30 Days

Sponsors

Tag Cloud