Edit MySQL variables - mysqltuner.pl optimization help

[mehnihma]

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:

[mysqld]
innodb_file_per_table=1

I have run mysqltuner.pl and got this:

-------- 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

 

[mehnihma]

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

 

[mehnihma]

If anyone can help with this?

 

[ES - George]

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