<html>
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{font-family:Arial;
color:windowtext;}
@page Section1
{size:595.3pt 841.9pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-AU link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hi Everyone,</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>We have been making use of RT for almost a year now, however
the performance began to quickly degrade (tickets would take 30+ seconds to
open at times) over time.</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>We're using RT v3.6.3</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>After we checked both this list and other resources for a
solution, we were unable to find a solid fix which appeared to correct the
issue.</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>We have managed to change a few core queries to our
installation which appears to have made a massive difference (tickets load in
less than one second most of the time).</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>We thought others would benefit from these changes and so I
am passing on the findings we made to fix up our RT install.</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial;font-weight:bold'>Force a JOIN order of important tables</span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>From Nathan at mammoth:</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>--</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>This is lib/RT/SearchBuilder_Local.pm - its a new file which
is automatically used by RT's extension system. We're using 3.6.3</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>It works by changing the SQL to do a straight join of tables
in a specific order. This appears to improve things because MySQL appears
unable to choose the optimal join order on its own.</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Regards</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Nathan</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>--</span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial;font-weight:bold'> </span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>This changed file is attached.</span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial;font-weight:bold'> </span></font></b></p>
<p class=MsoNormal><b><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial;font-weight:bold'>Use another locking scheme</span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>By default, it appears the user settings locking scheme (for
read/write locks) by default is done via MySQL (using the GET_LOCK() method). When
a user browses RT.</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>While this sounds fine, unfortunately its because there is
no ‘read/shared lock’ concept using GET_LOCK(), and so every lock
the user makes (eg, locking their preferences for READ during a page load) is
promoted to an exclusive write lock. This slows performance when loading
multiple pages at once as every page is competing for this exclusive lock,
instead of using a shared read lock.</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Regards,</span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Andrew</span></font></p>
</div>
</body>
</html>