CA Spectrum mysql-database service too slow due myisam

Document ID : KB000113325
Last Modified Date : 07/09/2018
Show Technical Document Details
Issue:
Customer sees CABI Report processing "takes ever" even for a smal (few pages) report. This was not seen perviously. 
When checking the process-list the mysqld service task is alway 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 - do so from $SPECROOT/mysql/bin:

./mysql[exe] --defaults-file=../my-spectrum.cnf -uroot -proot -e"show variables;"
./mysql[exe] --defaults-file=../my-spectrum.cnf -uroot -proot -e"show engines;"
./mysql[exe] --defaults-file=../my-spectrum.cnf -uroot -proot 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.