Hello,
When trying to delete several months worth of monitoring data at once
I ran into error ORA-30036 as the operation required far more undo
tablespace than I could make available (I tried up to 1000Mb).
This patch adds an option --interval to the monitoring-data-cleanup
script. --interval accepts a date delta like 1 week and if set
monitoring data between the oldest record in the time_series table and
the value for $delta will be deleted in batches separated by the value
for --interval. Consequently this requires a lot less space in the undo
tablespace.
For example, if I do:
monitoring-data-cleanup --no-delete-unmatched \
--keep-monitoring-data 1 week \
--interval 1 week
Deleting data from 15:33:26 Dec 6, 2009 to 16:41:21 Dec 30, 2009 with
interval of 1 week
Deleting probes data older than 15:33:26 Dec 13, 2009...
79949 record deleted.
Deleting probes data older than 15:33:26 Dec 20, 2009...
79949 record deleted.
Deleting probes data older than 15:33:26 Dec 27, 2009...
79949 record deleted.
--dry-run will not display correct record counts in this mode. This
can be fixed by changing the delete query to have 2 parameters.
Regards,
--
David NutterTel: +44 (0)131 650 4888
BioSS, JCMB, King's Buildings, Mayfield Rd, EH9 3JZ. Scotland, UK
Biomathematics and Statistics Scotland (BioSS) is formally part of The
Scottish Crop Research Institute (SCRI), a registered Scottish charity
No. SC006662
From fa6e7efcbeea712d8ab47996ef724e27fef8c8a5 Mon Sep 17 00:00:00 2001
From: David Nutter dav...@elrond.bioss.sari.ac.uk
Date: Wed, 6 Jan 2010 16:20:52 +
Subject: [PATCH] Mods to monitoring-data-cleanup to permit staged deletion of
monitoring data
---
.../PerlModules/NP/Probe/monitoring-data-cleanup | 52 +++-
1 files changed, 51 insertions(+), 1 deletions(-)
diff --git a/monitoring/PerlModules/NP/Probe/monitoring-data-cleanup
b/monitoring/PerlModules/NP/Probe/monitoring-data-cleanup
index 6367686..12e5dc1 100755
--- a/monitoring/PerlModules/NP/Probe/monitoring-data-cleanup
+++ b/monitoring/PerlModules/NP/Probe/monitoring-data-cleanup
@@ -27,12 +27,14 @@ my $delete_unmatched = 1;
my $delta = ;
my $dry_run = 0;
my $help = 0;
+my $interval= ;
my ($sql, $err, $sth);
GetOptions(
delete-unmatched! = \$delete_unmatched,
dry-run = \$dry_run,
+ interval:s = \$interval,
keep-monitoring-data:s = \$delta,
help = \$help,
);
@@ -86,7 +88,47 @@ if ($delete_unmatched) {
$dry_run ? $ini-dbh-rollback : $ini-dbh-commit;
}
-if ($delta) {
+if ($interval $delta) {
+#Lookup oldest monitoring data
+$sql = qq|select min(entry_time) as earliest from time_series|;
+
+ $sth = $ini-dbh-prepare($sql);
+ $sth-execute;
+ my $earliest_entry = $sth-fetchrow() or die(Unable to retrieve
earliest entry);
+ my $earliest_date = ParseDateString(epoch $earliest_entry);
+ my $date=DateCalc(today, - $delta,\$err);
+
+ print UnixDate($earliest_date,Deleting data from %T %b %e, %Y to
).UnixDate($date,%T %b %e, %Y). with interval of $interval\n;
+
+ #Calculate list of intervals, last one being $date
+ my (@date_list,$interval_date);
+ while(1) {
+ $interval_date=DateCalc(UnixDate($earliest_date,epoch %s),+
$interval,\$err);
+ if ($err 0) {
+ print STDERR Error: $interval is not valid date
delta.\n;
+ exit 1;
+ }
+ if (Date_Cmp($interval_date,$date) = 0) {
+ last;
+ }
+ push(@date_list,$interval_date);
+ $earliest_date=$interval_date;
+ }
+
+ push(@date_list,$date);
+
+ $sql = qq|delete from time_series where entry_time ?|;
+ $sth = $ini-dbh-prepare($sql);
+
+ foreach my $delete_date (@date_list) {
+print UnixDate($delete_date,Deleting probes data older than
%T %b %e, %Y...\n);
+ $ini-dbh-begin_work;
+ $sth-execute(UnixDate($delete_date,%s));
+ print \t, $sth-rows, record deleted.\n;
+ $dry_run ? $ini-dbh-rollback : $ini-dbh-commit;
+ }
+
+} elsif ($delta) {
# delete probes older then $delta
my $date=DateCalc(today, - $delta, \$err);
if ($err 0) {
@@ -141,6 +183,14 @@ data for deleted probes too. This script will delete them
too.
2 business days
For more information about format see ParseDateDelta section of
Date::Manip(3)
+--interval INTERVAL_DELTA
+ If set, delete data older than DELTA in increments of
+ INTERVAL_DELTA, starting with the oldest record.
+ Example:
+ monitoring-data-cleanup --keep-monitoring-data 1 month --interval
1 month
+ This is used to delete large amounts of monitoring data
