On 25/06/13 23:55, Rick James wrote:
> Switch to InnoDB so you won't have to repair after crashes.
> Caution: InnoDB takes 2x-3x the disk space per table. Be sure to use
> " Repair by sort." is usually much faster than "repair by keycache"; you probably got
> 'sort' because of this being big enough: "myisam_sort_buffer_size = 526M"
>> -----Original Message-----
>> From: nixofortune [mailto:nixofortune@stripped]
>> Sent: Monday, June 24, 2013 12:35 PM
>> To: mysql@stripped
>> Subject: Re: space gone after MyISAM REPAIR TABLE
>> On 24/06/13 19:57, Reindl Harald wrote:
>>> Am 24.06.2013 18:47, schrieb Johan De Meersman:
>>>> ----- Original Message -----
>>>>> From: "nixofortune" <nixofortune@stripped>
>>>>> Hi guys,
>>>>> any suggestions? I just repaired 90G MyISAM table with REPAIR TABLE
>>>>> command. the space on the hard drive gone down from 165 Gig to 70
>>>>> Gig. I understand that during repair process MySQL creates temp file
>>>>> and remove it after the job done. Or removal process executes on
>>>>> the server restart? how can I get that space back? I can't check the
>>>>> table directory as I don't have root perm on that box.
>>>> Oops... Can you run [show global variables like
>> 'innodb_file_per_table';] ?
>>>> I kind of expect it to be OFF, which means that the temp table would
>> have been created in the main tablespace. If that's the case, that space
>> has been permanently assimilated by the global tablespace; the only way to
>> get it back would be a full dump of all your (innodb) tables, stop server,
>> delete tablespace, start server and import the data again. Be sure to read
>> the documentation carefully before doing such an intrusive operation.
>>>> While you're doing that, use the opportunity to set
>>>> innodb_file_per_table to ON :-p
>>> he spoke about MYISAM table
>>>>> the space on the hard drive gone down from 165 Gig to 70 Gig how can
>>>>> I get that space back?
>>>>> I can't check the table directory as I don't have root perm
>>> well, someone should look at the dadadir and error-log it is not
>>> uncommon that a repair to such large tables fails due too small
>>> "myisam_sort_buffer_size" and i suspect the operation failed and some
>>> temp file is laying around
>> Thanks Reindl, It looks like Repair operation completed successfully.
>> Overall it took 2Hours to complete with OK massage and some other message
>> related to the index size. Repair process went through Repair by sort.
>> myisam_sort_buffer_size = 526M.
>> Provider runs MySQL on FreeBSD + ZFS file system. Could it be up to
>> snapshots as well?
>> I will ask them to look inside of datadir as we migrated this DB from
>> Solaris just day before. This is a new DB for me and I never worked with
>> MyISAM tables of that size.
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql
many thanks for the 2x3 space tip. I'm working on that trying to find a
way by reducing extremely inefficient tables.
Switched to innodb_file_per_table already. I'm gradually converting HUGE
(70-100Gig) MyISAM tables to InnoDB.
The way I do it is by
1. creating csv file to keep the original data
2. CREATE TABLE new_innodb LIKE old_myisam;
3. ALTER TABLE new_innodb MODIFY ADD id bigint UNSIGNED AUTO_INCREMENT
PRIMARY KEY FIRST; (YES NO PRIMARY KEYS :( )
4. LOAD DATA INFILE '/storage/mysql/dump/old_myisam.csv'
INTO TABLE new_innodb
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
Our operations allows to do that. But it takes a long time to load 7-8 H
for 250 000 000 Rows
I tried to DROP indexes on new_innodb, LOAD DATA, works quicker, but
then when I do
ALTER TABLE `new_innodb`
ADD KEY `idx1` (`col1`,`col2`),
ADD KEY `idx2` (`col1`,`col2`,`col3`);
Server become numb with I/O wait 15-20% and I had to kill the process ..
What would be the best way to convert BIG MyISAM table into InnoDB? We
do not have SLAVE.