Сolumns compression

[Duke Williams]

I propose to introduce columns compression with index syntax:

Table example:
CREATE TABLE `online` (
  `sid` varchar(32) NOT NULL,
  `uid` int(4) NOT NULL default '0',
  `xstas` tinyint(2) NOT NULL default '0',
  `name` varchar(20) NOT NULL,
  `time` int(12) NOT NULL,
  `ip` varchar(15) default NULL,
  `fields` BLOB NOT NULL,
  `text` TEXT NOT NULL,
  PRIMARY KEY USING BTREE (`sid`),
  KEY `uid` USING BTREE (`uid`,`xstas`),
  KEY `time` USING BTREE (`time`),
  COMRESSOR `data` USING LZO (`fields`,`text`) LEVEL 9
) ENGINE=MEMORY;

This universal realization of compression will be usefull for tables without compression support (such as myisam, memory, ...) and may be more effecient for innodb table, because this compression lets compress group of columns as one element...

Create syntax example:
CREATE COMPRESSOR compressor_name
    [compressor_type]
    ON tbl_name (col_name,...)
    [compressor_option] ...

col_name:
    col_name

compressor_type:
    USING {ZLIB | LZO | LZ4 | ZSTD | LZMA ...}

compressor_option:
    LEVEL compression_level
  | COMMENT 'string'

Example:
CREATE TABLE `lookup` (`id` INT, `name` TEXT, `text` TEXT) ENGINE = MEMORY;
CREATE INDEX `id_index` ON `lookup` (`id`) USING BTREE;
CREATE COMPRESSOR `data_compressor` ON `lookup` (`name`,`text`) USING ZLIB LEVEL 9;

[Laurynas Biveinis (laurynas-biveinis)]

Please take a look at https://blueprints.launchpad.net/percona-server/+spec/change-compression, we are likely to implement that. But it does not include support for column groups.

[Rafal (andruwn)]

This implementation is better, it can effectively compress a group of INTs. https://blueprints.launchpad.net/percona-server/+spec/change-compression can't compress INTs at all.
As for innodb, according to this post: https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/27169-the-table-size-between-innodb-and-myisam-engines-differs-in-7-times?p=31573#post31573 compressed XtraDB table in 2 times bigger than uncompressed MyISAM. Quite possible columns compression will solve this problem...

[Laurynas Biveinis (laurynas-biveinis)]

Have you looked into TokuDB compression?

[Rafal (andruwn)]

laurynas, yes. Also archive engine compress very good )

[Dima (fghs2013)]

Is https://blueprints.launchpad.net/percona-server/+spec/change-compression implemented in percona server 5.6 from repository?

[Laurynas Biveinis (laurynas-biveinis)]

Dima, no, it's not.

[ivantosov (ivantosov)]

Looks very interesting, but why it's not implemented yet?

[anton (anton-sons)]

> without compression support (such as myisam, memory, ...)
You can use ROW_FORMAT=COMPRESSED to create compressed myisam and memory tables:

CREATE TABLE `test` (
  `id` tinyint(4) DEFAULT NULL
) ENGINE=MEMORY ROW_FORMAT=COMPRESSED;

It is created without any problems. Also, as I know, big temporary memory tables are always compressed.

[Laurynas Biveinis (laurynas-biveinis)]

A version of this has been implemented in PS 5.6/5.7 column compression feature: https://www.percona.com/doc/percona-server/5.6/flexibility/compressed_columns.html

[Laurynas Biveinis (laurynas-biveinis)]

If the currently-implemented feature is missing something you'd like to have, please file new separate bug reports. Thanks!

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3319