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.

MySQLTuner Recommendations Help

Discussion in 'Workarounds and Optimization' started by JoshC, Dec 28, 2011.

  1. JoshC

    JoshC Registered

    Joined:
    Dec 28, 2011
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi everyone, I need help optimizing MySQL. :)

    I have a Phenom II quad core dedicated server, with 4 GB of RAM.

    The contents of my my.conf file are the following:

    Code:
    [mysqld]
    local-infile=0
    max_connections = 250
    MySQLTuner Output:

    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
    Please enter your MySQL administrative login: 
    Please enter your MySQL administrative password:
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.67-community
    [!!] 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: 5G (Tables: 227)
    [--] Data in MEMORY tables: 380K (Tables: 3)
    [!!] InnoDB is enabled but isn't being used
    [!!] Total fragmented tables: 35
    
    -------- Security Recommendations  -------------------------------------------
    ERROR 1142 (42000) at line 1: SELECT command denied to user '****'@'localhost' 
    
    for table 'user'
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 23m 53s (61K q [42.595 qps], 2K conn, TX: 2B, RX: 17M)
    [--] Reads / Writes: 76% / 24%
    [--] Total buffers: 34.0M global + 2.6M per thread (250 max threads)
    [OK] Maximum possible memory usage: 689.3M (18% of installed RAM)
    [OK] Slow queries: 0% (0/61K)
    [OK] Highest usage of available connections: 3% (8/250)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/1.3G
    [OK] Key buffer hit rate: 95.9% (1M cached / 70K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (14 temp sorts / 4K sorts)
    [!!] Joins performed without indexes: 3173
    [OK] Temporary tables created on disk: 0% (1 on disk / 5K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 6% (64 open / 980 opened)
    [OK] Open file limit used: 9% (118/1K)
    [OK] Table locks acquired immediately: 99% (77K immediate / 77K locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        thread_cache_size (start at 4)
    table_cache (> 64)
    I'd like to know how to edit my my.conf file to reflect the recommended changes.

    If you need any other info, please let me know.

    Thanks in advanced, happy new year!

    -Josh
     
    #1 JoshC, Dec 28, 2011
    Last edited: Dec 28, 2011
  2. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    If you have shell access you can use a program like vi or nano to edit your my.cnf. (Note, there is no 'o' in 'my.cnf'). Nano is generally easier unless you already have experience with vi, so:

    Code:
    # nano /etc/my.cnf
    Then type in all your settings, hit CTRL+X, tell it to save when it asks... and viola!... you have edited your my.cnf.

    Note, mysql was only running for 23 minutes at the time you ran mysqltuner, so the recommendations are going to be wrong. You should really wait until mysql has been running at least 24 hours before running mysqltuner. Despite that, here are some things that should be in your my.cnf that will be an improvement over your mostly empty file. You can just copy & paste this, making sure you erase what you already have first.

    Code:
    [mysqld]
    local-infile=0                  # security tweak
    
    max_connections         = 250   # cPanel default is 500
    max_allowed_packet      = 16M   # Default 1M
    
    slow-query-log                  # because enquiring minds want to know.
    long-query-time         = 5     # Default 10
    
    ######### Performance Tweaks ##########
    
    thread_cache_size       = 8    # Default 0
    table_cache             = 512  # Default 64
    
    key_buffer_size         = 1024M # Default 8M
    sort_buffer_size        = 256K  # Default 2M
    join_buffer_size        = 256K  # Default 128K
    read_buffer_size        = 256K  # Default 128K.
    
    query_cache_size        = 16M  # Default 0
    
    ######### End Performance Tweaks #######
    
    Restart mysql, wait 24 hours, and post a new mysqltuner. We'll see what needs to be tweaked from there.
     
  3. JoshC

    JoshC Registered

    Joined:
    Dec 28, 2011
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I'll do that. I'll paste my new results tomorrow.

    Thank you. :)
     
  4. crazyaboutlinux

    crazyaboutlinux Well-Known Member

    Joined:
    Nov 3, 2007
    Messages:
    938
    Likes Received:
    0
    Trophy Points:
    16
    hey JoshC

    any results ??
     
  5. JoshC

    JoshC Registered

    Joined:
    Dec 28, 2011
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi, sorry for my late reply. :)

    Here are the results after I added the above to the my.cnf file.

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.67-community
    [!!] 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: 5G (Tables: 227)
    [--] Data in MEMORY tables: 1M (Tables: 3)
    [!!] InnoDB is enabled but isn't being used
    [!!] Total fragmented tables: 35
    
    -------- Security Recommendations  -------------------------------------------
    ERROR 1142 (42000) at line 1: SELECT command denied to user '****'@'localhost' for table 'user'
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 17h 15m 28s (7M q [33.357 qps], 366K conn, TX: 5B, RX: 2B)
    [--] Reads / Writes: 53% / 47%
    [--] Total buffers: 1.0G global + 1.1M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.6G (42% of installed RAM)
    [OK] Slow queries: 0% (25/7M)
    [OK] Highest usage of available connections: 24% (123/500)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/1.4G
    [OK] Key buffer hit rate: 99.7% (131M cached / 347K reads)
    [OK] Query cache efficiency: 64.1% (3M cached / 5M selects)
    [!!] Query cache prunes per day: 3222
    [!!] Sorts requiring temporary tables: 31% (9K temp sorts / 31K sorts)
    [OK] Temporary tables created on disk: 3% (1K on disk / 32K total)
    [OK] Thread cache hit rate: 99% (205 created / 366K connections)
    [OK] Table cache hit rate: 46% (512 open / 1K opened)
    [OK] Open file limit used: 24% (613/2K)
    [OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
    Variables to adjust:
        query_cache_size (> 16M)
        sort_buffer_size (> 256K)
        read_rnd_buffer_size (> 256K)
    I don't know if this is any relevant, but I run a vBulletin forum, with 30k members and about 2M posts. ;)

    Thanks again, happy new year!

    -Josh
     
    #5 JoshC, Dec 31, 2011
    Last edited: Dec 31, 2011
  6. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi JoshC,

    Can you check that your max_connections is actually set to 250? Mysqltuner is showing 500.

    Do you know how to optimize indexes using SQL's EXPLAIN statement? If so, please follow cPanelTristan's instruction's here to enable your slow query log in MySQL 5.0.x. Optimizing indexes/queries is preferable to increasing sort related buffers, so I recommend that first if you are able.

    Here's some recommended updates to my.cnf:

    Add this: skip-innodb
    Bump table_cache up to 1024.
    Bump key_buffer_size to 1536M
    Bump query_cache_size to 32M.

    IF you don't know how to optimize indexes, then you can also:
    Add this: read_rnd_buffer_size=384K
    Bump sort_buffer_size to 384K
     
  7. JoshC

    JoshC Registered

    Joined:
    Dec 28, 2011
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Thank you, alphawolf50. :)

    I don't know how to optimize indexes, I'll look into it, but in the meantime I'll use the settings you suggested for it. ;)

    I had the max_connections to 250, but I changed it to 500 (since I saw cPanel uses 500 by default, I thought it was maybe the best configuration), I'll change it back to 250.

    I'll make the suggested changes and I'll let you know if I need any more help.

    Thanks again for your assistance. :)
     
Loading...

Share This Page