Finally figured out what was causing the lack of performance on the
partitions with the query. The use of FORCE INDEX was causing the slowdown
with the partitioned table. Once I removed the clause, the query ran in 1
minute 19 seconds, which is more in line with expectations. Sorry for any
inconvenience!

Linux and Unixes have excellent metric of system load called "loadavg". In fact load average is is 3 numbers which correspond to "load average" calculated for one five and 15 minutes. It is computed as exponential moving average so most recent load have more weight in the value than old one.

What does Load Average corresponds to ? At least on Linux it is number of processes which are in "running" state or in "uninterruptable sleep" state which typically corresponds to disk IO. You can also map LoadAvg to VMSTAT output - it is something like moving average of sum of "r" and "b" columns from VMSTAT.

Obviously minimum value for LoadAvg is zero which corresponds to completely idle system, and there is no maximum

First thing to understand about LoadAvg it does not really tell you if it is CPU bound load or IO bound load. For example if you have LoadAvg of 10 it may mean there are 10 processes/threads actively consuming CPU or it …

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.

…