How to specify compression for a column family?

[Sergey Petrunia]

Hello,

I've tried to create a column family that is using a different compression
algorithm. It doesn't seem to work, please clarify if I'm doing something
wrong? I'm using a recent fb/mysql-5.6 tree.

My attempt was as follows:

I have added this into my.cnf:

rocksdb_default_cf_options='compression_per_level=kZlibCompression;'

and restarted mysqld.

mysql> select * from information_schema.rocksdb_cf_options where option_type like 'COMPRESSION%' ;
+------------+-----------------------+------------------+
| CF_NAME | OPTION_TYPE | VALUE |
+------------+-----------------------+------------------+
| __system__ | COMPRESSION_TYPE | kNoCompression |
| __system__ | COMPRESSION_PER_LEVEL | kZlibCompression |
| __system__ | COMPRESSION_OPTS | -14:-1:0 |
| default | COMPRESSION_TYPE | kNoCompression |
| default | COMPRESSION_PER_LEVEL | kZlibCompression |
| default | COMPRESSION_OPTS | -14:-1:0 |
+------------+-----------------------+------------------+

Now I created a table:

mysql> create table tcf1 (a int, filler varchar(255), primary key(a) comment 'cf1');

... filled it with data...
... flushed the data to disk:

mysql> set global rocksdb_force_flush_memtable_and_lzero_now=1;

