The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Mysql takes up heavy load on CPU and eats most of the ram

Discussion in 'Workarounds and Optimization' started by webhosting-k999, Mar 29, 2014.

  1. webhosting-k999

    Joined:
    Mar 29, 2014
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    I'm running a dedicated server with Xeon 8 cores and 16 GB RAM...
    I was trying to update mysql optimization settings for my.cnf as per recommendation by mysqltuner.pl. But still mysql utilizes heavy load of CPU and Heavy RAM.

    How to apply recommendation by mysqltuner.pl---
    ------------------
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes---> how to apply this settings.

    my.cnf settings---

    Code:
    [mysqld]
        local-infile=0
    
        connect_timeout=360
        wait_timeout=2400
        interactive_timeout=2400
        default-storage-engine=MyISAM
    
        max_connections = 400
        max_user_connections = 100
    
        key_buffer_size=1400M
        join_buffer_size=4M
        sort_buffer_size=512K
        read_rnd_buffer_size=512K
    
        slow_query_log=1
        slow_query_log_file=mysql-slow.log
        long_query_time=0.1
    
        query_cache_type = 1
        query_cache_size = 150M
        query_cache_limit = 1M
    
        max_allowed_packet=32M
        tmp_table_size=256M
        max_heap_table_size=256M
        open_files_limit=100000
    
        table_definition_cache=2000
        table_open_cache=6000
    
        thread_cache_size=64
    
        innodb_buffer_pool_size=662M
        innodb_file_per_table=1
    
    Please suggest the best possible settings for the said above..
    Thanking you,
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    Could you post the full output of the MySQL tuner results so we can get a better idea of your existing performance statistics?

    Thank you.
     
  3. webhosting-k999

    Joined:
    Mar 29, 2014
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    After that from forum I got to know mysqltuner does not support mysql 5.5 any more...
    I'm using tuning-primer.sh which says the following-
    Code:
    bash  tuning-primer.sh
    
            -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.5.36-cll x86_64
    
    Uptime = 0 days 21 hrs 33 min 51 sec
    Avg. qps = 84
    Total Questions = 6550459
    Threads Connected = 35
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    [url=http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html]MySQL :: MySQL 5.5 Reference Manual :: 5.1.4 Server System Variables[/url]
    Visit [url]http://www.mysql.com/products/enterprise/advisors.html[/url]
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 10.000000 sec.
    You have 40963 out of 6550815 that take longer than 10.000000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See [url=http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html]MySQL :: MySQL 5.5 Reference Manual :: 7.5 Point-in-Time (Incremental) Recovery Using the Binary Log[/url]
    
    WORKER THREADS
    Current thread_cache_size = 6
    Current threads_cached = 0
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 150
    Current threads_connected = 37
    Historic max_used_connections = 102
    The number of used connections is 68% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 266 M
    Current InnoDB data space = 768 M
    Current InnoDB buffer pool free = 0 %
    Current innodb_buffer_pool_size = 64 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 661 M
    Configured Max Per-thread Buffers : 675 M
    Configured Max Global Buffers : 202 M
    Configured Max Memory Limit : 877 M
    Physical Memory : 15.57 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 3.14 G
    Current key_buffer_size = 96 M
    Key cache miss rate is 1 : 167
    Key buffer free ratio = 66 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 32 M
    Current query_cache_used = 19 M
    Current query_cache_limit = 2 M
    Current Query cache Memory fill ratio = 60.88 %
    Current query_cache_min_res_unit = 4 K
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 256 K
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 1.00 M
    You have had 4910 queries where a join could not use an index properly
    You have had 1 joins without keys that check for key usage after each row
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.
    
    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.
    
    OPEN FILES LIMIT
    Current open_files_limit = 8192 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 1024 tables
    Current table_definition_cache = 800 tables
    You have a total of 14234 tables
    You have 1024 open tables.
    Current table_cache hit rate is 0%
    , while 100% of your table cache is in use
    You should probably increase your table_cache
    You should probably increase your table_definition_cache value.
    
    TEMP TABLES
    Current max_heap_table_size = 32 M
    Current tmp_table_size = 32 M
    Of 263175 temp tables, 24% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 1 M
    Current table scan ratio = 659 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 150
    You may benefit from selective use of InnoDB.
    If you have long running SELECT's against MyISAM tables and perform
    frequent updates consider setting 'low_priority_updates=1'
    If you have a high concurrency of inserts on Dynamic row-length tables
    consider setting 'concurrent_insert=ALWAYS'.
    
    
    One time I increased the value of table cache and set slow query log. After that I optimize tables...that's it.

    Please update asap, since server becomes unstable.
    I'm using centos 6.4, Xeon 8 cores with 16 GB ram.

    Even, I tried default mysql values but it did not work...

    Waiting for your revert,
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Most advice for MySQL tuning will come from other users. It's not officially supported by cPanel. You mentioned that MySQL is unstable. Is it failing? If so, what do you see in your MySQL error log when MySQL fails?

    Thank you.
     
  5. webhosting-k999

    Joined:
    Mar 29, 2014
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    I have seen mysql cpu usage goes 100% sometimes 200% and even 500%. I'm totally helpless to manage it.
    Although I have been adjusting the settings... But no use.

    Can you suggest the best?
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Try running the following command when you notice heavy MySQL usage:

    Code:
    mysqladmin processlist
    Look for excessive usage from specific databases.

    Thank you.
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    adjust my.cnf
    innodb_buffer_pool_size = 800M
    table_open_cache = 7000
    key_buffer_size = 3500M
    thread_cache_size = 20

    then restart
     
Loading...

Share This Page