tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TencentDB for MySQL
    Documentation
    Download PDF

    Column Compression

    Last updated: 2024-07-22 11:34:21
    Download PDF

      Overview

      Row compression and data page compression are already supported, but if small fields in a table are read and written frequently while big fields are not, both of the compression methods waste a lot of computing resources.
      In contrast, column compression can compress big fields that are infrequently accessed while ignoring the frequently accessed small fields, which not only reduces the space for storing whole rows of fields but also improves the read and write access efficiency.
      For example, in the employee table create table employee(id int, age int, gender boolean, other varchar(1000) primary key (id)), if access is frequent for small fields such as id, age, and gender but infrequent for the large field other, you can compress the other column. Generally, only read/write of the other column rather than other columns will trigger the compression and decompression of this column, which further reduces the size of the stored row data. In this way, frequently accessed small fields can be accessed more quickly, while infrequently accessed large fields can be compress to use less storage space.

      Supported Versions

      Kernel version: MySQL 5.7 20210330 and above.
      Note:
      Column compression for closed by default, if you want to use, please submit a ticket to open.

      Use Cases

      If a table has many frequently accessed small fields and infrequently accessed large fields, you can compress the large field columns.

      Instructions

      Supported data types

      1. BLOB (including TINYBLOB, MEDIUMBLOB, and LONGBLOB)
      2. TEXT (including TINYTEXT, MEDIUMTEXT, and LONGTEXT)
      3. VARCHAR
      4. VARBINARY
      Note:
      Here, the maximum length of LONGBLOB and LONGTEXT is $2^{32}-2$ bytes, which is one byte less than $2^{32}-1$ supported by native MySQL as described in String Type Storage Requirements.

      Supported DDL syntax types

      Different from the table creation syntax of native MySQL, the definition of COLUMN_FORMAT in column_definition is changed in TencentDB for MySQL. In addition, column compression is supported only for tables with the InnoDB storage engine.
            column_definition:
              data_type [NOT NULL | NULL] [DEFAULT default_value]
                [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
                [COMMENT 'string']
                [COLLATE collation_name]
                [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}|COMPRESSED=[zlib]]  # `COMPRESSED` is the compressed column keyword
                [STORAGE {DISK|MEMORY}]
                [reference_definition]
      Below is a simple example:
      CREATE TABLE t1(
        id INT PRIMARY KEY,
        b BLOB COMPRESSED
      );
      Here, as the compression algorithm is not specified, the zlib algorithm will be selected by default. You can also specify the compression algorithm keyword, but only zlib is supported currently.
      CREATE TABLE t1(
        id INT PRIMARY KEY,
        b BLOB COMPRESSED=zlib
      );
      The following DDL syntaxes are supported: CREATE TABLE:
      DDL
      Whether the Compression Attribute is Inherited
      CREATE TABLE t2 LIKE t1;
      Yes
      CREATE TABLE t2 SELECT * FROM t1;
      Yes
      CREATE TABLE t2(a BLOB) SELECT * FROM t1;
      No
      ALTER TABLE:
      DDL
      Description
      ALTER TABLE t1 MODIFY COLUMN a BLOB;
      Alters a compressed column into a non-compressed one
      ALTER TABLE t1 MODIFY COLUMN a BLOB COMPRESSED;
      Alters a non-compressed column into a compressed one

      New variable description

      Parameter
      Effective Immediately
      Type
      Default Value
      Valid Values/Value Range
      Description
      innodb_column_compression_zlib_wrap
      Yes
      bool
      TRUE
      TRUE/FALSE
      If it is set to TRUE, the data zlib header and tail will be generated, and Adler-32 check will be performed.
      innodb_column_compression_zlib_strategy
      Yes
      Integer
      0
      [0, 4]
      Column compression policy. Valid values: 0: Z_DEFAULT_STRATEGY; 1: Z_FILTERED; 2: Z_HUFFMAN_ONLY; 3: Z_RLE; 4: Z_FIXED.
      Generally, Z_DEFAULT_STRATEGY is the best choice for text data, while Z_RLE for image data.
      innodb_column_compression_zlib_level
      Yes
      Integer
      6
      [0, 9]
      Column compression level. Value range: 0–9. 0 indicates not to compress. The higher the value, the smaller the data size after compression, and the longer the compression duration.
      innodb_column_compression_threshold
      Yes
      Integer
      256
      [0, 0xffffffff]
      Column compression threshold in bytes. Value range: 1–0xffffffff. Only data whose length is at or above this threshold will be compressed; otherwise, the original data will stay unchanged with only a compression header added.
      innodb_column_compression_pct
      Yes
      Integer
      100
      [1, 100]
      Column compression ratio in percentages. Value range: 1–100. Data will be compressed only if the data size after compression/data size before compression is below this value; otherwise, the original data will stay unchanged with only a compression header added.
      Note:
      Currently, you cannot directly modify the values of the above parameters. If needed, submit a ticket for assistance.

      New status description

      Name
      Type
      Description
      Innodb_column_compressed
      Integer
      Number of column compressions, including compressions for non-compressed data and compressed data.
      Innodb_column_decompressed
      Integer
      Number of column decompressions, including decompressions for non-compressed data and compressed data.

      New error description

      Name
      Scope
      Description
      Compressed column '%-.192s' can't be used in key specification
      Name of the column specified for compression
      The compression attribute cannot be specified for a column with an index.
      Unknown compression method: %s"
      Name of the compression algorithm specified in the DDL statement
      An invalid compression algorithm other than zlib is specified in the CREATE TABLE or ALTER TABLE statement.
      Compressed column '%-.192s' can't be used in column format specification
      Name of the column specified for compression
      If the COLUMN_FORMAT attribute has been specified for a column, other attributes cannot be specified, and COLUMN_FORMAT can be used only in NDB.
      Alter table ... discard/import tablespace not support column compression
      \\
      The ALTER TABLE ... DISCARD/IMPORT TABLESPACE statement cannot be executed for tables with column compression enabled.

      Performance

      The performance varies by DDL and DML statements: For DDL statements, sysbench is used for testing:
      Column compression compromises much performance of DDL statements with the COPY algorithm, and the performance after compression is 7–8 times lower than before.
      The impact of column compression on INPLACE DDL statements is subject to the data volume after compression. If the overall data size is reduced after compression, the DDL performance will be improved; otherwise, it will be compromised.
      Column compression almost has no impact on INSTANT DDL statements.
      For DML statements, in an 8-column table with the most common compression ratio of 1:1.8 (where the length of the inserted data varies randomly from 1 to 6,000, the inserted characters are random within 0–9 and a–b, a column contains a large volume of varchar data, and the data types of other columns are either char(60) or int), the performance of insertion, deletion, and query of non-compressed columns in this table is improved by below 10%, but the performance of update of non-compressed and compressed columns is reduced by below 10% and 15% respectively. This is because that TencentDB for MySQL first reads the value of a row and then writes the updated value, which triggers one decompression/compression process, while insertion and query only trigger one compression or decompression.

      Notes

      1. During logic export, CREATE TABLE statements will carry the COMPRESSED keyword. Therefore, TencentDB for MySQL supports such statements during import. Below are notes on official MySQL versions:
      If the official MySQL version is below 5.7.18, data can be imported directly.
      If the official MySQL version is 5.7.18 or above, the COMPRESSED keyword must be removed after logic export.
      2. When DTS exports data from other cloud service providers or users, incompatibility may occur during binlog sync. In this case, you can skip DDL statements with the COMPRESSED keyword.
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy