Re: space gone after MyISAM REPAIR TABLE
On 2013-06-26 18:31, nixofortune wrote: What would be the best way to convert BIG MyISAM table into InnoDB? We do not have SLAVE. I would do it on another computer. Then copy the table to the server and then add the data that has been added from the original table. And/or i would experiment with TokuDB. I havent had the time to do it myself but will probably soon. I am too looking for a lengthy 1 billion+ row conversion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: space gone after MyISAM REPAIR TABLE
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
Re: space gone after MyISAM REPAIR TABLE
You can't actually move innodb tables around until 5.6 where you have transpotable tablespaces. I suggest having a good hard look at pt-online-schema-change or whatsitcalled. Jay Ess li...@netrogenic.com wrote: On 2013-06-26 18:31, nixofortune wrote: What would be the best way to convert BIG MyISAM table into InnoDB? We do not have SLAVE. I would do it on another computer. Then copy the table to the server and then add the data that has been added from the original table. And/or i would experiment with TokuDB. I havent had the time to do it myself but will probably soon. I am too looking for a lengthy 1 billion+ row conversion. -- Sent from Kaiten Mail. Please excuse my brevity. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: space gone after MyISAM REPAIR TABLE
2013/06/26 17:31 +0100, nixofortune ALTER TABLE `new_innodb` ADD KEY `idx1` (`col1`,`col2`), ADD KEY `idx2` (`col1`,`col2`,`col3`); Is it really seemly for one index to be a leading part of another? (or maybe I am really thinking of something else) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: space gone after MyISAM REPAIR TABLE
This is my table: CREATE TABLE `ga_monthly_keyword_visits` ( `site_id` int(11) DEFAULT NULL, `index_date` int(11) DEFAULT NULL, `index_month` int(11) NOT NULL, `index_year` int(11) NOT NULL, `keyword` varchar(128) DEFAULT NULL, `source` varchar(30) DEFAULT NULL, `visits` int(11) DEFAULT NULL, `bounced_visits` int(11) DEFAULT NULL, `transactions` int(11) DEFAULT NULL, `revenue` float(10,2) DEFAULT NULL, `value_per_click` float(10,2) DEFAULT NULL, `conversions` int(11) DEFAULT NULL, `goal_value` float(10,2) DEFAULT NULL, KEY `idx_bounced_visits` (`site_id`,`index_date`), KEY `idx_visits_month` (`site_id`,`keyword`,`index_month`,`index_year`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 Was changed into: CREATE TABLE `ga_monthly_keyword_visits` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `site_id` int(11) DEFAULT NULL, `index_date` int(11) DEFAULT NULL, `index_month` int(11) NOT NULL, `index_year` int(11) NOT NULL, `keyword` varchar(128) DEFAULT NULL, `source` varchar(30) DEFAULT NULL, `visits` int(11) DEFAULT NULL, `bounced_visits` int(11) DEFAULT NULL, `transactions` int(11) DEFAULT NULL, `revenue` float(10,2) DEFAULT NULL, `value_per_click` float(10,2) DEFAULT NULL, `conversions` int(11) DEFAULT NULL, `goal_value` float(10,2) DEFAULT NULL, PRIMARY KEY (`id`) KEY `idx_bounced_visits` (`site_id`,`index_date`), KEY `idx_visits_month` (`site_id`,`keyword`,`index_month`,`index_year`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 I tried Converting like this: CREATE TABLE new LIKE old; ALTER TABLE new DROP (all indexes), ADD PRIMARY KEY (either autoincrement or a 'natural' compound PK), ENGINE=InnoDB; INSERT INTO new SELECT site_id, ..., goal_value FROM old; ALTER TABLE new ADD INDEX (...); With only difference The original MyISAM table crashed and I took it from backup, loading by LOAD DATA INFILE. The problem, yes It loaded much quicker into Database 4H 16M to be precise, but ALTER TABLE new ADD INDEX (...); Put server into meditation mode. High I/O Wait rendered box unusable. I had to interrupt the ALTER ADD KEY process after 5H of struggle. Now importing with Keys in place. It takes longer, much longer but at least the server is working and customers do not complaint. Schema design is awful, agree. I try to understand the process so will redesign it soon, but any suggestions are welcome. I' not a MySQL super guru so will be glad for hear your sorts, guys. Thanks On 27/06/13 00:04, Rick James wrote: (`site_id`,`index_date`,`index_month`,`index_year`,`keyword`,`source`,`visits`, `bounced_visits`,`transactions`,`revenue`,`value_per_click`,`conversions`,`goal_value`); May we see the SHOW CREATE TABLE? Some of this smells bad. * It is almost always bad to split day/month/year into multiple fields. * Often a fact table, which this sounds like, should not have extra indexes. * Is each datatype as small as is practical? * Are any of the fields VARCHAR, and could be 'normalized'? I would expect this to the fastest way to convert (assuming you have the disk space): CREATE TABLE new LIKE old; ALTER TABLE new DROP (all indexes), ADD PRIMARY KEY (either autoincrement or a 'natural' compound PK), ENGINE=InnoDB; INSERT INTO new SELECT site_id, ..., goal_value FROM old; ALTER TABLE new ADD INDEX (...); What version of MySQL are you running? Newer versions do the ALTER TABLE faster (online??), and may require you to do one add at a time. Another issue... If the data in `old` is in the same order as the PRIMARY KEY of `new`, then INSERT..SELECT will run fast. (No need to jump around to find where to put each row.) Case 1: You are adding an AUTO_INC -- it will be in the 'right' order. Case 2: The new PK is approximately the order of the insertions into `old` -- probably run fast. (However, I do not see a likely natural PK that would allow this INSERT ... SELECT...ORDER BY (new PK) -- This would make the INSERT part fast, but the SELECT part would be slow. (You can't win) Your task is all about disk hits. By understanding what MySQL has to do, you can 'predict' whether a plan will be slow or slower. Back to the secondary indexes... What are the SELECTs that will benefit from them? (Sometimes discussing this can lead to fewer/better INDEXes. Often it leads to suggesting Summary Table(s).) -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Wednesday, June 26, 2013 11:46 AM To: li...@netrogenic.com; Jay Ess; mysql@lists.mysql.com Subject: Re: space gone after MyISAM REPAIR TABLE You can't actually move innodb tables around until 5.6 where you have transpotable tablespaces. I suggest having a good hard look at pt-online-schema-change or whatsitcalled. Jay Ess li...@netrogenic.com wrote: On 2013-06-26 18:31, nixofortune wrote: What would be the best way to convert BIG MyISAM table into InnoDB? We do not have SLAVE. I would do it on another computer
RE: space gone after MyISAM REPAIR TABLE
(`site_id`,`index_date`,`index_month`,`index_year`,`keyword`,`source`,`visits`, `bounced_visits`,`transactions`,`revenue`,`value_per_click`,`conversions`,`goal_value`); May we see the SHOW CREATE TABLE? Some of this smells bad. * It is almost always bad to split day/month/year into multiple fields. * Often a fact table, which this sounds like, should not have extra indexes. * Is each datatype as small as is practical? * Are any of the fields VARCHAR, and could be 'normalized'? I would expect this to the fastest way to convert (assuming you have the disk space): CREATE TABLE new LIKE old; ALTER TABLE new DROP (all indexes), ADD PRIMARY KEY (either autoincrement or a 'natural' compound PK), ENGINE=InnoDB; INSERT INTO new SELECT site_id, ..., goal_value FROM old; ALTER TABLE new ADD INDEX (...); What version of MySQL are you running? Newer versions do the ALTER TABLE faster (online??), and may require you to do one add at a time. Another issue... If the data in `old` is in the same order as the PRIMARY KEY of `new`, then INSERT..SELECT will run fast. (No need to jump around to find where to put each row.) Case 1: You are adding an AUTO_INC -- it will be in the 'right' order. Case 2: The new PK is approximately the order of the insertions into `old` -- probably run fast. (However, I do not see a likely natural PK that would allow this INSERT ... SELECT...ORDER BY (new PK) -- This would make the INSERT part fast, but the SELECT part would be slow. (You can't win) Your task is all about disk hits. By understanding what MySQL has to do, you can 'predict' whether a plan will be slow or slower. Back to the secondary indexes... What are the SELECTs that will benefit from them? (Sometimes discussing this can lead to fewer/better INDEXes. Often it leads to suggesting Summary Table(s).) -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Wednesday, June 26, 2013 11:46 AM To: li...@netrogenic.com; Jay Ess; mysql@lists.mysql.com Subject: Re: space gone after MyISAM REPAIR TABLE You can't actually move innodb tables around until 5.6 where you have transpotable tablespaces. I suggest having a good hard look at pt-online-schema-change or whatsitcalled. Jay Ess li...@netrogenic.com wrote: On 2013-06-26 18:31, nixofortune wrote: What would be the best way to convert BIG MyISAM table into InnoDB? We do not have SLAVE. I would do it on another computer. Then copy the table to the server and then add the data that has been added from the original table. And/or i would experiment with TokuDB. I havent had the time to do it myself but will probably soon. I am too looking for a lengthy 1 billion+ row conversion. -- Sent from Kaiten Mail. Please excuse my brevity. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: space gone after MyISAM REPAIR TABLE
On 2013-06-27 01:27, nixofortune wrote: Now importing with Keys in place. It takes longer, much longer but at least the server is working and customers do not complaint. Schema design is awful, agree. I try to understand the process so will redesign it soon, but any suggestions are welcome. I' not a MySQL super guru so will be glad for hear your sorts, guys. Thanks You could probably reduce your table size a LOT by breaking out keyword and source to their own tables and reference them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: space gone after MyISAM REPAIR TABLE
You can use disable Key before loading data use enable Key after loading
Command :- alter table {table. Name } disable key;
Same enabling
DK Sent from Phone
On 27-Jun-2013, at 4:57, nixofortune nixofort...@gmail.com wrote:
This is my table:
CREATE TABLE `ga_monthly_keyword_visits` (
`site_id` int(11) DEFAULT NULL,
`index_date` int(11) DEFAULT NULL,
`index_month` int(11) NOT NULL,
`index_year` int(11) NOT NULL,
`keyword` varchar(128) DEFAULT NULL,
`source` varchar(30) DEFAULT NULL,
`visits` int(11) DEFAULT NULL,
`bounced_visits` int(11) DEFAULT NULL,
`transactions` int(11) DEFAULT NULL,
`revenue` float(10,2) DEFAULT NULL,
`value_per_click` float(10,2) DEFAULT NULL,
`conversions` int(11) DEFAULT NULL,
`goal_value` float(10,2) DEFAULT NULL,
KEY `idx_bounced_visits` (`site_id`,`index_date`),
KEY `idx_visits_month` (`site_id`,`keyword`,`index_month`,`index_year`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Was changed into:
CREATE TABLE `ga_monthly_keyword_visits` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`site_id` int(11) DEFAULT NULL,
`index_date` int(11) DEFAULT NULL,
`index_month` int(11) NOT NULL,
`index_year` int(11) NOT NULL,
`keyword` varchar(128) DEFAULT NULL,
`source` varchar(30) DEFAULT NULL,
`visits` int(11) DEFAULT NULL,
`bounced_visits` int(11) DEFAULT NULL,
`transactions` int(11) DEFAULT NULL,
`revenue` float(10,2) DEFAULT NULL,
`value_per_click` float(10,2) DEFAULT NULL,
`conversions` int(11) DEFAULT NULL,
`goal_value` float(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
KEY `idx_bounced_visits` (`site_id`,`index_date`),
KEY `idx_visits_month` (`site_id`,`keyword`,`index_month`,`index_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
I tried Converting like this:
CREATE TABLE new LIKE old;
ALTER TABLE new DROP (all indexes), ADD PRIMARY KEY (either autoincrement
or a 'natural' compound PK), ENGINE=InnoDB;
INSERT INTO new SELECT site_id, ..., goal_value FROM old;
ALTER TABLE new ADD INDEX (...);
With only difference The original MyISAM table crashed and I took it from
backup, loading by LOAD DATA INFILE.
The problem, yes It loaded much quicker into Database 4H 16M to be precise,
but ALTER TABLE new ADD INDEX (...);
Put server into meditation mode. High I/O Wait rendered box unusable. I had
to interrupt the ALTER ADD KEY process after 5H of struggle.
Now importing with Keys in place. It takes longer, much longer but at least
the server is working and customers do not complaint.
Schema design is awful, agree. I try to understand the process so will
redesign it soon, but any suggestions are welcome.
I' not a MySQL super guru so will be glad for hear your sorts, guys.
Thanks
On 27/06/13 00:04, Rick James wrote:
(`site_id`,`index_date`,`index_month`,`index_year`,`keyword`,`source`,`visits`,
`bounced_visits`,`transactions`,`revenue`,`value_per_click`,`conversions`,`goal_value`);
May we see the SHOW CREATE TABLE? Some of this smells bad.
* It is almost always bad to split day/month/year into multiple fields.
* Often a fact table, which this sounds like, should not have extra
indexes.
* Is each datatype as small as is practical?
* Are any of the fields VARCHAR, and could be 'normalized'?
I would expect this to the fastest way to convert (assuming you have the
disk space):
CREATE TABLE new LIKE old;
ALTER TABLE new DROP (all indexes), ADD PRIMARY KEY (either autoincrement
or a 'natural' compound PK), ENGINE=InnoDB;
INSERT INTO new SELECT site_id, ..., goal_value FROM old;
ALTER TABLE new ADD INDEX (...);
What version of MySQL are you running? Newer versions do the ALTER TABLE
faster (online??), and may require you to do one add at a time.
Another issue...
If the data in `old` is in the same order as the PRIMARY KEY of `new`, then
INSERT..SELECT will run fast. (No need to jump around to find where to put
each row.)
Case 1: You are adding an AUTO_INC -- it will be in the 'right' order.
Case 2: The new PK is approximately the order of the insertions into `old`
-- probably run fast. (However, I do not see a likely natural PK that would
allow this
INSERT ... SELECT...ORDER BY (new PK) -- This would make the INSERT part
fast, but the SELECT part would be slow. (You can't win)
Your task is all about disk hits. By understanding what MySQL has to do,
you can 'predict' whether a plan will be slow or slower.
Back to the secondary indexes...
What are the SELECTs that will benefit from them? (Sometimes discussing
this can lead to fewer/better INDEXes. Often it leads to suggesting Summary
Table(s).)
-Original Message-
From: Johan De Meersman [mailto:vegiv...@tuxera.be]
Sent: Wednesday, June 26, 2013 11:46 AM
To: li...@netrogenic.com; Jay Ess; mysql@lists.mysql.com
Subject: Re: space gone after MyISAM REPAIR TABLE
You can't actually move innodb tables around until 5.6 where you have
transpotable
RE: space gone after MyISAM REPAIR TABLE
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
Re: space gone after MyISAM REPAIR TABLE
- 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 -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: space gone after MyISAM REPAIR TABLE
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 signature.asc Description: OpenPGP digital signature
Re: space gone after MyISAM REPAIR TABLE
Oops, totally missed that, thanks. Reindl Harald h.rei...@thelounge.net 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 -- Sent from Kaiten Mail. Please excuse my brevity.
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
