[mythtv-users] Slow MySQL query after delete

[crs23]

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.

Cheers,
-chris


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).
> 
> Mike
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
> 
> 

-- 
View this message in context: http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a12469326
Sent from the mythtv-users mailing list archive at Nabble.com.