[rt-users] Slow Ticket History 3.8.8

Thu Jul 1 09:55:09 EDT 2010

Thanks Alberto. Will look at those as well.

Justin

-------------------------------------------------
Justin Hayes
OpenBet Support Manager
justin.hayes at openbet.com

On 1 Jul 2010, at 14:42, Alberto Villanueva wrote:

> Have you read next links? Maybe you could be good for your server.
> 
> http://wiki.bestpractical.com/view/PerformanceTuning
> http://wiki.bestpractical.com/view/CleanupSessions
> 
> 
> Good luck!
> 
> 
>> 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
>> justin.hayes at openbet.com <mailto:justin.hayes at openbet.com>
>> 
>> 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:justin.hayes at openbet.com]
>>> *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
>>> justin.hayes at openbet.com <mailto:justin.hayes at 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-bounces at lists.bestpractical.com
>>> <mailto:rt-users-bounces at lists.bestpractical.com>
>>> [mailto:rt-users-bounces at lists.bestpractical.com] *On Behalf Of
>>> *Justin Hayes
>>> *Sent:* 01 July 2010 11:39
>>> *To:* Kenneth Crocker
>>> *Cc:* rt-users at lists.bestpractical.com
>>> <mailto:rt-users at 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.hayes at openbet.com <mailto:justin.hayes at 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.hayes at openbet.com <mailto:justin.hayes at 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.hayes at openbet.com <mailto:justin.hayes at 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.hayes at openbet.com <mailto:justin.hayes at openbet.com>
>>> >
>>> > On 29 Jun 2010, at 15:22, Jason Doran wrote:
>>> >
>>> >> Hi,
>>> >> If you are using mysqld have a look at "mysqltuner.pl
>>> <http://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.hayes at openbet.com <mailto:justin.hayes at openbet.com>
>>> >>>
>>> >>>
>>> >>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>>> >>> Buy a copy at http://rtbook.bestpractical.com
>>> <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.bestpractical.com/>
>>> 
>>> 
>>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>>> Buy a copy at http://rtbook.bestpractical.com
>>> <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
> 
> 
> -- 
> Alberto Villanueva
> Industria
> ______________________________________
> 
> ALTRAN
> 
> C/Campezo, 1, Edificio 1, Planta 4
> 28022 Madrid, Spain
> Tel : + 34 91 550 41 00
> Fax: + 34 91 415 61 53
> 
> www.altran.es
> 
> Antes de imprimir este mensaje, asegúrate de que es necesario. Proteger
> el medio ambiente está también en tu mano.
> 
> En cumplimiento de la Ley Orgánica 15/1999, con fecha 13 de diciembre,
> de Protección de Datos de Carácter Personal, y la Ley 34/2002, con fecha
> 11 de julio, de Servicios de la Sociedad de la Información y de comercio
> electrónico, le comunicamos que su dirección de correo electrónico forma
> parte de un fichero del que es responsable Altran España, y que
> garantiza la confidencialidad y seguridad de sus datos. Tiene usted
> derecho al acceso, rectificación y cancelación de sus datos en los
> términos establecidos en la Ley Orgánica 15/1999 de Protección de Datos
> de Carácter Personal y demás normativa concordante, dirigiéndose a
> nuestra dirección anteriormente señalada o por medio de correo
> electrónico: comunicacion at altran.es <mailto:comunicacion at altran.es>.
> 
> AVISO LEGAL: Este mensaje, junto con cualquier fichero adjunto, está
> dirigido a su destinatario y es confidencial. Cualquier distribución,
> uso o reproducción sin consentimiento del remitente está estrictamente
> prohibido. Si ha recibido este mensaje por error, por favor proceda a
> ponerlo en conocimiento del remitente por e-mail y a borrarlo de su
> sistema sin realizar copias.
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com