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

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.