From: Mauri Sahlberg <Mauri.Sahlberg@claymountain.com>
To: pgsql-admin@postgresql.org
Subject: Help request: how to tune performance?
Date: Tue, 16 Sep 2008 11:31:47 +0300
Message-ID: <1221553908.3410.11.camel@fault.in.tuolla.net> (raw)
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
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: Mauri.Sahlberg@claymountain.com
Subject: Re: Help request: how to tune performance?
In-Reply-To: <1221553908.3410.11.camel@fault.in.tuolla.net>
* 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