[mythtv-users] Slow MySQL query after delete

[David Rees]

On 9/6/07, crs23 <pvr at groundhog.pair.com> wrote:
> 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.

OK, well, removing those duplicate recording entries has obviously
helped immensely.

> key_buffer = 16M,
> query_cache_size = 16M
>
> +-------------------------+----------+
> | 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 |
> +-------------------------+----------+

Hardly any of the key buffer or query cache is used at all for this
query, so there's really no need to analyze the other key buffer
settings. I would disable the query cache and leave the key buffer at
16M and after the system has run a while, see how many key_blocks are
used/unused.

The other posts regarding InnoDB had me check what I'm using on my system.

I realized that I'm using InnoDB on all my MythTV tables except
nestitle which has a full-text column and can only my MyISAM. I've
been running this way for as long as I can remember (obviously, since
I forgot!).

As the others suggested, there are 2 primary benefits InnoDB,
transaction support (which MySQL doesn't use) and row level locking.

The row level locking is very important and could allow much higher
levels of concurrency, meaning a smaller chance of multiple threads
accessing the DB blocking other threads.

InnoDB has a couple other options which are interesting - it will
actually cache table data where MyISAM backend relies on the OS
buffers, something I noted appeared to be a bottleneck in your system.

You might want to try turning on InnoDB and converting the tables
related to the BUSQ to InnoDB and see if that helps or not. Here are
the InnoDB related settings I use:

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 40M
innodb_additional_mem_pool_size = 6M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 10M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

40MB of innodb_buffer_pool_size may be too much for your system, you
might want to start with 20MB and try a couple different settings
(smaller and larger) to see if it affects performance of the BUSQ or
not.

-Dave