MySQL 5.1 offers new information tables such as GLOBAL_STATUS. This can be used to report certain performance metrics, such as the number of queries processed per second:

SELECT MAX(                                         -- use MAX to force aggregation
           IF(variable_name='Questions'             -- no. of queries sent to server
           ,   CAST(variable_value AS unsigned)     -- make integer value
           ,   0                                    -- ignore if not 'Questions'
           )
       )
/                                                   -- divide by
       MAX(                                         -- use MAX to force aggregation
           IF(variable_name='Uptime'                -- no. of seconds the server is up
           ,   CAST(variable_value AS unsigned)     -- make integer value
           ,   0                                    -- ignore if not …

From time to time we get the question how to split a query into a several smaller queries and unifying the result-set before we send it back to the client.

As the client only expects to get one result-set, we have to merge the result-sets from the server into one, like this:

First we need a storage for the result-set we want to build:

res = { }

Each connection gets its own one. We declare it outside of the functions as we want to share it between the result-sets of the same connection.

As an example let me just duplicate a query and send it to the server twice:

function read_query(packet)
    if packet:byte() ~= proxy.COM_QUERY then return end

    local q = packet:sub(2)

    res = { }

    if q:sub(1, 6):upper() == "SELECT" then
            proxy.queries:append(1, packet)
            proxy.queries:append(2, packet)

            return proxy.PROXY_SEND_QUERY
    end
end …

There were some funny moments during the conference at Rome University.
Before Marten's arrival, we built an impressive heap of fluff dolphins on the desk.

For convenience, Marten used my laptop (an Apple MacBook) for his presentation, and he remarked about "open source enthusiasts who use closed source software". Mac OSX is, indeed, not open source, but it is the friendlier closed source operating system around. And since I am not a zealot, but I use what is best for me, I can cope withApple. My Mac has all the applications I am used to in Linux, with more pleasant graphics, media, and networking features. This is my personal opinion, and YMMV. Back to the topic.

After the presentation, there was a Q&A session. When Marten said he was ready to take questions, I addressed the audience in Italian, saying that we could …

Today I have uploaded Q4M (a Queue for MySQL) 0.6, which is basically a performance-improvement from previous releases. Instead of using pread's and a small user-level cache, Q4M (in default configuration) now uses mmap for reads with a reader/writer lock to improve concurrency.

I also noticed that it would be possible to consume a queued row in one SQL statement.

SELECT * FROM queue_table WHERE queue_wait('queue_table');

This statement actually does the same thing as,

if (SELECT queue_wait('queue_table') == 1) {
  SELECT * FROM queue_table;
}

But since the former style requires only one SQL statement (compared to two statements of the second one), it has much less overhead.

And combining these optimizations together, consumption speed of Q4M has nearly doubled from previous post (or trippled from 0.5.1) to over 57,000 rows per second. …

Download Maatkit

Before I tell you what’s new, let me tell you how cool I think it would be if Maatkit were voted Sourceforge.net project of the year. Just something to think about :-) I suggest the “Best Tool or Utility for SysAdmins” category. You can actually click the Back button and nominate it for several categories. Not that anyone would do that, of course.

Also, if anyone wants to jump in and help out with bug fixes and new features, please, by all means. Maatkit is a true open-source project as well as being Free Software. If you can follow coding conventions and understand Perl, I’m a very benevolent dictator and would gladly grant commit rights. As it turns out, since I’ve joined Percona I’m interested in a whole different set of things, …

See http://www.spreadfirefox.com/en-US/worldrecord/ for details.

I'm working on a project that needs cross-platform use, through a browser. Pondering whether to just "standardise" on FireFox since it runs on all.... that way development focus can go towards actual functionality rather than hacks to make all different browser brands behave... your thoughts?

While preparing a presentation for Rome University, I took many snapshots of MySQL web site. I asked for review, and Colin pointed at the advertising that was in most every page.
Now that he mentioned it, yes. I saw the advertising. But when I was working with the live page, taking the screenshots, adjusting them with the Gimp, and inserting them into the presentation, I did not notice them at all.
I am blind to ads.
I must be the worst nightmare for advertisers. I have pop-up blockers and javascript filters in my browser, so don't see many of them, but the remaining ones are like a ink stain on the page. My brain registers the presence of an alien presence, and quickly instructs my senses to ignore it. If someone tells me that there is an ad in a given page, I have to look at the page twice, to put the ad into focus.
Why does this happen?
I guess that, after so much time spent using the web, I am trained to spot …

From time to time we get the question how to split a query into a several smaller queries and unifying the result-set before we send it back to the client.

As the client only expects to get one result-set, we have to merge the result-sets from the server into one, like this:

First we need a storage for the result-set we want to build:

res = { }

Each connection gets its own one. We declare it outside of the functions as we want to share it between the result-sets of the same connection.

As an example let me just duplicate a query and send it to the server twice:

function read_query(packet)
    if packet:byte() ~= proxy.COM_QUERY then return end

    local q = packet:sub(2)

    res = { }

    if q:sub(1, 6):upper() == "SELECT" then
            proxy.queries:append(1, packet)
            proxy.queries:append(2, packet)

            return proxy.PROXY_SEND_QUERY
    end
end

If it isn't a …

LewisC's An Expert's Guide To Oracle Technology

Sun just announced MySQL Workbench, a new database design tool for MySQL developers and DBAs. I'm a data modeling tool junkie. I like to play with any I can get my hands on. I've used almost every modeling tool that's been built. My all time favorite is probably Erwin.

I decided to download MySQL Workbench and give it a try. Since I was playing with it, I figured I should write about it and while I am writing about it, I might as well write about a couple of other tools, that I have personally used, that you might like.

TOAD …

Disaster is really inevitable. Even with all the redundant power investments, ThePlanet (formerly EV1 and RackShack), had to shut down their backup generators at their H1 data center on the instructions of the fire crew. This happened after a wire-short in fault transformer led to an explosion that knocked off one of their walls, ultimately bringing 9,000 servers down. Luckily no one was injured.

This just goes on to show that just because a data center has redundant power and backup generators, it does not mean that a disaster cannot happen. IIRC, ThePlanet's last disaster was blamed on backup generators not kicking off properly.

While there was no damage to servers, I wonder how many MyISAM repairs need to be triggered once the servers do come back online?

- …