Large mysql queries causing Spectrum Archive Manager to shut down or increase memory usage
search cancel

Large mysql queries causing Spectrum Archive Manager to shut down or increase memory usage

book

Article ID: 112198

calendar_today

Updated On:

Products

CA Spectrum DX NetOps

Issue/Introduction

Large mysql queries causing Spectrum Archive Manager to shut down or increase in memory usage.

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 <hostName> kernel: Out of memory: Kill process 16884 (mysqld) score 894 or sacrifice child
Aug  8 17:22:34 <hostName> kernel: Killed process 16884, UID 22357, (mysqld) total-vm:67062348kB, anon-rss:60628144kB, file-rss:16kB
Aug  8 17:22:42 <hostName> 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)

 

Environment

Release: Any
Component: SPCOCK

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

NOTE:  In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.

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 -p<PASSWD>

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: <userName>
   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

4.  A user requesting a massive amount of events in OC.  For example - selecting a GC or a container model and clicking on the Events tab for that collection.

Additional Information

There is an item in the Spectrum backlog for ArchMgr scaling to improve the handling which is scheduled to be resolved in a future release of Spectrum.