Excessive resource usage: mysql

[Clixer]

Hello folks,

Since I have switched on process monitoring in lfd, I am receiving the following notification from the server on an hourly basis:

Subject: lfd on server.domain.com: Excessive resource usage: mysql (1093 (Parent PID:1093))

Email body:

Time: Mon May 14 20:01:41 2018 +1000
Account: mysql
Resource: Process Time
Exceeded: 165589 > 1800 (seconds)
Executable: /usr/bin/bash
Command Line: /bin/sh /usr/bin/mysqld_safe
PID: 1093 (Parent PID:1093)
Killed: No

This notification has led me to believe that the MySQL server is not configured properly and there is a room for improvement.

Our VPS has 5 cpus and RAM is 5GB,

Following is the entry to the my.cnf file:

[mysqld]
default-storage-engine=innodb
performance-schema=0
innodb_file_per_table=1
innodb_buffer_pool_instances=1
innodb_buffer_pool_size=134217728
innodb_log_file_size=512M
max_connections=300
max_allowed_packet=268435456
open_files_limit=10000
query_cache_type=1
query_cache_limit=256K
query_cache_min_res_unit=2k
query_cache_size=80M
tmp_table_size=64M
max_heap_table_size=64M
slow-query-log=1
slow-query-log-file=/var/lib/mysql/mysql-slow.log
long_query_time=1
local-infile=0
# MySQL idle Connections
wait_timeout=60
# Skip reverse DNS lookup of clients
skip-name-resolve

Following is the output of the top command:

top - 21:51:51 up 3 days, 11:57, 1 user, load average: 0.29, 0.22, 0.27
Tasks: 178 total, 1 running, 175 sleeping, 0 stopped, 2 zombie
%Cpu(s): 21.7 us, 1.4 sy, 0.0 ni, 76.9 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 4931788 total, 143520 free, 1962884 used, 2825384 buff/cache
KiB Swap: 4980732 total, 4907260 free, 73472 used. 2346676 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1449 mysql 20 0 1753644 195476 7148 S 100.3 4.0 692:06.16 mysqld
38569 clubint+ 20 0 455076 21236 13076 S 3.3 0.4 0:00.10 php-fpm
92384 root 20 0 183832 31240 2180 S 0.7 0.6 1:38.41 lfd - sleeping
96459 nobody 20 0 2585292 166104 30120 S 0.7 3.4 1:14.02 httpd
1858 root 20 0 7188 568 460 S 0.3 0.0 0:18.15 strace
38418 root 20 0 160064 2268 1548 R 0.3 0.0 0:00.17 top
1 root 20 0 46492 5064 3028 S 0.0 0.1 4:53.80 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.41 kthreadd

What should I change in the my.cnf file to optimise the MySQL performance.

Many thanks for help.

 

[dalem]

MySQL runs all the time you need to set your csf firewall to ignore
add
exe:/usr/sbin/mysqld_safe
to your csf pignore

 

[Clixer]

Thanks Dalem.

So you dont see any issues with the MySQL performance?

 

[dalem]

Not from the info you posted

 

[cPanelLauren]

Thanks for the response on this @dalem! @Clixer please let us know if you have any other issues with this.

 

[Clixer]

Hello @dalem and @cPanelLauren.

I have found that the following two entries are already in the csf.pignore file

exe:/usr/sbin/mysqld
exe:/usr/sbin/mysqld_safe

This file is at location: /etc/csf/csf.pignore.

Any ideas as to why the lfd is still sending the email notifications?

Thanks for help.

 

[cPanelLauren]

Hi @Clixer

The exe you're referencing is not the same one you received the email about. It looks like the exe should be

Executable: /usr/bin/bash

Though as a whole I don't think it's a good idea to add /usr/bin/bash to that list.

The primary reason you're getting that notification is that the runtime of the process exceeded the threshold set by LFD's PT_USERTIME setting. Basically, CSF says the process ran too long or abnormally long.

If the length of time a process runs is inconsequential to you can modify the PT_USERTIME setting to ignore this, or you can increase it, though you may risk hitting it again if it starts complaining about bash.

 

[Clixer]

Hi @cPanelLauren. Thank you for further advice.

My computing knowledge is limited but mysqld is a process which should be running all the time. The websites hosted on the VPS are database driven, calls to the database will be made whenever a visitor requests a page which is not cached. I am at a loss as to why mysqld process should have a time limit.

Technically, unless the VPS is rebooted, myslqd would continue to run, hence I am not sure what maximum time I could use? I have used the maximum allowed of 86400. I am assuming lfd will continue to send me email notification every 24 hours.

 

[cPanelLauren]

Hi @Clixer

I agree, though in this case it wasn't mysql that it was actually complaining about, it was bash. The same predicament stands though, the only way to stop this is either set /usr/bin/bash to be ignored, disable the PT_USERTIME setting, or modify it though in this instance I don't think modifying PT_USERTIME to a higher threshold would stop you from receiving the email. You could also leave it as is and self-verify the process' legitimacy (processes like httpd, mysql, etc are processes that normally run for extended amounts of time.

You could also check over at the CSF forums for further advice on this, they may be able to provide you with some advice on customizations that will work for you. You can find the CSF forum here: ConfigServer Community Forum - Index page