UW Network Tools

strace and tcpdump: or "a good time with dhcpd and iptables"

noreply@blogger.com (Heather Sherman) — Fri, 10 Feb 2012 22:46:00 +0000

After an upgrade to our DHCP servers resulted in a failure to hand out leases under peak load, we enhanced our DHCP load testing client and started narrowing down the problem.

Testing soon sorted out the performance issue (while the new disks were much faster for reads and writes, but the old disk controller had a write cache. The ‘fsync() after every lease’ behavior of dhcpd meant that we were getting killed writing the leases file).

I wanted to quantify how many DHCP leases and requests we could serve once we’d resolved that and I noticed one odd little problem under load. If we set our lease time down to 60 seconds and had more than 600 clients, the dhcpd.log would start reporting an error when sending certain DHCP ACK messages:

send_packet: Operation not permitted

At first I thought it was happening when the client entered the ‘rebinding’ phase (i.e. after the unicast request for a lease renewal had failed, timed out and then switched back to broadcast) because it happened after the initial leases were offered and after some leases had successfully renewed. But closer investigation showed something more interesting.

The first thing to know is that we run our DHCP servers in failover mode and they are located on different subnets. None of the clients would be local in production so we test with a similar configuration. The host the client software runs on is located on a different network from both DHCP servers.

The test client itself is written in java and creates multiple threads, doling out tens or hundreds of thousands of clients requesting IP addresses in batches of 300 at a time. It tracks the clients, following up with a unicast RENEW. If the RENEW times out, it has the client thread go through the broadcast REBINDING process. If that fails, it expires the lease and logs the problem. When the test client is acting like a relay it sets fields in the DHCP packet just like a router with a ‘helper-address’ would.

We didn’t want the network engineers to have to change all the routers between the client and the two pre-production servers by adding routes for a large block of test addresses.

In the past we used iptables on the test DHCP servers to “DNAT” (destination NAT) all traffic to our test network to port 68 on the DHCP test client server with this command:

iptables -t nat -A OUTPUT -d 10.10.0.0/255.255.0.0 -p udp -j DNAT --to-destination 172.16.8.3:68

resulting in:

Chain PREROUTING (policy ACCEPT)
target     prot opt source               destination         

Chain POSTROUTING (policy ACCEPT)
target     prot opt source               destination         

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         
DNAT       udp  --  anywhere             10.10.0.0/16        to:172.16.8.3:68

Under low load, everything worked fine. Even under high load (once I’d worked around the issue with the fysnc() performance) I saw significantly higher DHCP ACK counts on the new servers. But I saw the send_packet complaints above intermittently appear in the log file and I saw clients go into rebinding even under relatively low load conditions.

A little searching turn up the claim that ‘Operation not permitted’ was related to iptables, but careful review of the iptables entries on the DHCP servers and examination of the connection tracking table limits didn’t show an obvious problem. The fact that most of the time traffic got through suggested the problem wasn’t a bad rule. And all the connection tracking limits were greater than 600 while the problem could be demonstrated with as few as 600 client threads.

Using the dhcpd source, I was able to add some debugging to narrow things down a bit more:

the problem only happened when a unicast packet was received from a client (but didn’t happen for all unicast packets)
the same functions and system calls were always being used to generate the responses whether there were issues or not

Using tcpdump on the DHCP servers to inspect the traffic I found a few more interesting things:

even though a DHCP ACK was logged in the dhcpd.log file, the corresponding packet was not seen by tcpdump (so the “send_packet:” complaint really did indicate a failure)
the complaint never happened until after the first 254 addresses were handed out. It always seemed to start when the client with 10.10.2.0 came back to renew.
the unicast responses from the DHCP server weren’t coming from port 67, but were going out from a variety of other low-numbered ports…

Using strace proved most informative of all:

the dhcpd sendto() call WAS sourcing traffic from port 67 despite what we saw with tcpdump
EPERM (the error code that corresponds to the message ‘Operation not permitted’) consistently happened at 10.10.2.0 – until I changed the range in the dhcpd.conf file to start offering addresses from a different range, after which the EPERM error started happening at 10.10.2.19.

So, back to iptables. Although we thought the DHCP traffic should not be subject to connection tracking due to the following table entries:

Chain PREROUTING (policy ACCEPT)
target     prot opt source               destination         
net_notrk  all  --  anywhere             anywhere            

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         

Chain net_notrk (1 references)
target     prot opt source               destination         
NOTRACK    udp  --  anywhere             anywhere            udp dpt:domain 
NOTRACK    udp  --  anywhere             anywhere            multiport dports bootp,bootpc

I was seeing entries in the connection tracking table:

ipv4     2 udp      17 18 src=192.168.1.12 dst=10.10.0.249 sport=67 dport=68 
          [UNREPLIED] src=172.16.8.3 dst=192.168.1.12 sport=68 dport=84 mark=0 secmark=0 use=2

A couple of things jump out:

as mentioned, I’m seeing connection tracking entries, despite the configuration above.
the dport=84 in the second line indicates that iptables is choosing to rewrite the source port when it sends out the traffic.

The first thing to realize is that the configuration above won’t affect packets sent out from the DHCP servers (note the empty OUTPUT clause).

Secondly, the dport=84 gives a big hint about the cause of the “EPERM” problem.

Because the outbound traffic is all destined for the same host and port, the iptables DNAT rule creates an entry in the /proc/net/nf_conntrack table to show how to map any responses. Not that there will be any for this application, of course, but the iptables rules don’t know that.

Well, okay. So why were packets dropping? We weren’t hitting connection table limits (the table never got above more than 500 or so entries).

Apparently, because the packets were sourced from port 67 (a reserved port), the implicit source mapping mapped the ports to a new unused port – but only used ports between 1 and 512. For more details see Implicit Source Mapping

So, how do I work around this issue? While it’s reassuring to know that this would not happen in production, I did want to finish my load testing. I thought perhaps we could DNAT w/o connection tracking, so we went ahead and fixed our connection tracking bypass rule:

Chain host_notrk (1 references)
target     prot opt source               destination         
NOTRACK    udp  --  anywhere             anywhere            multiport dports bootp,bootpc 
NOTRACK    icmp --  anywhere             anywhere            icmp echo-request

I anticipate using these settings in production, bypassing connection tracking for all DHCP traffic and for the “ping” that the DHCP server does to avoid assigning an address that is being squatted on by another client.

Result? DNAT stopped working entirely. Bypassing connection tracking appears to bypass DNAT, too.

An alternative option is dropping the length of time that UDP entries live in the nf_conntrack table. I don’t want them there at all, so instructing the system to keep them for 1 second instead of 30 seems to work fine:

sysctl -w net.netfilter.nf_conntrack_udp_timeout=1

I no longer see sendto() EPERM complaints, even at 20,000 clients with a 60 second lease time and we no longer see lease failures (well, with fsync() commented out, that is. I’m still working on the permanent preferred solution to THAT problem).

mDash, or Systems Monitoring Using jQuery Mobile

noreply@blogger.com (jody) — Fri, 03 Jun 2011 22:05:00 +0000

We've written Mobile Dash, or mDash, a mobile web app for monitoring UW-IT system alerts.

