|Showing entries 1 to 11|
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[Read more...]
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
creationDate. The most important part is that the partitioned field(s) need to be part of the primary key.
Now, add the partition definition. This can be done in the[Read more...]
innodb_file_per_table, then you have a separate .ibd file for each InnoDB table.
ALTER TABLE foo DISCARD TABLESPACE;(this deletes the current .ibd file)
ALTER TABLE foo IMPORT TABLESPACE;
By default, Cluster will partition based on primary key
When adding rows to a table that’s using MySQL Cluster as the storage engine, each row is assigned to a partition where that partition is mastered by a particular data node in the Cluster. The best performance comes when all of the data required to satisfy a transaction is held within a single partition so that it can be satisfied within a single data node rather than being bounced back and forth between multiple nodes where extra latency will be introduced.
By default, Cluster partions the data by hashing the primary key. This is[Read more...]
At the January 2009 Boston User Group I presented a session on the new partitioning feature in MySQL 5.1. I go through how to define partitions, how partitioning makes queries faster, the different types of partitioning and when to use each type, and the restrictions and limitations of partitioning.
The slides are available at http://www.technocation.org/files/doc/2009_01_Partitioning.pdf. The 380.6 Mb .mov movie (1 hr 16 min) can be played directly in your browser at http://technocation.org/node/671/play or downloaded at http://technocation.org/node/671/download.
The partitioning part of the MySQL Manual is at:
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://technocation.org/node/663/play. To download the 146 Mb video to your computer for offline playback, go to http://technocation.org/node/663/download. The slides can be downloaded as a[Read more...]
How good are your SQL and/or general coding skills? I have a specific challenge I'd like your help solving. I am not sure it's possible, but I'd love to be proven wrong.
|Showing entries 1 to 11|