ColumnStore System Variables
| Name | Cmd-Line | Scope | Data type | Default Value | Range |
|---|---|---|---|---|---|
| infinidb_compression_type | Yes | Both | enumeration | 2 | 0,2 |
| infinidb_decimal_scale | Yes | Both | numeric | 8 | |
| infinidb_diskjoin_bucketsize | Yes | Both | numeric | 100 | |
| infinidb_diskjoin_largesidelimit | Yes | Both | numeric | 0 | |
| infinidb_diskjoin_smallsidelimit | Yes | Both | numeric | 0 | |
| infinidb_double_for_decimal_math | Yes | Both | enumeration | OFF | OFF, ON |
| infinidb_import_for_batchinsert_delimiter | Yes | Both | numeric | 7 | |
| infinidb_import_for_batchinsert_enclosed_by | Yes | Both | numeric | 17 | |
| infinidb_local_query | Yes | Both | enumeration | 0 | 0,1 |
| infinidb_ordered_only | Yes | Both | enumeration | OFF | OFF, ON |
| infinidb_string_scan_threshold | Yes | Both | numeric | 10 | |
| infinidb_stringtable_threshold | Yes | Both | numeric | 20 | |
| infinidb_um_mem_limit | Yes | Both | numeric | 0 | |
| infinidb_use_decimal_scale | Yes | Both | enumeration | OFF | OFF, ON |
| infinidb_use_import_for_batchinsert | Yes | Both | enumeration | ON | OFF, ON |
| infinidb_varbin_always_hex | Yes | Both | enumeration | ON | OFF, ON |
| infinidb_vtable_mode | Yes | Both | enumeration | 1 | 0,1,2 |
Contents
Compression Mode:
MariaDB ColumnStore has the ability to compress data and is controlled through a compression mode. This compression mode may be set as a default Golbally or set at the session level.
To set the compression mode at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.
set infinidb_compression_type = n
where n is:
- 0) compression is turned off. Any subsequent table create statements run will have compression turned off for that table unless any statement overrides have been performed. Any alter statements run to add a column will have compression turned off for that column unless any statement override has been performed.
- 2) compression is turned on. Any subsequent table create statements run will have compression turned on for that table unless any statement overrides have been performed. Any alter statements run to add a column will have compression turned on for that column unless any statement override has been performed.
ColumnStore Decimal to Double Math
MariaDB ColumnStore has the ability to change intermediate decimal mathematical results from decimal type to double. The decimal type has approximately 17-18 digits of precision, but a smaller maximum range. Whereas the double type has approximately 15-16 digits of precision, but a much larger maximum range. In typical mathematical and scientific applications, the ability to avoid overflow in intermediate results with double math is likely more beneficial than the additional two digits of precisions. In banking applications, however, it may be more appropriate to leave in the default decimal setting to ensure accuracy to the least significant digit.
Enable/Disable Decimal to Double Math
The infinidb_double_for_decimal_math variable is used to control the data type for intermediate decimal results. This decimal for double math may be set as a default for the instance, set at the session level, or at the statement level by toggling this variable on and off.
To enable/disable the use of the decimal to double math at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.
set infinidb_double_for_decimal_math = n
where n is:
- 0 (disabled)
- 1 (enabled)
ColumnStore Decimal Scale
ColumnStore has the ability to support varied internal precision on decimal calculations. infinidb_decimal_scale is used internally by the ColumnStore engine to control how many significant digits to the right of the decimal point are carried through in suboperations on calculated columns. If, while running a query, you receive the message ‘aggregate overflow’, try reducing infinidb_decimal_scale and running the query again. Note that,as you decrease infinidb_decimal_scale, you may see reduced accuracy in the least significant digit(s) of a returned calculated column. infinidb_use_decimal_scale is used internally by the ColumnStore engine to turn the use of this internal precision on and off. These two system variables may be set as a default for the instance or set at the session level.
Enable/disable decimal scale
To enable/disable the use of the decimal scale at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.
set infinidb_use_decimal_scale = n
where n is 0 (disabled) or 1 (enabled).
Set decimal scale level
To set the decimal scale at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.
set infinidb_decimal_scale = n
where n is the amount of precision desired for calculations.
