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

[John Armstrong]

Same problem here with slow mythweb 0.19 and high CPU usage by mysqld. Much 
slower than 0.18.

I have 46 channels, 39 of them visible.
Listings page just took 16 seconds to load. Dropped to 7 seconds after 
making those changes to my.cnf.
mysql version is 4.1.14 on a P3-500

----- Original Message ----- 
From: "Todd Hutchinson" <tahutchinson_99 at yahoo.com>
To: "Discussion about mythtv" <mythtv-users at mythtv.org>
Sent: Friday, February 24, 2006 2:32 PM
Subject: Re: [mythtv-users] optimize_mythdb.pl (was Re: 0.19 Program 
Guideslowness...)


> 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
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>
>