Control Web Panel

WebPanel => MySQL => Topic started by: sergdev777 on July 27, 2023, 07:09:47 PM

Title: Excessive resource usage: mysql
Post by: sergdev777 on July 27, 2023, 07:09:47 PM

Quote

Excessive resource usage: mysql
Resource:     Virtual Memory Size
Exceeded:     1122 > 512 (MB)
Executable:   /usr/sbin/mariadbd
Command Line: /usr/sbin/mariadbd
PID:          2030 (Parent PID:2030)
Killed:       No

I receiving this meassage hundred times a day, how possible to check which sql query eats resource or which file calling this query?

Title: Re: Excessive resource usage: mysql
Post by: Starburst on July 27, 2023, 07:46:11 PM

We have that as part of the csf/lfd ignore list under *nix 8.x

But that's how we have it configured. Haven't noticed any problems.

Title: Re: Excessive resource usage: mysql
Post by: overseer on July 27, 2023, 10:17:52 PM

How much memory do you have allocated on your server? I have 6GB on one VM and this is a typical htop sample:

Code: [Select]

   PID USER      PRI  NI  VIRT   RES   SHR S CPU% MEM%   TIME+  Command
 17790 mysql      20   0 1552M  431M  6804 S  0.0  7.6  0:00.06 /usr/sbin/mariadbd
  1314 mysql      20   0 1552M  431M  6804 S  0.0  7.6 23:40.57 /usr/sbin/mariadbd
 17804 mysql      20   0 1552M  431M  6804 S  0.0  7.6  0:00.06 /usr/sbin/mariadbd

Title: Re: Excessive resource usage: mysql
Post by: sergdev777 on July 28, 2023, 10:08:54 AM

Quote from: overseer on July 27, 2023, 10:17:52 PM

How much memory do you have allocated on your server?

2GB.

Title: Re: Excessive resource usage: mysql
Post by: sergdev777 on August 31, 2023, 07:06:26 PM

Well, I increased memory on my server to 6GB,  but this "excessive resource usage" alert is still remaining.
Sometimes my server go down while this alert.
How possible to check which DB affect this excessive resource usage??

Account:      mysql
Resource:     Process Time
Exceeded:     719712 > 1800 (seconds)
Executable:   /usr/sbin/mariadbd
Command Line: /usr/sbin/mariadbd
PID:          2123 (Parent PID:2123)
Killed:       No

Title: Re: Excessive resource usage: mysql
Post by: overseer on August 31, 2023, 10:26:33 PM

Enable user statistics so you can monitor resource usage:
https://mariadb.com/resources/blog/using-resource-monitoring-to-avoid-user-service-overload/ (https://mariadb.com/resources/blog/using-resource-monitoring-to-avoid-user-service-overload/)

Title: Re: Excessive resource usage: mysql
Post by: sergdev777 on September 01, 2023, 09:50:11 PM

Quote from: overseer on August 31, 2023, 10:26:33 PM

Enable user statistics so you can monitor resource usage:
https://mariadb.com/resources/blog/using-resource-monitoring-to-avoid-user-service-overload/ (https://mariadb.com/resources/blog/using-resource-monitoring-to-avoid-user-service-overload/)

Thanks!

Title: Re: Excessive resource usage: mysql
Post by: Starburst on September 02, 2023, 01:38:07 AM

There's allot of false alerts for some reason also.

We added the following to the csf.ignore file back when CentOS 8 first came out:

# CentOS 8
exe:/usr/bin/rpcbind
exe:/usr/sbin/mariadbd
exe:/usr/lib/systemd/systemd-resolved
exe:/usr/sbin/rngd
exe:/usr/bin/rpcbind

--

File can be found at; /etc/csf/csf.pignore

Title: Re: Excessive resource usage: mysql
Post by: sergdev777 on September 13, 2023, 07:55:01 PM

Quote from: Starburst on September 02, 2023, 01:38:07 AM

There's allot of false alerts for some reason also.

We added the following to the csf.ignore file back when CentOS 8 first came out:

# CentOS 8
exe:/usr/bin/rpcbind
exe:/usr/sbin/mariadbd
exe:/usr/lib/systemd/systemd-resolved
exe:/usr/sbin/rngd
exe:/usr/bin/rpcbind

--

File can be found at; /etc/csf/csf.pignore

The web server is under load or restarting when message about "exe:/usr/sbin/mariadbd" is sent.

Title: Re: Excessive resource usage: mysql
Post by: Starburst on September 13, 2023, 11:59:45 PM

What version MariaDB are you running?

10.11.x is the latest LTS

Title: Re: Excessive resource usage: mysql
Post by: sergdev777 on September 14, 2023, 11:30:51 AM

Quote from: Starburst on September 13, 2023, 11:59:45 PM

What version MariaDB are you running?

10.11.x is the latest LTS

MariaDB 10.6.15

Title: Re: Excessive resource usage: mysql
Post by: overseer on September 14, 2023, 02:25:30 PM

Love that changelog (https://mariadb.com/kb/en/mariadb-10-11-5-release-notes/):

Quote

InnoDB
Crashing on I/O error is unhelpful (MDEV-27593) (https://jira.mariadb.org/browse/MDEV-27593)

Title: Re: Excessive resource usage: mysql
Post by: sergdev777 on September 15, 2023, 04:22:27 PM

Well, updated to ver 10.11, not resolve the problem.