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:[email protected]] För Joel
Bergmark
Skickat: den 27 september 2016 09:53
Till: [email protected]
Ä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 :-)
Regards, Joel
---------
RT 4.4 and RTIR training sessions, and a new workshop day!
https://bestpractical.com/training
* Boston - October 24-26
* Los Angeles - Q1 2017
