Re: Assistance with DB (mysql) size optimisation
"Reindl Harald (mobile)" <[email protected]> Mon, 07 Mar 2016 07:20:06 +0100
| Newsgroups | gmane.mail.imap.dbmail |
|---|---|
| Message-ID | <[email protected]> |
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 <[email protected]> Gesendet: 07. März 2016 02:22:24 MEZ An: DBMail mailinglist <[email protected]> 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 ([email protected]) 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 <[email protected]> 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 ([email protected]) 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 [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail -- --Curtis _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail ------------------------------------------------------------------------ _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail