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

Regards, Joel

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 :slight_smile:

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

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@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 +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 :slight_smile:

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

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@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@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 +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 :slight_smile:

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

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 :wink:

Hahaha, i used mysql syntax first “use rt4;” of course that messed it up :slight_smile:

Below is the output of EXPLAIN ANALYSE, but I need some help to interpret the relevant information in this :slight_smile:

                              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-bounces@lists.bestpractical.com] För Joel Bergmark
Skickat: den 27 september 2016 09:53
Till: 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.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 :slight_smile:

Regards, Joel

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-bounces@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 :slight_smile:

Below is the output of EXPLAIN ANALYSE, but I need some help to interpret the relevant information in this :slight_smile:

                              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-bounces@lists.bestpractical.com] För Joel Bergmark
Skickat: den 27 september 2016 09:53
Till: rt-users@lists.bestpractical.commailto: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.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 :slight_smile:

Regards, Joel

Hi Joel

I have the exact same issue as you describe after my upgrade to 4.4.2. Have you solved it in some way or are you just relying on the fact most users now will only be users and have few dashboards?

Regards Ulf Renman

We are experiencing the same issue with the same query. It appears to populate the Owner dropdowns and takes significantly longer than almost any other query (except unowned tickets)

Running the script shrink-cgm-table reduced the length of the query from 5 seconds to 2.5 seconds, but it’s still way too long compared to all other components of the Ticket page.

WE are using Mysql in RT 4.4.2

Perfect jabels! I did not know about that script. It took my responsetime down from ~8sec to ~2.5sec. So it is still to slow, but now it is at least bearable.

For users without SuperUser-rights the Ticket-page loads instantly just as before.

Thanks!