size of ibdata1 file

I got paged at home last night because all mail delivery had
stopped. It
turns out that the ibdata1 file (default value of
innodb_data_file_path
MySQL configuration variable) had grown to more than 20GB in
size,
filling up the DRBD-shared partition. The maillog file was
full of
"maia_mail table is full" errors from amavisd.

My question is, is this expected behavior? I note that the
files in
the /var/lib/mysql/maia directory are relatively small, so
with InnoDB
tables, is all the data actually stored in the
innodb_data_file_path
files?  We have about 1200 users on site and handle perhaps
2 million
messages a month; is 20GB a size that I should have expected
us to hit?
(It's been a little over a year).

I got my system working again by following the directions
at:

http://dev.mysql.com/doc/refman/5.0/en/adding-and
-removing.html

(I was lucky enough to hit on this with a Google search)

So now I have a second ibdata2 file, but it no longer
resides on my
DRBD-shared partition, so I no longer have high availability
and I will
have to take the system down and do some repartitioning; I
suspect that
creating a second DRBD-shared partition will probably be
easier than
trying to grow the existing one. I realize that 20GB isn't
that large a
partition, but at the time it seemed like more than enough.
When sharing
partitions with DRBD, the partition size is directly
proportional to how
long it takes to do a resync over the network, so I don't
want to make
it any bigger than I have to.

--Greg



_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

All mail quarantined or cached is stored in the maia_mail
table so it
can get large on a high traffic site. Your results seem
about 2-5 times
what I would expect given your user count. However, this all
depends on
your settings. For example, we clear out old email weekly
and only scan
messages under 1MB.

Keeping mail longer, caching spam and non-spam, and
quarantining large
messages will all add up in your database. I suggest you
re-evaluate
these settings based on your needs.

-Blake

Greg Woods wrote:
> I got paged at home last night because all mail
delivery had stopped. It
> turns out that the ibdata1 file (default value of
innodb_data_file_path
> MySQL configuration variable) had grown to more than
20GB in size,
> filling up the DRBD-shared partition. The maillog file
was full of
> "maia_mail table is full" errors from
amavisd.
>
> My question is, is this expected behavior? I note that
the files in
> the /var/lib/mysql/maia directory are relatively small,
so with InnoDB
> tables, is all the data actually stored in the
innodb_data_file_path
> files?  We have about 1200 users on site and handle
perhaps 2 million
> messages a month; is 20GB a size that I should have
expected us to hit?
> (It's been a little over a year).
>
> I got my system working again by following the
directions at:
>
> http://dev.mysql.com/doc/refman/5.0/en/adding-and
-removing.html
>
> (I was lucky enough to hit on this with a Google
search)
>
> So now I have a second ibdata2 file, but it no longer
resides on my
> DRBD-shared partition, so I no longer have high
availability and I will
> have to take the system down and do some
repartitioning; I suspect that
> creating a second DRBD-shared partition will probably
be easier than
> trying to grow the existing one. I realize that 20GB
isn't that large a
> partition, but at the time it seemed like more than
enough. When sharing
> partitions with DRBD, the partition size is directly
proportional to how
> long it takes to do a resync over the network, so I
don't want to make
> it any bigger than I have to.
>
> --Greg
>
>
>
> _______________________________________________
> Maia-users mailing list
> Maia-usersrenaissoft.com
> http://www.renaissoft.com/mailman/listinfo/maia-users
>   

_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users