[mythtv-users] Slow MySQL query after delete

[Michael T. Dean]

On 09/03/2007 07:31 PM, crs23 wrote:
> Michael T. Dean wrote:
>   
>> On 09/02/2007 12:10 AM, Gareth Glaccum wrote:
>>     
>>> Not sure if this helps, but this week I sent in a query about the size
>>> of the database I had, 168MB. Someone (Michael Dean I think) suggested
>>> mythfilldatabase as a cleanup method. I performed a mythfilldatabase
>>> without any extra commands (make sure it is set up correctly first),
>>> and my database shrunk from 168 to 78MB.
>>>       
>> Glad it worked for you (and thanks for paying the advice forward :).
>>     
>>> Before the cleanup, I was getting lags on deletes (I put it down to
>>> bad fragmentation and ext3, [ext3 is not meant to fragment but]
>>>       
>> Exactly.  See
>> http://www.gossamer-threads.com/lists/mythtv/users/261922#261922 .
>>     
>>> , my partition had been full (100%+) for some time, I had deleted old
>>> small programs and been recording larger programs in their place).
>>> However, after the cleanup the lag has dissapeared, I can now delete
>>> around 10G of data before things start to slow. (Originally I had
>>> discounted any database issue as it was on a seperate spindle to the
>>> myth store).
>>>       
>> You may find
>> http://www.gossamer-threads.com/lists/mythtv/users/278818#278818
>> interesting, too.  It contains a link to the results of a study by
>> Phillips Research about PVR's and fragmentation.  The most interesting
>> result of the study is that you should force your system to leave 5%
>> free space on the filesystem to prevent "runaway" fragmentation (if you
>> leave less free space, your files will tend to be broken into the same
>> number of fragments as the number of recordings on the filesystem).  You
>> can set aside free space with Myth by setting "Extra Disk Space (in
>> Gigabytes)" in mythfrontend settings (which I still need to modify to
>> allow a per-Storage-Group free space for SVN trunk users).
>>     
> This is all interesting info and I'll check on fragmentation 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.  I know that because I can run
> the query manually and it's always slow.  So I think this is exclusively a
> DB problem.  I checked and it is the case that some DB files are fragmented. 
> I'll take care of that and see what happens.
>
> I did a mythfilldatabase.  It went from 64MB to 65MB.

Right.  The fragmentation stuff was meant for Gareth since he had
mentioned it.  The mythfilldatabase suggestion (for users that don't
normally run it) from Gareth was meant for you.

Since it sounds like you had been running mfdb (meaning that DB clutter
wasn't the problem), you should probably next run optimize_mythdb.pl
from the contrib directory.

Mike