maria-discuss team mailing list archive

[Rhys Campbell <Rhys.Campbell@xxxxxxxxxxxxxxxxx>]

Hi Jan,

Just a follow up on my previous email. I understood that simply changing the setting for innodb_compression_algorithm would not change any existing tables. Between each test I was running the employees.sql script from the example database. This contains the following sql at the start of the script….

DROP TABLE IF EXISTS dept_emp,
                                                dept_manager,
                                                titles,
                                                salaries,
                                                employees,
                                                departments;

So I definitely think there’s something up here. I’ve performed a similar set of tests below…

SET GLOBAL innodb_compression_algorithm=none;
source employees.sql
du –sh /var/lib/mysql/employees – 385M <- Note all tables are created here with the PAGE_COMPRESSED=1 option. INNODB_SYS_TABLESPACES shows all are Barrcacuda/Dynamic
Next I run the ALTER TABLE … ROW_FORMAT=COMPRESSED PAGE_COMPRESSED=DEFAULT; for each table in the employees database
du –sh /var/lib/mysql/employees – 135MB; <- Expected 119MB here, probably due to a log file.
SET GLOBAL innodb_compression_algorithm=zlib <- After each compression algorithm change I run alter for each tables as above
du –sh /var/lib/mysql/employees – 119MB
SET GLOBAL innodb_compression_algorithm=lzo <- Alter run
du –sh /var/lib/mysql/employees – 119MB
SET GLOBAL innodb_compression_algorithm=lz4 <- Alter run…
du –sh /var/lib/mysql/employees – 119MB
SET GLOBAL innodb_compression_algorithm=lzma <- alter run
du –sh /var/lib/mysql/employees – 119MB
SET GLOBAL innodb_compression_algorithm=bzip2 < alter run
du –sh /var/lib/mysql/employees – 119MB
SET GLOBAL innodb_compression_algorithm=snappy <- alter run
du –sh /var/lib/mysql/employees – 119MB

SET GLOBAL innodb_use_trim=1; # Now set trim and run above tests again

SET GLOBAL innodb_compression_algorithm=zlib # Again the alter table statements are executed as above
du –sh /var/lib/mysql/employees – 119MB
SET GLOBAL innodb_compression_algorithm=lzo
du –sh /var/lib/mysql/employees – 119MB
SET GLOBAL innodb_compression_algorithm=lz4
du –sh /var/lib/mysql/employees – 119MB
SET GLOBAL innodb_compression_algorithm=lzma
du –sh /var/lib/mysql/employees – 119MB
SET GLOBAL innodb_compression_algorithm=bzip2
du –sh /var/lib/mysql/employees – 119MB
SET GLOBAL innodb_compression_algorithm=snappy
du –sh /var/lib/mysql/employees – 119MB


Recreating the database without the PAGE_COMPRESSED=1 options, then run ALTER TABLE … ROW_FORMAT=COMPRESSED results in a database size of 119MB. When I attempt the compression without ROW_FORMAT=COMPRESSED I get the same results as I previously poste, i.e. no difference in db size apart from when using lzma.

Cheers,

Rhys


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



On Wed, May 13, 2015 at 6:00 PM, Rhys Campbell <Rhys.Campbell@xxxxxxxxxxxxxxxxx<mailto:Rhys.Campbell@xxxxxxxxxxxxxxxxx>> 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