On 03/21/13 04:27, Uwe Schuerkamp wrote:
> Hi folks,
> 
> last night I managed to shrink a 300GB File table down to a very
> reasonable 85GB by exporting, dropping and re-importing the table
> (about 500 million rows). 
> 
> Dumping the table on an idle bacula server (see earlier posts for
> machine specs) took around 90 minutes and resulted in a 55GB sql
> file. Dropping the table was a breeze (about a minute or so),
> re-importing took around 3 hours: 
> 
> 
> date ; time mysql -pn1onex bacula <  file_2013_03_20.sql  ; date
> Mi 20. Mär 22:35:14 CET 2013
> 
> real    165m12.748s
> user    15m17.358s
> sys     1m2.320s
> Do 21. Mär 01:20:27 CET 2013
> 
> Post-Import File table size: 
> 
> -rw-rw---- 1 mysql mysql 8,7K 20. Mär 22:35 File.frm
> -rw-rw---- 1 mysql mysql  83G 21. Mär 01:20 File.ibd

Try this for a faster method:

CREATE TABLE NewFile LIKE File;
INSERT INTO NewFile (SELECT * FROM File);
DROP TABLE File;
RENAME TABLE NewFile TO File;



-- 
  Phil Stracchino, CDK#2     DoD#299792458     ICBM: 43.5607, -71.355
  alaric AT caerllewys DOT net   alaric AT metrocast DOT net   phil AT 
co.ordinate DOT org
  Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
                 It's not the years, it's the mileage.

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_mar
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users