18 August, 2014

My PostgresOpen schedule

by gorthx

We had so many good submissions for Postgres Open this year, we had to make some very difficult choices. While I haven’t quite achieved Dan Langille’s (of PgCon fame) level of conference-running zen and rarely get to all the talks I’d like to see, here are my picks:

The PostGIS tutorials. I am really excited about this tutorial, and that Regina Obe and Leo Hsu will be at our conference!

Bruce Momjian and Vibhor Kumar’s NoSQL on ACID (plus pizza and root beer!) Most everyone knows who Bruce is, but you may not know Vibhor – I got to work with him at EDB, and he’s very kind and always had answers to my most obscure questions. This tutorial is free, but you have to register in advance. If you’re coming to the conf early, this is a great thing to do on your first evening.

Denish Patel gives great presentations, and I’m looking forward to learning about Pg on RDS from him.

Streaming replication was first introduced in 9.0. Simon Rigg’s Pg Replication Overview will bring you up to speed on the new developments since then.

You can read the 9.4 release notes or just go to Magnus Hagander’s talk.

I’m really interested in the tools that Gleb Arshinov used for his customer retention analysis use case.

Jonathan Katz’s data types tour has had rave reviews, and I’m glad he’s giving it again here.

Jeff Amiel’s Monolithic Query Syndrome talk looks educational and entertaining.

I can’t decide between Sehrope Sarkuni’s Audit logging talk and Gary Seiling’s discussion about immutable data. (Note: if you are new to Postgres and configuring its security features, I recommend pairing one of these with Sarah Conway’s introductory talk on Thursday.)

Full text search is a hot topic right now.

I will almost always choose a monitoring talk over any other topics, so Shaun Thomas’s collectd and graphite talk is on my list.

What’s the state of Row level Security in Postgres? Go to this talk by Stephen Frost and find out.

John Melesky is a fellow PDXPUGer and always bring interesting problems to the table. This time he’s talking about partitions.

It’s not too late to join us in Chicago!

13 June, 2014

Postgres Monitoring Wishlist

by gorthx

Since “what should I monitor in my database” has come up in conversation several times lately, I thought I’d put this here where I (theoretically) won’t lose it. I’ll save for later the discussion of where to get this info and which tools give me which stats :)

Bare minimum:
server CPU, memory, I/O, network usage, and all “slow” queries logged.

More extensive:
System stats:
CPU usage, per-proc if available
Memory usage, including swap
disk usage (in terms of space – pay special attention to database partitions)
disk I/O
disk busy
network stats, including errors (if you have a Cisco network & are friends with the network team, netflow data is cool to have)

If I could have everything I wanted: everything from vmstat and iostat extended data

Pg stats:
number of connections
idle transactions
commits vs rollbacks
checkpoint frequency
database size
table size (plus bloat, if we can find a good query for it)
index size (same)

If I could have everything I wanted:
everything from pg_stat_database, pg_stat_sys_*, pg_statio_*, and pg_stat_user_activity

Activity logs configured as outlined here.

Then there’s a whole class of things that fall under “How long does it take to…”: do a backup, restore a backup, etc.

6 June, 2014

autovacuum: long naps aren’t better

by gorthx

There’s that saying about “the first time’s an accident, the second’s a coincidence, the third is a pattern”. It’s probably because I’ve been studying Postgres’s autovacuum feature so much lately and these things stand out to me now, but I’ve noticed a really intriguing pattern (n>5) over the past month or so: folks with their autovacuum_naptime set too dang high.

The autovacuum_naptime GUC is the amount of time the daemon waits between checking for tables that need vacuuming and analyzing (or have xids that need freezing). If you set this to, for example, one day1, each table in your database gets checked only once a day. It’s certainly possible that this will work ok for your use case. What tends to happen, though, is that you end up with several tables that need maintenance, but the long naptime doesn’t allow that to happen in a timely fashion. And of course, the longer you go between vaccing/analyzing/freezing them, the longer it takes to get that job done and soon you’re in a downward spiral, and maybe your database ends up taking a dirt nap, at least from the viewpoint of your users who are all saying “OMG, why is this so sloooooow?” Then you conclude “autovacuum sucks” and disable it and try to stay on top of it with cron jobs etc like back in the Bad Old Days When We Didn’t Have Nice Things.

So, while the autovac config params are all interdependent to some extent2, this is kind of a big one and IME you don’t want to increase the naptime beyond the already-conservative default of 1 minute.

