[rt-users] Performance with many attachments and transactions

[Kevin Murphy]

On Thu Nov 17 16:07:51 EST 2005, Jesse Vincent wrote:
 > On Thu, Nov 17, 2005 at 04:05:50PM -0500, Vivek Khera wrote:
 >> On Nov 17, 2005, at 12:07 PM, Kevin Murphy wrote:
 >>
 >> >There are 433 separate database queries made in the course of 
 >> >loading this ticket, whose cumulative time (as measured in the 
 >> >postgresql log file) is just 1.19 seconds, but the entire load 
 >> >takes 13-14 seconds
 >>
 >> Did you also measure the time to transfer the bits from the Pg server 
 >> to the web server?  That may also be a bottleneck.  I can literally 
 >> see the delay in painting the page when RT encounters a large 
 >> attachment only to report a link with the size of the attachment.
 >
 > Uh. It shouldn't be doing that with a modern version of RT. (If it is,
 > we're missing a ->columns() somewhere.

Oh ho:  in my case, Vivek is right.  The file attachments seem to be 
sent to RT from the database even when they are not displayed.

I just ran tcpdump on the PostgreSQL socket held by standalone_httpd, 
and the total amount of traffic on that port was 755kb.  The sum of the 
sizes of the attachments on this ticket is 620kb.  Also, I can see text 
from some of the non-displayed text attachments in the tcpdump packet 
output.  Not that it matters, but 154kb of the attachments are from 
binary files, the remaining 466kb are from .txt or .html files.  The 
generated HTML is 116kb.  MaxInlineBody is set to 10000, which excludes 
all but 18.2kb of the attachments from being displayed.  Possibly 
relevant: I must confess that I've made the following customization to 
ShowTransactionAttachments, which prevents any of the file attachments 
from being displayed:

    if (   $message->ContentType =~ m{^(text|message)}i
        && !$message->Filename   # <== prevent any file attachment from 
being displayed in-line
        && $size <= $RT::MaxInlineBody )

I'm using RT 3.4.4, PG 8.0.4, DBIx::SearchBuilder v1.35.

Other than the above customization, I have made only 3 or 4 cosmetic 
customizations of stock RT source files.

Assuming that my change above doesn't inadvertently drag file contents 
over ... could this somehow be a postgresql-specific problem?  Can 
somebody else who uses postgresql see if they have this problem also?

I'll try to debug this, but I'm not familiar with RT internals, so I may 
be a bit slow (and unfortunately I can't get the Perl profiler and 
debugger to work with standalone_httpd on my platform: Mac OS X 10.4.3, 
Perl 5.8.7 from source).

Thanks,
Kevin Murphy