<blockquote class="quote light-black dark-border-color"><div class="quote light-border-color">
<div class="quote-author" style="font-weight: bold;">David Rees wrote:</div>
<div class="quote-message">
On 9/6/07, David Rees <drees76@gmail.com> wrote:
> Given the limited memory you have to work with, I would try disabling
> the query cache and using all that memory in the key buffer.
>
> Try:
> key_buffer = 32M
> query_cache_size = 0
Couple more data points to check. Grab the output of this query before
and after the big query with each config setting you try: (existing
and new with query cache disabled and key buffer increased to 32 and
64 M)
show global status where variable_name like 'select%' or variable_name
like 'key%' or variable_name like 'qcache%';
That will help us determine if the key buffer size is appropriate and
whether the settings are too large, too small and helping or hurting.
</div>
</div></blockquote>
<p>I removed duplicate recording entries so my query time has dropped dramatically (~30 seconds to ~6) and the number of examined rows went from ~750,000 to ~150,000. Hopefully the info you wanted is still useful. To be clear, these numbers are after I removed the duplicate recording entries. Each pair is before the query and after the query and each pair was run on a freshly started mysqld. If this suggests something to you, please let me know so I can adjust my my.cnf accordingly.</p>
<pre>
key_buffer = 16M, query_cache_size = 16M
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 14496 |
| Key_blocks_used | 3 |
| Key_read_requests | 58 |
| Key_reads | 31 |
| Key_write_requests | 87 |
| Key_writes | 30 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16764504 |
| Qcache_hits | 0 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 14 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 8 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 12 |
+-------------------------+----------+
+-------------------------+----------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 14372 |
| Key_blocks_used | 134 |
| Key_read_requests | 12543 |
| Key_reads | 164 |
| Key_write_requests | 857 |
| Key_writes | 30 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16489664 |
| Qcache_hits | 8 |
| Qcache_inserts | 8 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 24 |
| Qcache_queries_in_cache | 8 |
| Qcache_total_blocks | 30 |
| Select_full_join | 1 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 19 |
+-------------------------+----------+
key_buffer = 16M, query_cache_size = 0
+-------------------------+-------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 14495 |
| Key_blocks_used | 3 |
| Key_read_requests | 59 |
| Key_reads | 32 |
| Key_write_requests | 87 |
| Key_writes | 30 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 13 |
+-------------------------+-------+
+-------------------------+-------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 14373 |
| Key_blocks_used | 133 |
| Key_read_requests | 12538 |
| Key_reads | 163 |
| Key_write_requests | 857 |
| Key_writes | 30 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Select_full_join | 1 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 16 |
+-------------------------+-------+
key_buffer = 32M, query_cache_size = 0
+-------------------------+-------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 28995 |
| Key_blocks_used | 3 |
| Key_read_requests | 22 |
| Key_reads | 10 |
| Key_write_requests | 32 |
| Key_writes | 10 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 9 |
+-------------------------+-------+
+-------------------------+-------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 28872 |
| Key_blocks_used | 132 |
| Key_read_requests | 12537 |
| Key_reads | 162 |
| Key_write_requests | 857 |
| Key_writes | 30 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Select_full_join | 1 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 15 |
+-------------------------+-------+
key_buffer = 64M, query_cache_size = 0
+-------------------------+-------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 57990 |
| Key_blocks_used | 3 |
| Key_read_requests | 29 |
| Key_reads | 14 |
| Key_write_requests | 43 |
| Key_writes | 14 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 10 |
+-------------------------+-------+
+-------------------------+-------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 57868 |
| Key_blocks_used | 131 |
| Key_read_requests | 12536 |
| Key_reads | 161 |
| Key_write_requests | 857 |
| Key_writes | 30 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Select_full_join | 1 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 15 |
+-------------------------+-------+
</pre>
<br><hr align="left" width="300">
View this message in context: <a href="http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a12534736">Re: Slow MySQL query after delete</a><br>
Sent from the <a href="http://www.nabble.com/mythtv-users-f15550.html">mythtv-users mailing list archive</a> at Nabble.com.<br>