Welcome to the 152^nd edition of Log Buffer, the weekly review of database blogs.

PostgreSQL

Courtesy the United States PostgreSQL Association, the big news: PostgreSQL 8.4 Released!.

Josh Berkus writes, “Now that PostgreSQL 8.4 is out, I thought I’d write a little about my favorite 8.4 feature. As Mr. Performance Whack-a-Mole, what makes me happy about 8.4 is the ability to whack moles faster … which is why I’m very fond of pg_stat_statements.”

On ad’s corner, Andreas Scherbaum says, “Up to PostgreSQL 8.3 it was only possible to grant (and revoke) permissions on the entire table. If column level permissions were needed, a workaround like a view solved (more or less) the problem . . .  This . . . is uneloquent, error prone and does not scale well.  . . .  PostgreSQL 8.4 solves the problem with a shiny new feature: column level permissions.”

David Fetter looks into WITH (so much drama in the CTE): ” By now, you’ve probably seen that PostgreSQL 8.4 can produce Mandelbrot sets . . . but what are Common Table Expressions really about? Read the rest of this entry »

Welcome to the 151^st edition of Log Buffer, the weekly review of database blogs. We’re going to take a fast tour through the best blogs from the week gone by, beginning this time, with Oracle.

Jonathan Lewis writes, “It occurred to me recently that I might be making casual use of terms that weren’t necessarily very well known to the less experienced user. So I’ve decided to build a glossary of terms – and I’ll try to add to it from time to time whenever I have a few minutes.”

Jonathan might want to add “Method R” to the glossary. Cary Millsap was making it understood, as he shows in Profiling with my Boy: “Today I’m going to raise the stakes, because yesterday I think I explained Method R so that an eleven year-old could understand it.”

Vivek Sharma offers a thorough look at the Cost-Based Optimizer: Inefficient Input yields Inefficient Output. Vivek begins, “Cost Based Optimizer has always been a mystery for most of the Database Administrators and Developers.  . . .  Cost Based Optimizer has improved a lot in previous few versions. Therefore, it can be said that CBO is still undergoing some enhancements. With these enhancements, Oracle Users have accepted the fact that the Application Performance might degrade after version upgrade . . . ”

Randolf Geist had some info to share on the matter of locked table statistics and subsequent create index. “ . . . in 10g and later index statistics are generated along with an index creation  . . .  so a newly created index usually has computed statistics. 10g also introduced the option to lock table statistics. Now if you lock statistics in 10g in later  . . .  and create an index on a locked table the statistics for the index will not be generated along with the CREATE INDEX command.”

Rob van Wijk gave us part three of his series on fast refreshable materialized view errors: aggregate MV’s. “In the third part I’m going to examine all restrictions for aggregate materialized views, as described in the documentation.  . . .  So this will be quite a lengthy and even tedious post, as you can imagine by the list above … but for a good cause.”

Read the rest of this entry »

This is the 149^th edition of Log Buffer, the weekly review of database blogs.

MySQL

Let’s begin with the big-picture stuff. Jeremy Zawodny laid out his view of the state of MySQL in Linux Magazine’s blog, noting the rough transition between versions 5.0 and 5.1, the status of storage engines, and outside contributions.

Like Baron Schwartz’s Maatkit, for example. Baron announced this week that he is writing a book about Maatkit, and also seeking input for a new tool to verify MySQL upgrades.

Percona is surely one of the foremost outside contributors in the MYSQL world. This week on their MySQL Performance Blog, Peter Zaitsev and his readers discuss some ad hoc ways to copy a database, in his item on using netcat to copy MySQL Database.

Darren Cassar, MySQL Preacher, delved into the particulars of replicating from MySQL to *. Read the rest of this entry »

This is the 148^th edition of Log Buffer, the weekly review of database blogs. Welcome.

PostgreSQL

Since PGCon ‘09 has concluded not long ago (and not far away), let’s start with Postgres stuff, much of which has to do with the convention.

Here are Robert Treat’s reflections on PGCon 2009, on his zillablog: “ . . . PGCon always presents the strongest line up of Postgres information available, and this year was certainly no exception.”

