<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"\@MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.E-postmall17
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.E-postmall18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.E-postmall19
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="SV" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">Hahaha, i used mysql syntax first “use rt4;” of course that messed it up :)<br>
<br>
Below is the output of EXPLAIN ANALYSE, but I need some help to interpret the relevant information in this :)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> QUERY PLAN
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">Unique (cost=311.85..311.93 rows=1 width=411) (actual time=371.738..371.809 rows=49 loops=1)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> -> Sort (cost=311.85..311.86 rows=1 width=411) (actual time=371.736..371.739 rows=94 loops=1)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> 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<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Sort Method: quicksort Memory: 59kB<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> -> Nested Loop (cost=1.82..311.84 rows=1 width=411) (actual time=0.321..370.336 rows=94 loops=1)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> -> Nested Loop (cost=1.55..309.32 rows=2 width=415) (actual time=0.076..22.220 rows=21011 loops=1)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Join Filter: (principals_1.id = cachedgroupmembers_4.memberid)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> -> Nested Loop (cost=1.12..306.10 rows=1 width=419) (actual time=0.067..0.937 rows=57 loops=1)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> -> Nested Loop (cost=0.70..299.02 rows=4 width=415) (actual time=0.060..0.554 rows=57 loops=1)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> -> 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)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Index Cond: ((groupid = 4) AND (disabled = 0))<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Heap Fetches: 57<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> -> 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)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Index Cond: (id = cachedgroupmembers_2.memberid)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> -> 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)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Index Cond: (id = main.id)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Filter: ((id <> 1) AND (disabled = 0) AND ((principaltype)::text = 'User'::text))<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> -> 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)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Index Cond: (memberid = main.id)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Filter: (disabled = 0)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Rows Removed by Filter: 0<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> -> 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)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Index Cond: ((rightname = 'OwnTicket'::text) AND (principaltype = 'Group'::text) AND (principalid = cachedgroupmembers_4.groupid))<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Filter: ((((objecttype)::text = 'RT::Queue'::text) AND (objectid = 56)) OR (((objecttype)::text = 'RT::System'::text) AND (objectid = 1)))<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Rows Removed by Filter: 0<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> Heap Fetches: 1615<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">Total runtime: 371.982 ms<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">(27 rows)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">Regards, Joel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b>Från:</b> rt-users [mailto:rt-users-bounces@lists.bestpractical.com]
<b>För </b>Joel Bergmark<br>
<b>Skickat:</b> den 27 september 2016 09:53<br>
<b>Till:</b> rt-users@lists.bestpractical.com<br>
<b>Ämne:</b> [rt-users] Postgresql 4.4.1 slow queries?<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Hi,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Postgres have access to shared buffers = 2048 and effective_cache_size = 4096MB that was modified yesterday but queries still are same speed.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I have read plenty on postgres optimizing but not much seem to make a difference, and the
<a href="https://rt-wiki.bestpractical.com/wiki/PerformanceTuning">https://rt-wiki.bestpractical.com/wiki/PerformanceTuning</a> seems a bit out of date.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Example of problem:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-bottom:10.0pt;line-height:115%;text-autospace:none">
<span lang="EN-US">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"<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-bottom:10.0pt;line-height:115%;text-autospace:none">
<span lang="EN-US">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<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Appreciate any feedback :-)<br>
<br>
Regards, Joel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
</body>
</html>