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

Jul 31, 2009 / By David Edwards

Tags: , , , , ,

Welcome to the 156th edition of Log Buffer, the weekly review of database blogs.

Oracle

Jonathan Lewis gets things rolling with his post, Empiricism. Jonathan asks his readers if an empirical approach to tuning would be appropriate for a particular wait scenario.

Doug Burns was also looking into wait times, beginning, “Sometimes you think a subject is understood so well, including by yourself, that you tend to overlook it until asked to explain it. That which seems intuitive to us might not be. I suspect Oracle’s event instrumentation timing is one of those subjects.” One reader responded, “When ASH is available, use it.” Replied Doug, “Oh, don’t say that. Now I’m just going to have another bunch of licensing comments!”

Probably. But then there’s the free version of ASH, as referenced by Marcin Przepiorowski on his Oracle Performance and Backup Blog.

On Striving for Optimal Performance, Christian Antognini examines the impact of direct reads on delayed block cleanouts, one of the “most important cons,” in his opinion, of serial full table scans can using direct reads in 11g.

Dion Cho was on another quirk, the stored outline abnormality in Oracle 11g. Bug or enhancement, he wonders, and the readers respond.

In the ninth of his epic “Little Things Doth Crabby Make” series, Kevin Closson opines, Sometimes You Have To Really, Really Want Your Hugepages Support For Oracle Database 11g.

Here’s Jonathan Lewis with his Index Quiz 2 , which is easier, he promises, than Index Quiz 1.

Our Indian readers will be pleased to learn about the first ever visit of Thomas Kyte to India—“a 3 half-day session organized by All India Oracle User Group (AIOUG) in Bangalore”—on Vivek Sharma’s blog.

MySQL

Does MySQL care about Windows users? So asks Peter Laursen of the Webyog blog. “Sun/MySQL executives have at many occasions made it clear that Windows is very important for them.  . . .  This is a very clear signal from management! Windows is an important platform and it is important for MySQL too.  . . .  But do MySQL developers care about the signals from their management? Not much!” Peter describes the symptoms; the readers chime in with their diagnoses.

Here on the Pythian blog, Sheeri Cabral diagnosed prevalent dependence on MySQL Documentation. She writes, “I think many people truly realized how much they take the MySQL documentation for granted during the recent multi-hour outage from mysql.com’s data center. Apparently there is a lot of FUD floating around about the legality of mirroring the documentation . . .  [You] can indeed mirror the documentation, so long as you mirror the binaries as well.”

Shlomi Noach posted a couple excellent items this week—Rotating SQL graphs horizontally, and Generic, auto scaling, scaled SQL graphs. Shlomi writes, “We all love graphs. We all love SQL hacks. We all know the SQL hack which displays a character-based graph (example follows for those unfamiliar).  . . .  But we all love horizontal graphs, not vertical ones. We are used to the X axis being horizontal, Y being vertical. Not vice versa.  . . . I’ll present a SQL hack which rotates a vertical graph to horizontal.”

In his Diamond Notes, Keith Murphy announced the release of Open Source Database Podcast Episode One. “This podcast,” Keith explains, “is about both open source database servers and the people who develop and use them.  . . .  Kicking things off with this inaugural episode is an interview with Brian Aker who is the creator of the Drizzle database.”

Robert Kaye of O’Reilly Radar reports from OSCON on the saga of MySQL. I’ll give you the ending instead of the beginning this time: “Even though I’ve switched to Postgres many moons ago, I’m utterly fascinated by what is currently happening with MySQL.  . . . I think we’ll be talking about this situation for quite some time to come. Oh, and MySQL users: Worry not — you’re going to be the winners in this whole debacle!” Now click through and find out why.

PostgreSQL

Josh Berkus was also at OSCON. He writes, “I recently did a session at Open Source Bridge and a tutorial at OSCON talking about the basics of designing a SQL database, including the value of normalization for data integrity, maintenance, and performance. A few attendees have asked me, ‘how do I test for normalization?‘” Josh and his readers cover the basics of this.

Curt Monash of DBMS2 asks, what are the best choices for scaling Postgres? “I have a client who wants to build a new application with peak update volume of several million transactions per hour.  . . .  The current base-case plan is to use generic open source PostgreSQL, with scale-out achieved via hand sharding, Hibernate, or … ??? Experience and thoughts along those lines would be much appreciated.”

David Wheeler too has a question. Well, to be precise, an SQL WTF: Neither NULL nor NOT NULL. “I had thought that a value, even a composite value, had to be either NULL or NOT NULL, so I thought it was a bug.  . . .  I dutifully asked on the pgsql-hackers list and was informed . . . that this is, in fact, mandated by the SQL standard. WTF?”

Selena Deckelmann announces: OpenSQL Camp comes to Portland!

SQL Server

Things have gotten so bad that Aaron Bertrand has been reduced to begging—please stop telling people they should shrink their log files!. “I realize that there are some cases where, in an emergency, you need to shrink a log file because it took over the drive.  . . .  But too many people pass this advice off casually, and this gives people the impression that it’s okay to change their recovery model from full to simple and then back to full, in order to allow them to shrink their log file(s).”

Joe Webb of WebbTech exposes the effects of DISTINCT in a SQL query, introducing it thus: “Many people (not those mentioned previously) resort to using the DISTINCT keyword in a SELECT statement to remove duplicates returned by a query.  . . .  [Issuing] the query without the DISTINCT keyword yields more rows than expected or needed so the keyword is employed to limit what is returned to the user.  . . .  So what’s the problem? Is using DISTINCT that really bad?” That’s just what I was wondering.

Merrill Aldrich writes, “When many people, including me, began their early T-SQL efforts, the magic two letter GO statement was a bit mysterious. What was that for? Was it just decoration sprinkled through your scripts? What’s the difference when two SQL statements are separated by GO or not?  . . .  Lately, though, I have noticed quite a lot of production code that still misses key elements of flow control in T-SQL scripts, which can cause unexpected results . . .  In this first installment I will talk about ‘old school’ flow control, which works against both new and older versions of SQL Server.” The post is Flow control in T-SQL Scripts.

Interested In Free SQL Training? Attend A SQL Saturday Near You! Thanks for the invite, Kendal Van Dyke, and for this post, which features a schedule of upcoming events and an outline of what people can expect there.

Adam Machanic too brings news of an event: Kalen Delaney Internals and Query Tuning. “[We’re] excited to announce that [Kalen] will be joining us . . . in October to deliver her SQL Server 2005/2008 Internals and Query Tuning seminar. This is, to my knowledge, the most advanced public SQL Server course to be offered in the Boston area in the last 5 years, and this is the first time that Kalen has ever taught in the area.”

Thanks for your attention, everyone. And for your patience. Log Buffer will be back next week, and on-time. See you then!

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>