MySQL wizards: Need Help with MythTV query

[tschak909]

Hello, everyone.

I need some help with some people who can do query optimization.

We have in the mythconverg database, a program table, which contains for lack of a better term, the Electronic Program Guide (EPG)...

This is being read into a data grid source as a single query, that gets the active program on every channel.

Code: [Select]

SELECT chanid, title, starttime, endtime, seriesid, programid FROM program WHERE starttime < '2009-06-21 10:52:10' AND endtime>'2009-06-21 10:52:10';

which outputs, for me, an example table:

Code: [Select]

mysql> SELECT chanid, title, starttime, endtime, seriesid, programid FROM program WHERE starttime < '2009-06-21 10:52:10' AND endtime>'2009-06-21 10:52:10';
+--------+--------------------------------------------------------------+---------------------+---------------------+------------+----------------+
| chanid | title                                                        | starttime           | endtime             | seriesid   | programid      |
+--------+--------------------------------------------------------------+---------------------+---------------------+------------+----------------+
|   2226 | Dr. G: Medical Examiner                                      | 2009-06-21 10:00:00 | 2009-06-21 11:00:00 | EP00666611 | EP006666110047 |
|   2260 | Monmouth Replay Show                                         | 2009-06-21 10:30:00 | 2009-06-21 11:00:00 | EP00924108 | SH009241080000 |
|   2542 | Pop Latino                                                   | 2009-06-21 10:00:00 | 2009-06-21 12:00:00 | EP00672118 | SH006721180000 |
|   2266 | ATP Tennis                                                   | 2009-06-21 08:30:00 | 2009-06-21 11:00:00 | EP00554477 | EP005544772067 |
|   2041 | Sunday Housecall With Dr. Rosenfeld                          | 2009-06-21 10:30:00 | 2009-06-21 11:00:00 | EP01135543 | SH011355430000 |
|   1481 | Ed Young TV                                                  | 2009-06-21 10:30:00 | 2009-06-21 11:00:00 | EP00711915 | SH007119150000 |
|   1381 | Phantom Gourmet                                              | 2009-06-21 10:30:00 | 2009-06-21 11:00:00 | EP00604850 | EP006048500756 |

.... some bits removed ......

| 2009-06-21 11:00:00 | EP00115044 | SH001150440000 |
|   2234 | Turning Point                                                | 2009-06-21 10:30:00 | 2009-06-21 11:00:00 | EP00370169 | SH003701690000 |
|   2452 | Filthy's Ass Obsession 2                                     | 2009-06-21 10:30:00 | 2009-06-21 12:00:00 |            | MV002404910000 |
|   2039 | MythBusters                                                  | 2009-06-21 10:00:00 | 2009-06-21 11:00:00 | EP00557678 | EP005576780023 |
|   2501 | Sounds of the Seasons                                        | 2009-06-21 10:00:00 | 2009-06-21 12:00:00 | EP00875092 | SH008750920000 |
|   2868 | Tropic Thunder                                               | 2009-06-21 09:30:00 | 2009-06-21 11:35:00 |            | MV002367310000 |
+--------+--------------------------------------------------------------+---------------------+---------------------+------------+----------------+
323 rows in set (0.00 sec)


This query, uncached, takes 26 seconds.

UGH.

the EXPLAIN SELECT makes this even more apparent:

mysql> EXPLAIN SELECT chanid, title, starttime, endtime, seriesid, programid FROM program WHERE starttime < '2009-06-21 10:52:10' AND endtime>'2009-06-21 10:52:10';

Code: [Select]

+----+-------------+---------+------+-------------------------------------------------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys                                         | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+-------------------------------------------------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | program | ALL  | endtime,starttime,program_start_time,program_end_time | NULL | NULL    | NULL | 158575 | Using where |
+----+-------------+---------+------+-------------------------------------------------------+------+---------+------+--------+-------------+

WTF?!

(pay no attention to the program_foo keys that I added, i was trying to force the issue!)

the EXPLAIN program:

Code: [Select]

