[mythtv-users] MythWeb (0.19) listing much slower than frontend

[Niels Dybdahl]

On 3/3/06, Niels Dybdahl <Niels at dybdahl.dk> wrote:
>
>
>
> On 3/3/06, Steve <zflyer at gmail.com> wrote:
> >
> > On 3/2/06, Niels Dybdahl <Niels at dybdahl.dk> wrote:
> > > Hi,
> > >
> > > I have upgraded from version 0.18.1 to version 0.19 and now my MythWeb
> > is
> > > much slower to show the program listings than before.
> > > It takes around 40 seconds to open the program listing in MythWeb even
> > > though I only have 9 visible channels (and only 5 populated channels).
> > When
> > > I open the program listing in mythfrontend it only takes 1-2 seconds.
> > >
> > > During the 40 seconds mysqld uses most of the CPU time, while httpd
> > also
> > > uses some.
> >
> > I have the same issue which wasn't present prior to 0.19.  The
> > Listings page takes about 11 seconds to generate on modern hardware,
> > and mysqld pegs the cpu most of that time.  My work-around has been to
> > enable mysql query caching, which makes subsequent page reloads quick,
> > but this doesn't resolve the issue.  My mysql config additions:
> >
> > [mysqld]
> > query-cache-type = 1
> > query-cache-size = 20M
>
>
> I tried to add these lines, but then mysql would not start
> Instead I added log=/var/log/mysql.log to create a log of the queries (I
> had to create the file manually and set access rights).
> I counted 246 queries (30 queries/second) like the following:
>
>                       8 Query       SELECT program.*,
>                          UNIX_TIMESTAMP(program.starttime) AS
> starttime_unix,
>                          UNIX_TIMESTAMP( program.endtime) AS endtime_unix,
>                          CONCAT(repeat('&diams;', program.stars * '4'),
>                                 IF((program.stars * '4' * 10) % 10,
>                                    "&frac12;", "")) AS starstring,
>                          IFNULL(programrating.system, "") AS rater,
>                          IFNULL(programrating.rating, "") AS rating,
>                          oldrecorded.recstatus,
>                          channel.channum
>                   FROM program
>                        LEFT JOIN programrating USING (chanid, starttime)
>                        LEFT JOIN oldrecorded
>                                  ON oldrecorded.recstatus IN (-3, 11)
>                                     AND IF(oldrecorded.programid OR
> oldrecorded.seriesid,
>                                            oldrecorded.programid =
> program.programid
>                                              AND oldrecorded.seriesid =
> program.seriesid,
>                                            oldrecorded.title =
> program.title
>                                              AND oldrecorded.subtitle =
> program.subtitle
>                                              AND oldrecorded.description =
> program.description
>                                           )
>                        LEFT JOIN channel ON program.chanid =
> channel.chanid
>                  WHERE program.chanid='1003' AND program.starttime =
> FROM_UNIXTIME('1141943100') GROUP BY program.chanid, program.starttime
>
> resulting in a page listing 22 shows on 5 channels (the other 4 channels
> are not populated).
> Is this normal ?
>

I just tested on a similar MythTV setup. I only did 29 of these queries even
though it has around 100 channels (and it does not behave that slow).
So why does my setup create so many of these queries ?

Niels
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20060303/8a10fe59/attachment.htm