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 Nutter Tel: +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 +0000
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 without
encountering error
+ ORA-30036 or requiring an enormous undo tablespace.
+
--help
Display this help.
--
1.5.5.6
_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
