Excessive resource usage: mysql

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.

 

Thanks Dalem.

So you dont see any issues with the MySQL performance?

 

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.

 

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.