Posts tagged ‘you’ll shoot your eye out’

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.

24 February, 2012

Postgres logging… on Windows.

by gorthx

Yep, you read that right. My regular readers (hi, dad!) are probably wondering “Who are you, and what have you done with gabrielle?” so let me explain. We’re currently demoing netbrain where I work; it’s a dynamic network mapping and troubleshooting tool. It runs on Windows, and uses Postgres as its backend database. Naturally, I wanted to have a look-see [1]. And since the general rule is “When in doubt, go with what you know”, I figured I’d set up Pg logging.

The first order of business was to find postgresql.conf and pg_hba.conf. Now, I’ve been out of the Windows world for a while and had to feel my way around, and it was a lot like trying to type while wearing a pair of fur mitts. Everything took me three times as long. By the time I had clicky-clicked my way to Computer/OS (C:)/Program Files (x86)/NetBrain/Postgresql 8.4/database, I clung to the conf files like flotsam in the tide. Then I got nervous about editing them with notepad and decided I needed to install Vim first.

Step 1, as always: Make a backup! I called mine postgresql.conf.distro. Normally I’d set up a git repo, but we have a (time limited!) demo license, and I didn’t want to spend time installing git on Windows [2].

So, logging. I wanted to see who’s connecting, and any “long” queries, so I changed postgresql.conf like so:
log_destination = eventlog
log_min_duration_statement = 1s # for now, will increase once I figure out what's "normal" for this database
log_statement = ddl # same
log_connections = on
log_disconnections = on
log_line_prefix = '<%r %d %u>'

You can log to another location (which I haven’t tried yet), but database shutdown and startup messages are going to go to the Windows eventlog, regardless, so you’ll have to watch two things at once [3].

Now I needed to restart the database [4]. Restarting is pretty straightforward: head over to Server Manager -> Configuration -> Services, find Pg in the list, right-click, restart.

Then I needed to figure out how to watch my log messages without ‘tail’. Hm.
Server Manager -> Diagnostics -> Event Viewer -> Windows Logs -> Application…and I see Pg messages!

I created a custom view so I wouldn’t have to look at the rest of the windows stuff; I just checked the “By source” radio button, and selected PostgreSQL from the drop-down. Voila! It doesn’t automatically update; you have to click the ‘refresh’ button on the lower right. Which is kind of a bother, but it is better than nothing.

This is good enough for now; I have other things to investigate while we still have the demo license. The ultimate plan is to get this logging to something that fouine can read. I got some good tips from the PDXPUGers at our meeting last week & am looking forward to checking them out.



1 – “What’s the worst that could happen?”
2 – Which is one of the first things I’ll do when we deploy to production. git for Windows: http://code.google.com/p/msysgit/downloads/list?can=3&q=official+Git
3 – Tim of PDXPUG fame tells me there are ways around this.
4 – Pg on windows is a “service”, so my choices are start, stop, and restart. There’s no reloading to e.g. re-read your pg_hba.conf.

29 February, 2008

“fra-gi-le”…must be Italian.

by gorthx

Totem, the default video player that comes with Ubuntu, was telling me I didn’t have the right plugins to play videos.

These steps got me partly there (I could see the DVD menu at least):

cd /usr/share/doc/libdvdread3/
./install-css.sh
sudo ./install-css.sh #grrr...
sudo apt-get install totem-xine

But then I got a message about needing libdvdcss when I tried to play the video (even though, according to my package manager, it was already installed). So I downloaded the package from here:
http://packages.medibuntu.org/pool/free/libd/libdvdcss/
and re-installed it. :P

…and I’m now watching “A Christmas Story“. While I sew. All is right with the world.