[MDEV-9820] global option to enable page compression for all new tables Created: 2016-03-28  Updated: 2017-02-09  Resolved: 2017-02-06

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: 10.2.3

Type: Task Priority: Major
Reporter: Thomas Maerz Assignee: Rasmus Johansson (Inactive)
Resolution: Fixed Votes: 3
Labels: 10.2-rc, compression

Issue Links:
Blocks
is blocked by MDEV-6113 merge 5.7 innodb Closed

 Description   

Page compression is transparent for the client accessing the database, but for most existing applications which create tables by themselves, it is inconvenient to implement compression because you must specify whether each table should be compressed at creation. Since an existing application might not support this function and it may create new tables as it runs, it would not use compression at all.

If the application is written in-house or open source, you could modify the source code to enable compression when creating new tables, but for closed source applications which use MariaDB/MySQL, it is impossible.

I propose that a global boot variable is created that can be configured in server.cnf to set the default value for all new tables created to compression=1, so that all tables created on the database server can utilize whatever compression the system is configured for.

 Comments   
Comment by Sergei Golubchik [ 2016-10-09 ]

Yes, we could easily add a variable that sets a default value for InnoDB's PAGE_COMPRESSED table attribute.

Comment by Igor Pashev [ 2016-10-23 ]

What name would you suggest for such an option? innodb_default_page_compression = ON / OFF ?
How SHOW CREATE TABLE should handle per table options (print page_compression unconditionally, print if not == default, never print)?

Comment by Sergei Golubchik [ 2016-10-23 ]

The name? Either innodb_default_page_compressed or innodb_default_page_compression look good, yes.
SHOW CREATE TABLE should do what it does for all other HA_TOPTION_SYSVAR table options: print if not == default.

https://mariadb.com/kb/en/mariadb/engine-defined-new-tablefieldindex-attributes/

Comment by Alexander Kuleshov [ 2016-11-07 ]

Hello,

Today I've tried to build last 10.2 and getting following error:

~/dev/server (10.2) $ make
 
...
 
...
 
...
 
[ 29%] Building CXX object storage/xtradb/CMakeFiles/xtradb.dir/handler/ha_innodb.cc.o
 
In file included from /home/alex/dev/server/sql/log.h:20:0,
 
                 from /home/alex/dev/server/sql/sql_class.h:28,
 
                 from /home/alex/dev/server/sql/sql_acl.h:21,
 
                 from /home/alex/dev/server/storage/xtradb/handler/ha_innodb.cc:41:
 
/home/alex/dev/server/storage/xtradb/handler/ha_innodb.cc:645:55: error: ‘compression_default’ was not declared in this scope
 
   HA_TOPTION_BOOL("PAGE_COMPRESSED", page_compressed, compression_default),
 
                                                       ^
 
/home/alex/dev/server/sql/handler.h:868:29: note: in definition of macro ‘HA_xOPTION_BOOL’
 
     offsetof(struc, field), def, 0, 1, 0, 0, 0 }
 
                             ^~~
 
/home/alex/dev/server/storage/xtradb/handler/ha_innodb.cc:645:3: note: in expansion of macro ‘HA_TOPTION_BOOL’
 
   HA_TOPTION_BOOL("PAGE_COMPRESSED", page_compressed, compression_default),
 
   ^~~~~~~~~~~~~~~
 
storage/xtradb/CMakeFiles/xtradb.dir/build.make:1190: recipe for target 'storage/xtradb/CMakeFiles/xtradb.dir/handler/ha_innodb.cc.o' failed
 
make[2]: *** [storage/xtradb/CMakeFiles/xtradb.dir/handler/ha_innodb.cc.o] Error 1
 
CMakeFiles/Makefile2:6974: recipe for target 'storage/xtradb/CMakeFiles/xtradb.dir/all' failed
 
make[1]: *** [storage/xtradb/CMakeFiles/xtradb.dir/all] Error 2
 
Makefile:160: recipe for target 'all' failed
 
make: *** [all] Error 2

After: https://github.com/MariaDB/server/pull/255 PR.

Comment by Elena Stepanova [ 2017-01-24 ]

Do we still need it?

Comment by Igor Pashev [ 2017-01-24 ]

I do. I'd like this feature on our dump-processing machine to reduce I/O.

Comment by Rasmus Johansson (Inactive) [ 2017-02-06 ]

Added the innodb_compression_default variable (boolean).

https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_compression_default

Comment by Igor Pashev [ 2017-02-07 ]

Comment by Igor Pashev [ 2017-02-09 ]

Does it affect migration from previous versions if I enable compression by default?

I got this error ( this is not a production server, no one is fired ):

[Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
 
[ERROR] InnoDB: Header page consists of zero bytes in datafile: ./ibdata1, Space ID:0, Flags: 0. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troub
 
[ERROR] InnoDB: Corrupted page [page id: space=0, page number=0] of datafile './ibdata1' could not be found in the doublewrite buffer.
 
[ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption

Previous version was 10.1.21 with lz4 compression (global default) and compression enabled per table, (all tables in all databases, except mysql of course).
New version is 10.2.3 with compression turned on globally and default algorithm lzma.

Comment by Igor Pashev [ 2017-02-09 ]

Uh.. with clean setup, and global lz4 compression mysqld eats all the RAM... and swap

Comment by Igor Pashev [ 2017-02-09 ]

That's unrelated to the innodb_compression_default option. 10.2.3 just runs out of memory.

Generated at Thu Feb 08 07:37:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.