From: Kenneth Marshall <ktm@rice.edu>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Mauri.Sahlberg@claymountain.com
Cc: pgsql-admin@postgresql.org
Subject: Re: Help request: how to tune performance?
Date: Tue, 16 Sep 2008 13:58:00 -0500
Message-ID: <20080916185800.GQ22468@it.is.rice.edu> (raw)
In-Reply-To: <dcc563d10809161010j4a04a9f9ufefab82abf2dce08@mail.gmail.com>
References: <1221553908.3410.11.camel@fault.in.tuolla.net>
<dcc563d10809161010j4a04a9f9ufefab82abf2dce08@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ktm@rice.edu, scott.marlowe@gmail.com, Mauri.Sahlberg@claymountain.com
Subject: Re: Help request: how to tune performance?
In-Reply-To: <20080916185800.GQ22468@it.is.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox