Excessive resource usage: mysql

Clixer

• May 14, 2018 06:55

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

  • May 14, 2018 12:33

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

  0
• 

  Clixer

  • May 14, 2018 12:58

  Thanks Dalem. So you dont see any issues with the MySQL performance?

  0
• 

  dalem

  • May 14, 2018 14:51

  Not from the info you posted

  0
• 

  cPanelLauren

  • May 14, 2018 19:50

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

  0
• 

  Clixer

  • May 15, 2018 07:35

  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.

  0
• 

  cPanelLauren

  • May 15, 2018 13:27

  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.

  0
• 

  Clixer

  • May 17, 2018 10:50

  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.

  0
• 

  cPanelLauren

  • May 17, 2018 13:11

  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

  0

Please sign in to leave a comment.