How to track high load on my VPS

Hugo E.

Member
Registered
Joined
Sep 8, 2014
Messages
62
Points
0
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

Well-known member
Hosting Provider
Registered
Joined
Jul 5, 2016
Messages
1,031
Points
63
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.
 

Hugo E.

Member
Registered
Joined
Sep 8, 2014
Messages
62
Points
0
Hugo E.
I read completely your post and not missed a word, from your explanation I can know Mysql is top reason causes high load on my VPS, so what is solution for it? should I change Mysql to Mariadb to decrease loading for my database and solve this problem? because as I knew Mariadb processes queries faster than Mysql.

I don't want to kill any processes from Mysql becase it can cause problems for my websites when they are running cron jobs or MySql queries.

One more question, can I find which Mysql queries made my VPS high load? from log files?
 

LJSHost

Well-known member
Hosting Provider
Registered
Joined
Jul 5, 2016
Messages
1,031
Points
63
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

Administrator
Staff Member
Joined
Dec 8, 2012
Messages
773
Points
63
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
 
Recommended Threads
Replies
2
Views
3,084
Replies
2
Views
2,519

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top