List:General Discussion« Previous MessageNext Message »
From:Karen Abgarian Date:November 30 2011 12:11am
Subject:Re: help needed restoring crashed mysql
View as plain text  
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



Thread
help needed restoring crashed mysqlLuis Pugoy29 Nov
  • Re: help needed restoring crashed mysqlReindl Harald29 Nov
    • Re: help needed restoring crashed mysqlKaren Abgarian29 Nov
      • Re: help needed restoring crashed mysqlClaudio Nanni29 Nov
      • Re: help needed restoring crashed mysqlReindl Harald29 Nov
        • Re: help needed restoring crashed mysqlKaren Abgarian30 Nov
          • Re: help needed restoring crashed mysqlReindl Harald30 Nov
            • Re: help needed restoring crashed mysqlKaren Abgarian30 Nov
              • [MySQL] innodb_file_per_table / apple workers and logicReindl Harald30 Nov
                • Re: [MySQL] innodb_file_per_table / apple workers and logicJohan De Meersman30 Nov
                • Re: [MySQL] innodb_file_per_table / apple workers and logicKaren Abgarian30 Nov
                  • Re: [MySQL] innodb_file_per_table / apple workers and logicReindl Harald30 Nov
                    • Re: [MySQL] innodb_file_per_table / apple workers and logicMySQL)1 Dec
                      • Re: [MySQL] innodb_file_per_table / apple workers and logicKaren Abgarian1 Dec
              • Re: help needed restoring crashed mysqlReindl Harald30 Nov
        • Re: help needed restoring crashed mysqlhsv30 Nov
          • Re: help needed restoring crashed mysqlReindl Harald30 Nov
Re: help needed restoring crashed mysqlKaren Abgarian30 Nov