Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

Server Load Issues

Discussion in 'Workarounds and Optimization' started by radu80, May 2, 2018.

  1. radu80

    radu80 Registered

    Joined:
    Apr 3, 2018
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Cluj
    cPanel Access Level:
    DataCenter Provider
    Hello.
    I have a problem with one of our VPS.
    Configuration: Centos 7 with
    36 GB RAM
    8x vCPU @ 2.5GHz
    We have a site on this VPS, but mysql uses the processor very much. Can you help me?
    here is mysql advisor:
    Issue Recommendation
    Code:
    Uptime is less than 1 day, performance tuning may not be accurate. To have more accurate averages it is recommended to let the server run for longer than a day before running this analyzer
    There is a high percentage of slow queries compared to the server uptime. You might want to increase long_query_time or optimize the queries listed in the slow query log
    {long_query_time} is set to 10 seconds or more, thus only slow queries that take above 10 seconds are logged. It is suggested to set long_query_time to a lower value, depending on your environment. Usually a value of 1-5 seconds is suggested.
    The slow query log is disabled. Enable slow query logging by setting slow_query_log to 'ON'. This will help troubleshooting badly performing queries.
    Suboptimal caching method. You are using the MySQL Query cache with a fairly high traffic database. It might be worth considering to use memcached instead of the MySQL Query cache, especially if you have multiple slaves.
    Less than 80% of the query cache is being utilized. This might be caused by query_cache_limit being too low. Flushing the query cache might help as well.
    Too many sorts are causing temporary tables. Consider increasing sort_buffer_size and/or read_rnd_buffer_size, depending on your system memory limits.
    Too many sorts are causing temporary tables. Consider increasing sort_buffer_size and/or read_rnd_buffer_size, depending on your system memory limits.
    There are lots of rows being sorted. While there is nothing wrong with a high amount of row sorting, you might want to make sure that the queries which require a lot of sorting use indexed columns in the ORDER BY clause, as this will result in much faster sorting.
    There are too many joins without indexes. This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins.
    The rate of reading the first index entry is high. This usually indicates frequent full index scans. Full index scans are faster than table scans but require lots of CPU cycles in big tables, if those tables that have or had high volumes of UPDATEs and DELETEs, running 'OPTIMIZE TABLE' might reduce the amount of and/or speed up full index scans. Other than that full index scans can only be reduced by rewriting queries.
    The rate of reading data from a fixed position is high. This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable.
    The rate of reading the next table row is high. This indicates that many queries are doing full table scans. Add indexes where applicable.
    {tmp_table_size} and {max_heap_table_size} are not the same. If you have deliberately changed one of either: The server uses the lower value of either to determine the maximum size of in-memory tables. So if you wish to increase the in-memory table limit you will have to increase the other value as well.
    Many temporary tables are being written to disk instead of being kept in memory. Increasing max_heap_table_size and tmp_table_size might help. However some temporary tables are always being written to disk, independent of the value of these variables. To eliminate these you will have to rewrite your queries to avoid those conditions (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column bigger than 512 bytes) as mentioned in the beginning of an Article by the Pythian Group
    MyISAM key buffer (index cache) % used is low. You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
    The rate of opening tables is high. Opening tables requires disk I/O which is costly. Increasing table_open_cache might avoid this.
    The rate of opening files is high. Consider increasing open_files_limit, and check the error log when restarting after changing open_files_limit.
    Too many table locks were not granted immediately. Optimize queries and/or use InnoDB to reduce lock wait.
    Too many table locks were not granted immediately. Optimize queries and/or use InnoDB to reduce lock wait.
    
    mysqltunner is attached and my.cnf

    Thank you
     

    Attached Files:

    #1 radu80, May 2, 2018
    Last edited by a moderator: May 2, 2018
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,214
    Likes Received:
    1,936
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @radu80,

    A common approach to improving the MySQL configuration after running a tuner is to add new variables and make changes to existing variables based on the tuner's advice under "Variables to adjust". We provide a list of companies offering system administration services if you require assistance with this:

    System Administration Services | cPanel Forums

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice