Bug #61927 DATA_LENGTH doesn't show anything useful for compressed tables
Submitted: 20 Jul 2011 12:11 Modified: 25 Jul 2011 14:46
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S3 (Non-critical)
Version:5.5-bzr, 5.1-bzr OS:Any
Assigned to: CPU Architecture:Any

[20 Jul 2011 12:11] Domas Mituzas
Description:
Both "SHOW TABLE STATUS" and INFORMATION_SCHEMA.TABLES does not report table sizes correctly for compressed tables, e.g.:

mysql> select table_name,create_options, data_length  from information_schema.tables where table_name like '%compr%';
+--------------+----------------------------------------+-------------+
| table_name   | create_options                         | data_length |
+--------------+----------------------------------------+-------------+
| compressed   | row_format=COMPRESSED KEY_BLOCK_SIZE=4 |    88883200 |
| uncompressed |                                        |    39403520 |
+--------------+----------------------------------------+-------------+
2 rows in set (0.00 sec)

mysql> show table status like '%compr%';
+--------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------------------------------+---------+
| Name         | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options                         | Comment |
+--------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------------------------------+---------+
| compressed   | InnoDB |      10 | Compressed |  998529 |             89 |    88883200 |               0 |            0 |   1835008 |           NULL | 2011-07-20 14:52:45 | NULL        | NULL       | latin1_swedish_ci |     NULL | row_format=COMPRESSED KEY_BLOCK_SIZE=4 |         |
| uncompressed | InnoDB |      10 | Compact    | 1000392 |             39 |    39403520 |               0 |            0 |   7340032 |           NULL | 2011-07-20 14:52:17 | NULL        | NULL       | latin1_swedish_ci |     NULL |                                        |         |
+--------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------------------------------+---------+

Whereas on disk:

# du -sh  *compr*ibd
 24M	compressed.ibd
 48M	uncompressed.ibd

How to repeat:
create a table with data that compresses ok'ish, check space on disk vs space reported, yay, broken. 

Suggested fix:
show correct data
[20 Jul 2011 13:57] Shane Bester
Is an already reported internal bug. Will link the two.
BUG 12770537 - I_S.TABLES.DATA_LENGTH DOES NOT SHOW ON-DISK SIZE FOR COMPRESSED INNODB
[20 Jul 2011 14:07] Shane Bester
we also have: http://bugs.mysql.com/bug.php?id=59659
[25 Jul 2011 14:46] Domas Mituzas
I was probably wrong, it does show something useful, you just have to divide it by (16/keyblocksize) ;-)