[mythtv-users] optimize_mythdb.pl (was Re: 0.19 Program Guide slowness...)
Todd Hutchinson
tahutchinson_99 at yahoo.com
Fri Feb 24 14:32:06 UTC 2006
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
More information about the mythtv-users
mailing list