1

Topic: Halt growth of Mysql ibdata1 & /tools/cleanup_amavisd_db.py

==== Required information ====
- iRedMail version:
- Store mail accounts in which backend (LDAP/MySQL/PGSQL):
- Linux/BSD distribution name and version:
- Related log if you're reporting an issue:
======== Required information ====
- iRedMail version: 0.8.6
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): mysql
- Linux/BSD distribution name and version: centos 6.4
- Related log if you're reporting an issue:
====

My /var/lib/mysql/ibdata1 file has seem to have grown unwieldy. After googling, it appears you cannot recover the space without dumping all DBs deleting the file then restoring.

This it not an ideal scenario considering we're in a mysql master-master configuration with over 40k users.

At the very least I''m probably going to move these mail servers to larger chassis, however I want to keep this file from growing into the new space.

# du -lsh /var/lib/mysql/ibdata1
53G     /var/lib/mysql/ibdata1

Again, with more googling I've found the below script that cleans out the amavisd database, the problem is that the when i execute the script on  a clone our production servers it fails with "error".

1. Is there away to reduce the amount of data it deletes so that it removes smaller chunk as opposed to everything at once? Ideally I'd like to run the script several many times till I get it down to where the script won't fail and I can set it up as a cron job as instructed. I've changed it to the following as instructed but i still get an error

AMAVISD_REMOVE_MAILLOG_IN_DAYS = 360
AMAVISD_REMOVE_QUARANTINED_IN_DAYS = 360

# python /var/www/iredadmin/tools/cleanup_amavisd_db.py
* [2014-10-01 13:56:42] Backend: mysql
* [2014-10-01 13:56:42] Remove records of quarantined mails older than 360 days.
* [2014-10-01 13:56:42] Remove records of sent/received mails older than 360 days.
* [2014-10-01 13:56:42] Delete quarantined mails which older than 360 days
Traceback (most recent call last):
  File "/var/www/iredadmin/tools/cleanup_amavisd_db.py", line 73, in <module>
    ''' % settings.AMAVISD_REMOVE_QUARANTINED_IN_DAYS
  File "/usr/lib/python2.6/site-packages/web/db.py", line 644, in query
    self._db_execute(db_cursor, sql_query)
  File "/usr/lib/python2.6/site-packages/web/db.py", line 587, in _db_execute
    out = cur.execute(query, params)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1206, 'The total number of locks exceeds the lock table size')

2. Will setting this script as a cron job keep the /var/lib/mysql/ibdata1 file from growing like it currently is?
/var/www/iredadmin/tools/cleanup_amavisd_db.py

3. If the script cant cut it, can I just truncate the tables for a quick fix and let amavisd rebuild on its own? If so which tables can I truncate safely?

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2

Re: Halt growth of Mysql ibdata1 & /tools/cleanup_amavisd_db.py

I should have searched harder, nevermind:

Solution
http://www.iredmail.org/forum/topic7587 … admin.html

3

Re: Halt growth of Mysql ibdata1 & /tools/cleanup_amavisd_db.py

I still however need a response to:

2. Will setting this script as a cron job keep the /var/lib/mysql/ibdata1 file from growing like it currently is?
/var/www/iredadmin/tools/cleanup_amavisd_db.py

I need to halt that growing of /var/lib/mysql/ibdata1, with the amount of traffic we do, I can't have the file get out of hand and cant affort to be dumping and restoring the DB's especially with master-master mysql replication going.

4

Re: Halt growth of Mysql ibdata1 & /tools/cleanup_amavisd_db.py

amora wrote:

2. Will setting this script as a cron job keep the /var/lib/mysql/ibdata1 file from growing like it currently is?
/var/www/iredadmin/tools/cleanup_amavisd_db.py

I don't think so.
I suggest you asking MySQL experts in its mailing list, or try Google.