[Rt-devel] 3.4.0rc1 slow

[David Kerry]

Hello,

I've been spending some time with 3.4.0rc1 lately with some of
our existing data and have found some issues related to slow
or excessive database access.

Setup:

Postgres 7.4.6
RT 3.4.0rc1
SearchBuilder 1.16
Apache 2+mod_perl2 (Debian sarge)

Hardware: dual 3Ghz Xeon server

Both rt and database are on the same machine.

I have 2 issues:

1) The query builder page (when you hit the 'tickets' link) is
   exceptionally slow to initially come up.  About 10 seconds just
   to bring up the empty form.  Is this normal?

   I've looked at the postgres logs, and it seems to do many many iterations
   of this type of query:

   LOG:  duration: 1983.993 ms  statement:                                                                                         
              SELECT DISTINCT main.* FROM (Groups main LEFT JOIN Principals Principals_2 ON ( main.id = Principals_2.id)),
   ACL ACL_1 WHERE ((ACL_1.RightName = 'OwnTicket')) AND ((Principals_2.Disabled = '0')) AND ( ( ACL_1.PrincipalId = main.id AND
   ACL_1.PrincipalType = 'Group' AND ( main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain =
   'ACLEquivalence')) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 102) ) AND main.Type = ACL_1.PrincipalType) ) AND
   (ACL_1.ObjectType = 'RT::System' OR (ACL_1.ObjectType = 'RT::Queue' AND ACL_1.ObjectId = 102) ) ORDER BY main.Name ASC

   At the time, there were about 50k+ users and 20k+ groups in the
   database.


2) The method used in the Postgres version of SearchBuilder/Handle/Pg.pm
   for insert() is suboptimal.  I've turned on postgres logging to dump
   any queries taking > .5 seconds and the query in this method comes up
   continually:

   ie:

   postgres[18131]: [6610-1] LOG:  duration: 881.154 ms  statement: SELECT id FROM Attachments WHERE oid = '3609306'

   From doing a bit of reading in the postgres docs, the oid should not
   be used for this type of thing - they can/will roll, which can lead
   to duplicates too.

   The same query, selecting on 'id' from the Attachments table is
   virtually instantaneous.

   I would highly recommend taking the approach used in the Oracle version
   of this class and select the next id from a sequence and insert using
   that instead of depending on the database to auto-generate an id and
   then having to figure out what id it generated.

   Alternately - use guids everywhere instead of sequences/ids - easy to
   generate, avoids duplicate key issues on imports and has many other
   benefits.

   This becomes a serious issue when you throw database replication into
   the mix too.

-- 
David Kerry