Log Buffer #126: A Carnival of the Vanities for DBAs

Welcome to the 126^th edition of Log Buffer, the weekly review of database blogs. Just to be different, let’s start with PostgreSQL this time.

The Postgres OnLine Journal offers their PostgreSQL 8.3 PLPGSQL Cheatsheet.

The ever-reliable Hubert Lubaczewski of select * from depesz; has a couple posts this week. He has a howto for recovering from a lost PostgreSQL password. Thanks, Hubert. We’ve all been there. He also introduces explain.depesz.com, a web-based EXPLAIN ANALYZE filter: “[The] basic idea is: paste your explain analyze plan, and see the output. You can click on column headers to let it know which parameter is the most important for you – exclusive node time, inclusive node time, or rowcount mis-estimate.”

Over on the SQL Server hemisphere, The SQL Batman was on the trail of a showplan error. Take-away lessons include this: ” . . . error messages have a habit of not being helpful and at the same time being quite accurate.” Well said, Batman!

Michelle Ufford the SQL Fool produced SQL tweaks and tools that make my life easier.

The Rambling DBA was also in the game of making things easier, having collected their complete series of automating common DBA tasks complete series. Very nice indeed.

Kalen Delaney writes, “Most of you are probably aware that having a clustered index on the column(s) in an ORDER BY clause means that SQL Server can avoid having to sort your data  . . . But what about a nonclustered index?” She lays out he answer to that question in Using a Nonclustered Index to Avoid a Sort.

Kalen also answers this question: when is FULL recovery not really FULL recovery? Must be a trick question. Best read it to make sure it’s not you that gets tricked.

Alexander Kuznetsov has the latest in his defensive database programming series: eliminating IF statements. Alexander demonstrated that the IF EXISTS pattern may fail up to 40% of the time in high concurrency environments.

The much-loved UKOUG Conference & Expo went by this week, so here are a few wrap-ups from the Oracle world. Doug Burns has a thorough look at UKOUG Day 1, including his rendezvous with Pythian’s European side. One of whom, Lukas Vysusil, posted his UKOUG 2008 highlights, with the slides from his presentation on Autoconfig customization & best practices.

Here are Alex Gorbachev’s thoughts on UKOUG Tuesday.

Radio Free Tooting’s Andrew Clarke gave his account of Wednesday at UKOUG2008. “Words of wisdom from the bottle of Rittman-Mead beer I’m drinking as I write this: ‘A consultant is a man sent in after the battle to bayonet the wounded.'” I like that. (And it’s funny how often the subject of beer comes up in these conference postings, isn’t it? It’s almost as though these things are fun.)

Jeremy Schneider of the Ardent Performance Computing blog has his recipe for parsing LISTENER.ORA with awk and sed. Nice stuff with Unix text tools, some of the most useful things around.

In other Schneiders, Laurent Schneider is all about HOT! Heap Organized Tables, that is, and the differences in syntax and plan between them and IOTs.

Charles Schultz of OraJourn issues a complaint regarding upgrading clusterware/ASM from 10g to 11g.

Richard Foote looks at a gotcha in his item on function-based indexes and missing statistics.

It was a chatty week in MySQL. On the heels of the long-awaited GA release of MySQL 5.1, Monty Widenius summed up his feeling on the release thus: Oops, we did it again. In other words, he’s not too happy about it. In his words, “If you are using MySQL 5.1 just as a ‘better’ version of MySQL 5.0 and you don’t plan to use any of the new features in MySQL 5.1 then you are probably fine to try out MySQL 5.1.”

And there was much comment. Mark Callaghan wrote, “Monty endorses MySQL 5.1”, a claim that some of his readers dispute.

Hide-and-SQL disputed Monty’s arguments one-by-one in its review of the quality of 5.1 GA release. Fake Amelia does likewise, viewing the controversy as, “Yet Another Case Of Developers vs Users”.

Jim Winstead Jr., trainedmonkey, deemed Monty an observer of his own legacy, comparing him in that light to the outgoing US president. “Ouch” commented Jeremy Zawodny. Yes, “ouch” indeed!

Morgan Tocker suggested there’s nothing point one about 5.1, and he was one of a few bloggers saying that they would prefer their 5.1 to come by way of the OurDelta fork/branch.

Back to Mark Callaghan for a technical post on Linux, mysql, vmstat, laying out what all those columns of vmstat output actually mean.

Ariejan.net offered a little howto on exporting CSV directly from MySQL, with additional suggestions from the readers.

Peter Zaitsev of the MySQL Performance Blog wondered how do MySQL hosting providers manage, when MySQL (including the 5.1 release) offers so little by way of resource limiting of users.

And that’s all for this week. I invite you to mention your favourite blogs from this week in the comments, and as always, get in touch to host an edition of Log Buffer on your own blog. Robert Treat of zillablog is on-deck for next week, giving me a little break from LB, and LB readers a break from me. See you in two week’s time!