Query cache prunes per day 50k and cache hit rate 0%

[josuablirup]

Hey Everyone

We currently have an issue with the settings in our MySQL database:

Stats:
4 cores
16GB ram

Below are the results from mysqltuner:

[OK] Currently running supported MySQL version 5.5.30-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 626M (Tables: 919)
[--] Data in InnoDB tables: 360M (Tables: 2342)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 75)
[!!] Total fragmented tables: 79

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 14h 35m 41s (7M q [31.747 qps], 68K conn, TX: 30B, RX: 1B)
[--] Reads / Writes: 51% / 49%
[--] Total buffers: 8.7G global + 3.2M per thread (650 max threads)
[OK] Maximum possible memory usage: 10.8G (69% of installed RAM)
[OK] Slow queries: 0% (3/7M)
[OK] Highest usage of available connections: 3% (20/650)
[OK] Key buffer size / total MyISAM indexes: 512.0M/53.1M
[OK] Key buffer hit rate: 100.0% (38M cached / 17K reads)
[OK] Query cache efficiency: 89.7% (5M cached / 6M selects)
[!!] Query cache prunes per day: 51806
[OK] Sorts requiring temporary tables: 0% (20 temp sorts / 75K sorts)
[!!] Joins performed without indexes: 9480
[OK] Temporary tables created on disk: 13% (34K on disk / 258K total)
[OK] Thread cache hit rate: 99% (20 created / 68K connections)
[!!] Table cache hit rate: 0% (3K open / 2M opened)
[OK] Open file limit used: 12% (2K/16K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 360.6M/8.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 128M)
join_buffer_size (> 1.0M, or always use indexes with joins)
table_cache (> 8000)

The my.cnf looks like this:

[mysqld]
default_storage_engine=InnoDB
innodb_file_per_table=1
local-infile=0
tmpdir =/dev/shm
max_connections = 650
max_user_connections=300
key_buffer_size = 512M
myisam_sort_buffer_size = 64M
log-slow-queries
innodb_file_per_table=1
key_buffer_size = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 256K
table_cache = 8000 #was 4000
thread_cache_size = 384
wait_timeout = 120 #was 20
connect_timeout = 120 #was 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 8
concurrent_insert = 2
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 7M
query_cache_size = 128M #was 64M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
max_write_lock_count = 16
innodb_buffer_pool_size=8G #75 % of RAM
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=4 # of cpus
innodb_flush_method=O_DIRECT
innodb_additional_mem_pool_size=20M
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M

Please advice?

 

[arunsv84]

Your Maximum possible memory usage is already 69% of installed RAM. So I wont recommend increasing the value. "query_cache_size" is the amount of RAM MySQL can use to cache the results of database queries. Results stored in the query cache are returned much faster than normal selects, so this variable can significantly speed things up. I would recommend you to keep this setting. But If you have enough free memory, it's safe to increase query_cache size.

Cheers!!!