Checked the index number (it's 256):

mysql> select * from information_schema.ROCKSDB_DDL;
+--------------+------------+----------------+--------------+---------------+--------------+------------+-------------------+--------------+-------------+---------+----------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | INDEX_NAME | COLUMN_FAMILY | INDEX_NUMBER | INDEX_TYPE | KV_FORMAT_VERSION | TTL_DURATION | INDEX_FLAGS | CF | AUTO_INCREMENT |
+--------------+------------+----------------+--------------+---------------+--------------+------------+-------------------+--------------+-------------+---------+----------------+
| test | tcf1 | NULL | PRIMARY | 2 | 256 | 1 | 13 | 0 | 0 | cf1 | NULL |
+--------------+------------+----------------+--------------+---------------+--------------+------------+-------------------+--------------+-------------+---------+----------------+

Looked at what files the table resides in:

mysql> select * from information_schema.ROCKSDB_INDEX_FILE_MAP;
+---------------+--------------+------------+----------+-----------+---------------+---------------------+--------------+--------------+----------------------+
| COLUMN_FAMILY | INDEX_NUMBER | SST_NAME | NUM_ROWS | DATA_SIZE | ENTRY_DELETES | ENTRY_SINGLEDELETES | ENTRY_MERGES | ENTRY_OTHERS | DISTINCT_KEYS_PREFIX |
+---------------+--------------+------------+----------+-----------+---------------+---------------------+--------------+--------------+----------------------+
...
| 2 | 256 | 000031.sst | 214567 | 53641750 | 0 | 0 | 0 | 0 | 212750 |
| 2 | 256 | 000030.sst | 261811 | 65452750 | 0 | 0 | 0 | 0 | 263810 |
| 2 | 256 | 000029.sst | 261811 | 65452750 | 0 | 0 | 0 | 0 | 260160 |
| 2 | 256 | 000028.sst | 261811 | 65452750 | 0 | 0 | 0 | 0 | 259050 |
+---------------+--------------+------------+----------+-----------+---------------+---------------------+--------------+--------------+----------------------+

and then looked at the SST files to see what compression is used:

psergey@psergey-desktop:~/dev-git/mysql-5.6-rocksdb-look500$ ./storage/rocksdb/sst_dump --file=mysql-test/var/install.db/.rocksdb/000028.sst --command=check --show_properties
Process mysql-test/var/install.db/.rocksdb/000028.sst
Sst file format: block-based
Table Properties:
------------------------------
# data blocks: 17455
# entries: 261811
...
comparator name: RocksDB_SE_v3.10
merge operator name: nullptr
property collectors names: [Rdb_tbl_prop_coll_factory]
SST file compression algo: Snappy


So it is using Snappy compression, and that repeats for all files.

When I select from CF options, I get this:

mysql> select * from information_schema.rocksdb_cf_options where option_type like 'COMPRESSION%' ;
+------------+-----------------------+------------------+
| CF_NAME | OPTION_TYPE | VALUE |
+------------+-----------------------+------------------+
...
| cf1 | COMPRESSION_TYPE | kNoCompression |
| cf1 | COMPRESSION_PER_LEVEL | kZlibCompression |
| cf1 | COMPRESSION_OPTS | -14:-1:0 |
+------------+-----------------------+------------------+

Am I doing something wrong? Why is it using Snappy compression when I specified
Zlib?

(Also, Is there any reason that it's not possible to set compression_type?
I see other emails in this list recommending to set compression_per_level
parameter, not compression_type)

I've also looked into mysqld's stderr output and didn't find anything that
would give clue.

BR
Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

[Sergey Petrunia]

On Fri, Nov 03, 2017 at 01:34:04PM +0300, Sergey Petrunia wrote:
> I've tried to create a column family that is using a different compression
> algorithm. It doesn't seem to work, please clarify if I'm doing something
> wrong? I'm using a recent fb/mysql-5.6 tree.
>
> My attempt was as follows:
>
> I have added this into my.cnf:
>
> rocksdb_default_cf_options='compression_per_level=kZlibCompression;'

I've made another attempt. I started from an empty datadir with my.cnf having:

loose-rocksdb-default-cf-options="compression_per_level=kZlibCompression;"

then I do get

SST file compression algo: Zlib

for the SST files that were created for the data in the default column family.

However if I on the same server attempt to create another column family:

create table tcf1 (a int, filler varchar(255), primary key(a) comment 'cf1');

... insert some data there ...
set global rocksdb_force_flush_memtable_and_lzero_now=1;
select * from information_schema.ROCKSDB_DDL;
select * from information_schema.ROCKSDB_INDEX_FILE_MAP;
sst_dump --file=000908.sst --command=check --show_properties
...

SST file compression algo: Snappy

...

I still get Snappy compression.

rocksdb_cf_options looks the same for both the default and cf1:

select * from information_schema.rocksdb_cf_options
where option_type like 'COMPRESSION%' ;

+------------+-----------------------+--------------------+
| CF_NAME | OPTION_TYPE | VALUE |
+------------+-----------------------+--------------------+
| __system__ | COMPRESSION_TYPE | kSnappyCompression |

| __system__ | COMPRESSION_PER_LEVEL | kZlibCompression |
| __system__ | COMPRESSION_OPTS | -14:-1:0 |

| cf1 | COMPRESSION_TYPE | kSnappyCompression |

| cf1 | COMPRESSION_PER_LEVEL | kZlibCompression |
| cf1 | COMPRESSION_OPTS | -14:-1:0 |

| default | COMPRESSION_TYPE | kSnappyCompression |

| default | COMPRESSION_PER_LEVEL | kZlibCompression |
| default | COMPRESSION_OPTS | -14:-1:0 |

+------------+-----------------------+--------------------+

I am still confused.

[MARK CALLAGHAN]

On Fri, Nov 3, 2017 at 3:34 AM, Sergey Petrunia <ser...@mariadb.com> wrote:

Hello,

I've tried to create a column family that is using a different compression
algorithm. It doesn't seem to work, please clarify if I'm doing something
wrong? I'm using a recent fb/mysql-5.6 tree.

My attempt was as follows:

Now I created a table:

mysql> create table tcf1 (a int,  filler varchar(255), primary key(a) comment 'cf1');

Is the comment in the right place? You have it between the parens, for linkbench I have it outside them...

      PRIMARY KEY (link_type, `id1`,`id2`) COMMENT 'cf_link_pk',

      KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`id2`,`version`,`data`) COMMENT 'rev:cf_link_id1_type' 

Mark Callaghan

mdca...@gmail.com