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!

Edit MySQL variables - mysqltuner.pl optimization help

Discussion in 'Workarounds and Optimization' started by mehnihma, Mar 11, 2013.

  1. mehnihma

    mehnihma Well-Known Member

    Joined:
    Dec 15, 2012
    Messages:
    57
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Hi
    I am new to linux and CPanel,

    I am trying to edit /etc/my.cfn and when I have added some variables MySQL could not start. So I reverted to backup. How it should look like?

    when I open /etc/my.cnf I have:

    Code:
    [mysqld]
    innodb_file_per_table=1
    I have run mysqltuner.pl and got this:

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.68-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1M (Tables: 84)
    [--] Data in InnoDB tables: 223M (Tables: 533)
    [!!] Total fragmented tables: 24
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 8m 21s (15K q [31.764 qps], 58 conn, TX: 15M, RX: 4M)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 449.2M (5% of installed RAM)
    [OK] Slow queries: 0% (0/15K)
    [OK] Highest usage of available connections: 3% (5/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/456.0K
    [OK] Key buffer hit rate: 97.1% (2K cached / 85 reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
    [!!] Joins performed without indexes: 6
    [OK] Temporary tables created on disk: 14% (716 on disk / 5K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 7% (64 open / 862 opened)
    [OK] Open file limit used: 0% (6/1K)
    [OK] Table locks acquired immediately: 100% (37K immediate / 37K locks)
    [!!] InnoDB data size / buffer pool: 223.9M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        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)
        innodb_buffer_pool_size (>= 223M)
    

    Can you help me setup it?

    Thanks
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #1 mehnihma, Mar 11, 2013
    Last edited: Mar 12, 2013
  2. mehnihma

    mehnihma Well-Known Member

    Joined:
    Dec 15, 2012
    Messages:
    57
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Re: edit my.cnf

    Hi

    When I view my.cnf with SSH I get only

    [mysqld]
    innodb_file_per_table=1

    How can I edit it and see all variables? Because if I add something in this file (/etc/my.cnf ) my sql fails

    Thanks
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. mehnihma

    mehnihma Well-Known Member

    Joined:
    Dec 15, 2012
    Messages:
    57
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    If anyone can help with this?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  4. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    141
    Likes Received:
    2
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Use this:

    [mysqld]
    innodb_file_per_table = 1
    query_cache_size = 8M
    join_buffer_size = 128K
    thread_cache_size = 4
    table_cache = 64
    innodb_buffer_pool_size = 223M

    That should do the trick. Then run service mysql restart and recheck in 24 hours.
    You may wish to optimise and repair tables too using mysqlcheck -Aor
     
    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