Re-initializing the reporting db, is there a way to improve the SRM performance to upload the historical events from ddmdb more quickly?

Products

• CA Spectrum

Issue/Introduction

Customer is re-initializing the reporting db by running: ./RpmgrInitializeLandscape.sh root root -initHist 120. As the DDMDB is very large the loading of ddmdb historical events in reporting db is taking several days. Any way to improve performance and make the synchronization quicker?

Re-initializing the reporting db, is there a way to improve the SRM performance to upload the historical events from ddmdb more quickly?

Environment

any

Resolution

In my-spectrum.cnf file set the following parameter: 

innodb_flush_log_at_trx_commit=2 

Then restart mysql (you can stop and start processd) and Spectrum Tomcat. 

This should improve the mysql performance and ddmdb events will be loaded more quickly in the reporting db. 

 

Additional Information

https://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2 and also http://www.rathishkumar.in/2017/10/understanding-mysql-innodb-flush-log-at-trx-commit.html 

where you can read: 

innodb_flush_log_trx_at_commit=1 

When you set innodb_flush_log_trx_commit=1, InnoDB will write the log buffer to transaction log and flush to durable storage for every transaction. Here, for all transaction commit, InnoDB will write to log and then write to disk, if in case the slower disk storage, it will badly impact the performance, i.e. the number of InnoDB transaction per second will be reduced. 

innodb_flush_log_trx_at_commit=2 

When you set innodb_flush_log_trx_commit = 2, InnoDB will write the log buffer to log file at every commit, but don't write data to disk. InnoDB flushes data once in every second. Option 2, even if there is a power failure or system crash, data will be available in log file and can be recoverable. 

Key points: 

During buffer to log operation, i.e. writing from InnoDB buffer pool to InnoDB transaction log file - data is simply moved from InnoDB buffer to Operating system's cache, actually not written to the durable storage. So if you set innodb_fush_log_at_trx_commit either 0 or 2, there is a possibility of losing data up to one second. 

If innodb_fush_log_at_trx_commit is set to 1 - InnoDB compels Operating System to flush data to durable storage. Writing to disk is a slow process and it is I/O blocking operation to ensure data is written to disk. So using this option, there is a chance; number of transaction per second will be reduced. 

Note that, by default MySQL will auto commit the transactions. 

Setting innodb_fush_log_at_trx_commit as 2 is useful when restoring huge databases, it will reduce the restoration time dramatically, and there are different opinions on this point, it is better to test in our own hand. In my experience, it really helped with the reduced restoration time.