Hello,
We are trying to identify very poor performance levels of the RT
Application 3.6.3 on REHL/Apache with Oracle. In doing so, we had our
DBAs take a look at the Oracle box and the identified some very
expensive queries that were taking nearly 10 seconds to return, such as:
SELECT * FROM
( SELECT limitquery., rownum limitrownum FROM
( SELECT main. FROM
( SELECT DISTINCT main.id
FROM CustomFields main
JOIN ObjectCustomFields
ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField = main.id )
WHERE (main.Name = ‘Customer’)
AND (ObjectCustomFields_1.ObjectId =
‘0’)
AND (main.LookupType =
‘RT::Queue-RT::Ticket’) ) distinctquery,
CustomFields main
WHERE (main.id = distinctquery.id)
ORDER BY main.SortOrder ASC, main.id ASC )
limitquery WHERE rownum <= 1 )
WHERE limitrownum >= 1
Their concern is mainly around the joins being inequality joins, as even
indexes won’t help with these types of queries. Has anyone seen this
same behavior and/or has anyone done anything to modify the system to
generate better join clauses in the queries?
Also I must note, we only have 450 tickets in the system at this
time…this was in just over a week with less than half of the users
active, so we expect the number to grow significantly as we onboard
additional users.
Thanks,
Jeff Stark
Jeff,
It sounds like you may need to make some more indexes. Please
have your DBAs provide a plan for the query execution. Look
for sequential scans in particular. That may help you identify
possible index creation options. We use PostgreSQL here, but
I would suspect that many of the index creations needed for it
to be performant would be the same with Oracle. There are a
couple of posts about PostgreSQL tuning in the mailing list
that you can refer too. Good luck.
KenOn Tue, May 15, 2007 at 09:37:11AM -0700, Jeff Stark wrote:
Hello,
We are trying to identify very poor performance levels of the RT
Application 3.6.3 on REHL/Apache with Oracle. In doing so, we had our
DBAs take a look at the Oracle box and the identified some very
expensive queries that were taking nearly 10 seconds to return, such as:
SELECT * FROM
( SELECT limitquery., rownum limitrownum FROM
( SELECT main. FROM
( SELECT DISTINCT main.id
FROM CustomFields main
JOIN ObjectCustomFields
ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField = main.id )
WHERE (main.Name = ‘Customer’)
AND (ObjectCustomFields_1.ObjectId =
‘0’)
AND (main.LookupType =
‘RT::Queue-RT::Ticket’) ) distinctquery,
CustomFields main
WHERE (main.id = distinctquery.id)
ORDER BY main.SortOrder ASC, main.id ASC )
limitquery WHERE rownum <= 1 )
WHERE limitrownum >= 1
Their concern is mainly around the joins being inequality joins, as even
indexes won’t help with these types of queries. Has anyone seen this
same behavior and/or has anyone done anything to modify the system to
generate better join clauses in the queries?
Also I must note, we only have 450 tickets in the system at this
time…this was in just over a week with less than half of the users
active, so we expect the number to grow significantly as we onboard
additional users.
Thanks,
Jeff Stark
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Jeff,
It sounds like you may need to make some more indexes. Please
have your DBAs provide a plan for the query execution. Look
for sequential scans in particular. That may help you identify
possible index creation options. We use PostgreSQL here, but
I would suspect that many of the index creations needed for it
to be performant would be the same with Oracle.
Sadly, no. Every database really does want a different set of
indexes. In particular, Oracle seems to strongly prefer single-column
indexes. (And can use many of them on the same query.)
The last time we saw test data with mysql and postgres, they dealt
better with multi-column indexes.
Best,
Jesse
PGP.sig (186 Bytes)
Thanks Ken and Jesse. We will continue to evaluate to see if we can
find some performance gains in other ways.
-Stark-----Original Message-----
From: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Tuesday, May 15, 2007 12:47 PM
To: Kenneth Marshall
Cc: Jeff Stark; rt-users
Subject: Re: [rt-users] Expensive Queries
On May 15, 2007, at 12:43 PM, Kenneth Marshall wrote:
Jeff,
It sounds like you may need to make some more indexes. Please have
your DBAs provide a plan for the query execution. Look for sequential
scans in particular. That may help you identify possible index
creation options. We use PostgreSQL here, but I would suspect that
many of the index creations needed for it to be performant would be
the same with Oracle.
Sadly, no. Every database really does want a different set of indexes.
In particular, Oracle seems to strongly prefer single-column indexes.
(And can use many of them on the same query.) The last time we saw test
data with mysql and postgres, they dealt better with multi-column
indexes.
Best,
Jesse
Thanks Ken and Jesse. We will continue to evaluate to see if we can
find some performance gains in other ways.
-Stark
Are the queries repeated? If so then memcached might be able to help you
out. Memcached - Wikipedia
-Brian
Brian Gupta
Time Inc
Information Technology Dept
212-522-1401
Has this been integrated with RT?
Justin BrodleyFrom: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of
Brian_Gupta@timeinc.com
Sent: Tuesday, May 15, 2007 11:00 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Expensive Queries
Thanks Ken and Jesse. We will continue to evaluate to see if we can
find some performance gains in other ways.
-Stark
Are the queries repeated? If so then memcached might be able to help you
out. Memcached - Wikipedia
-Brian
Brian Gupta
Time Inc
Information Technology Dept
212-522-1401
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Has this been integrated with RT?
Justin Brodley
I suspect that most people are running RT with MySQL, so it is quite
unlikely that enough people have run into performance issues that need
to be resolved by memcached. That said, I’d imagine it would be fairly
trivial to get up and running.
I’d be interested in hearing what Jesse has to say on this topic.
-Brian
Brian Gupta
Time Inc
Information Technology Dept
212-522-1401
Justin,
I never saw any problem plans posted, but some of the most needed
indexes reduced the need for a sequential scan of a table by replacing
it with an index lookup. Here are some candidates to check for in your
DB instance:
CREATE UNIQUE INDEX Queues1 ON Queues (lower(Name)) ;
CREATE INDEX GroupMembers1 ON GroupMembers (GroupID);
CREATE UNIQUE INDEX Users1 ON Users (lower(Name)) ;
CREATE INDEX Users2 ON Users (lower(EmailAddress));
CREATE INDEX Tickets4 ON Tickets (Status);
Again look for slow queries caused by sequential scans of a
table. I think that Oracle has something equivalent to the
functional indexes above to allow you to index the lowercased
values. Good luck in your performance problem seek-n-destroy.
KenOn Tue, May 15, 2007 at 11:08:14AM -0700, Justin Brodley wrote:
Has this been integrated with RT?
Justin Brodley
-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of
Brian_Gupta@timeinc.com
Sent: Tuesday, May 15, 2007 11:00 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Expensive Queries
Thanks Ken and Jesse. We will continue to evaluate to see if we can
find some performance gains in other ways.
-Stark
Are the queries repeated? If so then memcached might be able to help you
out. Memcached - Wikipedia
-Brian
Brian Gupta
Time Inc
Information Technology Dept
212-522-1401
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
We are running RT 3.4.5 on PostgreSQL with 1200 open tickets and
110k tickets processed and growing. We have no performance problem
to speak of. How are you generating the slow query?
KenOn Tue, May 15, 2007 at 02:18:15PM -0400, Brian_Gupta@timeinc.com wrote:
Has this been integrated with RT?
Justin Brodley
I suspect that most people are running RT with MySQL, so it is quite
unlikely that enough people have run into performance issues that need
to be resolved by memcached. That said, I’d imagine it would be fairly
trivial to get up and running.
I’d be interested in hearing what Jesse has to say on this topic.
-Brian
Brian Gupta
Time Inc
Information Technology Dept
212-522-1401
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
They are just saved searches we are displaying on the home page…I am
not sure exactly where that specific query that I posted was created,
but where we are seeing the biggest performance hit is on RT at a
Glance.
Thanks for the feedback on the indexes, we shall give them a try.
We are running RT 3.4.5 on PostgreSQL with 1200 open tickets and 110k
tickets processed and growing. We have no performance problem to speak
of. How are you generating the slow query?
Ken