Ticket Search SQL Query Stuck

[estoque]

I'm having problems with my MySQL server frequently getting stuck on Ticket Search Queries, using 100% CPU of the database server and OTRS will be unresponsive.
TicketSearch.pm: SELECT COUNT(DISTINCT(st.id)) FROM ticket.....
Found these queries to be 5 minutes and still running. Any advise would be helpful

Changed already from RuntimeDB to Static
OTRS 4.0.6
Open Tickets: 6000+
Total Tickets: 200k+
CPU: Intel(R) Xeon(R) CPU E7-L8867 @ 2.13GHz, 32 cores
RAM: 64GB

my.cnf
tmp_table_size = 32M
max_heap_table_size = 64M
key_buffer = 4096M
max_allowed_packet = 80M
thread_stack = 192K
thread_cache_size = 8
innodb_buffer_pool_size=24G
innodb_buffer_pool_instances=24
innodb_additional_mem_pool_size=16M

myisam-recover = BACKUP
max_connections = 1000
thread_concurrency = 64
query_cache_limit = 32M
query_cache_size = 64M
general_log_file = /var/log/mysql/mysql.log
general_log = 0

[RStraub]

Do you have a proper index on the ticket-id column?

Try with

Code: Select all

show indexes from ticket;

If that's a BTREE, a cardinality of 200.000+ shouldn't be a problem and not account for the long run time.

Do you have where clauses and the engine of InnoDb? In that case try without where to verify the index is working.

[root]

Enable the slow query log and check the long running queries with the EXPLAIN statement.

[estoque]

Yes mysql is configured with innodb plugin, and ticket has proper indexes. I enabled slow query log and these are the long running queries:
1. SELECT DISTINCT(xml_key) FROM xml_storage WHERE xml_type = 'Stats';
(213s)
2. SELECT login , first_name, last_name, email FROM customer_user WHERE ((login = '29100799' OR first_name = '29100799' OR last_name = '29100799' OR customer_id = '29100799' OR circuit_id = '29100799' OR node_HQ = '29100799' OR node_remote = '29100799' OR mobile = '29100799' OR email = '29100799') ) AND valid_id IN (1) LIMIT 20;
(180s)

I've tried:
1. For customer_user searching, I minimize searched columns in Config.pm from 9 columns to 3 columns(login,customer_id,email)
2. Removed * prefix and suffix on customer user searching.
3. Configured MirrorDB and set to my DB slave server

Now, CPU usage is down to 22% From 90%. Are there any workarounds for this issue aside from what I have done? I'm thinking that I'm just limiting OTRS.

[estoque]

update: enabled case sensitive search in Config.pm and the slow queries are now using indexes, LOWER() function in particular does not use index.