High load average so where to start to trouble shoot

It seems my vps needs some optimization but I have to first find out why I have high load average a lot of time . can any body help me step by step ?

«1

Comments

  • 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.

    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?

  • 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/

    Thanked by (1)mikho
  • @PHP_Backend said:
    I would suggest this book if you are interested : https://sql-performance-explained.com/

    Suggesting me to read a book? LOL

  • heyhey OG

    @lofan said:

    @PHP_Backend said:
    I would suggest this book if you are interested : https://sql-performance-explained.com/

    Suggesting me to read a book? LOL

    Why not

  • edited July 4

    @hey said:

    @lofan said:

    @PHP_Backend said:
    I would suggest this book if you are interested : https://sql-performance-explained.com/

    Suggesting me to read a book? LOL

    Why not

    Why not ? why yes? LOL

  • AnthonySmithAnthonySmith AdministratorHosting Provider

    @lofan said:

    @hey said:

    @lofan said:

    @PHP_Backend said:
    I would suggest this book if you are interested : https://sql-performance-explained.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 :)

    Inception Hosting - 256MB OpenVZ VPS back in stock for €8.00 p/year - DEDICATED IP4 + /64 IPv6 https://clients.inceptionhosting.com/cart.php?a=add&pid=177
    Please do not use the PM system here for Inception Hosting support issues.

  • WSCallumWSCallum Hosting Provider

    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 Cloud Servers & Hosting
    AMD EPYC Powered | DDoS Protected | Snapshots | Auto Backups

  • @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.

    4 core SSD 12 gig ram centos 7 cpanel with whm, Xen

  • @lofan said:

    @hey said:

    @lofan said:

    @PHP_Backend said:
    I would suggest this book if you are interested : https://sql-performance-explained.com/

    Suggesting me to read a book? LOL

    Why not

    Why not ? why yes? LOL

    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.

  • edited July 4

    @debaser said:

    @lofan said:

    @hey said:

    @lofan said:

    @PHP_Backend said:
    I would suggest this book if you are interested : https://sql-performance-explained.com/

    Suggesting me to read a book? LOL

    Why not

    Why not ? why yes? LOL

    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 :)

  • AnthonySmithAnthonySmith AdministratorHosting Provider
    edited July 4

    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.

    Thanked by (2)lofan wdmg

    Inception Hosting - 256MB OpenVZ VPS back in stock for €8.00 p/year - DEDICATED IP4 + /64 IPv6 https://clients.inceptionhosting.com/cart.php?a=add&pid=177
    Please do not use the PM system here for Inception Hosting support issues.

  • @AnthonySmith said:
    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.

    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?

  • @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.

    Thanked by (2)lofan vimalware
  • @PHP_Backend said:

    @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.

    Thanked by (1)lofan

    Easy Website Backup Script: easybackup Extremely affordable FTP backup storage: 1Fichier
    One-click CDN installation on your VPS: OneClickCDN

  • wdmgwdmg Services Provider

    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.

    Thanked by (1)lofan

    🍌

  • @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

    https://pastebin.com/raw/2LUkzesr

    Does it give you any idea?

  • @lofan said:
    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.

    🍌

  • SpeedBusSpeedBus Hosting ProviderOG

    Run mysqltuner.pl as well to see if there's anything you can adjust further in the my.cnf to smoothen things out

  • AnthonySmithAnthonySmith AdministratorHosting Provider

    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.

    Inception Hosting - 256MB OpenVZ VPS back in stock for €8.00 p/year - DEDICATED IP4 + /64 IPv6 https://clients.inceptionhosting.com/cart.php?a=add&pid=177
    Please do not use the PM system here for Inception Hosting support issues.

  • @AnthonySmith said:

    Maximum reached memory usage: 26.8G (351.05% of installed RAM)

    That should stand out,

    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?

  • AnthonySmithAnthonySmith AdministratorHosting Provider
    edited July 5

    @lofan said: 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.

    Inception Hosting - 256MB OpenVZ VPS back in stock for €8.00 p/year - DEDICATED IP4 + /64 IPv6 https://clients.inceptionhosting.com/cart.php?a=add&pid=177
    Please do not use the PM system here for Inception Hosting support issues.

  • edited July 5

    @AnthonySmith said:

    @lofan said: 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.

    Yes it impacts my site and I use cpanel because it is easier for me to manage it , I am not server admin.

  • AnthonySmithAnthonySmith AdministratorHosting Provider

    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.

    Thanked by (1)vimalware

    Inception Hosting - 256MB OpenVZ VPS back in stock for €8.00 p/year - DEDICATED IP4 + /64 IPv6 https://clients.inceptionhosting.com/cart.php?a=add&pid=177
    Please do not use the PM system here for Inception Hosting support issues.

  • @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.

  • AnthonySmithAnthonySmith AdministratorHosting Provider
    edited July 5

    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.

    Inception Hosting - 256MB OpenVZ VPS back in stock for €8.00 p/year - DEDICATED IP4 + /64 IPv6 https://clients.inceptionhosting.com/cart.php?a=add&pid=177
    Please do not use the PM system here for Inception Hosting support issues.

Sign In or Register to comment.