MySQL Forums
Forum List  »  InnoDB

Re: Automatic Extension Question
Posted by: Rick James
Date: June 26, 2014 02:39PM

First, I will comment on your comments...

> sum( data_length + index_length ) / 1024 / 1024 / 1024 "Data Base Size in GB",

That is the amount of disk space needed for the data and indexes in all the tables. But it does not include certain overhead.

> sum( data_free )/ 1024 / 1024 / 1024 "Free Space in GB"

That is the "free" space that it taking up disk space in the table space(s). Each Data_free is the free space for the tablespace that the table is in. Now it gets messy...

> I think the query isn't quite right because it assumes a per-table tablespace and it looks like the setup is a shared tablespace.

That is an astute observation. The SUM(data_free) works ONLY if _all_ tables are in separate tablespaces. If _none_ of the tables are in their own tablespaces (none created with innodb_file_per_table=1), then each value of Data_free will be the same, and it will be the free space in ibdata1. It is also possible to have a mixture of per-table and shared; this makes it impossible to do anything like SUM().

> command "show variables;" which shows "innodb_data_file_path ibdata1:10M:autoextend" and "innodb_autoextend_increment 8".
> If the database file started at 10MB and autoextends in increments of 8MB, then how did it come to have 1.2GB of free space?

Use MAX(Data_free) instead of SUM(Data_free) in the case of a single tablespace.

You can also look in the filesystem for ibdata1. It's size will probably equal SUM(Data_length+Index_length) + MAX(Data_free). If it is equal (or close), then you have the right formula.

innodb_file_per_table = 1 is useful if you have multi-megabyte table(s). It is less useful for small tables.

Note: There are other things on the disk that take space, so no SELECT will be 'perfect' for checking against disk space. For example, each table has a .frm file of about 8KB (regardless of file_per_table).

Options: ReplyQuote

Written By
June 20, 2014 08:38AM
Re: Automatic Extension Question
June 26, 2014 02:39PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.