mysql> explain program;
+-------------------------+-------------------------------------------------------------------+------+-----+---------------------+-------+
| Field                   | Type                                                              | Null | Key | Default             | Extra |
+-------------------------+-------------------------------------------------------------------+------+-----+---------------------+-------+
| chanid                  | int(10) unsigned                                                  | NO   | PRI | 0                   |       |
| starttime               | datetime                                                          | NO   | PRI | 0000-00-00 00:00:00 |       |
| endtime                 | datetime                                                          | NO   | MUL | 0000-00-00 00:00:00 |       |
| title                   | varchar(128)                                                      | NO   | MUL |                     |       |
| subtitle                | varchar(128)                                                      | NO   |     |                     |       |
| description             | text                                                              | NO   |     | NULL                |       |
| category                | varchar(64)                                                       | NO   |     |                     |       |
| category_type           | varchar(64)                                                       | NO   |     |                     |       |
| airdate                 | year(4)                                                           | NO   |     | 0000                |       |
| stars                   | float                                                             | NO   |     | 0                   |       |
| previouslyshown         | tinyint(4)                                                        | NO   | MUL | 0                   |       |
| title_pronounce         | varchar(128)                                                      | NO   | MUL |                     |       |
| stereo                  | tinyint(1)                                                        | NO   |     | 0                   |       |
| subtitled               | tinyint(1)                                                        | NO   |     | 0                   |       |
| hdtv                    | tinyint(1)                                                        | NO   |     | 0                   |       |
| closecaptioned          | tinyint(1)                                                        | NO   |     | 0                   |       |
| partnumber              | int(11)                                                           | NO   |     | 0                   |       |
| parttotal               | int(11)                                                           | NO   |     | 0                   |       |
| seriesid                | varchar(40)                                                       | NO   | MUL |                     |       |
| originalairdate         | date                                                              | YES  |     | NULL                |       |
| showtype                | varchar(30)                                                       | NO   |     |                     |       |
| colorcode               | varchar(20)                                                       | NO   |     |                     |       |
| syndicatedepisodenumber | varchar(20)                                                       | NO   |     |                     |       |
| programid               | varchar(40)                                                       | NO   | MUL |                     |       |
| manualid                | int(10) unsigned                                                  | NO   | PRI | 0                   |       |
| generic                 | tinyint(1)                                                        | YES  |     | 0                   |       |
| listingsource           | int(11)                                                           | NO   |     | 0                   |       |
| first                   | tinyint(1)                                                        | NO   |     | 0                   |       |
| last                    | tinyint(1)                                                        | NO   |     | 0                   |       |
| audioprop               | set('STEREO','MONO','SURROUND','DOLBY','HARDHEAR','VISUALIMPAIR') | NO   |     | NULL                |       |
| subtitletypes           | set('HARDHEAR','NORMAL','ONSCREEN','SIGNED')                      | NO   |     | NULL                |       |
| videoprop               | set('HDTV','WIDESCREEN','AVC')                                    | NO   |     | NULL                |       |
+-------------------------+-------------------------------------------------------------------+------+-----+---------------------+-------+
32 rows in set (0.00 sec)

.....

What's going on, here? this one is an interesting critical query problem, because the fact that the query takes over 26 seconds is enough to cause Orbiter to deadlock and time out, thinking that something has frozen.

Any ideas, fellas?

-Thom

[Pnuts]

I have only worked in MS SQL and it was probably 4 years ago now, but maybe something like this will work?


Create a new table, Have a job that runs on a schedule or a trigger that updates said table with the results of the select statement.

Now simply select * from table when needed, because it isn't parsing the original entire table and the query is very simple, the results should be almost instant (depending on the size of the new table once populated).

I had to do this a couple times with some complex queries combining data from multiple tables that would take several minutes to complete. In the end, it took less then a second to pull what I needed from the new table. I was able to run the jobs every couple hours and it worked well.

probably not what your looking for, but it works very well.

-Pnuts

[rlb2]

I agree.  I have done the very same thing for Dashboards in my company.  Run maybe every 1/2 hr since most show are not any shorter than that?...  Give's the response to the request (qry) a google like speed.

Rob

[tschak909]

You can't assume 30 minute blocks for shows, and that the show blocks start on :00 and :15 and :30 and :45 boundaries.

-Thom

[wierdbeard65]

Like the others, I have faced this problem on MS SQL and gone for the temporary table solution. I found, however, that creating the table immediately before it is required was still substantially faster than extracting the data directly! So, it might be worth trying that (rather than a timed job or a trigger). Shouldn't take long to test 

