[mythtv] innodb vs myism preformance

[Daniel Manjarres]

Yan-Fa Li wrote:

>Daniel Manjarres wrote:
>  
>
>>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.
>>
>>    
>>
>
>Interesting.  I run the db on a separate file system.  You're running
>everything on one file system ?  Ext3 is kind of famous for slow deletes
>as I remember.  My video is stored on xfs, my database on ext3.  All my
>disks are RAID1 arrays with LVM overlays of smaller partitions.  Have 
>you tried moving the database to it's own file system separate from your
>video ?
>  
>
My main unix filesystems are on 10,000 RPM scsi disks in raid 5. My tv 
filesystem is slow, big IDE disks.

>From your description, it seems that mythtv is blocking mysql from 
>doing file actions due to your filesystem choice.  This is pretty relevant,
>because myisam is file based.  Each table being 3 discrete files, data,
>index and meta data.  Innodb is a binary blob and probably does a lot of
>caching in memory, deferring writes.  I think this is why your 
>performance goes up by switching db.
>  
>

You know, you're prety smart, dude. But a 100% CPU spike tells me that 
somebody (exactly 1 thread, since I have 2 cpus and one is mostly 
idle)is spining on a resource lock, not waiting for disk acceess, and 
since my GUI is ot responsive I am guessing it's the frontend that's 
doing the waiting. Innodb is log structured, so that does reduce the 
size of incremental  update writes, but c'mon deleting a row shouldn't 
take several seconds, no matter how busy the disk is, or else mysql 
would die under low-moderate loads. Benchmarks are in kilo queries per 
second, not seconds per query.

>I would try a couple of things:
>
>	a) move the db files to a separate disk partition, or even as a
>	test a different machine and use myisam.
>  
>
Check. 10,000 RPM u160 scsi raid 5.

>	b) if you can afford the downtime or loss of shows, try
>	different filesystem types.  I find xfs to be quite stable
>	these days under 2.4.29 and I'm sure 2.6.10 is pretty good
>	too.
>  
>
Never going to happen. I am not losing my archives of The Daily Show. 
Like I said I tried to relieve disk I/O pressure by repeatedly 
truncating the file and sleeping for awhile instead of unlinking it, and 
it made things WORSE, not better.

>Good luck,
>  
>

Thanks for all the ideas, I'm sure somebody will get to the bottom of 
this, eventually.

Dan Manjarres