mysql cache optimization?

[ammarzubair]

does anyone here know how to optimize mysql query caching?

it is enabled but too many queries are not being cached. any help is appreciated

STATUS Value
Qcache_free_blocks 1
Qcache_free_memory 261990728
Qcache_hits 1746957245
Qcache_inserts 31137810
Qcache_lowmem_prunes 18678760
Qcache_not_cached 89856558
Qcache_queries_in_cache 3147
Qcache_total_blocks 6440



Variable Value
query_alloc_block_size 8192
query_cache_limit 33554432
query_cache_min_res_unit 4096
query_cache_size 268435456
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192


[MENTION=21338]mubaidr[/MENTION] [MENTION=4177]NaNoW[/MENTION] [MENTION=104069]puppet[/MENTION]

 

[puppet]

500 rupees (sm1)

 

[ammarzubair]

500 rupees (sm1)

chal aglay mahine le lena abhi jawab to bta

 

[fpga123]

it would be best to implement application level query caching rather than in the DB as even if you optimize the cache algorithm, it will have adverse effects for the memory management of the instance. In application level query caching you will have plethora of other optimizations possible like you can cache it in redis or memcache, do selective caching, define custom timeouts for cash renewals. For cash renewals you can add events and attach a function pointer to it for hassle free updating.

The overall implementation will depend upon your stack, in the famous ones there are good libraries available to do just that.