Mailing List Archive

"optimize table" is your friend in case you have files per table enabled - before mysql 5.6 the table is locked while rebuild is running - innodb never reclaims space


-------- Ursprüngliche Nachricht --------
Von: Simon <greminn@gmail.com>
Gesendet: 19. März 2015 04:21:52 MEZ
An: DBMail mailinglist <dbmail@dbmail.org>
Betreff: [Dbmail] curmail_size vs innodb disk usage

Hi There,

We are using dbmail (2.2.18) on debian with a (separate) mysql 5.1 backend
(master/slave with innodb_file_per_table enabled). We use innobackupex for
nightly backups.

Currently a SUM(curmail_size) of all mailboxes gives us about 110GB.. yet
our dbmail_messageblks.ibd file is nearly 500GB and the mysql data space is
just about full.

Is this something that dbmail is not reporting correctly "curmail_size" or
is this just innodb (e.g. does not reclaim the space).

If the later, I would really appreciate some pointers to deal with
reclaiming the space...?

Many thanks,

Simon


------------------------------------------------------------------------

_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


--

Reindl Harald (mobile)
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
+43 (676) 40 221 40
http://www.thelounge.net
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Am 19.03.2015 um 04:21 schrieb Simon:
> Hi There,
>
> We are using dbmail (2.2.18) on debian with a (separate) mysql 5.1
> backend (master/slave with innodb_file_per_table enabled). We
> use innobackupex for nightly backups.
>
> Currently a SUM(curmail_size) of all mailboxes gives us about 110GB..
> yet our dbmail_messageblks.ibd file is nearly 500GB and the mysql data
> space is just about full.
>
> Is this something that dbmail is not reporting correctly "curmail_size"
> or is this just innodb (e.g. does not reclaim the space).
>
> If the later, I would really appreciate some pointers to deal with
> reclaiming the space...?
>
> Many thanks,
>
> Simon
>
Hi.
Not an expert on mysql, but on postgres you run vacuumdb to reclaim
space. A quick search gave me this:

http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

Should be about what you need, but I am sure the mysql/mariadb users can
help you more on that if needed.

Regards,
Thomas R

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.

_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

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

On Thu, Mar 19, 2015 at 10:27 PM, Reindl Harald <h.reindl@thelounge.net>
wrote:

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


I can provision new space for the device, so not too late :)


> 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


I can use (percona tools) pt-online-schema-change todo the same thing as
Optimize, but without locking.

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


Yes.. in this case there is about 100MB of free data (SUM of Data_free),
and the tables come to 500GB (SUM of SUM of Data_length).

I performed a mysqldump of the entire DB (there is only the one database on
the server, except for mysql that is), and the size of the file is 393G..
so the difference between the database size and the dump file is approx
100GB.

What im trying to understand is why is the SUM of curmail_size is approx
110GB, yet the database size (even dump'ed) is 400GB. Surely dbmail does
not have that much overhead in its database?

Thanks!

Simon

Am 19.03.2015 um 21:16 schrieb Simon:
> What im trying to understand is why is the SUM of curmail_size is approx
> 110GB, yet the database size (even dump'ed) is 400GB. Surely dbmail does
> not have that much overhead in its database?

mailsize has barely to do with database size
completly different worlds

no database at all gives back space just because data are removed
because the overhead would be way too large, be it mysql, berkely db,
postgresql or something else - they all try to re-use allocated space as
good as possible without vaccum all the time

On Fri, Mar 20, 2015 at 9:31 AM, Reindl Harald <h.reindl@thelounge.net>
wrote:

>
> Am 19.03.2015 um 21:16 schrieb Simon:
>
>> What im trying to understand is why is the SUM of curmail_size is approx
>> 110GB, yet the database size (even dump'ed) is 400GB. Surely dbmail does
>> not have that much overhead in its database?
>>
>
> mailsize has barely to do with database size
> completly different worlds
>
> no database at all gives back space just because data are removed because
> the overhead would be way too large, be it mysql, berkely db, postgresql or
> something else - they all try to re-use allocated space as good as possible
> without vacuum all the time
>

OK thanks for the clarification on this. I just seems completely out of
wack that the DB would use 4 times the amount of space for the actual data
to me :)

One of the reasons we have moved away from dbmail for our primary
platform...

Simon

Am 19.03.2015 um 21:54 schrieb Simon:
> On Fri, Mar 20, 2015 at 9:31 AM, Reindl Harald wrote:
>
>
> Am 19.03.2015 um 21:16 schrieb Simon:
>
> What im trying to understand is why is the SUM of curmail_size
> is approx
> 110GB, yet the database size (even dump'ed) is 400GB. Surely
> dbmail does
> not have that much overhead in its database?
>
>
> mailsize has barely to do with database size
> completly different worlds
>
> no database at all gives back space just because data are removed
> because the overhead would be way too large, be it mysql, berkely
> db, postgresql or something else - they all try to re-use allocated
> space as good as possible without vacuum all the time
>
> OK thanks for the clarification on this. I just seems completely out of
> wack that the DB would use 4 times the amount of space for the actual
> data to me :)
>
> One of the reasons we have moved away from dbmail for our primary
> platform...

no system is without drawbacks

if the size of the database is your largest problem you should ask
youself why still use dbmail 2.2 without the single-instance-storage and
a outdated mysql 5.1

* dbmail 3.x stores eachmessagepart once with references
and if it is identical for several messages you need
the sapce only once
* mysql 5.5 supports innodb compression
* mysql 5.6 / MariaDB 10.x even supports optimize table without locking

in fact all of your storage problems are coming from using legacy
software versions and in any case if storage is your primary problem you
made mistakes before

On 19-03-15 21:16, Simon wrote:
>
> What im trying to understand is why is the SUM of curmail_size is
> approx 110GB, yet the database size (even dump'ed) is 400GB. Surely
> dbmail does not have that much overhead in its database?
As Reindl stated, why still use dbmail 2.2 without the
single-instance-storage and a outdated mysql 5.1?

In our setup we get curmail size well below the actual disk usage
because people tend to email each other the same signature image over
and over, and in the new dbmail these are stored only once.

Still, 400G is a little too much for me compaired to the 110G curmail
size. My guess is that your sql dump files are very verbose (use a
statement per row, thus containing a lot of SQL language overhead) or
you should make sure your dbmail-util (see
http://linux.die.net/man/8/dbmail-util ) cron job is running.

Have you ran innodb table optimization yet? What is the new size?
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail