Hi Roy, We've logged the SQL statements on the test server - virtually every query is .000s of a second. Yet the ticket we're using on there still takes 12s to render.
So the queries seem ok. However I know that having a huge ticket in a small DB is much faster (say 2s). So it *seems* to be DB or server related, I just don't know how... Thanks, Justin ------------------------------------------------- Justin Hayes OpenBet Support Manager [email protected] On 1 Jul 2010, at 12:28, Raed El-Hames wrote: > Hi Justin; > > Our database is ~ 59G, the attachments table is ~ 40G. > In our setup , the web server is different hardware to the db servers , and > the db servers are 2 * dual core 2.6 AMD processors with 16G Ram, > > A stupid question, but did you restart apache after you commented out the > ShowTransaction lines ? > What version of RT by the way? > Do you use UseSQLForACLChecks?? I found it slows things a bit as it tries to > build long joins with cached group members and groups etc. > > In terms of debug; if you have not done this yet enable DBIx-SearchBuilder > StatementLog > Set($StatementLog,’debug’); in your etc/RT_SiteConfig. > Then try to load the ticket, then from your rt.log grab some of the sql > statements generated and try executing them on the sql server via sql > console, and see if any are slow , for any slow queries try an explain and > see what indexes are used. > > Something else I also try is to put debug lines with timestamps within the > html page itself and see which component taking the longest time, then do the > same within the slowest component breaking it into various parts to determine > which bit of code is taking the longest time etc etc. > > Feel free to mail me again if you need further help. > > Regards; > Roy > > > > > From: Justin Hayes [mailto:[email protected]] > Sent: 01 July 2010 12:08 > To: Raed El-Hames > Subject: Re: [rt-users] Slow Ticket History 3.8.8 > > Hi Raed, > > How big is your DB? We've got about 10gb in Attachments. If we put a really > long ticket in an empty DB performance is excellent. > > So something is wrong with the server/DB, we just don't know what. > > Cheers, > > Justin > > ------------------------------------------------- > Justin Hayes > OpenBet Support Manager > [email protected] > > On 1 Jul 2010, at 11:51, Raed El-Hames wrote: > > > Justin, > > Do you use Transaction custom fields, if you do n’t ; try and comment out > lines 70,71,72 from html/Ticket/Elements/ShowTransaction > % if ( $Transaction->CustomFieldValues->Count ) { > <& /Elements/ShowCustomFields, Object => $Transaction &> > % } > See if that improves things for you. > Some of our monitoring tickets can have up to 500 updates, such tickets use > to take up to 20s to load, once I commented out the above lines, load time is > now down to less than 5 seconds. > > Regards; > Roy > > > From: [email protected] > [mailto:[email protected]] On Behalf Of Justin Hayes > Sent: 01 July 2010 11:39 > To: Kenneth Crocker > Cc: [email protected] > Subject: Re: [rt-users] Slow Ticket History 3.8.8 > > We do Kenneth, but most tickets don't have many file attachments, so I assume > that's not an issue? > > Cheers, > > Justin > > ------------------------------------------------- > Justin Hayes > OpenBet Support Manager > [email protected] > > On 29 Jun 2010, at 17:54, Kenneth Crocker wrote: > > > > Justin, > > I didn't see this mentioned and may have missed it, but are you displaying > attachements inline? That might cut back on the I/O for History. Just a > thought. > > Kenn > LBNL > > On Tue, Jun 29, 2010 at 8:04 AM, Justin Hayes <[email protected]> > wrote: > As a test we've just created a long ticket in an empty RT DB and it's very > fast. So does look to be DB related - contrary to our earlier investigations. > > I guess it must still access the DB resultset during the ticket rendering > (which isn't how we thought it would work). > > Time to tune the hell out of mysql then....... > > Justin > > ------------------------------------------------- > Justin Hayes > OpenBet Support Manager > [email protected] > > On 29 Jun 2010, at 15:53, Justin Hayes wrote: > > > Seem to be quite a few things to look at Jason. Need to figure out what > > they all mean first. > > > > Justin > > > > -------- General Statistics > > -------------------------------------------------- > > [--] Skipped version check for MySQLTuner script > > [OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log > > [OK] Operating on 64-bit architecture > > > > -------- Storage Engine Statistics > > ------------------------------------------- > > [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster > > [--] Data in MyISAM tables: 611M (Tables: 8) > > [--] Data in InnoDB tables: 10G (Tables: 20) > > [!!] Total fragmented tables: 21 > > > > -------- Performance Metrics > > ------------------------------------------------- > > [--] Up for: 19d 19h 32m 37s (110M q [64.266 qps], 222K conn, TX: 637B, RX: > > 39B) > > [--] Reads / Writes: 98% / 2% > > [--] Total buffers: 602.0M global + 134.8M per thread (150 max threads) > > [!!] Maximum possible memory usage: 20.3G (262% of installed RAM) > > [OK] Slow queries: 0% (229K/110M) > > [!!] Highest connection usage: 100% (151/150) > > [OK] Key buffer size / total MyISAM indexes: 512.0M/6.7M > > [OK] Key buffer hit rate: 100.0% (84M cached / 7K reads) > > [OK] Query cache efficiency: 71.4% (76M cached / 107M selects) > > [!!] Query cache prunes per day: 661360 > > [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts) > > [!!] Joins performed without indexes: 112714 > > [!!] Temporary tables created on disk: 33% (968K on disk / 2M total) > > [OK] Thread cache hit rate: 99% (1K created / 222K connections) > > [OK] Table cache hit rate: 36% (318 open / 880 opened) > > [OK] Open file limit used: 14% (166/1K) > > [OK] Table locks acquired immediately: 99% (39M immediate / 39M locks) > > [!!] InnoDB data size / buffer pool: 10.1G/8.0M > > > > -------- Recommendations > > ----------------------------------------------------- > > General recommendations: > > Run OPTIMIZE TABLE to defragment tables for better performance > > Reduce your overall MySQL memory footprint for system stability > > Reduce or eliminate persistent connections to reduce connection usage > > Adjust your join queries to always utilize indexes > > When making adjustments, make tmp_table_size/max_heap_table_size equal > > Reduce your SELECT DISTINCT queries without LIMIT clauses > > Variables to adjust: > > *** MySQL's maximum memory usage is dangerously high *** > > *** Add RAM before increasing MySQL buffer variables *** > > max_connections (> 150) > > wait_timeout (< 28800) > > interactive_timeout (< 28800) > > query_cache_size (> 16M) > > join_buffer_size (> 2.0M, or always use indexes with joins) > > tmp_table_size (> 128M) > > max_heap_table_size (> 64M) > > innodb_buffer_pool_size (>= 10G) > > > > > > ------------------------------------------------- > > Justin Hayes > > OpenBet Support Manager > > [email protected] > > > > On 29 Jun 2010, at 15:22, Jason Doran wrote: > > > >> Hi, > >> If you are using mysqld have a look at "mysqltuner.pl" perl script (google) > >> This has fixed quickly many performance issues on both RT and other > >> web-based software we use. I run this every few weeks and apply suggested > >> changes and then simply restart mysqld when things are quite. > >> > >> Regards, > >> Jason Doran > >> Computer Centre > >> NUI, Maynooth > >> > >> On 29 Jun 2010, at 14:09, Justin Hayes wrote: > >> > >>> Hi everyone, > >>> > >>> I've raised this before, but we've had another look at it and still can't > >>> see how to improve things. > >>> > >>> We put a lot of comments/replies in our tickets. Often there can be > >>> 50-100 entries in a ticket, mostly plain text. Loading such a ticket can > >>> take 10-20secs. > >>> > >>> We don't have any slow queries - all the time seems to be in the code > >>> rendering the history of the ticket. > >>> We've had a go at stripping functions out of ShowHistory, ShowTransaction > >>> and ShowTransactionAttachmments but not had much success. > >>> > >>> FWIW our RT runs on quad 3ghz Xeons with 8gb of ram. > >>> > >>> I'd like to try and determine if we're just slow, or if this is just how > >>> long RT takes. Maybe perl is just slow. > >>> > >>> Can anyone shed any light on how long it takes them to render long > >>> tickets in their systems? If you look at the page source it gives you a > >>> value e.g. > >>> > >>> <span>Time to display: 24.996907</span> > >>> > >>> Can anyone share some numbers from theirs for longer tickets? It would be > >>> really appreciated. > >>> > >>> > >>> Thanks, > >>> > >>> Justin > >>> > >>> ------------------------------------------------- > >>> Justin Hayes > >>> OpenBet Support Manager > >>> [email protected] > >>> > >>> > >>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > >>> Buy a copy at http://rtbook.bestpractical.com > >> > > > > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > > Buy a copy at http://rtbook.bestpractical.com > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > Buy a copy at http://rtbook.bestpractical.com > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > Buy a copy at http://rtbook.bestpractical.com > >
Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
