Re: curmail_size vs innodb disk usage

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. If you have this space, why would you want to 
'reclaim' space from MySQL?

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.

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. 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.