[rt-users] Severe performance problems suddenly in RT 3.8.2

[Dave Holland]

On Tue, Feb 17, 2009 at 12:59:17PM -0500, Jesse Vincent wrote:
> And just to be sure, you ran the commands in UPGRADING.mysql?

I believe so (Tim did it).

> > # Query_time: 240  Lock_time: 0  Rows_sent: 4  Rows_examined: 27
> > SELECT DISTINCT main.Id AS id, main.Filename AS filename,
> > main.ContentType AS contenttype, main.Headers AS headers, main.Subject
> > AS subject, main.Parent AS parent, main.ContentEncoding AS
> > contentencoding, main.ContentType AS contenttype, main.TransactionId AS
> > transactionid, main.Created AS created FROM Attachments main JOIN
> > Transactions Transactions_1  ON ( Transactions_1.id = main.TransactionId
> > ) JOIN Tickets Tickets_2 ON ( Tickets_2.id = Transactions_1.ObjectId )
> > WHERE (Tickets_2.EffectiveId = '97814') AND (Transactions_1.ObjectType =
> > 'RT::Ticket')  ORDER BY main.id ASC;
> 
> 
> Can you give me an 'EXPLAIN' on that query?

Sure:

+----+-------------+----------------+------+-----------------------+---------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table          | type | possible_keys         | key           | key_len | ref                     | rows | Extra                                        |
+----+-------------+----------------+------+-----------------------+---------------+---------+-------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | Tickets_2      | ref  | PRIMARY,Tickets6      | Tickets6      | 4       | const                   |    1 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | Transactions_1 | ref  | PRIMARY,Transactions1 | Transactions1 | 70      | const,rtdb.Tickets_2.id |    1 | Using where; Using index                     | 
|  1 | SIMPLE      | main           | ref  | Attachments2          | Attachments2  | 4       | rtdb.Transactions_1.id  |    1 |                                              | 
+----+-------------+----------------+------+-----------------------+---------------+---------+-------------------------+------+----------------------------------------------+

> Can you easily optimize your tables? Perhaps first, it's worth running
> mysqltuner.pl (http://mysqltuner.pl) and posting the output.

 >>  MySQLTuner 1.0.0 - Major Hayden <major at mhtx.net>
 >>  Bug reports, feature requests, and downloads at
 >>  http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.32-Debian_7etch8-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 13m 11s (106K q [24.317 qps], 456 conn, TX: 228M, RX: 41M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 1.3G global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.6G (80% of installed RAM)
[OK] Slow queries: 0% (72/106K)
[OK] Highest usage of available connections: 56% (56/100)
[OK] Key buffer size / total MyISAM indexes: 256.0M/209.0M
[!!] Key buffer hit rate: 87.9% (13K cached / 1K reads)
[OK] Query cache efficiency: 53.8% (53K cached / 98K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
[!!] Temporary tables created on disk: 26% (1K on disk / 7K total)
[OK] Thread cache hit rate: 87% (57 created / 456 connections)
[OK] Table cache hit rate: 42% (89 open / 207 opened)
[OK] Open file limit used: 4% (43/1K)
[OK] Table locks acquired immediately: 99% (95K immediate / 95K locks)
[!!] InnoDB data size / buffer pool: 5.0G/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    tmp_table_size (> 64M)
    max_heap_table_size (> 16M)
    innodb_buffer_pool_size (>= 4G)

It's a 2GB machine so we can't up the innodb_buffer_pool_size (much)
more. I think the tmp_table_size/max_heap_table_size is the most recent
tweak we've tried.

thanks again,
Dave
-- 
** Dave Holland ** Systems Support -- Infrastructure Management **
** 01223 496923 ** The Sanger Institute, Hinxton, Cambridge, UK **
"Flattery is flattery, but chocolate gets results."


-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.