Slow DB queries

[grandmastermarclar@…]

I'm using fixes/0.28 [v0.28.1-21-ge26a33c] and I've recently upgraded to both Ubuntu 17.04 (from 16.10), and MariaDB 10.1.22.

I've enabled slow query logging, and get plenty of examples such as:

SET timestamp=1493211910;
SELECT     c.chanid,         c.sourceid,           p.starttime,           p.endtime,        p.title,              p.subtitle,            p.description,    c.channum,            c.callsign,            c.name,           oldrecduplicate,      p.category,            sched_temp_record.recpriority, sched_temp_record.dupin,
  recduplicate,          findduplicate,    sched_temp_record.type,        sched_temp_record.recordid,     p.starttime - INTERVAL sched_temp_record.startoffset     minute AS recstartts,     p.endtime + INTERVAL sched_temp_record.endoffset     minute AS recendts,                                             p.previouslys
hown,     sched_temp_record.recgroup, sched_temp_record.dupmethod,  c.commmethod,          capturecard.cardid, 0,                  p.seriesid,          p.programid,       sched_temp_record.inetref,    p.category_type,       p.airdate,         p.stars,             p.originalairdate,     sched_temp_record.inactive, sche
d_temp_record.parentid,   recordmatch.findid,     sched_temp_record.playgroup, oldrecstatus.recstatus,     oldrecstatus.reactivate, p.videoprop+0,         p.subtitletypes+0, p.audioprop+0,   sched_temp_record.storagegroup,     capturecard.hostname, recordmatch.oldrecstatus, NULL,     oldrecstatus.future, capturecard.s
chedorder,     p.syndicatedepisodenumber, p.partnumber, p.parttotal,     c.mplexid, c.recpriority + capturecard.recpriority + (capturecard.cardid = sched_temp_record.prefinput) * 2 + (FIND_IN_SET('SIGNED', p.subtitletypes) > 0) * -99 AS powerpriority FROM recordmatch INNER JOIN sched_temp_record ON (recordmatch.record
id = sched_temp_record.recordid) INNER JOIN program AS p ON ( recordmatch.chanid    = p.chanid    AND      recordmatch.starttime = p.starttime AND      recordmatch.manualid  = p.manualid ) INNER JOIN channel AS c ON ( c.chanid = p.chanid ) INNER JOIN capturecard ON (c.sourceid = capturecard.sourceid) LEFT JOIN oldreco
rded as oldrecstatus ON ( oldrecstatus.station   = c.callsign  AND      oldrecstatus.starttime = p.starttime AND      oldrecstatus.title     = p.title ) WHERE p.endtime > (NOW() - INTERVAL 480 MINUTE) ORDER BY sched_temp_record.recordid DESC, p.starttime, p.title, c.callsign,          c.channum;
# User@Host: mythtv[mythtv] @ localhost []
# Thread_id: 154  Schema: mythconverg  QC_hit: No
# Query_time: 0.001141  Lock_time: 0.000055  Rows_sent: 1  Rows_examined: 119
# Rows_affected: 0

...or...

SET timestamp=1493211902;
REPLACE INTO recordmatch (recordid, chanid, starttime, manualid,                           oldrecduplicate, findid) SELECT record.recordid, program.chanid, program.starttime,  IF(search = 5, record.recordid, 0), (CASE   WHEN record.type IN (1, 7, 8) THEN  0   WHEN record.type IN (6, 2, 5) THEN  -1   ELSE (program.gene
ric - 1)  END) , (CASE record.type   WHEN 6    THEN record.findid   WHEN 2    THEN to_days(date_sub(convert_tz(program.starttime, 'UTC', 'SYSTEM'),             interval time_format(record.findtime, '%H:%i') hour_minute))   WHEN 5    THEN floor((to_days(date_sub(convert_tz(program.starttime, 'UTC',             'SYSTEM'
), interval time_format(record.findtime, '%H:%i')             hour_minute)) - record.findday)/7) * 7 + record.findday   WHEN 7    THEN record.findid   ELSE 0  END) FROM (record, program INNER JOIN channel       ON channel.chanid = program.chanid)  WHERE record.recordid = '573' AND program.manualid = 0 AND (program.tit
le LIKE '%Alan Partridge%' OR program.subtitle LIKE '%Alan Partridge%' OR program.description LIKE '%Alan Partridge%') AND channel.visible = 1  AND program.endtime > (NOW() - INTERVAL 480 MINUTE) AND (((record.filter & 1) = 0) OR (program.previouslyshown = 0)) AND (((record.filter & 2) = 0) OR (program.generic = 0)) A
ND (((record.filter & 4) = 0) OR (program.first > 0)) AND (((record.filter & 8) = 0) OR (HOUR(CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM')) >= 19 AND HOUR(CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM')) < 22)) AND (((record.filter & 16) = 0) OR (channel.commmethod = -2)) AND (((record.filter & 32) = 0) OR (p
rogram.hdtv > 0)) AND (((record.filter & 64) = 0) OR ((record.programid <> '' AND program.programid = record.programid) OR (record.programid = '' AND program.subtitle = record.subtitle AND program.description = record.description))) AND (((record.filter & 128) = 0) OR ((record.seriesid <> '' AND program.seriesid = rec
ord.seriesid))) AND (((record.filter & 256) = 0) OR (ABS(TIMESTAMPDIFF(MINUTE, CONVERT_TZ(  ADDTIME(record.startdate, record.starttime), 'Etc/UTC', 'SYSTEM'),   CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM'))) MOD 1440   NOT BETWEEN 11 AND 1429)) AND (((record.filter & 512) = 0) OR (ABS(TIMESTAMPDIFF(MINUTE, CONVE
RT_TZ(  ADDTIME(record.startdate, record.starttime), 'Etc/UTC', 'SYSTEM'),   CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM'))) MOD 10080   NOT BETWEEN 11 AND 10069)) AND (((record.filter & 1024) = 0) OR (channel.callsign = record.station)) AND (((record.filter & 2048) = 0) OR (program.category_type <> 'series')) AN
D ( (record.type = 4   OR record.type = 6   OR record.type = 2   OR record.type = 5)  OR   ((record.type = 1    OR record.type = 7    OR record.type = 8)   AND    ADDTIME(record.startdate, record.starttime) = program.starttime    AND    record.station = channel.callsign) );
# Time: 170426 14:05:03
# User@Host: mythtv[mythtv] @ localhost []
# Thread_id: 98  Schema: mythconverg  QC_hit: No
# Query_time: 0.254018  Lock_time: 0.000018  Rows_sent: 0  Rows_examined: 232591
# Rows_affected: 0

More attached.

[grandmastermarclar@…]

few minutes log of slow sql queries

[grandmastermarclar@…]

This may have been down to some other configuration issues completely unrelated to MythTV which I have since resolved. There are DB issues, but I'll raise those once I've gone through a major self-inflicted PC reconstruction!

I confirm ticket #13033 can be closed.

[Peter Bennett]

Closed as requested by reporter.