Subject: Help request: how to tune performance?
From: Mauri Sahlberg <Mauri.Sahlberg@claymountain.com>
Reply-To: Mauri.Sahlberg@claymountain.com
To: pgsql-admin@postgresql.org
Content-Type: text/plain
Organization: Claymountain Solutions Oy
Date: Tue, 16 Sep 2008 11:31:47 +0300
Message-Id: <1221553908.3410.11.camel@fault.in.tuolla.net>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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. :-( 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

and explain tells me that:
        ->  Nested Loop  (cost=16.17..9953.51 rows=1 width=4318)
              Join Filter: ("outer".principalid = "inner".groupid)
              ->  Index Scan using acl1 on acl acl_2  (cost=0.00..49.38
rows=1 width=4)
                    Index Cond: ((principaltype)::text = 'Group'::text)
                    Filter: ((((rightname)::text = 'OwnTicket'::text) OR
((rightname)::text = 'SuperUser'::text)) AND (((objecttype)::text =
'RT::Queue'::text) OR ((objecttype)::text = 'RT::System'::text)))
              ->  Nested Loop  (cost=16.17..9903.98 rows=12 width=4322)
                    ->  Nested Loop  (cost=0.00..6343.98 rows=1
width=4322)
                          ->  Seq Scan on principals principals_1
(cost=0.00..6308.00 rows=6 width=4)
                                Filter: ((disabled = 0) AND (id <> 1)
AND ((principaltype)::text = 'User'::text))
                          ->  Index Scan using users_pkey on users main
(cost=0.00..5.98 rows=1 width=4318)
                                Index Cond: ("outer".id = main.id)
                    ->  Bitmap Heap Scan on cachedgroupmembers
cachedgroupmembers_3  (cost=16.17..3527.23 rows=2621 width=8)
                          Recheck Cond: (cachedgroupmembers_3.memberid =
"outer".id)
                          ->  Bitmap Index Scan on cachedgroupmembers2
(cost=0.00..16.17 rows=2621 width=0)
                                Index Cond:
(cachedgroupmembers_3.memberid = "outer".id)

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!

Database version:
Name        : postgresql-server            Relocations: (not
relocatable)
Version     : 8.1.11                            Vendor: CentOS
Release     : 1.el5_1.1                     Build Date: Sat 12 Jan 2008
04:45:09 PM EET

pg_top:

last pid:  7201;  load avg:  0.62,  0.90,  0.62;       up 0+19:17:00
11:28:10
13 processes: 1 running, 12 sleeping
CPU states:  8.2% user,  0.0% nice, 42.4% system, 49.5% idle,  0.0%
iowait
Memory: 1083M used, 2722M free, 234M buffers, 759M cached
Swap: 1024M free

  PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPU    CPU COMMAND
 6015 postgres  17    0   22M   12M run     0:32 13.28% 99.82% postgres:
rt rt 
 6018 postgres  15    0   22M   12M sleep   3:25  0.01%  0.00% postgres:
rt rt 
 6035 postgres  15    0   22M   12M sleep   2:11  1.31%  0.00% postgres:
rt rt 
 6037 postgres  15    0   22M   12M sleep   1:33  0.01%  0.00% postgres:
rt rt