commit f7002c05ae4e4a09bc6859ccc568064cfd6bb268
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Wed Jun 3 13:10:18 2015 +0300

MDEV-8250: InnoDB: Page compressed tables are not compressed and compressed+encrypted tables cause crash

Analysis: Problem is that both encrypted tables and compressed tables use
FIL header offset FIL_PAGE_FILE_FLUSH_LSN_OR_KEY_VERSION to store
required metadata. Furhermore, for only compressed tables currently
code skips compression.

Fixes:

• Only encrypted pages store key_version to FIL header offset FIL_PAGE_FILE_FLUSH_LSN_OR_KEY_VERSION,
  no need to fix
• Only compressed pages store compression algorithm to FIL header offset FIL_PAGE_FILE_FLUSH_LSN_OR_KEY_VERSION,
  no need to fix as they have different page type FIL_PAGE_PAGE_COMPRESSED
• Compressed and encrypted pages now use a new page type FIL_PAGE_PAGE_COMPRESSED_ENCRYPTED and
  key_version is stored on FIL header offset FIL_PAGE_FILE_FLUSH_LSN_OR_KEY_VERSION and compression
  method is stored after FIL header similar way as compressed size, so that first
  FIL_PAGE_COMPRESSED_SIZE is stored followed by FIL_PAGE_COMPRESSION_METHOD
• Fix buf_page_encrypt_before_write function to really compress pages if compression is enabled
• Fix buf_page_decrypt_after_read function to really decompress pages if compression is used
• Small style fixes

I am still not seeing any difference in on-disk file size for compressed/uncompressed files. This is with statistics being reported as compression having been done when a compressed table is compressed.

I tried with stock 10.1.7 from the repository, as well as a freshly compiled 10.1.7 & 10.1.6 with lz4 compression with the same results. Also this was done with a completely fresh set of db files for each version.

Relevant config from my.cnf :

{{## Page compression setup:
innodb_use_fallocate=ON
innodb_file_format=barracuda
innodb_compression_level=6
innodb_compression_algorithm=lz4

[mariadb]
innodb_mtflush_threads=4
innodb_use_mtflush=1}}

Reproduction steps :

• After installation from source, create and start new server with new test db :
  {{rm -Rf /var/lib/mysql && cd /usr/local/mysql && /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/
  cd /usr/local/mysql && /usr/local/mysql/bin/mysqld_safe --datadir=/var/lib/mysql
  mysqladmin -uroot create t}}

• Create some data :
  {{create table a(c int not null default 0,t char(255) not null default '') engine=innodb;insert into a VALUES(912312,'asdljkashdkhqgerhwvqefasqeldkjasdljadkljasdasdd');
  MariaDB [t]> insert into a select * from a;
  ...
  repeat
  ...
  MariaDB [t]> insert into a select * from a;
  Query OK, 32768 rows affected (0.14 sec)
  Records: 32768 Duplicates: 0 Warnings: 0}}

• Insert into page compressed table :
  create table b(c int not null default 0,t char(255) not null default '') page_compressed=1 engine=innodb;insert into b select * from a;

• DB status says compression took place and saved quite a bit of data :
  {{# mysql -uroot -e "show status" | egrep 'Innodb_page_compression_saved|Innodb_num_pages_page_compressed'
  Innodb_page_compression_saved 15220736
  Innodb_num_pages_page_compressed 1240}}

• Look at files on filesystem though, and storage is identical :
  {{# ls -la /var/lib/mysql/t/*.ibd
  rw-rw--- 1 mysql mysql 28311552 Oct 5 23:45 /var/lib/mysql/t/a.ibd
  rw-rw--- 1 mysql mysql 28311552 Oct 5 23:45 /var/lib/mysql/t/b.ibd}}

Probably should add : This is with Ubuntu 14.04, ext4 on an SSD. Also just to confirm, all the compression methods are available (have tried zlib & lz4 with default compression level):

{{# mysql -uroot -e "show status" | grep Innodb_have
Innodb_have_atomic_builtins ON
Innodb_have_lz4 ON
Innodb_have_lzo ON
Innodb_have_lzma ON
Innodb_have_bzip2 ON
Innodb_have_snappy ON}}

OK, last bit of info after some more tests :

• For the record a 'du' still shows the file sizes as identical, ie the file for the compressed table isn't sparse. I realise now the directory listing I posted above wasn't a good indication of success.

• For comparison I have installed and run the same test with Mysql 5.7 with page compressions, and can see the file sizes reported the same by ls, but they are taking up less space when looking at them with du (about half the size)
  .
• Could this problem be because 'innodb_use_trim' is disabled? If I emable it the server crashes and also seems to corrupt the db, sample info for this below :

{{Server version: 10.1.7-MariaDB-1~trusty
key_buffer_size=1073741824
read_buffer_size=2097152
max_used_connections=0
max_threads=102
thread_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1677325 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x0 thread_stack 0x48000
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7f3cbb9233de]
/usr/sbin/mysqld(handle_fatal_signal+0x38d)[0x7f3cbb4559ad]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f3cb99da340]
/usr/sbin/mysqld(+0x82d8c4)[0x7f3cbb7008c4]
/usr/sbin/mysqld(+0x82de0c)[0x7f3cbb700e0c]
/usr/sbin/mysqld(+0x97123a)[0x7f3cbb84423a]
/usr/sbin/mysqld(+0x8a3da0)[0x7f3cbb776da0]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f3cb99d2182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f3cb90f547d]}}

Hi,

Yes, innodb-use-trim is currently required to create parse files. I created MDEV-8900 to enable trim by default if operating system and file system does support it by default. I will also see if I can repeat the reported crash.

R: Jan

Crash repeated, this is tracked on MDEV-8901, problem is that trim is also used on log files.