Hi Justin, just found this threat, sounds interessting. What i read so far: You have 1 quad core system with 8GB RAM, running both WEB and DB, correct?
Think you should follow Raed's hints first to log the queries generated with RT In terms of debug; if you have not done this yet enable DBIx-SearchBuilder StatementLog Set($StatementLog,’debug’); in your etc/RT_SiteConfig. I'm sure you will find some funny queries. Normally the Query Log of default MySQL can only log queries taking longer than a second, but in your case i think, you will have several much faster queries but in summary they take longer - but you can't find in mysql-slow log. Some more question regarding your hardware and setup. 1. One Server / quad core (hyper threating) -> how many threats for Mysql/Postgresql? / 8 GB Ram 2. Hard Disk Setup? (logfiles and db storred on different HDD's? Any I/O Problems?) 3. RT Rights Setup, does the user performance is faster or slower than the performance with root user? Some more information? We're running also a larger RT Instance with dedicated hardware for DB and Webservers with no huge perferomance bottlenacks. Tob 2010/9/7 Justin Hayes <justin.ha...@openbet.com> > I *think* we're just CPU bound. Roy's webservers are 3.6ghz so quite a bit > faster than ours. We're going to try it on a faster server and that should > drop our times. Guess we just wanted to explore all avenues before throwing > hardware at the problem. > > Justin > > ------------------------------------------------- > Justin Hayes > OpenBet Support Manager > justin.ha...@openbet.com > > On 7 Sep 2010, at 10:30, Justin Hayes wrote: > > Tried Centos last night, and no difference at all. > > ------------------------------------------------- > Justin Hayes > OpenBet Support Manager > justin.ha...@openbet.com > > On 6 Sep 2010, at 20:49, Justin Hayes wrote: > > Hi Ruslan, > > Sorry looks like I shrunk the image too much. The thing I find odd is that > there are others with similar hardware who don't get the problem. It'll be > great if 3.10 fixes it for me, but I'd love to get to the bottom of it > first. I'm pretty much positive it's not a DB issue, as I've tried different > sizes of DB, tried postgres AND mysql etc. I don't think it's apache as I've > tried the built in webserver with RT and no change there either. > > Currently trying to install RT on Centos given that Roy (who has kindly > been helping me with details of his own setup) appears to have none of the > same problems on that OS. Perhaps perl is just slow on the 64bit ubuntu > we've currently got live. > > No idea if it's going to have any effect though :( > > Justin > > ------------------------------------------------- > Justin Hayes > OpenBet Support Manager > justin.ha...@openbet.com > > On 6 Sep 2010, at 18:37, Ruslan Zakirov wrote: > > Justin. > > First of all, I can not read from the chart, but anyway history rendering > has been worked on in a new code branch. Probably this code will be part of > RT 3.10. Code at the moment is unstable, but eventually it wil be faster > then the current version. > > On Mon, Sep 6, 2010 at 8:56 PM, Justin Hayes <justin.ha...@openbet.com>wrote: > >> So far we've tried installing RT on different hardware, both 32 and 64bit >> versions of linux. RT is still very slow for long tickets. All the time is >> taken up by the perl/apache process maxing out a core of CPU. >> >> We've even gone as far as trying to profile the code. We came up with this >> graph of where the time was going: >> >> <TIMING.png> >> We then tried to go further into those functions but can't find a single >> smoking gun call that is taking all the time. >> >> For example in a ticket that takes 22s to render approx 5 secs goes on >> these 2 lines: >> >> File: Ticket/Elements/ShowHistory line: 100-103 version 3.8.8 >> >> my @trans_attachments = grep { $_->TransactionId == $Transaction->Id } >> @attachments; >> >> grep { ($_->TransactionId == $Transaction->Id ) && >> ($trans_content->{$_->Id} = $_) } @attachment_content; >> >> Both are greps. Does this imply that perl itself is just slow? >> >> IF so why would our perl be slow compared to other people's? We've tried >> compiling it from source and that made no difference. >> >> ATM we're at a bit of a loss.... >> >> Justin >> >> ------------------------------------------------- >> Justin Hayes >> OpenBet Support Manager >> justin.ha...@openbet.com >> >> 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:* rt-users-boun...@lists.bestpractical.com [mailto: >> rt-users-boun...@lists.bestpractical.com] *On Behalf Of *Justin Hayes >> >> *Sent:* 01 July 2010 11:39 >> *To:* Kenneth Crocker >> *Cc:* rt-users@lists.bestpractical.com >> *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 >> justin.ha...@openbet.com >> >> 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 <justin.ha...@openbet.com> >> 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 >> justin.ha...@openbet.com >> 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 >> > justin.ha...@openbet.com >> > >> > 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 >> >>> justin.ha...@openbet.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<http://rtbook.bestpracticalcom/> >> >> >> 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 >> >> >> >> >> >> RT Training in Washington DC, USA on Oct 25 & 26 2010 >> Last one this year -- Learn how to get the most out of RT! >> > > > > -- > Best regards, Ruslan. > > > > RT Training in Washington DC, USA on Oct 25 & 26 2010 > Last one this year -- Learn how to get the most out of RT! > > > > RT Training in Washington DC, USA on Oct 25 & 26 2010 > Last one this year -- Learn how to get the most out of RT! > > > > > RT Training in Washington DC, USA on Oct 25 & 26 2010 > Last one this year -- Learn how to get the most out of RT! > -- MFG Torsten Brumm http://www.brumm.me http://www.elektrofeld.de
RT Training in Washington DC, USA on Oct 25 & 26 2010 Last one this year -- Learn how to get the most out of RT!