[mythtv-users] Slow MySQL query after delete
Gareth Glaccum
gareth.glaccum at btopenworld.com
Sun Sep 2 04:10:35 UTC 2007
Hi,
Not sure if this helps, but this week I sent in a query about the size of the database I had, 168MB. Someone (Michael Dean I think) suggested mythfilldatabase as a cleanup method. I performed a mythfilldatabase without any extra commands (make sure it is set up correctly first), and my database shrunk from 168 to 78MB. Before the cleanup, I was getting lags on deletes (I put it down to bad fragmentation and ext3, [ext3 is not meant to fragment but], my partition had been full (100%+) for some time, I had deleted old small programs and been recording larger programs in their place). However, after the cleanup the lag has dissapeared, I can now delete around 10G of data before things start to slow. (Originally I had discounted any database issue as it was on a seperate spindle to the myth store).
Gareth
----- Original Message -----
From: crs23
To: mythtv-users at mythtv.org
Sent: Sunday, September 02, 2007 8:02 AM
Subject: [mythtv-users] Slow MySQL query after delete
Deleting programs has been slow for me I think since I started using MythTV, but it's been getting worse and now it's pretty bad. I think I've tracked it down to a particularly slow SQL query and I'm hoping some one will have some advice for making it fast(er).
First, the backend seems to hold off on this query for a few seconds. I can usually manage to delete two programs before it kicks in. But when it does the frontend blocks waiting for the backend which itself blocks waiting for mysql.
The query takes around 35 to 40 seconds. It's a pretty complicated query. I've included it below but here I'll just mention it JOINs ten tables. Here are the EXPLAIN results. Sorry for the wide columns.
+----+-------------+--------------+--------+----------------------+---------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+----------------------+---------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+
| 1 | SIMPLE | cardinput | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary; Using filesort |
| 1 | SIMPLE | capturecard | eq_ref | PRIMARY | PRIMARY | 4 | mythconverg.cardinput.cardid | 1 | Using index |
| 1 | SIMPLE | recordmatch | ALL | recordid | NULL | NULL | NULL | 1984 | |
| 1 | SIMPLE | record | eq_ref | PRIMARY | PRIMARY | 4 | mythconverg.recordmatch.recordid | 1 | |
| 1 | SIMPLE | channel | eq_ref | PRIMARY | PRIMARY | 4 | mythconverg.recordmatch.chanid | 1 | Using where |
| 1 | SIMPLE | program | eq_ref | PRIMARY,id_start_end | PRIMARY | 16 | mythconverg.recordmatch.chanid,mythconverg.recordmatch.starttime,mythconverg.recordmatch.manualid | 1 | |
| 1 | SIMPLE | oldrecstatus | eq_ref | PRIMARY,title | PRIMARY | 160 | mythconverg.channel.callsign,mythconverg.recordmatch.starttime,mythconverg.program.title | 1 | |
| 1 | SIMPLE | oldrecorded | ref | title,programid | title | 130 | mythconverg.program.title | 36 | |
| 1 | SIMPLE | recorded | ref | programid,title | title | 130 | mythconverg.program.title | 5 | |
| 1 | SIMPLE | oldfind | eq_ref | PRIMARY | PRIMARY | 8 | mythconverg.recordmatch.recordid,func | 1 | Using index |
+----+-------------+--------------+--------+----------------------+---------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+
10 rows in set (0.02 sec)
Running this on my DB retrieves 3968 rows in set (39.54 sec). The record table has 87 entries and the recordmatch has 1984 (as you can see).
The two ALL types are troubling as are the using temporary and using filesort but I'm not very familiar with databases or SQL so I don't know if I can improve this and if so how. Ideally I'm just missing some index and can adjust a table. If I need to remove info about previously recorded programs I can do that, too, though that's less desirable.
Does anybody else have this problem? Or is my DB messed up somehow? That wouldn't surprise me as I've had not infrequent problems where MythTV decides to record programs that I haven't requested or forgets to record those I have. I suspect it's swapped one for the other but perhaps that's something to do with the downloaded program guide data.
Cheers,
-chris
The query is:
SELECT DISTINCT channel.chanid, channel.sourceid, program.starttime, program.endtime, program.title, program.subtitle, program.description, channel.channum, channel.callsign, channel.name, oldrecorded.endtime IS NOT NULL AS oldrecduplicate, program.category, record.recpriority, record.dupin, recorded.endtime IS NOT NULL AS recduplicate, oldfind.findid IS NOT NULL AS findduplicate, record.type, record.recordid, program.starttime - INTERVAL record.startoffset minute AS recstartts, program.endtime + INTERVAL record.endoffset minute AS recendts, program.previouslyshown, record.recgroup, record.dupmethod, channel.commfree, capturecard.cardid, cardinput.cardinputid, UPPER(cardinput.shareable) = 'Y' AS shareable, program.seriesid, program.programid, program.category_type, program.airdate, program.stars, program.originalairdate, record.inactive, record.parentid, (CASE record.type WHEN 6 THEN record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday WHEN 7 THEN record.findid ELSE 0 END) , record.playgroup, oldrecstatus.recstatus, oldrecstatus.reactivate, channel.recpriority + cardinput.recpriority, record.prefinput, program.hdtv, program.closecaptioned, program.first, program.last, program.stereo FROM recordmatch INNER JOIN record ON (recordmatch.recordid = record.recordid) INNER JOIN program ON (recordmatch.chanid = program.chanid AND recordmatch.starttime = program.starttime AND recordmatch.manualid = program.manualid) INNER JOIN channel ON (channel.chanid = program.chanid) INNER JOIN cardinput ON (channel.sourceid = cardinput.sourceid) INNER JOIN capturecard ON (capturecard.cardid = cardinput.cardid) LEFT JOIN oldrecorded as oldrecstatus ON ( oldrecstatus.station = channel.callsign AND oldrecstatus.starttime = program.starttime AND oldrecstatus.title = program.title ) LEFT JOIN oldrecorded ON ( record.dupmethod > 1 AND oldrecorded.duplicate <> 0 AND program.title = oldrecorded.title AND ( (program.programid <> '' AND program.generic = 0 AND program.programid = oldrecorded.programid) OR (oldrecorded.findid <> 0 AND oldrecorded.findid = (CASE record.type WHEN 6 THEN record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday WHEN 7 THEN record.findid ELSE 0 END) ) OR ( program.generic = 0 AND (program.programid = '' OR oldrecorded.programid = '') AND (((record.dupmethod & 0x02) = 0) OR (program.subtitle <> '' AND program.subtitle = oldrecorded.subtitle)) AND (((record.dupmethod & 0x04) = 0) OR (program.description <> '' AND program.description = oldrecorded.description)) ) ) ) LEFT JOIN recorded ON ( record.dupmethod > 1 AND recorded.duplicate <> 0 AND program.title = recorded.title AND recorded.recgroup <> 'LiveTV' AND ( (program.programid <> '' AND program.generic = 0 AND program.programid = recorded.programid) OR (recorded.findid <> 0 AND recorded.findid = (CASE record.type WHEN 6 THEN record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday WHEN 7 THEN record.findid ELSE 0 END) ) OR ( program.generic = 0 AND (program.programid = '' OR recorded.programid = '') AND (((record.dupmethod & 0x02) = 0) OR (program.subtitle <> '' AND program.subtitle = recorded.subtitle)) AND (((record.dupmethod & 0x04) = 0) OR (program.description <> '' AND program.description = recorded.description)) ) ) ) LEFT JOIN oldfind ON (oldfind.recordid = recordmatch.recordid AND oldfind.findid = (CASE record.type WHEN 6 THEN record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday WHEN 7 THEN record.findid ELSE 0 END) ) ORDER BY record.recordid DESC;
------------------------------------------------------------------------------
View this message in context: Slow MySQL query after delete
Sent from the mythtv-users mailing list archive at Nabble.com.
------------------------------------------------------------------------------
_______________________________________________
mythtv-users mailing list
mythtv-users at mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20070902/d417a75e/attachment.htm
More information about the mythtv-users
mailing list