Log Buffer #177: A Carnival of the Vanities for DBAs
Feb 5, 2010 / By David Edwards
Welcome, everyone, to the 177th edition of Log Buffer, the weekly review of database blogs. It was another week heavy with technical posts, so let’s waste no time, and get it all started with . . .
David Fetter shares his recipe for adding only new rows: “Let’s say you have a table and a data set, and would like to add only those rows in your data set that aren’t already in the table. There are hard ways, but here’s an easy one.”
Simon Riggs, the Database Explorer, offers his thoughts on parallel query in Postgres: “I’m disappointed we’ve not made much progress with parallel operations and partitioning in core Postgres in last few releases. Recent Greenplum results show we have much work to do in improving things.”
Contrariwise (or not), Iggy Fernandez offers five reasons not to attend a NoCOUG conference in 2010. “Most Oracle professionals will benefit a lot from attending a NoCOUG conference in 2010. However, the following categories will not benefit much: . . . Those Oracle professionals who believe that Oracle’s goal in buying Sun is to replace Oracle Database with MySQL. This is probably a very small group . . . ”
Uwe Hesse, the Oracle Instructor and his readers share a discussion on sharing READ ONLY Tablespaces between databases. Uwe says, “ . . . the question was raised, whether it is possible to use the same READ ONLY Tablespace in multiple Databases. At first glance, I thought that this should of course be possible, though the answers where somewhat discouraging. So I have done a quick test to prove it . . . ”
Krishna Manoharan of Performance Engineering and Capacity Planning steps up with an item on understanding CPU time as an Oracle Wait event. Krishna pursues the question, “[What] if the stats from the system show that CPU Utilization (% Util and Run queue) are well within thresholds and show plenty of available capacity, but Oracle continues to report CPU time as a Top 5 wait event?”
Charles Hooper grapples the question, Which Plan is Better? Charles writes, “A recent post appeared in the OTN forums that indirectly asked the question: which execution plan is better? . . . If you are attempting to conclude which plan is faster/better based on the estimates in [a] first plan and an altered plan with a hinted cardinality estimate, you might be setting yourself up for failure.”
“Seems like all I ever write about these days is SQL Profiles,” writes Kerry Osborne. “I do other stuff, honest! It just seems like getting Oracle to do what you want when you can’t touch the code is the closest thing to ‘Magic’ that DBAs get to do.” Here’s Kerry’s post on single-hint SQL profiles, inspired by a discussion with Jonathan Lewis.
And now here is Jonathan with a post on SQL Server. . . . Wait a minute–what the?!
Ahem. And now here is Jonathan Lewis with a post on SQL Server. Yes, Jonathan Lewis, famous Oracle guy. “A few days ago,” Jonathan writes, “I did a presentation on SQL Server. . . . The title was ‘What the Enterprise needs in an RDBMS’ . . . and the presentation was about whether or not you could find everything you needed in SQL Server 2008, where you’d have to look in the manuals, and supplementary questions you’d have to ask.”
Buck Woody also has been, as it were, treading the boards: “I give series of classes and presentations on Data Design. I say ‘data’ design instead of ‘database’ design because we should consider more than just the database. . . . Here are the links I use in that presentation. Although this isn’t a comprehensive list of Data Design topics, I’ll visit this topic from time to time so you may want to bookmark this page in your favorites[.]”
Merrill Aldrich admonishes, don’t get burned by replication of SQL Server files: “ . . . if you try to use file system replication (robocopy, xcopy, repli-whatever) to maintain a DR server from your production SQL Server, you might be in for a nasty surprise.”
Buck Woody relays more nastiness in his post, Transparent Data Encryption and the Latest Data Breach: “Well, It’s happened again. Hundreds of thousands of private records were stolen from a database. This one, however, was different. No one stole any passwords, no one did any social engineering, nothing was captured in-line. No, this one was accomplished by stealing the actual hard drives themselves!”
One way to circumvent this–no hard drives! But wait–before you send them all to the kilns, Aaron Bertrand says, your laptop may be ready for SSDs, but are your SQL Servers? “I am not trying to be Debbie Downer here,” he explains,” … SSDs sound great . . . But right now, if you are looking at expanding or upgrading your I/O under SQL Server, I’d give the vendors some time to shake off these early jitters.”
Here’s Thomas LaRock, DBA Survivor, with a Name That Caption Contest. The deal is: “The person who provides the best caption will win a copy of my book,” DBA Survivor, “and I will figure out a way to incorporate your caption into the main page.”
On Serge’s blog appears a useful item on using UNIX_TIMESTAMP as a partitioning function for MySQL 5.1.43
Falko Timme shares How To Back Up MySQL Databases With mylvmbackup On Debian Lenny on HowtoForge.
Gotcha again. Dathan Vance Pattishall says, “INNODB has some irritating gotchas that makes disk space management hard. In 2002ish INNODB, added innodb_file_per_table to get around allot of these issues, but it does not fix everything.” The post is innodb_file_per_table, shrinking table spaces and the data dictionary.
On EXPLAIN EXTENDED, Quassnoi examines some details of join on overlapping date ranges in answering the question, “Is there any way to optimize the query for overlapping ranges in MySQL if both ranges are dynamic?”
Last, The InfiniDB Team Blog announces that InfiniDB 1.0 is Now Available!
That’s all there’s room and time for. Let’s hear your favourite DB blogs from this week, and we’ll see each other again in the next one. Till then!