Large mysql queries causing Spectrum Archive Manager to shut down

Document ID : KB000112198
Last Modified Date : 27/08/2018
Show Technical Document Details
Issue:
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[17583]: 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)

 
Cause:
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.
Resolution:
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):

\T mysql.output

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:

\t

8. Examine the mysql.out file for a large query. The following is an example for a smaller query:

*************************** 11. row ***************************
     Id: 175
   User: ackjo04
   Host: localhost:51040
     db: ddmdb
Command: Query
   Time: 1
  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