The latest from the MySQL community

By Tim O'Reilly

As some of you have probably already noted, I recently joined the MySQL board. There are a couple of good reasons.

First, MySQL has a unique position at the juncture of two of my abiding interests, open source and Web 2.0. As I wrote in the quote that I provided for the press release: "The platforms powering the Web today are the enterprise infrastructure of tomorrow. What we learn from those platforms is the enormous power of open source, open standards, and user involvement ... and one more thing -- that the future belongs to data. Every killer app on the Internet is a database application. And that makes MySQL the 'Intel Inside' of the next-generation of computer applications."

It's not every open source project that gets to be part of the acronym …

Are you seeing a MySQL error that says InnoDB support isn't enabled, even though it is? This article explains why it happens and how to fix it.

The way InnoDB handles locking of the Buffer Pool has been changed with 5.0.30. Sites that have a large (16GB+) innodb_buffer_pool_size and high concurrency (4+ CPUs) will experience lock contention of the single global Buffer Pool lock in older versions of MySQL. In Cacti that behavior can look like this:

5.0.26, high InnoDB load, memory saturated environment (database smaller than RAM), very high concurrency.

CPU usage >100% not plotted properly, thus the strange graph. Watch the high system time consumption.

After upgrading from 5.0.26 to 5.0.36, the lock contention goes away. The gain materializes itself as a greatly reduced system time usage.

5.0.36, high InnoDB load, memory saturated, high concurrency.

User time comparable, system time a lot smaller.

It has been very productive this week working at Brian's place in Seattle. No sightseeing this trip but had an enjoyable experience talking with other developers and users at the MySQL meetup session.
I have been working on developing a flexible Log Plugin system which hopefully does not have the drawbacks of the existing system. The new code seems to be working well now although it is not tied into any system variables in a way to show off what it can do. My patch earlier on Wednesday replaced about 2500 LOC with about 500. Even after the code written today, there is still a significant reduction in the overall code size. Now, all I need is my plugin server variables patch pushed...

Open Source software is often being referred to as commodity products. This is particularly true for OSS databases like MySQL or PostgreSQL. Developers of such systems can heavily make use of defined standards. In this case, it’s the various SQL standards. These standards define the general functionality set your product should have. They help you define the commodity features of your software.

The question is: where do you get your software requirements from if the OSS product you are developing cannot rely on any or only a few standards?

Let’s take a look at two other types of OSS products: Enterprise Content Management (ECM) and collaborative software. I used to work for an Open Source ECM vendor until recently and just …

I reckon that techies sometimes go overboard in accuracy!
Many sites use so-called Great-Circle calculations to find places within a certain distance of a specified location. The resulting queries tend to do full table scans with relatively few actual matches, then order by distance and page or limit that.

If, instead, you use a bounding rectangle and also pretend that earth is flat, life becomes much easier. The resulting queries then will use range scans on indexes. So much faster you wouldn't believe it.

What's the difference? Well, accuracy.
For short distances, the earth's curvature is not significant.
Apart from that, the maximum deviation from the radius is (SQRT(POWER(r,2) * 2) - r) where r is the radius, simply applying Pythagoras' Theorem.
It comes down to a deviation of about 20 kilometers when looking in …

For those that follow Daylight Savings Time in the US and Canada, watch out this weekend, because we “spring forward”!

The biggest caveat I have is: Do not arrive 1 hour late to work on Sunday or Monday.

As for MySQL, to test if you are fine, run:

SELECT @@global.time_zone;

If you get back “SYSTEM”, then MySQL is looking to the OS for timezone data, which is the default.

The real sanity check:

SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'), UNIX_TIMESTAMP('2007-03-11 03:00:00');

This should return the same value, even though you are feeding it different times, because this is when the 1 hr change occurs. If not, and you’ve played with timezone data, remember that timezone data is only loaded when MySQL starts, so if you haven’t restarted MySQL since you patched your OS, you need to do that.

This is mostly stolen from a MySQL list post I found …

Do you hate it when you execute a query in the mysql cli only to have the output wrap three times making it almost impossible to read? Do you end up re-executing the query with \G waiting once again for the query to run? Well wait no more! My very first submitted patch to mysql is a client patch that adds –auto-vertical-output. This option compares the width of your terminal to the width of the result set and enables vertical output if it’s going to wrap. The patch link and details are Bug #26780

At last month’s Vancouver PHP Conference, local journalist Bruce Byfield gave me a quick interview about how I got involved in Free Software and Open Source. We chatted for a while about my time at MySQL, my bits of work in the PHP community and what I am currently up to. The interview turned into a profile for Linux.com that was published a few days ago.

I was a bit surprised to find that there have been a few anonymous trolls for the profile - I didn’t think that there would be any comments on the profile.

The Linux.com profile: …

XenSource launches global virtualization initiative…O’Reilly joins MySQL AB board of directors…OSBC announces keynote speakers…and more…

XenSource to Kick-off Global Virtualization Initiative, XenSource (Press Release)

Tim O’Reilly to Join MySQL AB’s Board of Directors, MySQL AB (Press Release)

InfoWorld’s Open Source Business Conference Announces Keynote Speakers for May Event, InfoWorld (Press Release)

Mr. Softy Won’t Save Novell, The Motley Fool, Tom Taulli (Article)

…