Re: Help request: how to tune performance? - Mailing list pgsql-admin

From Kenneth Marshall
Subject Re: Help request: how to tune performance?
Date
Msg-id 20080916185800.GQ22468@it.is.rice.edu
Whole thread Raw
In response to Re: Help request: how to tune performance?  ("Scott Marlowe" <scott.marlowe@gmail.com>)
List pgsql-admin
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.Sahlberg@claymountain.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

pgsql-admin by date:

Previous
From: "Scott Marlowe"
Date:
Subject: Re: Setting Effective Cache Size
Next
From:
Date:
Subject: Postgres && Swap