Log Buffer #106: A Carnival of the Vanities for DBAs
Jul 18, 2008 / By Don Seiler
Greetings from Wisconsin! Welcome to the 106th edition of the Log Buffer. Mr. Edwards is on a brief holiday and kindly asked me to fill in for him. So join me as we take a tour of some of this week’s database blogging activity.
I’d like to start by sharing the story of MySQL engineer Andrii Nikitin’s young son, Ivan. The short story is that Ivan is in need of a bone marrow transplant and that would also require travelling outside of their native Ukraine for the procedure. The family is asking for donations to cover the cost of the operation and trip, so please consider donating via the previous link.
Now, moving on to the database topics, we begin with my own area of (relative) expertise, Oracle. The big news this week is the July 2008 CPU, or Critical Patch Update, which Oracle releases quarterly. The Integrity blog has a nice summary of what’s included in this latest patchset. I’m sure plenty of you will be heading to Oracle OpenWorld 2008 as well, and OTN’s Justin Kestelyn announced a community preview page with some sneak peeks of what to expect (spoiler: beer!).
In a quick round-up of Oracle 11g topics, Amit Bansal at AskDBA.org has a nice guide for installing Oracle 11g on Solaris 10. Meanwhile, Oracle performance guru (and fellow Wisconsin native) Greg Rahn makes a great case for using incremental global statistics on partitioned tables. I see that Tim Hall has taken a break from reviewing summer blockbusters to write up a nice article on AWR baseline enhancements in Oracle 11gR1, and Alex Nuijten at AMIS has a nice presentation on using Oracle 11g’s virtual columns to clean up your data model.
It’s always a pleasure to read Lewis Cunningham comparing database transaction models and giving a glimpse into his trips in the 1960s. Jeff Kemp serves up a nice reminder of the ESCAPE clause for wildcard searching with the LIKE operator, for when you actually need to search for “_” or “%”. Pete Finnigan shares an improved version of woraauthbf, an Oracle password cracking tool that he likes to use for security audits. Next, Jonathan Lewis gives a hard-hitting expose on sorted hash clusters, and it doesn’t look good for them.
Finally from the Oracle side, I’d like to draw everyone’s attention to Richard Foote’s 5-part series on deleting index entries. Richard knows Oracle indexes inside and out and this recent series is really an interesting one that I felt should be highlighted and wasn’t covered in this space before.
Let’s shift over to an area with which I’d like to become more familiar, MySQL. I must admit that I’ve been greatly underestimating MySQL in the years that I’ve been focusing in the Oracle world. To start things off, MySQL (and now Sun) employee Peter Gulutzan gave the world a sneak peak of some of the changes coming the ANSI SQL:2008 standard. Obviously this affects much more than just MySQL, and is a tasty glimpse of the future. Jonathan over at MySQL Barbecue (aka “When Pet Projects Bite Back”) explores the idea of choosing a database specialization with the job market in mind, addressing the stereotype that big business only wants fancypants Oracle or SQL Server DBAs. Mark Callaghan, author of High AVailability MySQL, wrote an interesting piece on how to diagnose when InnoDB lags behind on I/O. He’s also apparently not a big fan of XA in his MySQL.
Devananda had two posts that caught my eye this week. One was a story of a workaround to get MySQL 4.1 to work with a MySQL 5.1 slave, since 4.1 can’t directly talk to 5.1 (due to a known bug). The other post was a nice write-up about the performance of JOINs on columns of different numeric datatypes. Sticking with the numbers game, Roland Bouman had a neat demonstration of the DIV operator to do integer-based division, including some nice benchmarks. Jay Janssen also has some numbers for you, putting the rumors about InnoDB performance on over 4 cores to the test.
The next stop on our tour is the SQL Server blogosphere. First up, Jason Massie from StatisticsIO offered up his less-then-favorable assessment of the SQL Profiler tool, and even made a little comic for us. Louis Davidson offers his opinion of the [over]use of triggers. Fellow SQLblog SQL blogger Andy Leonard posted a screen-by-screen journal on installing SQL Server 2008 RC0. One thing I certainly don’t like about Oracle is the clunkiness of OUI (give me a nice curses-based UI anyday), and it looks like Microsoft has that beat here. Yet another SQLblog blogger is Aaron Bertrand (yes, THE Aaron Bertrand) and he’s back with a glowing review of SQLSentry’s Performance Advisor tool.
While I’m sure he isn’t the only grumpy old DBA, THE Grumpy Old DBA wants you to know that SQL Server’s filtered indexes are really cool. Check that. They’re “really cool!!!!!!!!!!!!!!!” Once that excitement dies down, feel free to head over to the MSDN blogs where you’ll find a keen article on the pitfalls of UPDATEs with OUTPUT when triggers are involved.
How are we doing on time? Good? Good. Then let’s take a look at Mladen Prajdis write-up of a SQL Server 2005 bug affecting LIKE searches. Mladen does say that, “it only manifests itself if your search ends in number 9 followed by a wildcard when selecting only columns covered by a nonclustered index,” so let’s none of us do that. Now, who else but Craig Freedman can end a Log Buffer, this time with a snazzy analysis of partitioned tables in SQL Server 2008.
That’s going to wrap-up this edition of the Log Buffer. I hope it’s been as much a joy for you to read as it has been for me to write. If not, well … we’ll just have to worry about that later. Thanks again, and have a good weekend!