List:General Discussion« Previous MessageNext Message »
From:Christophe Date:March 26 2014 6:53pm
Subject:Re: Database migration from default configuration to innodb_file_per_table
View as plain text  
Hi,

Data migration made last night.

Use of a new MySQL instance has been quite useful to operate, the outage
was about 7 minutes.
ibdata1 is now using 58 MiB of disk space ! each table having a proper
.ibd file and full data directory from 13GiB to 3.2GiB ...

Server load divided by 3 ~ 4 ;).

I think operation is successful ;) .

Thanks Shawn !

Regards,
Christophe.



Le 23/03/2014 18:49, Christophe a écrit :
> Hi Shawn, and thanks for this concise anwser ;) .
> 
> Le 22/03/2014 05:35, shawn l.green a écrit :
>>
>> The system is operating exactly as designed. The ibdata* file(s) contain
>> more than just your data and indexes. This is the common tablespace and
>> it contains all the metadata necessary to identify where *all* your
>> InnoDB tables actually are (where they are in a tablespace and which
>> tablespace they are in) and several other things about them. In the
>> terms of the InnoDB developers, this is the "data dictionary".  This
>> means that once you blow it away, MySQL has no details about any where
>> any of your InnoDB tables are, exactly as the message says.
>>
>> The table names are visible in a SHOW TABLES command because that is
>> essentially performing a directory listing of any .FRM files in that
>> database's folder. 
> 
> It's good to know, and explains why I got this behaviour while my lab
> run ...
> 
>> Without both parts (the definition in the .FRM file
>> and the metadata in the common tablespace) your tables are broken. If
>> you have the .frm file, you can find out which columns you have defined,
>> what data types they are, if the table is partitioned or not and what
>> your indexes and other constraints look like. The .frm file cannot tell
>> the InnoDB engine which tablespace a table is in or what offset the root
>> page of the table is within the tablespace. That information was stored
>> in the ibdata file that you erased during your test run.
>>
>> The proper way to change the size of your common tablespace is
>> documented here in the user manual
>> http://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html
>>
>> Search for the section header "Decreasing the Size of the InnoDB
>> Tablespace"
>>
> 
> It's just what I wanted to avoid :( ... but thanks for the link ;).
> 
> As a Workaround, I think I will run a second MySQL instance during task,
> and make a binary copy of files after making sure they are good.
> 
> Best thanks and Regards,
> Christophe.
> 

Thread
Database migration from default configuration to innodb_file_per_tableChristophe21 Mar 2014
  • Re: Database migration from default configuration to innodb_file_per_tableshawn l.green22 Mar 2014
    • Re: Database migration from default configuration to innodb_file_per_tableChristophe23 Mar 2014
      • Re: Database migration from default configuration to innodb_file_per_tableChristophe26 Mar 2014