RT and Attachments

RT Developers
1

Hello, it seems that attachments are killing our system, even if they
are not being accessed.

Is that the attachments (standard and custom field) are returned on
every query of a ticket?

Every ticket that has an attachment greater than (roughly) 6MB is
causing our system to hand and I must then clear the mason obj cache and
restart httpd.

Is this because the attachments are stored in the database and not
published to the file system in some fashion?

Has anyone possibly created some performance patches for attachments or
created a patch that copies the files to the file system instead?

Thanks,

  • Stark
2

Jeff, as you’re writing to devel list you should know how to report
things in the right way.
We need to know versions of perl, RT, apache, FCGI/MP and DB at least.
We also need to know each your step after server restart. So you have
to stop the server, turn on debug log, prune logs or at least know
where it ends at this point, then start the server, reproduce the
problem and send us the log and shiny report.On 6/12/07, Jeff Stark JStark@sumtotalsystems.com wrote:

Hello, it seems that attachments are killing our system, even if they are
not being accessed.

Is that the attachments (standard and custom field) are returned on every
query of a ticket?

Every ticket that has an attachment greater than (roughly) 6MB is causing
our system to hand and I must then clear the mason obj cache and restart
httpd.

Is this because the attachments are stored in the database and not published
to the file system in some fashion?

Has anyone possibly created some performance patches for attachments or
created a patch that copies the files to the file system instead?

Thanks,

  • Stark

List info:
The rt-devel Archives

Best regards, Ruslan.

3

Sorry Ruslan.

RT: 3.6.3
Perl: v5.8.5 under linux RHEL 4
Apache: 2.0.52
FCGI: v0.67;
DB: 10g Enterprise Edition Release 10.2.0.1.0

Steps are quite simple at this point…someone accesses a ticket with
an attachment, after churning for several minutes, the receive a 500
error, the entire system is pretty much hosed at this point. I connect
to the box, stop httpd, clear mason obj cache, restart httpd and system
is fine, until someone accesses a ticket with a large attachment again.

Our Oracle DBA has also identified that the joins created are not
allowing Oracle to utilize indexes which is causing 13 to 15 seconds (on
average) for each page load. After changing custom fields from
attachment to text box, the system response time increased slightly to 7
to 9 seconds (on average) for each page load. Is there something I can
do to boost the performance of RT on Oracle so that it can utilize
indexes to speed up queries?

Thanks,

Jeff Stark, Sr Systems Administrator
SumTotal Systems, Inc. (Nasdaq; SUMT)

OFFICE +1 919 326 7548
MOBILE +1 919 622 0418

EMAIL jstark@sumtotalsystems.com

The contents of this communication are considered SumTotal Confidential,
and should not be shared with anyone outside of SumTotal, either
electronically or verbally, without the express permission of the
author(s).From: ruslan.zakirov@gmail.com [mailto:ruslan.zakirov@gmail.com] On
Behalf Of Ruslan Zakirov
Sent: Wednesday, June 13, 2007 4:13 AM
To: Jeff Stark
Cc: rt-devel@lists.bestpractical.com
Subject: Re: [Rt-devel] RT and Attachments

Jeff, as you’re writing to devel list you should know how to report
things in the right way.
We need to know versions of perl, RT, apache, FCGI/MP and DB at least.
We also need to know each your step after server restart. So you have to
stop the server, turn on debug log, prune logs or at least know where it
ends at this point, then start the server, reproduce the problem and
send us the log and shiny report.

4

Jeff Stark wrote:

Sorry Ruslan.

RT: 3.6.3
Perl: v5.8.5 under linux RHEL 4
Apache: 2.0.52
FCGI: v0.67;
DB: 10g Enterprise Edition Release 10.2.0.1.0

Steps are quite simple at this point…someone accesses a ticket with
an attachment, after churning for several minutes, the receive a 500
error, the entire system is pretty much hosed at this point. I connect
to the box, stop httpd, clear mason obj cache, restart httpd and system
is fine, until someone accesses a ticket with a large attachment again.
The 500 error is due to a timeout on your FCGI process (probably) and I
know that when we used FastCGI I set it to a higher value. I think the
default is 180sec and we changed it to atleast 300 but maybe even 600
because of searching in the content of email message took sometime.

