[mythtv-users] Slow MySQL query after delete
crs23
pvr at groundhog.pair.com
Fri Sep 7 02:10:51 UTC 2007
David Rees wrote:
>
> On 9/6/07, David Rees 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.
>
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.
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 |
+-------------------------+-------+
--
View this message in context: http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a12534736
Sent from the mythtv-users mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20070906/6e34e185/attachment-0001.htm
More information about the mythtv-users
mailing list