Currently having couple tables with ROW_FORMAT=COMPRESSED which all have at least 200 GB in size, I wonder if I can change the innodb_compression_level that is currently on default (6) to 3. What happens with the current rows? Would that only affect new and updated rows?
The motivation behind it is that for some tables, we face quite some issues with the insert speed (datawarehouse tables) and when studying all the metrics, its clear that the CPU is reaching its limit (as it all runs on 1 core) so we hope to reduce the burden on the cpu by reducing the compression level
Ok. I have found the answer to this. The higher the compression level, the more CPU is required at time of inserting. As we are using 1 thread only (1 explicit transaction), the CPU of course gets more work to do hence compression has a direct negative impact on insert speed.
The good thing is: you can change the setting and it only affects new data (data pages) so for us it was improved by changing the setting innodb_compression_level to 3 and all new inserts are now faster.