Skip navigation links

MySQL Forums :: Newbie :: MySQL variables - mysqltuner.pl optimization help


Advanced Search

MySQL variables - mysqltuner.pl optimization help
Posted by: Marin Smoljan ()
Date: March 12, 2013 09:33AM

Hi
I am new to linux and MySQl,

I have MySQL 5.1 on CentOS 6.3 running Cpanel and have problems editing my.cfn.

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=1open_files_limit=2804


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 editing this and get it working, because if I add changes to it mysql server fails to start

Thanks

Options: ReplyQuote


Subject Written By Posted
MySQL variables - mysqltuner.pl optimization help Marin Smoljan 03/12/2013 09:33AM
Re: MySQL variables - mysqltuner.pl optimization help Peter Brawley 03/12/2013 09:45AM
Re: MySQL variables - mysqltuner.pl optimization help Marin Smoljan 03/12/2013 09:53AM
Re: MySQL variables - mysqltuner.pl optimization help Peter Brawley 03/12/2013 10:22AM
Re: MySQL variables - mysqltuner.pl optimization help Marin Smoljan 03/12/2013 10:26AM
Re: MySQL variables - mysqltuner.pl optimization help Marin Smoljan 03/12/2013 10:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.