search cancel

CA Spectrum mysql-database service too slow due myisam

book

Article ID: 113325

calendar_today

Updated On:

Products

CA Spectrum

Issue/Introduction

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).


 

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. 
 
 

Environment

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

 

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.