script to delete tiles that can be rerendered very fast (e.g. in less than 1 second...

diff --git a/scripts/deletecheaptiles.pl b/scripts/deletecheaptiles.pl
new file mode 100755 (executable)
index 0000000..eaab412
--- /dev/null
+++ b/scripts/deletecheaptiles.pl
@@ -0,0 +1,86 @@
+#!/usr/bin/perl -w
+
+# This script is used to delete tiles that are extremely cheap to rerender.
+# If a tile can be regenerated in one second, we really don't need to keep it
+# on disk and regularily update it. This can happen on the fly when it is
+# requested instead.
+
+# Where are the tile directories?
+$tiledirprefix = '/mnt/tiles/tiles/';
+# Map for map-ID in DB to tile subdir.
+%tiledirmap = ( 1 => 'osm',
+                2 => 'osmde',
+                4 => 'osmhd'
+              );
+# What is the maximum rendertime to consider a tile useless?
+$maxuselesstime = 0.5;
+
+# How old do files need to be before we delete them? in seconds
+$minuselessage = 48 * 60 * 60;
+
+# Where is the osm stats database?
+$statsdb = 'osmstats';
+
+# -----------------------------------------------------------------------------
+
+use DBI;
+use POSIX qw(strftime mktime);
+use Fcntl ':mode';
+
+# Par. 0: x
+# Par. 1: y
+sub calcpathfromcomponents($$) {
+  my @res = ();
+  my $i; my $x = $_[0]; my $y = $_[1];
+  for ($i = 4; $i >= 0; $i--) {
+    $res[$i] = sprintf("%d", (($x & 0x0f) << 4) + ($y & 0x0f));
+    $x = $x >> 4;
+    $y = $y >> 4;
+  }
+  return $res[0] . "/" . $res[1] . "/" . $res[2] . "/" . $res[3] . "/" . $res[4];
+}
+
+unless ($dbh = DBI->connect("dbi:Pg:dbname=$statsdb","","")) {
+  print(STDERR "Failed to open database. Please try again later.\n"); exit(1);
+}
+# select x, y, z, avg(rendertime) as avgrt, max(rendertime) as maxrt from renderrequests where mapid=1 group by x, y, z
+# select distinct on (x, y, z) x, y, z, rendertime from renderrequests where mapid=1 order by x,y,z,ts desc;
+foreach $mapid (sort(keys(%tiledirmap))) {
+  my $ndeleted = 0;
+  my $sth = $dbh->prepare("select distinct on (x, y, z) x, y, z, rendertime"
+                        . " from renderrequests where mapid=? order by x, y, z, ts desc");
+  unless ($sth->execute($mapid)) {
+    print(STDERR "Sorry, database query blew up.!\n");
+    exit(1);
+  }
+  while (($x, $y, $z, $rendertime) = $sth->fetchrow_array()) {
+    if ($z < 5) { next; }
+    if ($rendertime > $maxuselesstime) {
+      #print("$tiledirmap{$mapid} $z/$x/$y rendered in $rendertime seconds, leaving it alone\n");
+      next;
+    }
+    my $tilepath = $tiledirprefix . $tiledirmap{$mapid} . '/';
+    $tilepath .= $z . '/' . calcpathfromcomponents($x, $y) . '.meta';
+    unless (-e $tilepath) {
+      #print("...file does not exist, nothing to delete.\n");
+      next;
+    }
+    @statres = lstat($tilepath);
+    unless (@statres > 12) {
+      # This usually happens if we have a symlink to a file for which we have
+      # no permission to read.
+      #print("...stat failed\n");
+      next;
+    }
+    if (S_ISLNK($statres[2])) { next; } # A symlink? We no like.
+    if (($statres[9] + $minuselessage) >= time()) {
+      #print("...not old enough, skipped\n");
+      next;
+    }
+    print("$tiledirmap{$mapid} $z/$x/$y rendered in $rendertime seconds, deleting $tilepath\n");
+    unlink($tilepath);
+    $ndeleted++;
+  }
+  $sth->finish();
+  print("Deleted $ndeleted tiles in $tiledirmap{$mapid}\n");
+}