Data encoding and compression

OceanBase supports MySQL and Oracle tenants. To facilitate the use in MySQL or Oracle mode, OceanBase Database provides multiple configuration methods for you to use data encoding and data compression.

OceanBase Database supports the use of DDL statements to configure encoding and compression of data tables.

Configure data compression and encoding in MySQL mode

When you create or modify a table in MySQL mode, you can set the row_format and compression parameters to specify the encoding format and compression algorithm.

The syntax is as follows:

• Specify the encoding format and compression algorithm when you create the table.

  CREATE TABLE table_name table_definition_list
  ROW_FORMAT [=] row_format COMPRESSION [=] 'compression';
  

  For more information about the CREATE TABLE statement, see CREATE TABLE.

• Modify the encoding format and compression algorithm when you modify the table.

  ALTER TABLE table_name [alter_table_action_list] [SET] ROW_FORMAT [=]row_format COMPRESSION [=]  'compression';
  

  For more information about the ALTER TABLE statement, see ALTER TABLE.

Valid values of the row_format parameter include:

• REDUNDANT and COMPACT: Data is not encoded and is saved in the flat format.

• DYNAMIC and COMPRESSED: Data is encoded and saved in the encoding format. By default, DYNAMIC is used.

• CONDENSED: Data is encoded and saved in the selective encoding format.

  Selective encoding is a subset of encoding and is more friendly to read-only scenarios.

Valid values of the compression parameter include:

• 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.

• zlib_1.0: Data is compressed by using the zlib_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. By default, the zstd_1.3.8 algorithm is used.

• lz4_1.9.1: Data is compressed by using the lz4_1.9.1 algorithm.

For example:

• Specify the encoding format and compression algorithm when you create the table.

  Execute the following statement to set the encoding format to encoding and the compression algorithm to zstd_1.0 when you create 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 algorithm when you modify the table.

  Execute the following statement to change the table encoding format to selective encoding and the compression algorithm to zlib_1.0:

  obclient> ALTER TABLE test SET ROW_FORMAT = CONDENSED COMPRESSION = 'zlib_1.0';
  

Configure data compression and encoding in Oracle mode

When you create or modify a table in Oracle mode, you can specify the data encoding format and compression algorithm.

The syntax is as follows:

• Specify the encoding format and compression algorithm when you create the table.

  CREATE TABLE table_name table_definition_list compression;
  

  For more information about the CREATE TABLE statement, see CREATE TABLE.

• Modify the encoding format and compression algorithm when you modify the table.

  ALTER TABLE table_name [alter_table_action_list] compression;
  

  For more information about the ALTER TABLE statement, see ALTER TABLE.

Valid values of the compression parameter include:

• NOCOMPRESS: Data is not encoded or compressed, and is saved in the flat format.

• COMPRESS BASIC: Data is not encoded but is saved in the flat format and compressed by using the lz4_1.0 algorithm.

• COMPRESS FOR OLTP: Data is not encoded but is saved in the flat format and compressed by using the zstd_1.3.8 algorithm.

• COMPRESS FOR QUERY: Data is encoded, saved in the encoding format, and compressed by using the lz4_1.0 algorithm.

• COMPRESS FOR ARCHIVE: Data is encoded, saved in the encoding format, and compressed by using the zstd_1.3.8 algorithm. By default, COMPRESS FOR ARCHIVE is used.

• COMPRESS FOR QUERY LOW: Data is encoded, saved in the selective encoding format, and compressed by using the lz4_1.0 algorithm.

  Selective encoding is a subset of encoding and is more friendly to read-only scenarios.

For example:

• Specify the encoding format and compression algorithm when you create the table.

  Execute the following statement to set the encoding format to encoding and the compression algorithm to lz4_1.0 when you create a table:

  obclient> CREATE TABLE test (
  id number NOT NULL,
  fname VARCHAR2(30),
  lname VARCHAR2(30),
  hired DATE NOT NULL DEFAULT sysdate,
  separated DATE,
  job_code INT NOT NULL,
  store_id INT NOT NULL
  ) COMPRESS FOR QUERY;
  

• Modify the encoding format and compression algorithm when you modify the table.

  Execute the following statement to change the table encoding format to encoding and the compression algorithm to zstd_1.3.8:

  obclient> ALTER TABLE test COMPRESS FOR ARCHIVE;
  

Change the compression algorithm for SSTables

To change the compression algorithm for SSTables without causing a huge volume of I/O writes in major compactions, you need to launch a progressive compaction to rewrite all the data microblocks. The number of rounds of progressive compactions can be determined by specifying the progressive_merge_num parameter in the ALTER TABLE statement.

The procedure is as follows:

1. Log on to the corresponding tenant as an administrator.

2. Execute the following statement to set the number of rounds of progressive compactions.

   The progressive_merge_num parameter specifies the number of the rounds of progressive compactions for a table. The default value is 0, which indicates 100 rounds of progressive compactions. If you set the parameter to 1, a full compaction is performed.

   To set two rounds of progressive compactions, execute the following statement:

   obclient> ALTER TABLE t1 SET progressive_merge_num=2;
   

   After the parameter is set, data is progressively rewritten during major compactions each day.

   Note

   To complete data rewriting at the earliest opportunity, you can set progressive_merge_num to 1, and launch a major compaction. For more information about how to manually initiate a major compaction, see Manually initiate a major compaction.

3. Change the compression algorithm.

   For more information about how to change the compression algorithm in MySQL mode, see Configure data compression and encoding in MySQL mode in this topic.

   For more information about how to change the compression algorithm in Oracle mode, see Configure data compression and encoding in Oracle mode in this topic.