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 load seems very high

Discussion in 'Workarounds and Optimization' started by shufil, Mar 19, 2014.

  1. shufil

    shufil Well-Known Member

    Joined:
    Mar 19, 2014
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello All ,

    Am Shufil , working as a Linux system admin . we are using WHM servers , i regularly watching all forms for finding a solution .
    Currently i have a issue . our server running with 4 core cpu and 4 GB Ram , our MySQL DB size 5.6 GB , problem is our MySQL load seems very high , i need to check our my.cnf configure correctly or not . also you can see mysql tuning result .

    MySQL tuning scan Result .

    Code:
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increasing the query_cache size over 128M may reduce performance
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 256M) [see warning above]
        join_buffer_size (> 1.0M, or always use indexes with joins)
        table_cache (> 4096)
        innodb_buffer_pool_size (>= 12G)
    Code:
    [mysqld]
    #innodb_force_recovery = 4
    old_passwords=1
    tmpdir=/home/mysqltmp
    datadir=/var/lib/mysql
    skip-locking
    #skip-networking
    #tmp_table_size = 1024M
    #max_heap_table_size = 1024M
    tmp_table_size = 256M
    max_heap_table_size = 256M
    query_cache_limit=20M
    query_cache_size=128M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=100
    max_connections=150
    innodb_file_per_table=1
    
    innodb_buffer_pool_size = 1073741824
    collation_server=utf8_unicode_ci
    character_set_server=utf8
    
    delayed_insert_timeout=40
    
    interactive_timeout=10
    wait_timeout=500
    connect_timeout=20
    thread_cache_size=128
    key_buffer=256M ## 32MB for every 1GB of RAM
    join_buffer=1M
    max_connect_errors=20
    max_allowed_packet=64M
    table_cache=4096
    table_definition_cache=1024
    record_buffer=2M
    sort_buffer_size=4M ## 1MB for every 1GB of RAM
    read_buffer_size=4M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=4M  ## 1MB for every 1GB of RAM
    thread_concurrency=4 ## Number of CPUs x 2
    myisam_sort_buffer_size=64M
    server-id=1
    
    log-slow-queries=/home/mysqltmp/mysql_slow_queries.log 
    
    open_files_limit=36116
    [mysql.server]
    user=mysql
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    
    [mysqldump]
    quick
    max_allowed_packet=32M
    
    [mysql]
    no-auto-rehash
    Regards,
    Shufil
    ______________________________________________________
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,787
    Likes Received:
    665
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You may want to post the full output of the tuner, as opposed to just the recommendations. It might help users to better offer you advice on settings to change.

    Thank you.
     
  3. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    It would be more helpful for other users if you posted the whole output of mysqltuner that is needed so users can assist you in better way.

    The output has usually General Statistics, Storage Engine Statistics and Performance Metrics.

    edit:

    sorry for the reply, didn't know that Michael had replied.
     
    #3 Archmactrix, Mar 19, 2014
    Last edited: Mar 19, 2014
  4. shufil

    shufil Well-Known Member

    Joined:
    Mar 19, 2014
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Heloo ,

    Sorry for delay , below the full scan result .

    Code:
     ./mysqltuner.pl
    
     >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.73-cll
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 15G (Tables: 4752)
    [--] Data in InnoDB tables: 12G (Tables: 1841)
    [--] Data in MEMORY tables: 0B (Tables: 4)
    [!!] Total fragmented tables: 138
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 17d 6h 14m 35s (98M q [65.728 qps], 1M conn, TX: 246B, RX: 15B)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 1.6G global + 13.2M per thread (150 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 3.6G (89% of installed RAM)
    [OK] Slow queries: 0% (8K/98M)
    [OK] Highest usage of available connections: 28% (43/150)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/620.0M
    [OK] Key buffer hit rate: 99.8% (636M cached / 1M reads)
    [OK] Query cache efficiency: 55.6% (51M cached / 92M selects)
    [!!] Query cache prunes per day: 134269
    [OK] Sorts requiring temporary tables: 0% (200 temp sorts / 8M sorts)
    [!!] Joins performed without indexes: 29849
    [!!] Temporary tables created on disk: 49% (3M on disk / 7M total)
    [OK] Thread cache hit rate: 99% (43 created / 1M connections)
    [!!] Table cache hit rate: 0% (4K open / 1M opened)
    [OK] Open file limit used: 16% (5K/36K)
    [OK] Table locks acquired immediately: 99% (58M immediate / 58M locks)
    [!!] InnoDB data size / buffer pool: 12.8G/1.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 128M)
        join_buffer_size (> 1.0M, or always use indexes with joins)
        table_cache (> 4096)
        innodb_buffer_pool_size (>= 12G)
    
    Regards,
    Shufil
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You got 15GB in MyISAM and 12GB in InnoDB
    For MyISAM, key_buffer_size must be higher than 620M if you use all contents indexes, so
    [OK] Key buffer size / total MyISAM indexes: 256.0M/620.0M

    set key_buffer=650M


    For innodb you need to set as much as you can in your case, innodb_buffer_pool_size
    but since you don't have RAM, you got options, or you get more RAM, or you compress some of your data, or convert it to MyISAM, and increase key_buffer


    and delete those vars below:
    record_buffer=2M
    sort_buffer_size=4M ## 1MB for every 1GB of RAM
    read_buffer_size=4M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=4M ## 1MB for every 1GB of RAM
    thread_concurrency=4 ## Number of CPUs x 2

    no need to increase them at all
     
  6. shufil

    shufil Well-Known Member

    Joined:
    Mar 19, 2014
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello,

    Thanks for the reply and support .

    Ok, i Will increase key_buffer , but can you advice me how can i compress database , can i get this option in phpmyadmin ?.

    Regards,
    Shufil
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    The thing is,
    for fast query execution it's important to have indexes (MyISAM) and data+indexes (Innodb) in memory, RAM

    MyISAM places only indexes in RAM, InnoDB both, indexes and data
    so if you have only 4GB, it would be better to convert InnoDB databases to MyISAM

    when you do that, please rerun mysqltuner.pl and I will suggest adjusted settings
     
  8. shufil

    shufil Well-Known Member

    Joined:
    Mar 19, 2014
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello,

    Before its running with MyISAM so our site regularly running very slow so we are changed from MyISAM to InnoDB because MyISAM Lock table base, any way after the change site running fine . may the problem is running site without indexes .
    If we add 2 GB memory extra , can we expect it will run without any slow

    Regards,
    Shufil
     
  9. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Yes, but in this case you should convert to InnoDB only the table which had problems with locking
    And previously you might not had properly configured myisam key buffer size, so that might be the problem too

    Anyways, since you have too less RAM, your option is to get your data/indexes smaller to fit in RAM, I suggest converting back to MyISAM, increase key buffer size to fit indexes to database
    and then do optimize of queries/indexes when necessary
    or if needed convert only tables that got locking problems to InnoDB
     
  10. shufil

    shufil Well-Known Member

    Joined:
    Mar 19, 2014
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello,

    Sorry for late ,
    Do you know why the site slow with InnoDB , becouse InnoDB is 5.5 defualt engine in mysql 5.5 right ?.

    Regards,
    Shufil
     
  11. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Becouse you got too small buffers to fit InnoDB data/indexes to RAM
    [!!] InnoDB data size / buffer pool: 12.8G/1.0G

    and you are using 32-bit system
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
     
    #11 thinkbot, Apr 2, 2014
    Last edited: Apr 2, 2014
  12. shufil

    shufil Well-Known Member

    Joined:
    Mar 19, 2014
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello,

    Currently our innodb_buffer_pool_size = 1073741824 , so we need to increase to 1573741824 , is this get any result .

    Regards,
    Shufil
     
  13. shufil

    shufil Well-Known Member

    Joined:
    Mar 19, 2014
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello,

    Can this increase without increase our primary ram . show process list will give current mysql activity , but how can we know each query how many memory consuming or how many load taken , can we know this any command or using any external tool .

    Regards,
    Shufil
     
  14. shufil

    shufil Well-Known Member

    Joined:
    Mar 19, 2014
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello,

    Value changed to innodb_buffer_pool_size = 1573741824 , before mysql running with - Uptime: 4083648 Threads: 5 Questions: 268046639 Slow queries: 26915 Opens: 2908061 Flush tables: 1 Open tables: 4096 Queries per second avg: 65.639

    after the change
    Uptime: 2585 Threads: 2 Questions: 82706 Slow queries: 2 Opens: 231 Flush tables: 1 Open tables: 224 Queries per second avg: 31.994
    This time mysql show process list seems only eximstat .but load seems 31.94 .

    Regards,
    Shufil
     
Loading...

Share This Page