Welcome to the 154th edition of Log Buffer, the weekly review of database blogs. Let’s dive right in, shall we?
On Radio Free Tooting, Andrew Clarke says, “No SQL, so what?” taking as his keynote something Nuno Souto said: ” . . . Google, Facebook, Myspace, Ning etcetc, and what they do as far as IT goes, are absolutely and totally irrelevant to the VAST majority of enterprise business.”
On The Dutch Prutser’s Blog, Harald van Breederode gives a lesson in rolling cursor invalidation. He writes, ” . . . I call DBMS_STATS to create a histogram and I expected that dependent cursors would be marked INVALID afterwards but this simply didn’t happen. . . . Somehow I forgot, or maybe completely missed, the fact that cursors are invalidated in a rolling fashion since the introduction of Oracle10g.”
Miladin Modrakovic looks into another 10g-ish thing—Wide Table Select (Row Shipping): “Row shipping is feature which allows row data from the datablock to be shipped directly to the client. . . . Aperently [sic], this feature had some issues in earlier version of 10g and fix was to disable the ‘row shipping’ feature by default.Oracle introduced ‘fix’ in version 10.2 . . . ”
Who should tune SQL: the DBA or the developer? So asks Iggy Fernandez on his blog, So Many Oracle Manuals, So Little Time, as he tries to reconcile two mutually exclusive assertions: “Generally, only the author of the SQL has all of the knowledge required to tune the SQL,” and ” . . . you do not need to understand other people’s SQL to tune it!”
Whomever should do the tuning, they will appreciate Brent Ozar’s SQL Server Index Tuning Tip: Identify Overlaps. Brent says, “These tips and tricks pay off more than pouring money into hardware that might look good sitting in the datacenter, but doesn’t really make the application significantly faster.”
On Claypole’s World, James Rowland-Jones was calculating the ROI of DRY SQL vs FLY SQL. “Pages of SQL obscured by layer upon layer of view definitions is horrendous to have to unpick when there is an issue. However, investing time in making SQL DRY may not give you any real performance benefit. . . . Just remember that it could and it could also make things worse. . . . However, a little look at making your SQL FLY with a spot of index tuning might be just the ticket!”
John Paul Cook wants to know: would you like to be able to do minimally logged deletes? “This is being advocated,” he writes, “for testing. . . . What is being sought is to be able to quickly delete large amounts of data without bloating the transaction log.”
Kevin Kline and his readers kick around loop optimization, when Kevin asks: Why Do I Keep Seeing This Mistake? “I’m still surprised that otherwise experienced and competent database programmers are still embedding very stable elements of their code inside of extensive looping operations rather than outside of them. Thoughts?”
Andy Leonard has been thinking about the profession of the DBA, in light of non-relational DBMSs, and concludes that it’s a question of Art vs. Science. “I consider the database profession a craft. That makes it part art and part science,” writes Andy.
In his item on helping The US Department Of Justice, Monty says, “I was yesterday, for the second time, on a call with the [DOJ] regarding how the Oracle/Sun deal could affect Open Source software, in particular MySQL and Java. . . . For those that are worried about the future of OSS software as part of the . . . deal, and the affect [sic] it may have on their business, the [DOJ] is encouraging companies that are dependent on MySQL/Java to contact them . . . ”
On the MySQL Performance Blog, Morgan Tocker offers three key things to know about moving MySQL into the cloud. “The question “what problems will I have when migrating to the cloud” gets asked often enough. If by cloud you mean Amazon EC2, then from a technical perspective there isn’t much that changes. . . . Having said that, there’s still a few potential gotchas . . . If you can live with these three things, then hopefully your migration should work smoothly.”
While we’re on new ways of doing MySQL, Brian “Krow” Aker and his readers have a very worthwhile discussion of Drizzle, views and triggers. “In Drizzle right now we do not have views,” writes Brian. “There are plans to add views which never ‘materialize’, but that is still a couple of milestones off. . . . One of the problems when talking about views is that the word “materialize” has been over used. . . . To ‘materialize’ a view, means that you take the view definition, turn it into a temporary table, and then join it against a query. In Drizzle we consider this a ‘no no’.”
Here’s a related item from Justin Swanhart: how to support COUNT(DISTINCT expression) expressions with Flexviews. Justin writes, “I am seriously considering porting Flexviews directly into Drizzle. I’m excited about replication plugins as this may make it easy to produce the necessary table change logs to support the materialization logic. Drizzle is becoming completely plugin oriented, so eventually materialized view rewrite and other cool features could be implemented too as optimizer plugins.”
Ronald Bradford posts a lazyweb item understanding InnoDB MVCC. “I wanted to clearly document this situation so I could then seek the advice of the guru’s in InnoDB Internals such as Mark Callaghan, Percona and the Innodb development team for example. I’m happy to say I’m not a MySQL expert in every aspect of MySQL, specifically internals where I have not had the detailed time to read the code, and understanding all internal workings.”
The Postgres OnLine Journal has got another item on the new PostgreSQL 8.4: Faster array building with array_agg, “This takes a set of elements similar to what COUNT, SUM etc do and builds an array out of them. This approach is faster than the old used array_append, array_accum since it does not rebuild the array on each iteration.”
Greg Sabino Mullane was on the 8.4 beat, too, with his piece on Bucardo and truncate triggers. Greg writes, “One of the features that hasn’t gotten a lot of press, but which I’m excited about, is truncate triggers. This fixes a critical hole in trigger-based PostgreSQL replication systems, and support for these new triggers is now working in the Bucardo replication program.”
Peter Eisentraut published solid-state drive benchmarks with latency measurements, following up and earlier item on SSD benchmarks and the write cache.
Conor O’Mahony brings news of interesting developments at IDUG: “If you visit the new IDUG Web site, you will see a nice new look-and-feel. However, you will also notice that there is a lot of new technical content available.”
Will Favero reminds us that Information on Demand (IOD) 2009 is on its way. “[It] is going to be held in Las Vegas again this year on October 25-29, 2009 at the Mandalay Bay Hotel. I think you will find that this year’s IOD conference in Las Vegas will be bigger and better than any of the previous IOD conferences. . . . It was recently also announce that the keynote speaker at this year’s conference will be Malcolm Gladwell . . . ”
Finally, from Henrik Loeser, here’s some fun with databases, which I hope will inspire you to practice your craft with care, knowing that it does indeed make a difference, even if it’s only the difference between cereal and silverware.
Till next time!
Interested in working with David? Schedule a tech call.