[rt-users] Slow Ticket History 3.8.8

Thu Jul 1 06:42:14 EDT 2010

Thanks a lot for this response Kim.

r.e. the joins without indexes. How can we work out which indexes we need? I'd have thought this would cause slow queries but we're not seeing any...

We're trying to tune on our test server before making any changes to live, however the hardware etc is different so not sure it it's going to be that useful an excercise.

This is what we're not getting on test - we still get long ticket load times and we've done a lot of the recommended tuning:

[OK] Logged in using credentials passed on the command line

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.75-0ubuntu10.2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 131M (Tables: 15)
[--] Data in InnoDB tables: 10G (Tables: 81)
[!!] Total fragmented tables: 2

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3m 44s (9K q [41.304 qps], 73 conn, TX: 17M, RX: 2M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 11.0G global + 3.5M per thread (150 max threads)
[!!] Maximum possible memory usage: 11.5G (149% of installed RAM)
[OK] Slow queries: 0% (20/9K)
[OK] Highest usage of available connections: 9% (14/150)
[OK] Key buffer size / total MyISAM indexes: 3.0G/4.6M
[OK] Key buffer hit rate: 97.8% (453 cached / 10 reads)
[OK] Query cache efficiency: 89.0% (8K cached / 9K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 22 sorts)
[OK] Temporary tables created on disk: 14% (28 on disk / 195 total)
[OK] Thread cache hit rate: 80% (14 created / 73 connections)
[OK] Table cache hit rate: 95% (121 open / 127 opened)
[OK] Open file limit used: 7% (80/1K)
[OK] Table locks acquired immediately: 100% (773 immediate / 773 locks)
[!!] InnoDB data size / buffer pool: 10.7G/6.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_buffer_pool_size (>= 10G)

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

On 30 Jun 2010, at 08:55, Kim Pedersen wrote:

> 
> Hi Justin,
> 
> Our RT instance is much smaller than yours, but we had very similar problems.
> 
> Optimizing MySQL using the output from the tuner script made nearly all the speed issues go away.
> 
> The one remaining issue we had with slow queries was resolved after the upgrade to 3.8.8
> 
> 
> Your key buffer size looks like overkill - probably want to reduce that, and you have many joins done without indexes - that's a performance killer.
> 
> Your InnoDB memory allocation looks like it needs to be increasing.
> 
> Overall I'd say your database performance is skewed towards ISAM performance and not InnoDB (Which is what RT3 uses by default)
> 
> 
> Kim
> 
> 
> On 2010-06-29 10: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)
>> 
>>   
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com