Large mysql queries causing Spectrum Archive Manager to shut down.
The ARCHMGR.OUT is showing a very large query similar to the following:
Aug 10 10:02:37 ERROR TRACE at ModelArchDBImp.cc(4820): doSqlQuery/mysql_query: Failure executing query:
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.type <>4293920527 AND E.type <>4293921173 AND E.type <>18258867 AND E.type <>79501342 AND E.type <>79504618 AND E.type <>79506020 AND E.type <>108007762 AND ......
The following error is also seen in the ARCHMGR.OUT file
Native Error: 2003, Can't connect to MySQL server on 'localhost' (111)
If running Spectrum on Linux, check the /var/log/messages file for an error similar to the following:
Aug 8 17:22:34 dc01amutilnm912 kernel: Out of memory: Kill process 16884 (mysqld) score 894 or sacrifice child
Aug 8 17:22:34 dc01amutilnm912 kernel: Killed process 16884, UID 22357, (mysqld) total-vm:67062348kB, anon-rss:60628144kB, file-rss:16kB
Aug 8 17:22:42 dc01amutilnm912 abrt: Saved core dump of pid 17178 (/data/CA/Spectrum/SS/DDM/ArchMgr) to /var/spool/abrt/ccpp-2018-08-08-17:22:36-17178 (733437952 bytes)
A large mysql query is causing mysql to use up all memory shutting down the Archive Manager. This may also affect SpectroSERVER performance if all memory is exhausted.
The mysql query can be killed by doing the following:
1. Log into the SpectroSERVER system as the user that owns the Spectrum installation
2. If on Windows, start a bash shell by running "bash -login"
3. cd to the $SPECROOT/mysql/bin directory
4. Log into mysql by entering the following command:
./mysql --defaults-file=../my-spectrum.cnf -uroot -proot
5. Enter the following command to output information to a file named mysql.output (the reason for this is the next command may print out more information than what can fit in the window buffer):
6. Enter the following command to show the mysql process list (If a large query is running, you may have to refer to the mysql.output file to find the problem query):
show full processlist\G
7. Enter the following command to stop the logging:
8. Examine the mysql.out file for a large query. The following is an example for a smaller query:
*************************** 11. row ***************************
State: Copying to tmp table
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.type NOT IN ( 67331,65541,66074,66073,67335,67333 ) AND E.utime >= 1533130560 ) AND E.utime >= 0 ORDER BY E.utime ASC, E.counter LIMIT 0 , 10007
9. Note the process "Id" of the querry. In the above example, the "Id" is 175
10. Enter the following command to kill the query where <ID> is the :
kill <ID> ;
The next step is trying to identify where the large query came from. Some places to look:
1. Users setting a large Start/End time range filter in the OneClick Events tab
2. Users setting a large "Excluded event types" list filter in the OneClick Events tab
3. Large mysql queries directly to the mysql database