Optimize the get_datarate() function, using some SQL and some C instead.

diff --git a/sql/get_rate.c b/sql/get_rate.c
new file mode 100644 (file)
index 0000000..4e4a8b1
--- /dev/null
+++ b/sql/get_rate.c
@@ -0,0 +1,70 @@
+/*
+   gcc -fPIC -c get_rate.c -I$( pg_config --includedir-server )
+   gcc -shared -o get_rate.so get_rate.o
+ */
+
+#include "postgres.h"
+#include <string.h>
+#include "fmgr.h"
+#include "funcapi.h"
+#include "catalog/pg_type.h"
+#include "utils/lsyscache.h"
+#include "utils/array.h"
+#include "access/heapam.h"
+
+#ifdef PG_MODULE_MAGIC
+PG_MODULE_MAGIC;
+#endif
+
+PG_FUNCTION_INFO_V1(get_rate);
+
+Datum
+get_rate(PG_FUNCTION_ARGS)
+{
+       float8 bytes_in_1 = PG_GETARG_FLOAT8(0);
+       float8 bytes_out_1 = PG_GETARG_FLOAT8(1);
+       float8 time_1 = PG_GETARG_FLOAT8(2);
+
+       float8 bytes_in_2 = PG_GETARG_FLOAT8(3);
+       float8 bytes_out_2 = PG_GETARG_FLOAT8(4);
+       float8 time_2 = PG_GETARG_FLOAT8(5);
+
+       bool no_wrap = PG_GETARG_BOOL(6);
+
+       Datum values[2];
+
+       int16 elmlen;
+       bool elmbyval;
+       char elmalign;
+
+       double in_rate, out_rate;
+
+       if (bytes_in_2 >= bytes_in_1 && bytes_out_2 >= bytes_out_1) {
+               // regular case, no wraparound or reset
+               in_rate = (bytes_in_2 - bytes_in_1) / (time_2 - time_1);
+               out_rate = (bytes_out_2 - bytes_out_1) / (time_2 - time_1);
+       } else {
+               // is wraparound unlikely?
+               if (no_wrap 
+                   || (bytes_in_2 + 4294967296.0 - bytes_in_1) >= 2147483648.0 
+                   || (bytes_out_2 + 4294967296.0 - bytes_out_1) >= 2147483648.0) {
+                       // assume the switch zeroed in the middle of the poll interval
+                       in_rate = bytes_in_2 / (2.0 * (time_2 - time_1));
+                       out_rate = bytes_out_2 / (2.0 * (time_2 - time_1));
+               } else {
+                       // probably wraparound
+                       if (bytes_in_2 < bytes_in_1)
+                               bytes_in_2 += 4294967296.0;
+                       if (bytes_out_2 < bytes_out_1)
+                               bytes_out_2 += 4294967296.0;
+
+                       in_rate = (bytes_in_2 - bytes_in_1) / (time_2 - time_1);
+                       out_rate = (bytes_out_2 - bytes_out_1) / (time_2 - time_1);
+               }
+       }
+
+       values[0] = Float8GetDatum(in_rate);
+       values[1] = Float8GetDatum(out_rate);
+       get_typlenbyvalalign(FLOAT8OID, &elmlen, &elmbyval, &elmalign);
+       PG_RETURN_ARRAYTYPE_P(construct_array(values, 2, FLOAT8OID, elmlen, elmbyval, elmalign));
+}

diff --git a/web/nettkart.pl b/web/nettkart.pl
index 89c63eaca08af3b9de603ec83b37084acdbebec1..bff972bbd4b9a08f3f5ac96ffbcc956df1c8d4c6 100755 (executable)
--- a/web/nettkart.pl
+++ b/web/nettkart.pl
@@ -55,9 +55,7 @@ $text_img->stringFT($tclr, "/usr/share/fonts/truetype/msttcorefonts/Arial.ttf",
 $text_img->stringFT($tclr, "/usr/share/fonts/truetype/msttcorefonts/Arial.ttf", 10, 0, 40, 47 + (236-42)*4.0/4.0, "10 Mbit/sec");
 $text_img->stringFT($tclr, "/usr/share/fonts/truetype/msttcorefonts/Arial.ttf", 10, 0, 1000, 620, "NMS (C) 2005-2007 Tech:Server");
 
-my $q = $dbh->prepare('select * from switches natural join placements natural left join
-( select switch,sum(bytes_in) as bytes_in,sum(bytes_out) as bytes_out from get_datarate() group
-by switch ) t1 order by zorder');
+my $q = $dbh->prepare('select * from switches natural join placements natural left join current_datarate order by zorder');
 $q->execute();
 while (my $ref = $q->fetchrow_hashref()) {