Meeting recap!

John Naylor entertained a packed room at FreeGeek with stories from his time as a data manager with the Obama campaign. Especially interesting to me was the way he pulled together and verified voter data from a number of sources. I sure wish I'd known about the Geo::StreetAddress::US Perl module before I completed a similar project (albeit on a much smaller scale - several orders of magnitude smaller) for a local organization.

Also: we had bacon. And donuts.

See you next month for "The Drama of a Fully Versioned Database in MySQL" with Ben Hengst.

We totally missed our 3rd anniversary last month! Congrats to everyone for keeping things going over the past few years.

Last night, we heard from Jim Cser about Metro's Economic & Land-use Forecasting. Metro's in charge of the famous Urban Growth Boundary (search www.oregonmetro.gov for "UGB" for more info). We're required by OR law to maintain a 20-year supply of land within the UGB, and the UBG is reviewed every 5 years. "Metroscope" combines land-use & transportation data modeling to create forecasts. The hardest part is taking the forecasting data & turning it into information the city planners & policy makers can actually use. Jim gets to make some really cool maps with it. :) Thanks, Jim!

Then we retired to the pub & crashed the PDX.pm hackathon.

Next meeting, Thurs Sep 17: "Unit Test Your Database" with David Wheeler. It's also getting to be time for our annual Relational Algebra Cocktail Party, so be thinking about what you'd like to learn/teach/drink.

Other announcements:
- PGWest is coming up - Oct 16-18 in Seattle.
- There is an OpenGIS group in Portland - PDX OSGIS google group, check it out.

Here are my rough notes from the meeting. Thanks a ton to Kristin for giving a great talk! Thanks also to Gabrielle for providing gin & tonics.

Professor Kristin Tufte, Portland State University
Portal dataset
http://portal.its.pdx.edu

* Only realtime data - Loop Detector Data
** 20 s count, per lane, # of vehicles average
** happy if 40% of the data is fine
* Crash data in portland metro since 1999
* Bus data (not in realtime)
** every time they stop, they record how many ppl got on/got off, door open, lift go up/down
** made a speed map for portland

* FOCUSING ON Loop detector data
** student looked at the car data in portal, and analyze what the estimated emissions are
** proposal is to look at what the emissions were and create some "green" metrics based on the collected data

* INTERESTING METRIC:
** Estimated 137k hours in the car in one day
** Estimated 7million miles on oregon roads

* Speed contour plot
** 20k reported incidents per year
** Website -- Portlan website -- portal: portland oregon regional transportation archive listing

* Data being grabbed:
* 5-minute - every 5 minutes, rolled up
* 15-minute and 1hr - every night (but moving to every 15 minutes)

* GOALS:
** store the data
** show the pretty pictures
*** researchers want the raw data, but they can probably wait a little while to get it

* 20 second data:
** 1 table per day:
*** monthly view - union of all daily tables
*** 2.8 million tables in each table
** 4 billion tuples total, 5-minute aggregation; 1-table/month - 6 million tuples

* QUERIES
** Lotsa JOINs, reducing the # of EXTRACTs (better performance)
** Lots of SELECT * from today's data

* Misc comments:
** parameterized queries can't use partial indexes
* Dual path SaS
** Linux multipath -- active/passive
** can't get dual channel active/active

EPQA - sourceforge.net/projects/epqa/

* RECOMMENDATIONS:
** Upgrade to 8.3
** fio test on local disks and SAN and find out if there's a contention, as its a shared SAN with RAID5, Get access to SAN stats to review
** Reindex on a weekly basis
** Review postgresql.conf settings - increase shared buffers to ~4GB
** Check out this blog post from Josh Berkus: http://it.toolbox.com/blogs/database-soup/how-do-i-examine-the-linux-pag...

This past weekend, Selena, Mark & I loaded up Mark's car with flyers, mugs & t-shirts & headed up to Bellingham, WA for Linuxfest NW (website: http://linuxfestnorthwest.org/).

Several LUGs in the area host this annual conference. This was my first Linuxfest & I was really impressed - it was very well-organized, well-attended and FUN.

