PolarDB:Create a column-store snapshot

Prerequisites

The instance must be 5.4.20 or later, and the engine must be MySQL 5.7 or MySQL 8.0.

Preparations

Before you use the column-store snapshot feature, you can configure the following parameters of the database. After you connect to the database, perform the following steps:

• (Optional) Execute the following statement to enable strict external consistency guarantee for all write transactions:

  SET GLOBAL ENABLE_EXTERNAL_CONSISTENCY_FOR_WRITE_TRX = TRUE;

  Note

  • Use the privileged account to execute the statement.

  • If the result contains warnings, the execution failed. Check whether you are using the privileged account.

  • After strict external consistency guarantee is enabled for transactions, all transactions use the strict two-phase commit mode. This slightly impacts the data write performance of row-store data. A column-store snapshot point generated by using columnar_flush() is also a row-store snapshot point.

  • If strict external consistency guarantee is disabled for transactions, the transaction integrity of a column-store snapshot point is guaranteed. However, the column-store snapshot point may not be a row-store snapshot point. For more information, see Generate snapshot points.

• (Optional) Disable adaptive heartbeats for CCIs.

  CALL polardbx.columnar_set_config('columnar_heartbeat_interval_ms_self_adaption', false);

  Note

  We recommend that you disable adaptive heartbeats for CCIs to avoid excessive metadata.

Create a column-store snapshot

A column-store snapshot is implemented based on CCIs. Therefore, you can create a column-store snapshot by using the CREATE CLUSTERED COLUMNAR INDEX statement and setting the type parameter in columnar_options to snapshot. For more information, see Create and use a CCI.

Syntax

CREATE
    CLUSTERED COLUMNAR INDEX snapshot_name    
    ON tbl_name (index_sort_key_name,...)    
    [partition_options] 
    columnar_options='{
      "k1":"v1",
      "k2":"v2",
    }'

columnar_options:
    "type":"snapshot"
    "snapshot_retention_days":"7"
    "auto_gen_columnar_snapshot_interval":"30"

The following table describes the parameters.

Examples

You can use one of the following methods to create a column-store snapshot.

• Create a CCI and a column-store snapshot when you create a table.

  CREATE TABLE t_order (
    `id` BIGINT(11) NOT NULL AUTO_INCREMENT,
    `order_id` VARCHAR(20) DEFAULT NULL,
    `buyer_id` VARCHAR(20) DEFAULT NULL,
    `seller_id` VARCHAR(20) DEFAULT NULL,
    `order_snapshot` LONGTEXT DEFAULT NULL,
    `order_detail` LONGTEXT DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `l_i_order` (`order_id`),
    CLUSTERED COLUMNAR INDEX `seller_snapshot` (`seller_id`)
      PARTITION BY HASH(`order_id`)
      PARTITIONS 16
      COLUMNAR_OPTIONS='{
        "type":"snapshot",
        "snapshot_retention_days":"7",
        "auto_gen_columnar_snapshot_interval":"30"
      }'
  ) ENGINE=INNODB DEFAULT CHARSET=UTF8 PARTITION BY HASH(`order_id`) PARTITIONS 16;

• Use the CREATE CLUSTERED COLUMNAR INDEX statement to create a CCI and a column-store snapshot for a table.

  CREATE CLUSTERED COLUMNAR INDEX `seller_snapshot` ON t_order (`seller_id`) 
  PARTITION BY HASH(`order_id`) 
  PARTITIONS 16 
  COLUMNAR_OPTIONS='{
    "type":"snapshot",
    "snapshot_retention_days":"7",
    "auto_gen_columnar_snapshot_interval":"30"
  }';

• Use the ALTER statement to create a CCI and a column-store snapshot for a table.

  ALTER TABLE t_order ADD CLUSTERED COLUMNAR INDEX `seller_snapshot` (`seller_id`) 
  partition by hash(`order_id`) 
  partitions 16 
  columnar_options='{
    "type":"snapshot",
    "snapshot_retention_days":"10",
    "auto_gen_columnar_snapshot_interval":"30"
  }';

• Create a column-store snapshot for a table that has a CCI created.

  CALL polardbx.columnar_set_config(<cci_id>, 'type', 'snapshot');

  Note

  cci_id is the CCI ID. You can execute the SHOW COLUMNAR STATUS statement to query the cci_id value. For more information, see SHOW COLUMNAR STATUS.

Modify the attributes of a column-store snapshot

• Modify the retention period of the column-store snapshot and the interval at which snapshot points are automatically generated.

  -- Modify the retention period to 10 days.
  CALL polardbx.columnar_set_config(<cci_id>, 'snapshot_retention_days', 10);
  
  -- Modify the interval to generating a snapshot point every 10 minutes.
  CALL polardbx.columnar_set_config(<cci_id>, 'auto_gen_columnar_snapshot_interval', 10);
  
  -- Disable automatic snapshot point generation.
  CALL polardbx.columnar_set_config(<cci_id>, 'auto_gen_columnar_snapshot_interval', -1);

• Modify the global defaults of the column-store snapshot.

  -- Set the global default interval for generating snapshots to 20 minutes
  CALL polardbx.columnar_set_config('auto_gen_columnar_snapshot_interval', 20);
  -- Set the global default snapshot retention period to 7 days
  CALL polardbx.columnar_set_config('snapshot_retention_days', 7);

  Note

  • The new values take effect only on new column-store snapshots and do not affect existing column-store snapshots.

  • If you do not explicitly set the snapshot_retention_days andauto_gen_columnar_snapshot_interval values when you create a column-store snapshot, the system uses the global settings of these two parameters.

Delete a column-store snapshot

• Delete the CCI.

  DROP INDEX `seller_snapshot` ON t_order;

• Delete the table.

  DROP TABLE t_order;

View column-store snapshot definitions

You can execute the following statement to view the definitions of a column-store snapshot.

SHOW FULL CREATE TABLE t_order;