Hello Reindl,

On 7/15/2013 10:28 AM, Reindl Harald wrote:


Am 15.07.2013 16:00, schrieb Hartmut Holzgraefe:
On 15.07.2013 15:31, Egoitz Aurrekoetxea wrote:
Could Mysql cache cause a performance penalty, when invalidating
queries of a big Mysql cache zone?

can, and *will* ... see also http://dom.as/tech/query-cache-tuner/

Optimal size for your query cache: 0

thats ridiculous

cache is locked while entries are being purged to prevent handing out
cached results that may already be out-of-date, and the more active
cache entries that need to be purged the longer it stays locked ...

but in most cases the benefit outbeats this overhead massive
since you have more read than write

[OK] Query cache efficiency: 93.6% (40M cached / 42M selects)
nobody can tell me that all these 40M queries would have been
faster without cache by reduce the overhead


I just want to verify your that your efficiency formula.

Query Cache Efficiency = Qcache_hits / (Qcache_hits + Com_select)

The way yours is described it looks like it may be a simple ratio of Qcache_hits to Com_select which would be inaccurate.


Another way of judging the efficiency of the cache is to compare how many times a cached result is actually reused (on average). Compute a very broad reuse rate with:

Reuse estimate = Qcache_hits/Qcache_inserts

If that ratio is under about 5 you have very poor reuse. If it is less than 1, you are getting no reuse at all for some of the results you are caching. If that's the case, strongly consider disabling the cache or using more selective caching techniques as already discussed.



I'm not trying to pick on you, Reindl. Your cache may be doing splendidly well. I just didn't want anyone else to see your efficiency rating and derive the wrong formula on their own.

Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql

Reply via email to