Large mysql queries causing Spectrum Archive Manager to shut down or increase memory usage
Article ID: 112198
Updated On:
Products
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
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.
Feedback
