Locate the file my.cnf from the server and open in a text editor.
Find the line:
On CA Spactrum 10.x, this is set to 256M. Increase this by 256M and test if the problem has been fixed by changing above line to:
innodb_buffer_pool_size = 512M
Modify the $SPECROOT/mysql/my-spectrum.cnf file.
If the innodb_lock_wait_timeout parameter is not present, add it to the end of the file and set it to 300 as follows:
innodb_lock_wait_timeout = 300
Test the optimize command, as the results may differ, based on the size of your reporting database. If it errors again, increase the innodb_buffer_pool_size by another 256M and retry.
It's better to increase gradually, until it resolves the problem, while also ensuring that there are no side effects, in other parts of the system.