NCM Updates to SRM causing mysql to consume 100% cpu

Products

• DX NetOps
• CA Spectrum

Issue/Introduction

The mysqld process on the Spectrum Report Manager machine was consuming 100% cpu consistently.

When enabled slow query logging, it was found that ncm updates were taking anywhere from 10 seconds to 3 hrs and they were running constantly.

UPDATE ncm_config SET last_updated_time  = CURRENT_TIMESTAMP where ncm_config.device_model_key = 34813863 |

Cause

Needed to index the ncm_config table

Resolution

On the Spectrum Report Manager OneClick server, log into mysql:

cd $SPECROOT/mysql/bin:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<password> reporting -A;

CREATE INDEX idx_device_model_key ON ncm_config(device_model_key);