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

Welcome to the 165^th edition of Log Buffer, the weekly review of database blogs.

Since they haven’t had any Log Buffer love for a couple weeks, let’s start this one with . . .

PostgreSQL

Selena Marie Deckelmann was tending the garden and found a Snow Leopard amongst the Macintoshes. The result, her post Snow Leopard and PostgreSQL: installation help links.

Josh Berkus posts a poll on encrypted backup. he writes, ” . . . I realized it would be relatively simple to add a simple encryption option to pg_dump and pg_restore.  . . .  So that’s what I’m asking here: would an -e option actually be useful and make you more likely to encrypt your backups? If not, is there some other encrypted backup feature which would?”

The other Postgres Josh, Joshua Drake, reminds us, Everybody loves parties! (Pg Conference West in Seattle next week).

Tom Copeland, junior developer, shares his notes on upgrading PostgreSQL with pg_migrator.

Magnus Hagander loves 80 pages about PostgreSQL! “I was just told that the latest edition of GNU/Linux Magazine in France is dedicated to PostgreSQL.  . . .  A commenter says, “I wish PostgreSQL would get even a small amount of coverage in magazines, but it’s all MySQL or SQLite.”

A perfect segue to that attention-hog . . .

MySQL

“There is no centralized authentication for mysql,” says the MMM Community Blog says. “You have to set up your user accounts individually for each of your mysql servers.  . . . So, here is a little workaround that I did and I can’t find the same anywhere.”

Pythian’s Sheeri Cabral relates a little hack she found to simulate a dynamic general and slow query log before MySQL 5.1. She writes, “If you are in a similar position — where you frequently need to turn the general log or slow query log on temporarily for debugging purposes — this tip just might help.”

Everything MySQL has some tips too, in Pager – but not on call! “Over the past few years I have found that “pager” inside of MySQL is a really useful tool. I have come up with a few simple, but extremely effective, ways to use it.”

The RethinkDB Blog posts Rebench: cutting through the myths of I/O performance. ” . . . none of [the existing tools designed to test I/O performance] gave us the high precision control and number of options we needed. So we wrote our own . . .  Rebench is designed to perform precision drilldown tests for different I/O workloads, and combine workloads in order to give an idea of how a system will behave in complex situations.”

Jayant Kumar posts writes, “Innodb monitors show information about innodb internal status – which could be used for performance tuning. Lets break down the output of show engine innodb status and get a look at what is happening and how it can be improved. Just fire the “show engine innodb status” command and check the Output.”

Ronald Bradford has a survey going. He asks: “What alert monitoring do you use?”

He also surveys NoSQL options.

SQL Server

Davide Mauri shares his findings in response to the question, do NoSQL people really want to drop the relational model? He writes, ” . . . I can formulate the question in a more direct form: ‘What you don’t like of RDBMS, that brings you to ask such huge change?'”

I want some Moore started an extensive conversation with a post on Why I prefer surrogate keys instead of natural keys in database design. “Simply put: I prefer using surrogate keys because natural keys are by default a subject to change which is a bad behavior for a row identifier.” But not so simple that there isn’t a lot more to say about it.

Home of the Scary DBA posts Ad Hoc Queries Don’t Reuse Execution Plans: Myth or Fact. “Another frequently heard story is that stored procedures get and reuse execution plans, but ad hoc queries do not. A lot of people believe this and say as much online and in the real world. Unlike my last myth, this time, I’m going to give you the DBA answer to this question. It depends.”

On Arcane Code, Robert C. Cain shares his HOWTO on using SSIS package configuration values as parameters to SQL tasks. He begins, “Last weekend I presented at SQL Saturday in Redmond WA. One attendee asked if it was possible to use values from the Package configuration file as parameters to a SQL Task. The answer was a definite yes, although I didn’t have a good example. This post aims to fix that.”

Aaron Bertrand began a series of posts which I’m sure we could all add to: Bad habits to kick : ORDER BY ordinal.

Oracle

So Many Oracle Manuals, So Little Time has an excellent interview with Steven Feuerstein, Pl/SQL expert and purveyor of feuerthoughts.

Martin Widlake has some ideas about what he calls an oddity: describing tables you can’t DESC.

The ocpdba oracle weblog looks into advanced corruption resolution with RMAN. Luis Moreno Campos begins, “When Oracle Database 11g came with automatic recovering procedures, I was somewhat suspicious, but since all the exercises in the Oracle University labs went well in my classes, my perception of these procedures was somewhat neutral.  . . .  The case I present to you today will show that these procedures are not so automatic, and above all, not so flexible that a DBA can use them in any panic situation.”

René Nyffenegger shares his work on XML queries: “I’ve been playing with Oracle’s implementation for XQueries recently. Here are a few queries to demonstrate some features of XQueries. By no means are they complete, but they’re meant to give a quick overview on some possibilities.”

Thanks for looking in. As always, you’re invited to share your favourite DB blogs from this week in the comments. Till next time!