[rt-users] Slow Ticket History 3.8.8

Thu Jul 1 09:42:33 EDT 2010

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.