[mythtv-users] 0.19 Program Guide slowness...

[gLaNDix (Jesse Kaufman)]

Dan Wilga wrote:
> At 7:41 PM -0800 2/21/06, Todd Hutchinson wrote:
>> I too notice a slow response when I click "Listings"
>> on the web page.  If I click "listings" and view top
>> at the same time, mysqld uses a large amount of CPU
>> (70-98%) for about 12 seconds, then, httpd uses a
> 
> The database might be missing some indices, or may just have large 
> amounts of leftover junk in it waiting to be garbage collected.
> 
> Have you tried optimizing it? Here's one script that does so:
> 
> http://forevermore.net/files/mythtv_misc/optimize_mythdb.pl.txt
> 

you might want to add an "ANALYZE TABLE" to the script as well ... this 
"analyzes and stores the key distribution for a table" ...

according to mysql.com, "MySQL uses the stored key distribution to 
decide the order in which tables should be joined when you perform a 
join on something other than a constant."

i've found that this also helps performance on many projects i've worked 
on with lots of JOINs (i admittedly don't know how many there are in 
myth, as i haven't looked at the code)

i have a cron job that goes through CHECK TABLE, REPAIR TABLE, OPTIMIZE 
TABLE, and ANALYZE TABLE every night (i think, maybe every week) on all 
servers that host any coding projects (i am our head php/mysql 
developer) ... i've noticed a DEFINITE performance increase since i 
started doing this

-g-