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

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

Please post the output of explain analyze as an attachment.  explain
is only half the answer.

> Is there something I can do to improve performance with tuning something
> on postgresql.conf? Or adding/dropping indexes? What I read from that
> query plan is that the single most expensive thing is sequential scan on
> Principals. Principals already has indexes for both id and object.id!

Possibly.  explain analyze will help you identify where stats are
wrong.  sometimes just cranking the stats target on a few columns and
re-analyzing gets you a noticeable performance boost.  It's cheap and
easy.

When the estimated and actual number of rows are fairly close, then
look for the slowest thing and see if an index can help.

What have to already done to tune the install?  shared_buffers,
work_mem, random_page_cost, effective_cache_size.  Is your db bloating
during the day?

Why no try 8.3 on this?

Are you running on a single SATA hard drive?    How big's the database
directory?  I'm guessing from your top output that the db is about 500
meg or so.  it should all fit in memory.