1 – Yes, really. Not trying to embarrass anyone here! Rest assured you are not alone.
2 – I was going to say that individual GUCs don’t operate in a vacuum, but figured that might be taking things Just Too Far.

25 April, 2014

Mistakes I make with Perl hashes

by gorthx

I’ve been doing a bit of Perl programming the past couple months. I felt pretty rusty at first (it’s been over a year since I’ve written anything serious with it) but am getting back into the swing of things. My main use of Perl is manipulating delimited text data (from databases or flat files) for reports or loading into databases and the like. For these types of tasks, I really prefer hashes (and HoH…oH) to arrays because I can can give my variables appropriately-named keys, such as $switch{‘card’}{‘port’}. It’s a lot more obvious what that’s doing than $switch[12][2]. Obvious is good, especially 6 months later when I’ve come back to a project and am saying “what the hell is this” (as we all do).

I always seem to make the same three mistakes with hashes. The first two feature the fun symptom “I’m getting data that’s different from what I expect, and it’s randomly different every time I run my program”:

1. Oops, I need my data in a certain order. This is the first thing I forget if I haven’t written any Perl code for a while: Perl hands hash data back in whatever order it feels like. When I’m writing my initial tests, I’m using smaller data sets (like maybe one or two hash “rows”) and I get lucky and my data’s in the sort order I want. Then I get some real data, and … “oh right!” I fix this by using an AoH instead, or storing (some of) the keys in a separate array (feels kludgy), and I suppose some day I will get around to trying one of the permutations of Tie::Hash.

2. I fail to provide a unique key for the hash. This is another one that doesn’t become apparent until I’m working with real data: a “random” small data sample has two unique identifiers, but when get a bigger data set I find out there’s actually five, and so on, and pretty soon I’ve got a HoH…oH that’s 17 levels deep. (I kid.) (Maybe.)

3. The bane of my existence: typos in my hash keys. ‘use strict’ doesn’t protect from this. Writing tests helps[1], but they still occasionally slip through[2]. I troubleshoot with rigorous use of Data::Dumper::Simple and the debugger.

1 – Thank you, PDX.pm.
2 – My test for variable names: 3 months (6 months, a year) later, is it still obvious what this variable holds, and would I name it the same thing?

28 February, 2014

SCALE12x Report

by gorthx

Last week I headed to sunny LA for the SoCal Linux Expo, aka SCALE. This was my first time at SCALE & I’ll definitely be going back (it reminded me of my first OSCON several years ago).

I’ve wanted to go to this conference for a few years now & finally had a reason to go: I gave a talk at LAPgDay (aka the Postgres track) on Friday. The Pg talks were mostly SRO throughout the day! That’s pretty exciting. I had a lot of fun giving my talk about Pg’s vacuum & autovacuum processes & got some good feedback about it. Which reminds me: if you haven’t already, please fill out the speaker survey; there’s a link on the back of your badge.

On Saturday, I spent most of my time in the Postgres booth (of course) with friends old and new. I’m just about finished following up with folks who had questions for me, so if you haven’t heard from me yet, hang tight. :) I managed to take one quick spin through the Expo Hall, and have to say I am very intrigued with the DIYBio movement, and hope the PDX group gets something going soon. I also picked up yet another copy of The Manga Guide to Databases at the NoStarch booth, and gave it away the day I got home. I can’t seem to keep a copy of that book for myself!

Two Pg tips I picked up this weekend (thanks Steven & Joe):
1. “TABLE my_table;” is a handy alias for “SELECT * FROM my_table;”

2. The ‘AS’ keyword is not required to specify a field name for an alias. Apparently it’s been this way for a while, and I just managed to avoid being bitten by it until last weekend when I misplaced a comma.

21 February, 2014

Ideas for future PDXPUG workshops

by gorthx

The recent Streaming Rep Lab at PDXPUG was such an excellent learning experience. I really want to continue having these sessions.

Our definition of a “workshop” is pretty loose. There’s a basic list of topics we want to cover, but no real agenda or leader; it is truly a group effort.

Here are some ideas I’m toying with for future workshops.

- Choosing a High Availability plan
– Different ways to take backups
– Troubleshooting slow queries
– Monitoring (this could easily be a series on its own)
– Disaster Recovery
– Oh no, somebody deleted pg_xlog
– Transaction wraparound
– Postgres on zfs
– Postgres packet captures
– Tour of contrib modules
– Foreign Data Wrappers
– Benchmarking changes to GUCs, e.g. maintenance_work_mem.

