Showing disk usage of innodb tables?

[Gh!]

How can I see how much disk space is being occupied by an InnoDB table?

Looking into /var/lib/mysql doesn't seem to work anymore for InnoDB tables.

Please tell also if there is a way to see how much space is occupied by
a particular row or column of a table.

Thanks

[Bill Karwin]

Gh! wrote:

How can I see how much disk space is being occupied by an InnoDB table?

Looking into /var/lib/mysql doesn't seem to work anymore for InnoDB tables.
The default configuration is for all InnoDB tables to share one
tablespace, which may consist of multiple files, but each table is
spread throughout the tablespace in a unpredictable manner.

Read this page in the docs:

http://dev.mysql.com/doc/mysql/en/in...ile-space.html

But you can also initialize the InnoDB tablespace to store each table in
a separate file.

Read this page in the docs:
http://dev.mysql.com/doc/mysql/en/mu...blespaces.html

This still doesn't tell you how much space is occupied by a given table,
because typically the files do not shrink, even if you delete a lot of
records. The space inside in marked as free -- i.e., available for
storing record versions created by future inserts or updates.

You can get a report of the free space in a tablespace with the SHOW
TABLE STATUS command (or mysqlshow --status databasename). If you use a
shared tablespace (the default), the report is for the whole shared
tablespace. If you use separate files for each InnoDB table, the report
is for the free space in the corresponding file.

Read this page in the docs:
http://dev.mysql.com/doc/mysql/en/sh...le-status.html
Please tell also if there is a way to see how much space is occupied by
a particular row or column of a table.

I don't know of a good way to get this information. You could create
another table and copy an individual row or column into it, and then get
space statistics on that table, but it might not match the space usage
of the original table precisely, because of differences in InnoDB file
fragmentation, etc.

Regards,
Bill K.