Josh Berkus was there, of course, and he sends two detailed reports: pgCon 2009 part 1, and part 2.

tail -f /dev/dim has a review of PGCon 09: “ . . . I found the experience to be a great one . . . ”

Read the rest of this entry »

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

Let’s start this week, with blogs from the SQL Server world, where a number of excellent technical posts appear. Alexander Kuznetsov surprises his readers with this assertion: without ORDER BY, there is no default sort order. “Sounds trivial? Right, but different flavors of this myth still persist.  . . .  Because apparently many visitors agreed with this myth, I decided to post a repro script which demonstrates that this is simply not true.”

In their post on Read/Write deadlock, the rusano.com blog likewise was answering a question, how does a simple SELECT deadlock with an UPDATE? “Surprisingly, they can deadlock even on well tuned systems that does not do spurious table scans. The answer is very simple: when the read and the write use two distinct access paths to reach the same key and they use them in reverse order.” Examples and fixes follow.

Linchi Shea looks for an answer to this mystery—why the tempdb log keeps growing. Linchi says, “Whether it’s a user database or tempdb, when its transaction log is filled up or forced to autogrow, large long-running transactions should not be the only potential culprit for you to track down.”

The SQL Programmability & API Development Team Blog was also concerned by the matter of unexpected growth, in their case seeking to provide an answer to the question, Why did the size of my indexes expand when I rebuilt my indexes?

On a related matter, Paul S. Randal gave an example of a nasty cluster key on In recovery… Paul’s readers offers examples from their own experience of disastrous primary keys and clustered indexes.

Michelle Ufford, the SQL Fool, covers overhead in non-unique clustered indexes, giving a walk-through of scenarios in which the DBA will encounter this.

In the Oracle ’sphere, Read the rest of this entry »

Hello and welcome to the 146^th edition of Log Buffer, the weekly review of database blogs. I have to make this a quick one, but I hope (as always) that the links give you the highlights of this week’s blogs.

Oracle

Let’s start with Jonathan Lewis’s report from IOUG Day 4: ” Not so much a little gem today as a little surprise and a few consequential thoughts. In a presentation on optimising star transformations the presenter pointed out that bitmap indexes are only available in Oracle Enterprise Edition.”

Here’s Doug Burns with the first of a series on Adaptive Thresholds in 10g (Metric Baselines). Doug writes, “[I really didn't want to get into another multi-part blog post, but this has grown longer than I hoped, so I'll split it up ...]”

Doug, you could have started with the second part, as Kevin Closson did: You Buy a NUMA System, Oracle Says Disable NUMA! What Gives? Part II Haven’t read Part I? Don’t worry. “Yes, in this mini-series of posts Part II will precede Part I. I’ll explain…eventually,” writes Kevin.

Richard Foote still has left Doug and Kevin in the dust, having got to Part V of his Indexes And Small Tables series. Read the rest of this entry »

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

MySQL

Since MySQL was surely the belle of the bloggers’ ball this week—why, everyone was talking—let’s begin with it.

Baron Schwartz started something with his post examining why MySQL might not benefit from having a mother ship. Dean Ellis of niflheim responded, arguing that everyone needs the MySQL mothership. And that got Sheeri’s Cabral’s attention—she took the middle path in her post, What If, and her readers had plenty to say.

Justin Swanhart gave news and opinion in one headline: MySQL documentation team announces docs will NOT be GPLed. Boo MySQL. Boooo, adding, “I’m now totally convinced that MySQL does not understand, and will never understand the MySQL community.” In his piece on MySQL docs freedom, Arjen Lentz wrote, “I believe this is a serious concern for the product as a whole, and hope this concern will be addressed by Sun Microsystems very soon - with action.” Read the rest of this entry »

This is the 143^rd edition of Log Buffer, the weekly review of database blogs.

So . . .  Anything happen while I was away?

Okay, so I heard the big news. And just in case you haven’t, here it is from Sheeri Cabral: Oracle Buys Sun. This is a sea-change in the hi-tech world, and the DB part of it will also get rocked, Sun being the home of MySQL. There’s lots of comment in Sheeri’s post, and indeed, all over the database blogging world. I will try here to cover the best of it.

