In my previous post I started out by setting up MySQL 5.1.12 on a box in order to test the performance of the new partitioning option. During testing, I noted that I did not see any noticeable performance improvements from using partitioning. So I spent some time Saturday and Sunday (I guess I don't have anything else better to do!) to build the testing environment and perform the tests. So I was wrong, but only slightly. Partitioning does show better performance than standard tables, but not by as much as you would think. But, wait, there is light at the end of the tunnel (as well as a WTF). The numbers...

By partitioning, …

Been quite a while since I've posted on my blog... I've been really busy planning the 2007 MySQL Conference and Expo (yes, the planning starts way early). This past week, I've gone through hundreds of presentation proposals, some of which are fantastic. We will begin notifying external speakers — that is, non MySQLers — in the next day or two.

Edwin and the markeing team have put together a survey for the MySQL user community on a website called Zoomerang. One of the survey participants will win a free pass to the 2007 MySQL Conference (>$1,000 value!), so if you've got time, please consider filling out the survey and giving us your opinions and suggestions.

Complete the survey here

I was fighting four days now, with a threading problem, which are known to be hard to track. But I finally found it and learned that I actually had made a beginner’s mistake.

What happened?
From the front end I trigger via AJAX a view that again starts a thread that does some import work, that might take quite a while. This enables the user to keep going and have the import run without interrupting him/her. Every once in a while an asynchronous call checks on the state of the import.
And here lies the problem: while the thread is running and busy like a bee adding data in the DB the asynchronous call to check on the state also tries to run a query and that causes the following exception:

...
 File "/Users/cain/programming/django/trunk/django/db/backends/mysql/base.py",
line 42, in execute
   return self.cursor.execute(sql, params)
 File …

A lot of MySQL software development articles focus on the server-side. This is true even end-user applications: most MySQL applications I read about, are web applications.

I decided it would be fun to write down a few simple tips on how to use MySQL in Office applications. Yup, you heard me: using MySQL as "Desktop Database", quite like MS Access is being used on many, many windows desktops.

Of course, I do not want to suggest that MySQL "...is a Desktop Database", because it's not. Rather, MySQL is a general-purpose database, so there's no reason at all to dismiss it for typical "Desktop Database" purposes.

Even though MySQL is a server product, MySQL resembles a typical Desktop Database in a number of ways. For example, MySQL is not extravagantly resource-hungry, and does not …

I attended the T-Dose open source event yesterday in Eindhoven (The Netherlands) and it must be said: the TU/e (Technical University of Eindhoven) looks pretty nice indeed! Entering the T-Dose I was a bit overwhelmed by the venue. However, on Saturday, the crowed was thin. Of course, it was the first time and I hope it will be as big as FOSDEM in Brussels some day.


My talk was scheduled for 15:00 so I had a bit time to mix with the people. To be honest, I'm still not used to this.. There was a booth of UPservers which is selling certified hardware for MySQL and MySQL Cluster. It's interesting to actually have face-to-face contact with people actually having a clue about hardware.

Gnome and KDE had a booth there as well. Yes, it has evolved a bit again. However, I left the Linux workstation for MacOS X: just because I got lazy I guess.

…

Finally, it's time to start putting MySQL 5.1.12-beta through the wringer. First order of business, convert the existing table schema to one that supports partitioning...

I made some minor changes to the configuration for partitioning, namely innodb_file_per_table and innodb_open_files. I set innodb_open_files to 1000 based on the tables and partitions I plan on supporting.

This is what the new table schema looks like with partitioning:

CREATE TABLE `network_daily` (
  `entity_id` int(11) NOT NULL default '0',
  `buyer_entity_id` int(11) NOT NULL default '0',
  `buyer_line_item_id` int(11) NOT NULL default '0',
  `seller_entity_id` int(11) NOT NULL default '0',
  `seller_line_item_id` int(11) NOT NULL default '0',
  `size_id` int(11) NOT NULL default '0',
  `pop_type_id` int(11) NOT NULL default '0',
  `country_group_id` int(11) NOT …

I attended the T-Dose open source event yesterday in Eindhoven (The Netherlands) and it must be said: the TU/e (Technical University of Eindhoven) looks pretty nice indeed! Entering the T-Dose I was a bit overwhelmed by the venue. However, on Saturday, the crowed was thin. Of course, it was the first time and I hope it will be as big as FOSDEM in Brussels some day.


My talk was scheduled for 15:00 so I had a bit time to mix with the people. To be honest, I'm still not used to this.. There was a booth of UPservers which is selling certified hardware for MySQL and MySQL Cluster. It's interesting to actually have face-to-face contact with people actually having a clue about hardware.

Gnome and KDE had a booth there as well. Yes, it has evolved a bit again. However, I left the Linux workstation for MacOS X: just because I got lazy I guess.

…

It's not often that I am criticized for being technically imprecise. After all, I'm an open-source code monkey and proud of it. This time, the criticism came from a marketing guy, and even more surprisingly, he was right.

I was discussing the launch of an internal Google tool called 'Google Mondrian' with Lance Walter, who runs marketing at Pentaho, but is also technical enough to tell a big-endian half-adder from a Python cross-compiler if he were to trip over one in his front lawn. Google Mondrian is a web-based code-review tool written by Guido van Russum, the author of Python, and is apparently in widespread use internally within Google.

Our concern was that a high-profile announcement would somehow overshadow the Mondrian open-source OLAP project, but we fairly quickly concluded that this was unlikely. Supposing that this …

I thought about covering an issue that many people will come across when implementing MySQL Cluster if they are not aware of it. That is of bandwidth and latency in TCP/IP when implementing a transport over ethernet cabling.

The MySQL Cluster is always passing information between the different nodes that it contains. This is obvious for synchronisation and also for retrieval of data through the MySQL api nodes. When people first setup the cluster they often go for a default install on a higher speed network such as gigabit ethernet (GigE). This will probably work fine for very small data sets or initial testing data, but when the data becomes larger in size, the user realises very quickly that there is a lot of traffic passing through the network on the cluster.

So what can be done to help in reducing the load on the network, or in improving the performance of the cluster's network? Well, the first and most simplest step is to make …