[gull] MySQL table partitions

[Daniel Cordey]

On Thursday 23 November 2006 23:19, Marc SCHAEFER wrote:
> On Thu, Nov 23, 2006 at 06:15:49PM +0100, Daniel Cordey wrote:
> > Je suis en train d'etudier la possibilite, avec MySQL 5.1, de creer des
> > tables avec des partitions et des sous-partitions.
>
> Peux-tu préciser de quoi il s'agit ?  A quoi ça sert, qu'est-ce que cela
> fait, etc ?

La notion de partition permet d'assurer le "decoupage" d'une table. Chaque 
partition est materialisee par un "fichier" (en fait trois). Ceci permet de 
repartir ces partitions sur differents disques physiques. Le partitionnement 
est determine lors de la creation de la table en definissant la regle de 
pertionnement. Ces reges concernent des valeurs d'une, ou plusieurs, colonne. 
Si, par exemple, on a une colonne 'year', on peut dire (shematiquement) :

partition 1  <==> 1995 < year < 1997
...

Les regles de partitionnement peuvent etre definie par n'importe quelle 
operation SQL. CAD que l'on peut effectuer des operation de calcul et de 
comparaison de dates etc. 

C'est le serveur qui, lors de la requete a la base, va calculer le numero de 
partition sur la base des regles definies et des arguments de la requete. 

On peut dire que les partitions appportent un avantage suplementaire et 
non-negligeable aux tables MERGE de MySQL. Une table MERGE n'est pas 
inteligente et balaie toute les tables, sans avoir la possibilite de 
determiner quel table est pertinente pour la requete. De plus, une table 
MERGE n'a pas d'index mais se repose sur celles de toutes les tables la 
constituant. Ce qui fait qu'un insertion ne peut se faire au travers de la 
table MERGE mais doit etre explicitement faite sur la bonne table. En fait, 
une table MERGE evite de faire des jointures mais impose un certain nombre de 
contrainte logistique parfois assez delicates. 

A contrario, les partitions permettent de s'affranchir de toutes les 
contraintes des tables MERGE. Comme il s'agit d'une seule table, les 
partitions sont garanties semantiquement identiques, l'insertion se fera 
automatiquement dans la bonne partition, les performances sont optimum, etc. 

Les partitions permettent donc de fractionner un tres grosse table en plus 
petits morceaux, en ameliorant les performnces puisque la taille des indexes 
de l atable initiale se trouvera fragmentee en plus petits morceaux 
(insert/update !). De plus, il est possible de repartir les partitions sur 
plusieurs disques, ameliorant les performances globales de l'ensemble.

Bref, pour gerer 30 tables avec une table MERGE, j'avais ecrit un programme de 
modification/creation special, ainsi qu'un module python assez complexe pour 
optimiser l'acces exhaustif des tables pour les requetes. Avec les 
partitions, je peut jeter mon premier programme et virer plusieurs centaines 
de lignes de code de mon module... tout en etant sans doute plus performant 
dans certains cas et sans les problemes de logistiques tres delicats de la 
table MERGE.

... Et... pour couronner le tout... sachez qu'il est possible de faire 
des 'SUB-PARTITION'. Je sens que je vais me regaler :-)

Cette fonctionalite n'est disponible qu'a partir de MySQL 5.1 

Partitioning: This capability enables distributing portions of individual 
tables across a filesystem, according to rules which can be set when the 
table is created. In effect, different portions of a table are stored as 
separate tables in different locations, but from the user point of view, the 
partitioned table is still a single table. Syntactically, this implements a 
number of new extensions to the CREATE TABLE, ALTER TABLE, and EXPLAIN ... 
SELECT statements. As of MySQL 5.1.6, queries against partitioned tables can 
take advantage of partition pruning. In some cases, this can result in query 
execution that is an order of magnitude faster than the same query against a 
non-partitioned version of the same table. See Chapter 16, Partitioning, for
further information on this functionality. (Author: Mikael Ronström)

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

> PostgreSQL (libre) ou Progress (propriétaire) ?

Progress... non merci... il s'agit bien de PostgreSQL.

dc