Re: Partionnement automatique

On 30/08/2011 18:57, Cédric Villemain wrote:
> Le 30 août 2011 16:09, Baptiste Manson
> <baptiste(dot)manson(at)inovia-team(dot)com> a écrit :
>> Bonjour à tous,
>>
>> C'est mon premier article dans cette mailing list, alors n'hésitez pas
>> à me faire vos commentaires si je ne respecte pas l'étiquette.
>> Nous avons écrit quelques articles sur la mise en place d'un
>> partionnement automatique :
>> L'idée est de fournir, pour des applications avec peu de write
>> (beaucoup de read) des fonctionnalités avancées pour gérer les
>> partitions et les maintenir.
>> Nous avons implémenté cela avec un trigger basé sur l'INSERT et sur
>> une clé fonctionnelle de partionnement.
>> Voici le code de la fonction concernée :
>>
>> -- Attach a magic function to the insert of this table:
>> CREATE OR REPLACE FUNCTION create_partition_and_insert()
>> RETURNS trigger AS
>> $BODY$
>> DECLARE
>> partition VARCHAR(25);
>> BEGIN
>> partition := TG_RELNAME || '_' || NEW.period || ‘p’;
>> IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
>> RAISE NOTICE 'A partition has been created %',partition;
>> EXECUTE 'CREATE TABLE ' || partition || ' (check (period = ''' ||
>> NEW.period || ''')) INHERITS (' || TG_RELNAME || ');';
>> END IF;
>> EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' '
>> || quote_literal(NEW) || ').*;';
>> RETURN NULL;
>> END;
>> $BODY$
>> LANGUAGE plpgsql VOLATILE
>> COST 100;
>> Voici un article détaillant le processus :
>> http://blog.inovia.fr/auto-partitioning-on-postgresql-part-1/
>>
>> Quels inconvénients et avantages voyez vous sur cette méthode ?
> L'avantage est le coté dynamique bien sur. Toutefois il faut pondérer.
> Le dynamisme sur l'insert dans une table ou dans une autre aura un
> léger impact comparé à un insert dédié pour chaque table mais impact
> probablement négligeable. (et puis a l'inverse, le nombre de trigger à
> déployer peut impacter si il y en a trop).
J'ai eu l'occasion de tester les performances des diverses stratégies
pour insérer dans une partition, donc je me permets d'intervenir… :)

Pour commencer, entre insérer directement les enregs dans la bonne
partition, et insérer via le trigger, il y a presque un facteur dix,
surtout si on passe par un COPY ou un insert multiple. Donc quand on
peut, dans l'appli cliente, envoyer dans la bonne partition, il ne faut
surtout pas hésiter…

Par contre le gain entre insérer par un ordre SQL dynamique comme dans
l'exemple ou par un INSERT dans chaque partition (avec une série de IF
dans le code PL) est moindre: c'est environ 2 fois plus coûteux de
passer par l'ordre SQL dynamique. Par contre, les tests IF eux même
peuvent devenir très coûteux, si il y a beaucoup de partitions (on peut
imaginer 100 IFs pour 100 partitions si on code de façon naïve, et là on
devient 5 fois plus coûteux que l'insert dynamique, et avec un temps
variable d'exécution suivant la partition dans laquelle atteri
l'enregistrement). Donc si on part du côté des multiples IF consécutifs,
il faut en plus prévoir de faire une arborescence de IF, pour limiter le
nombre de tests. Donc créer un arbre binaire équilibré, et l'utiliser
pour générer l'arborescence de IFs. Trigger illisible garanti :)

On peut aussi essayer, si on est en 9.0, de faire des triggers avec une
clause WHEN. Comme avec le PL, les tests pour déterminer quel trigger
doit être exécuté deviennent rapidement trop coûteux quand le nombre de
partitions augmente (du même ordre que les IFs naïfs dans le PL).

J'ai en dernier recours essayé en PL/Perl, avec un objet $_SHARED qui
contenait tous les plans préparés pour toutes les partitions, au fur et
à mesure qu'elles étaient accédées. La vitesse était comparable à la
requête dynamique en PL, probablement du au surcoût de passer les
données à Perl.

Pour résumer, de ce que j'avais retiré de mes tests, la stratégie
'simple' pour l'insertion dans les partitions, c'est de passer par du
SQL dynamique. Les performances restent décentes, quel que soit le
nombre de partitions, et ne dépendent pas du nombre de partitions. On
peut aller plus vite en construisant un arbre binaire de IF dans une
procédure PL, et en écrivant le code pour le générer. Et en re-générant
le code PL à chaque ajout de partiition. Mais je ne conseillerais pas
cette solution, même à mon pire ennemi, c'est vraiment laid.

Je n'ai pas fait de tests sur les rules, étant donné que dans le
contexte dans lequel j'étudiais la chose, l'injection des données
passait par COPY (et donc ne déclenchait pas les rules…).