10

In my MySQL database I see these statistics:

Type    Usage  
Data    16,384  Bytes  
Index   32,768  Bytes  
Overhead    405,0   MB  
Effective   -424,624,128    Bytes  
Total   49,152  Bytes

When I try the commands check table, ALTER TABLE 'mytable' ENGINE = INNODB, OPTIMIZE TABLE, nothing happens to the overhead.

Why does nothing happen, should I worry, and when should I worry? I've seen other questions that state 'to worry when the overhead gets too large'. What is too large?

Matthew Lock
  • 13,144
  • 12
  • 92
  • 130
nhaarman
  • 98,571
  • 55
  • 246
  • 278
  • 2
    I think running an OPTIMIZE TABLE should fix the overhead. And I'm not sure but I think it does not apply to InnoDB. This seems to be a duplicate of http://stackoverflow.com/questions/565997/in-mysql-what-does-overhead-mean-what-is-bad-about-it-and-how-to-fix-it – Abhay Feb 19 '12 at 18:03
  • What does this question have to do with phpMyAdmin? – Mchl Feb 19 '12 at 18:34
  • @Mchl The commands are executable in phpMyAdmin. – nhaarman Feb 19 '12 at 21:31
  • @Abhay Forgot to add that command, but that doesn't change anything to the overhead size. Also, it is the same kind of question, but it doesn't answer mine, as it doesn't work. – nhaarman Feb 19 '12 at 21:33

2 Answers2

12

Overhead in PHPMyAdmin is calculated by 'Data_free' column returned by SHOW TABLE STATUS. It is actually explained in MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Data_free: The number of allocated but unused bytes.

Beginning with MySQL 5.1.24, this information is also shown for InnoDB tables (previously, it was in the Comment value). InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace. If you are using multiple tablespaces and the table has its own tablespace, the free space is for only that table. Free space means the number of completely free 1MB extents minus a safety margin. Even if free space displays as 0, it may be possible to insert rows as long as new extents need not be allocated.

However for InnoDB this is important "InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace.". So with a typical InnoDB setup ('innondb_file_per_table' is not set) you will get the free space for all tables and not for a single table. That is probably why phpMyAdmin ignores/discards the information or on Optimize table nothing happens.

You should read this post which clearly explains how to optimize a table in innodb.

Community
  • 1
  • 1
Ashwin A
  • 3,779
  • 23
  • 28
8

Run:

OPTIMIZE TABLE table_name;

On each table you wish to fix - this should reduce the overhead. The other option is using this command:

/usr/bin/mysqlcheck --auto-repair --check --optimize --all-databases

This will check, repair and optimize all tables in all databases.

northtree
  • 8,569
  • 11
  • 61
  • 80
Gareth Davies
  • 453
  • 2
  • 7