Pythian Group

Overview

We know that having the chance to split a table in different and smaller chunk helps.
It helps because performance searches, insert, index handling and data management as well.
All fine we are all happy, and very often we push on our customer to go for partition in order to solve part of their problems.
What happens quite often is that customer (and sometime MySQL dba as well) see partitions as separate tables.
Unfortunately is not like that, there are some operations that are (still?) creating unexpected results.
One of this is the OPTIMIZATION of a partition.

The case

Some time ago, almost 16 months I was at customer site and we were working with MySQL 5.5 rc.
We were really happy with the new version of MySQL, and being working for MySQL/SUN I was quite proud of it as well.
Then we run the ALTER TABLE X OPTIMIZE PARTITION Y;

Suddenly we realize something was not working as expected because all partitions had temporary tables.
We know and were expecting the LOCK while doing the exercise, but this was not expected at all.

I was attending several internal presentations, and I was in many internal calls,
all stating that the operation should not affect all partitions but only one.
It was not nice, and it was a bug (42822) marked non critical, referring to another one 46158,
which was close because referring to the previous as a duplicate.

Funny thing at the end is … we still have it in 5.5.12.
Read the rest of this entry . . .

At Kscope this year, I attended a half day in-depth session entitled Data Warehousing Performance Best Practices, given by Maria Colgan of Oracle. My impression, which was confirmed by folks in the Oracle world, is that she knows her way around the Oracle optimizer.

See part 1 for the introduction and talking about power and hardware. This part will go over the 2nd “P”, partitioning. Learning about Oracle’s partitioning has gotten me more interested in how MySQL’s partitioning works, and I do hope that MySQL partitioning will develop to the level that Oracle partitioning does, because Oracle’s partitioning looks very nice (then again, that’s why it costs so much I guess).
Read the rest of this entry . . .

One common question I get is how to use partitioning instead of MERGE tables. The process I use involves using stored procedures to create and drop partitions. This article will go over the stored procedures I use; special thanks to Roland Bouman for taking a look and giving great feedback to optimize this process.

First, a simple table, not partitioned (yet):

use test;
DROP TABLE IF EXISTS my_part;
CREATE TABLE IF NOT EXISTS my_part (
  id int NOT NULL,
  creationDate datetime NOT NULL,
  PRIMARY KEY (id,creationDate)
) ENGINE=InnoDB;

In real, life there is more to the table than just id and creationDate. The most important part is that the partitioned field(s) need to be part of the primary key.
Read the rest of this entry . . .

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://www.youtube.com/watch?v=Hs4S7vONGMQ. Or watch it embedded inline here:

Read the rest of this entry . . .