A large file is created in the Spectrum $SPECROOT/mysql/tmp directory

Products

• CA Spectrum

Issue/Introduction

A large file is created in the Spectrum $SPECROOT/mysql/tmp directory

It grows to more than 10 times the size of the Archive Manager database and consumes nearly all of the available disk space.

It eventually stops growing, reduces in size and disappears.

Environment

Release: Any
Component: SPCAEM

Cause

The most likely root cause is a large mysql query is running

Resolution

So the following to check if there is a large mysql query running:

1. Log into the SpectroSERVER system as the user that owns the Spectrum installation

2. If running Windows, start a bash shell by running "bash -login"

3. cd to the $SPECROOT/mysql/bin directory and execute the following command:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> ddmdb -e "show full processlist\G"

4. Look for a large query. The following is an example:

*************************** 5. row ***************************
     Id: 246
   User: spectrum
   Host: localhost:36356
     db: ddmdb
Command: Query
   Time: 0
  State: Sending data
   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 >= 1583414878 )  AND E.utime  >= 0  ORDER BY E.utime ASC, E.counter  LIMIT 90063 , 10007

5. You can kill the query the kill command. For example, the above query is id "246". To kill this query, you would enter the following command from the $SPECROOT/mysql/bin directory:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> ddmdb -e "kill 246;"

Additional Information

Try to determine where the large query originated.

Some known reasons for large database queries:

1. A user is viewing a large number of events in a OneClick Events tab. For example, if they choose the Landscape icon in the Explorer tab and then click on the Events tab in the Contents panel.

2. A user has a large number excluded event types in the "Event Type" tab of the "Event Filter"

If you are unable to determine where the large query is originating, please contact Spectrum Support for assistance.