[rt-users] Attachments table of RT's Mysql database

Tue Aug 7 10:39:18 EDT 2007

I personally agree with Boris on this. This should be a configuration option to either store attachments in the database or on the web front end.

In most enterprise applications you don't store files or session state in the database unless you have a really good reason too. The overhead added by sessions and files results in increased reads from the web server to verify session or large database sizes to store the files in blobs.  

Justin Brodley 

-----Original Message-----
From: rt-users-bounces at lists.bestpractical.com [mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Boris Lytochkin
Sent: Tuesday, August 07, 2007 6:31 AM
To: rt-users at lists.bestpractical.com
Subject: Re[2]: [rt-users] Attachments table of RT's Mysql database

Ken,

> First, if everything is inside a database, then a
> simple backup of the database will get everything related to a
> particular RT instance.
Wrong. We _stopped_ backup process of RT database due to LARGE amount
data every day. We have no such amount of tape to store DB's everyday
backups.
Now, DB backup is done every day and attachment backup is done
separately. As a result we have everyday SQL-backup of DB and
incremental backup for attachments. It uses much less space.

> Second, in many cases you would like to
> isolate the front-end from the back-end information store. Once
> you need access to the filesystem, everything becomes much more
> involved.

I understand that storing attachments out of RT involves much more
than DB-only solution, BUT 10 Gb DB with 9.5 Gb of images involves much more.

Anyway, it is up to admin to decide whether to store attachments
separate or not.

Tuesday, August 7, 2007, 4:22:49 PM, you wrote:

> Dear Mr. Lytochkin,

> There are two very good reasons to not store attachments outside
> of the database. First, if everything is inside a database, then a
> simple backup of the database will get everything related to a
> particular RT instance. Second, in many cases you would like to
> isolate the front-end from the back-end information store. Once
> you need access to the filesystem, everything becomes much more
> involved. I am certain that there are other reasons, but those
> two are certainly enough for me. I have appreciated the ease of
> generating a consistent backup of my RT information store.

> Ken

> On Tue, Aug 07, 2007 at 04:15:02PM +0400, Boris Lytochkin wrote:
>> I wrote a patch that allows to store non-text attachments to be
>> stored out of DB - in my case it greatly reduced DB swelling.
>> Just for now it uses constant string in Attachments->Content to
>> indicate that file is written to FS.
>> 
>> You will need to specify some variables in RT_Siteconfig.pm:
>> Set($AttachmentsDirectory, '/var/RT/attachments');
>> Set($LogAttachmentsLoading, "1");
>> Set($LogAttachmentsSaving, "1");
>> Set($StoreNonTextAttachmensInDB, undef);
>> #Set($StoreNonTextAttachmensInDB, "1");
>> 
>> A new share/html/Ticket/Attachment/dhandler and attach.patch for
>> rest of RT distribution is in attachment.
>> 
>> 
>> Gregory Harper, you can find more complex set of patches allowing to
>> produce & show image thumbs automaticly in attachment too.
>> Some more variables must be specified in RT_Siteconfig.pm
>> 
>> Set($ShowTransactionImages, 1);
>> Set($ProduceImageThumbs, 1);
>> Set($ImageThumbsDirectory, '/var/RT/thumbs');
>> 
>> 
>> I wonder why bestprcactical is not interested in intergating these
>> patches into RT:
>> From: Jesse Vincent
>> Sent: 21 march 2007 ?., 23:53
>> To:   lytochkin
>> Subject: [RT 3.6] Storing attachments away from DB
>> Hi Boris,
>> 
>> Thanks very much for the mail, but I think we're not really  
>> interested in offering this feature within RT.
>> Best,
>> Jesse
>> 
>> 
>> 
>> 
>> -- 
>> Boris Lytochkin,
>> JSC e-port, Moscow
>> web: www.e-port.ru, wap: wap.e-port.ru
>> tel: +7 (495) 777 1872, ext. 251
>> 
>> 
>> ____________
>> 
>> Date: Mon, 06 Aug 2007 12:31:31 -0500
>> From: Gregory Harper <gregh at stevensind.com>
>> Subject: Re: [rt-users] Attachments table of RT's Mysql database
>> To: Justin Brodley <jbrodley at sumtotalsystems.com>
>> Cc: rt-users at lists.bestpractical.com
>> Message-ID: <46B75AF3.3080200 at stevensind.com>
>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>> 
>> Justin Brodley wrote:
>> > We actually had to disable the attachment feature as we were having our customers attach enormous files and killed our DB processing. Ultimately we
>> are looking
>> > into rewriting the attachment feature to store the attachments on the web server to alleviate this overhead from the DB. I understand that the
>> attachment table also
>> > stores all updates to a ticket, not just the attachments. 
>> >
>> >
>> > Justin Brodley 
>> >
>> >
>> >
>> > -----Original Message-----
>> > From: rt-users-bounces at lists.bestpractical.com [mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Gregory Harper
>> > Sent: Wednesday, August 01, 2007 12:41 PM
>> > To: rt-users at lists.bestpractical.com
>> > Subject: [rt-users] Attachments table of RT's Mysql database
>> >
>> > Hello everybody.
>> >
>> >    We've been using RT for more than three months as part of our 
>> > customer concern processes.  Overall, things have been going well.
>> > The configuration includes Mysql, Apache2 and Postfix running on Ubuntu 
>> > 6.06.  I've made no modifications to the databases.
>> > The primary concern at this point is that the Attachments table of the 
>> > Mysql database is growing significantly.  Our CSR's want to attach
>> > PDFs, jpegs, etc. to the tickets with the jpegs usually created by our 
>> > customers.  The digital photos are the main culprit. I've read about
>> > scaling back the photos, creating thumbnails, etc. and we need to find a 
>> > way to limit the attachment size prior to attachment.
>> >
>> >   Has anyone else using RT had this type of problem?
>> >
>> >   What are the best approaches for minimizing and controlling the size 
>> > of the Attachments table?
>> >
>> > Any information, feedback and guidance are appreciated.
>> >
>> > thanks - Gregory Harper , Stevens Industries
>> >
>> >
>> >  
>> >
>> >
>> > _______________________________________________
>> > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>> >
>> > Community help: http://wiki.bestpractical.com
>> > Commercial support: sales at bestpractical.com
>> >
>> >
>> > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
>> > Buy a copy at http://rtbook.bestpractical.com
>> >
>> >   
>> Thanks Justin for the feedback.  Anyone else have input regarding their 
>> experiences with Attachments and RT?
>> 
>> thanks - Greg
>> 
>> 
>> 
>> ------------------------------
>> 
>> Message: 3
>> Date: Mon, 6 Aug 2007 11:53:13 -0700 (PDT)
>> From: Ed Matthews <g8orade at yahoo.com>
>> Subject: [rt-users] Kwiki Table Rendering?
>> To: rt-users at lists.bestpractical.com
>> Message-ID: <569062.32875.qm at web51102.mail.re2.yahoo.com>
>> Content-Type: text/plain; charset=us-ascii
>> 
>> Happy Monday.
>> 
>> Does anyone know why Kwiki renders tables on this page,
>> http://wiki.bestpractical.com/view/ManualScrips
>> 
>> but not on this page
>> http://wiki.bestpractical.com/view/ManualRights
>> nor in my personal page .
>> 
>> I tried copy pasting the first ManualScrips table wiki markup into another edit page and saving, and it wouldn't render there either after save.
>> 
>> Ed Matthews
>> g8orade at yahoo.com
>> 
>> 
>> 
>> 
>>        
>> ____________________________________________________________________________________
>> Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more. 
>> http://mobile.yahoo.com/go?refer=1GNXIC
>> 
>> 
>> ------------------------------
>> 
>> Message: 4
>> Date: Tue, 7 Aug 2007 10:20:33 +0100
>> From: Luke E Morgan <lmorgan at mtl-inst.com>
>> Subject: [rt-users] Trying to change the logo. Fedora Core 7,   RT3.6.3
>>         yum install
>> To: rt-users at lists.bestpractical.com
>> Message-ID:
>>         <OF74585201.1DEBE7DD-ON80257330.003044E6-80257330.0033159C at mtl-inst.com>
>>         
>> Content-Type: text/plain; charset="us-ascii"
>> 
>> 
>> I'm sure I'm just missing something simple, but I cannot seem to change the
>> BP logo on my install of RT 3.6.3 on Fedora Core 7.
>> I've installed RT using yum (lazy I know, but it works apart from this
>> issue).
>> 
>> It has been installed into
>> /usr/share/rt3
>> I've created /usr/local/rt3/html/Elements/
>> and copied the Logo file into that directory.
>> 
>> I've added the logo I want to use into /usr/share/rt3/html/NoAuth/images
>> It is a 92x50pixels jpeg.
>> 
>> Following suggestions from the wiki and the mailing list archives, I put
>> this line into the Logo file :
>> <a href="<%$RT::LogoLinkURL%>"><img src="<%$RT::LogoURL%>" alt="Intranet"
>> width="<%$RT::LogoWidth%>" height="<%$RT::LogoHeight%>" /></a>
>> 
>> I have modified by RT_SiteConfig.pm file.
>> If I log into the web interface, under tools, choose system configuration,
>> the following variables are set :
>> RT::LogoLinkURL http://192.168.66.1
>> RT::LogoURL /rt3/NoAuth/images/mtllogo.jpg
>> RT::LogoWidth 92
>> RT::LogoHeight 50
>> RT::MasonDataDir /var/cache/rt3/mason_data
>> 
>> I've tried stopping the webserver, deleting Logo.obj from
>> /var/cache/rt3/mason_data/obj/3989424063/standard
>> When I restart apache, and refresh the RT page, Logo.obj is recreated with
>> the best practical logo in it.
>> 
>> Where am I going wrong ?  Can anyone point me in the right direction please
>> ?
>> 
>> Thanks in advance
>> 
>> Luke
>> 
>> _____________________________________________________________
>> 
>> This email message may contain privileged/confidential information and/or
>> copyright material.  It is intended only for the use of the person(s) to whom 
>> it is addressed and any unauthorised use may be unlawful.  If you receive this
>> email by mistake, please advise the sender immediately by using the reply
>> facility in your email software and delete the material from your computer.
>> 
>> The material contained in this message does not constitute a binding
>> contract with any company within the MTL Instruments Group plc.  Opinions,
>> conclusions and other information in this email that do not relate to the official
>> business of this organisation shall be understood as neither given nor endorsed 
>> by it.  Registered in England No. 1871978, VAT Reg. No 449343040, 
>> MTL Instruments Ltd, Power Court, Luton, LU1 3JJ
>> 
>> -------------- next part --------------
>> An HTML attachment was scrubbed...
>> URL: http://lists.bestpractical.com/pipermail/rt-users/attachments/20070807/37bbd464/attachment-0001.htm
>> 
>> ------------------------------
>> 
>> Message: 5
>> Date: Tue, 7 Aug 2007 12:39:04 +0100
>> From: Luke E Morgan <lmorgan at mtl-inst.com>
>> Subject: Re: {Disarmed} [rt-users] Trying to change the logo. Fedora
>>         Core 7, RT3.6.3 yum install
>> To: Boris Jordanov <jordanov at brg.bg>
>> Cc: rt-users at lists.bestpractical.com
>> Message-ID:
>>         <OF7F7C160E.3A42C7E0-ON80257330.003EF00C-80257330.003FC412 at mtl-inst.com>
>>         
>> Content-Type: text/plain; charset="us-ascii"
>> 
>> Skipped content of type multipart/alternative-------------- next part --------------
>> A non-text attachment was scrubbed...
>> Name: graycol.gif
>> Type: image/gif
>> Size: 105 bytes
>> Desc: not available
>> Url : http://lists.bestpractical.com/pipermail/rt-users/attachments/20070807/910b3d6a/graycol.gif
>> -------------- next part --------------
>> A non-text attachment was scrubbed...
>> Name: pic05705.gif
>> Type: image/gif
>> Size: 1255 bytes
>> Desc: not available
>> Url : http://lists.bestpractical.com/pipermail/rt-users/attachments/20070807/910b3d6a/pic05705.gif
>> -------------- next part --------------
>> A non-text attachment was scrubbed...
>> Name: ecblank.gif
>> Type: image/gif
>> Size: 45 bytes
>> Desc: not available
>> Url : http://lists.bestpractical.com/pipermail/rt-users/attachments/20070807/910b3d6a/ecblank.gif
>> 
>> ------------------------------
>> 
>> _______________________________________________
>> RT-Users mailing list
>> RT-Users at lists.bestpractical.com
>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>> 
>> 
>> End of RT-Users Digest, Vol 41, Issue 20
>> ****************************************
>> 
>>  

>> _______________________________________________
>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>> 
>> Community help: http://wiki.bestpractical.com
>> Commercial support: sales at bestpractical.com
>> 
>> 
>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
>> Buy a copy at http://rtbook.bestpractical.com

-- 
Best regards,
 Boris Lytochkin                            mailto:lytochkin at e-port.ru

_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales at bestpractical.com

Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com