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

One week and a whole lot of snow later, it is time for the 173^rd edition of Log Buffer, the weekly review of database blogs. MySQL goes first this week.

MySQL

On the MySQL Performance Blog, Peter Zaitsev and his readers discuss the question, how many partitions can you have? In Peter’s opinion, ” . . . be careful with number of partitions you use. Creating unused partitions for future use may cost you.”

Also, Peter’s colleague Aleksandr Kuzminsky announces the release of xtrabackup-1.0, an “open source online (non-blockable) backup solution for InnoDB and XtraDB engines.”

On SELECT mysqlgenie FROM lamp; Nicklas Westerlund published the first part of a series on IO benchmarking for MySQL, showing the results of his tests with sysbench.

Nick will also be tackling next’s week’s Log Buffer.

Harrison Fisk’s MySQL Thoughts this week included this item about an Ext4 with MySQL binary logs oddity. ” . . . we were able to pin it down to ext4 and how it delays data writes for a very long time (30 minutes).”

As long as we’re in /var, here’s Eric Bergen, attempting to unwind the tangled web of pid file creation, which he started doing having discovered a pid file creation race condition in mysqld_safe.

Here on the Pythian Blog, Sheeri K. Cabral reports that active support for MySQL 5.0 ends soon.

Lenz Grimmer announces the call for papers for the “MySQL and Friends” developer room at FOSDEM 2010.

PostgreSQL

Dave Page, PostgreSQL hacker and core team member, brings word that PostgreSQL at FOSDEM 2010 – the call for talks has been issued. Dave also has a post clarifying the PostgreSQL release support policy.

Dan Langille, coordinator of the PGCon conference, shares bsdtalk’s interview with himself on BSDCan/PGCon 2010.

SQL Server

Linchi Shea exposes the performance impact of UNION ALL views, ANSI_PADDING, and bad query plans. Writes Linchi, “Whether or not you specify it explicitly, ANSI_PADDING setting is there when you create a table, and can have an impact on the performance of some queries. If you are not careful, it can even hurt performance big time!” Examples and script follow.

In a post is called, For want of a nail, Michael Zilberstein writes, “I’ll describe here how small bug can ‘kill’ [a] strong server. The story begins when for some unknown reason [the] ‘rows’ column in [the] sys.partitions DMV begins to show 4.6 billion rows . . . ”

How many rows? asks Kalen Delaney, inquiring into how best to answer that question.

Here’s Adam Machanic with the release of Who is Active? v9.57, a “fast, comprehensive DMV collection” to answer this question: What’s Really Happening on Your Server?

Adam also provides a roundup of T-SQL Tuesday #001 (Date/Time Tricks).

In a similar vein, Dave Ballantyne gives a lesson in age calculation with SQL Server, sharing what he says is, ” . . . by far the simplest and accurate method that I know.”

Oracle

Let’s start the Oracle news with some reviews of the UKOUG Conference, which closed last weekend. Here are adhd ocd dba’s notes from UKOUG, and Marco Gralike’s UKOUG 2009 wrap-up: “The wifi was a minor bummer, but the rest was up on high standard as always. Good quality presentations as should be expected.”

Martin Widlake introduces, ” . . . a tuning method with partition exclusion where you infer a relationship between the value you want to limit a query on and the partition key. It takes a while to explain the theory so I am going to give it a name, in the vain hope it catches on . . .  I’m going to call it Correlated Partition Exclusion.”

From Igor’s Oracle Lab, Gary Myers brings the CREATE ANY TRIGGER three card trick, a security-related post.

The Oracle Performance and Backup Blog offers the first part of a series on Oracle RAC One Node, which is, “about a installation and configuration of Oracle RAC 11gR2 in One Node configuration on VMWare.”

Retuning to the My Oracle Support outcry, Hans Forbrich looks at MOS one week later, giving a break-down of improvements what remains wrong.

John Hallas gives a lesson on using glance to see Oracle process usage on HPUX.

To close things, here is Drupal guy Dries Buytaert on NoSQL and SQL. Dries writes, “Have a look at this video of Brian Aker’s great 10 minute lightning talk about NoSQL. NoSQL is a database movement which promotes non-relational data stores that do not need a fixed schema.  . . . NoSQL is an ‘and’ and not a ‘versus’. Plus, I expect the gap to close as there are a couple of interesting projects under way that bring some of the NoSQL advantages to the SQL world. One of them is Brian Aker’s own Drizzle project . . .  [Have] a look Brian’s NoSQL presentation. It’s funny!”

Also funny—Geert JM Vanderkelen’s Clusterious defined.

That is all. As always, I invite you to comment with your favourite DB blogs from the week gone by. Please return next week for Nick Westerlund’s LB#173. Till then!