Re: mysqldump and import to de-fragment tables
Posted by:
Rick James
Date: January 17, 2011 02:48PM
97% -- PANIC!
<name>#P#p0 -- sounds like you have a PARTITIONed table `name`.
How big is ibdata1? (You mention 600GB and 999GB, but are not clear what you are referring to.)
Need to know whether the spare room is in ibdata1 or in the .ibd files.
To dump and reload, you would need 600GB? 999GB? (some amount) of spare room...
Case 1: Lots of free room in a .ibd file -- If you have enough room (that is, the data+index < 52GB) to rebuilt it (ALTER TABLE foo ENGINE=InnoDB), do so.
Case 2: .ibd data+index bigger than free room, but that is enough space in ibdata1 -- Turn off file_per_table, restart mysqld, ALTER TABLE; this will move it to ibdata1. Leave it there for now. The 52GB should increase.
Once you have done all of those cases that you can, and if you still need to free more, you must
1. dump _all_ innodb tables directly to another machine. Suggest running mysqldump on the other machine.
2. stop mysqld
3. remove ibdata1
4. start mysqld (all InnoDB tables will be gone!)
5. Check my.cnf: Be sure file_per_table is on. Change the setting (in my.cnf) to have only a modest (say, 100M + autoextend) ibdata1
6. restart mysqld (this will recreate ibdata1 with the new size)
7. reload all tables.
Good luck. And don't let it get to 97% again!
Subject
Written By
Posted
Re: mysqldump and import to de-fragment tables
January 17, 2011 02:48PM
February 17, 2011 08:23AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.