Mysql uses too much cpu

[Oxheberg LLC]

Hey everyone,
I've migrated my reseller account to a VPS server 10 core 60GB, I have cloudlinux 8.6 installed.
After all accounts were migrated, mysql cpu usage is almost always at 50% and sometimes it increases to 80% and makes all websites lags, I looked on this forum and found the command:
mysqladmin process status
it doesn't show much databases in use, but i noticed that when the cpu usage increases sharply, queries per second raises to 350 or more, opens is like 20k and slow queries are 50.
I have DB Governor installed and I thought this would solve this kind of issues but it doesn't help

any help about this and how to secure my server and make it more resilient to failing due to one account using more than he should be is appreciated!

 

[cPRex]

Hey there! Without seeing exactly what is happening, it's hard to say what the issue may be. The number of opens typically doesn't concern me, as that just means MySQL is holding the table open to make future reads faster.

When you say you see 80% usage, is that the number that shows up in the CPU column? If so, I wouldn't expect that to be an issue either on a 10-core device, as that percentage is based on the number of cores. So, 200% would mean MySQL is using 2 full CPU cores to perform work.

There's two possible things happening - your VPS could be overloaded outside of your own instance, which is something you'd need to speak to your host about, or MySQL Governor isn't configured to enforce the limits. There's a few different modes for MySQL governor that can be found here:

CloudLinux - Restricting MySQL usage resources with MySQL Governor - CloudLinux

www.cloudlinux.com

so you'll want to ensure that is actually set to take action instead of just monitor.

 

[Oxheberg LLC]

Hey,
so I took sometime to verify the mysql and governor, I have set a limit for each client, but for some reason i'm having an issue with a client, he constantly gets restricted and then his account make bunch of slow queries, that terminate after 15 seconds (my governor settings) but it keeps making new and new process until it hit the 30 open connections that I have configured, this alone makes the database really really slow so I disabled governor for that account (his wordpress theme runs really slow queries that takes 5 seconds or more).

anyway my other question is, sometimes after I let mysql run for 1 or 2 days I notice that mytop shows 200 threads being use but all of the connections show in orange color and no query gets completed and all websites stop working until i restart mysql, I'm not sure what could be causing this either slow VPS or mysql configuration.

for some reason when ever I try to post my.conf cloudflare tells me the action is blocked and I can't.

 

[cPRex]

At this point it would likely be best to create a ticket with our team so we can check the MySQL system on the server while the issue is happening. We can take a look and let you know if CloudLinux needs to get involved with the issue also.

If you're able to create that ticket can you please post the number here so I can follow along?

 

[Humus007]

Hey everyone,
I've migrated my reseller account to a VPS server 10 core 60GB, I have cloudlinux 8.6 installed.
After all accounts were migrated, mysql cpu usage is almost always at 50% and sometimes it increases to 80% and makes all websites lags, I looked on this forum and found the command:
mysqladmin process status
it doesn't show much databases in use, but i noticed that when the cpu usage increases sharply, queries per second raises to 350 or more, opens is like 20k and slow queries are 50.
I have DB Governor installed and I thought this would solve this kind of issues but it doesn't help

any help about this and how to secure my server and make it more resilient to failing due to one account using more than he should be is appreciated!

My guess is that it's related to whatever component is causing the query to execute. Because manually running the query is very fast, the query itself cannot be slow, so whatever is triggering it could be the source of the high CPU usage.

Do you understand how the query is triggered?

A database is not independent of its application. Allocating memory to some buffers, particularly the innodb buffer pool, will greatly assist you (there are many tools that suggest the "right" values for your resources and usage variables), but you must also monitor what is happening with the information input. High CPU usage may be caused by excessive IO, such as slow or frequent writes.

 

[Bidi]

Hey,
so I took sometime to verify the mysql and governor, I have set a limit for each client, but for some reason i'm having an issue with a client, he constantly gets restricted and then his account make bunch of slow queries, that terminate after 15 seconds (my governor settings) but it keeps making new and new process until it hit the 30 open connections that I have configured, this alone makes the database really really slow so I disabled governor for that account (his wordpress theme runs really slow queries that takes 5 seconds or more).

anyway my other question is, sometimes after I let mysql run for 1 or 2 days I notice that mytop shows 200 threads being use but all of the connections show in orange color and no query gets completed and all websites stop working until i restart mysql, I'm not sure what could be causing this either slow VPS or mysql configuration.

for some reason when ever I try to post my.conf cloudflare tells me the action is blocked and I can't.

Hello,

How did you disabled mysql governor for one account ? I have the same issue

 

[cPRex]

@Bidi - I'm not finding an obvious way to completely disable the mysqlgovernor tool for one user, but you could set high limits for the user so they won't reach it:

CloudLinux - Restricting MySQL usage resources with MySQL Governor - CloudLinux

www.cloudlinux.com