InnoDB file too big

Hi all,

Help needed. 
I have about 5000 mailboxes in mail system. I use maia for
some time. But some 
day ago my mail system was stopped because ibdata1 file has
grown till the 
big size and has borrowed all accessible place at HDD.  I
have checked up 
maia and have seen that users didn't check the Uncorfirmed
Non-Spam. But 
after confirm some of these mails ibdata1 size was not
changed. How i can 
clean these messages and what i will do that this file will
not grow?

-- 
Best regards,

Anatoliy Chernov
System Administrator
ISP TPS
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Anatoliy Chernov wrote:
> Hi all,
> 
> Help needed. 
> I have about 5000 mailboxes in mail system. I use maia
for some time. But some 
> day ago my mail system was stopped because ibdata1 file
has grown till the 
> big size and has borrowed all accessible place at HDD. 
I have checked up 
> maia and have seen that users didn't check the
Uncorfirmed Non-Spam. But 
> after confirm some of these mails ibdata1 size was not
changed. How i can 
> clean these messages and what i will do that this file
will not grow?

(1) First, make sure that you are running the
process-quarantine.pl
script frequently (e.g. once per hour), and the
expire-quarantine-cache.pl script once per day.  These
scripts are
responsible for managing the confirmed and unconfirmed items
in the
database, so that it does not grow endlessly.

(2) Second, reduce your expiry periods (on the
Admin->System
Configuration page).  The default setting retains
quarantined items for
30 days and non-spam items are cached for 14 or so, which is
probably
too much for your combination of disk space and traffic
volume.  Reduce
these values to something like 3-5 days, then run the
expire-quarantine-cache.pl script, which will purge any
items older than
this.

(3) Third, to shrink your ibdata1 file down to a more
reasonable size,
use the OPTIMIZE TABLE command for all of the affected
tables.  The
maia_mail table in particular will benefit from this, but if
you want to
be cautious you can run OPTIMIZE TABLE on all of the tables
if you like.

- --
Robert LeBlanc <rjlrenaissoft.com>
Renaissoft, Inc.
Maia Mailguard <http://www.maiamail
guard.com/>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGVKYvGmqOER2NHewRAonRAJwImrlWvnt9QTWZf/UlX/Pa2BWDbACe
OGUU
seVrwYZAHvq1GLN5aGQq0Y8=
=UQ3c
-----END PGP SIGNATURE-----
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Robert LeBlanc wrote:

> (3) Third, to shrink your ibdata1 file down to a more
reasonable size,
> use the OPTIMIZE TABLE command for all of the affected
tables.  The
> maia_mail table in particular will benefit from this,
but if you want to
> be cautious you can run OPTIMIZE TABLE on all of the
tables if you like.

Just to amend this a bit, it has been pointed out (thanks
Ed!) that with
InnoDB tables the OPTIMIZE TABLE command does not perform
exactly as
advertised in the MySQL docs.  In fact, while it does
optimize the
tables and indexes it does not reduce the size of the
ibdata1 file, it
simply uses whatever free space remains in that file to
create a new
copy of the table being optimized.  This means that if
there's not
enough available space inside the existing ibdata1 file for
the second
copy, it can actually take up /more/ space by forcing the
ibdata1 file
to grow.

Given that fact, I can only think of a few other ways to
actually reduce
the size of the ibdata1 file:

(1) Another MySQL user suggested a little trick that
supposedly
optimizes an InnoDB table: use ALTER TABLE to remind it that
it's an
InnoDB table, i.e. ALTER TABLE tablename ENGINE=InnoDB.  I
have my
doubts about this one, since this sounds just like what
OPTIMIZE TABLE
does on InnoDB tables.

(2) Yet another MySQL user suggested that you could try
changing the
table type from InnoDB to MyISAM temporarily (e.g. ALTER
TABLE tablename
ENGINE=MyISAM), then use OPTIMIZE TABLE on these MyISAM
tables, since it
/will/ reduce the size of MyISAM tables.  Then, once this is
done you
can change the table type back to InnoDB (i.e. ALTER TABLE
tablename
ENGINE=InnoDB).  Since your ibdata1 file should end up
getting deleted
after all your InnoDB tables are converted to MyISAM, the
new ibdata1
file that gets created during the conversion back to InnoDB
should have
no empty space in it, so this method looks promising.

(3) If all else fails, you can use mysqldump to dump the
contents of
your database to another drive/partition, then reload the
data from the
dump file into an new, empty database.  Note that the
ibdata1 file
contains /all/ of the tables and indexes that use InnoDB
tables, so if
you use InnoDB tables for other databases (not just the Maia
database),
you'll need to use mysqldump to back those up as well.  When
you
reimport the dump files into an empty ibdata1 file it should
end up
being much smaller than your original, since it will not
contain any
empty space.

- --
Robert LeBlanc <rjlrenaissoft.com>
Renaissoft, Inc.
Maia Mailguard <http://www.maiamail
guard.com/>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGVLtpGmqOER2NHewRApBZAKCTllHj67QjK3U4DgZjVIeGtKbj5QCg
oDFP
eR1XLedGSUnFBdOAXEXcRAc=
=8wqs
-----END PGP SIGNATURE-----
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users