[mythtv-users] Slow MySQL query after delete - On topic

[Gareth Glaccum]

From: "crs23"

>but in my case
>the videos are stored on a separate disk from the database, the database 
>was
>restored onto a clean install recently so there should be very little
>fragmentation on the DB disk, and most importantly it's not the file delete
>that's slow, it's the DB query that's slow

It was the same situation for me, I even made sure that the disks were on 
seperate SATA channels to each other. Don't know why it worked, it just did 
(possibly due to my point below?). (Since my upgrade, I have changed to only 
a single volume for both data and database, but with BB cache to deletes 
should be stored to chache, reads should be reasonably undisturbed).
For your info, all my procs are around 2GHz
My run of the query is 591 rows in 1.13 seconds on my new hardware

On my old server, after optimisation: 386 rows in 0.49 sec (85 meg database)
On my old server, before optimisation (restored old database into old file) 
15042 rows in 15.11 sec (176 meg database), so even if the issue was 
exponential, it kinda makes your result a little dissapointing.
Running filefrag on the database, shocking. Some good bits, some horrible 
bits:
mythconverg/recordedseek.MYI: 13444 extents found, perfection would be 1 
extent
<OUCH>
after a short optimise on that (my FS is only 79% full, what is wrong here?)
mythconverg/recordedseek.MYI: 185 extents found, perfection would be 1 
extent, got the time down to 14 sec.


When you select delete, do you delete, or delete and re-record?
The database being a file that grows over time, perhaps this itself is 
suffering from fragmentation, even though it is on a different filesystem to 
your recorded data? (where are the straws?)