Pythian Group

Welcome, everyone, to the 177^th edition of Log Buffer, the weekly review of database blogs. It was another week heavy with technical posts, so let’s waste no time, and get it all started with . . . 

PostgreSQL

David Fetter shares his recipe for adding only new rows: “Let’s say you have a table and a data set, and would like to add only those rows in your data set that aren’t already in the table. There are hard ways, but here’s an easy one.”

Simon Riggs, the Database Explorer, offers his thoughts on parallel query in Postgres: “I’m disappointed we’ve not made much progress with parallel operations and partitioning in core Postgres in last few releases. Recent Greenplum results show we have much work to do in improving things.”

David Christensen shares a PostgreSQL tip: using pg_dump to extract a single function.

Roppert Kalmar shares a screenshot of the new Sun Oracle PostgreSQL. That doesn’t even sound right, does it?

Read the rest of this entry . . .

Last month at the Boston MySQL User Group, I went through the meanings of INNER, LEFT/RIGHT OUTER, CROSS, NATURAL joins, how to do a FULL OUTER JOIN in MySQL, and what STRAIGHT_JOIN means. I also explained how to recognize when you want those types of joins, and best practices for the semantics of writing joins and design patterns. Subqueries were explained in this session, and some examples of how to think differently so that you end up writing JOINs instead of subqueries. The slides (slightly different from the slides in the video — due to error correction) can be found at http://technocation.org/files/doc/2010_01MySQLJoins.pdf.

Here’s the video:
Read the rest of this entry . . .

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

There were heaps of mostly technical posts this week. I think bloggers are tired of kicking around the ins-and-outs of Sun and Oracle, and wanted to talk about what really matters. So let’s start with . . . 

Oracle

Harald van Breederode shows how to setup a private DNS for your virtual cluster.

Pythian’s Alex Fatkulin discusses Oracle GoldenGate Extract Internals.

From Charles Hooper comes this investigation: Simple Query Generates Complex Execution Plan, the Mysterious 4063.88 Second Single Block Read Wait.

Coskan Gundogar was also in a deductive frame of mind. Here is his Working with statspack-part-1a-Diagnosis, featuring both a challenge and purty pictures (pastels!).

Here is Jonah H. Harris with an introduction to the NEXTGRES Gateway, a MySQL Emulator for Oracle. Jonah writes: “So, a few people have asked me what NEXTGRES Gateway is. My short answer, the ultimate database compatibility server.  . . . I’ve been working on this personal project non-stop for the last 8 months and am really excited about it.”

Read the rest of this entry . . .

The Call for Proposals for the 2010 MySQL User Conference and Expo ended about 6 hours ago. However, birds-of-a-feather sessions (BoFs) have a separate call for proposals. From the MySQL Conference page at http://en.oreilly.com/mysql2010/, select the “Program” menu item, then “Birds of a feather sessions”, and you will be directed to the proper page. Which, for easy clicking, is http://en.oreilly.com/mysql2010/public/cfp/90.

The call for proposals for Birds of a feather sessions closes at 11:59 pm PST on Thursday, February 18th, 2010. Sessions can be added during the conference, but if you submit and your BoF is accepted it will be printed in the schedule.

Note: If you submitted a BoF proposal in the regular call for proposals, it’s much much easier if you submit the proposal again through the BoF call for proposals.

In light of the official Oracle acquisition of Sun, I dug out a presentation video I realize I never officially shared with either the MySQL or the Oracle community. It’s the presentation I did at the 2008 Oracle Open World conference called, “So, you want to be an Oracle ACE?” and is a good resource for anyone who wants to contribute to anything — not just Oracle or MySQL, though certainly it’s based on my experiences with contributing to MySQL.

Oracle has lots of resources for the community. While I have joked about Oracle calling its conference “Open World”, I have also experience it, and the Oracle community first-hand and second-hand — through professional contacts such as my colleagues at Pythian who work on Oracle databases, and also through personal contacts such as my mother who has been to a few Oracle conferences herself.

Like MySQL, Oracle has recognized community contributors. However, Oracle offers more tangible benefits than a photo opportunity and a physical award. Oracle has the Oracle ACE program, with 2 levels: Oracle ACE and Oracle ACE director.
Read the rest of this entry . . .

