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.
Apache 2+mod_perl2 (Debian sarge)
Hardware: dual 3Ghz Xeon server
Both rt and database are on the same machine.
I have 2 issues:
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
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
postgres: [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
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
This becomes a serious issue when you throw database replication into
the mix too.