Update: I started a wiki page, so other group members can add their ideas.

Tags: , ,
14 February, 2014

I’m speaking at SCALE next week

by gorthx

I’m giving my Autovacuum talk at SCALE next week. There’s a whole track of Postgres talks on Friday.

I’ll also be hanging out at the Pg booth on Saturday – come by & say hello!

7 February, 2014

PDXPUG labs – Streaming Rep Saturday

by gorthx

Several years back, a new fellow on a ride with my bike club had a rather serious crash. I’ll spare you the gory details here1, other than to say that he was very lucky we had two Wilderness First Responders, a nurse, and an Army medic with us. The experience made quite an impression on me, and I got a Wilderness First Aid certification a few months after that.

The way the NOLS courses work is you have lectures about eg “how to splint a broken arm with found materials”, then you divvy up into pairs or small groups for practice. One person is the “victim” and the other has to fix them. The first time I took the class, I “killed” most of my patients. I did much better the next few times, and have had several occasions to be grateful for these skills.

Over the past year, I’ve been thinking a lot about how training like this applies to our everday work as IT specialists. A lot of us inherit systems that may not be documented, or maybe the backup’s not actually running, etc. And then disaster strikes, and we think, “well, I know *in theory* what I’m supposed to do”. Good luck!

My point is, we need to practice this stuff. Preferably in a low-pressure environment where it’s ok to make mistakes. This is right up there with the concept that “you don’t have a backup unless you’ve successfully restored it recently”. Do many places encourage (or even allow) you to practice restores, though? I know of a few that do, but for the most part people are pretty silent about this and I suspect it’s because nobody’s doing “what’s right”.

This was my reasoning behind starting a lab series with PDXPUG. Streaming Rep seemed like a good candidate for the first lab; several PDXPUG members expressed interest, and I personally was feeling a bit rusty because I haven’t had to actually configure it since 9.1.

My goals were:
– create a checklist of steps to follow to set it up. This is one of those things you probably won’t have to do very often, but why not make it as streamlined as possible?
– figure out if it’s actually working correctly
– practice a fail over, practice restoring a standby, etc

The group recap is here.

The checklist is up on github; the group found a few mistakes in my original.

Additional cool tidbits I learned:
wal_level has to be hot_standby on the master, or the standby won’t start up. (Postgres is pretty good about letting you know exactly what the problem is, though.) I was unable to get around this without completely reimaging the master; I would like to dig into that further and see if there’s a shortcut.

SELECT * FROM pg_stat_replication; shows you the current rep status. Combine this with \watch for extra entertainment. Available on the master, and on standbys if you’re cascading. (Hmm, setting up cascading rep might be another fun lab…)

pg_xlogfile_name translates ID -> WAL name, also only available on the master.

\df *log* …somehow, all these years I have missed that you can pass globs to the \ commands.

You can actually ship and stream WALs, but be prepared for 2X the network load.

CREATE USER applies login privs; CREATE ROLE does not.

Items that we’ll be looking at during Streaming Rep part 22:
– what is the “right” # for max_wal_senders – is it just the number of standbys? Any reason you’d want to have more?
– what causes that “FATAL: database is starting up” problem, and how do you fix it?
– how the heck do we monitor this thing, and what is reason for alarm
– the “human-readable” WAL transform

Yes, there will be more labs. Watch the PDXPUG blog for announcements. If you’re in the Portland area, please come join us.

1 – I will tell them over beer, though.
2 – Thursday Feb 27, sign up here

Tags: , ,
17 January, 2014

Model 66 – treadle

by gorthx

Part 1.
Part 2.


The treadle
Again, disassemble this into its component parts so it’s easier to work on. This was the perfect excuse I needed to replace the Honkin Huge Screwdriver that disappeared with my ex-husband. A lot of these bolts were stuck pretty firmly; PB Blaster wasn’t helping, so I used the old trick of using a crescent wrench to turn the screwdriver. Voila.

My original plan was to scrub the rust and paint off of “the irons” (as they are called) and then spraypaint it black. I spent a couple of hours just on the screws, and when I discovered how much rust there was under the paint that was covering the pedal, well… when my sweetie suggested (again) “You know, you could just take this to the powder coaters that is right around the corner“, I agreed. Best $100 I spent this year.

