mysqltuner result help/info Table cache hit rate: 0%

[Schwibby]

hello ! i've read rthe forum and see the mysql video but not understand very well....

can i ask if my.cnf is ok for my server ? i've shared hosting server 6gb ram 6vcpu xeon e5504 2.2ghz

this is my.cnf

[email protected] [/usr/local/cpanel/3rdparty/mysqltuner]# cat /etc/my.cnf
[mysqld]
set-variable = max_connections=200
query_cache_type=1
query_cache_size=64M
query_cache_limit=1M
sort_buffer_size=2M
read_rnd_buffer_size=512K
tmp_table_size=64M
max_heap_table_size=64M
thread_cache_size=24
key_buffer_size=16M
table_cache=192
join_buffer_size=2M
log-slow-queries=/var/lib/mysql/slow.log
innodb_buffer_pool_size=80M
innodb_file_per_table=1
open_files_limit=13460
max_allowed_packet=128M

and this the result of mysql tuner

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 461M (Tables: 2032)
[--] Data in InnoDB tables: 69M (Tables: 239)
[!!] Total fragmented tables: 119

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 0h 32m 26s (7M q [82.111 qps], 118K conn, TX: 25B, RX: 1B)
[--] Reads / Writes: 86% / 14%
[--] Total buffers: 226.0M global + 4.9M per thread (200 max threads)
[OK] Maximum possible memory usage: 1.2G (20% of installed RAM)
[OK] Slow queries: 0% (2/7M)
[OK] Highest usage of available connections: 10% (20/200)
[OK] Key buffer size / total MyISAM indexes: 16.0M/130.2M
[OK] Key buffer hit rate: 99.3% (131M cached / 917K reads)
[OK] Query cache efficiency: 82.3% (5M cached / 6M selects)
[!!] Query cache prunes per day: 474342
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 183K sorts)
[!!] Joins performed without indexes: 1211
[!!] Temporary tables created on disk: 33% (75K on disk / 222K total)
[OK] Thread cache hit rate: 99% (20 created / 118K connections)
[!!] Table cache hit rate: 0% (192 open / 1M opened)
[OK] Open file limit used: 2% (367/13K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 69.1M/80.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 64M)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    table_cache (> 192)

yesterday i've adjust setting but the result are the same... can i modyfy add or delete something ??

thanks

 

[thinkbot]

[mysqld]

wait_timeout = 30
connect_timeout = 1
local-infile=0

open_files_limit=20000

max_connections=200

key_buffer_size = 1G
max_allowed_packet=20M

query_cache_size=30M
query_cache_limit=1M
tmp_table_size=50M
max_heap_table_size=50M
thread_cache_size=50

table_open_cache = 3000
table_definition_cache = 2000

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

sort_buffer_size=256K
join_buffer_size=2M
read_rnd_buffer_size=4M

max_write_lock_count = 10
concurrent_insert=2


innodb_buffer_pool_size=200M
innodb_file_per_table=1
innodb_buffer_pool_instances = 4
innodb_flush_log_at_trx_commit = 2

table cache hit rate 0% means that your script is creating many temp tables and you need to review your queries

wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

or for old slow file
./pt-query-digest /var/lib/mysql/slow.log > slow.txt

 

[Schwibby]

hello thinkbot,

first of all thank you very much for the quick response.

the code you have posted is to replace the mine to give to make a test?

the pt-query-digest is a tool to test the slow query ?

 

[thinkbot]

yes

pt-query-digest is to review slow queries