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.

Requesting help optimizing MySQL settings

Discussion in 'Workarounds and Optimization' started by Ebridge, Nov 15, 2013.

  1. Ebridge

    Ebridge Member

    Joined:
    May 3, 2012
    Messages:
    16
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    I hope someone can advise me on optimizing the MySQL settings...

    MySQLTuner:

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  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.70-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: 1G (Tables: 6244)
    [--] Data in InnoDB tables: 468M (Tables: 4371)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 5022
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 10d 19h 25m 56s (24M q [25.812 qps], 514K conn, TX: 88B, RX: 8B)
    [--] Reads / Writes: 60% / 40%
    [--] Total buffers: 662.0M global + 34.6M per thread (60 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.7G (68% of installed RAM)
    [OK] Slow queries: 0% (9K/24M)
    [OK] Highest usage of available connections: 48% (29/60)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/481.9M
    [OK] Key buffer hit rate: 97.2% (160M cached / 4M reads)
    [OK] Query cache efficiency: 76.4% (13M cached / 17M selects)
    [!!] Query cache prunes per day: 58139
    [OK] Sorts requiring temporary tables: 1% (5K temp sorts / 470K sorts)
    [!!] Joins performed without indexes: 71006
    [!!] Temporary tables created on disk: 32% (304K on disk / 940K total)
    [OK] Thread cache hit rate: 99% (4K created / 514K connections)
    [!!] Table cache hit rate: 0% (5K open / 63M opened)
    [OK] Open file limit used: 14% (6K/48K)
    [OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)
    [!!] InnoDB data size / buffer pool: 468.8M/300.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        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 (> 30.0M, or always use indexes with joins)
        tmp_table_size (> 224M)
        max_heap_table_size (> 224M)
        table_cache (> 5000)
        innodb_buffer_pool_size (>= 468M)
    
    
    my.conf:
    Code:
    [mysqld]
    set-variable = max_connections=60
    log-slow-queries
    safe-show-database
    max_allowed_packet=16M
    local-infile=0
    query_cache_size = 128M
    join_buffer_size = 30M
    tmp_table_size = 224M
    max_heap_table_size = 224M
    thread_cache_size = 4
    table_cache = 9000
    table_open_cache = 5000
    table_definition_cache = 3000
    innodb_buffer_pool_size = 300M
    open_files_limit=48542
    long_query_time = 1
    read_rnd_buffer = 4M
    sort_buffer_size = 256k
    
    Server details:
    Code:
    Total processors: 4
    Processor #1
    Vendor
    GenuineIntel
    Name
    Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
    Speed
    2000.000 MHz
    Cache
    2048 KB
    Processor #2
    Vendor
    GenuineIntel
    Name
    Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
    Speed
    2000.000 MHz
    Cache
    2048 KB
    Processor #3
    Vendor
    GenuineIntel
    Name
    Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
    Speed
    2000.000 MHz
    Cache
    2048 KB
    Processor #4
    Vendor
    GenuineIntel
    Name
    Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz
    Speed
    2000.000 MHz
    Cache
    2048 KB
    Server is running CloudLinux 5 (Hybrid kernel)

    I went through a couple of runs of MySQLTuner, following it's advice, but I feel like I'm tweaking the settings outside of the sweet spot already.

    Any help would be highly appreciated! :)
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You have 32-bit Linux and system with 4GB RAM
    For 4GB you should use 64-bit system,

    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.7G (68% of installed RAM)
    [--] Total buffers: 662.0M global + 34.6M per thread (60 max threads)

    But since you have very bad settings (especially for per thread ones) we can optimize max memory usage a lot
    After the settings below you should have higher memory usage on start, but much slower for each additional thread: 3.6M per thread
    So in sum, your total memory usage for current set max threads (60) will be much lower than 2GB

    anways adjust:
    join_buffer_size = 1M
    read_rnd_buffer = 2M

    query_cache_size = 100M
    tmp_table_size = 25M
    max_heap_table_size = 25M
    thread_cache_size = 20
    table_cache = 9000 - remove that, its the same as one below
    table_open_cache = 6000

    innodb_buffer_pool_size = 600M

    This value keep always higher than
    [!!] InnoDB data size / buffer pool: 468.8M/300.0M

    than InnoDB data size, so higher than 468 MB in this case


    add there also
    key_buffer_size = 500M

    since
    [OK] Key buffer size / total MyISAM indexes: 8.0M/481.9M
     
  3. Ebridge

    Ebridge Member

    Joined:
    May 3, 2012
    Messages:
    16
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    I was suspecting that, MySQLTuner kept telling me to increase the values so I'm glad I stopped increasing values even more and went to this forum :)

    I will let it run for a couple of days and report back.

    Thanks a lot for your advice! Really appreciated.
     
  4. Ebridge

    Ebridge Member

    Joined:
    May 3, 2012
    Messages:
    16
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    Server running noticably smoother now :)
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    I am happy to hear you are have experienced better results. Thank you for updating us with the outcome.
     
  6. Ebridge

    Ebridge Member

    Joined:
    May 3, 2012
    Messages:
    16
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    latest output of MySQLTuner

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  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.70-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: 1G (Tables: 6245)
    [--] Data in InnoDB tables: 481M (Tables: 4468)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 5123
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 10h 54m 39s (7M q [24.637 qps], 151K conn, TX: 27B, RX: 2B)
    [--] Reads / Writes: 64% / 36%
    [--] Total buffers: 1.2G global + 3.6M per thread (60 max threads)
    [OK] Maximum possible memory usage: 1.4G (35% of installed RAM)
    [OK] Slow queries: 0% (3K/7M)
    [OK] Highest usage of available connections: 41% (25/60)
    [OK] Key buffer size / total MyISAM indexes: 500.0M/480.8M
    [OK] Key buffer hit rate: 97.9% (60M cached / 1M reads)
    [OK] Query cache efficiency: 75.2% (4M cached / 5M selects)
    [!!] Query cache prunes per day: 81101
    [OK] Sorts requiring temporary tables: 1% (1K temp sorts / 145K sorts)
    [!!] Joins performed without indexes: 18607
    [!!] Temporary tables created on disk: 32% (113K on disk / 344K total)
    [OK] Thread cache hit rate: 99% (25 created / 151K connections)
    [!!] Table cache hit rate: 0% (6K open / 20M opened)
    [OK] Open file limit used: 15% (7K/48K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [OK] InnoDB data size / buffer pool: 481.5M/600.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 100M)
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 25M)
        max_heap_table_size (> 25M)
        table_cache (> 6000)
     
Loading...

Share This Page