I *THINK* the problem is with the Date comparisons. These are even slower than String comparisons. I don't know how MySQL stores (and compares) dates, but you could always serialize (perhaps number of seconds since some date) them in another column (use a trigger to update this column when the table is updated) and then apply your WHERE clause to the serialized column.

Another line of inquiry could be the size of the table. Assuming the table itself isn't updated that much at short notice, you could extract, for example, all of today's shows to a second table (timed job every 1/2 hour or so) then do your query on that table. Looking at your query, I'm assuming it's trying to find shows currently on, so you won't be interested in yesterday's (or tomorrow's) shows.

Just some random musings first thing (here) in the morning! Hope they help or at least spark an idea 

[chriss]

MySQL isn't using any keys/indexes to process the query, you could try if it gets any better if you add a 'USE INDEX(starttime, endtime)' or even 'FORCE INDEX(starttime, endtime)'.

br,
/chriss

[merkur2k]

I suspect weirdbeard is onto something with the processing and storage of dates. Most databases have odd behavior when working with dates, and will often drop the use of an index. I spent alot of time with a large sybase db that was just incredibly slow whenever i was working with dates. well due to horrible documentation i finally figured out that it wanted to use its built in date handler functions to construct dates rather than using strings. mysql may be the same way, might be worth some research.
its also possible that the way it stores the 'datetime' type is just really bad. from the mysql manual:

Code: [Select]

DATETIME: Eight bytes:

A four-byte integer packed as YYYY×10000 + MM×100 + DD

A four-byte integer packed as HH×10000 + MM×100 + SS
an alternative is to use the common unix timestamp type:

Code: [Select]

TIMESTAMP: A four-byte integer representing seconds UTC since the epoch ('1970-01-01 00:00:00' UTC)and do the conversions in the client software (any programming or scripting language has built-in or library functions for this).

[gadget]

I think chriss is on the simpler track of using indexes. The 'program' table has starttime in its primary index and endtime in a secondary index.
Try creating an index with starttime and endtime and mysql should use it by default.

gadget

[chriss]

Most databases have odd behavior when working with dates,

If I remember correctly, MySQL stores datetime as a long integer and will convert the strings from the query to long int for processing it. However, I'm not sure about that and this approach should be way faster. A problem with the keys might be, that the primary key spans several fields and not only starttime, i.e., the key will probably be stored as a hash and not an integer.
A simple solution might be to modify the table to reflect a better structure for the mentioned query, e.g., by introducing another index for starttime, as also mentioned by gadget.

BUT, the table is not a pluto/LMCE table but a Myth table if I understand correctly. Is this correct? I think we should not alter these tables but rather use the vanilla packages.

Another approach could be to use an on-update/on-insert trigger to fill a temporary table with the required information.

Thom, do you know which query is used by MythTV itself?

[jeangot]

Hello,

since I upgraded from 710 to 810, my DCERouter device started crashing each time I click on TV, then when it starts again (which takes a few mins) and I click on TV again, it successfully launches MythTV. The wife is very annoyed. After looking through the logs, adding mysql slow query log and so on, I tracked the issue down to the problem described in this thread. My query also takes between 20 and 30 secs the first time when it's not cashed.

I had even tried to create a new index with the columns starttime and endtime, but it didn't help. (It only barely reduces the query time).

I was starting to think that there was a configuration problem with my mysql or some disk throughput problem, until I stumbled upon this thread.

Are there any further thoughts as to what could be done to solve this problem? I am surprised that there are not more users affected by it, since my setup is not special besides the fact that my cable company happens to have a few hundred channels.

Since the EPG guide on the orbiter only displays 5 channels at a time, I was wondering if perhaps it would be possible to query the program table for only 5 channels, then query the next 5 when loading the next page on the orbiter? This should speed it up greatly since it would use the channel ids as keys. But since I'm not sure how the datagrid works, I don't know if this is possible.

Alternatively, could someone tell me where the DCERouter deadlock timer is located? I would be happy to play with that (and recompile if necessary) to see if I find a value high enough to solve this problem but still low enough to be effective?

Jean

[tschak909]

The issues in this thread have long been fixed.

You can attach gdb to the DCERouter, and use a coredump file in /usr/pluto/coredump to find the problem.

Be sure to run gdb from /usr/pluto/bin, so that the plugins DCERouter needs can be properly loaded.

-Thom