Thanks Alberto. Will look at those as well. Justin
------------------------------------------------- Justin Hayes OpenBet Support Manager justin.ha...@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.ha...@openbet.com <mailto:justin.ha...@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.ha...@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.ha...@openbet.com <mailto: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> >>> [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 >>> <mailto: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 <mailto: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 <mailto: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 <mailto: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 <mailto: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 >>> <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.ha...@openbet.com <mailto:justin.ha...@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: comunicac...@altran.es <mailto:comunicac...@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 Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com