2

I tried to know how much extent ( "free space" ) does my database have after deleting a rather large table. ( Around 10GB )

I have run the command:

SELECT table_schema "Data Base Name", 
round( sum( data_free ) / 1024 / 1024 / 1024 ) "Free Space in GB" 
FROM information_schema.TABLES
GROUP BY table_schema;

which gave me a list of databases, and their "free spaces".

The problem is, that the database which had the 10GB table removed now has a 1500GB+ free space according to this report which is significally bigger than my actual hard drive capacity. ( which is around 200GB )

How is this possible? How could I get a more realistic report? Am I missing something?

UPDATE

As an experiment, I have added and removed an 1GB table in this database, now the report shows around 110GB more free space. Might there be a problem with my configuration, or is this a common issue?

István Pálinkás
  • 2,217
  • 7
  • 25
  • 50
  • Is there an overflow (in the SUM)? If not, which table(s) are reported as abnormally large? – user2864740 Feb 21 '17 at 16:46
  • I cannot really track back the table itself, since it has been dropped. I only see this abnormality in the parent database's size. How could I identify a potentional overflow you have mentioned? – István Pálinkás Feb 21 '17 at 16:51
  • But... Are you sure you can just SUM the free space? Isn't it share between them all? – Álvaro González Feb 21 '17 at 16:53
  • Yeah, I am not familiar with the subject this deep. I found the query I used here on StackOverflow: http://stackoverflow.com/questions/14714750/how-to-get-true-size-of-mysql-database – István Pálinkás Feb 21 '17 at 17:02

2 Answers2

4

(This is answering some of the questions buried in Comments.)

Misnomer "Free" space only includes whole blocks, not spare room inside blocks, and many other details.

Case 1: All tables are in ibdata1 -- SHOW TABLE STATUS (or the equivalent query into information_schema will show the same Data_free value, namely how much is free in ibdata1. This space can be reused by any table. It is hard to give the space back to the OS.

Case 2: All tables are file_per_table -- Now each Data_free refers to the space for the table. And the SUM() is meaningful. (ibdata1 still exists, but it does not contain any real tables; there is a lot of other stuff that InnoDB needs.)

Case 3: Mixture -- If you turn file_per_table on/off at various times, some tables will be in ibdata1, some will have their own tablespaces.

Case 4: CREATE TABLESPACE in 5.7 -- For example, you can have a tablespace for each database.

Case 5: PARTITIONed tables -- Each partition acts like a table.

Case 6: 8.0 -- Even more changes are coming.

Database == Directory In MySQL's directory tree each database can be seen as a filesystem directory. Within that directory can be seen some set of files for each table. The .frm file contains the table definition. If an .ibd file exists, the table was created with file_per_table. This may be the most reliable way to discover whether the table is file_per_table. (8.0 will have significant changes here.)

How much space can I reuse? There is no good answer. Usually inserting a row will find space in the block where it belongs, and Data_free will not shrink. But, if there were block split(s), Data_free can drop by some multiple of 16KB (the block size) or 4MB (the "extent size" - or maybe it is 8MB?). Also, random inserts lead to BTree blocks being, on average, about 69% full.

Changing innodb_file_per_table has no effect until the next CREATE TABLE or ALTER TABLE. And then it only has effect on where to put the newly created/copied data+indexes (ibdata1 or .ibd). It will not destroy data.

Big tables usually have 4MB to 7MB of Data_free. When computing how many rows you can add, don't plan on Data_free dropping below that range.

Avg_row_size should be useful. But sometimes it (and Rows) are poorly approximated. Their product (Data_length) is always correct. So, this might be a good estimate of "rows to go before grabbing more space from OS:

(Data_free - 7M) / Avg_row_size

Tablespace Recommendations: Put 'big' tables in file_per_table. Put 'tiny' tables in ibdata1 or database-specific tablespaces (5.7). Sorry, no simple recommendation on the dividing line between 'big' and 'tiny'. And it is clumsy to migrate a table: SET global innodb_file_per_table = ...;; logout; login (to pick up the global); ALTER TABLE tbl ENGINE=InnoDB;. And it is necessarily a full copy of the table.

(Caveat: I have left out many details.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks, Rick! What do You think about this alternative solution. Assuming that I have **Case 1** if I measure the actual file size of the `ibdata1` file with `ls -l` and I substract the data size measured from mysql `data_length + index_length` can I get the "real" free space approximately? – István Pálinkás Feb 22 '17 at 13:32
  • Maybe it would be interesting. – Rick James Feb 22 '17 at 17:02
  • I did it, and it seems approximately realistic. – István Pálinkás Feb 23 '17 at 11:05
  • I don't know how many of miscellaneous data structures there in ibdata1. But if the formula works for you, fine. – Rick James Feb 23 '17 at 15:56
  • 2
    A caution: An `ALTER TABLE` or a multi-row `UPDATE` or ... may temporarily grab a bunch of space in ibdata1, possibly growing the size of the file before your formula shrinks back to about where it had been. – Rick James Feb 23 '17 at 15:59
1

It sounds as though you do not have innondb_file_per_table set, and are therefore using a shared table space. If so, then you will be reurning the global 'allocated but unused' shared space, repeatedly for each table_schema.

Gavin Jackson
  • 1,907
  • 1
  • 22
  • 28
  • Yeah, that sounds logical, thanks. How could I make sure this is my issue? Is there a way to find out if I have **innondb_file_per_table** set within the mysql environment? – István Pálinkás Feb 21 '17 at 17:19
  • You can confirm the current state via... show variables like 'innodb_file_per_table'; innodb_file_per_table | ON 1 row in set (0.07 sec) Though this may not reflect the actual usage of tables that existed prior to making a setting change. – Gavin Jackson Feb 21 '17 at 17:30
  • If you have filesystem command line access, then each table will have its own .ibd file (in /var/db/mysql or similar), if the setting is ON. – Gavin Jackson Feb 21 '17 at 17:35
  • I am sure it wasn't changed in a long time. It had to be declared at the installation of the mysql module on the server. The query you have attached says that **innodb_file_per_table** is set to `OFF` – István Pálinkás Feb 21 '17 at 17:35
  • You will probably have an ibdata1 (or more) file in /var/db , this will be the communal shgared table space including any unused alllocated space left over after deletions. I don't think that you can have pre-existing tables with their own .ibd files if the setting is OFF, be very careful if you change the setting, it might delete exiting data etc. – Gavin Jackson Feb 21 '17 at 17:46
  • I see, thank You. Could You please provide some ideas in your answer on my other sub-question about how could I reliably get the free size of this database? Like _How could I reliably know how much space can I (re)use before InnoDB starts reserving more space on my hard drive?_ – István Pálinkás Feb 21 '17 at 17:52