Re: [rt-users] Postgresql 4.4.1 slow queries?
Just want to update this, It seems that the issue at hand depends on if user is admin or has more dashboards available, hence most users now will only be users and have few dashboards available, the results of this is very fast performance (no lag or slow queries). Regards, Joel Från: rt-users [mailto:rt-users-boun...@lists.bestpractical.com] För Joel Bergmark Skickat: den 28 september 2016 16:58 Till: rt-users@lists.bestpractical.com Ämne: Re: [rt-users] Postgresql 4.4.1 slow queries? Hahaha, i used mysql syntax first "use rt4;" of course that messed it up :) Below is the output of EXPLAIN ANALYSE, but I need some help to interpret the relevant information in this :) QUERY PLAN - Unique (cost=311.85..311.93 rows=1 width=411) (actual time=371.738..371.809 rows=49 loops=1) -> Sort (cost=311.85..311.86 rows=1 width=411) (actual time=371.736..371.739 rows=94 loops=1) 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 Sort Method: quicksort Memory: 59kB -> Nested Loop (cost=1.82..311.84 rows=1 width=411) (actual time=0.321..370.336 rows=94 loops=1) -> Nested Loop (cost=1.55..309.32 rows=2 width=415) (actual time=0.076..22.220 rows=21011 loops=1) Join Filter: (principals_1.id = cachedgroupmembers_4.memberid) -> Nested Loop (cost=1.12..306.10 rows=1 width=419) (actual time=0.067..0.937 rows=57 loops=1) -> Nested Loop (cost=0.70..299.02 rows=4 width=415) (actual time=0.060..0.554 rows=57 loops=1) -> Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_2 (cost=0.42..80.08 rows=35 width=4) (actual time=0.052..0.122 rows=58 loops=1) Index Cond: ((groupid = 4) AND (disabled = 0)) Heap Fetches: 57 -> Index Scan using users_pkey on users main (cost=0.28..6.25 rows=1 width=411) (actual time=0.005..0.006 rows=1 loops=58) Index Cond: (id = cachedgroupmembers_2.memberid) -> Index Scan using principals_pkey on principals principals_1 (cost=0.42..1.76 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=57) Index Cond: (id = main.id) Filter: ((id <> 1) AND (disabled = 0) AND ((principaltype)::text = 'User'::text)) -> Index Scan using cachedgroupmembers3 on cachedgroupmembers cachedgroupmembers_4 (cost=0.42..3.15 rows=6 width=8) (actual time=0.006..0.291 rows=369 loops=57) Index Cond: (memberid = main.id) Filter: (disabled = 0) Rows Removed by Filter: 0 -> Index Only Scan using acl1 on acl acl_3 (cost=0.28..1.25 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=21011) Index Cond: ((rightname = 'OwnTicket'::text) AND (principaltype = 'Group'::text) AND (principalid = cachedgroupmembers_4.groupid)) Filter: objecttype)::text = 'RT::Queue'::text) AND (objectid = 56)) OR (((objecttype)::text = 'RT::System'::text) AND (objectid = 1))) Rows Removed by Filter: 0 Heap Fetches: 1615 Total runtime: 371.982 ms (27 rows) Regards, Joel Från: rt-users [mailto:rt-users-boun...@lists.bestpractical.com] För Joel Bergmark Skickat: den 27 september 2016 09:53 Till: rt-users@lists.bestpractical.com<mailto:rt-users@lists.bestpractical.com> Ämne: [rt-users] Postgresql 4.4.1 slow queries? 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
Re: [rt-users] Postgresql 4.4.1 slow queries?
Le 28/09/2016 à 11:12, Joel Bergmark a écrit : > 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... > seems you are not connected to the right database as the table Users is not found ;) - RT 4.4 and RTIR training sessions, and a new workshop day! https://bestpractical.com/training * Boston - October 24-26 * Los Angeles - Q1 2017
Re: [rt-users] Postgresql 4.4.1 slow queries?
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.bergm...@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-boun...@lists.bestpractical.com] För > Nilesh > Skickat: den 27 september 2016 17:36 > Till: rt-users@lists.bestpractical.com > Ämne: Re: [rt-users] Postgresql 4.4.1 slow queries? > > On Tue, 2016-09-27 at 07:52 +, 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
Re: [rt-users] Postgresql 4.4.1 slow queries?
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-boun...@lists.bestpractical.com] För Nilesh Skickat: den 27 september 2016 17:36 Till: rt-users@lists.bestpractical.com Ämne: Re: [rt-users] Postgresql 4.4.1 slow queries? On Tue, 2016-09-27 at 07:52 +, 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 - RT 4.4 and RTIR training sessions, and a new workshop day! https://bestpractical.com/training * Boston - October 24-26 * Los Angeles - Q1 2017
Re: [rt-users] Postgresql 4.4.1 slow queries?
On Tue, 2016-09-27 at 07:52 +, 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
