Re: curmail_size vs innodb disk usage

Am 19.03.2015 um 10:13 schrieb Casper Langemeijer:
> On 19-03-15 04:21, Simon wrote:
>>
>> If the later, I would really appreciate some pointers to deal with
>> reclaiming the space...?
>>
> As others suggested, optimize table (or the mysqloptimize command line
> utility) can help you 'reclaim' space.
>
> But you shouldn't use it unless you really need to because:
>
> 1. Optimize table rewrites the entire table into a new file. This means
> you'll need about 110G-150G of storage space to be able to perform this
> action in the first place

sadly yes, maybe it's too late

> If you have this space, why would you want to
> 'reclaim' space from MySQL?

beause if he would have done that earlier disk now would not run full...

> 2. In the process the table will be locked, and rewriting 110G of data
> will take a while. In this time you won't be able to write to this
> table. Every new message delivery will cause a new MySQL thread waiting
> for the table optimize to be over. Depending on how many incoming emails
> you get, you could run out of connections (see max_connection) and your
> reading clients (pop3) won't be able to connect.

before MySQL 5.6 it's easy to weight: just shutdown mailservices for 
that time because it affects also sent-mail and drafts

> 3. Unless you are using the MySQL server for something else too (you
> shouldn't) the space is not really wasted. The dbmail_messageblks.ibd
> file is managed by innodb and contains empty spaces. They get filled
> with new emails. Until your server reaches 500GB of messageblks data,
> the file won't grow

that's not true, there is overhead too

> In your installation this has happened before, and
> you certainly want to reserve the space for it. It doesn't matter if
> this reserved space is in the idb file, or available as free space on
> the file system.

not true - in many cases it affects backups including deduplication

_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlUKloQACgkQhmBjz394AnkkWwCfdTuoFyGcg80airPGUp+UU659
N0AAn3VwQmB9LCZzz5yF6uGZ6Hui4xZw
=4ycJ
-----END PGP SIGNATURE-----