[rt-users] Postgresql 4.4.1 slow queries?
Nilesh
me at nileshgr.com
Wed Sep 28 09:20:55 EDT 2016
You made some typing mistake, you should get something like this:
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=33.54..33.62 rows=1 width=329)
-> Sort (cost=33.54..33.54 rows=1 width=329)
Sort Key: main.name, main.id, main.password, main.authtoken,
main.comments, main.signature, main.emailaddress, main.freeformcontactinfo,
main.organization, main.realname, main.nickname, main.lang, main.gecos,
main.homephone, main.workphone, main.mobilephone, main.pagerphone,
main.address1, main.address2, main.city, main.state, main.zip,
main.country, main.timezone, main.smimecertificate, main.creator,
main.created, main.lastupdatedby, main.lastupdated
-> Nested Loop (cost=1.12..33.53 rows=1 width=329)
Join Filter: (main.id = principals_1.id)
-> Nested Loop (cost=0.84..33.18 rows=1 width=337)
Join Filter: (main.id = cachedgroupmembers_2.memberid)
-> Nested Loop (cost=0.56..29.94 rows=9 width=333)
-> Nested Loop (cost=0.28..13.74 rows=51
width=4)
-> Seq Scan on acl acl_3
(cost=0.00..5.40 rows=1 width=4)
Filter: (((principaltype)::text =
'Group'::text) AND ((rightname)::text = 'OwnTicket'::text) AND
((((objecttype)::text = 'RT::Queue'::text) AND (objectid = 56)) OR
(((objecttype)::text = 'RT::System'::text) AND (objectid = 1))))
-> Index Only Scan using disgroumem on
cachedgroupmembers cachedgroupmembers_4 (cost=0.28..8.32 rows=2 width=8)
Index Cond: ((groupid =
acl_3.principalid) AND (disabled = 0))
-> Index Scan using users_pkey on users main
(cost=0.28..0.31 rows=1 width=329)
Index Cond: (id =
cachedgroupmembers_4.memberid)
-> Index Only Scan using disgroumem on
cachedgroupmembers cachedgroupmembers_2 (cost=0.28..0.35 rows=1 width=4)
Index Cond: ((groupid = 4) AND (memberid =
cachedgroupmembers_4.memberid) AND (disabled = 0))
-> Index Scan using principals_pkey on principals
principals_1 (cost=0.28..0.34 rows=1 width=4)
Index Cond: (id = cachedgroupmembers_4.memberid)
Filter: ((id <> 1) AND (disabled = 0) AND
((principaltype)::text = 'User'::text))
On Wed, Sep 28, 2016 at 2:42 PM, Joel Bergmark <joel.bergmark at t3.se> wrote:
> Hi!
>
> This is the output from explain:
>
> postgres=# EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL
> ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN
> CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId
> = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON (
> CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE ((ACL_3.ObjectType
> = 'RT::Queue' AND ACL_3.ObjectId = 56) OR (ACL_3.ObjectType =
> 'RT::System' AND ACL_3.ObjectId = 1)) AND (ACL_3.PrincipalId =
> CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND
> (ACL_3.RightName = 'OwnTicket') AND (CachedGroupMembers_2.Disabled = '0')
> AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled
> = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType =
> 'User') AND (Principals_1.id != '1') ORDER BY main.Name ASC;
> ERROR: relation "users" does not exist
> LINE 1: EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN AC...
>
> I have looked for more queries that takes time and its always this query
> that generates the slow behavior, but seemingly the query itself seems fine
> "users" exists in db, but then again obviously postgres has issues with
> something here.
>
> Any thoughts?
>
> Regards, Joel
>
> -----Ursprungligt meddelande-----
> Från: rt-users [mailto:rt-users-bounces at lists.bestpractical.com] För
> Nilesh
> Skickat: den 27 september 2016 17:36
> Till: rt-users at lists.bestpractical.com
> Ämne: Re: [rt-users] Postgresql 4.4.1 slow queries?
>
> On Tue, 2016-09-27 at 07:52 +0000, Joel Bergmark wrote:
> > Hi,
> >
> > After upgrading to version 4.4.1 i have noticed that the SQL-queries
> > takes significant longer time to produce a result, on average on my
> > system, running Ubuntu 14.04 LTS, Postgres 9.3, Apache with perlmod. 8
> > gigram and plenty of CPU in a vmware cluster on SSD:s (likely not a
> > hardware issue). We did not experience RT to have been this slow on
> 4.4.0.
> >
> > Each query takes about 350ms to give a result, occasionally up to
> > 4000ms have been seen, not a big problem but somethings that needs to be
> fixed.
> >
> > Postgres have access to shared buffers = 2048 and effective_cache_size
> > = 4096MB that was modified yesterday but queries still are same speed.
> >
> > I have read plenty on postgres optimizing but not much seem to make a
> > difference, and the
> > https://rt-wiki.bestpractical.com/wiki/PerformanceTuning
> > seems a bit out of date.
> >
> > Example of problem:
> >
> > 192.168.2.65 - - [27/Sep/2016:09:06:49 +0200] "GET
> > /Search/Build.html?NewQuery=1 HTTP/1.1" 200 12072 Mozilla/5.0 (Windows
> > NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko)
> > Chrome/53.0.2785.101 Safari/537.36"
> >
> > 09:06:52 CEST LOG: duration: 3362.432 ms execute dbdpg_p2242_1745:
> > SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN
> > Principals
> > Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers
> > CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId =
> > Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON (
> > CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE
> > ((ACL_3.ObjectType =
> > 'RT::Queue') OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId
> > = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND
> > (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR
> > ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled =
> > '0') AND (CachedGroupMembers_2.GroupId = '4') AND
> > (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled =
> > '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id !=
> > '1') ORDER BY main.Name ASC
> >
> > I’m looking for any suggestions about this, and I’m not an expert on
> > postgresql, but guess that this could have something to do with RT:s
> > “($UseSQLForACLChecks, 1);” or modperl via Apache or simply some index
> > in postgres?
> >
> > Appreciate any feedback :-)
> >
> > Regards, Joel
> >
> > ---------
> > RT 4.4 and RTIR training sessions, and a new workshop day!
> > https://bestpractic al.com/training
> > * Boston - October 24-26
> > * Los Angeles - Q1 2017
>
> What do you get if you run EXPLAIN over that query directly in psql CLI?
>
> --
> Nilesh
>
> ---------
> RT 4.4 and RTIR training sessions, and a new workshop day!
> https://bestpractical.com/training
> * Boston - October 24-26
> * Los Angeles - Q1 2017
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20160928/129c6f8f/attachment.htm>
More information about the rt-users
mailing list