getAspect() SQL query is slow

[nordmark@…]

I noticed that the Recorded programs web page started to take tens of seconds to load. During this time, the MySQL database server is continuously running at full CPU utilization, sorting queries for recordedmarkup.type. These SQL queries are found in the getAspect() function of the Program class. In the recordedmarkup table there are a number of recordings where there thousands of custom aspect ratio (type 14) marks.

Looking at the query (found in modules/tv/classes/Program.php): $sh = $db->query('SELECT recordedmarkup.type, recordedmarkup.data FROM recordedmarkup WHERE recordedmarkup.chanid = ? AND recordedmarkup.starttime = FROM_UNIXTIME(?) AND recordedmarkup.type IN (10, 11, 12, 13, 14) GROUP BY recordedmarkup.type ORDER BY SUM((SELECT IFNULL(rm.mark, recordedmarkup.mark) FROM recordedmarkup AS rm WHERE rm.chanid = recordedmarkup.chanid AND rm.starttime = recordedmarkup.starttime AND rm.type IN (10, 11, 12, 13, 14) AND rm.mark > recordedmarkup.mark ORDER BY rm.mark ASC LIMIT 1)- recordedmarkup.mark) DESC LIMIT 1', $this->chanid, $this->recstartts ); it looks overly complicated, and even with my limited understanding of SQL, it looks at least quadratic in the number of aspect ratio marks, since each one is compared to every other. Indeed, one such query can take several seconds to execute.

Since it comes down to picking one (probably just the first or last) aspect ratio mark, there surely must be a quicker way to write the query without a sub-query for each mark, like 'ORDER BY recordedmarkup.mark ASC LIMIT 1' or something similar.

Arne

[Rob Smith]

The problem is the last mark may not be the actual show's aspect ratio. Digital channels can be HD for the show and SD for the ads or even the opposite.

So given the a show like the following:

• Intro - 30 seconds HD
• Ads - 3 minutes SD
• Show 10 minutes HD
• Ads - 5 minutes SD
• Show - 8 minutes HD
• Ads - 6 minutes SD

VS

• Intro - 30 seconds SD
• Ads - 3 minutes HD
• Show 10 minutes SD
• Ads - 5 minutes HD
• Show - 8 minutes SD
• Ads - 6 minutes HD

it gets really complex to know which one is the show. So what we do is we add up all the durations for the segments and use the aspect ratio of the longest duration, which is why it's so complex.

I'm sorry, but it's just not easy to know without the work

[josbouten@…]

I encountered the same problem. Very sluggish behaviour of the web gui, even problems with subsequent actions that involved the database. I had to kill mysql queries to get things running again. The markup table contained 100.000+ elements. Since I do not edit any of the recordings and since I do not care about commercials flagging, the table content was not useful to me and I emptied the database. This solved the problem for me.

[stuartm]

In 0.28 the aspect ratio is stored as as single column in recordedfile if anyone wishes to persue this, but since 0.28 also deprecates mythweb in favour of the WebFrontend? maybe it's not worth the effort.

[thomas@…]

This is a pretty basic hack, but I just forced the aspect ratio to be 16/9 in: /usr/share/mythtv/mythweb/modules/tv/classes/Program.php

by adding this: return 16/9;

right after this: public function getAspect() { and before this: global $db;

The reason is that, recently, I'd get an occasional recording from a HDTC2-us that would create a landmine for the Recorded Programs entry in mythweb. Accessing that page would drive mysql to 100% CPU. The resulting system performance would cause recordings to fail, the system to be unresponsive, etc. I once let it run for 4 days, wondering if the job would end -- it did not; only a reboot would clear up the condition.

If anyone has an idea for a less drastic measure, I'm all ears.

[stuartm]

We store the aspect ratio in the recordedfile table, if you want to submit a patch to use that instead of the existing (old) logic your welcome. It hasn't been done already because mythweb is being phased out and doesn't have a full time maintainer.

[Karl Egly]

Like 305b8a1a859fe78c382d185339bdb2b81576b2df/mythweb?

[stuartm]

Exactly like that ;)

[stuartm]

Fixed in 0.28/fixes branch