jplindst@perro:~/db$ ../10.1/client/mysql -u root -S mysql.sock2
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.8-MariaDB-debug Source distribution
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table a(c int not null default 0,t char(255) not null default '') engine=innodb;insert into a VALUES(912312,'asdljkashdkhqgerhwvqefasqeldkjasdljadkljasdasdd');
Query OK, 0 rows affected (0.91 sec)
Query OK, 1 row affected (0.07 sec)
MariaDB [test]> insert into a select * from a;
Query OK, 1 row affected (0.70 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 8 rows affected (0.04 sec)
Records: 8 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 16 rows affected (0.04 sec)
Records: 16 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 32 rows affected (0.06 sec)
Records: 32 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 64 rows affected (0.10 sec)
Records: 64 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 128 rows affected (0.21 sec)
Records: 128 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 256 rows affected (0.32 sec)
Records: 256 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 512 rows affected (0.58 sec)
Records: 512 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 1024 rows affected (1.35 sec)
Records: 1024 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 2048 rows affected (0.34 sec)
Records: 2048 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 4096 rows affected (0.60 sec)
Records: 4096 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 8192 rows affected (1.77 sec)
Records: 8192 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 16384 rows affected (2.24 sec)
Records: 16384 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into a select * from a;
Query OK, 32768 rows affected (3.90 sec)
Records: 32768 Duplicates: 0 Warnings: 0
MariaDB [test]> create table b(c int not null default 0,t char(255) not null default '') page_compressed=1 engine=innodb;
Query OK, 0 rows affected (0.31 sec)
MariaDB [test]> insert into b select * from a;
Query OK, 65536 rows affected (13.49 sec)
Records: 65536 Duplicates: 0 Warnings: 0
MariaDB [test]> show status;
+--------------------------------------------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------------------------------------------+----------------------------------------+
<cut>
| Innodb_page_compression_saved | 14766080 |
| Innodb_page_compression_trim_sect512 | 0 |
| Innodb_page_compression_trim_sect1024 | 0 |
| Innodb_page_compression_trim_sect2048 | 0 |
| Innodb_page_compression_trim_sect4096 | 3605 |
| Innodb_page_compression_trim_sect8192 | 0 |
| Innodb_page_compression_trim_sect16384 | 0 |
| Innodb_page_compression_trim_sect32768 | 0 |
| Innodb_num_index_pages_written | 1280 |
| Innodb_num_non_index_pages_written | 1483 |
| Innodb_num_pages_page_compressed | 1203 |
| Innodb_num_page_compressed_trim_op | 1203 |
| Innodb_num_page_compressed_trim_op_saved | 0 |
| Innodb_num_pages_page_decompressed | 1203 |
| Innodb_num_pages_page_compression_error | 0 |
<cut>
+--------------------------------------------------------------+----------------------------------------+
515 rows in set (0.01 sec)
MariaDB [test]> exit
Bye
jplindst@perro:~/db$ killall mysqld
jplindst@perro:~/db$ ls
aria_log.00000001 err.log ib_logfile0 multi-master.info my.cnf~ performance_schema
aria_log_control ibdata1 ib_logfile1 my.cnf mysql test
[1]+ Done 10.1/sql/mysqld --defaults-file=/home/jplindst/db/my.cnf (wd: ~)
(wd now: ~/db)
jplindst@perro:~/db$ cd test
jplindst@perro:~/db/test$ ls -la
total 40428
drwx------ 2 jplindst my 4096 Oct 6 09:53 .
drwxr-xr-x 5 jplindst my 4096 Oct 6 09:55 ..
-rw-rw---- 1 jplindst my 706 Oct 6 09:52 a.frm
-rw-rw---- 1 jplindst my 28311552 Oct 6 09:53 a.ibd
-rw-rw---- 1 jplindst my 730 Oct 6 09:53 b.frm
-rw-rw---- 1 jplindst my 28311552 Oct 6 09:54 b.ibd
jplindst@perro:~/db/test$ du
40424 .
jplindst@perro:~/db/test$ du a.ibd
27652 a.ibd
jplindst@perro:~/db/test$ du b.ibd
12760 b.ibd
Bug
Critical
After two bug fixes I see page compressed table to use less actual disk space: