[mythtv-users] Slow MySQL query after delete

[David Rees]

On 9/6/07, crs23 <pvr at groundhog.pair.com> wrote:
> It's a slow machine, a 600MHz C3.  It also has limited memory:  256MB.

Ouch. From the vmstat settings, you are not disk limited, but 100% CPU
and Memory bound. Hardware wise, obviously more CPU and memory would
help (even as little as another 256MB which should significantly
reduce the amount of swap you are using and allow more room for disk
cache.

>  I did not post any of those things. Linux 2.6.18. dpkg shows MySQL
> 5.0.32-2. Here are the interesting bits from my.cnf:
> key_buffer = 16M
> max_allowed_packet = 16M
> thread_stack = 128K
> thread_cache_size = 8
> table_cache = 256
> sort_buffer_size = 4M
> read_buffer_size = 1M
> read_rnd_buffer_size = 1M
> query_cache_limit = 1048576
> query_cache_size = 16777216
> query_cache_type = 1
> skip-bdb
> skip-innodb

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

This will slightly slow down the smaller queries (as they aren't
cached) but should improve the speed of the larger queries.

> I recently added 'skip-innodb' at the suggestion of one post and adjusted
> some of the numbers a little. Those changes don't seem to have made much
> difference.

skip-innodb will mainly just reduce the memory consumption of MySQL a
bit if you aren't using innodb.

> When running the query I'm seeing 98% CPU on the mysqld process, ~65% user
> and ~35% system. The vmstat results are below for the second of two back to
> back runs of the query (the query times were essentially the same for both).

High system time like that indicates that MySQL is performing a lot of
system calls, in this case, most likely it is doing a lot of reads
from the kernel's IO cache to retrieve data. Increasing the key buffer
size should help here. If increasing it to 32MB doesn't help, try 64MB
temporarily.

Thank you for the detailed information, that's exactly the type of
info required to analyze these types of issues.

I'll try to find some time later to see if there are ways to optimize
the query itself as well.

-Dave