Re: Help request: how to tune performance?

On Tue, Sep 16, 2008 at 11:10:01AM -0600, Scott Marlowe wrote:
> On Tue, Sep 16, 2008 at 2:31 AM, Mauri Sahlberg
> <Mauri(dot)Sahlberg(at)claymountain(dot)com> wrote:
> > Hi,
> >
> > We just upgraded Best Practical's RT from 3.6 to 3.81 and gave the
> > database completely own machine. And the users still complain that it is
> > dog slow.
>
> Moved up from below:
>
> > Version : 8.1.11 Vendor: CentOS
>
> So, you built it its own machine, but you didn't upgrade to at least 8.2?
>
> Last place I worked we ran rt 3.6.1 and got a noticeable performance
> boost from switching to 8.2 but the only thing that was ever really
> slow was viewing the rather large approval queue.
>
> > :-( I installed pg_top and it seems that at the beginning of
> > the ticket display RT-issues a query that eats everything the database
> > has. Query is as follows:
> >
> > SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_2 JOIN
> > Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
> > CachedGroupMembers CachedGroupMembers_3 ON
> > ( CachedGroupMembers_3.MemberId = Principals_1.id ) WHERE
> > (Principals_1.Disabled = '0') AND (ACL_2.PrincipalId =
> > CachedGroupMembers_3.GroupId) AND (Principals_1.id != '1') AND
> > (ACL_2.PrincipalType = 'Group') AND (Principals_1.PrincipalType =
> > 'User') AND (ACL_2.RightName = 'OwnTicket') AND ((ACL_2.ObjectType =
> > 'RT::Queue' AND ACL_2.ObjectId = 18) OR (ACL_2.ObjectType =
> > 'RT::System')) ORDER BY main.Name ASC
>

What is your version of DBIx-SearchBuilder? In particular, the
DISTINCT definition was changed from:

$$statementref = "SELECT DISTINCT main.* FROM $$statementref";

to:

$$statementref = "SELECT main.* FROM ( SELECT DISTINCT main.id FROM $$statementref ) distinctquery, $table main WHERE (main.id = distinctquery.id) ";

You can change the line in your version of SearchBuilder and restart
RT with a cache flush to get approximately the same benefit.

Ken