CODEWITHSUNDEEP
mysqlsqldatabase
GBM
GBM

Reputation: 63

Why is there no free space in MySQL database?

Recently, I checked the SQL databases I'm using in phpMyAdmin (with WAMP), and they all report no free space. This was not true before, and I'm not sure what caused it to change. The lack of free space is making calls to the databases very, very slow. I've reinstalled WAMP, created a new user for phpMyAdmin, and reimported the databases into the new user, but to no avail.

Here is the query I used to check for space and the result:

SELECT table_schema "beek", SUM( data_length + index_length ) /1024 / 1024 "Data Base Size in MB", SUM( data_free ) /1024 /1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema
LIMIT 0 , 30

beek
Data Base Size in MB
Free Space in MB

-------------------------------------------
beek

Data Base Size in MB 4.11813927

Free Space in MB 0.00000000

-----------------------------------------
information_schema

Data Base Size in MB 0.00976563

Free Space in MB 0.00000000

---------------------------------------
mysql

Data Base Size in MB 0.76406479

Free Space in MB 0.00000000

-------------------------------------
performance_schema

Data Base Size in MB 0.00000000

Free Space in MB 0.00000000

--------------------------------------------

Does anyone know why all of the extra space in these databases has disappeared?

Upvotes: 1

Views: 3854

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

"Free space" for InnoDB tables is reported as the size of extents that have no data.

When a tablespace file grows physically, it grows in a big chunks of pages, called extents. Then you gradually INSERT data to fill the extent.

You could have previously queries the information_schema when you had just allocated new extents, but before you inserted data. Then as you insert data, it uses some space in the extent and it's no longer unoccupied (even if it's only partially filled).

Extents in the middle of the tablespace file can also be emptied when you do a lot of DELETEs. But then that free space is re-used by later INSERTs.

Either way, having zero unoccupied extents is normal, and it's not a problem. If the file needs to grow physically, InnoDB can do that.

For more details, see:

Upvotes: 3

Related Questions