Hi Jan,

That’s for those answers.

For #1 I was dropping and recreating all tables  after each change to innodb_compression_algorithm.

Cheers,

Rhys

From: Jan Lindström [mailto:jan.lindstrom@mariadb.com]
Sent: 14 May 2015 06:14
To: Rhys Campbell
Cc: maria-discuss@lists.launchpad.net
Subject: Re: [Maria-discuss] Innodb Page Compression

On Wed, May 13, 2015 at 6:00 PM, Rhys Campbell <Rhys.Campbell@tradingscreen.com> wrote:

1. Why when trim is not used is the db size unchanged apart from lzma?

 SET GLOBAL innodb_compression_algorithm effects only new tables, it does not change existing tables. If you want to change compression algorithm for existing table you need to do:

SET GLOBAL innodb_compression_algorithm = lzo;

CREATE TABLE t1 (a int not null primary key, b char(200)) engine=innodb page_compressed =1; -- uses lzo

SET GLOBAL innodb_compression_algorithm = lz4; -- no change to existing tables

ALTER TABLE t1 ENGINE=InnoDB; -- noe table t1 uses compression algorithm lz4;

2. Why is the compressed size the same for all algorithms? Is the correct algorithm being used?

No

3. Can I confirm which algorithm has been used for a page. I can’t see anything in information_schema

Not really at the moment, I need to think about adding support for this.

4. Status variables / info schema not working?

Status variables work only if pages are really compressed or decompressed see 1)

5. ALTER TABLE … PAGE_COMPRESSED = 1 doesn’t work. Must I always do a full dump & load to enable page compression?

You have incorrect syntax.  ALTER TABLE departments ROW_FORMAT=COMPRESSED means legacy row-compression and if departments table is page compressed you need to specify

ALTER TABLE departments ROW_FORMAT=COMPRESSED PAGE_COMPRESSED=DEFAULT;

6. Lzma/9 compression is not that impressive. Is it meant to rival tokudb? (converting to tokudb results in a db size of ~ 54MB and this includes logs!)

See 1) tokudb might use different version of lzma as they use bundled one.

R: Jan