Mysql slowness with subject searches (possibly others)

Rich_Bishop · August 14, 2014, 9:33pm

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:

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

Rich_Bishop · August 17, 2014, 4:38pm

Follow up to my own question - we’ve still seeing these problems and have
discovered it seems to be due to mysql not using an index on the query. This
query:

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 =
‘1060860’ ) 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 = ‘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 =
‘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 ‘%floria%’) ORDER
BY main.id ASC LIMIT 50;

takes around 22s, add use index (Tickets1) and it runs in 1.5s. I’m not aware
that we can add the index hint into a DBIx::SearchBuilder query and I’m not sure
how to force mysql to use the index without a hint. Does anyone have any ideas?

Thanks,

Rich