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.
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.