[mythtv] innodb vs myism preformance
Daniel Manjarres
danmanj at gmail.com
Mon Feb 21 19:58:01 UTC 2005
After losing all those shows I am still trying to figure out wtf went wrong.
for reference here are the sizes of the tables in my database.
allrecord.MYI records: 11
callsignnetworkmap.MYI records: 52
channel.MYI records: 72
codecparams.MYI records: 145
conflictresolutionany.MYI records: 6
conflictresolutionsingle.MYI records: 11
credits.MYI records: 71707
dtv_privatetypes.MYI records: 56
dvdinput.MYI records: 8
dvdtranscode.MYI records: 11
gallerymetadata.MYI records: 44
gamemetadata.MYI records: 685
housekeeping.MYI records: 3
jobqueue.MYI records: 13
jumppoints.MYI records: 48
keybindings.MYI records: 257
mamemetadata.MYI records: 683
musicmetadata.MYI records: 3085
musicplaylist.MYI records: 11
neskeyword.MYI records: 76
nestitle.MYI records: 762
networkiconmap.MYI records: 52
oldprogram.MYI records: 16610
oldrecorded.MYI records: 1198
people.MYI records: 67556
profilegroups.MYI records: 17
program.MYI records: 24436
programgenres.MYI records: 36560
programrating.MYI records: 11325
record.MYI records: 311
recorded.MYI records: 732
recordedmarkup.MYI records: 1543852
recordingprofiles.MYI records: 22
recordmatch.MYI records: 2472
settings.MYI records: 551
smartplaylist.MYI records: 8
smartplaylistcategory.MYI records: 3
smartplaylistitem.MYI records: 8
videocountry.MYI records: 5
videometadata.MYI records: 419
videometadatacountry.MYI records: 9
videometadatagenre.MYI records: 6
What I did was nuke the old database, install a default 0.17 one, dump
the structure with 'mysqldump -d', and then use that to manually
convert the old database to the new structure. Now the only
differences are in the order of the keys and columns of some of the
tables. Then I turned on logging of slow queries, and got the
following
Time: 050221 13:43:09
# User at Host: mythtv[mythtv] @ localhost []
# Query_time: 22 Lock_time: 0 Rows_sent: 2473 Rows_examined: 2117916
SET timestamp=1109014989;
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 +
channel.recpriority + cardinput.preference, record.dupin,
recorded.endtime IS NOT NULL AND recorded.endtime < NOW() AS
recduplicate, record.type, record.recordid, 0, 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, (CASE record.type 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) FROM recordmatch INNER JOIN record ON
(recordmatch.recordid = record.recordid) INNER JOIN program ON
(recordmatch.chanid = program.chanid AND
recordmatch.starttime = program.starttime) 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 ON (
record.dupmethod > 1 AND program.title = oldrecorded.title
AND ( (program.programid <> '' AND NOT
(program.category_type = 'series' AND program.programid LIKE '%0000')
AND program.programid = oldrecorded.programid) OR
(oldrecorded.findid <> 0 AND oldrecorded.findid = (CASE
record.type 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.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 program.title = recorded.title
AND ( (program.programid <> '' AND NOT
(program.category_type = 'series' AND program.programid LIKE '%0000')
AND program.programid = recorded.programid) OR
(recorded.findid <> 0 AND recorded.findid = (CASE record.type
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.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)) ) ) );
This query takes 22 seconds with MyISAM, and 15 with innodb, and
happens everytime I delete or activate a recording, and is always
running when I see those pauses that were driving me crazy. The log
says there is no lock time, so my theory of this being a resource
locking issue seems disproven. At this point I am too tired to make
sense of this thing, but I figureed i should let you guys know what I
figured out so far.
More information about the mythtv-dev
mailing list