<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">
Many thanks for that Roy. I applied the fix and it appears to have reduced the number of SELECTS:<div><br class="webkit-block-placeholder"></div><div>Before:</div><div>rt@ceres:~/etc$ tail -f /var/log/syslog | grep SELECT >> just.out</div><div><div>rt@ceres:~/etc$ cat just.out | grep 'SELECT' | wc -l</div><div>593</div><div><br class="webkit-block-placeholder"></div><div>After:</div><div>rt@ceres:~/etc$ rm just.out </div><div>rt@ceres:~/etc$ tail -f /var/log/syslog | grep SELECT >> just.out</div><div>rt@ceres:~/etc$ cat just.out | grep 'SELECT' | wc -l</div><div>364</div><br class="webkit-block-placeholder"></div><div>Doesn't seem to be displaying any faster though, but fewer queries must be a good thing.</div><div><br class="webkit-block-placeholder"></div><div>Interestingly I've noticed some tickets with only 8 comments on that are also taking over 10 seconds. Then I'll look at the same ticket a bit later and it will only take 1 second to display.</div><div><br class="webkit-block-placeholder"></div><div>More than likely there's something else going on besides the sheer number of queries, but RT does seem to run an awful lot of them at times.</div><div><br class="webkit-block-placeholder"></div><div>Justin</div><div><br><div> <span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; "><div><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>------------------------------------------------------</div><div>Justin Hayes</div><div>Support Manager</div><div><a href="mailto:justin.hayes@orbisuk.com">justin.hayes@orbisuk.com</a></div><div><br class="khtml-block-placeholder"></div></div><br></div></span> </div><br><div><div>On 3 Aug 2007, at 12:04, Roy El-Hames wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Justin;</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Not sure if it'll help you ;</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I had the same issue, where tickets with 100 or so updates/comments were taking over 1 minute to display ..</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">In our RT we do not use Transaction custom fields , so I commented out the following lines in ShowTransaction :</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">%# if ($Transaction->CustomFieldValues->Count) {</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">%#<span class="Apple-converted-space"> </span><& /Elements/ShowCustomFields, Object => $Transaction &></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">%#}</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">And now these same tickets open in less that 10 seconds ..</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I think there is a bug in pulling transaction cf's, which until we start using them I wont need to worry about them.</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Roy</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Justin Hayes wrote:</div> <blockquote type="cite"><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Using RT 3.6.3</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">on Mysql 5.0.38</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Some of our larger tickets are taking a long time to display (>10 seconds) so I thought I'd have a look to see where the time is spent.</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I've added Set($StatementLog, 'crit'); to my config file to see what queries are run and it seems like it runs a lot of queries just to display 1 ticket and it's history.</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I dumped all the queries run to display a ticket with 100 comments/replies into a file 'just.out' and it's running approx 500 SELECT statements:</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">rt@ceres:~/etc$ cat just.out | grep 'SELECT' | wc -l</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">491</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">For example this query is run:</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Aug<span class="Apple-converted-space"> </span>3 11:16:02 ceres RT: SQL(0.00s):</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">SELECT</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">DISTINCT main.Id AS id,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">main.Filename AS filename,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">main.ContentType AS contenttype,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">main.Headers AS headers,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">main.Subject AS subject,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">main.Parent AS parent,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">main.ContentEncoding AS contentencoding,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">main.ContentType AS contenttype,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">main.TransactionId AS transactionid,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">main.Created AS created</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">FROM</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Attachments main JOIN Transactions Transactions_1<span class="Apple-converted-space"> </span>ON ( Transactions_1.id = main.TransactionId ) JOIN Tickets Tickets_2<span class="Apple-converted-space"> </span>ON ( Tickets_2.id = Transactions_1.ObjectId )</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">WHERE (Tickets_2.EffectiveId = '22526') AND (Transactions_1.ObjectType = 'RT::Ticket')</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">ORDER BY main.id ASC ; (/opt/rt3/share/html/autohandler:320)</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Which returns 100 rows and then it runs one select for each Transaction:</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Aug<span class="Apple-converted-space"> </span>3 11:16:02 ceres RT: SQL(0.00s):</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">SELECT<span class="Apple-converted-space"> </span>* FROM Transactions WHERE id = ?;<span class="Apple-converted-space"> </span>[ bound values: '197857' ] (/opt/rt3/share/html/autohandler:320)</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">So you get 100 of those:</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">rt@ceres:~/etc$ cat just.out | grep 'SELECT<span class="Apple-converted-space"> </span>\* FROM Transactions' | wc -l</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">100</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Just wondering why it does all those 'Select *'s? Couldn't the fields you want from Transactions be retrieved in the first query and iterate over that in code?</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Also it seems to be doing a large number of CustomField related queries.</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">rt@ceres:~/etc$ cat just.out | grep 'CustomField' | wc -l</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">236</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I've got 5 CustomFields at the ticket level. Why would it run 236 queries relating to CustomFields for 1 ticket?</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Maybe this is all normal, with perfectly good explanation. Or maybe my install is broken or maybe some of our customisations are causing it.</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Any thoughts/opinions welcome...</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Justin</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">------------------------------------------------------</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Justin Hayes</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Support Manager</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><a href="mailto:justin.hayes@orbisuk.com">justin.hayes@orbisuk.com</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">------------------------------------------------------------------------</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">_______________________________________________</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><a href="http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users">http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Community help: <a href="http://wiki.bestpractical.com">http://wiki.bestpractical.com</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Commercial support: <a href="mailto:sales@bestpractical.com">sales@bestpractical.com</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at <a href="http://rtbook.bestpractical.com">http://rtbook.bestpractical.com</a></div> </blockquote><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">_______________________________________________</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><a href="http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users">http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Community help: <a href="http://wiki.bestpractical.com">http://wiki.bestpractical.com</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Commercial support: <a href="mailto:sales@bestpractical.com">sales@bestpractical.com</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at <a href="http://rtbook.bestpractical.com">http://rtbook.bestpractical.com</a></div> </blockquote></div><br></div></body></html>