As a side node that will require longer time and more space than the ALTER TABLE Engine=InnoDB ROW_FORMAT_COMPRESSED, as you will have to read the table to write the dump, and then read the dump to write the table, opposed to reading the table to write the new one.
Furthermore unless you do online gzipping or such the dump will require more space than the new, compressed, table. But, hey, it's your server :-) --- Andrea Brancatelli Schema31 S.p.a. Responsabile IT ROMA - BO - FI - PA ITALY Tel: +39. 06.98.358.472 Cell: +39 331.2488468 Fax: +39. 055.71.880.466 Società del Gruppo SC31 ITALIA Il 2016-03-07 02:22 Simon Buchanan ha scritto: > Thanks for the replies on this. After consideration, and the fact that this > is a "best effort" service for us, I'm going to take it offline mysqldump and > re-import. > > Mysql is currently on 5.1, is it worth upgrading to 5.5, 5.6 or 5.7? > > On 5 March 2016 at 1:53:08 AM, Curtis Maurand (cmaur...@gmail.com) wrote: > > On a 650 GB table, the OPTIMIZE is going to take quite a while. > > On Fri, Mar 4, 2016 at 3:57 AM, Andrea Brancatelli <abrancate...@schema31.it> > wrote: > > Sorry I forgot you're on mysql 5.1. > > Maybe it will lock the table. Just run the update deep in the night... > > --- > > Andrea Brancatelli > Schema31 S.p.a. > Responsabile IT > > ROMA - BO - FI - PA > ITALY > Tel: +39. 06.98.358.472 > Cell: +39 331.2488468 [1] > Fax: +39. 055.71.880.466 > Società del Gruppo SC31 ITALIA > > Il 2016-03-04 09:53 Andrea Brancatelli ha scritto: > > Alter doesn't lock the table - but it will slow down the server. > > If you're starting with 650GB table I expect you to come out with, more or > less 200GB after the compression. > > Obviously yes, you need the space for the new table as the process of alter > is creating the new table and then dropping the old one. > > --- > > Andrea Brancatelli > Schema31 S.p.a. > Responsabile IT > > ROMA - BO - FI - PA > ITALY > Tel: +39. 06.98.358.472 > Cell: +39 331.2488468 [1] > Fax: +39. 055.71.880.466 > Società del Gruppo SC31 ITALIA > > Il 2016-03-03 22:08 Simon Buchanan ha scritto: > > On 3 March 2016 at 11:36:59 PM, Andrea Brancatelli (abrancate...@schema31.it) > wrote: > > Yes you can proceed easily with those two settings and have a first bigger > improvement. > > If you're switching away from dbmail I wouldn't go trough the hassle of > upgrading, since the upgrade need to reparse all the message to use the > de-deduplication features. > > Note: the ALTER TABLE will perform the OPTIMIZE TABLE, so you don't need to > run the two commands, just go for the ALTER TABLE. > > Thanks for the reply - my thoughts with the upgrade. The issue with OPTIMISE > TABLE is that it needs the same amount of space to do the optimise, plus the > tables are write locked whist this happens (is this correct?). Thats going to > take a while on a 650GB table :) with live data on it. > > Does Alter table write lock as well? > > PS: also - what do i alter... > > Simon > > _______________________________________________ > DBmail mailing list > DBmail@dbmail.org > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail _______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail _______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail -- --Curtis _______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail _______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail Links: ------ [1] tel:%2B39%20331.2488468
_______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
