Possible Bug in DATA_FREE Reporting for Partitioned Tables

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hello,

I’ve encountered a potential issue with the reporting of the DATA_FREE value for partitioned tables in MySQL (5.7.42-46).

When I run SHOW TABLE STATUS LIKE 'testtab'; , I get a DATA_FREE value of 47555018752 .

However, when I query the information_schema.PARTITIONS table with the following command:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    PARTITION_NAME,
    DATA_LENGTH,
    DATA_FREE
FROM
    information_schema.PARTITIONS
WHERE
    TABLE_NAME='testtab'
AND DATA_FREE>0;

I get an empty set, indicating that there’s no DATA_FREE space.

But, when I query the information_schema.TABLES table with the following command:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    DATA_LENGTH,
    DATA_FREE
FROM
    information_schema.TABLES
WHERE
    TABLE_NAME='testtab'
AND DATA_FREE>0;

I get a DATA_FREE value of 47555018752, which matches the value from the SHOW TABLE STATUS command.

According to the MySQL documentation, the DATA_FREE column in the PARTITIONS table should show “The number of bytes allocated to the partition or subpartition but not used.” However, it seems like this value is not being properly reported for partitioned tables.

I’ve tried running ANALYZE TABLE to gather fresh statistics, but it didn’t help. It seems like this might be a bug.

Could you please look into this issue and provide some guidance on how to resolve it?

Thank you for your time and assistance.

Best regards,
Marko

2

Hi Marko,

Thanks for reaching us.
MySQL 5.7 reached end of life last October 2023.
Percona is in the process of releasing the last 5.7.44 minor patch at the moment and there won’t be anymore official 5.7 patches.

For 5.7 there is no guarantee of a fix but if you provide a reproducible test case there is the possibility that someone might take a look and check what’s wrong and provide some suggestions.

Are you able to reproduce the same issue with MySQL/Percona Server 8.0?

Thanks in advance

3

Hello Carlos,

In 5.6 version the problem was not evident. Need to test MySQL 5.8 and maybe MariaDB.

As for 5.7 I am sharing reproducible test case.

CREATE TABLE temp_table (
    id INT NOT NULL,
    value INT NOT NULL
)
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (333333),
    PARTITION p1 VALUES LESS THAN (666666),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

DELIMITER //
CREATE PROCEDURE populate_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 1000000 DO
        INSERT INTO temp_table (id, value) VALUES (i, i);
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL populate_data();


mysql> select count(*) from temp_table;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (1.13 sec)


mysql> SELECT
    ->     TABLE_SCHEMA,
    ->     TABLE_NAME,
    ->     PARTITION_NAME,
    ->     DATA_LENGTH,
    ->     DATA_FREE
    -> FROM
    ->     information_schema.PARTITIONS
    -> WHERE
    ->     PARTITION_NAME IS NOT NULL
    -> AND TABLE_NAME='temp_table';
+--------------+------------+----------------+-------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | DATA_LENGTH | DATA_FREE |
+--------------+------------+----------------+-------------+-----------+
| testdb       | temp_table | p0             |    12075008 |         0 |
| testdb       | temp_table | p1             |    12075008 |         0 |
| testdb       | temp_table | p2             |    12075008 |         0 |
+--------------+------------+----------------+-------------+-----------+
3 rows in set (0.44 sec)


mysql> delete from temp_table where id<333333;
Query OK, 333333 rows affected (2.92 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> analyze table temp_table;
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| testdb.temp_table | analyze | status   | OK       |
+-------------------+---------+----------+----------+
1 row in set (0.78 sec)

mysql> SELECT
    ->     TABLE_SCHEMA,
    ->     TABLE_NAME,
    ->     PARTITION_NAME,
    ->     DATA_LENGTH,
    ->     DATA_FREE
    -> FROM
    ->     information_schema.PARTITIONS
    -> WHERE
    ->     PARTITION_NAME IS NOT NULL
    -> AND TABLE_NAME='temp_table';
+--------------+------------+----------------+-------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | DATA_LENGTH | DATA_FREE |
+--------------+------------+----------------+-------------+-----------+
| testdb       | temp_table | p0             |       16384 |         0 |
| testdb       | temp_table | p1             |    12075008 |         0 |
| testdb       | temp_table | p2             |    12075008 |         0 |
+--------------+------------+----------------+-------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->     TABLE_SCHEMA,
    ->     TABLE_NAME,
    ->     DATA_LENGTH,
    ->     DATA_FREE
    -> FROM
    ->     information_schema.TABLES
    -> WHERE TABLE_NAME='temp_table';
+--------------+------------+-------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | DATA_LENGTH | DATA_FREE |
+--------------+------------+-------------+-----------+
| testdb       | temp_table |    24166400 |  24117248 |
+--------------+------------+-------------+-----------+
1 row in set (0.00 sec)


# ls -l /var/lib/mysql/testdb/temp_table*
-rw-r----- 1 mysql mysql 19922944 Nov 24 08:25 /var/lib/mysql/testdb/temp_table#P#p0.ibd
-rw-r----- 1 mysql mysql 19922944 Nov 24 08:21 /var/lib/mysql/testdb/temp_table#P#p1.ibd
-rw-r----- 1 mysql mysql 19922944 Nov 24 08:22 /var/lib/mysql/testdb/temp_table#P#p2.ibd
-rw-r----- 1 mysql mysql     8588 Nov 24 08:19 /var/lib/mysql/testdb/temp_table.frm

mysql> alter table temp_table rebuild partition p0;
Query OK, 0 rows affected (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

# ls -l /var/lib/mysql/testdb/temp_table*
-rw-r----- 1 mysql mysql    98304 Nov 24 08:33 /var/lib/mysql/testdb/temp_table#P#p0.ibd
-rw-r----- 1 mysql mysql 19922944 Nov 24 08:21 /var/lib/mysql/testdb/temp_table#P#p1.ibd
-rw-r----- 1 mysql mysql 19922944 Nov 24 08:22 /var/lib/mysql/testdb/temp_table#P#p2.ibd
-rw-r----- 1 mysql mysql     8588 Nov 24 08:33 /var/lib/mysql/testdb/temp_table.frm

Please let me know if we can do something on 5.7 version to collect DATA_FREE info.

btw
MariaDB 11.1.2 is not affected by the issue and Percona/MySQL 5.6.
If you need verification for MySQL/Percona Server 8.0 please use provided test case.

Thanks

4

Hi again!

Thanks for the provided test case.
I have verified that it also affects 8.0 and I have created the following bug reports:
https://bugs.mysql.com/bug.php?id=113256
https://jira.percona.com/browse/PS-9015

You can add yourself to the “watch list” to follow the status of the bug and potential future fixes.

Regards

5

Hi Carlos,

Thank you for sharing info.

Best regards,
Marko