Ronald Bradford’s recent warning to be sure to know your my.cnf sections reminded me of a similar issue that I ran into last summer, where putting the “group” option in both the [mysqld_safe] and [mysqld] directives resulted in a mostly silent problem.

I started noticing this in MySQL 5.1 and it affected both the official MySQL binary and the Percona binary. In trying to be conscientious, I had the following set:
Read the rest of this entry . . .

On first glance, it looks like TEXT and VARCHAR can store the same information. However, there are fundamental differences between the way TEXT fields and VARCHAR fields work, which are important to take into consideration.

Standard
VARCHAR is actually part of the ISO SQL:2003 standard; The TEXT data types, including TINYTEXT, are non-standard.

Storage
TEXT data types are stored as separate objects from the tables and result sets that contain them. This storage is transparent — there is no difference in how a query involving a TEXT field is written versus one involving a VARCHAR field. Since TEXT is not stored as part of a row, retrieval of TEXT fields requires extra [edited 1/22] memory overhead.

Read the rest of this entry . . .

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

Oracle

Let’s begin with remoteDBAexperts blog, and Chris Foot’s prediction of the future of database tuning and database administration. It will be, “ . . . administrators interpreting and implementing the recommendations generated by the intelligent advisors and ADDM.  . . .  I also think that Oracle will eventually become self-tuning.”

Here in the present, DBAs (Oracle and otherwise) are still Striving for Optimal Performance as Christian Antognini is. Here’s his item on join elimination, which he introduces thus: “In some specific situations the query optimizer is able to completely avoid executing a join even if a SQL statement explicitly calls for it. Two are the cases currently covered by this optimization technique, which is called join elimination.”

Jeff Hunter of the So What Co-operative was at the optimizer too, and found an interesting optimizer result, and pursued it into Interesting Optimizer Result, Part II. “If the query included the package call in the WHERE clause, the query finished in over an hour. If the package call was not in the WHERE clause, the query finished in 5 minutes (but did not return the correct results).  . . .  Confident in my fondness for inline views, I ran the query fully expecting to get the results back in a few minutes. Except the query went on, and on, and on for a full 15 minutes before I killed it.”

Kellyn Pedersen discusses what to do when PGA size is not enough. Read the rest of this entry . . .

Happy New Year to all our readers! Welcome to 2010 and the 174^th edition of Log Buffer, the weekly review of database blogs.

MySQL

The MySQL ’sphere since the holidays has been thick with posts on the matter of Oracle’s purchase of Sun, and thereby of MySQL. And in particular, there’s been a lot of talk about MySQL founder Monty Widenius’s response. I call all of this the . . . 

Monty My-Thon

On the 28^th of December, Monty framed the issue thus: Help keep the Internet free.

Singer Wang of Pythian, in reply, offers his perspective on GPL/ASL/BSD License Misconceptions and MySQL.

On Poo-tee-weet, Lukas Kahwe Smith is heard to say, Come on Monty . . .  “What on earth is Monty . . . thinking? How can you spin around 180 and expect to come of believable? How can suddenly the GPL be the wrong choice? How can suddenly OSS depend on proprietary sales?”

On the WireLust blog, Terrence Curran writes, Monty Widenius is trying to regain control of MySQL and why this is bad for OSS.

Kristian Nielsen shares some Oracle speculations, stating, “I think it is basically a matter of obtaining control over MySQL.”

Antony Curtis throws in his two cents: “The topic of today is [Monty's] ‘Save MySQL’ campaign and how I believe it is unnecessary.  . . .  In fact, I believe that it could be harmful.”

Read the rest of this entry . . .

The O’Reilly MySQL Conference & Expo 2010 Call for Participation ends in just under 3 weeks. I am on the conference committee, and thus get to see and review all the conference proposals.

This blog post will briefly explain the how each part of the proposal is used, then have a list of what not to do in your conference proposal, and end with a checklist of questions to go over your proposal before submitting. Click here if you want to skip to the checklist.

Read the rest of this entry . . .