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
innodb_file_per_table=1.
" 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:nixofort...@gmail.com]
Sent: Monday, June 24, 2013 12:35 PM
To: mysql@lists.mysql.com
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" <nixofort...@gmail.com>
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
Hi Rick,
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'
(`site_id`,`index_date`,`index_month`,`index_year`,`keyword`,`source`,`visits`,`bounced_visits`,`transactions`,`revenue`,`value_per_click`,`conversions`,`goal_value`);
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.
Thanks,
Igor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
