Mailing List Archive

Assistance with DB (mysql) size optimisation
Hi All,

We are running dbmail 2.2.18 for some legacy clients, the database is a debian default 5.1.73. We have around 260 mailboxes on this server that (when dbmail-export’ed) comprise of 72G of data, or approx 280MB per mailbox on average. We are running "/usr/local/sbin/dbmail-util -ay” every morning at 3am.

The problem we have is that the mysql data store is around 680G with the dbmail_messageblks.ibd file being the culprit at 654G. 

Is there any way to claw back this space?

Thanks!

Simon
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
Further to this - sorry i just looked at the archive and saw the "[Dbmail] database overhead” subject that has been discussed just recently… 

We have "innodb_file_per_table = 1”, so am i correct that i should run "optimize table” on all the tables, then also enable InnoDB Barracuda and do an ALTER TABLE  Engine=InnoDB ROW_FORMAT=COMPRESSED?

Thanks

Simon


On 3 March 2016 at 9:30:50 AM, Simon Buchanan (greminn@gmail.com) wrote:

Hi All,

We are running dbmail 2.2.18 for some legacy clients, the database is a debian default 5.1.73. We have around 260 mailboxes on this server that (when dbmail-export’ed) comprise of 72G of data, or approx 280MB per mailbox on average. We are running "/usr/local/sbin/dbmail-util -ay” every morning at 3am.

The problem we have is that the mysql data store is around 680G with the dbmail_messageblks.ibd file being the culprit at 654G. 

Is there any way to claw back this space?

Thanks!

Simon
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
Am 02.03.2016 um 21:30 schrieb Simon Buchanan:
> Hi All,
>
> We are running dbmail 2.2.18 for some legacy clients, the database is a
> debian default 5.1.73. We have around 260 mailboxes on this server that
> (when dbmail-export’ed) comprise of 72G of data, or approx 280MB per
> mailbox on average. We are running "/usr/local/sbin/dbmail-util -ay”
> every morning at 3am.
>
> The problem we have is that the mysql data store is around 680G with
> the dbmail_messageblks.ibd file being the culprit at 654G.
>
> Is there any way to claw back this space?

first consider migartion to a more recent dbmail because
single-instance-storage will deduplicate mime-parts and so any
compression and other optimizing afterwards will be much more efficient
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
On 3 March 2016 at 9:42:03 AM, Reindl Harald (h.reindl@thelounge.net) wrote:

Am 02.03.2016 um 21:30 schrieb Simon Buchanan: 

> Is there any way to claw back this space? 

first consider migartion to a more recent dbmail because 
single-instance-storage will deduplicate mime-parts and so any 
compression and other optimizing afterwards will be much more efficient 
Thanks for the feedback here. For us dbmail is our legacy platform and we are not adding new clients to this, in fact we are removing clients every month. Do you still think its worth updating? an easy process?
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
Am 02.03.2016 um 21:47 schrieb Simon Buchanan:
> On 3 March 2016 at 9:42:03 AM, Reindl Harald (h.reindl@thelounge.net
> <mailto:h.reindl@thelounge.net>) wrote:
>
>> Am 02.03.2016 um 21:30 schrieb Simon Buchanan:
>>
>> > Is there any way to claw back this space?
>>
>> first consider migartion to a more recent dbmail because
>> single-instance-storage will deduplicate mime-parts and so any
>> compression and other optimizing afterwards will be much more efficient
>
> Thanks for the feedback here. For us dbmail is our legacy platform and
> we are not adding new clients to this, in fact we are removing clients
> every month. Do you still think its worth updating? an easy process?

sicne i built a ton of infrastructure around o dbmail i can't answer
that for you - dbmail 3.0 upgrade here was years ago
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
On 02/03/16 20:30, Simon Buchanan wrote:
> Hi All,
>
> We are running dbmail 2.2.18 for some legacy clients, the database is a
> debian default 5.1.73. We have around 260 mailboxes on this server that
> (when dbmail-export’ed) comprise of 72G of data, or approx 280MB per
> mailbox on average. We are running "/usr/local/sbin/dbmail-util -ay”
> every morning at 3am.
>
> The problem we have is that the mysql data store is around 680G with
> the dbmail_messageblks.ibd file being the culprit at 654G.
>
> Is there any way to claw back this space?

I would try restoring a dump of dbmail to an empty database with perhaps
100GB innodb space allocated (plus auto expand option).

Our setup is quite similar in scale to yours dbmail 2.2.18, mysql
5.1.63, ~ 200 user accounts and we run the same nightly dbmail-util
options. Our mysql install is standalone (on CentOS) rather than distro
package but it seems unlikely this should make a difference.

