Re: Anti-comrpession

Showing 1-12 of 12 messages
Re: Anti-comrpession Tim Callaghan 5/2/13 7:10 AM

I've never seen this before, can you please give us a little more information:

1. Attach your my.cnf file
2. Include the output of "show variables like 'tokudb%';"

I'm curious to see what your default compression is set to.


On Thursday, May 2, 2013 1:06:01 AM UTC-4, Shlomi Noach wrote:
I'm evaluating 5.5.30-tokudb-7.0.1-MariaDB-log after having compiled it from source.
I've attempted ALTERing one table from InnoDB to TokuDB, to have a 4 times increase in volume on disk.

Original table:

  `n` varchar(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `s` varchar(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `m` text NOT NULL,
  `l` tinyint(11) NOT NULL DEFAULT '0',
  `a` char(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `appSignature` (`a`),
  KEY `n` (`n`,`t`),
  KEY `s` (`s`,`t`)

This takes at about 4.7GB on disk; it is in COMPRESSED format. Uncompressed volume is at about 9-10GB

After ALTERing to TokuDB:

mysql> alter table logs engine=tokudb;
Query OK, 66325471 rows affected (7 hours 39 min 20.78 sec)
Records: 66325471  Duplicates: 0  Warnings: 0

Altering took more than 7 hours, to the result of:

-rwxrwx--x 1 mysql mysql  5513439744 2013-04-30 23:55 _myschema_sql_2194_4_key_appSignature_b_1_18_B_1.tokudb
-rwxrwx--x 1 mysql mysql  5483389440 2013-05-01 01:13 _myschema_sql_2194_4_key_name_b_1_18_B_2.tokudb
-rwxrwx--x 1 mysql mysql  5713266688 2013-05-01 02:33 _myschema_sql_2194_4_key_source_b_1_18_B_3.tokudb
-rwxrwx--x 1 mysql mysql 15438368256 2013-04-30 22:38 _myschema_sql_2194_4_main_b_1_18_B_0.tokudb
-rwxrwx--x 1 mysql mysql       65536 2013-04-30 19:08 _myschema_sql_2194_4_status_6_1_18.tokudb

This is after verifying:

mysql> select * from information_Schema.TokuDB_file_map;
| dictionary_name                  | internal_file_name                                        |
| ./myschema/logs-key-appSignature | ./_myschema_sql_2194_4_key_appSignature_b_1_18_B_1.tokudb |
| ./myschema/logs-key-name         | ./_myschema_sql_2194_4_key_name_b_1_18_B_2.tokudb         |
| ./myschema/logs-key-source       | ./_myschema_sql_2194_4_key_source_b_1_18_B_3.tokudb       |
| ./myschema/logs-main             | ./_myschema_sql_2194_4_main_b_1_18_B_0.tokudb             |
| ./myschema/logs-status           | ./_myschema_sql_2194_4_status_6_1_18.tokudb               |
| tokudb_meta                      | ./tokudb_meta_3_0_18.tokudb                               |

And so TokuDB table amounts to ~17GB on disk.

Makes sense in any way?
Re: Anti-comrpession Tim Callaghan 5/2/13 8:17 AM

On further investigation we now see what the issue is.  You are converting a compressed InnoDB table, with key_block_size=4.  In creating the TokuDB version of this table our code is picking up that 4K and using it as the node size for the TokuDB table.  TokuDB node size defaults to 4M (much larger than 4K), and can be overriden by setting tokudb_block_size.

I've added a bug ticket to get this fixed.  For now to see the benefit of TokuDB compression you'll need to either alter an uncompressed InnoDB table or load directly into the TokuDB table the way you loaded the InnoDB table.

Please let us know your findings.
Re: Anti-comrpession Tim Callaghan 5/2/13 10:24 AM
I'm not sure what will happen, can you try it and let me know?

The solution is that we'll be modifying our alter table code to strip out this option, I'm not sure there are any other work-arounds at the moment.


On Thursday, May 2, 2013 1:12:13 PM UTC-4, Shlomi Noach wrote:
Hi Tim,

This makes sense. Would you know if the following would make for a working hack:
alter table logs engine=tokudb key_block_size=4096;
Would it compute 4096 as 4096K = 4M?

Reason I'm looking for a workaround is this: InnoDB has this notorious of preserving the KEY_BLOCK_SIZE option. Even if I modify my InnoDB table to ROW_FORMAT=COMPACT, the KEY_BLOCK_SIZE info is still there. So when I next convert it to TokuDB, it's still there... No way to get rid of it.


ALTER TABLE my_innodb_compressed_table ENGINE=TokuDB TOKUDB_
Re: Anti-comrpession Shlomi Noach 5/2/13 10:49 AM
Will try next.
Re: Anti-comrpession Shlomi Noach 5/3/13 4:37 AM

The trick with
did not do what I was hoping for: still got very long ALTER time and a larger TokuDB table than original InnoDB.

So either dumping everything out, or creating a new table of TokuDB engine, copying everything there, swapping the new table instead of the old InnoDB one and dropping the old one. I'm trying just that now. Another advantage of this is that the files on disk are named after the table and not after MySQL's own temporary naming.
Re: Anti-comrpession Tim Callaghan 5/3/13 4:53 AM

Thanks for trying, as we fix this issue we'll be looking for any other non-TokuDB attributes that we need to eliminate as part of the alter table operation.

Re: Anti-comrpession Mark Callaghan 6/4/13 7:36 AM

On Tue, Jun 4, 2013 at 7:27 AM, Nail Kashapov <> wrote:
Hi guys,

Is there a workaround for this issue? I'm thinking about how to convert tables on production without recreating them.
Is it possible to remove KEY_BLOCK_SIZE from the table description? It's still there when I change to ROW_FORMAT=COMPACT.

You received this message because you are subscribed to the Google Groups "tokudb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To post to this group, send email to
Visit this group at
For more options, visit

Mark Callaghan
Re: Anti-comrpession Shlomi Noach 6/4/13 10:01 PM

Did you happen to try it with a single command?

alter table table_namedrop primary key, add primary key (obj_id), key_block_size=0 ;

Re: Anti-comrpession Shlomi Noach 6/4/13 10:05 PM

The bug helps in knowing other share the same trouble; but going to ROW_FORMAT=DEFAULT does not really help since we really want something like ROW_FORMAT=TOKUDB_LZMA, and the problem arises with the KEY_BLOCK_SIZE. I will try suggested KEY_BLOCK_SIZE=0 and report.

Re: Anti-comrpession Shlomi Noach 6/5/13 12:12 AM
doing it all in one command works just well. So, DROP all KEYs, recreate them, ENGINE=TokuDB, KEY_BLOCK_SIZE=0, all in one ALTER.
See some experiments here:

Re: Anti-comrpession Shlomi Noach 7/28/13 2:07 AM

Checking on the latest 7.0.3 release I don't see this as fixed. Nor can I find the bug reported in GitHub's Issues. Can you please update on this?


Re: Anti-comrpession Rich Prohaska 7/29/13 12:12 PM
Github issues #58 and #62 now refer to this bug.