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 …

I believe we wrote about this before, but this topic popups again and again.
Today I've read opinion that if we have clause WHERE has_something=1 we should have index on column `has_something` (the column has two values 0 and 1).

In reality the right answer is not so simple.

Let's look next table

PLAIN TEXT SQL:

1. CREATE TABLE `testr` (
2.   `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3.   `name` varchar(32) NOT NULL,
4.   `has_something` tinyint(3) UNSIGNED NOT NULL,
5.   PRIMARY KEY  (`id`),
6.   KEY `has_something` (`has_something`)
7. ) ENGINE=MyISAM

with 20.000.000 records.

And in first case has_something=0 for 90% of rows (with random distribution)

PLAIN TEXT SQL:

1. mysql> SELECT cnt0/cnt FROM (SELECT count(*) cnt0 FROM …

Dana Blankenhorn over at sister publication ZDNet has a great analysis of the looming Microsoft Sharepoint threat. As Dana points out, it's a threat that open source is well-suited to meet and beat, but doing so will require some project coordination.

Interestingly, it's a threat that the proprietary Enterprise Content Management vendors have rejected as credible, even as Sharepoint boots them out of customer accounts. While ECM, operating system, database, etc. vendors sleep, Sharepoint is gaining ground.

If only open source could get its act together:

...

Where can you get access to some of the most informed people from MySQL and the community, for free?

The answer: at MySQL Camp. And then throw in lunch and breakfast for free, being able to influence the session topics and you have quite a package deal.

So it is strange why so few people took up the offer in New York this year!?

My talk was about the BLOB Streaming engine, MyBS, and I have posted the slides: Presentation - MySQL Camp 2007: The BLOB Streaming Project.

OK, so I got pretty much ragged about the name, MyBS. Why, I was asked, did I name it that? Jay, even suggested a session to find a new name for the engine! Thanks, Jay, very considerate of you... :)

But it was quite unnecessary, because I really can't see what the …

About year ago Peter wrote about redundant indexes and mentioned sometimes it is good to leave two indexes, even one is first part of another. I'm speaking about BTREE indexes, for example, KEY (A), and KEY (A,B). From SQL point of view KEY(A) is not needed, as for queries like WHERE A=5 the index (A,B) also can be used.

But there is case when for performance it would be good to have both

Let we have the table

PLAIN TEXT SQL:

1. CREATE TABLE `userinfo` (
2.   `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3.   `name` varchar(64) NOT NULL DEFAULT '',
4.   `email` varchar(64) NOT NULL DEFAULT '',
5.   `password` varchar(64) NOT NULL DEFAULT '',
6.   `dob` date DEFAULT NULL,
7.   `address` …