Hello,

Our system:
- MySQL 5 on Windows 2003
- using innoDB
- data length is approximately 120 GB of data
- total space = 210 GB


We recently changed teh data type of several fields from INT to DOUBLE and INT to CHAR. After doing this our database increased by about 40 GB, for which we did not plan. We ended up using all free space on the disk.

To get around this problem we:
1. dropped an unused table (about 10GB)
2. used MySQL Administrator to backup to a .sql file a number of old tables (about 15 GB) then deleted all rows in those tables and Optiimised them.

The innoDB file ibdata1 file remained at 210 GB. From my reading the ibdata1 file will remain at that size.
The Data length is now 110 GB and Index length = 25 GB.


A. However, will new data continue to fill the whitespace (75 GB) left after dropping the table and deleting rows from the other tables?

B. Should we drop the empty tables (that were archived off) to recover space?

C. Do dropping tables and leaving empty tables free up space for new data, so that we can do either to recover space?

D. Is there a way to recover disk space without dumping the entire database and then inserting all the records into a new database?

thanks for any assistance,
DJmysql

bump.

any assistance welcome.

cheers,
DJmysql

A. My understanding is that it will.

B. I don't think dropping the tables will recover any space, unless you had innodb_file_per_table=1 in my.ini when you created the tables. Unless this option is set, the only way to free space is to export everything from innodb, regenerate the ibdata file, and re-import everything.

C. It frees up space for innodb to store data, but the ibdata file doesn't shrink, so it doesn't free up space on the file system.

D. Not unless you had innodb_file_per_table=1 in my.ini. Also, dropping the database won't free space unless you have innodb_file_per_table set to 1.

Basically, if you don't want this problem in the future, you could export everything, set innodb_file_per_table to 1 in my.ini, regenerate the ibdata file, and import everything back in.

Thanks for the reply.I think we may try to redesign the database to allow for a file per table- it just feels safer.

at the moment "data length + index length" is much smaller than the size of the ibdata1 file so i have some time to plan.

thanks again.

DJmysql