Hi Andrew,<br><br>the first part of your mail was fine, its working like a charme, but this part:<br><br><p><b><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; font-weight: bold;">Use another locking scheme
</span></font></b></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; 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><font face="Arial" size="2"><span style="font-size: 10pt; 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>I dont understand, do you have an idea how to change this like you described it???<br><br>Thanks<br><br>Torsten<br><br><div><span class="gmail_quote">2007/6/26, Andrew Armstrong <
<a href="mailto:andrew@mammoth.com.au">andrew@mammoth.com.au</a>>:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div link="blue" vlink="purple" lang="EN-AU">
<div>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Hi Everyone,</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; 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><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">We're using RT v3.6.3</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; 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><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; 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><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; 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><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><b><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; font-weight: bold;">Force a JOIN order of important tables</span></font></b></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">From Nathan at mammoth:</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">--</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; 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><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; 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><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Regards</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Nathan</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">--</span></font></p>
<p><b><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; font-weight: bold;"> </span></font></b></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">This changed file is attached.</span></font></p>
<p><b><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; font-weight: bold;"> </span></font></b></p>
<p><b><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; font-weight: bold;">Use another locking scheme</span></font></b></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; 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><font face="Arial" size="2"><span style="font-size: 10pt; 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><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Regards,</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Andrew</span></font></p>
</div>
</div>
<br>_______________________________________________<br><a onclick="return top.js.OpenExtLink(window,event,this)" href="http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users" target="_blank">http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
</a><br><br>Community help: <a onclick="return top.js.OpenExtLink(window,event,this)" href="http://wiki.bestpractical.com" target="_blank">http://wiki.bestpractical.com</a><br>Commercial support: <a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:sales@bestpractical.com">
sales@bestpractical.com</a><br><br><br>Discover RT's hidden secrets with RT Essentials from O'Reilly Media.<br>Buy a copy at <a onclick="return top.js.OpenExtLink(window,event,this)" href="http://rtbook.bestpractical.com" target="_blank">
http://rtbook.bestpractical.com</a><br><br clear="all"></blockquote></div><br><br clear="all"><br>-- <br>MFG<br><br>Torsten Brumm<br><br><a href="http://www.torsten-brumm.de">http://www.torsten-brumm.de</a>