Measuring free space in InnoDB's global tablespaceFri, Sep 7, 2012 in Databases
innodb_file_per_table=1, InnoDB places every table’s data and indexes in a separate
.ibd file, but there is still a “global” system tablespace, stored by default in a file named
ibdata1. This contains some of each table’s data, such as the undo log and insert buffer. If it is fixed-size, you can fill it up and crash the server, as I’ve mentioned in a few recent blog posts.
In older versions of MySQL, the
SHOW TABLE STATUS command showed the amount of space free in the tablespace as an entry in the
Comment column. If you weren’t using
innodb_file_per_table, you could use this to see how full your tablespace was.
The servers I’m managing use
innodb_file_per_table=1, so I thought perhaps I can find out how full the system tablespace is by disabling
innodb_file_per_table, creating a table, and enabling it again. This quickly reminded me that the setting is global only, not per-connection. It would be nice to have more flexibility for that option.
But then I discovered that it didn’t work anyway. My new table, created in the system tablespace instead of in its own file, doesn’t show anything in the
Comment column. And the
INFORMATION_SCHEMA.TABLESPACES table appears to be a stub; it is empty and the docs say nothing meaningful about it.
Are there any other options for measuring the space usage in the tablespace? I’d rather do this within the server itself than use an external tool like xtrabackup.
I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.