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

Welcome to the 161^st edition of Log Buffer, the weekly review of database blogs … and the first one under my penmanship.

MySQL

Johan Andersson explains in a very simple way the scenarios in which you may fall into a split brain situation and how to avoid it in MySQL Cluster on two hosts – options and implications. An article worth reading from one of the MySQL Cluster experts.

I love simple scripts that solve complex problems. I love it even more when the command line can be defined in an alias, and SQL from SQL offers some powerful examples. Ronald Bradford contributes another example in MySQL DML stats per table. It would be fun if someone would collect these and post them somewhere as a script to add at the end of our .profile or .bashrc files.

Sarah Sproehnle wrote two interesting articles this week. The first one explaining in simple terms when and how to use MySQL’s Query Cache in MySQL’s query cache and the other one about how to overcome one of MySQL’s partitioning limitations in Partitioning by dates: the quick how-to.

The [email protected] blog always offers very deep technical articles and these 2 on InnoDB I/O are no exception: When does InnoDB do disk IO? and [email protected]: Too much IO?. I wonder when I will have some time to test the Facebook patch for MySQL.

SQL Server

Database Programming: Did You Know IN Can Do This? … the title says it all, you’ll have to read this short article to find out.

Ben Nevarez offers an insight into SQL Server‘s optimizer in How the Query Optimizer Uses Statistics. Another article worth reading is Spools in Execution Plans. Understanding how the optimizer works in your databases server is the key to write good SQL statements.

I have two pet peeves when it comes to the IT Industry practices: 1. Lack of attention to details; and 2. wasted resources. In Can you be too Thorough? Jason Massie talks about being thorough (a way to have attention to detail), and knowing when to stop in a project (a way to avoid waste of resources). Thomas LaRock offers another twist on the subject with Reproduce, then Repeat.

Oracle

Oracle 11gR2 has been released, so it’s time to start looking into the details. Christian Antognini‘s Deferred Segment Creation and his Script to Download 11gR2 Documentation are useful articles to get going. If you need help upgrading, read Get Upgrading: Steps To Upgrade To 11gR2 by Saurabh Sood.

Analytical functions can be a pain in all databases. The detailed article from Jonathan Lewis — Analytic Agony looks into ways to alleviate that pain.

Since Oracle 10.2 it’s valid to say that buffer cache can be stored inside shared pool. If you are curious about this fact, Tanel Poder delivers the proof in KGH: NO ACCESS allocations in V$SGASTAT – buffer cache within shared pool!. Heed the warning and do not run the examples in a production environment unless you want a posse of users hunting you down your company hallways.

Did I mention already how much I like simple shell tricks? Jared Still offers some Shell Tricks for the Oracle fans.

Last but not least, another article addressing attention to detail on which Chris Foot gives it a funny name: Poka-Yoke. You’ll have to read The Art of Being a Successful DBA – Poka-Yoke and Paranoid DBA Best Practices to know what it means.