PgSQL 7.3.2 queries in rt 3.0.2pre4, using DBIx-SearchBuilder-0.81_04

RT Developers
1

Hi people,

I’m seeing some unusually high query times using the setup mentioned above.
The system is a FreeBSD 4.8 PIII-550 with 256 MB RAM and running PgSQL 7.3.2,
and Apache 1.3.27 with mod_fastcgi

After importing our production rt2 data into the test rt3 (767 tickets),
such simple queries as viewing open tickets or replying to an open ticket
will send Apache into Internal Server Error as the CGI times out after
30 seconds. And yes, the DB has been vacuumed after import.

Example of a query generated by RT:

2003-05-02 14:14:44 LOG: query: SELECT DISTINCT main.* FROM
Users main JOIN Principals as Principals_3 ON main.id =
Principals_3.id JOIN CachedGroupMembers as CachedGroupMembers_6
ON Principals_3.Id = CachedGroupMembers_6.MemberId JOIN
Principals as Principals_1 ON main.id = Principals_1.id JOIN
CachedGroupMembers as CachedGroupMembers_7 ON Principals_1.id =
CachedGroupMembers_7.MemberId JOIN Principals as Principals_4 ON
CachedGroupMembers_6.GroupId = Principals_4.Id, Groups Groups_2,
ACL ACL_5 WHERE ((ACL_5.RightName = ‘SuperUser’)OR(ACL_5.RightName
= ‘OwnTicket’)) AND ((CachedGroupMembers_7.GroupId = ‘4’)) AND
((Principals_1.Disabled = ‘0’)) AND ((Principals_1.PrincipalType
= ‘User’)) AND ((Principals_3.PrincipalType = ‘User’)) AND
(ACL_5.ObjectType = ‘RT::System’ OR (ACL_5.ObjectType = ‘RT::Queue’ AND
ACL_5.ObjectId = ‘5’) ) AND ( (ACL_5.PrincipalId = Principals_4.Id AND
Principals_4.id = Groups_2.Id AND ACL_5.PrincipalType = ‘Group’ AND
(Groups_2.Domain = ‘SystemInternal’ OR Groups_2.Domain = ‘UserDefined’
OR Groups_2.Domain = ‘ACLEquivalence’)) OR ( ( (Groups_2.Domain =
‘RT::Queue-Role’ AND Groups_2.Instance = ‘5’) OR ( Groups_2.Domain =
‘RT::Ticket-Role’ AND Groups_2.Instance = ‘771’) ) AND Groups_2.Type
= ACL_5.PrincipalType AND Groups_2.Id = Principals_4.id AND
Principals_4.PrincipalType = ‘Group’) ) ORDER BY main.Name ASC

2003-05-02 14:15:12 LOG: duration: 27.121106 sec

This is to say the least a bit on the complex side. I know the idea
is to have as much database abstract queries as possible (since MySQL 3
for example doesn’t implement subselects, among other things, while
PgSQL, Oracle, etc… do), but this is obviously very MySQL-oriented.
This would be much more efficient (27 seconds!) using subselects…

Any ideas on how this can be improved ? I’ll have to stick to RT 2
then, or convince myself to use MySQL 4 (we use Postgres for everything
else…).

_ _ |_ | regnauld@catpipe.net catpipe Systems ApS |
((||_ | *BSD solutions, consulting, development |
| Tlf.: +45 7021 0050 http://www.catpipe.net/ |

2

Hi Phil, :wink:

This is to say the least a bit on the complex side. I know the idea
is to have as much database abstract queries as possible (since MySQL 3
for example doesn’t implement subselects, among other things, while
PgSQL, Oracle, etc… do), but this is obviously very MySQL-oriented.
This would be much more efficient (27 seconds!) using subselects…

Yes, it is very MySQL-centric. :frowning:

Any ideas on how this can be improved ? I’ll have to stick to RT 2
then, or convince myself to use MySQL 4 (we use Postgres for everything
else…).

Using MySQL is for me not an option.

There were people working on the performance issues on which I provided
some feedback, but it seems as if they dropped off the face of the
planet.

I admire all the work Jesse did with rt, but I don’t find the sources
and built-up of the sources to be overly clear. Rather, I am inclined
to call them obscure, which hampers my ability to work on it a lot.
Not to put it down, or anything, but I’ve tried and it is not easy to
get through it and create enhancements. :frowning:

Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai / a capoeirista
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
Only the wisest and the stupidest of men never change…

3

I admire all the work Jesse did with rt, but I don’t find the sources
and built-up of the sources to be overly clear. Rather, I am inclined
to call them obscure, which hampers my ability to work on it a lot.
Not to put it down, or anything, but I’ve tried and it is not easy to
get through it and create enhancements. :frowning:

There is a somewhat steep (but not high) learning curve to understand
the DBIx::SearchBuilder model of looking at data, but once you
understand that, most of the rest of RT falls into place.

If you have specific issues that you don’t understand, you can post
them to this list, and likely someone will explain them.

-R