Our total innodb space usage is around 63GB (2 files, not file per
table). A dump of the database uncompressed is 54GB.
This is consistent with the 20% space overhead in the discussion thread
you referenced.

I think when we last did a dump and restore around a 15 months back we
gained back ~ 20GB, but if you've been running without for many years
and with your current factor of 9 overhead you may gain much more.


_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
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.

---

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-02 21:36 Simon Buchanan ha scritto:

> Further to this - sorry i just looked at the archive and saw the "[Dbmail] database overhead" subject that has been discussed just recently...
>
> We have "innodb_file_per_table = 1", so am i correct that i should run "optimize table" on all the tables, then also enable InnoDB Barracuda and do an ALTER TABLE Engine=InnoDB ROW_FORMAT=COMPRESSED?
>
> Thanks
>
> Simon
>
> On 3 March 2016 at 9:30:50 AM, Simon Buchanan (greminn@gmail.com) wrote:
>
>> Hi All,
>>
>> We are running dbmail 2.2.18 for some legacy clients, the database is a debian default 5.1.73. We have around 260 mailboxes on this server that (when dbmail-export'ed) comprise of 72G of data, or approx 280MB per mailbox on average. We are running "/usr/local/sbin/dbmail-util -ay" every morning at 3am.
>>
>> The problem we have is that the mysql data store is around 680G with the dbmail_messageblks.ibd file being the culprit at 654G.
>>
>> Is there any way to claw back this space?
>>
>> Thanks!
>>
>> Simon
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
On 3 March 2016 at 11:36:59 PM, Andrea Brancatelli (abrancatelli@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
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
Am 03.03.2016 um 22:08 schrieb Simon Buchanan:
>
> On 3 March 2016 at 11:36:59 PM, Andrea Brancatelli
> (abrancatelli@schema31.it <mailto:abrancatelli@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?)

on a modern setup (MariaDB 10.0.x) they are not locked
in your case - yes, they are
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
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
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 (abrancatelli@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
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
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
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
> 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 (abrancatelli@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
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
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 <abrancatelli@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
> 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
> 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 (
> abrancatelli@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
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
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 (cmaurand@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 <abrancatelli@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
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
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 (abrancatelli@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
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
5.1 is terrible slow compared to 5.5 with optimized configs (see list archive) and with 5.6 you would type just "optimize table" online regularly to prevent get database sizes where no enough space is left

in other words: you should have upgraded years ago, can't remember when it was here, in the meantime switched to mariadb 5.5 and last year upgraded to mariadb 10


-------- Ursprüngliche Nachricht --------
Von: Simon Buchanan <greminn@gmail.com>
Gesendet: 07. März 2016 02:22:24 MEZ
An: DBMail mailinglist <dbmail@dbmail.org>
Betreff: Re: [Dbmail] Assistance with DB (mysql) size optimisation

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 (cmaurand@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 <abrancatelli@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
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
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 (abrancatelli@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

_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
Thanks for the feedback Reindl


On 7 March 2016 at 7:19:57 PM, Reindl Harald (mobile) (h.reindl@thelounge.net) wrote:

5.1 is terrible slow compared to 5.5 with optimized configs (see list archive) and with 5.6 you would type just "optimize table" online regularly to prevent get database sizes where no enough space is left

in other words: you should have upgraded years ago, can't remember when it was here, in the meantime switched to mariadb 5.5 and last year upgraded to mariadb 10


-------- Ursprüngliche Nachricht --------
Von: Simon Buchanan <greminn@gmail.com>
Gesendet: 07. März 2016 02:22:24 MEZ
An: DBMail mailinglist <dbmail@dbmail.org>
Betreff: Re: [Dbmail] Assistance with DB (mysql) size optimisation

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 (cmaurand@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 <abrancatelli@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
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
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 (abrancatelli@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
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
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 (cmaurand@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 <abrancatelli@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 (abrancatelli@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
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
Don’t forget this is mysql 5.1...

On 8 March 2016 at 10:21:45 PM, Andrea Brancatelli (abrancatelli@schema31.it) wrote:

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
Re: Assistance with DB (mysql) size optimisation [ In reply to ]
Actually your reply here made me re-think. We are using Percona 5.1, so upgrading to 5.5 then 5.6 - so downtime of 5mins. Then using pt-online-schema-change to perform the ALTER the structure without blocking reads or writes.

Works a treat!

On 8 March 2016 at 10:21:45 PM, Andrea Brancatelli (abrancatelli@schema31.it) wrote:

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 :-)