[mythtv] innodb vs myism preformance

[Daniel Manjarres]

On Fri, 18 Feb 2005 09:36:33 +0000, Ed Wildgoose <lists at wildgooses.com> wrote:

>  From your symptoms and without looking at the code, my first thought
> was some kind of transaction wrapping in the myth delete function so
> that the metadata delete and disk delete would both be sure to happen.
> However, in retrospect I think that's a bit advanced for mysql.
> 
> So that suggests that the next most likely place to look is the recorded
> markup table.  If you have an mpeg2 recording then you have a huge bunch
> of rows stored in this table which have to be deleted.  Can you try just
> changing this single table and re-benching to see what happens?

I can give it a shot, but all my recordings are mpeg4 (if that matters
to the size of recordedmarkup). Ok, the results are:

still fast with myisam for recordedmarkup.

slow as shit as MYISAM for recorded and oldrecorded as well (we're closing in!)

I convert everything back to MYISAM..... slow, but not really slower
than half innodb

convert only recorded and oldrecorded to innodb.........  OK, it's
fast enough. The delay is about 1/2 a second, which I can deal with.

convert oldrecorded back to MYISAM........ The first delete is 1/2
second, the second takes 6 seconds.

convert recorded to MYISAM, oldrecoreded back to innnodb........first
delete takes 10 seconds, AND a a new stutter appears after the
recording is done (this may be uncorrelated).
(btw completely converting the tables takes 0.0 seconds)

for confirmation: convert recorded to innodb...... fast again.


analysis: recorded and oldrecorded slow the GUI down when they're
MYISAM. These tables are only about a thousand rows, so there's no way
this is  datbase disk write problem, it can completely rewrite the
tables in a millisecond. There's some resource locking problem
somewhere, and innodb avoids the problem. MYISAM should be just find
for these tiny tables, the bug is in myth or qt.

> Failing that I think switching that table to innodb sounds like the way
> ahead...

For me it is.... I have carpal tunnel and ain't working on this
anymore, sorry guys.

> Curious to hear if you can track it down further to a single table

Great idea. Btw Iam using mysql 4.0.23_Debian-4-log.

Thanks for the ideas, guys.

Dan Manjarres