(If you're UW-IT staff, you can check it out here: mDash.)

Some screenshots:

mDash home screen on iPhone 4

mDash alert detail page

(It's only for UW-IT personnel or we'd have a link for you. If you are UW-IT personnel and want a closer look, feel free to leave us a comment.)

The interface uses jQuery Mobile. Behind the scenes, mDash uses jQuery for its Ajax call and DOM manipulation. It also uses Badgerfishjs to transform the alerts from our XML web service into JSON. And some custom CSS adds UW colors to the interface.

So what is mDash? It's the mobile version Dash. But what's Dash?

Dash is UW-IT's Java Web Start (Swing) application for monitoring system alerts. One description reads that, "It is intended to be [a] simple and reliable way for system engineers, computer operations, client support groups, and management to view DSUE system status." (DSUE being Distributed Systems and Unix Engineering). By system we mean all sorts of systems. Dash monitors everything from web services used by a handful of developers to student email servers.

Our team's goal with mDash was to replicate a subset of Dash's functionality in mobile web app form. We wanted to: show what's down (an abbreviation of the hostname), indicate the severity of the problem (the color-coded numbers adopted from the Java app), and allow a person to view a few more essential details via an alert detail page.

When monitoring critical systems, freshness of information matters. So, on the home page we display the time when alerts were last checked and we provide an unavoidable "Refresh Alerts" button so people can retrieve the latest alerts when they want. In the future we may refresh the alerts asynchronously, but due to the large amount of alerts that asynchronous process can slow some devices' browsers to a crawl or bring them to a standstill.

The alert detail page's fields might seem esoteric to people outside DSUE at UW-IT, but these labels are familiar to the smallish set of people who will use this app at the outset. As mDash (and Dash) moves toward wider adoption, we'll need more human-friendly field labels.

It will sound clichéd, but mDash was fun to make. And jQuery Mobile is what made it fun. We did run into some funky bugs and behaviors along the way, but never insurmountable ones and never ones that the jQuery Mobile community hadn't already solved.

To say we're looking forward to making another jQuery Mobile web app is understating it.

Following wrong assumptions

noreply@blogger.com (KenM) — Fri, 18 Feb 2011 18:18:00 +0000

An example of following wrong assumptions when debugging:

I was porting a bit of hyak (a several hundred node compute cluster)
monitoring from ProxD (an older collector of alerts) to Injector (a newer
generation). This monitor runs a vendor disgnostic utility once a minute
that lists status for every hyak node and issues alerts for any that are
'Down'. It saves status by node, so normally it only generates traffic for
node state changes. The exception is at daemon startup, when (since it
doesn't know whether there might be existing alerts) it issues traffic for
every node (either a Upd if the node is down or a Del otherwise).

Because determining the state of a node is just looking at the next line
of output, at startup there are going to be 500 odd alerts in a short
period of time, like .02 seconds.

Usually, there are no nodes down, so if you start the daemon, you won't
notice any dropped alerts (I'm sending these via UDP). Today, though,
there were a few nodes down, and one of them had a high number - n0418 or
something like that. When I fired up my newly ported code, I checked
against the list of active alerts from ProxD - and n0418 was missing.

I wrote a quicky program to send 500 alerts as fast as possible, and indeed
it start dropping alerts after 150 alerts or so. With ProxD, OTOH, you
could send blocks of 500 over and over w/ no skips. So I started picking
apart the code. The implementations aren't all that different, though, but
they did use different xml parsers and some minor differences. Or could
the read thread be blocking waiting because the write-to-activeMQ queue was
full? Anyway, I spent a couple of hours picking both pieces of code
apart, always looking at millisecond per transaction times - but those
were pretty close - on the order of 3 to 4 millisecs (I dunno if that is a
server limit or not; the client couldn't send faster).

Oooops. The problem was the udp input buffer size. The new alert
format is a fair amount longer than the old. The proxd one is:

The new one is:

foo.u
myProg
5
...

and so on. Longer inbound messages fill the buffer sooner - duh. I hadn't
really thought about the performance implications there, and was quite
suprised they made such a difference. I might be a little more terse next
time. Anyway, I increased the buffer size so you can send 2000 odd msgs as
fast as a singlethreaded python prog can send them w/o problems.

I should have kept an open mind longer - once I focused in on transaction
times, I stopped thinking about buffer sizes; a classic case of narrowing
the suspect list too soon.

BTW, I went back and tried sending larger numbers of msgs to proxd, and
it started dropping them between 1000 and 2000 msgs, so we had been running
in a sweet spot - no other app sends traffic quite as bursty as this one,
and it was sending a little under the ceiling, so there weren't any
problems. I was also lucky that a high numbered node was down - if it had
been N0099, I never would have noticed.

... and ipset comes to the rescue

noreply@blogger.com (Nick) — Fri, 05 Nov 2010 16:29:00 +0000

We require that hosts located on certain subnets register before they are allowed to use the full capabilities of our campus network. By default, all hosts on such a network are blocked, until the registration is performed. While blocked, however, they need to get redirected to the registration page. For years now, we have used iptables for dynamic dns redirection when performing these host registrations.

To achieve this, a source NAT mapping is generated to a lying dns server when a host on a network that requires registrations. This determination is done by a realtime processor script acting on DHCP transitions. Once a device has been registered the entry is updated to point to the true DNS and and the iptables entry is updated.

Unfortunately, iptables became a performance bottleneck. The finite time for inserting and deleting a rule for a chain increases as the number of rules in the table increases. In addition, adding a rule takes two calls to iptables (one to list and make sure the rule does not exist already, a second one to actually add the rule). This is necessary to prevent duplicated rules in the same chain.

As the demand for our service increased over the years, the number of rules in iptables increased along with it. Eventually the realtime processor script became "almost realtime" and then "almost never in time" during peak periods of use.

Analysis showed that the average time to add or delete an iptables rule was 0.17s (over 100k samples). At times where the DHCP transition rate could be as high as 1000/min, the iptables updates ran several minutes behind, making the registration process effectively unusable.

We researched an alternative method of storing the host ip address mappings using ipset (http://ipset.netfilter.org/). Designed to work with iptables, ipset is basically a very efficient bucket for storing network address (ip and port) information. Time to add and remove addresses is almost constant and much smaller than using iptables at 0.0065s (over 100k samples). This allows more head room for capacity!

tool	average time	stdev
iptables	0.17s	0.12
ipset tested	0.0065s time	0.034
pset in production	0.008s	0.027

Jolly times with the postgresql optimizer and explain analyze

noreply@blogger.com (Heather Sherman) — Thu, 23 Sep 2010 17:28:00 +0000

Suddenly yesterday one of my rex functions (REX is our REST/SQL mapper tool) jumped off a cliff, performance-wise. With a hint from Nick (who will forevermore be known as "the database whisperer") I was able to narrow the performance problem down to this particular query in the function that REX is calling, specifically the family(network)=4 element of the where clause:

SELECT DISTINCT network, sector.sector::text
FROM nfa_k20_network
LEFT JOIN info_site ON info_site.siteid = nfa_k20_network.site_id
LEFT JOIN dev_location on dev_location.site_id = info_site.id
LEFT JOIN dev_classification on dev_classification.device_id =
dev_location.device_id
LEFT JOIN sector on sector.id = dev_classification.sector
WHERE sector.sector is not null and family(network) = 4;

But I was unable to get the optimizer to generate the same plan on netdbeval/allyourbase as I got on netdbdev. I'm assuming this is an indication that the data size in one of the tables tipped the query optimizer over into selecting a less ideal strategy, although I'm not clear
on how or why.

The good strategy uses hashes, the bad one a nested join and a filter.
Here's where things start to run amok:

Good (netdbdev) http://explain.depesz.com/s/ukE

Hash Join (cost=3185.37..4996.42 rows=1 width=28) (actual
time=953.056..1227.372 rows=7379 loops=1)
* Hash Cond: (public.dev_state.device_id = interface.device_id)

Bad (netdbeval) http://explain.depesz.com/s/TI6

Nested Loop (cost=1851.83..8488.08 rows=1 width=34) (actual
time=247.812..404814.965 rows=1465 loops=1)
* Join Filter: (interface.device_id = public.dev_state.device_id)

Wowsers! Check out how the actual time compares to the estimated cost!

Explicitly disabling nested loops in psql before trying this query caused it to go back from taking several minutes to taking 3 seconds, so we clearly want to get postgres to use the first strategy.

The choice seems to be driven by the estimated row count that ifc_ip will return:

ripit_2=# explain analyze select count(*) from ifc_ip where family(ip)=4;
QUERY PLAN

------------------------------------------------------------------------------------------------------------
Aggregate (cost=268.85..268.87 rows=1 width=0) (actual
time=15.433..15.434 rows=1 loops=1)
-> Seq Scan on ifc_ip (cost=0.00..268.70 rows=61 width=0) (actual
time=0.048..9.489 rows=8498 loops=1)
Filter: (family(ip) = 4)
Total runtime: 15.481 ms
(4 rows)

Note that the estimated row count is 61, while the actual row count returned is 8498.

I tinkered with a number of settings (work_mem, effective_cache_size) to see if I could drive the query planner to go back to making better choices. I also tried analyzing and reindexing the table. The basic problem seems to be how much filtering the query planner expects the "family(ip)=4" clause to result in and I don't see how the pg_statistics table could capture that without adding an index (heck I don't even see where that mysterious 61 number is coming from).

A simple and reasonable solution, however, seems to be to rewrite the query as "family(ip) != 6" (the goal of the query is to eliminate any IPv6 addresses from getting returned).

Doing this results in an estimate that captures something closer to the expected size of the ifc_ip result set (on the high end!) and has the desired effect on the overall query.

ripit_2=# explain analyze select count(*) from ifc_ip where family(ip)!=6;
QUERY PLAN

---------------------------------------------------------------------------------------------------------------
Aggregate (cost=299.00..299.01 rows=1 width=0) (actual
time=15.380..15.381 rows=1 loops=1)
-> Seq Scan on ifc_ip (cost=0.00..268.70 rows=12119 width=0) (actual
time=0.049..9.353 rows=8498 loops=1)
Filter: (family(ip) <> 6)
Total runtime: 15.432 ms
(4 rows)

The downside, of course, is that I've no idea what might cause the query planner to change its mind in future about this table or perhaps another table lurking in the query. So my sense is that it might be a more robust solution to add an index, but I'd like to understand better how the returned rows estimation is generated before I go adding more random indexes to tables which, after all, aren't free . . .

Presenting ... UW Service Tools

noreply@blogger.com (jody) — Fri, 06 Aug 2010 23:15:00 +0000

You might have noticed that we've changed the blog's name from UW Network Management Tools to UW Service Tools. The name change reflects reorganization within UW Information Technology. Our group has moved from Network Systems to the Technology Management division's Application Infrastructure Engineering unit--a move we're excited about.

For the most part, what we do on a daily basis remains the same: we'll keep making good software. As for what's new, our focus is widening. We'll be working on automating a wide range of technology services to support UW-IT and UW.

We'll be posting more news here about our new work soon.

Meerkat Framework: Presentation at SASAG ...

noreply@blogger.com (Sridhar Komandur, PhD) — Mon, 14 Jun 2010 17:01:00 +0000

Meerkat Framework presentation to the Seattle Area System Administrators Guild (SASAG) on June 10, 2010

20100610sasg-pdf

How Kittyfetch Was Named

noreply@blogger.com (jody) — Fri, 30 Apr 2010 22:04:00 +0000

In our regular team meeting on Friday April 30, we began discussing the etymology of "kittyfetch", one of our device backup tools. The tool's earlier incarnation was called RANCID (in our wiki it's almost always CAPITALIZED) but we weren't clear how kittyfetch became "kittyfetch."

I searched through my chat logs and found that the name dated from November 1, 2007. The transcript follows (emoticons removed):

vaughan: What do we call the new rancid thingy?

jtate: anything but rancid, please

hsherman: squirrel.

or maybe magpie

nick?

wells: ooo, squirrel is a great name!

hsherman: "animals who hoard"

vaughan: What are those birds that plunder campsites Clarence wonders?

Anyone?

benroy: camp robbers?

pack rats

hsherman: raccoons, except for the not being birds thing

jtate: type squirrel ten times in a row and then tell me it's a great name

vultures

amcharg: i would not like it if raccoons could fly

jtate: they do: pigeons

hsherman: it never needs typing

amcharg: true and i dislike pigeons

hsherman: just a cute picture.

this will actually have the side effect of limiting requests for enhancements to the system.

wells: squirrelsquirrelsquirrelsquirrelsquirrelsquirrelsquirrelsquirrelsquirrelsquirrel

are you kinding? It just *rolls* off the finger tips

nickchen

why do we need a new name?

config-backup is good for me

or coba

cobac

jtate: copacobana

vaughan: squirrels

yeah, that does type nice.

gincoba biloba

ok, config-backup it is.

wells: LOL

nickchen: maybe something like, a cat or something

kitty

kitty go get my backup

jtate: puma, meerkat, i see a trend

cat's don't follow commands well

cats

nickchen: kitty failed to backup

hsherman: meerkats aren't cats.

nickchen: http://upload.wikimedia.org/wikipedia/commons/3/3f/Toilet_Trained_Cat_22_Aug_2005.jpg

hsherman: maybe we could continue the non-cat cat theme with "catfish".

vaughan: LOL re: "kitty go get my backup"

[ vaughan is still laughing in his office box. ]

benroy: I think Mr. Bo Jangles is a cat.

wells: $ hai kitty does gets backup star-k12

- kitty can haz backup star-k12

- oh noez, kitty does haz not backup star-k12, sez: "port connection failed"

- kthnxbye

nickchen: lol

amcharg: lulz

nickchen: i shall change all the log messages to lolcats

wells: we should totally make the "hai kitty" command ...

amcharg: s/backup/cheezburger/

wells: haha

And so the transcript ends. Somehow, and the history books are unclear, from that day forward the tool was known as kittyfetch.

To an outsider, the above may not be funny. For us, it's hilarious--and here's why. At a basic level, you need to know about lolcats. Lolcats are an internet meme that may never die. They and their idiolect sneak into our team's everyday communication. How lolcats speak is, well, funny. Very funny. The fact lolcat-speak (translators exist) enters our work at all is evidence of our team's cohesion: we have in-jokes. To put it a basic way, lolcats aside: we laugh together.

Laughing together and working together can seem to some like two mutually exclusive things, but they're not. Chris Roberts and Wan Yan at the University of Missouri-Columbia found that, "humor among colleagues ... enhances creativity, department cohesiveness and overall performance." Laughter, or humor, in the workplace is a good thing. Our team collaborates well and humor emerges from that collaboration and energizes it.

Yet, it's become an adage: you can't make fun mandatory. And our fun isn't. So, how did our team come together in such a way that we find kittyfetch a hilarious name? No one knows for sure. But we're glad it happened.

For more, you can read Roberts and Yan's study: http://www.sciencedaily.com/releases/2007/10/071031130917.htm

Avaya PBX admin web service

noreply@blogger.com (Ben) — Mon, 19 Apr 2010 20:55:00 +0000

At the UW we've been heavily invested in Avaya telecommunications products for nearly two decades. In particular, we've been invested in the DEFINITY platform, now called Communications Manager (CM). We started using CM when it was owned by AT&T. It was later acquired by Lucent and then separated to become Avaya.

CM is the cadillac of enterprise phone systems (PBX) with almost every feature imaginable. Like many enterprise vendors, they provide GUI applications written for Windows and they work fine--if they have the features that you want to use. Once you want to start integrating with other enterprise systems and automating tasks, you run into situations where the provided tools hinder work more than they help.

The primary interface to these PBX systems is with a terminal. Originally it was a serial terminal but now telnet and ssh are available. When you log in you select a "terminal type." Most people use the screen-based VT220 interface where you type commands and use tab and return to move around the screen to make changes. It's not fun to use those screens for scripting, but there's another undocumented terminal type called "OSSI" that's designed for scripting and is used by Avaya's own GUI tools.

In this post I'll describe the web service system I've built around the OSSI terminal connection. The end result is contained within a Perl module that comprises the heart of this system.

The system breaks down into three parts:

1. DEFINITY_ossi.pm
2. A separate PBXD server instance for each PBX.
3. A single web CGI interface that passes XML requests between the client applications and the correct PBXD instance.

DEFINITY_ossi.pm is the Perl module that knows how to connect to all of our PBX systems, process commands, and return data either formatted like the output of the VT220 screen or as a set of OSSI fields.

PBXD is a server based on the Net::Server::PreFork CPAN module that manages a pool of connections to a single PBX and provides an XML interface to DEFINITY_ossi.pm. There is 1 instance of PBXD running for each of our 3 PBX systems. Each PBXD instance has its own config file.

Finally the web interface is a Perl CGI script that knows about each PBXD instance and proxies the XML requests from clients to specific the PBXD instance for each PBX.

Here's a visual description of the system

Once it's setup and deployed you have a scalable platform for rapid development of web applications to manage all of your PBX systems. We've moved both batch and interactive application to the new system and it's been working well. I hope you've found this post interesting and that it will give you some ideas and code that can help manage you own PBX systems.

REX: Database Driven Web Services Service

noreply@blogger.com (dharmabruce) — Thu, 11 Mar 2010 22:09:00 +0000

REX presentation given to the University of Washington Web Application Developers meeting:

Rex: Database Driven Web Services Service

KPI part 2: materializing views

noreply@blogger.com (Heather Sherman) — Tue, 02 Feb 2010 07:15:00 +0000

In my first KPI post, I mentioned how performance became an issue and how we solved it by pre-generating the results of the summarized data we wanted to display. That was dandy ... once. But it was obvious we'd want to do something similar for other data already stored in our database. As a huge fan of consistency and repeatability, I didn't care for the idea of a mess of tables and another mess of cron jobs and scripts to keep those different tables up to date, each in their own custom way.

A little research turned up the concept of 'Materialized Views'. The idea is simple: generate a view and use it to create and maintain the contents of a static table. A wide variety of "refesh" techniques can be applied (depending on the importance of having current data and limiting impact on either inserts to the source tables or to the end user viewing the summary. Some databases implement this natively. PostgreSQL isn't one of them.

Some great discussions of the concept are already available, so I'll just highlight how we chose to implement it.

First, we created a schema in our 'ironfist' database to contain the views as well as the tables where the materialized content is stored (an MV table) along with a master table too keep track of them:


CREATE SCHEMA matview;
GRANT ALL ON SCHEMA matview TO PUBLIC;

CREATE TABLE matview.views (
  mv_name NAME NOT NULL PRIMARY KEY
  , v_name NAME NOT NULL
  , last_refresh TIMESTAMP WITH TIME ZONE
);

For our application, stale data wasn't a concern. All of our capacity KPIs either show monthly data or show current data. Regenerating the data weekly (and even the performance hit of regenerating all the data each time) doesn't cost us much. So the set of functions we used to create a materialized view infrastructure is fairly simple.

Before starting, we test and create a view in the 'matview' schema then use the various matview functions to create and maintain the MV table.

This first function creates an MV table based on an existing view. We tell it the name (which is used to find the view and to create the corresponding table) and the columns we wish to use as the primary key (this helps with future in-place updates). For example:


 select matview.create('kpi_network_switch_ports','sector,month');

The create function looks like:


CREATE OR REPLACE FUNCTION matview.create(name text,primary_cols text) RETURNS VOID AS 
$$
 DECLARE
    entry matview.views%ROWTYPE;
    view_name text:= 'matview.' || name || '_v';
    matview_name text:= 'matview.' || name || '_mv';
 BEGIN
    SELECT * INTO entry FROM matview.views WHERE mv_name = matview_name;
    IF FOUND THEN
         RAISE EXCEPTION 'Materialized view ''%'' already exists.',
           matview_name;
    END IF;  
    EXECUTE 'REVOKE ALL ON '       || view_name ||    ' FROM PUBLIC';
    EXECUTE 'GRANT SELECT ON '     || view_name ||    ' TO PUBLIC'; 
    EXECUTE 'CREATE TABLE '        || matview_name || ' AS SELECT * FROM ' || view_name;
    EXECUTE 'ALTER TABLE '         || matview_name || ' ADD PRIMARY KEY ' || '(' || primary_cols || ')';
    EXECUTE 'REVOKE ALL ON '       || matview_name || ' FROM PUBLIC';
    EXECUTE 'GRANT SELECT ON '     || matview_name || ' TO PUBLIC';
    EXECUTE 'CREATE TRIGGER ' || name || '_trigger' || 
                ' BEFORE INSERT ON ' || matview_name || 
                ' FOR EACH ROW EXECUTE PROCEDURE insert_or_replace_if_duplicate ()';

    INSERT INTO matview.views (mv_name, v_name, last_refresh)
       VALUES (matview_name, view_name, CURRENT_TIMESTAMP);
     
    RETURN;
END
$$
 SECURITY DEFINER
 LANGUAGE plpgsql;

The final insert populates the table with the current results of a SELECT * on the view. The table can subsequently be 'refreshed' with the matview.refresh function. This function refreshes either a single table by name or 'all' (all MVs listed in the matview.views table):


CREATE OR REPLACE FUNCTION matview.refresh(name text) RETURNS VOID AS 
$$
DECLARE
    query text;
    mview matview.views%ROWTYPE;
    matview_name text:= 'matview.' || name || '_mv';
BEGIN
    IF (name = 'all')
    THEN
        query := 'SELECT * from matview.views';
    ELSE
        query := 'SELECT * FROM matview.views WHERE mv_name =' || quote_literal(matview_name);
    END IF;

    FOR mview in EXECUTE query LOOP
        EXECUTE 'INSERT INTO ' || mview.mv_name || ' SELECT * FROM ' || mview.v_name;
        UPDATE matview.views
            SET last_refresh=CURRENT_TIMESTAMP WHERE mv_name=mview.mv_name;
    END LOOP;

    IF NOT FOUND THEN
         RAISE EXCEPTION 'Materialized view % does not exist.', matview_name;
    END IF;

    RETURN;
END;
 SECURITY DEFINER
 LANGUAGE plpgsql;

In the initial MV table creation (in the matview.create function), you'll notice that we added a BEFORE INSERT trigger to the table. When we refresh the table, we update all rows in the table. Other refresh approaches involve placing triggers on data source tables (we wanted to avoid that, especially since some of our tables are stored on another database server).

We could just empty the table and completely regenerate it, but some of our views take several minutes to run. During that time our table would be empty of data. And if the view failed (if, say, the dblink connection to our network modeling database failed) the table would remain empty. Stale data is better than no data.

Finally, although we have a last refresh time that last refresh time doesn't guarantee that running the view again won't change existing values for existing rows in the table. For "total number of devices active on the network in January", that number will update on January 1st, then again on January 2nd, etc. The refresh time is useful for confirming how up to date our data is, but not useful for reducing the work needed to update it.

So instead, we regenerate all data in an MV, adding a row if no row with the same primary key values exists, but otherwise replacing (not updating!) the matching row with a new one using the following function as a trigger:


CREATE FUNCTION insert_or_replace_if_duplicate() RETURNS trigger
    AS $$
/*  Description:  Trigger function to insert a new row, first deleting if it would violate the primary key

    To use this function, create a trigger on the appropriate table as follows:

    CREATE TRIGGER availability_summary_insert_trigger
        BEFORE INSERT ON kpi.availability_summary 
        FOR EACH ROW EXECUTE PROCEDURE insert_or_replace_if_duplicate();

    This before trigger checks to see if the insert would violate the primary key of the table.  
    If it does, it will delete the entire row before returning to the insert call.   This is easier
    than trying to figure out which fields to update.
*/
DECLARE
    table_name text      := TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME;
    where_clause text    := ' WHERE TRUE';
    colname text;
BEGIN
    FOR colname IN  select column_name 
                        from information_schema.key_column_usage
                        natural join information_schema.table_constraints itc
                        where itc.table_schema=quote_ident(TG_TABLE_SCHEMA)
                          and itc.table_name=quote_ident(TG_TABLE_NAME)
                          and itc.constraint_type='PRIMARY KEY'
    LOOP
-- the syntax is hairy, but this casts the NEW row to the appropriate table and lets us pick 
-- out the columns we want to compare.

        where_clause := where_clause || ' AND ' || colname || '=' || '(' || quote_literal(new) || '::' || table_name || ').' || colname;
    END LOOP;

-- now that we know what row we will conflict with, delete it before doing our insert.

    EXECUTE 'DELETE FROM ' || table_name || where_clause;
    RETURN NEW;
END
$$
    LANGUAGE plpgsql;

This works because we are assured that updates will always generate data for all columns. The above function wouldn't be useful for cases where only some column data was provided for subsequent updates.

Finally, of course, we have a function that cleans up any MV tables if we choose to remove them:


CREATE OR REPLACE FUNCTION matview.drop(name text) RETURNS VOID
 AS 
$$
 DECLARE
    entry matview.views%ROWTYPE;
    view_name text:= 'matview.' || name || '_v';
    matview_name text:= 'matview.' || name || '_mv';
 BEGIN
     SELECT * INTO entry FROM matview.views WHERE mv_name = matview_name;

     IF NOT FOUND THEN
         RAISE EXCEPTION 'Materialized view % does not exist.', matview_name;
     END IF;

     EXECUTE 'DROP TABLE ' || matview_name;
     DELETE FROM matview.views WHERE mv_name=matview_name;
     RETURN;
 END
$$
 SECURITY DEFINER
 LANGUAGE plpgsql;

The final post in this series will discuss some of what we did with the Google Visualization API to use and display the data from our Materialized Views.

PostgreSQL fun: cross-database joins

noreply@blogger.com (Heather Sherman) — Sat, 23 Jan 2010 00:12:00 +0000

Before I discuss how we implemented Materialized Views, I should give a little background about our database configuration. We have two production PostgreSQL databases running on two different servers. The first database, "ripit_2" is our primary database, containing network modeling information (our devices, their interfaces, network contact people, and all associated attributes) and other critical support information (e.g. meerkat network events).

Non-critical, highly active data is stored on our second database, ironfist.

We've tried to keep our PostgreSQL installation as vanilla as possible. We rely on a fabulous and responsive team of five DBAs who support not only our PostgreSQL servers, but also many campus MS SQL, IBM DB2, IBM INFORMIX, ORACLE, and SYBASE servers! To reduce our demands on their limited time and to avoid locking ourselves into a particular database, we historically shied away from installing add-ons or depending on PostgreSQL specific features.

When the KPI project started, we knew that much of the information it generated would be stored on ironfist, such as the daily device and subnet availability numbers we planned to produce. And KPI would summarize information already available on ironfist, including arp cache information and switch mac address table information. Some information would come from the ripit_2 database, but we anticipated storing all the final summary results on ironfist.

The lack of cross-database joins became a headache almost immediately. PostgreSQL doesn't natively support cross-database joins, so in the past we did a mixture of replicating static data from ripit_2 into ironfist tables or performing some types of joins into client code (in java, Perl or Python). There are obvious downsides to both of these approaches, as we wanted to move to a model extensively based on web services using the rex SQL/XML mapper.

As an experiment we decided to install the dblink contrib module on ironfist. By doing this, we could have ironfist perform queries against ripit_2 and use the results to join against data in the ironfist database. We found a couple of helpful articles online. The Postgres OnLine Journal provided a great set of examples and highlighted some of the limitations. A typical query might look like:


SELECT *
FROM dblink('dbname=ripit_2 host=dbr port=5432 user=db_kpi password=testing',
       'select make, model from system_id_types')
as p(make text, model text);

So we installed dblink and are now using it for the KPI project extensively.

The biggest issues we've found with dblink are:

performance: your query must take into account that the full data set from the remote server is going to be retrieved before any joins against local data can happen. Reduce that data set in your query on the remote server as much as possible.

authentication: either you need to be superuser or you need to have the remote db set up to require a password from the source db host/user and that password has to be included in the query. Yuck!

explicit structure declarations: a local ironfist query (or view, or function) that uses a dblink call must explicitly list the row output structure. Note the use of "(make text, model text)" in the above.

The only one of these that we addressed in a general way is the authorization issue. While the KPI project was, so far, the only consumer of dblink queries, we realized that other projects, with different ripit_2 authorization needs on the ironfist end might make use of dblink in future. So we installed dblink in its own schema (dblink) and added a couple of tables and helper functions. In addition we set up some authentication infrastructure between our two databases (and between the development and eval systems as well):



-- created a 'readonly' db group 'db_ro_group'
CREATE ROLE dblink_readonly nologin;                           
-- created a user dblink_kpi_or in the group
CREATE USER dblink_kpi_ro IN ROLE dblink_readonly password 'testing';

# for dblink access from ironfist database hosts (prod, eval and dev), require md5 passwords
host    all     +dblink_readonly     192.168.1.1   255.255.255.255 md5 #dblink from dbi
host    all     +dblink_readonly     192.168.1.2   255.255.255.255 md5 #dblink from dbieval
host    all     +dblink_readonly     192.168.1.3   255.255.255.255 md5 #dblink from dbidev

We wanted to ensure that functions would not have to hardcode passwords. Passwords change and having to replace some unknown number of functions, scattered throughout schemas in the ironfist database someday was a painful thought.


CREATE schema dblink;
GRANT USAGE ON schema dblink to public;

CREATE TABLE dblink.users (
id integer NOT NULL,
"user" character varying,
password character varying
);

CREATE OR REPLACE FUNCTION dblink.connect_ripit_2(text, text) returns text
as $SQL$
SELECT 'dbname=ripit_2 host=' || $2 || ' port=5432 user=' || $1 ||' password=' || password
FROM   dblink.users
WHERE  dblink.users.user = $1
$SQL$
LANGUAGE SQL
SECURITY DEFINER
STABLE;

Note the "SECURITY DEFINER" attribute of that function. We restricted access to the dblink.users table so that only user postgres can select from it. "SECURITY DEFINER" means that the function dblink.connect_ripit_2 can read the table regardless of who is executing that function. Someone can run the function, but won't be able to directly view the table of users and passwords.

Below I'll show one of the shorter KPI views we created which uses dblink. This view generates a histogram of active devices by type per month per sector for the past 60 months. The entire query ru ns on the ripit_2 database server (no data from ironfist is joined for this one), using the dev_state and dev_state_history tables. The raw data looks like:


sector    |   month    |   device_type   |  make  |  model       | count
-------------+------------+-----------------+--------+--------------+-----------
uwcampus    | 2010-01-01 | layer2          | smc    | swsmc8612t   |     2
uwcampus    | 2010-01-01 | layer2          | smc    | swsmc8612xl3 |   299
uwcampus    | 2010-01-01 | layer2          | smc    | swsmc8624t   |    72
uwcampus    | 2010-01-01 | router          | cisco  | 1811         |     2

And so on.

Here's a taste of what we can prese nt with the Google Visualization API from the above data: a stacked bar chart showing co unts of the di fferent device types by month:

A "SELECT *" on the following query takes almost 60 seconds! "Materializing" this view (by pre-generating its results and storing them in a table on ironfist) were critical to providing a responsive user interface.


CREATE OR REPLACE VIEW matview.kpi_network_devices_v AS
SELECT * FROM dblink.dblink(dblink.connect_ripit_2('dblink_kpi_ro'::text, 'dbr'::text),
$$  SELECT sector, month, device_type, make,  model,  system_type, count(device_id)
   FROM (  SELECT  CASE WHEN sector.sector = 'mcis'
                            THEN 'uwmedcenter'
                            WHEN sector.owner = 'uw'
                             THEN 'uwcampus'
                             ELSE sector.owner
                       END as sector,
                   intervals.month,
                       device_types.type as device_type,
                       system_id_types.id as system_type,
                       make,
                       model,
                   dev_state.device_id     
           FROM (  SELECT (date_trunc('month', now())::date - s.a * '1 mon'::interval)::date as month
                   FROM generate_series(0,60) as s(a)
               ) as intervals
           JOIN (  SELECT device_id,system_id,installdate, null as modified FROM dev_state
                           UNION
                           SELECT device_id, system_id, installdate,modified FROM dev_state_history
                           WHERE dev_state_history.deleted = true
                   ) AS dev_state
                 ON (dev_state.modified > intervals.month or dev_state.modified is NULL)
                 AND dev_state.installdate < system_id="system_id_types.id" system_type="device_types.id" device_id =" dev_state.device_id" id="dc.sector">
See our "dblink_connect_ripit_2" function which specifies the user we want to connect as and which database server we want to use?  Note that we can use $$ for quoting here.  My first query was crazy full of escaped quotes until I realized that $$ quoting worked just fine in views as well as functions.

KPI part 1: database/XML mapping populates Key Performance Data charts

noreply@blogger.com (Heather Sherman) — Thu, 14 Jan 2010 19:21:00 +0000

Recently we were asked to develop a page of Key Performance Indicator charts showing network availability and other network capacity statistics. We have a lot of historical data, scattered throughout flat files, RRD files and in our PostgreSQL database. In the past we've responded to similar requests by producing ad-hoc charts and tables, ending up with several web pages with disparate look and feel and levels of detail.

This time, we wanted to come up with a complete system that could be re-used; settling on a preferred (preferably javascript-based) flexible charting system, a consistent look and feel and a manageable way of storing the underlying data. Ideally some of those ad-hoc charts would even be migrated to this new system.

Just identifying how to calculate the data for the initial KPI was challenging (when you measure average network availability for a campus network, where do you measure? Do you weight the 'service points' equally? And what's the smallest meaningful time range to show?)

We determined that UW campus network availability would be measured by 'average uptime of unweighted subnet prefix' and that we would show average availability over week, quarter and year.

So, where do we get this data? We merge it from several of our production network management tools:

Database We use a PostgreSQL database to model our network. The Network Operations Center maintains tables of devices, interfaces, networks, contacts, events and all their related attributes, using a mixture of manual addition and automated device discovery systems.

Meerkat historical data Meerkat is our locally developed open source network monitoring system. It tracks all network events (writing them to the database) and generates alerts for the Network Operations Center. We have a companion web service which can provide availability information for devices or network prefixes from the detected network events.

Meerkat availability service The companion availability service reports raw availability for a given device or network prefix. Device availability is easy: if there's an event showing the device was unavailable in our database, its raw availability for the year is reduced by the length of the outage window. Determining network availability requires topology discovery and awareness of redundant paths. Only if outage events occur interrupting all paths from the monitoring system to the network will the raw availability of the network be reduced.

RT::Outages planned event tickets RT::Outages is locally written tool which interacts with the RT trouble ticket system. It provides a workbench out network operations staff can use to generate outage reports. The workbench allows selection of devices and interfaces involved in an outage and provides a date picker for consistent entry of outage window start and end times. Contact lists are automatically generated from the database device and network contact lists. And, essential for the KPI project, RT::Outages is also used to schedule planned events.

For the KPI project, some changes had to be made to enhance the Meerkat availability service. Availability isn't conventionally calculated on all network downtime; just on unplanned outages. The RT::Outages service has that information available and we use that to permit the Meerkat availability service to exclude those events. In addition, the service only provided an annual availability measurement. To get quarterly and weekly measurements we updated it to accept arbitrary time ranges.

Now that we knew where to get our raw information what would we do with it?

We started by writing a nightly tool to fetch the availability of each of the "service points" on our network (service points are network prefixes for the UW campus and UW Medical Centers networks, sites for the Washington K20 networks). To enable this, we made use of another locally developed open source initiative:

REX REX is a SQL/XML mapper that provides a framework for turning PostgreSQL database queries into XML documents available through a REST service. It leverages the XML functions available in PostgreSQL. In addition to fetching information from the database this way, we can also update the database.

The nightly process, for example, is a Perl program that connects to a REX-based REST service for the list of service points, processes each service point by sending a request to the Meerkat availability service for the availability for the previous day, then uses XML::Simple to generate an XML document to post back to a final service which updates the database with all the service point measurements. Given a hash of measurements:


$VAR1 = {
        'measurement' => [
                           {
                             'owner' => 'uwcampus',
                             'availability' => '100.0',
                             'includes_planned_events' => 'false',
                             'service_point' => '128.95.138.0/24',
                             'datetime' => '2010-01-12'
                           }
                         ]
      };

We update the database with the following XML document:


<measurements>
 <measurement availability="100.0" datetime="2010-01-12" includes_planned_events="false" owner="uwcampus" service_point="128.95.138.0/24" />
</measurements>

using the following code, using Perl, XML::Simple and LWP::UserAgent::Determined:


post_measurements( { ua => $ua,
                       post_document => XMLout ($measurements, RootName=>'measurements')
} );

sub post_measurements {
  my ($param_ref) = @_;
  my $post_document = $param_ref->{post_document} || croak 'No document to post';

  my $ua       = $param_ref->{ua} || LWP::UserAgent::Determined->new;
  my $url      = "https://$options{ws}/rex/xml/kpi_availability/ADD_DATA";
  my $request  = HTTP::Request->new('POST', $url);

  $request->content_type('application/xml');
  $request->content($post_document);
  my $response = $ua->request($request);
}

Typically we send multiple measurements at a time. This produces a table full of measurements, one per day per service point.

When we display them, however, we want to summarize the daily service_point measurements to weekly, quarterly and annual measurements for an entire collection of service points (e.g. the service points in the UW campus network or in the K20 statewide network). For our prototype, we intended for the front end to process XML, so we wrote a PL/pgSQL function (see end of post) to generate an XML document. The REX-based service supplying data to the front end was configured to run this on demand. For more information on using the xml functions in PostgreSQL, see http://www.postgresql.org/docs/8.3/interactive/functions-xml.html

We soon found that running this on demand was much too slow (seven seconds!) and that the pchart system we initially used didn't provide all the features we wanted and locked us in to PHP.

Stay tuned; in my next posts I'll outline how we implemented Materialized Views to rescue us from performance problems like those experienced above and how we implemented our final Javascript-based front-end using Google Charts via the Google Visualization API.


CREATE FUNCTION rex_fn.kpi_availability_read(owner_selected text, year_selected text,
                                            include_planned_events boolean)
RETURNS xml
AS $$
BEGIN
/* FUNCTION:     rex_fn.kpi_availability_read()
  Description:  Summarizes weekly, quarterly and yearly availability measurements
  Affects:      Makes no changes to the data    
  Arguments:    name of a supported owner, a year and whether or not the measurements should include or
                exclude planned event windows
  Returns:      xml document like:

<availability>
  <yearlyavailability>
      <plannedincluded>true</plannedincluded>
      <owner>
          <name>k20</name>
          <year>
              <number>2009</number>
              <averageavailability>99.8159518537013916</averageavailability>
              <quarter>
                  <number>1</number>
                  <averageavailability>99.8047910271571390</averageavailability>
                  <complete>true</complete>
                  <week>
                      <number>01</number>
                      <averageavailability>99.6165324837199837</averageavailability>
                      <start_date>1230796800</start_date>
                      <end_date>1231315200</end_date>
                      <complete>true</complete>
                  </week>
              </quarter>
          </year>
      </owner>
  <yearlyavailability>
</availability>
*/
BEGIN
  RETURN xmlelement (name availability,
                     xmlagg(availability_frag)
         )
  FROM (  SELECT xmlelement (name yearlyAvailability,
                             xmlconcat (xmlelement (name plannedIncluded, include_planned_events),
                                        xmlagg (xmlelement (name owner, owner_frag))
                             )
                 ) AS availability_frag
          FROM (  SELECT xmlconcat(xmlelement (name name, owner),
                                   xmlagg(xmlelement (name year, year_frag))
                         ) AS owner_frag
                  FROM (  SELECT owner, year, xmlconcat (xmlelement (name number, year),
                                                         xmlagg (quarter_frag)
                                              ) AS year_frag
-- Clause to produce the "yearly availability"
                          FROM (  SELECT owner,
                                         to_char(datetime,'YYYY') AS year,
                                         xmlelement ( name averageAvailability,avg(availability) ) AS quarter_frag
                                  FROM kpi.availability
                                  WHERE owner = owner_selected
                                    AND includes_planned_events = include_planned_events
                                    AND to_char(datetime,'YYYY') = year_selected
                                  GROUP BY owner, year
                                UNION ALL
-- Call helper function to produce the xml for the "quarterly availability"
                                  SELECT *
                                  FROM rex_fn.kpi_availability_read_quarter (owner_selected,
                                                                             year_selected,
                                                                             include_planned_events)
                          ) AS sub4 GROUP BY owner, year
                  ) AS sub3 GROUP BY owner
          ) AS sub2
  ) AS sub1;
END;
$$
  LANGUAGE plpgsql;

CREATE FUNCTION rex_fn.kpi_availability_read_quarter(owner_selected text, year_selected text,
                                                    include_planned_events boolean)
RETURNS SETOF kpi_availability_quarter_record_type
  AS $$
/*  FUNCTION:     rex_fn.kpi_availability_read_quarter()
  Description:  helper function for rex_fn.kpi_availability_read -- generates quarterly xml subtree

  Affects:      Makes no changes to the data    
  Arguments:    name of a supported owner, a year and whether or not the measurements should include or
                exclude planned event windows
  Returns:      set of xml documents
*/
BEGIN
  RETURN QUERY    SELECT owner, year, xmlelement(name quarter, xmlconcat (xmlelement (name number, quarter),
                                                                          xmlagg (week_frag)
                                                               )
                                                    ) AS quarter_frag
-- Clause to produce the "quarterly availability"
                  FROM (  SELECT owner, to_char(datetime,'YYYY') AS year, to_char(datetime, 'Q') as quarter,
                                 xmlconcat (xmlelement ( name averageAvailability,avg(availability) ),
                                            xmlelement ( name complete,
                                                         CASE WHEN to_char(max(datetime),'Q') != to_char( max(datetime) + interval '1 day','Q')
                                                              THEN TRUE
                                                              ELSE FALSE END
                                            )
                                 ) AS week_frag
                          FROM kpi.availability
                          WHERE owner = owner_selected
                            AND includes_planned_events = include_planned_events
                            AND to_char(datetime,'YYYY') = year_selected
                          GROUP BY owner, year, quarter
                        UNION ALL
-- Call helper function to produce the xml for the "weekly availability"
                          SELECT * FROM rex_fn.kpi_availability_read_week (owner_selected,
                                                                           year_selected,
                                                                           include_planned_events)
                  ) AS sub GROUP BY owner, year, quarter ORDER BY year, quarter ASC;
  RETURN;
END;
$$ 
  LANGUAGE plpgsql;

CREATE FUNCTION kpi_availability_read_week(owner_selected text, year_selected text, include_planned_events boolean)
RETURNS SETOF kpi_availability_week_record_type
  AS $$
/*  FUNCTION:     rex_fn.kpi_availability_read_week()
  Description:  helper function for rex_fn.kpi_availability_read -- generates weekly xml subtree
  Affects:      Makes no changes to the data    
  Arguments:    name of a supported owner, a year and whether or not the measurements should include or
                exclude planned event windows
  Returns:      set of xml documents
*/
BEGIN
  RETURN QUERY    SELECT owner,
                         year,
                         quarter,
                         xmlelement(name week, xmlconcat (xmlelement (name number, week),
                             xmlagg ( xmlelement (name averageAvailability, averageAvailability) ),
                             xmlagg ( xmlelement (name start_date, extract (epoch from start_date)) ),
                             xmlagg ( xmlelement (name end_date,  extract (epoch from end_date)) ),
                             xmlagg ( xmlelement (name complete,
                                        CASE WHEN start_date + interval '1 week' = end_date + interval '1 day'
                                             THEN TRUE
                                             ELSE FALSE END)
                                     ) -- xml element week
                             ) -- xmlconcat
                         ) AS week_frag
                  FROM (  SELECT  owner                                               AS owner,
                                  to_char(week_start_date(datetime,'Mon'),'YYYY')     AS year,
                                  to_char(week_start_date(datetime,'Mon'), 'Q')       AS quarter,
                                  to_char(week_start_date(datetime,'Mon'),'WW')       AS week,
                                  min(datetime)                                       AS start_date,
                                  max(datetime)                                       AS end_date,
                                  avg(availability)                                   AS averageAvailability
                          FROM kpi.availability
                          WHERE owner = owner_selected
                            AND includes_planned_events = include_planned_events
                            AND to_char(week_start_date(datetime,'Mon'),'YYYY') = year_selected
                          GROUP BY owner, year, quarter, week
                  ) AS sub
                  GROUP BY owner, year, quarter, week
                  ORDER BY quarter, week ASC;
  RETURN;
END;
$$
  LANGUAGE plpgsql;

jPop: Binding Javascript Objects to HTML Automatically

noreply@blogger.com (dharmabruce) — Tue, 17 Nov 2009 23:27:00 +0000

jPop is a jQuery plugin that will automatically bind data from javascript object to the contents of html elements. Example:


<html>
 <head>
  <title>jPop Example</title>
  <script src="/js/jquery/1.3.2/jquery-1.3.2.min.js" type="text/javascript"></script>
  <script src="/js/jquery.jpop.js" type="text/javascript"></script>
 </head>
 <body>
  <h1 id="greeting" class="jpop_class"></h1>
  <script type="text/javascript">
    $(function() {
      var jso= { greeting: "Hello, World!" };
      $('.jpop_class').jpop(jso);
    });
  </script>
 </body>
</html>

Download jPop.

jPop works with any javascript object and doesn't put restrictions on the data structure. Here is a more complicated example which demonstrates how jpop handles complex data structures and arrays:


<html>
 <head>
  <title>jPop Example</title>
  <script src="/js/jquery/1.3.2/jquery-1.3.2.min.js" type="text/javascript"></script>
  <script src="/js/jquery.jpop.js" type="text/javascript"></script>
 </head>
 <body>
  <h1 id="greeting" class="jpop_class"></h1>
  <p id="greetingProp.greeting" class="jpop_class"></p>
  <p>And,
   <span id="greetingProp2:greetingArray" class="jpop_class">
    <br><span id="greetingArray.greeting" class="jpop_class"></span>
   </span>
  <script type="text/javascript">
    $(function() {
      var jso= {
          greeting: "Hello, World!",
          greetingProp: {
              greeting: "Hello again, World!"
          },
          greetingProp2: {
              greetingArray: [
                  { greeting: "Hello, Nick!" },                  
                  { greeting: "Hello, Sri!" },
                  { greeting: "Hello, Jody!" },
                  { greeting: "Hello, Andrew!" },
                  { greeting: "Hello, Heather!" },
                  { greeting: "Hello, Ben!" }
              ]
          }
      };
      $('.jpop_class').jpop(jso);
    });
  </script>
 </body>
</html>

The primary convention is that html element ids are used to reference into the javascript object. In the first example, 'greeting' is a property on the javscript object 'jso' which has the string "Hello, World!". When an html id contains a ':' that will cause the containing html to be cloned and appended to the element and the property name after the ':' can be used to refer to elements of the array within the contained html.

jpop only binds to the elements selected by the jQuery expression. It is not required to use the 'jpop_class" or any other class.

If html form elements are jpopped, the value attribute will be set. Further, when the form is filled out (i.e. a 'change' event occurs), jpop will automatically modify the javascript object with the new form values. Example:


<html>
 <head>
  <title>jPop Example</title>
  <script src="/js/jquery/1.3.2/jquery-1.3.2.min.js" type="text/javascript"></script>
  <script src="/js/jquery.jpop.js" type="text/javascript"></script>
 </head>
 <body>
  <form>
   <input type="text" id="greeting" class='jpop_class'>
   <input type="submit" value="modify greeting">
  </form>
  <script type="text/javascript">
    $(function() {
      var jso= {
          greeting: "Hello, World!"
      };  
      $('.jpop_class').jpop(jso);
      $(':submit').click(function() {
          alert(jso.greeting);
          return false; 
      });
    });
  </script>
 </body>
</html>

Project Updates

noreply@blogger.com (dharmabruce) — Mon, 19 Oct 2009 23:16:00 +0000

It seems like every moment is only more busy than the last. We are working on Network Systems Key Performance Indicators reports (KPI; what we do and how well we do it), Circuit/Network Diagrams in a web-friendly manner (LinkViz), Equipment (Asset) Database (EDB) focusing on tracking costs and budgeting, and BGP monitoring (Meerkat BGP). Facing inward, we're improving the efficiency of developing web services built on our postgresql database (rex and automatic html/javascript object binding).

The primary goal of the KPI project is to report on the depth and breadth of Network Systems services and, further, report the quality of those services. Service availability was the first chart we deployed. The user can include or exclude planned outage windows from the report. The variable represents the average of all customers' availability. An individual customer's availability is defined as the percentage of time the customer's service point (gateway) was determined to be available (pingable from our poller).

In our LinkViz project, we are making another attempt at automatically visualizing circuits and networks in a way that a Network Engineer might diagram. In my opinion, automatic Network Topology "christmas trees" have always gotten the problem wrong. It looks like a Software Engineer is solving the problem using Software Engineering visualizations from graph theory instead of trying to mimic what a Network Architect or Engineer would draw for themselves and their peers. This problem is ripe for innovation and we're trying to take a baby step towards xanadu here. We considered using flash, directly generate a Visio file, and several javascript canvas libraries before deciding to go with the html/css box modeling directly. We need to refine the layout but here's a simple example showing where we are now:

The UW Technology Equipment Database project is an effort to converge the different databases from each unit into a single database so that costs can be centrally tracked and we can improve our equipment budget projections. The new functionality is to track next-step lifecycle decisions such as retire, replace, etc., with a date when action needs to occur.

We were surprised to find that we were monitoring nearly 1000 BGP sessions. While we've been managing the bgp monitoring config manually, I had no idea there were this many already. This project is to automate BGP session discovery and monitoring configuration.

Facing inward, we've taken several steps to improve how efficiently we develop open, ReST-like, database backed web services. We try to spend a sensible amount of time improving our common tasks so we can deliver them rapidly. Two successes here "rex" and html/javascript binding.

Rex allows you to map ReST HTTP requests directly to database queries that generate XML. Since we write a lot of services that simply do a simple transformation on the database data and expose it in a ReST-like manner, it was important for us to normalize how we do this and make it more efficient. After setting up and configuring rex with your jdbc datasources, you can start configuring HTTP endpoints directly to queries. Once a RexMap is created, your web service is immediately available. Rex also provides a json endpoint that exposes the badgerfish transformed xml data. Rex exposes a test interface for entering parameters and displaying the syntax highlighted xml or json results. It is hosted on sourceforge.

The last efficiency improvement we've gained is using a library that allows you to bind a javascript object (jso) to html directly. It's kind of like XPath and XSLT but using a jso instead of XML and html directly instead of XSLT. It is very similar to the chain.js jquery plugin. The downside of chain.js is that it requires you to format your data in the way it expects (e.g. arrays within the jso must have the key "items"). The html/jso binding approach we took was to simply match the html id to the jso "key".

For example, to bind an html class "equipment_bind" to a javascript object asset, you do this:
var asset={ Name: "ucswww" };
var equipment=HtmlJsoBind("equipment_bind", asset);
equipment.bind();

The html would look like this:
<p id="Asset.Name" class="equipment_bind"></p>

After equipment.bind() is called, the CDATA portion of the paragraph will be filled with the Asset.Name "ucswww".

The primary win here is that you don't have a ton of javascript code that is simply creating html to wrap your data. Our library also handles arrays.