Thanks Torsten. We'll have a look at those settings as well. Appreciate the help!
Justin ------------------------------------------------- Justin Hayes OpenBet Support Manager justin.ha...@openbet.com On 7 Sep 2010, at 12:45, Torsten Brumm wrote: > Hi Justin, > just created inside a RT Test VM (slow one with 500mb ram) a single ticket > with around 60 replies and some comments. Tested the speed with different > users > > 1. root user to open this ticket: around 26 sec -> 870 single sql queries in > around 4 sec! (Queries: http://pastebin.com/7Yekfx2Y) > 2. user with full access (take, own, modify etc): around same time and > queries like root (Queries: http://pastebin.com/U0HnPcJL) > 3. user with less rights (no take, no own, only showticket, seequeue): time > around 15 sec and 600 sql queries in around 2 sec! (Queries: > http://pastebin.com/fXDHu6im) > > After this the apache starts to render the page from the results and push > them to the browser. The page is for my few comments/replies already 206KB > without any apache optimizations > > After adding: > > SetOutputFilter DEFLATE > SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$ no-gzip dont-vary > SetEnvIfNoCase Request_URI \.pdf$ no-gzip dont-vary > ExpiresActive On > ExpiresByType text/css "A604800" > > ExpiresByType image/x-icon "A31536000" > ExpiresByType image/gif "A604800" > ExpiresByType image/jpg "A604800" > ExpiresByType image/jpeg "A604800" > > ExpiresByType image/png "A604800" > ExpiresByType application/x-javascript A3600 > Header set Cache-Control "must-revalidate" > to the rt vhost, the page load time goes down from 26 sec to 8 sec and from > 206 kb to 10kb > > you should try. > > Torsten > > 2010/9/7 Justin Hayes <justin.ha...@openbet.com> > Well we've captured the time for all the queries run for our long ticket > (which takes ~20secs to generate). > > Total query time is 0.871493s > > So it's not the DB. > > Justin > > ------------------------------------------------- > Justin Hayes > OpenBet Support Manager > justin.ha...@openbet.com > > On 7 Sep 2010, at 11:13, Torsten Brumm wrote: > >> 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 >>>>>> >>>>>> >>>>>> 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 > > > > > -- > 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!
