[mythtv-users] Slow MySQL query after delete

[Michael T. Dean]

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).

Mike