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

May 25, 2007 / By David Edwards

Tags:

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

PGCon 2007 has been happening just a few blocks from where I sit, and judging by what the bloggers have to say, organizer Dan Langille has done a great job putting together a very full conference. Let’s start with some blogs about that.

On Database Soup, Josh Berkus gets going with a look at PGCon 2007 Day One: “The problem with attending an all-PostgreSQL conference is that I want to attend everything. At a place like OSCON or FISL, I am really only interested in one talk in a given timeslot… On the first day of PGCon, I was faced with a terrible choice between 3 fascinating talks, every hour. … Then, the EnterpriseDB party… It was a very warm night here in Ottawa, so the party went from the restaurant to some of the outdoor bars on York…” I quote that last part just to show that it does, in fact, get warm here.

Log Buffer alumnus Robert Treat covers Day Two on his zillablog, and does Magnus Hagander. Robert has already posted some slides from the conference; Neil Conway has, too.

Peter Eisentraut’s Day Three highlights include Bruce Momjian’s keynote, Great Steps in PostgreSQL History: “A reconstruction of the first moonlanding using PostgreSQL. Never mind that. You had to have been there.”

Continuing with PG (if only Postgres bloggers had this much to say every week!), a post from the Revolution Systems Blog on PostgreSQL error messages confusing new users. To offset that problem, Frank Wiles has created a gazetteer page of common error messages, which he links to from the post.

As I begin to suspect that what I cover is not databases, but conferences, let’s cross the aisle to MySQL-land, where Sheeri, the MySQL She-BA, has kindly condensed a lot of 2007 MySQL Conference slides, video and audio in one place. She also has video of a recent talk for the Boston MySQL User Group on Rippletechs Informant product: “not only interesting because it’s currently the only software that audits MySQL, but it’s impressive in its simplicity and flexibility. I think my favorite surprise about Informant was that it has the ability to store a user session as just that.”

Johann Andersson has an item on MySQL disk subsystem configuration. “I get the opportunity see a lot of different MySQL Cluster configurations and most of them does not configure the disk subsystem in a good way. E.g, the redo log is way to often not correctly dimensioned. Wrongly dimensioned buffers affects system stability!!” He discusses the factors and provides a template to help us set things to rights.

On the MySQL Performance Blog, Peter Zaitsev, another guy with a lot of insight, shows us a little about the ins and outs of dealing with corruption of InnoDB tables. Peter now has a follow-up to this.

On O’Reilly Databases, Roland Bouman published a thorough article debunking GROUP BY myths. “There is a popular myth about the SQL GROUP BY clause. The myth holds that ‘standard SQL’ requires columns referenced in the SELECT list of a query to also appear in the GROUP BY clause, unless these columns appear exclusively in an aggregated expression. MySQL is often accused of violating this standard.”

Many blogs blogs this week dealt with review and straight-forward no-nos, the kind we may need reminding of from time to time. On An Expert’s Guide to Oracle Technology, Lewis Cunningham says, “I believe implicit conversion to be bad. Badder. Evil. A serious no-no! … I don’t believe that this argument applies only to Oracle. Using explicit conversions is a best practice for any database and any programming language.”

Vivek Sharma of Database / SQL Experiences writes about the effect of non-standard block sizes on multiblock reads. “Non-Standard Block Sizes were introduced in Oracle 9i that allows database to have tablespaces with different block sizes. … This feature should not be used as a performance feature and tempt the dba’s to create larger block size tablespaces so that it reads more number of rows in one I/O. There are many side effects to this and can cause issue in some or the other ways.” Vivek demonstrates this in a scenario.

Andrew Fraser admonishes, listener passwords: always for 9i, never for 10g, calling the default arrangement in 9i, “a glaring security hole”.

In any case, Jon Emmons of Life After Coffee sounds the alarm that Oracle database 9i will be desupported as of June 2007. That’s just a few days away, in case you haven’t been following. As he says, “If you don’t have a plan to get onto 10g (preferably 10gR2) it’s time to start getting one together.”

Apropos of which, the Oracle Security Blog links to an interview on SearchOracle.com with Aaron Newman that “…blasts Oracle’s patching policies”.

Laurent Scheider produced an all-code post on dbms_xplan and v$sql_plan, with some snazzy Oracle-fu in the comments from Yas of the Oracle Today blog, who has a post of his own on modifying sprepins.sql for custom statspack reports.

Some posts from the SQL Server ‘sphere now. Sticking with the theme of review, Mladen Prajdic writes about JOIN vs IN vs EXISTS – the logical difference: “There is a common misconception that IN behaves equaliy to EXISTS or JOIN in terms of returned results.” He reviews each statement type to show just what the differences are.

Systems Engineering and RDBMs examines the distinction between except and intersect operators in SQL Server 2005.

SQL Server Users and Tools reviews SQL Server 2005 & 2000 index optimization best practices.

To close, a blog entry from an Oracle expert, featuring photos of adorable, fluffy plush animals. … You’re thinking it’s that Doug Burns again, aren’t you? You are so wrong!

Please come back next week, when Ronald Bradford does his second LB, #47, on Technical Notes and Articles of Interest. ‘Til 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>