[rt-users] Postgresql 4.4.1 slow queries?
Joel Bergmark
joel.bergmark at t3.se
Wed Oct 5 05:45:31 EDT 2016
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 at lists.bestpractical.com] För Joel Bergmark
Skickat: den 28 september 2016 16:58
Till: rt-users at 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-bounces at lists.bestpractical.com] För Joel Bergmark
Skickat: den 27 september 2016 09:53
Till: rt-users at lists.bestpractical.com<mailto:rt-users at 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 :-)
Regards, Joel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20161005/0776f956/attachment.htm>
More information about the rt-users
mailing list