Need some help for my MySQL Server

[DRaver]

I have mysql 5.6.14 running.

Output from mysqltuner.pl :

[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.14
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 972)
[--] Data in InnoDB tables: 1G (Tables: 542)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 1008K (Tables: 19)
[!!] Total fragmented tables: 82

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12d 21h 43m 44s (15M q [13.756 qps], 273K conn, TX: 18B, RX: 5B)
[--] Reads / Writes: 34% / 66%
[--] Total buffers: 22.1G global + 2.9M per thread (300 max threads)
[OK] Maximum possible memory usage: 22.9G (73% of installed RAM)
[OK] Slow queries: 0% (1/15M)
[OK] Highest usage of available connections: 17% (51/300)
[OK] Key buffer size / total MyISAM indexes: 4.0G/1.3G
[OK] Key buffer hit rate: 100.0% (22M cached / 1K reads)
[OK] Query cache efficiency: 77.5% (7M cached / 10M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (5K temp sorts / 368K sorts)
[OK] Temporary tables created on disk: 11% (122K on disk / 1M total)
[OK] Thread cache hit rate: 99% (51 created / 273K connections)
[!!] Table cache hit rate: 0% (1K open / 5M opened)
[OK] Open file limit used: 19% (1K/10K)
[OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)
[OK] InnoDB data size / buffer pool: 1.3G/16.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    table_cache (> 5000)

My my.cnf :

[client]
port                            = 3306
socket                          = /var/run/mysqld/mysqld.sock

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 16G

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir        = /opt/mysql/server-5.6
datadir = /opt/mysql/server-5.6/data
port = 3306
# server_id = .....
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
bind-address  = 127.0.0.1
default-storage-engine = InnoDB
user        = mysql
tmpdir        = /tmp
tmp_table_size=32M
max_heap_table_size=32M
key_buffer = 2048M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 256
myisam-recover = BACKUP
read_rnd_buffer_size = 256K
max_connections = 300
table_open_cache = 5000
sort_buffer_size = 256K
read_buffer_size = 256K
key_buffer_size = 4096M
join_buffer_size = 2048K
query_cache_limit    = 64M
query_cache_size = 2048M
query_cache_type=1
#log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 2
expire_logs_days = 10
max_binlog_size = 100M
innodb_file_per_table
innodb_data_file_path = ibdata1:10G:autoextend
innodb_log_buffer_size=16M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
innodb_log_file_size = 4GB
innodb_buffer_pool_size = 16G
innodb_additional_mem_pool_size = 8M
innodb_support_xa = 0
innodb_lock_wait_timeout = 100
query_cache_min_res_unit = 2K

The only problem seems to me that message:
[!!] Table cache hit rate: 0% (1K open / 5M opened)

I think table_open_cache = 5000 is high but 0% cache hit is not good.

How can I fix my problem. Any performance tricks for MySQL 5.6 ?

 

[UrlJet]

Careful not to read too much into mysqltuner tuner results from mysql 5.6

http://forums.cpanel.net/f354/case-90317-warning-using-mysqltuner-mysql-5-6-a-389971.html

 

[Floren]

Mysqltuner is nothing fancy, unfortunately. The real way to tune your server is monitor closely the query logs and see what is eating your server up. Then, gradually make adjustments until you obtain proper results. This is an ongoing process, you have to repeat it every 6 months at least as your forums usage change constantly. It involves a lot of work effort but this is the only way you will get your MySQL server tuned properly.