List:General Discussion« Previous MessageNext Message »
From:shawn l.green Date:June 24 2014 8:45pm
Subject:Re: Optimizing InnoDB tables
View as plain text  
Hello Reindl,

On 6/24/2014 3:29 PM, Reindl Harald wrote:
>
>
> Am 24.06.2014 21:07, schrieb shawn l.green:
>> It makes a huge difference if the tables you are trying to optimize have their
> own tablespace files or if they live
>> inside the common tablespace.
>>
>>
> http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table
>
> which is the most stupid default in case of innodb and only survivable
> without a lot of work for people who realize that *before* start
> operations and enable "innodb_file_per_table" from the very begin
>
> having defaults which can't be changed later without complete re-import
> of data and prevent from ever get disk space for long ago deleted data
> free is the most wrong thing a software developer can do
>

The tables can be moved from the common tablespace into their own 
tablespace at any time after the option is enabled. The space they once 
occupied within the primary tablespace will remain and it will be marked 
as 'available' for any general purpose (such as the UNDO log)

The only way to shrink the primary tablespace is, as you correctly 
described, through a dump/restore of your data. This process to resize 
the primary tablespace (such as to shrink it) must be followed precisely 
or problems will result.

http://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html

-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Optimizing InnoDB tablesAntonio Fernández Pérez24 Jun 2014
  • Re: Optimizing InnoDB tablesWagner Bianchi24 Jun 2014
    • Re: Optimizing InnoDB tablesAntonio Fernández Pérez24 Jun 2014
  • Re: Optimizing InnoDB tablesshawn l.green24 Jun 2014
    • Re: Optimizing InnoDB tablesReindl Harald24 Jun 2014
      • Re: Optimizing InnoDB tablesshawn l.green24 Jun 2014
        • Re: Optimizing InnoDB tablesAntonio Fernández Pérez25 Jun 2014
          • Re: Optimizing InnoDB tablesJohan De Meersman25 Jun 2014
          • Re: Optimizing InnoDB tablesAndre Matos25 Jun 2014
            • Re: Optimizing InnoDB tablesAntonio Fernández Pérez27 Jun 2014
              • Re: Optimizing InnoDB tablesReindl Harald27 Jun 2014
                • Re: Optimizing InnoDB tablesAntonio Fernández Pérez27 Jun 2014
                  • Re: Optimizing InnoDB tablesshawn l.green27 Jun 2014
                  • Re: Optimizing InnoDB tablesJohan De Meersman27 Jun 2014
                    • Re: Optimizing InnoDB tablesAntonio Fernández Pérez30 Jun 2014
                      • Re: Optimizing InnoDB tablesReindl Harald30 Jun 2014