logger database growth

[borispr]

Here is a script that cleans records in logger database.

#!/bin/bash

AMAVIS=10 #keep last 10 days
MTA=30 #keep last 30 days
RAW=2 #keep last 2 days

/opt/zimbra/bin/logmysql -D zimbra_logger << EOF
delete from amavis where arrive_time < adddate(curdate(),interval -$AMAVIS day);
optimize table amavis;
select count(*)  AS amavis_rec_left from amavis;
delete from mta where (arrive_time > "2000-01-01" and arrive_time < adddate(curdate(),interval -$MTA day)) or (leave_time > "2000-01-01" and leave_time < adddate(curdate(),interval -$MTA day));
optimize table mta;
select count(*) AS MTA_rec_left from mta;
delete from raw_logs where log_date < adddate(curdate(),interval -$RAW day);
optimize table raw_logs;
select count(*) AS raw_rec_left from raw_logs;
quit
EOF

Run it under zimbra account.
If you logger db is already big the script will work long time. In that case before run it do "zmlogswatchctl stop" in order to stop processes that works with logger database.

For first time is good to clean db manually if the database is very big. In my case raws_logs table was about 40gb
The commands bellow will delete all data in three tables. If you need this data don't execute them!

$zmlogswatchctl stop 
(don't execute "zmloggerctl stop" this also stops logger mysqld)
$logmysql -D zimbra_logger
mysql> delete from amavis;
mysql> optimize table amavis;
mysql> delete from mta;
mysql> optimize table mta;
mysql> delete from raw_logs;
mysql> optimize table raw_logs;
mysql> quit
$zmlogswatchctl start

Be patient, each deleting query may lasts for a long time.

[raj]

Hi..it would be nice if you can add this to Zimbr Wiki page for logger Troubleshooting

Logger - Zimbra :: Wiki

Thanks
Raj

[borispr]

Originally Posted by raj

Hi..it would be nice if you can add this to Zimbr Wiki page for logger Troubleshooting

Logger - Zimbra :: Wiki

Thanks
Raj

done
How to shrink logger database

[raj]

Thanks for the wiki update..

i am too lazy to do this..i just delete the whole thing and recreate...lol

Raj

[maumar]

is this safe for zcs 7.x.x, too?

$zmlogswatchctl stop (don't execute "zmloggerctl stop" this also stops logger mysqld)
 $logmysql -D zimbra_logger
 mysql> delete from amavis;
 mysql> optimize table amavis;
 mysql> delete from mta;
 mysql> optimize table mta;
 mysql> delete from raw_logs;
 mysql> optimize table raw_logs;
 mysql> quit
 $zmlogswatchctl start

[maumar]

it doesn't, at all ;(
logmysql is not part of a zimbra 7.x installation