[mythtv] innodb vs myism preformance

[Daniel Manjarres]

Yan-Fa Li wrote:

>True, but I was wondering if the problem was related to re-reading the 
>table after you deleted the show.  Setting the query cache might have 
>helped that case.  I've been using mysql with myisam for quite some time
>and I've not noticed multiple seconds of delay deleting shows, but maybe 
>my hardware is really slow and I'm used to the performance as is.
>  
>

One difference may be the filesystems we're using. I have ext3 on a 
700GB lvm volume. The delays get really bad when deleting more than one 
show on my system.

>OK, I think I see a problem.  Set your thread_cache_size to a non-zero 
>value.  Starting up new threads is expensive for mysql.  I'm not sure
>what the access pattern is for QTMYSQL.  Perhaps it's really smart about
>keeping a connection open to the database, or perhaps it isn't.  It's
>definitely worth a try.
>  
>

Thanks for the suggestion. I don't think thread cache is the issue at 
all (although it may be a way to avoid what looks like a pathological 
resource locking case in myth/mysql interaction). I have a dual opteron 
system, and when I delete a recording the utilization of CPU #0 hits 
100% and stays there until the ext3 filesystem finishes the unlink call, 
which leads me to think the problem is in database resource locking 
somewhere. I actually hacked things to make the function that calls 
unlink take a long time to return in the mistaken belief it would make 
the system smoother; bascially instead of calling unlink I was 
truncating the file a few tens of MBs at a time, to avoid the sudden 
disk I/O pressure on the ext3 filesystem from unlinking a giant file all 
at once. Instead of making it smoother it just made the pauses longer.

Given a choice between messing with mysql params or switching to innodb, 
I'll stick to innodb, unless somebody plans to do something in the main 
mythconverge tables that is incompatible with innodb, like the nestitle 
table is. Honestly, if I had to guess I would say the slowdown is due to 
table vs row locking, but don't take my word for it.

If you really want I can do the mysql thread cache thing, but listen: 
While running mysql, without restarting mythfrontend or mythbackend I 
can convert the tables back and forth from myisam to innodb and see the 
pausing while deleting disapear and reappear. And right now I'm running 
out of shows to delete :-)

Dan Manjarres