[rt-users] Postgresql 4.4.1 slow queries?

[Joel Bergmark]

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