[mythtv-users] optimize_mythdb.pl (was Re: 0.19 Program Guide slowness...)

[Todd Hutchinson]

Thanks everyone--but, this did not seem to solve my
problem.  

Here are my results for the time to load the listings
page on mythweb:

Before running optimize_mythdb.pl: 27s
After running optimze_mythdb.pl in contrib: 27s
After adding analyze and running mythdb.pl: 27s
After adding the following to /etc/my.cnf: 12s
   key_buffer = 48M
   max_allowed_packet = 8M
   table_cache = 128
   sort_buffer_size = 48M
   net_buffer_length = 8M
   thread_cache_size = 4
   query_cache_type = 1
   query_cache_size = 4M 

With the settings in my.cnf, if I click listings a
second time, it only takes 5s to load.

In all of the above cases, most of the time is spent
with mysqld running at very high CPU usage (> 80%).

Does anyone else have timing numbers for how long it
takes to load "listings"?  I have 83 channels set up. 
Maybe the slow load time is normal with this many
channels?

--- "Michael T. Dean" <mtdean at thirdcontact.com> wrote:

> On 02/23/2006 06:06 PM, Phill Edwards wrote:
> 
> >>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
> >>    
> >>
> >So would it come after the Optimise and look like
> this?
> >
> ># Repair and optimize each table
> >    foreach $table (@tables) {
> >        unless ($dbh->do("REPAIR TABLE $table")) {
> >            print "Skipped:  $table\n";
> >            next;
> >        };
> >        if ($dbh->do("OPTIMIZE TABLE $table")) {
> >            print "Repaired/Optimized: $table\n";
> >        }
> >        if ($dbh->do("ANALYZE TABLE $table")) {
> >            print "Analysed: $table\n";
> >        }
> >    }
> >  
> >
> 
> Just thought I'd mention that Chris Petersen wrote a
> script that does 
> all of this except the analyze step.  The script is
> in the source 
> package at contrib/optimize_mythdb.pl and includes
> comments which 
> describe how to set it up.
> 
> Chris, any comments on the addition of the analyze
> table stuff?
> 
> Mike
> 
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
>
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com