Compression dictionaries are not being used in partitioned tables

Bug #1653104 reported by Shahriyar Rzayev on 2016-12-29
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Invalid
Undecided
Unassigned
5.6
Fix Released
Medium
Yura Sorokin
5.7
Fix Released
Medium
Yura Sorokin

Bug Description

Hi dear all,
Testing with latest 5.7 branch.

Created table as:

CREATE TABLE `sbtest2` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` varchar(250) DEFAULT NULL,
  `pad` char(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB AUTO_INCREMENT=10001
/*!50100 PARTITION BY RANGE (k)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (10001) ENGINE = InnoDB) */;

mysql [localhost] {msandbox} (dbtest) > insert into sbtest2 select * from sbtest1;
Query OK, 10000 rows affected (1.35 sec)
Records: 10000 Duplicates: 0 Warnings: 0

mysql [localhost] {msandbox} (dbtest) > CREATE COMPRESSION_DICTIONARY numbers ('08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977');
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (dbtest) > alter table sbtest2 modify `c` varchar(250) column_format compressed with compression_dictionary numbers;
Query OK, 10000 rows affected (2.10 sec)
Records: 10000 Duplicates: 0 Warnings: 0

mysql [localhost] {msandbox} (dbtest) > show create table sbtest2\G
*************************** 1. row ***************************
       Table: sbtest2
Create Table: CREATE TABLE `sbtest2` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` varchar(250) /*!50633 COLUMN_FORMAT COMPRESSED */ DEFAULT NULL,
  `pad` char(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (k)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (10001) ENGINE = InnoDB) */
1 row in set (0.00 sec)

Noted the missing dictionary name -> `c` varchar(250) /*!50633 COLUMN_FORMAT COMPRESSED */ DEFAULT NULL,

With ordinary table:

mysql [localhost] {msandbox} (dbtest) > alter table sbtest1 modify `c` varchar(250) column_format compressed with compression_dictionary numbers;
Query OK, 10000 rows affected (2.45 sec)
Records: 10000 Duplicates: 0 Warnings: 0

mysql [localhost] {msandbox} (dbtest) > show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` varchar(250) /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `numbers` */ DEFAULT NULL,
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.00 sec)

tags: added: qa
Changed in percona-server:
assignee: nobody → Yura Sorokin (yura-sorokin)
Roel Van de Paar (roel11) wrote :

So it looks like "WITH COMPRESSION_DICTIONARY `numbers`" is not being used/dropped for partitioned tables.

Yura Sorokin (yura-sorokin) wrote :

A simplified test case to reproduce the problem

CREATE COMPRESSION_DICTIONARY numbers ('one' 'two' 'three');

CREATE TABLE t1 (
  id BIGINT UNSIGNED NOT NULL,
  a VARCHAR(250) COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY numbers DEFAULT NULL
) ENGINE=InnoDB
PARTITION BY RANGE (id) (
 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);
SHOW CREATE TABLE t1;

summary: - Missing compression dictionary name with partitioned table
+ Compression dictionaries are not being used in partitioned tables
Yura Sorokin (yura-sorokin) wrote :

Although, upon creating a partitioned table, references to compression dictionaries are successfully added to SYS_ZIP_DICT_COLS, dictionary data is never used for compression/decompression.

So, for 5.6.35 / 5.7.17 specifying 'WITH COMPRESSION_DICTIONARY ...' in field definitions of partitioned tables does not have any effect.

This happens because 'ha_partition' does not have a overloaded 'update_field_defs_with_zip_dict_info()' which would redirect to 'update_field_defs_with_zip_dict_info()' of one of its handlers.
Moreover, 'ha_innobase::update_field_defs_with_zip_dict_info()' does not handle the case when table name includes partitioning part ('#p#') and always uses 'table_share->normalized_path' (which always looks like './test/t1' regardless of for which partition it is called).

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers