PostgreSQL QA Platform

http://wiki.postgresql.org/wiki/QA_Platform_hosted_at_Command_Prompt

This past weekend, Josh Drake, Mark Wong, & I spent about 12 hours re-organizing the commandprompt colo facility: building new racks, shuffling existing servers, installing new ones, and running new cable. (#31 in Josh Berkus’s ’50 ways to contribute’ talk: http://www.powerpostgresql.com/download/TFCKUpload/21.x-pdf.)

Commandprompt is hosting the PostgreSQL QA Platform lab (aka “PostgreSQL’s Portland Performance Pad”, aka “The P4 Lab.”) Equipment for this project was donated by HP, Sun, and IBM.

Watch the wiki for updates to the DL380 Tuning Guide: http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide

Cisco::Reconfig

Poking around on CPAN a week or so ago, I stumbled across Cisco::Reconfig.

It looked pretty interesting, and turned out to be the work of David Muir Sharnoff. He’s responsible for Net::Netmask, which is one of my five favorites, so I had to check it out.

Sample router config (not all lines shown):

interface Loopback0
 ip address 10.254.254.1 255.255.255.255
!
interface Ethernet0/0
 description Admin LAN
 ip address 10.10.1.1 255.255.255.0
 duplex auto
!
interface Serial0/0
 description to Internet ID W065432
 ip address 1.1.1.1 255.255.255.252
!
interface Ethernet0/1
 no ip address
 shutdown
!
interface Ethernet1/0
 description User LAN
 ip address 10.10.2.1 255.255.254.0
 duplex auto
!
interface Ethernet1/1
 ip address 10.10.4.1 255.255.254.0
 duplex auto
!

Let’s write a quick & dirty script^W program to look for blank interface descriptions, because that’s something I find really annoying:

#!/usr/local/bin/perl

use strict;
use Cisco::Reconfig;

my $host        = "gabrielle";
my $host_config = "$host.confg";
print ("Checking: $hostn");
my $config  = readconfig("$host_config");

for my $intf ( $config->get('interface') ) {
    next if ( $intf->get('shutdown') );	#ignore, don't care
    next if ( $intf =~ /Loopback/ );	#idc

    my $descr   = $intf->get('description');
    chomp ($intf, $descr);  	# hm, kinda feel like I shouldn't have to do this?
    print ("$intf: $descrn");	# just to be chatty
    if (! $descr) {
        print ("$host: $intf: Description is blank!n");
    }
}

exit 0;

:::–>./sample.pl
Checking: gabrielle
interface Ethernet0/0: description Admin LAN
interface Serial0/0: description to Internet ID W065432
interface Ethernet1/0: description User LAN
interface Ethernet1/1:
gabrielle: interface Ethernet1/1: Description is blank!

I’m primarily interested in it for checking compliance of our configurations with our business standards. No need to chunk through a config line-by-line with regexps; in a lot of cases, a simple ‘get’ will tell me if something’s configured or not (eg “snmp server-location”.)

Specific features that look really intriguing:
– you can generate the commands you need to “fix” your config
– the “context” method allows you to draw out the surrounding lines

I can’t wait to mess around with this some more.

PDXPUG Meeting recap – Logic and Databases with Jeff Davis

[ Jeff’s slides are available at: http://www.pgcon.org/2008/schedule/events/83.en.html ]

Last night’s meeting was Jeff Davis’ last presentation as a Portlander (for a while at least!). He is leaving his job at Laika to pursue streaming database nirvana at Truviso, a company whose database product is based on PostgreSQL. There’s a bunch of whip-smart people working there, and Jeff will fit right in. If you’ve got other questions about what Truviso does, feel free to ask Jeff.

Our “question of the day” was – What are your plans for the summer? We had some great answers — including that Len’s son Ari is hosting the morning NPR news show for the next two weeks! Go Ari! It sounds like James *might* be leaving us for Redmond — stay tuned for more news on that. Gab will be riding her bike, and other people are going to just enjoy the fact that it finally seems like it *is* actually summertime.

Mark Wong is working with Joshua Drake (and Gab?) to install our new Performance Lab downtown on Saturday. Look for updates and announcements about what those servers will be used for from Mark and JD next week. HP donated two of the machines, and I believe that one other came from Sun.

We also had cocktails served by Gabrielle. THANK YOU, GAB!

Jeff’s presentation started with using logic to help express in English basic SQL concepts. For example, JOIN can be expressed with AND, and UNION can be expressed with OR. Thinking about what questions you’ll be asking about the data, and also forming those questions and statements of fact in ways that allow the assignment of clear ‘true’ or ‘false’ values will help enormously in ensuring that your data structures and SQL queries work the way that you and your customers expect.

At the risk of butchering the next discussion, here’s my take on the second part: Jeff spent quite a bit of time talking about the merits of using statements of fact when describing states, and how focusing on the truth values helps you test your assumptions about results from queries. His closing words on this topic: “Use an iterative process to clean up data before migrating it to a new system: make assumptions, test the assumptions, revise the assumptions, and repeat.”

He used the example of “pending” versus “approved by purchasing but not yet received”. This sparked a lively debate about the terminology – is it accurate to say that “pending” is Context Sensitive, and “approved by purchasing…” is Context Insensitive? Most of us seemed to agree that the second example was more useful — although Len Shapiro made the case that “pending” wasn’t so bad. He made the case that we just needed a Data Dictionary to explain it in the application. James brought up that programmers and businesses are lazy, and are unlikely to keep a piece of documentation like that up-to-date. There was a lot more discussion about this.. but at that point, I think I asked Gab for another drink, and then got Len a second drink.

In the third segment, Jeff went over two ways that the SQL standard treats NULLs – as UNKNOWN or as NO VALUE. In particular he shared these two statements:

test=# select sum(column1) FROM (values (1), (NULL)) t;
sum
—–
1
(1 row)

test=# select (1 + NULL) as plus;
plus
——

(1 row)

We chewed on that for a bit. In the first case, NULL is being treated as “no value”, and just ignored by SUM(). In the second case, NULL is being treated as UNKNOWN – as the NULL is not treated as the same data type (or domain) as ‘1’, and so the answer is NULL.

(oh boy, i hope i got that right, or there’s going to be a long thread to explain this :D)

So, then James brought up the 17 possible meanings for NULL, and how our puny human brains could not compute. Several people made exploding brain noises, we continued to talk about NULL and aggregate functions and how COUNT() seems to violate ALL the proper rules, and… whew.

—

It was almost 9pm, and we all decided it was time to retire to the Lucky Lab.

Len brought me up to speed on Tom’s latest GSOC work, and I said that I would help test out his graphical Planner analyzer tool. It’s something that helps you understand why it is that the Planner chose the plan it did. I’m sure that I’ll be able to speak more articulately about it after I play with it :)

This week in geekville.

PACUG‘s 2nd meeting. (Well, probably not their second meeting *ever*, but the second meeting since they started back up recently.) Phillip James gave a talk about Network Access Control, which I expected to be about things like 802.1x. Turns out I wasn’t reading the mail list closely enough – his talk was specifically about Cisco’s NAC appliance, including very detailed instructions on how to deploy it. He’s going to sanitize his slides a bit and pass them by the appropriate authorities & they should be publicly available next week.

Cisco’s NAC appliance (which is made up of several parts: a manager, a server, agents) sits in-line (usually; there are OOB options coming up) at various points on your network and controls who’s allowed to access which corporate resources. You can allow the same user different access permissions based on what type of system they’re on (eg Mac/Windows, *nix), who owns it (e.g., user’s personal equipment vs corporate), how they’re connecting (e.g., LAN, VPN), and the status of various applications on the machine (e.g., is their AV up-to-date). Licensing, of course, is dependent on the number of users & sites.

Four actions the NAC performs:
1. Identify the device & user
2. Enforce policies in a consistent manner (HR departments probably require consistent enforcement across all users in order for necessary disciplinary action to be taken)
3. Quarantine & Remediate non-compliant equipment
4. Configure & manage access policies

Steps to deployment (this is actually a useful checklist for *any* application deployment, IMO):
– gather your list of contacts. Phillip included an exhaustive list of all the contacts you need to have within the organization
– technical requirements analysis
– ops requirements analysis (eg, training, how many licenses)
– design phase
– lab testing (you have a lab, right?)
– field testing at select sites; rule of thumb is 10-15% of your final deployment
– production deployment

A good test plan:
– deploy it first as audit only. This gives you a baseline of compliance before you start enforcement. (And allows you to troubleshoot piece-by-piece instead of just dropping it all in there at once.)
– next: checks without enforcement. User is given a popup notification that they are out of compliance with whatever policy, and they have the option to correct the issue now or bypass it.
– once compliance is at an agreed-upon level (say, 85-90%), enable checks with enforcement. From my experience, if you wait for a level of compliance from users (esp if they’re given the option to bypass it), you’ll be waiting a loooooooong time. My preference would be to give them a cutoff date instead.
– make sure the test plan has an explicit definition of a “successful” test.

Phillip finished up with my favorite part of any presentation: War Stories. (He called them “Tips from the Field”.) One gotcha to consider is that tcpdump will only show traffic destined to the NAC appliance, not through it.

I would have liked to have heard a bit more about how the NAC actually operates – it sounds like the connection gets transferred around between different VLANS – one for authentication, then to a different one depending on the permissions granted to the user, which is pretty intriguing.

After the presentation, we had a short “general networking Q&A”. I should have come prepared with questions. (Maybe about monitoring Metro Ethernet connections.) A few Cisco SEs were present to answer questions.
Some items of interest to me:
Apparently there is a bug/feature in IPv6 reflexive ACLs. Good to know in advance.
VPN Tunnel issues: sometimes the tunnel will show as up, but is not passing traffic, and you have to bounce it (“clear crypto sa” IIRC). It would be nice to have a warning about this situation. Supposedly the interface should be flapping if this is going on, which should generate an SNMP trap. I need to look into that.

The next meeting is about Virtual Switching System (VSS), and I’m sad I’m going to miss it. (Due to the holiday weekend, the meeting’s been moved to July 9th, which conflicts with PDX.pm.)