I start from running top then press SHIFT+p
It will list all processes that are running sorted by cpu usage.
Try to detect which process is eating cpu.
@PHP_Backend said:
I start from running top then press SHIFT+p
It will list all processes that are running sorted by cpu usage.
Try to detect which process is eating cpu.
That is a common culprit then.
There are several reason why mysql is eating CPU. In most time, it is because of inefficient queries. Are your DB is optimized according to your need? Is it indexed properly? Do you have traffic hike? Maybe a cache layer before mysql can help a bit?
Well I start with saying confidently that my DB needs to be optimizad and I was wondering for along time how to do that perhaps I should have started a thread with that subject
Because it is important that you understand what you are doing not just knowing which buttons to press.
Database optimisation is individual to the database there is not just 1 line of copy and paste code you can use to fix it.
If the load is not causing an issue then dont worry about it, if you are getting warnings from your host then pay more for a dedicated server with raid 10 ssd and a brand new CPU so you can cover the fact you dont want to learn
What are the specifications of the server you're having issues with? You can optimize software yes but there are still hardware limitations to think about.
@WSCallum said:
What are the specifications of the server you're having issues with? You can optimize software yes but there are still hardware limitations to think about.
please start by giving as much info as you'r can, mysql stats, software versions etc, sites with the highest use, what sort of sites, about 10 different views of top over an hour, better if you can make them 30-second gifs, also about a 5-minute video of 'atop -d 5'
Then we can see where the real bottleneck is and take it from there.
edit: forgot, please also install mytop and give us a good overview of that.
please start by giving as much info as you'r can, mysql stats, software versions etc, sites with the highest use, what sort of sites, about 10 different views of top over an hour, better if you can make them 30-second gifs, also about a 5-minute video of 'atop -d 5'
Then we can see where the real bottleneck is and take it from there.
edit: forgot, please also install mytop and give us a good overview of that.
I really understand your point . I know this is not just making a few changes in whm , some tweaks but that guy who suggested go and buy a book makes me speechless.
@lofan said: but that guy who suggested go and buy a book
I am really sorry for offending you. That was not my intention.
Personally, I like to read books. And that book helped me to learn a lot about database performance. That's why I though it would help you too.
@lofan said: I was wondering for along time how to do that
By reading this, I thought you would be interested to learn to optimize DB.
@lofan said: but that guy who suggested go and buy a book
I am really sorry for offending you. That was not my intention.
Personally, I like to read books. And that book helped me to learn a lot about database performance. That's why I though it would help you too.
@lofan said: I was wondering for along time how to do that
By reading this, I thought you would be interested to learn to optimize DB.
Again, I am sorry.
Oh , You are a humble man. I am sorry for misunderstanding. Thanks.
Some general ideas about optimizing DB: use NVMe, use cache, eliminate unecessary DB quries, etc. If you don't wanna read a book, a quick way is to search for the script you are using, for example, if you are using wordpress, just search "how to optimize wordpress database" and there is going to be tons of articles and even automated plugins about that.
I half want to suggest an offloaded database system that will handle it properly for you, especially if you’re not willing to learn a bit on the workings, performance, etc.
I run MySQLTuner and other similar scripts to get an idea of where the slowdown may be, turn on the slow query log and tail that for a bit when the box is under load, then inevitably I have to yell at our developers to optimize their shit.
I'm not a DB admin or SQL whiz but the tools are there to help troubleshoot performance issues. 99.99% of the time you just need to tune whatever's hitting the DB so hard, and static cache the crap out of it. If you have to hit the DB hard for the application to work, then you need to throw more hardware at it.
@Harambe said:
I run MySQLTuner and other similar scripts to get an idea of where the slowdown may be, turn on the slow query log and tail that for a bit when the box is under load, then inevitably I have to yell at our developers to optimize their shit.
I'm not a DB admin or SQL whiz but the tools are there to help troubleshoot performance issues. 99.99% of the time you just need to tune whatever's hitting the DB so hard, and static cache the crap out of it. If you have to hit the DB hard for the application to work, then you need to throw more hardware at it.
Thanks, Now the load average is OK but just for ref
It lists them all at the bottom. I don't know what applications you're running and if they could be optimized, if they can't then you need more hardware.
Maximum reached memory usage: 26.8G (351.05% of installed RAM)
That should stand out, aside from that, what seems fairly conclusive as a report that you are probably about maxed out already, time to either upgrade the server or if this is one you are hosting on for customers install cloudlinux and restrict them or charge them more.
Sometimes with vanilla installs when people are hosting and don't really know what they are doing you can end up with 1 bad customer giving everyone a bad day, if these are all your sites, you need to just reach deeper into your pockets I think, double your ram for a start.
Always a difficult one, as it could be something about the sites that could be changed to reduce use by 80% but the only person that could know that is you, if you just don't know where to start consider hiring someone to check things in detail, Upwork is a good place to find someone on fair hourly rates to do this for you, might be cheaper in the long run if they can identify it for you, the risk is though you pay someone and they also conclude you just don't have enough ram.
I suppose if it is possible you could double your ram for a month and see if that takes care of it.
@AnthonySmith said:
Then if you don’t want to hire someone to optimise your site you should just get more ram, restrictions will only slow it down further.
I am learning that is why I am here or I would not waste my time on forums and just hire some body and enjoy my summer and buying more rams , giving up cpanel all these are not the purpose of me finding this forum and asking questions . I am learning , Can't go to an institute and spend hours in classrooms or go buy a book but I have already learned a lot by asking.
To make a VPS use less ram there are a lot of tricks I can see searching in google and I will implement them. Thanks for your suggestion any way. I understand your point.
ok, well start with MySQLTuner and combine that with observing mytop over a few hours, that should show you what specifically is causing the issue, it may be a plugin that needs to be rewritten.
The bottom line here though is your website wants to use about 30GB ram (which is insane)
You posted the output from the tuner already:
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (40000) variable
should be greater than table_open_cache (2000)
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_open_cache (> 2000)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 181.6M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
Have you followed up on any of that yet, if so what difference did it make and which changes did you implement?
I can read it for you, I can't understand it for you, you need to take it line by line and do some googling if you really want to learn.
Comments
I start from running
topthen press SHIFT+pIt will list all processes that are running sorted by cpu usage.
Try to detect which process is eating cpu.
https://phpbackend.com/
OK let's say it is mysql
That is a common culprit then.
There are several reason why mysql is eating CPU. In most time, it is because of inefficient queries. Are your DB is optimized according to your need? Is it indexed properly? Do you have traffic hike? Maybe a cache layer before mysql can help a bit?
https://phpbackend.com/
Well I start with saying confidently that my DB needs to be optimizad and I was wondering for along time how to do that perhaps I should have started a thread with that subject
I would suggest this book if you are interested : https://sql-performance-explained.com/
https://phpbackend.com/
Suggesting me to read a book? LOL
Why not
Why not ? why yes? LOL
Because it is important that you understand what you are doing not just knowing which buttons to press.
Database optimisation is individual to the database there is not just 1 line of copy and paste code you can use to fix it.
If the load is not causing an issue then dont worry about it, if you are getting warnings from your host then pay more for a dedicated server with raid 10 ssd and a brand new CPU so you can cover the fact you dont want to learn
https://inceptionhosting.com
Please do not use the PM system here for Inception Hosting support issues.
What are the specifications of the server you're having issues with? You can optimize software yes but there are still hardware limitations to think about.
Clouveo - SSD/NVMe Cloud VPS & Hosting
Performance & AMD EPYC Cloud | DDoS Protected | Snapshots | Auto Backups
4 core SSD 12 gig ram centos 7 cpanel with whm, Xen
I get it. You’re too cool for school. Instead of learning what’s going on at your server, you just expect that one of us solves your problem.
You are smart . I am impressed at your inteligence
Well let's have a go anyway.
please start by giving as much info as you'r can, mysql stats, software versions etc, sites with the highest use, what sort of sites, about 10 different views of top over an hour, better if you can make them 30-second gifs, also about a 5-minute video of 'atop -d 5'
Then we can see where the real bottleneck is and take it from there.
edit: forgot, please also install mytop and give us a good overview of that.
https://inceptionhosting.com
Please do not use the PM system here for Inception Hosting support issues.
I really understand your point . I know this is not just making a few changes in whm , some tweaks but that guy who suggested go and buy a book
makes me speechless.
What about MySQLTuner will that do any help?
I am really sorry for offending you. That was not my intention.
Personally, I like to read books. And that book helped me to learn a lot about database performance. That's why I though it would help you too.
By reading this, I thought you would be interested to learn to optimize DB.
Again, I am sorry.
https://phpbackend.com/
Oh , You are a humble man. I am sorry for misunderstanding. Thanks.
Some general ideas about optimizing DB: use NVMe, use cache, eliminate unecessary DB quries, etc. If you don't wanna read a book, a quick way is to search for the script you are using, for example, if you are using wordpress, just search "how to optimize wordpress database" and there is going to be tons of articles and even automated plugins about that.
I half want to suggest an offloaded database system that will handle it properly for you, especially if you’re not willing to learn a bit on the workings, performance, etc.
I run MySQLTuner and other similar scripts to get an idea of where the slowdown may be, turn on the slow query log and
tailthat for a bit when the box is under load, then inevitably I have to yell at our developers to optimize their shit.I'm not a DB admin or SQL whiz but the tools are there to help troubleshoot performance issues. 99.99% of the time you just need to tune whatever's hitting the DB so hard, and static cache the crap out of it. If you have to hit the DB hard for the application to work, then you need to throw more hardware at it.
🦍🍌
Thanks, Now the load average is OK but just for ref
https://pastebin.com/raw/2LUkzesr
Does it give you any idea?
It lists them all at the bottom. I don't know what applications you're running and if they could be optimized, if they can't then you need more hardware.
🦍🍌
Run mysqltuner.pl as well to see if there's anything you can adjust further in the my.cnf to smoothen things out
CrownCloud - Internet Services | Los Angeles, California | Frankfurt, Germany | Amsterdam, The Netherlands
That should stand out, aside from that, what seems fairly conclusive as a report that you are probably about maxed out already, time to either upgrade the server or if this is one you are hosting on for customers install cloudlinux and restrict them or charge them more.
Sometimes with vanilla installs when people are hosting and don't really know what they are doing you can end up with 1 bad customer giving everyone a bad day, if these are all your sites, you need to just reach deeper into your pockets I think, double your ram for a start.
Always a difficult one, as it could be something about the sites that could be changed to reduce use by 80% but the only person that could know that is you, if you just don't know where to start consider hiring someone to check things in detail, Upwork is a good place to find someone on fair hourly rates to do this for you, might be cheaper in the long run if they can identify it for you, the risk is though you pay someone and they also conclude you just don't have enough ram.
I suppose if it is possible you could double your ram for a month and see if that takes care of it.
https://inceptionhosting.com
Please do not use the PM system here for Inception Hosting support issues.
But this is my cnf
https://pastebin.com/raw/68hch9sq
It is not for any customer , Just 1 domain
Where in CNF does it permit ram be used that much?
How can I correct this?
Well just to be clear then, you are happy for your site to slow down considerably rather than add more resources yes?
What I am not clear about here is what the real issue is, just because you see load spikes? does it impact the site?
Installing cPanel for just a single site is madness anyway, to be honest.
https://inceptionhosting.com
Please do not use the PM system here for Inception Hosting support issues.
Yes it impacts my site and I use cpanel because it is easier for me to manage it , I am not server admin.
Then if you don’t want to hire someone to optimise your site you should just get more ram, restrictions will only slow it down further.
https://inceptionhosting.com
Please do not use the PM system here for Inception Hosting support issues.
I am learning that is why I am here or I would not waste my time on forums and just hire some body and enjoy my summer and buying more rams , giving up cpanel all these are not the purpose of me finding this forum and asking questions . I am learning , Can't go to an institute and spend hours in classrooms or go buy a book but I have already learned a lot by asking.
To make a VPS use less ram there are a lot of tricks I can see searching in google and I will implement them. Thanks for your suggestion any way. I understand your point.
ok, well start with MySQLTuner and combine that with observing mytop over a few hours, that should show you what specifically is causing the issue, it may be a plugin that needs to be rewritten.
The bottom line here though is your website wants to use about 30GB ram (which is insane)
You posted the output from the tuner already:
-------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/log/mysqld.log file Control error line(s) into /var/log/mysqld.log file Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found. See https://dev.mysql.com/doc/internals/en/join-buffer-size.html (specially the conclusions at the bottom of the page). When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/ This is MyISAM only table_cache scalability problem, InnoDB not affected. See more details here: https://bugs.mysql.com/bug.php?id=49177 This bug already fixed in MySQL 5.7.9 and newer MySQL versions. Beware that open_files_limit (40000) variable should be greater than table_open_cache (2000) Performance schema should be activated for better diagnostics Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (=0) query_cache_type (=0) query_cache_limit (> 1M, or use smaller result sets) join_buffer_size (> 256.0K, or always use indexes with JOINs) tmp_table_size (> 16M) max_heap_table_size (> 16M) table_open_cache (> 2000) performance_schema = ON enable PFS innodb_buffer_pool_size (>= 181.6M) if possible. innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.Have you followed up on any of that yet, if so what difference did it make and which changes did you implement?
I can read it for you, I can't understand it for you, you need to take it line by line and do some googling if you really want to learn.
With this sort of thing, there are no shortcuts.
https://inceptionhosting.com
Please do not use the PM system here for Inception Hosting support issues.