After using the Events tab in OneClick it is noticed that queries are backing up in MySQL.
Using "SHOW FULL PROCESSLIST;" in the MySQL Client shows something similar to this query:
INFO: SELECT E.model_h, M.model_name, M.mtype_h, MT.mtype_name, E.utime, E.counter, E.clk_seq, E.version, E.node_id, U.user_name, E.type, E.severity, E.vardata_string FROM event as E, model as M, model_type as MT, user_def as U WHERE E.model_h = M.model_h AND M.mtype_h = MT.mtype_h AND E.user_key = U.user_key AND E.utime >= 1533239885 ORDER BY E.utime ASC, E.counter LIMIT 0 , 10000
The "Time" column will show a very large value, in seconds.
This is caused by requesting too much data from the OneClick Events tab.
In versions prior to 10.3, this will have to be resolved manually by logging into MySQL and killing the queries.
1. Navigate to $SPECROOT/mysql/bin
2. Type: ./mysql --defaults-file=../my-spectrum.cnf -uroot -proot
3. At the mysql> prompt type:
show full processlist\G
Each query seen here will have an ID.
Note - Do NOT kill any queries that are not SELECT queries. INSERT and DELETE queries must finish or there will be risk of data loss/inconsistencies.
On Spectrum 10.3 Oracle introduced a feature in MySQL 5.7.8+ to set a max statement time. (CA Spectrum 10.3 is running 5.7.22)
1. Navigate to $SPECROOT/mysql directory
2. Open my-spectrum.cnf file in a text editor.
3. At the bottom of the file add:
max_execution_time=<time in ms>
This will set it to 1 minute.
4. Restart the Spectrum MySQL service
This will automatically kill any read-only SELECT query hitting that limit.