[rt-users] Option to store attachments on the filesystem

[ktm at rice.edu]

On Wed, Dec 21, 2011 at 11:12:04PM +0000, Geoff Mayes wrote:
> Hello RT Users and Developers,
> 
> Our RT instance at the University of Oregon is outgrowing the standard settings in some ways.  One way is with attachments.  The size of our database is 15.3GB and 13.7GB of that comes from the Attachments table.  If our attachments were stored on a high-performance fileserver (or locally if you prefer), our database would shrink to 1.6GB.  This would have numerous positive ramifications:
> 
> - Database dumps/backups would finish in 1/10 the time
> - Database restores would finish in 1/10 the time
> - Planned downtimes and disaster recovery situations could be more nimbly performed (scp'ing around the db dump, restoring, etc)
> - Backups could be taken much more frequently
> - More backups could be stored
> - MySQL replication would be more robust with less binary data to chew on
> - Larger attachments could be permitted because there would be less fear of the database growing too quickly
> - Reduced database load querying/inserting/deleting/joining attachments
> 
> I've read in previous posts to this mailing list (see below) that the arguments against this are that (1) attachments on the filesystem can't be searched and (2) the data backing the application will not be in one tidy database package but instead spread out across the db and filesystem.  For our instance we don't care about #1, and for #2, while I understand the argument, I would actually argue the opposite: when attachments are on a high-performance, redundant SAN managed by a dedicated storage team that I don't have to worry about, my job administering RT just got a whole lot easier because I only have to worry about ensuring the fileserver is mounted and $AttachmentsPath (just an example config option) is properly set.  I worked previously at a company that ran one of the largest instances of Bugzilla in the world and we served up 30TB of attachments over a fileserver without any problems.  Can you imagine those attachments in a MySQL database?  When ticket tracking syste
>  ms are no longer small-ish, moving attachments out of the database becomes a must.
> 
> I'm not asking the RT folks to switch attachment storage to the filesystem instead of the database.  My wish is that RT offers its administrators the ability to choose one or the other.  I know this has been a hot topic in the past, but I was hoping we could revisit the issue.  Best Practical folks -- are you open to this?  If so, would it help the process if I did all the work and submitted a patch?  If so, should I file a bug so that we can talk about the way you would like this implemented?
> 
> Given my reading of the history of this issue, I think a lot of folks would benefit from this feature.  I've included previous postings about this issue below.  Let me know if I can help and how I can.  We would love to upstream a patch so our local instance doesn't diverge too severely from you all.
> 
> Thanks for your consideration, Geoff Mayes
> 
> One of the first, meaty discussions:
> http://www.gossamer-threads.com/lists/rt/devel/706
> http://www.gossamer-threads.com/lists/rt/devel/37733
> http://www.gossamer-threads.com/lists/rt/users/39507
> The best discussion of the issue:
> http://www.gossamer-threads.com/lists/rt/users/67406
> Best Practical has recently worked on this issue:
> http://www.gossamer-threads.com/lists/rt/users/89596
> 

Hi Geoff,

I had thought that something like this had already been implemented
by Best Practical for a customer. Hopefully, they can provide some
feedback regarding the utility and possible problems of such an
approach from personal experience. Maybe they would consider releasing
it as an extenstion.

As far as the assertion that "a lot of folks would benefit from this
feature", I doubt that would be the case for the vast majority of RT
users. Most users can handle "one-stop-shopping" type applications
with far fewer problems. Once you divorce the metadate repository
from the actual ticket data, you add a whole slew of different failure
modes that will require much more sophisticated administration processes
to prevent, ameliorate, or recover from. Your reference to leveraging
an existing SAN+SAN management team gives a hint to the increase in
both complexity and cost of running an instance.

There are a wide range of RT users from systems that manage a handful
of tickets a week all the way to systems handling thousands of tickets
or more a week. Those on the small end can/should use whatever DB
backend that they are familiar with to simplify administration and
the "what did I do?!" errors due to a lack of familiarity. As you
move towards larger implementations, your DB backend needs to be
chosen based on it viability in an enterprise/large-scale environment.
I do not know the level of your local MySQL expertise and I am certainly
not a MySQL expert, but a 15GB database does not strike me as particularly
large, by any metric. Maybe you would benefit by changing your backend DB
to something that scales better. I know that other DBs support tablespaces
that can allow you to move certain tables to different filesystems/locations
to provide for more parallel partitioning across more I/O resources.

Sorry for the slight ramble. I am looking forward to this discussion and
if this feature is added some documentation describing when and when not
to use it will be essential.

Regards,
Ken
> --------
> RT Training Sessions (http://bestpractical.com/services/training.html)
> * Boston  March 5 & 6, 2012
>