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:
jplindst@perro:~/db$ ../10.1/client/mysql -u root -S mysql.sock2Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 2Server version: 10.1.8-MariaDB-debug Source distributionCopyright (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 changedMariaDB [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: 0MariaDB [test]> insert into a select * from a;Query OK, 2 rows affected (0.06 sec)Records: 2 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 4 rows affected (0.05 sec)Records: 4 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 8 rows affected (0.04 sec)Records: 8 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 16 rows affected (0.04 sec)Records: 16 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 32 rows affected (0.06 sec)Records: 32 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 64 rows affected (0.10 sec)Records: 64 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 128 rows affected (0.21 sec)Records: 128 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 256 rows affected (0.32 sec)Records: 256 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 512 rows affected (0.58 sec)Records: 512 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 1024 rows affected (1.35 sec)Records: 1024 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 2048 rows affected (0.34 sec)Records: 2048 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 4096 rows affected (0.60 sec)Records: 4096 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 8192 rows affected (1.77 sec)Records: 8192 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 16384 rows affected (2.24 sec)Records: 16384 Duplicates: 0 Warnings: 0MariaDB [test]> insert into a select * from a;Query OK, 32768 rows affected (3.90 sec)Records: 32768 Duplicates: 0 Warnings: 0MariaDB [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: 0MariaDB [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]> exitByejplindst@perro:~/db$ killall mysqldjplindst@perro:~/db$ lsaria_log.00000001 err.log ib_logfile0 multi-master.info my.cnf~ performance_schemaaria_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 testjplindst@perro:~/db/test$ ls -latotal 40428drwx------ 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.ibdjplindst@perro:~/db/test$ du40424 .jplindst@perro:~/db/test$ du a.ibd27652 a.ibdjplindst@perro:~/db/test$ du b.ibd12760 b.ibd