[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