Log Buffer #155: A Carnival of the Vanities for DBAs
Jul 24, 2009 / By David Edwards
This is the 155th edition of Log Buffer, the weekly review of database blogs.
On the SQL Server blogs this week, CSS SQL Server Engineers demonstrated that using DateDiff can query performance problems in SQL 2005 and 2008.
The kind of problems, perhaps, that Linchi Shea examines in his post on linked server security configuration and how it can hurt you. Linchi writes, “If you change the security configuration of a linked server from using login UserA to login UserB, would you expect that to significantly impact the performance of a query that uses the linked server . . . Many people probably would answer no. After all, as long as the connection has the permission to access the remote table, how the connection is authenticated should not matter, right? Well, it’s more complicated than that.”
The Rambling DBA, Jonathan Kehayias, looked into the matter of unexpected side effects—problems from implicit conversions. He writes, “ . . . you shouldn’t rely on implicit conversions as a matter of practice in your code. When dealing with data of varying types, it is best to perform explicit conversions to prevent problems such as the one demonstrated above from occurring.”
Several worthwhile HOWTOs and recipes appeared this week. On I want some Moore, Mladen Prajdic illustrated how to check when was SQL Server installed with a T-SQL query.
He also offered a simple base converter, “ . . . that manages (from and to) base 2 to 16.”
Pencils poised? Jonathan Lewis gives his Index Quiz 1: “I’m encroaching on Richard Foote’s territory here – with plans to write a few details about some of the implementation details of Oracle’s B-tree indexes. My strategy, though, is to entertain by asking a few questions that might prompt a little speculation before giving some answers.” Plenty of great comment and submissions, don’t miss it.
Here’s the selfsame Richard Foote, looking at why a segment really has to be at least 2 blocks in size.
Chen Shapira was stalking WHEN-OTHERS-THEN and other troubleshooting monsters. Chen says, “Trying to troubleshoot an issue based on guesses about what was the error is a complete waste of time – what are the chances that you’ll find the real issue and that the next time the night-job runs everything will work?”
Doug Burns made a welcome appearance with a review of a TOP book—Troubleshooting Oracle Performance by Christian Antognini to be precise. “It’s an excellent book and I’ve recommended it to so many people . . . It’s both practical and rigorous. Some of my favourite Oracle books are the most rigorous but although their content is important for understanding Oracle, they don’t always help you do your job better tomorrow. I can virtually guarantee that there will be something in this book that will help you do your job better tomorrow . . . ”
On Monday, Daniel Abadi announced the release of HadoopDB, “A hybrid of DBMS and MapReduce technologies targeting analytical query workloads . . . an open source stack that includes PostgreSQL . . . ”
On his zillblog, Robert Treat notes that the first issue of open source database magazine is out. “For those that aren’t aware, Open Source Database Magazine is a re-incarnation of the old MySQL Magazine. The open source database ecosystem has grown a lot of the last year, with the rising popularity of newish systems like Drizzle and MariaDB, the continued growth of the PostgreSQL community, the revival of old concepts like CouchDB, and the really ground breaking stuff like HadoopDB.”
Tony Bain, in turn, wondered aloud, could MySQL be pigeon holed by Oracle love? “Oracle has yet to make any public statements about their intentions for MySQL. nbsp;. . . I was thinking about possible scenarios that could play out. One of the interesting ones is what happens if Oracle positions MySQL as an entry level database, or as small scale web backend database, and showers it with love and attention, sales & marketing effort in that space. . . . Is it possible that MySQL could start to become known for that limited capability only and recognition elsewhere could start to fade? Would it matter?”
On the Open Query blog, Arjen Lentz observed that TYPE= disappears again: “While perusing the MySQL 5.4.4 changelog, one particular change drew my attention . . . the removal of the TYPE= keyword which was obsoleted since MySQL 4.1 in favour of the ENGINE= syntax in CREATE/ALTER TABLE. . . . While on the surface it may seem ok to remove the obsolete keyword, there are quite a few apps out there that use it, and that cannot be changed.”
On DimitriK’s Weblog appeared his inquiry, MySQL Performance: Why Ahead Flushing for InnoDB?, the outcome of, “ . . . several performance studies to find the most optimal way to fix the “furious flushing” observed during all my benchmarks with InnoDB.”
On the International DB2 Users Group Blogs, Rob Williams shared his script for DB2 Log Archiving/User Exit with Amazon S3.
That’s all for now. Thanks for checking in, see you next time!