Skip to main content

mySQL high ram usagw

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

Comments

17 comments

Date Votes
  • 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
    0
  • retechpro
    Here is top -c result: here is mysqladmin processlist 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
    0
  • 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
    0
  • cPRex Jurassic Moderator
    Hey there! What is the output from "mysqladmin proc status" on the command line?
    0
  • retechpro
    Hey there! What is the output from "mysqladmin proc status" on the command line?

    Please check. [root@server1 ~]# 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
    0
  • retechpro
    I"m unable to post mysql tuner output.
    0
  • cPRex Jurassic Moderator
    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.
    0
  • 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
    0
  • cPRex Jurassic Moderator
    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.
    0
  • 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?
    0
  • cPRex Jurassic Moderator
    It couldn't hurt, especially if you're not familiar with reading MySQL queries.
    0
  • 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?
    0
  • cPRex Jurassic Moderator
    I'm not able to recommend anyone specifically but we have a list of admins that work with cPanel here:
    0
  • 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. [root@server1 ~]# 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 | +------+-----------------+-----------+----+---------+------+------------------------+------------------+
    0
  • cPRex Jurassic Moderator
    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.
    0
  • retechpro
    How to prevent queries to go to sleep mod. Also event scheduler have lot of waiting queues
    0
  • cPRex Jurassic Moderator
    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:
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post