[MDEV-13198] ALTER TABLE `text` ENGINE = InnoDB; does not free unused space Created: 2017-06-28  Updated: 2017-07-03  Resolved: 2017-07-03

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2.6
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Frank Sagurna Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: None
Environment:

Ubuntu 16.04 packages from mariadb repository


 Description   

In mariadb 10.1.23 running following command did reclaim unused space (defragment by rebuild) in ibd file (one ibd per table), in 10.2.6 it does not.

ALTER TABLE `text` ENGINE = InnoDB;

10.1.23:

meme@fook:~$ sudo ls -hal /var/lib/mysql/wikidb/ |grep text
 
-rw-rw----  1 mysql mysql  998 Jun 28 11:22 text.frm
 
-rw-rw----  1 mysql mysql  15M Jun 28 11:22 text.ibd

10.2.6:

meme@lunkwill:~$ sudo ls -hal /var/lib/mysql/wikidb/ |grep text
 
-rw-rw----  1 mysql mysql  998 Jun 28 11:23 text.frm
 
-rw-rw----  1 mysql mysql  21M Jun 28 11:23 text.ibd



 Comments   
Comment by Elena Stepanova [ 2017-07-02 ]

Works for me:

MariaDB [test]> CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY, t TEXT) ENGINE=InnoDB;
 
Query OK, 0 rows affected (0.19 sec)
 
 
 
MariaDB [test]> BEGIN;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> INSERT INTO t1 (t) VALUES (REPEAT('a',1024)),(REPEAT('b',1024)),(REPEAT('c',1024)),(REPEAT('d',1024));
 
Query OK, 4 rows affected (0.01 sec)
 
Records: 4  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 4 rows affected (0.08 sec)
 
Records: 4  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 8 rows affected (0.00 sec)
 
Records: 8  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 16 rows affected (0.01 sec)
 
Records: 16  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 32 rows affected (0.09 sec)
 
Records: 32  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 64 rows affected (0.07 sec)
 
Records: 64  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 128 rows affected (0.19 sec)
 
Records: 128  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 256 rows affected (0.38 sec)
 
Records: 256  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 512 rows affected (0.00 sec)
 
Records: 512  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 1024 rows affected (0.02 sec)
 
Records: 1024  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 2048 rows affected (0.05 sec)
 
Records: 2048  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 4096 rows affected (0.09 sec)
 
Records: 4096  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 8192 rows affected (0.61 sec)
 
Records: 8192  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> INSERT INTO t1 (t) SELECT t FROM t1;
 
Query OK, 16384 rows affected (0.28 sec)
 
Records: 16384  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> COMMIT;
 
Query OK, 0 rows affected (0.20 sec)


MariaDB [test]> system ls -l data/test/
 
total 49160
 
-rw-rw---- 1 elenst elenst      951 Jul  3 02:45 t1.frm
 
-rw-rw---- 1 elenst elenst 50331648 Jul  3 02:45 t1.ibd
 
MariaDB [test]> DELETE FROM t1;
 
Query OK, 32768 rows affected (0.15 sec)
 
 
 
MariaDB [test]> system ls -l data/test/
 
total 49160
 
-rw-rw---- 1 elenst elenst      951 Jul  3 02:45 t1.frm
 
-rw-rw---- 1 elenst elenst 50331648 Jul  3 02:45 t1.ibd
 
MariaDB [test]> ALTER TABLE t1 ENGINE=InnoDB;
 
Query OK, 0 rows affected (0.39 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> system ls -l data/test/
 
total 100
 
-rw-rw---- 1 elenst elenst   951 Jul  3 02:45 t1.frm
 
-rw-rw---- 1 elenst elenst 98304 Jul  3 02:45 t1.ibd


MariaDB [test]> select @@version;
 
+----------------+
 
| @@version      |
 
+----------------+
 
| 10.2.6-MariaDB |
 
+----------------+
 
1 row in set (0.00 sec)

Please paste SHOW CREATE TABLE for your table and indicate somehow how much data was removed (how much space should be reclaimed), and how it does not happen.
Please also attach your cnf file(s).
Thanks.

Comment by Frank Sagurna [ 2017-07-03 ]

I tested it again, and it seems like the table size for the same table is just bigger in 10.2.6
Have no conclusion yet, so maybe it is best to close the ticket until i can test more.

Comment by Frank Sagurna [ 2017-07-03 ]

But anyway some more info:
I run two servers with master-master replication. I updated one of them from 10.1.23 to 10.2.6.
Table size on the 10.2.6 is bigger for many tables. But have to look more to see if it really does not reclaim space, as it seems it does if i test this on the 10.2.6

Comment by Elena Stepanova [ 2017-07-03 ]

I'll close it for now, but if you find out it's reproducible after all, you can comment with more information and we will re-open it.

Generated at Thu Feb 08 08:03:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.