[mythtv-users] Sloooow database

Brad DerManouelian myth at dermanouelian.com
Fri Jun 6 16:26:29 UTC 2008

On Jun 6, 2008, at 9:10 AM, Curtis Stanford wrote:

> Hey everyone, here's something I've put up with for a long time and
> thought I'd try to resolve it. I'm using the release-21-fixes branch
> of mythtv with a separate backend and frontend. The mysql database on
> the backend (version 5.0.30) is so slow, especially when selecting
> from the program table. Flipping pages in the program guide takes over
> 5 seconds at times. Bringing up the Program Finder sometimes takes
> over 30 seconds to become useable. Showing New Titles in the scheduler
> can take almost as long. Is this normal?
> I run the optimize_database script every day. Right after running it,
> things seem more responsive but it quickly degrades back to its
> normally slow state. Maybe I'm missing an index or something?
> Any hints appreciated, thanks. By the way, my backend machine is an
> athlon xp 2200. At present there are just over 118,000 rows in my
> program table. It has primary keys on chanid, starttime and manualid
> and secondary keys on endtime, title, previouslyshown,
> title_pronounce, seriesid and programid. I have tuned the mysql
> parameters to match the ones in messages from this list.
> Curtis

Is your backend swapping? Have you optimized mysql in your my.cnf file  
at all?
I have 2GB in my backend, so I set /etc/my.cnf with this:

key_buffer = 32M
table_cache = 256
sort_buffer_size = 4M
myisam_sort_buffer_size = 16M
query_cache_size = 64M
query_cache_limit = 8M
query_cache_type  = 1

It's much faster than the default settings. I could probably give it  
more memory, but I do lots of transcoding and other stuff on my  
backend so I didn't want to push it too much.

More information about the mythtv-users mailing list