List:General Discussion« Previous MessageNext Message »
From:Karen Abgarian Date:November 29 2011 7:25pm
Subject:Re: help needed restoring crashed mysql
View as plain text  
On 29.11.2011, at 5:21, Reindl Harald wrote:

> 
> ibdata1 does NEVER get smaller, this is normal and a hughe problem
> in your case, only if you are using "innodb_file_per_table" which
> is NOT default would retire the space after drop tables
> 
> why is this dumb "innodb_file_per_table=0" default since MOST PEOPLE
> have only troubles with it because they can not free space with
> "optimize table" with no real benefits?
> 

Hi...

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.  

If we think about it, the database product should only resolve problems of
the database space management, not of the OS space management.   Then, 
the user essentially asked InnoDB to keep allocating arbitrary amount of space
as needed, ignoring that the OS disk is actually of the limited size.  To be
correct about it, the user should have stated that the ibdata file should have a
firm limit and not autoextend beyond that. 

This is not to say that MySQL could not  have more of the file management 
features.  For example, the ability to add or remove datafiles on the fly and the
ability to detach tablespaces as collections of tables.   

Making innodb tablespaces default... well, it still would not liberate the users
from thinking whether they want to run with them enabled or not.   For example, 
if I have 10000 tables of 100 bytes each, I probably do not want tablespaces. 
If I have 1% of tables consuming 99% of the space, I would also not want the
tablespaces. 

As for the OP's problem, unless he changed his mind about the need to import, 
the same amount of space would anyway be consumed.   The solution would 
probably be to find some bigger O Sdisk and copy that ibdata file there.   Right?


Cheers
Karen 

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