MySQL optimization for Spectrum Report Manager

Products

• Spectrum
• Network Observability

Issue/Introduction

For busy Spectrum Reporting Manager installations, we can see MySQL CPU quite high

How do we optimize MySQL for reporting in Spectrum?

Environment

Release : All current releases.

Component : Spectrum Reporting

Cause

For busy Spectrum Reporting Manager installations, we can tune mySql to optimize for SRM by adding resources to mySql. 

Resolution

in the mysql conf file we can increase resources as follows:

innodb_file_per_table
innodb_buffer_pool_size = 16384M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb-buffer-pool-instances = 4
innodb_lock_wait_timeout = 600
innodb_read_io_threads = 32
innodb_write_io_threads = 32
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:20G

we will need to restart mysql when appropriate to have these changes implemented.