Oracle + Sun + MySQL

First, Monty Says: to be (free) or not to be (free), that is the question. He projects three possible ways Oracle could treat MySQL, and extends a hand to what he expects will be yet more disenchanted MySQL employees.

On The Open Road, Matt Asay asks, Oracle can help Sun, but will it lose MySQL?: “Given the fracturing we’ve already seen with MySQL . . . I suspect that we may be in for several more forks of the MySQL code base.  . . .  So here’s a thought: could Red Hat fork MySQL, hire some key developers, and effectively assume the mantle of MySQL leadership?”

Pythian’s Paul Vallée obligingly gathered links to Curt Monash’s analyses of the Sun/Oracle deal. Those are very worth reading.

In his post MySQL, Sun and Oracle, Kristian Köhntopp bases his predictions on this premise: “MySQL has been instrumental in building a completely new database market. 12 years ago most people were . . . storing data in flat files. SQL knowledge was expert knowledge, and if you knew SQL you were either out of academia or have been on the career and certification-programme of some vendor.”

Mark Schoonover offers his thoughts on Oracle’s Purchase of Sun: “Who could be impacted by Oracle’s purchase? That huge computer company in Redmond. For years, their database team has had access to the operating system source code.  . . .  Oracle not only gains the source code to Solaris, but also their hardware too. No company in my 22 years in IT has had access to everything - hardware, operating system and the database. It’s going to be a wild ride.”

On the Oracle side, Glenn Fawcett takes a pragmatic point of view: “Could you imagine… ‘Dtrace probes for Oracle?’ How cool would that be?”

In his post, So Oracle buys Sun, Andrew Clarke says, “The notion of Oracle as a hardware vendor is an intriguing one. Oracle will be able to offer appliances such as Exadata without the trouble (and loss of potential revenue) incurred by partnering with a hardware vendor. The flip side is that hardware vendors may be less happy to accommodate Oracle on their boxes.”

From PostgreSQL, Peter Eisentraut writes, “Now with MySQL actually owned by Oracle, this makes PostgreSQL the primary alternative.  . . .  I don’t expect that MySQL will be “killed” either.  . . .  Much of the MySQL momentum already lies outside of Sun anyway, in the hands of Percona, Open Query, the Monty Program, Drizzle, and others, so killing MySQL is already impossible for a single company. Which is probably a good situation for the extended open-source database community.”

Now, I’m not sure exactly what Christopher Powers means by this image from his post Whither MySQL?, but it certainly captures the portent of the moment.

Read the rest of this entry »

Welcome to the 142^nd edition of Log Buffer, the weekly review of database blogs.

The SQL Server ’sphere was a busy place this week. On In Recovery… Paul S. Randall posted his latest straw poll, this time looking into your practices around transaction log size management.

Linchi Shea observed, “In a multi-process/multi-thread system, locking is central to maintain data consistency and keep things in order.  . . . [We] need to begin with understanding the locking behavior of the basic building blocks offered by SQL Server . . .  [One] would think that the basic locking behavior of these building blocks  . . .  would be well documented. Unfortunately, that is not the case at all.” Is it too much to ask for, he wonders, to document the locking behavior?

Kevin Kline is looking for good DMV/database admin queries. You can post or link to yours in the comments.

On thinking outside the box, Peter Larsson offered a little introduction to composable DML, a new tool in 2008. “What is Composable DML? Well, with this tool you can have a statement of UPDATE, DELETE and even MERGE as a data source for your query!”

Michelle Ufford, the SQL Fool, wrote, “[While] I was at the grocery store last night, my mind wandered to SQL Server.” (Yes, that happens to me too.) Read the rest of this entry »

I’m sorry to have to tell you, dear Log Buffer readers, but you’ll have to go without your fix of database blogs today. Your humble Log Buffer coordinator was too busy to take it all in this week (and “all” is an awful lot these days, have you noticed?).

Of course I invite you to add your favourite blogs from this week in the comments, and as always, to publish an edition of LB on your own blog.

See you in a week’s time!