mySQL high ram usagw

[retechpro]

Hi mates,
Please help to optimize my mysql to reduce ram loads

 

[retechpro]

I’m facing to much usage of ram and cpu due to mysql.
I’m not a expert but hope will get good suggestions from experts.
Server specs : Ryzen 5950x 16 core
Ram : 128GB ECC
Hard : 3TB NVME on Raid-1
Using Litespeed, Cloudlinux, cpanel, Imunify360, jetbackup incremental backups

 

[retechpro]

Here is top -c result:

View attachment 81345

here is mysqladmin processlistView attachment 81349


Now explaining issue.
I move on this server in September 2022. I move my 400 cpanels from two servers into one. After that i faced issue. My websites goes down and took 15-20s for load. When I increase max connection = 10000 it goes normal. When i do 500-1000. It again slow down.
Now I’m facing high load issue on ram. My ram consumption is very high now. I am unable to sort out the issue.

please let me know how can i optimize my mysql

 

[retechpro]

Sorry for multiple comments. I’m unable to post all these in one comment.
Also enable to post my.cnf setting here.


Now attaching image

 

[cPRex]

Hey there! What is the output from "mysqladmin proc status" on the command line?

 

[retechpro]

Hey there! What is the output from "mysqladmin proc status" on the command line?

Please check.


[[email protected] ~]# mysqladmin proc status
+----------+--------------------+-----------+--------------------+---------+---------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+--------------------+-----------+--------------------+---------+---------+------------------------+------------------+
| 5 | event_scheduler | localhost | | Daemon | 1416871 | Waiting on empty queue | |
| 14822406 | root | localhost | | Sleep | 55 | | |
| 14822412 | root | localhost | | Sleep | 9 | | |
| 14868492 | root | localhost | | Sleep | 1210 | | |
| 14882249 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 34 | | |
| 14882250 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 32 | | |
| 14882255 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 33 | | |
| 14882263 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 33 | | |
| 14882344 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 34 | | |
| 14882345 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 33 | | |
| 14882349 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 32 | | |
| 14882354 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 32 | | |
| 14882355 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 34 | | |
| 14882496 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 33 | | |
| 14882515 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 32 | | |
| 14882591 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 33 | | |
| 14882592 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 32 | | |
| 14882593 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 33 | | |
| 14882595 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 59 | | |
| 14882596 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 33 | | |
| 14882598 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 33 | | |
| 14882599 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 33 | | |
| 14882602 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 33 | | |
| 14882603 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 33 | | |
| 14882846 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 39 | | |
| 14882847 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 39 | | |
| 14882849 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 39 | | |
| 14882850 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 39 | | |
| 14882851 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 38 | | |
| 14882852 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 39 | | |
| 14882853 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 39 | | |
| 14882854 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 39 | | |
| 14882856 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 39 | | |
| 14883152 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 2 | | |
| 14883153 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 2 | | |
| 14883164 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 3 | | |
| 14883196 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 1 | | |
| 14883197 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 1 | | |
| 14883198 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 1 | | |
| 14883200 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 1 | | |
| 14883202 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 1 | | |
| 14883204 | onetouchcosmo_w462 | localhost | onetouchcosmo_w462 | Sleep | 1 | | |
| 14883207 | ignou111_web | localhost | ignou111_web | Sleep | 0 | | |
| 14883209 | root | localhost | | Query | 0 | init | show processlist |
+----------+--------------------+-----------+--------------------+---------+---------+------------------------+------------------+
Uptime: 1416875 Threads: 44 Questions: 1410565280 Slow queries: 204 Opens: 101935027 Flush tables: 3 Open tables: 4000 Queries per second avg: 995.546


 

[retechpro]

I’m unable to post mysql tuner output.

 

[cPRex]

You could always post a screenshot of the mysqltuner output if you think that would be helpful.

Thanks for that. Are you expecting your MySQL server to be this busy? With 1000 queries per second, I would expect it to take a large amount of CPU and RAM to handle that.

If you wanted to drop the open_files_limit value that would decrease the RAM usage, but you'd likely see the CPU usage and query times go up.

For this much database activity, it might be worth creating a separate database server specifically to handle that service.

 

[retechpro]

Thanks for reply. I don’t know about the mysql. Recently i moved the server and faced to much load time issue so I increased the max connection to 10000.
Now the ram usage is very high as my all server websites usage is very low. 500-1000 traffic per month on per cpanel. Don’t know why the mysql usage are high.
Please suggest a some recommendation to reduce my ram usage.

here is the output of mysql tuner

 

[cPRex]

Thanks for sharing that. I'm honestly not sure, as that seems like a very large amount of MySQL traffic happening for a server where the websites aren't very busy.

It would be a good idea to review the queries running on the server by enabling the query log for a *very* short time, as having that on for more than a few minutes with the amount of traffic you're seeing will likely cause server issues.

It's also possible the system is compromised and causing problems with MySQL.

The issue here isn't going to be lowering the server load, but finding the root cause as to why MySQL is so busy on the system.

 

[retechpro]

Thanks for sharing that. I'm honestly not sure, as that seems like a very large amount of MySQL traffic happening for a server where the websites aren't very busy.

It would be a good idea to review the queries running on the server by enabling the query log for a *very* short time, as having that on for more than a few minutes with the amount of traffic you're seeing will likely cause server issues.

It's also possible the system is compromised and causing problems with MySQL.

The issue here isn't going to be lowering the server load, but finding the root cause as to why MySQL is so busy on the system.

Should i hire a server administrator for this?

 

[cPRex]

It couldn't hurt, especially if you're not familiar with reading MySQL queries.

 

[retechpro]

It couldn't hurt, especially if you're not familiar with reading MySQL queries.

Do you have any server administrator recommendation for solving this issue?

 

[cPRex]

I'm not able to recommend anyone specifically but we have a list of admins that work with cPanel here: System Administration Services

 

[retechpro]

Hi could you please let me know why the event_scheduler have lot of sleep queries and toot have lot of sleep queries what does it mean.


[[email protected] ~]# mysqladmin processlist
+------+-----------------+-----------+----+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+-----------+----+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | | Daemon | 818 | Waiting on empty queue | |
| 106 | root | localhost | | Sleep | 10 | | |
| 456 | root | localhost | | Sleep | 42 | | |
| 4487 | root | localhost | | Sleep | 441 | | |
| 8223 | root | localhost | | Query | 0 | init | show processlist |
+------+-----------------+-----------+----+---------+------+------------------------+------------------+

 

[cPRex]

I don't believe that is what is being shown there. The event_schedule user is just waiting on the queue, but the root user processes are the ones in the sleep state. Only the top row is related to event_scheduler.

 

[retechpro]

How to prevent queries to go to sleep mod. Also event scheduler have lot of waiting queues

 

[cPRex]

There isn't going to be just one good answer to this issue, unfortunately, as this is usually an issue with the site's code. We do have a guide that provides more details about MySQL and the sleep status here:

https://support.cpanel.net/hc/en-us/articles/4402602895639-What-is-MySQL-Sleep-Query-

so it would be good to review that to see if that applies to your machine. Ultimately, the best solution in your case would be to work with a database professional to check the server, since it seems you've had MySQL issues for a few months at this point.