CA Spectrum mysql-database service too slow due myisam

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

Customer sees CABI Report processing "takes ever" even for a small (few pages) report. This was not seen previously. 
When checking the process-list the mysqld service task is always very high in CPU (almost 100% all time).

Environment

This applies to all supported CA Spectrum platforms and all CA Spectrum releases

 

Cause

CA Spectrum mysql -database service shows the " innodb  engine" is not loaded and working. By this the mysql  service run into a fall back to make use of " myisam"-table technology. As the myisam  engine only supports "table lock", the mysql  effective performance drops down even seeing all time 1 thread / 100% CPU.

The "innodb" engine and table-design supports "row level lock" so a concurrent (multi-threaded) mysql service is possible. 

Resolution

New OC/SRM install from scratch to get mysql database service into proper "innodb" operation.

 

Additional Information

NOTE: Starting from DX NetOps Spectrum 21.2.4, the default root password for MySql is "MySqlR00t". For DX NetOps Spectrum versions prior to 21.2.4, the default root password is "root". In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.

At native mysql-level it is possible to check for the "innodb" engine status and details by doing the following

- Log into the system as the user that owns the Spectrum installation

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

- cd to the $SPECROOT/mysql/bin directory and enter the following commands:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> -e "show variables;"
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> -e "show engines;"
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting -e "show table status\G" | grep -A 2 "\. row \*"

In case the mysql service runs into exceptions it will provide more details in MYSQL.OUT or <hostname>.err files.