OceanBase AI Database supports the use of DDL statements to configure the encoding and compression of data tables.
Set data compression and encoding methods
When creating or modifying a table, you can specify the encoding format and compression algorithm by setting the row_format and compression parameters.
The syntax is as follows:
Specify the encoding format and compression method for a table when creating it.
CREATE TABLE table_name table_definition_list ROW_FORMAT [=] row_format COMPRESSION [=] 'compression';For more information about the
CREATE TABLEstatement, see CREATE TABLE.Change the encoding format and compression method for a table when modifying it.
ALTER TABLE table_name [alter_table_action_list] [SET] ROW_FORMAT [=]row_format COMPRESSION [=] 'compression';For more information about the
ALTER TABLEstatement, see ALTER TABLE.
The possible values for row_format are as follows:
REDUNDANTandCOMPACT: Data is not encoded and is stored in a flat format.DYNAMIC: Data is encoded and stored in an encoding format. The default value isDYNAMIC.COMPRESSED: Data is encoded and stored in an encoding format.CONDENSED: Data is encoded and stored in a selective encoding format.The selective encoding format is a subset of the encoding format, using only query-friendlier encoding methods.
The possible values for compression are as follows:
Note
Because different minor versions of the zlib compression algorithm may produce inconsistent compressed results for the same data, to avoid issues where replica data cannot be recovered during an OceanBase AI Database upgrade, OceanBase AI Database no longer supports the zlib_1.0 compression algorithm.
none: Data is not compressed.lz4_1.0: Data is compressed by using the lz4_1.0 algorithm.snappy_1.0: Data is compressed by using the snappy_1.0 algorithm.zstd_1.0: Data is compressed by using the zstd_1.0 algorithm.zstd_1.3.8: Data is compressed by using the zstd_1.3.8 algorithm. This is the default compression algorithm.lz4_1.9.1: Data is compressed by using the lz4_1.9.1 algorithm.
Examples:
Specify the encoding format and compression method when creating a table
Execute the following statement to specify the encoding format as encoding and the compression method as zstd_1.0 when creating a table.
obclient> CREATE TABLE test ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) ROW_FORMAT=CONDENSED COMPRESSION='zstd_1.0';Modify the encoding format and compression method of a table
Execute the following statement to change the encoding format to selective encoding and the compression method to lz4_1.0.
obclient> ALTER TABLE test SET ROW_FORMAT = CONDENSED COMPRESSION = 'lz4_1.0';
Set the storage format for minor compaction data
OceanBase AI Database supports storing minor compaction data in the flat or encoding format. You can specify the storage format by using the delta_format table attribute. The flat format may become a performance bottleneck in scenarios with a large volume of incremental data. The encoding format improves query performance in OLAP scenarios and reduces the storage space required for incremental data.
Notice
After you change the storage format for minor compaction data, the system will use the encoding format to store incremental data in subsequent minor compactions and will not rewrite existing incremental data.
Specify the storage format for minor compaction data when creating a table
CREATE TABLE table_name delta_format = 'flat | encoding' ;Note
Alternatively, you can leave the storage format unspecified when creating a table and set the default minor compaction format for all new tables by using the tenant-level parameter
default_delta_format.Modify the storage format for minor compaction data of a table
ALTER TABLE table_name SET delta_format = 'flat | encoding';In the statement,
delta_formatsupports the following two values:flat: Specifies to use the flat format for minor compaction data. The flat format is the default minor compaction format. It is designed for OLTP scenarios and provides fast write speeds but a low compression ratio, which is not optimal for OLAP queries.encoding: Specifies to use the encoding format for minor compaction data. This format is designed for OLAP scenarios and provides relatively slow writes but a higher compression ratio, which is beneficial for OLAP queries. The effect is more obvious on DELETE/INSERT tables.
Example:
When creating table t1, specify its storage format for minor compaction data as encoding.
obclient(root@mysql001)[test]> CREATE TABLE t1(col1 int, col2 float) delta_format = 'encoding';When modifying table t2, change its minor compaction data storage format to encoding.
obclient(root@mysql001)[test]> ALTER TABLE t2 SET delta_format = 'encoding';After the statement executes successfully, the modification takes effect after a delay of approximately 2 minutes. After the modification takes effect, the system uses the encoding format to store incremental data in subsequent minor compactions and does not rewrite existing incremental data.
Change the compression method for a table that has already generated SSTables
When you change the compression options for a table that has already generated SSTables, to avoid excessive I/O write pressure on a single major compaction, you need to gradually rewrite all microblock data through progressive major compactions. You can set the number of progressive major compactions by specifying the progressive_merge_num value in the ALTER TABLE statement.
The procedure is as follows:
Log in to the database as a tenant administrator.
Execute the following statement to set the number of progressive major compactions.
progressive_merge_numspecifies the number of progressive major compactions for a table. The default value is0, which indicates incremental compaction. If the value is set to1, it indicates full compaction.Example:
obclient> ALTER TABLE t1 SET progressive_merge_num=2;After the value is set, data is progressively rewritten during daily major compactions.
Note
To complete the data rewriting as soon as possible, you can set the
progressive_merge_numvalue to1and then manually initiate a major compaction. For more information, see Manually trigger a major compaction.Change the compression options.
For more information about how to change the compression options, see Set data compression and encoding methods in this section.