[rt-users] Mysql slowness with subject searches (possibly others)

Thu Aug 14 17:33:43 EDT 2014

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