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

Feb 19, 2010 / By David Edwards

Tags: , , , , ,

You have found the 179th edition of Log Buffer, the weekly review of database blogs. Welcome. Enjoy your stay. We begin with . . .

SQL Server

Merrill Alrich gets going with a fresh juxtaposition–his thoughts on motorcycles and Access. “Many DBAs,” he writes, “have been called in to rescue people, or teams, or projects who have mission critical Access applications gone horribly wrong. It’s very unpleasant, especially the typical discussion we have to have with the Access afficionado . . . ”

Brent Ozar is in on this discussion too. Here he gives his top 10 reasons why access still doesn’t rock.

Brent’s blog also has an interview with Joe Sack, ” . . . public face for the SQL MCM program.”

Aaron Bertrand has a couple new items in his Bad Habits to Kick series: inconsistent table aliasing and blind SQL Server installs.

The Rambling DBA Jonathan Kehayias, advises, pay attention to maintenance cleanup job configuration, specifically with regard to backup files.

Jeremiah Peschka shows how a simple refactoring of functions in the WHERE clause can turn a performance disaster into success. He says, “Putting functions in the where clause of a SQL Statement can cause performance problems. If you aren’t careful, these problems can add up and bring a powerful production system to its knees.  . . .  [If] we move the function from the table to the variable . . . [this] is going to be orders of magnitude faster . . . ”

John Paul Cook introduces his favorite free tool, SQL Heartbeat. “There are many great scripts available,” writes John, “for monitoring SQL Server. But admit it, don’t you miss animation and color? I’ve been using SQL Heartbeat for about two weeks and I really appreciate the animation and color. These features make the output highly effective and easy to grasp.”

MySQL

Mark Callaghan gets our MySQL stuff started. “What is the future for MyISAM?” He asks. “MySQL has invested a lot in storage engines over the past few years (Falcon, Maria) and it isn’t clear that anything will come from those efforts. A lot of effort has been put into InnoDB and much will come from that. There has not been a significant effort to improve MyISAM (other than hot backup). What could be done with it?” The post is Save MyISAM.

The ThetaJoin Blog features a review of the MySQL Administrator’s Bible. The author writes, “On the front cover of MySQL Administrator’s Bible is a sentence that reads: ‘The book you need to succeed!’ I must say, I do agree.”

Over on the MySQL Performance Blog, Vadim compares READ-COMMITED vs REPETABLE-READ in tpcc-like load. “[The] question, what is better isolation level is poping up again and again. Recently it was discussed in InnoDB : Any real performance improvement when using READ COMMITED isolation level ? and in Repeatable read versus read committed for InnoDB . Serge in his post explains why READ COMMITED is better for TPCC load, so why don’t we take tpcc-mysql bencmark and check on results.”

Giuseppe Maxia, the Data Charmer, reports on the Linux MySQL distros meeting in Brussels. He says, “When I saw Shlomi’s post on why not to use apt-get or yum for MySQL, I thought immediately that his conclusions are quite reasonable. What you get from the Linux distributions is not the same thing that you find in the official MySQL downloads page.  . . .  We at the MySQL team have organized a meeting with the Linux distributions with the intent of finding out which differences and problems we may have with each other, and to solve them by improving communication.”

Speaking of distros, here is Falko’s How To Set Up MySQL Database Replication With SSL Encryption On CentOS 5.4 on HowtoForge.

PostgreSQL

Meanwhile, in the PG ‘sphere, Jeremiah Peschka walks through installing PostgreSQL on Mac OS X, ” . . . a pretty simple process, but one that I thought I would document because I ran into a few gotchas along the way.”

Leo Hsu and Regina Obe introduce the subject of regular expressions in PostgreSQL, on the Postgres OnLine Journal. “Every programmer,” they say, “should embrace and use regular expressions (INCLUDING Database programmers). There are many places where regular expressions can be used to reduce a 20 line piece of code into a 1 liner. Why write 20 lines of code when you can write 1.”

Marc Balmer’s PostgreSQL blog looks into using PostgreSQL to decouple applications, or, OO meets SQL (Part I): “What works at the object level in an object oriented programming language can be applied to distributed PostgreSQL applications as well, allowing for proper decoupling of applications or application parts. The tools needed for this are PostgreSQL’s trigger procedures and the asynchronous notification mechanism. In this first installment of a small series of blog entries, I will talk about the basics needed to implement a distributed decoupled software solution.”

Oracle

The Oracle world is full of sturm und drang this week. Accusations, recriminations, and enigmas–of the technical kind–abound.

Kerry Osborne asserts that autotrace lies too, following up an older post of his on explain plan’s untruths: “Autotrace is another commonly used tool that suffers from the same basic character flaw (i.e. it lies too). The reason is simple. It uses the Explain Plan command under the covers.”

Richard Foote, for his part, looks for answers to this puzzler: how does an execution plan suddenly change when the statistics (and everything else) remains the same?

Charles Hooper, meanwhile, wonders aloud, V$FILESTAT is Wrong?

According to James Morle, The Oracle Wait Interface Is Useless (sometimes), the third in a series. “In this part I will finally get to the point, and talk about some alternative techniques for determining the reasons for poor performance for our example user session.” Pythian’s Mark Brinsmead says this is “hyper-cool.”

On The Blog from the DBA Classroom, Joel Goodman shows how to tell RAC to leave your leaves alone. “One use of sequences about which most DBAs and developers are aware is that of generating primary keys for a table and naturally the key data for the underlying index.  . . .  But the choice of sequence parameters can have an effect on performance when using high volume insert applications in a RAC database environment. The problem is one of Index Leaf Block contention . . . ”

Doug Burns is here with the first part of a series on statistics on partitioned tables, which he introduces thus: “We’ve encountered a few problems at work recently and I decided it would be an idea to put together a series of posts covering the basics of what can become quite an involved topic because it’s not difficult to find yourself going round in circles reading the documentation, Oracle Support Notes, blog posts, forum threads and the rest until you don’t know whether you’re coming or going!”

Martin Widlake has a thoughtful post on the DBA and his or her job, called, Making Things Better Makes Things Worse. He says, “I’m encountering a phenomenon that I have talked about with Dennis Adams a couple of times. It probably has a proper term, but basically it is the odd situation that when you make things better, you get more complaints.”

That’s all for this edition. See you for LB#180!

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>