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
RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year -- Learn how to get the most out of RT!
