[rt-users] Slow Ticket History 3.8.8

[Justin Hayes]

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

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.hayes at openbet.com
>> 
>> 
>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>> Buy a copy at http://rtbook.bestpractical.com
>