It’s a simple machine, but again, take lots of photos when you’re taking this thing apart. There is a pretty good text guide here.

Order of reassembly:
– attach belt control to dress guard, to minimize the number of pieces you need to keep track of.
– attach the belt guide to the center brace
– attach center brace to legs. It’s easier to do this if it’s upside down. Put the bottom bolts in first (they’re at the top if you’re doing it upside down), then the top bolts. The side pieces are interchangeable so there are two sets of holes for the top. You want the center brace angled toward the back. (This is where photos of the disassembly come in handy, especially if it’s been 3 weeks since you’ve had it apart & have just gotten the big pieces back from being powder coated.)
– like any other project involving multiple fasteners, get each bolt threaded just a bit before you start tightening them.
– connect the pitman to the drive arm.
– attach drive arm to center brace
– attach dress guard. I don’t have the belt thrower reattached correctly yet; the spring has lost a lot of its spring over the past century.
– attach pedal to center brace & adjust the cone bearings (put those bike maintenance skills to use!)
– connect pitman to pedal

Connecting the pitman to the drive arm was the hardest part; the bearing housing + bearings had to be reassembled in place around the drive arm. This requires very sticky grease and/or an extra set of hands to help chase bearings around the workbench.
bearings in pitman housing

So! This machine is up & running new, and I sewed actual fabric (a quilt block) with it this week. I spent a good amount of time learning to work the treadle without thread in the machine, and then stitching on paper, before I tried to work on a project. It is a lot like learning how to drive :).



10 January, 2014

Model 66 – head

by gorthx

Part 1.


As you can probably guess, I got this baby home and hooked up a belt and discovered it wouldn’t sew. The treadle wouldn’t power the head, indicating it was gummed up somehow. The handwheel wasn’t completely stuck, so I figured “how hard could it be?”

Tools and tips:
The best advice I read, besides the obvious “take pictures from every angle, and take more than you think you could possibly need”, is to keep screws in their taps as much as possible. Some of these babies are tiny, and doing this helped me keep track of them.

This project mat from ifixit was invaluable. It helped me keep track of parts while I was working, and because I took pictures, I have a labeled record of all the parts.

There are a lot of good manuals and instructions online, so I won’t give a blow-by-blow here other than specific trouble spots I encountered. (If you use the manuals from Tools for Self Reliance, please consider making a donation.)

Tension mechanism: this just didn’t look right to me1, and once I found a manual online with images I could actually pick up details from2, I realized it *wasn’t* my imagination, the spring really wasn’t in the right spot. And thus began the extraction of the tension housing: Every few hours I’d drip a little more PB Blaster in the set screw hole, and tap it gently with a mallet and block of wood. Finally, on day 3 of this, I got it to move. A few more cycles of PB Blaster-tapping-waiting, and I smacked it right on out. To get it completely loose, I pushed it almost completely inside the machine by hitting it with the mallet & wood block. Then used the handle of a plastic toothbrush to pop it out from the inside.

Bobbin winder: this was coated with enough grease and dirt that none of the parts would spin and the spring-loaded stop latch wouldn’t even move. This required a complete teardown, cleaning, and relubing. A camera is the most essential tool here. The rubber bobbin wheel is much easier to remove/replace when the bobbin winder is not attached to the machine. It’s so much easier that it’s worth taking off the entire bobbin winder just to replace that little wheel, even though it is a major pain in the kiester to reattach the winder.

Bobbin and hook area: I tried for quite a while to remove the bobbin latch, because I’d read “absolutely do not remove this screw”, then found out that there are two different styles of bobbin latch – one needs to be unscrewed, the other doesn’t. They don’t actually look all that different. Use the manual from Tools for Self Reliance to figure out which one you have; you could break the bobbin latch if you do this wrong.

Underneath: I took this as far apart as I dared; I didn’t want to mess with the timing, because the machine would stitch when cranked by hand. PB Blaster and sewing machine oil were the key here. This was probably the easiest part of the whole refurb. When this was back together, I could give the handwheel a little flick and it spun freely for at a few full rotations.

Next: the treadle!

1 – it helps that I know my way around a sewing machine.
2 – a lot of the manuals available now are scans of photocopies of originals. The TFSR are the best I’ve found, as they are not reproductions.


Get every new post delivered to your Inbox.