Re: Anti-comrpession

343 views
Skip to first unread message

Tim Callaghan

unread,
May 2, 2013, 10:10:37 AM5/2/13
to tokud...@googlegroups.com
Schlomi,

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.

-Tim


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:

CREATE TABLE `logs` (
  `n` varchar(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `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`)
) ENGINE=InnoDB AUTO_INCREMENT=92055455 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
;


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?

Tim Callaghan

unread,
May 2, 2013, 11:17:32 AM5/2/13
to tokud...@googlegroups.com
Shlomi,

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.

Tim Callaghan

unread,
May 2, 2013, 1:24:28 PM5/2/13
to tokud...@googlegroups.com
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.

-Tim


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.

Thanks,
Shlomi



ALTER TABLE my_innodb_compressed_table ENGINE=TokuDB TOKUDB_

Shlomi Noach

unread,
May 2, 2013, 1:49:57 PM5/2/13
to tokud...@googlegroups.com
Will try next.

Shlomi Noach

unread,
May 3, 2013, 7:37:17 AM5/3/13
to tokud...@googlegroups.com
Tim,

The trick with
ALTER TABLE ENGINE=TokuDB KEY_BLOCK_SIZE=4096
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.

Tim Callaghan

unread,
May 3, 2013, 7:53:56 AM5/3/13
to tokud...@googlegroups.com
Shlomi,

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.

-Tim

MARK CALLAGHAN

unread,
Jun 4, 2013, 10:36:49 AM6/4/13
to Nail Kashapov, tokud...@googlegroups.com


On Tue, Jun 4, 2013 at 7:27 AM, Nail Kashapov <nail.k...@gmail.com> 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.

Thanks,
Nail
--
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 tokudb-user...@googlegroups.com.
To post to this group, send email to tokud...@googlegroups.com.
Visit this group at http://groups.google.com/group/tokudb-user?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 



--
Mark Callaghan
mdca...@gmail.com

Shlomi Noach

unread,
Jun 5, 2013, 1:01:08 AM6/5/13
to tokud...@googlegroups.com, Nail Kashapov
@Nail,

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 ;

?

Shlomi Noach

unread,
Jun 5, 2013, 1:05:16 AM6/5/13
to tokud...@googlegroups.com, Nail Kashapov
@Mark,

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.

Shlomi Noach

unread,
Jun 5, 2013, 3:12:10 AM6/5/13
to tokud...@googlegroups.com, Nail Kashapov
FYI,
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:
http://code.openark.org/blog/mysql/converting-compressed-innodb-tables-to-tokudb-7-0-1

Shlomi Noach

unread,
Jul 28, 2013, 5:07:16 AM7/28/13
to tokud...@googlegroups.com
Hi,

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?

Thanks

Rich Prohaska

unread,
Jul 29, 2013, 3:12:03 PM7/29/13
to tokud...@googlegroups.com
Github issues #58 and #62 now refer to this bug.  
Reply all
Reply to author
Forward
0 new messages