[mythtv-users] What does this SQL query do in MythWeb ?

[Robert Tsai]

On Mon, Mar 06, 2006 at 11:49:32AM +0100, Niels Dybdahl wrote:
> I have commented the following lines out of the file
> /var/www/html/mythweb/includes/programs.php:
> 
> /*            elseif ($this->chanid) {
>                 unset($this->filename);
>             // Kludge to avoid redefining the object, which doesn't work in
> php5
>                 $tmp = @get_object_vars(load_one_program($this->starttime,
> $this->chanid));
>                 if (is_array($tmp) && count($tmp) > 0) {
>                     foreach ($tmp as $key => $value) {
>                         $this->$key = $value;
>                     }
>                 }
>             }*/
> 
> 
> It has speeded my MythWeb program listing up to around 2 seconds and
> I have not noticed any missing details in the listing. Note however
> that this file (programs.php) probably is used by other modules, so
> they may have been impacted.

You might want to try mucking with includes/recording_schedules.php.

That module has code that is always run at the beginning of the TV
listings page (via require_once, if you start at mythweb.php). The
problem is that it has code to load certain objects from the database
one-by-one (search for "new Program" and "load_one_channel").

Subsequent calls in modules/tv/list.php to routines like
"load_all_channels" and "load_all_program_data" then don't do too much
good because the DB has already been hit a bunch of times.

That kind of mucking around saved me about 50 queries to the database
for a single load of the TV listings page, assuming the "status"
command does what I think it does, and that "Questions" means what I
think it means:

	% mysql --disable-pager -uroot -p[password] -e status

You can also tail the /var/log/mysql/mysql.log file to watch queries
as they happen (at least on my distro/setup).

I still had a bit more to go, but I wasn't really noticing any real
performance improvement (I only have 12 channels). But if you have
more channels (and therefore more programs), you might see more
improvement.

If this the route that needs to be taken, then there is a non-trivial
amount of work to be done in terms of reorganizing the way all the
different program/channel/scheduled_recording objects are loaded upon
demand, vs. pre-loading them at script startup.

Also, if you reduce queries against the database by caching the
results in PHP code, you might increase the memory usage of PHP (if
the queries aren't exactly no more/no less than what is required),
which might itself be bad for your system.

Didn't someone post some MySQL my.cnf configuration changes on this
list awhile ago that gave them a big boost?

--Rob
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://mythtv.org/pipermail/mythtv-users/attachments/20060306/e9df8a73/attachment.pgp