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

Hello and welcome to my Log Buffer, the weekly review of database blogs. My name is Keith Murphy and I am the editor of MySQL Magazine, a free digital magazine with information for MySQL administrators and developers, released quarterly.  I am also a MySQL database administrator at Pythian.

I am losing count of how many of these Log Buffers I have done. If I recall correctly, this is number three. But then again, it could be number four. You know what they say though—the more the merrier!

This week’s Buffer features a great allegory about a database, some tips, feature requests, and breaking news. It seems that this week, people shared information that was relevant across database servers, so I recommend that no matter the database server you work on, you should read through each section today.

MySQL

Since the writer of the Buffer gets to choose the order of the stories, I will begin with my favorite db—MySQL. This week the MySQL world saw  some benchmarking of testing of recent improvements to the XtraDB storage engine. the discussion generated around what was done to improve the performance on a 16-core box was really interesting. I’m looking forward to working with the XtraDB engine.

And while only somewhat MySQL-specific, for those who have an interest in database warehousing, comes this interesting post from Alex Tomic on agile development with data warehousing. Bet you never though you would hear those two phrases together!

Gregory Haas talks about his methods for source control for his database servers. It’s something that many DBAs don’t consider. I won’t embarrass everyone  by asking for a show of hands of who has their schema and tools under source control.

Now that MySQL server 5.1 has been GA for a while, it is starting to roll out into production at some sites. Karlsson writes about his use of events.  I will leave it to the gentle reader to determine the very important benefit he points out about events. Thanks for the tip Karlsson!

For those still using 5.0 ( . . . um . . . the approximate 99.9% of you), Yves Trudeau Matt Yonkovit shows how with a little manual hackery you can gather the queries that are hitting your server”at will”. While not as elegant as 5.1’s dynamic logging, it certainly does the job. Thanks Matt!

Baron has a good idea: a reverse changelog.  If you have ever worked your way through a changelog in preparation for an upgrade, this will make sense to you.

My colleague Sheeri wrote a great blog post on MySQL’s default silent truncation behavior and how this can affect your database server’s data integrity.

Mark Callaghan, Google wizard of the dolphin, writes about global mutexes and how they are limiting the SMP performance  of MySQL server. There has been much discussion about this over the last year and it seems some progress is being made. Brian Aker writes down his thoughts on the matter as well.

Finally, for those going to the MySQL User’s Conference, Giuseppe Maxia officially announced the MySQL Camp, which will run in conjunction with the User’s Conference.

Microsoft SQL Server

You know there are people who think DBAs are boring and stuffy. I think these people should all meet Simon Sabin. This chap from the UK has a twisted sense of humor, and he manages to relate a fish and chip shop to a database server. Take time to read through. It is absolutely hysterical!

On a more serious note. What does it take to build the 60GB index for a billion-and-a-half rows? Well the SQL Fool knows. And she shows you how to execute a well-crafted plan to do this without bring the database to its knees (and keeping your users from being howling mad).

Buck Woody points to a site with an SQL formatter available online. Not only does it format SQL for Microsoft SQL server, it also does Oracle, MySQL, and DB2. Not only that, but it will translate the output to a multitude of programming languages including C#, Java, and PHP to name a few. It looks a really cool tool.

Speaking of tools, Kalen Delaney provides a nice list of free troubleshooting tools available for SQL Server.

Need a random number? George Mastros give some great tips on generating numbers for creating tables for testing.

Are you someone who has to hire a senior DBA? Brent Ozar has a list of 10 questions you can ask in the interview process. Good questions, Brent!

Oracle

I’ll kick this off with some basic material excellently presented. Eddie Awad points to a 9.5 minute video on indexing by Stephane Faroult. Stephane is the the author of The Art of SQL (a book I highly recommend). Arup Nanda has a very good post about how to alter a materialized view of any size in seconds.

Lewis Cunningham’s blog is always a good read. It seems he just got involved with agile development. He gives his perspective on the process.

Tanel Poder gets in-depth with cursors in Oracle. He calls it Multipart cursor subexection and PRECOMPUTE_SUBQUERY hint.

Want to learn about the ALL_TABS_COLUMN table? Don’t know what it is? Join Robert Vollman—AKA the Oracle guru—as he schools a pupil in the how you can use the ALL_TABS _COLUMN to find out information about your tables columns.

Joel Goodman talks about different ways an ASM external storage solution can be integrated with your Oracle RAC or single-instance server.

Chen Shapira, the Simple DBA, offered some good theory and practical application on queues.

PostgreSQL

In the world of PostgreSQL,  Gavin brings us some benchmarking results comparing two GNU/Linux filesystems, ext4 and ext3. If GNU/Linux is running on your servers, you need to take a look at this post.

That is all for this edition. I hope you had as much fun reading it as I had writing it. If I missed you favorite DB blog from this week, please leave a comment with a link. Also, I encourage you to give it a shot as well—drop Dave a line and get Log Buffer going on your own blog.

See you all soon!