Anti-comrpession

Showing 1-17 of 17 messages
Anti-comrpession Shlomi Noach 5/1/13 10:06 PM
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?
Re: Anti-comrpession Tim Callaghan 5/2/13 7:10 AM
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
Re: Anti-comrpession Tim Callaghan 5/2/13 8:17 AM
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


On Thursday, May 2, 2013 1:06:01 AM UTC-4, Shlomi Noach wrote:
Re: Anti-comrpession Shlomi Noach 5/2/13 10:12 AM
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_
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.

-Tim
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
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.
Re: Anti-comrpession Tim Callaghan 5/3/13 4:53 AM
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
Re: Anti-comrpession Nail Kashapov 6/4/13 7:27 AM
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
Re: Anti-comrpession Mark Callaghan 6/4/13 7:36 AM


--
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
Re: Anti-comrpession Nail Kashapov 6/4/13 10:33 AM
This removes KEY_BLOCK_SIZE from table description:
alter table table_name KEY_BLOCK_SIZE=0;

After that KEY_BLOCK_SIZE appeared in primary key (the only index of the table):

CREATE TABLE `table_name` (
  `obj_id` int(10) unsigned NOT NULL,  `val` text,
  PRIMARY KEY (`obj_id`) KEY_BLOCK_SIZE=4
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

At this step I tried to convert the table to TokuDB compressed format, but the file size was too large again.
I had to recreate primary key:
alter table table_name drop primary key, add primary key (obj_id);

After that I was able to compress the table correctly, and size of the table is 2 times smaller than InnoDB compressed!
Re: Anti-comrpession Shlomi Noach 6/4/13 10:01 PM
@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 ;

?
Re: Anti-comrpession Shlomi Noach 6/4/13 10:05 PM
@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.

Re: Anti-comrpession Shlomi Noach 6/5/13 12:12 AM
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

Re: Anti-comrpession Nail Kashapov 6/5/13 5:18 AM
Schlomi,

Thanks, it's really nice that conversion can be done in 1 step.
Re: Anti-comrpession Shlomi Noach 7/28/13 2:07 AM
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

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