In last couple of weeks there has been a tide of ORDER/GROUP BY-related optimization bugs, where I was the fixer or the reviewer. This wasn't an easy job because there is no sane description of how GROUP BY/ORDER BY handling is supposed to work.

To figure it out, I had to write an explanation of how it works. The first part is about ORDER BY. Hopefully there will be subsequent parts that will show how GROUP BY is related to ORDER BY and how it works.

Available means to produce ordered sequences

MySQL has two methods to produce ordered streams.

The first is to use a "range", "ref" or "index" access method over an ordered index. For versions up to 5.1, those access methods naturally return records in the index order, so we get ordering for free (the exception is NDB engine which needs to do merge-sort when it gets data from several storage nodes). In MySQL 5.2, MyISAM and InnoDB have …

A high-performance application that has producers and consumers of some resource, such as a queue of messages, needs an efficient way to notify the consumers when the producer has inserted into the queue. Polling the queue for changes is not a good option. MySQL's GET_LOCK() and RELEASE_LOCK() functions can provide both mutual exclusivity and notifications.

This post was prompted by a message to the MySQL general emailing list some time ago, but I'm finally getting around to actually testing the theoretical solution I mentioned then. I can never just think my way through anything that involves locking and waiting... I have to test it.

I (belatedly) noticed a meme running on Planet MySQL regarding wishlist items for the company. I think it started with Jay Pipes and Mårten Mickos, but has since moved on to users. In particular, I'd endorse most of Jeremy Cole's and Ronald Bradford's wishes myself as well.

But let me jump on the bandwagon and offer my view of the things that would most help us run and develop our services.

1. Online table changes. Ronald mentioned this as well, but I have to emphasize this more: for all the good that InnoDB did in terms of …

A few weeks ago (time flies..) i posted a short note about my LOAD XML contribution. It has actually been available for some time, veven if it is not so easy to find. It is included in MySql 5.2.5:
download
documentation - quite good I think!

It is a bit sad that it is included in 5.2 (still in alfa) and not in 5.1. Since it is a separate command it will not affect existing installations and would not be risky. That faster it gets included the faster we will get feedback on it, which is what I believe is needed right now. Is this something that MySql users need and will use? Or will they use other tools to read XML data into their MySql databases?

LOAD XML reads data into a MySql table in much the same way as LOAD DATA does and in fact …

I've returned home safely from my trip to St. Augustin, Germany (near to Bonn), where I attended this year's FrOSCon. As last year, it was a very well organized event, kudos and thanks to the conference organizers (who are all volunteers!). The conferece program was packed with good sessions again and the ones I attended were interesting and well done. Many OSS projects also exhibited in the hall way, it was nice to stop by and chat with these folks.

I gave a talk about "Opening the doors (and windows) of the Cathedral - Enabling an architecture of participation around the MySQL Server", which describes the ongoing activity at MySQL to open up the development processes to the Community. Given that I gave this talk for the first time, I think it went very well, at least I did not run out of time The slides are …

Well, I have written a good bit about MySQL Cluster this year.  We had been using it as a sort of pregenerated cache for our forward facing web servers.  However, we have decided on a different route.

Why the change

With normal MySQL, configuration can make big performance differences.  With cluster, it can make the cluster shut down. We woke up one morning to errors about REDO logs being overloaded.  It had been overloaded for about 8 hours.  We had made some changes the day before, but they all worked fine on our test cluster.  So, we shut down the processes that were new and even shut off all other processes that were loading data into the servers.  4 hours later, the simplest insert would still complain about the REDO logs.  The only thing that cleared it up was a full rolling restart of the storage nodes.  That took 5 hours.  Luckily, we were still operating …

A high-performance application that has producers and consumers of some resource, such as a queue of messages, needs an efficient way to notify the consumers when the producer has inserted into the queue. Polling the queue for changes is not a good option. MySQL’s GET_LOCK() and RELEASE_LOCK() functions can provide both mutual exclusivity and notifications. This post was prompted by a message to the MySQL general emailing list some time ago, but I’m finally getting around to actually testing the theoretical solution I mentioned then (I can never just think my way through anything that involves locking and waiting… I have to test it).

Having switched from Oracle to MySQL we were chugging along with development when we realized that in MySQL, autocommit is set to 1 by default. Ie, if you don’t explicitly start and end a transaction, each statement will commit as it executes. Type “delete from user” in the MySQL client, hit return, think “oops”, and it’s too late. “rollback” won’t help you now.

So I used “init_connect” to set autocommit=0 for incoming connections. (In my.cnf, I added init_connect=’set autocommit=0′.) Seemed to work great. (init_connect can be used to set session-level variables for connections.)

Then we moved this change from dev and QA into production. Suddenly, our UI wasn’t working. We were scrambling to figure out what was different between QA and production. I was told that the permissions must be different. Being a real newbie, I compared all the grants for ‘UI’ in the information_schema database and said they …

When we optimize clients' SQL queries I pretty often see a queries with SQL_CALC_FOUND_ROWS option used. Many people think, that it is faster to use this option than run two separate queries: one - to get a result set, another - to count total number of rows. In this post I'll try to check, is this true or not and when it is better to run two separate queries.

For my tests I've created following simple table:

PLAIN TEXT SQL:

1. CREATE TABLE `count_test` (
2.   `a` int(10) NOT NULL AUTO_INCREMENT,
3.   `b` int(10) NOT NULL,
4.   `c` int(10) NOT NULL,
5.   `d` varchar(32) NOT NULL,
6.   PRIMARY KEY  (`a`),
7.   KEY `bc` (`b`,`c`)
8. ) ENGINE=MyISAM

Test data has been created with following script (which creates 10M records):

PLAIN TEXT PHP:

1. …

In the Enterprise Team we had the need to track how many connections to the MySQL server in parallel. By "used" I mean running queries and not just idling around.

And the make it more complicated we needed the information not just every few seconds (while true; do echo "SHOW PROCESSLIST" | mysql; sleep 1;done) but when it happens.

Proxy to the rescue we now have a script which tracks the global state of all connections going through the proxy and dump them to stdout when their state changes:

2007-08-28 21:39:55
  #connections: 20, #active trx: 3, max(active trx): 4
  [792] (merlin@merlin) (COM_QUERY) "SET autocommit=0" (state=started)
  [794] (merlin@merlin) (COM_QUERY) "SET autocommit=1" (state=started)
  [795] (merlin@merlin) (COM_QUERY) "SET autocommit=1" (state=started)

• when the query comes in, we add it with its thread-id to the global table
• when the query is done, we …