Mark gave his talk about ptop/pgtop first thing Saturday morning. We managed to see some other talks too - Selena checked out some Drupal talks & I went to Eric Hopper's IPv6 discussion on Sunday.

We all had a great time running the booth, despite the lack of JD's entertaining presence. He was replaced by surprise guest boothster, Chris Travers! Chris showed up to give a LedgerSMB talk and we conned him into boothbeasting with us. Thanks, Chris! Even though it was crowded behind our table, I think four staffers was the right (minimum!) number to have for handling questions & greeting people.

The conference was really packed. I think I talked at length to somewhere around 12-15 people. We had a HUGE range of questions, all the way from people wondering about replication strategies to "What's a database, anyway?" We also ran through the inevitable "how do you pronounce it?" Selena gave a long demo of pgadmin to someone who is currently using SQL Server. A lot of people are very interested in a MySQL -> PostgreSQL migration tool.

We ran out of almost all printed materials we'd brought on the first day, necessitating phone calls to Josh B to acquire more flyers and a local copy shop to print them. Selena had the brilliant idea to get some elephant logo stickers printed as well; they were very popular.

Saturday night, Silicon Mechanics hosted the afterparty at the American Museum of Radio & Electricity, a truly excellent site for a geek party! We got to dink around with static electricity machines & toys from past centuries. Selena stepped up to the theramin while Mark & I checked out the old music boxes. The best part of the evening for me was when the docents set up a small tesla coil. >:) We had a great little indoor lightning show & Selena became part of a multi-person chain that lit up a fluorescent tube.

Back home now, attempting to recover from Conference Brain.

Thanks everyone who came out for the meeting! Next meeting on our schedule will be about Python and PostgreSQL, courtesy of Jason Kirtland.

We had a terrific turnout, and four new people. One of the FreeGeek folks came and talked with me after the meeting about how he had changed the database that FreeGeek uses from MySQL to PostgreSQL a while back.

We had two people from InnoTech attend that had also seen my 5-minute schpeale. One had already planned on coming (Hi Mike!), but I take credit for getting Craig interested! The other new person, Ed, works with Mike on Rails application development.

ANNOUNCEMENTS:

PDXPUG PgDay before OSCON is happening July 20, 2008! We have some volunteers to help organize it, I'm setting up a mailing list. Those who volunteered, expect some email this weekend about kicking things off.

PostgreSQL Conference East was a total success! ~100 people
Check out details at http://www.postgresqlconference.org/

MEETING DETAILS:

David's presentation was both an introduction to Rails development, and a tutorial on getting Rails to work with PostgreSQL. Some of his examples came directly from his work on I Want Sandy (www.iwantsandy.com), a web-based product that uses PostgreSQL.

There was intense discussion of drivers with thank-yous to Jeff Davis for adopting the Ruby PostgreSQL driver. There were useful, detailed examples of how to create data migrations and develop queries for Rails. There was a lot of monkey patching.

I enjoyed this talk so much. You can find audio of David's presentation of this talk at PostgreSQL Conference West here: http://www.postgresqlconference.org/fall2007_audio/RoR_Essentials.mp3

David is going to pass the updated slides on to me, and I'll post them on http://pugs.postgresq.org/pdx

Mark also brought the T2000 that was donated to the new Performance Lab that Mark is setting up. He's working with Joshua Drake on getting the machine racked and set up to run tests! Gabrielle used her serial-fu to help him get the machine booted and running.

Many of us retired to the Lucky Lab for refreshments.

Just quickly uploading some slides from the meeting. Covered all the crazy conferences coming up soon, and included a plug for Summer of Code and PostgreSQL -- if you're a student, its $4500 for the summer and great experience! Check it out!!

Also, a few slides about USPA and what we're up to!

Check out Joshua's recap of our ice-breaker over on his blog.

Ed Sawicki gave an overview of spam supression techniques he's using and gave us a few tips on tools that were useful to him. We recommended he have a look at some CIDR address types available on pgfoundry.org to help with some performance issues he found.