If you are using block-level compression (BLC) you may have glanced over the list of 40+ DBS Control parameters that influence how this feature is behaving on your platform. Be aware that most of these parameters have been filled in with reasonable defaults for your system, and with some exceptions, you are not encouraged to experiment with them.
One parameter that might be particularly tempting to change is called CompressionLevel. This posting describes what the compression level parameter is and why you do not want to casually start increasing it.
This posting applies to Teradata Database 16.10 and greater releases.
Before jumping into a discussion on compression level, be aware that compression parameters take effect without having to perform a database restart. If you change compression parameters and want them to be applied to a table that has already been compressed, use the Ferret COMPRESS command on the table. After doing so, the whole table is recompressed using the new tunable values. Otherwise, changes to the parameters only take effect when compressed blocks are updated, and then only on the particular data blocks being updated.
CompressionLevel, which can be expressed in a range of '1' to' 9', directs the compression operation how to balance the speed of the operation and the degree of compression that results. If CompressionLevel is set to '1' compression algorithms will execute in way to favor speed, by minimizing the CPU applied to do the compression. As a result of favoring speed, the degree of compression is expected be less with the '1' setting because less effort is being applied.
As you increase the CompressionLevel parameter value, more time is spent on the compression operation so greater compression may be achieved, but at the expense of more CPU and a longer elapsed time. The maximum value for the CompressionLevel parameter is '9'.
As you increase the parameter from the default of '1', the CPU used for compression gradually grows. More CPU used during the compression operations on data blocks is likely to lengthen the time to load data into the database.
There are a couple of things to note about this parameter:
What to Expect Increasing Compression Level
Increasing the compression level across different test and production systems has not led to favorable results. In cases where Teradata sites have increased CompressionLevel from '1' to '6' only a mere 5% improvement in space savings has been achieved. However, with that change the CPU used increased by about 30%.
With smaller adjustments upward, for example from '1' to '3', the compression level parameter hasn't shown much change either way. In one test case, space savings improved only 0.8% while the CPU overhead for compression went up 0.9%. That's not a significant enough difference to make it worthwhile to deviate from the default of '1'.
The impact of changing the compression level is very dependent on the data and its characteristics. You could see more, or less change on either the CPU overhead side or the space savings side, than reported in the cases above. There is no way to predict ahead of time, which is a reason to be cautious.
The impact of this setting is not just on the compression side when data blocks are being written. In order to read data that has been compressed with BLC, the data blocks have to be decompressed. Decompression of BLC-compressed data comes with some CPU overhead, although it is generally a fraction of the CPU required for compressing the same data. Increasing CompressionLevel is likely to increase the CPU cost of decompression when reading BLC-compressed data.
If you are on a platform where all user tables are universally compressed, it is even more important to be understand the tradeoffs and be cautious of increasing the CompressionLevel parameter. In such cases, the side-effects of the change will have a wider impact.
Because the increase in CPU time involved in compression is disproportionately higher than the advantage realized in space savings when this setting is increased, it is recommended that the default setting for CompressionLevel remain unchanged at '1' if you are on 16.10 or above. I advise anyone considering a modification to this setting to speak with the support center as a first step.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.