[mythtv-users] Slow MySQL query after delete - On topic
gareth.glaccum at btopenworld.com
Wed Sep 5 20:55:41 UTC 2007
>but in my case
>the videos are stored on a separate disk from the database, the database
>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
mythconverg/recordedseek.MYI: 13444 extents found, perfection would be 1
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?)
More information about the mythtv-users