Hi... there is stuff inline there. >> The logic behind this is probably that without innodb_file_per_table=1 >> and with several large ibdata files, the space IS freed up when one does >> optimize table or drop table. The space is freed up inside the database >> files and can be reused. > > well, and if you have this day 2 TB mysql-data and a year later > get rid of 1 TB of it they allocated space can be REUSED for > innodb but never for any other application
I did not say it is the right thing to not have an option to shrink the database or do file management. I tried to explain the logic that is probably put into this product. Another piece of logic is that it is not really typical for the databases to lose 50% of its volume. The databases usually either grow, or can grow, or are destroyed. In that regard the product with this feature lacking probably still covers the needs of most. By comparison, Oracle did not provide ability to drop the datafiles until, eh, version 8, I believe, and it was not made easy until version 10. > >> If we think about it, the database product should only resolve problems of >> the database space management, not of the OS space management. > > the database producht with default settings is the part starting > the troubles of os-space-managment and this is idiotic, no other > words for this! I would say inconvenient. As I explained above, the OS space allocation problems that way could be considered a corner case and thus be considered unimportant by MySQL development. Considering the problem of reclaiming 1 terabyte out of 2-terabyte database, one could resolve it with creating a brand new instance followed by export/import of data. It is not that there is no solution, it is inconvenient to use. > MY only luck is that i recognized this years ago after PLAYING > with innodb and so i started with "innodb_file_per_table=1" from > the begin with the first production database Well, I would not base my database design on luck and playing. There should be good awareness of what the features do and what would be the plan to deal with file allocations should the database grow, shrink or somerset. > but the cases where ibdata1 is growing becasue ONCE bigger > data was stored and never release the allocated space is a > design-problem > Not exactly. A design problem is to build a server in such a way as that adding a feature to remove datafiles would be impossible (without major rebuild). I think this one can be added. I didn't bother to check, but I would be surprised if there isn't already an enhancement request for this