Determine the average size of your applications cached queries by taking the query cache used memory and divide by the number of queries in the cache.

The former is determined by query_cache_size system variable, minus the qcache_free_memory status variable.

Each of these three things can be measured, and once set - assuming your application footprint doesn't change dramatically - should remain fairly static. The query_cache_type variable should be set to ON, and the query_cache_size should be non-zero.

Use this command: show variables like 'query%'; ------------------------------ ----------- | Variable_name | Value | ------------------------------ ----------- | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 1024 | | query_cache_size | 256000000 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | ------------------------------ ----------- 7 rows in set (0.00 sec) Recall that we described above the maximum size query that the query cache will allow to be cached. If there are very large reports with large result sets, you probably don't want them to clog up your query cache. The biggest problem most untuned query caches will have is either that they (a) aren't big enough, or (b) have fragmentation.

One question: How is it possible to get N previous events using the SHOW BINLOG EVENTS command? You cannot get the previous five events using and tail its output.

For example, the position is 999 and I want to analyze the previous five events. Q: We are having issues with inconsistencies over time.

My SQL's query cache is an impressive piece of engineering if sometimes misunderstood.

Keeping it optimized and used efficiently can make a big difference in the overall throughput of your application, so it's worth taking a look under the hood, understanding it, and then keeping it tuned optimally.

If you believe it hasn’t received the event in a timely fashion, check the error log files on both the master and slave for connection errors.

If there is no error message, or if the message doesn’t provide enough information to solve the issue, use the network troubleshooting methods discussed in the “Troubleshooting hardware resource usage” webinar.

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align Dev Ops for your applications with Dev Ops for your SQL Server databases.

Discover true Database Dev Ops, brought to you in partnership with Redgate. First, I want to thank everybody for attending the August 25 webinar.

Q: Sometimes when IO is slow and during lag we see info: Reading event from the relay log “Waiting for master to send event” — How do we troubleshoot to get more details.