I'm running RT 4.2.3 with mysql 5.1.71 on RHEL 6.5. This install has been solid for months, then yesterday we start having serious performance issues. We've spent the last two days looking at everything we can think of and still can't crack the problem. I'd appreciate any suggestions for things to investigate.
Searches using text in the quick seach box take a very long time and multiple searches bring the whole of RT to a crawl. Subject searches in the searchbuilder show the same behaviour. Searching by ticket number, owner or fulltext (using sphinx) are fast. When things slow down I see very high mysql cpu usage, no io wait. Show processlist shows the thread in "Copying to tmp table" state. Here's a problematic query. It was the only thing running at this time, it's already taken 24 seconds. During bad times earlier today I saw queries still running after half an hour. | 17 | rt_user | localhost | rt3 | Query | 24 | Copying to tmp table | SELECT DISTINCT main.* FROM Tickets main JOIN Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.MemberId = '38844' ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE ( ( ( main.Queue = '3' OR main.Queue = '4' OR main.Queue = '5' OR main.Queue = '6' OR main.Queue = '7' OR main.Queue = '8' OR main.Queue = '9' OR main.Queue = '10' OR main.Queue = '10' OR main.Queue = '11' OR main.Queue = '11' OR main.Queue = '12' OR main.Queue = '12' OR main.Queue = '13' OR main.Queue = '13' OR main.Queue = '14' OR main.Queue = '14' OR main.Queue = '15' OR main.Queue = '16' OR main.Queue = '18' OR main.Queue = '18' OR main.Queue = '19' OR main.Queue = '20' OR main.Queue = '21' OR main.Queue = '21' OR main.Queue = '21' OR main.Queue = '22' OR main. Queue = '23' OR main.Queue = '24' OR main.Queue = '26' OR main.Queue = '29' OR main.Queue = '30' OR main.Queue = '31' OR main.Queue = '32' OR main.Queue = '36' OR main.Queue = '38' OR main.Queue = '44' OR main.Queue = '51' OR main.Queue = '54' OR main.Queue = '55' OR main.Queue = '56' OR main.Queue = '57' OR main.Queue = '58' OR main.Queue = '59' OR main.Queue = '60' OR main.Queue = '61' OR main.Queue = '62' OR main.Queue = '63' OR main.Queue = '64' OR main.Queue = '65' OR main.Queue = '66' OR main.Queue = '67' OR main.Queue = '68' OR main.Queue = '70' OR main.Queue = '72' OR main.Queue = '73' OR main.Queue = '75' OR main.Queue = '77' OR main.Queue = '81' OR main.Queue = '86' OR main.Queue = '87' OR main.Queue = '88' OR main.Queue = '89' OR main.Queue = '90' OR main.Queue = '91' OR main.Queue = '92' OR main.Queue = '93' OR main.Queue = '93' OR main.Queue = '94' OR main.Queue = '95' OR main.Queue = '96' OR main.Queue = '105' OR main.Queue = '106' OR main.Queue = '110' OR main .Queue = '115' OR main.Queue = '120' OR main.Queue = '124' OR main.Queue = '125' OR main.Queue = '128' OR main.Queue = '129' OR main.Queue = '138' OR main.Queue = '139' OR main.Queue = '141' OR main.Queue = '142' OR main.Queue = '148' OR main.Queue = '150' OR main.Queue = '153' OR main.Queue = '154' ) OR ( CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'Requestor' AND ( main.Queue = '17' OR main.Queue = '46' ) ) OR ( CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'Cc' AND main.Queue = '17' ) ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Subject LIKE '%blah%') ORDER BY main.id DESC LIMIT 50 | Explain plan for above query: +----+-------------+----------------------+--------+----------------------------------------------+------------+---------+-----------------------------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------+--------+----------------------------------------------+------------+---------+-----------------------------+-------+--------------------------+ | 1 | SIMPLE | Groups_1 | ref | groups1,groups2,groups3 | groups2 | 67 | const | 40094 | Using where; Using index | | 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,CachedGroupMembers3,SHREDDER_CGM1 | DisGrouMem | 12 | rt3.Groups_1.id,const,const | 1 | Using index | | 1 | SIMPLE | main | eq_ref | PRIMARY,Tickets1 | PRIMARY | 4 | rt3.Groups_1.Instance | 1 | Using where | +----+-------------+----------------------+--------+----------------------------------------------+------------+---------+-----------------------------+-------+--------------------------+ my.cnf: ======================================================================== [client] socket=/srv/mysql/mysql.sock [mysqld] large-pages datadir=/srv/mysql socket=/srv/mysql/mysql.sock port = 3306 # Maximum allowed size for a single HEAP (in memory) table. This option # is a protection against the accidential creation of a very large HEAP # table which could otherwise use up all memory resources. max_heap_table_size = 128M max_allowed_packet = 32M # Set to size of largest BLOB # sort_buffer_size = 8M # Speeds order by & group by sort_buffer_size = 1024M # Speeds order by & group by join_buffer_size = 16M thread_cache = 32 thread_concurrency = 32 query_cache_size = 64M query_cache_limit = 4M query_cache_type = 1 thread_stack = 192K tmp_table_size = 512M table_cache = 1024 # Max # of opened tables for all threads (see Opened_tables in status) max_connections = 512 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 skip-external-locking # key_buffer = 256M # MyISAM only? #key_buffer = 2048M # MyISAM only? key_buffer = 128M read_buffer_size = 1024M #read_buffer_size = 128M # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /srv/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /srv/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16192M #innodb_buffer_pool_size = 8096M #innodb_buffer_pool_instances = 2 #innodb_buffer_pool_size = 2048M innodb_additional_mem_pool_size = 40M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 32 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 45 # If you run into InnoDB tablespace corruption, setting this to a nonzero # value will likely help you to dump your tables. Start from value 1 and # increase it until you're able to dump the table successfully. #innodb_force_recovery=0 # # Logging log_bin = 1 binlog_cache_size = 1M max_binlog_size = 100M slow_query_log_file = /srv/mysql/rt-slowquery.log slow_query_log = 1 long_query_time = 20 log_long_format #log = /srv/mysql/mysql.log [mysqldump] quick max_allowed_packet = 24M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ======================================================================== There's nothing of note in any of the logs. Thanks in advance, Rich -- RT Training - Boston, September 9-10 http://bestpractical.com/training