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

This is the 159^th edition of Log Buffer, the weekly review of database blogs. Welcome.

MySQL

Sun’s Trent Lloyd cautions Watch out for hostname changes when using replication!, for there is a gotcha there.

Justin Swanhart was also in the cautioning business this week, saying Be careful with BETWEEN clauses, because the MySQL optimizer is not smarter than a fifth grader!. The readers say, that’s SQL.

Anyway, it’s probably unwise to underestimate the intelligence of a child. Patrick Galbraith’s post, SQL for youngsters, provides a case in point.

Baron Schwartz shared How to find per-process I/O statistics on Linux. He writes, “Newer Linux kernels have per-process I/O accounting and you can use the iotop tool to find out what’s performing I/O, but in many cases I’m trying to find the source of an I/O problem in an older kernel. I found sort of a hack-ish way to do that today, while trying to figure out why a system was basically unresponsive.”

The [email protected] people declare, Yes, there is a Facebook patch. They introduce it thus: “A Launchpad project, mysqlatfacebook, has been created to publish patches for MySQL from Facebook.  . . .  A few changes have been published and more are to come. The focus for many of our early changes is to add performance counters to InnoDB. We want to make InnoDB faster . . . ”

They and their readers also shared their thoughts on the futures of replication in MySQL.

PostgreSQL

Pavel Stehule has a great article on MySQL functions for PostgreSQL. He writes, “PostgreSQL doesn’t support similar function [to field] (it is nice together with ORDER BY expression clause), but with new variadic parameters is easy to develop it in pg  . . .  I ported mostly used MySQL functions to PostgreSQL.”

I’m sure Pavel already knows of Andrew Dunstan and his Rules for SQL programming, #48: “Don’t mix use of the “,” operator with use of JOIN operators in your FROM clause. It’s a recipe for endless confusion and errors . . . ”

Here on the Pythian Group Blog, Sheeri Cabral shared a lot of video coverage of OpenSQLCamp 2009 presentations. There stuff here for Apache Derby, the Blackray data engine, CouchDB, Firebird, MySQL, and of course, PostgreSQL.

Josh Berkus has PostgreSQL video galore! “At this point, media.postgresql.org has over 40 hours of video to entertain and educate you about all aspects of running, developing on, and hacking PostgreSQL.”

Oracle

Tanel Poder lays out a better way for finding the hot block when facing cache buffers chains latch contention.

Jeff Hunter for his part, encountered and bested SGA Latch Contention.

On the Oracle Scratchpad, Jonathan Lewis has the third part of his Index Explosion series, this time answering the quesiton, “How can you set maxtrans on an index in 10g (and above)?”

SQL Server

The Scary DBA looks into what happens when you use WITH RECOMPILE. He (or she) writes, “I saw this question and my immediate thought was ‘Well, duh, the execution plan is recreated by a recompile.’ But, someone a bit smarter than myself suggested that, in fact, no plan was stored in cache, forcing a new plan to be created each time the query was run.  . . .  So, which is it? Does a plan get added to cache and then removed every time the procedure is called, or do you get nothing in cache and the ‘recompile’ is actually a compile every time? As Mr. Owl says, let’s find out.”

Aaron Bertrand was peeking under the hood to discover what bits have changed in SQL Server 2008 R2.

Adam Machanic has been watching film classics again. How else do you explain Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE? Adam writes, “[SQL Server 2008] was clearly manageability themed rather than programmability themed, and I can count the most interesting and far-reaching developer enhancements on a few fingers . . .  MERGE is a feature that initially made this list for me. [It] provides us with at least two features, independent of its ability to do ‘upsert’, that put it . . . right at the top–as the number one most important developer enhancement in SQL Server 2008.”

Ward Pond agrees. Here’s his Why I like MERGE.

Linchi Shea shared a method for finding top CPU consumers. Quick and dirty, but effective, Linchi says.

That’s all for this week. Thanks for stopping by, and do come back in a week’s time. Till then!