Log Buffer #123: A Carnival of the Vanities for DBAs
Nov 14, 2008 / By David Edwards
This is the 123rd edition of Log Buffer, the weekly review of database blogs. Welcome.
Let’s begin with some SQL Server, where it was a week of technical tips. Alexander Kuznetsov looks at defensive database programming. “In most cases LIKE conditions should by followed by ESCAPE clauses,” he asserts, continuing, “You have a choice: you can either have a CHECK constraint disallow special characters, or you can fix the procedure,” thus error-proofing your logic. Readers Alejandro Mesa and Adam Machanic suggest a couple refinements.
On In Recovery, Paul S. Randal shows how to move constraint indexes, taking as his cue a question he heard at SQL Connections Fall in Las Vegas: “Can CREATE INDEX … WITH DROP_EXISTING be used to move indexes that enforce constraints?” Short answer: yes. “This is really good, as it allows you to move these indexes without having to do DROP INDEX then CREATE INDEX.”
Piotr Rodak offers his example of the use of a numbers table, which “ . . . [implements] string split functionality for parameters passed as CSV arrays.” He asks for more examples, and Adam pops up there, too.
Speaking of duplicates. Mark, of Ajarn’s SQL Corner, wants us to know why he hates DISTINCT: “You know… that ‘handy’ keyword that eliminates duplicates from your result set. Yeah, that DISTINCT. I Hate it! . . . ‘It’s a sign of weakness!’, I would say. Or, ‘It just shows you don’t know how to write SQL!’” The problem, he suggests, is that it too often obviates asking why you have duplicates in the first place. Good point! Good comments, too.
Matt Reid asks his readers, What do you want in a OpenSource MySQL Monitoring solution? He asks because, as he says, “Iâ€™m rewriting Monolith – MySQL DBA Console from the ground up. This will be version 2 and I would like to get some input from the global MySQL community.” Let him hear you.
Shlomi Noach of code.openark.org published the second part of his series, two storage engines; different plans. This part looks at index search capabilities of MyISAM and InnoDB.
Stewart Smith has issued his technology predictions, which, like the Oscar nominations, we have all been waiting on. This is somewhat off-topic, but Stewart does augur the following: “Somebody will have ported Drizzle back to Microsoft Windowsâ€¦ possibly Microsoft,” “the majority of consumer bought machines (which will be laptops) will have SSD and not rotational media,” and, “ZFS . . . will become nothing more than a Solaris oddity.”
Domas Mituzas has some thoughts of his own on SSDs, rotations and I/O, which begin, “Every time anyone mentions SSDs, I have a feeling of futility and being useless in near future.” Why, Domas? “You can sense the futility of any work done to optimize for rotation – all the ‘make reads fast’ techniques will end up resolved at hardware layer, and the human isnâ€™t needed anymore (nor all these servers with lots of memory and lots of spindles).” A good discussion follows.
Martin ‘MC’ Brown of MCslp Coalface takes on the matter of ZFS Replication for MySQL data. This is a thorough examination of ZFS’s snapshot, used for MySQL.
Replication, backups. We all have to do it. Maciej Dobrzanski of the MySQL Performance Blog expounds on what it means to be living with backups. Backups are taken (regularly, of course!) at a performance cost, he writes, and it helps to know some ways to mitigate that, as he shows. Very useful.
And now it’s time to hear from Oracle. Hector Rivera Madrid is asking, What Happens during a hot backup? He is trying to debunk what he sees as common myths on this matter: “Myth #1: The hot backup generates ‘a lot’ of redo information; Myth #2: The archivelog mode ‘dramatically slows down’ the database; Myth #3: When a hot backup is in progress the target datafile is frozen.”
Here on the Pythian Group Blog, Riyaj Shamsudeen contributed an item on performance tuning with HugePages in Linux, showing again the real advantages of knowing your way around the host OS.
Eddie Awad has 5 Recommendations About Cursor FOR Loops in Oracle PL/SQL, from the keyboard of Steven Feuerstein. “In short, stop using cursor FOR loops and start using BULK COLLECT. Itâ€™s that simple.” It can’t really be that simple, or Eddie wouldn’t have written such a fine article about it.
On the iMERGE Group blog, Bhavik Fuletra introduces a new feature in Oracle 11g, the Automatic Diagnostic Repository (ADR): “Oracle Database 11g had adds two more important axes i.e. Change Management and Fault Management. Here I am going to describe you some points about fault management capabilities . . . ” (As my colleague Mark Brinsmead remarked on reading this, “Boy, Oracle will never cease to grow in complexity. And they tried to get rid of DBAs at some point—that will never happen.”)
A comforting thought to any DBA, and certainly to a novice. With those in mind, Sabdar Syed of the Oracle DBA and Apps DBA blog publishes How to Get/Gain Real Time Experience/Exposure as a DBA.
From Karen Morton: “Call me dense. Call me biased.” Karen, we would never call you those awful things. “Call me performance conscious.” Well, maybe that. “But I just can’t groove on the concept of database independence. You know what I mean?” Judging by the comments, many of us do know what you mean. And Dominic Brooks would say we’re biased, too. “Fat databases” forever!
Finally, Guy Bowerman of Informix Application Development lets us know that Informix Dynamic Server has got a planet. Congratulations, IDS! . . . Ah, the music of the blogoshperes!
That’s all for now. See you in a week’s time.