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

On 6/24/2014 7:03 AM, Antonio Fernández Pérez wrote:
> ​Hi list,
>
> I was trying to optimize the InnoDB tables. I have executed the next query
> to detect what are the fragmented tables.
>
> ​​SELECT TABLE_SCHEMA,TABLE_NAME
> FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND
> Data_free > 0​
>
> After that, I have seen that there are 49 fragmented tables. With one
> table, I have executed "optimize table table_name;" and "analyze table
> table_name;". The result is the same, the table continuos fragmented.
>
> Any ideas? I have followed the mysqltuner recomendations ...
>
> Thanks in advance.
>
> Regards,
>
> Antonio.
>

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


-- 
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