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.

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:
    6
    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
     
    #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:
    6
    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
     
  3. mehnihma

    mehnihma Well-Known Member

    Joined:
    Dec 15, 2012
    Messages:
    57
    Likes Received:
    1
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    If anyone can help with this?
     
  4. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    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
     
Loading...

Share This Page