[rt-users] Fwd: Attachments table constantly fragmenting

[Rob Munsch]

i am incapable of clicking reply-all today
i blame the weather.

On Wed, Feb 18, 2009 at 1:37 PM, Tom Lahti <toml at bitstatement.net> wrote:
>> All good points.  I guess what i need to balance against this is: will the
>> fragmentation continue to worsen?  Is it inevitable that performance will,
>> at some point, deteriorate?
>
> I have another database (not RT) in mysql InnoDB tables that is about 2
> years old and has 15 million records.  I have never optimized it.
> Fragmentation worsens mainly when you DELETE and then INSERT; this
> particular database almost never has DELETE operations, similar to RT.

Makes sense.  The idea is to keep all this for stats and history
anyway, no plans on deleting anything.

>> There's a daily 4 AM backup of the DB, so after that finishes, once a month
>> (barring quicker performance deterioration) seems like a good idea.  FYI,
>> the reason i'm asking all these hypotheticals is that the DB is not very
>> large now, but will increase exponentially over the next year.  Data will
>> increase, usage will increase, and the rate of increase will increase... i
>> want some best practices in place, however general, before things really
>> ramp up.  As i have a window of a few hours a month when no one will be
>> using the DB, i see no contraindications to using it - yah?
>
> If I had an installation where I was adding 1,000 tickets a day and almost
> all with attachments, I would definitely consider it.  I don't know what
> size you're dealing with.

Not quite that volume, but getting there, with a strong likelihood of
increased growth over the next few years. An attachment-less ticket
will be the exception.  Thanks for all the info; a noninvasive monthly
OPTIMIZE seems like it won't hurt anything, and making a schedule for
it now means it'll be in place when it matters later, and i won't have
to get users used to the maintenance window :)

-- 
/chown -R us:us /yourbase