Our Oracle DBA has also identified that the joins created are not
allowing Oracle to utilize indexes which is causing 13 to 15 seconds (on
average) for each page load. After changing custom fields from
attachment to text box, the system response time increased slightly to 7
to 9 seconds (on average) for each page load. Is there something I can
do to boost the performance of RT on Oracle so that it can utilize
indexes to speed up queries?
Please post query and explain plan.

Yes I have a patch specifically for searching content using OracleText
but not seeing your problem queries I can’t say if you’ll benefit from
those patches.

Joop

PS:
My config is:
VirtualMachine:Ubuntu-5.10 Svr, rt-3.6.3, apache2, mod_perl2,
perl-5.8.7, Oracle XE
Performance is OK, frontpage loading time is: ~2-3sec with 7 portlets.
(30 newest, 30 I own, quick ticket creation, Calendar, Reminders, Quick
Search (35 rows,3 columns), Don’t refresh this page)

5

Jeff Stark wrote:

Sorry Ruslan.

RT: 3.6.3
Perl: v5.8.5 under linux RHEL 4
Apache: 2.0.52
FCGI: v0.67;
DB: 10g Enterprise Edition Release 10.2.0.1.0

Steps are quite simple at this point…someone accesses a ticket with
an attachment, after churning for several minutes, the receive a 500
error, the entire system is pretty much hosed at this point. I connect
to the box, stop httpd, clear mason obj cache, restart httpd and system
is fine, until someone accesses a ticket with a large attachment again.
The 500 error is due to a timeout on your FCGI process (probably) and I
know that when we used FastCGI I set it to a higher value. I think the
default is 180sec and we changed it to atleast 300 but maybe even 600
because of searching in the content of email message took sometime.
As I understand Jeff access to a ticket, not searching for tickets,
but it’s not absolutely clear from description. Also, Jeff, can you
block access to RT to all users and try to reproduce the problem in
“single user mode”? As really it could be not big attachments, but a
search by content of attachments which terribly slow down system so
other requests to RT timeout.

We fixed an issue with searches by content of attachments in 3.6.4rc2.
Can you try it? Things should be better, still not ideal, but better.

Our Oracle DBA has also identified that the joins created are not
allowing Oracle to utilize indexes which is causing 13 to 15 seconds (on
average) for each page load. After changing custom fields from
attachment to text box, the system response time increased slightly to 7
to 9 seconds (on average) for each page load. Is there something I can
do to boost the performance of RT on Oracle so that it can utilize
indexes to speed up queries?
Please post query and explain plan.

Yes I have a patch specifically for searching content using OracleText
but not seeing your problem queries I can’t say if you’ll benefit from
those patches.

Joop

PS:
My config is:
VirtualMachine:Ubuntu-5.10 Svr, rt-3.6.3, apache2, mod_perl2,
perl-5.8.7, Oracle XE
Performance is OK, frontpage loading time is: ~2-3sec with 7 portlets.
(30 newest, 30 I own, quick ticket creation, Calendar, Reminders, Quick
Search (35 rows,3 columns), Don’t refresh this page)

List info: The rt-devel Archives

Best regards, Ruslan.

6

Ruslan Zakirov wrote:

We fixed an issue with searches by content of attachments in 3.6.4rc2.
Can you try it? Things should be better, still not ideal, but better.
Could you give us an idea to what is changed?

Joop

7

We changed SQL query we build, but this only affects queries where
“Content like x” joined with another condition, for example “Content
LIKE X OR Subject LIKE X”, but we still don’t use full text indexes
(ideal solution).On 6/13/07, Joop van de Wege JoopvandeWege@mococo.nl wrote:

Ruslan Zakirov wrote:

We fixed an issue with searches by content of attachments in 3.6.4rc2.
Can you try it? Things should be better, still not ideal, but better.
Could you give us an idea to what is changed?

Joop

Best regards, Ruslan.