postgreSQL partitionnement information
Le
Etienne
Salut;
J'ai une très grosse table dans laquelle je fais des requetes sur un
champs indéxé.
J'envisage je partitionner cette table.
J'ai quelques questions tout de même avant de procéder à l'opération.
Tout d'abors est ce vraiment utile en terme de performance.
Mon interrogation n'est pas tellement sur les lectures, mais plutôt sur
les lectures et écritures concurrentes.
je passe mon temps a ajouter, modifier, supprimer et lire des
enregistrement dans cette table.
Les transactions lock les tables. Donc je suppose que plus de tables =
moins de conflit non ?
Bref. Est ce que quelqu'un aurai un retour sur l'interet du
partitionnement. Car c'est quand même assez penible à mettre en place
alors autant ne pas le faire pour rien.
Ma deuxième question est:
puisque le partitionnement dans postgreSQL est juste un usage
particulier de l'héritage.
Comment puis-je interdire l'accès en écriture de la table père. Vu que
je vais devoir modifier une application qui pour le moment utilise la
table pére, j'aimerai mettre une contrainte qui m'interdit d'écrire
dedans afin d'être sur de ne pas avoir oublié une requète quelquepart.
Troisième Question:
Existe t-il une instruction qui permette de créer une table sur le
modèle d'une autre.J'aimerai, si c'est possible, éviter de devoir créer
les tables à partir d'un CREATE TABLE.
J'aimerai avoir une partition modèle qui me servierai de base pour la
creation d'une autre partition.
C'est possible ca ?
Enfin.
Lorsqu'on a 100 partitions (qui sont donc 100 tables filles d'une autre
tables), comment fait t-on pour modifier d'un seul coup les 100
partitions. Par exemple, si je dois ajouter un trigger ou une contrainte
sur toutes les partitions ?
Merci
Etienne.
J'ai une très grosse table dans laquelle je fais des requetes sur un
champs indéxé.
J'envisage je partitionner cette table.
J'ai quelques questions tout de même avant de procéder à l'opération.
Tout d'abors est ce vraiment utile en terme de performance.
Mon interrogation n'est pas tellement sur les lectures, mais plutôt sur
les lectures et écritures concurrentes.
je passe mon temps a ajouter, modifier, supprimer et lire des
enregistrement dans cette table.
Les transactions lock les tables. Donc je suppose que plus de tables =
moins de conflit non ?
Bref. Est ce que quelqu'un aurai un retour sur l'interet du
partitionnement. Car c'est quand même assez penible à mettre en place
alors autant ne pas le faire pour rien.
Ma deuxième question est:
puisque le partitionnement dans postgreSQL est juste un usage
particulier de l'héritage.
Comment puis-je interdire l'accès en écriture de la table père. Vu que
je vais devoir modifier une application qui pour le moment utilise la
table pére, j'aimerai mettre une contrainte qui m'interdit d'écrire
dedans afin d'être sur de ne pas avoir oublié une requète quelquepart.
Troisième Question:
Existe t-il une instruction qui permette de créer une table sur le
modèle d'une autre.J'aimerai, si c'est possible, éviter de devoir créer
les tables à partir d'un CREATE TABLE.
J'aimerai avoir une partition modèle qui me servierai de base pour la
creation d'une autre partition.
C'est possible ca ?
Enfin.
Lorsqu'on a 100 partitions (qui sont donc 100 tables filles d'une autre
tables), comment fait t-on pour modifier d'un seul coup les 100
partitions. Par exemple, si je dois ajouter un trigger ou une contrainte
sur toutes les partitions ?
Merci
Etienne.
Poser une question
t'as pas droit au dirty read ? pourquoi de lock de tables ? 1 seul
client monotache ?
difficile a dire sans avoir plus d'info sur la volumetrie et les acces:
- combien de requete par secondes ?
- quelle taille de table ?
- combien de tuples retournés en moyenne ?
- quelle requete en gros ?
- combien d'insertion/délétion/update par rapport au insertions ?
Je sais pas quelle est ton application et les techno en jeu, mais le
plus souvent la solution réside dans :
- l'index
- la procédure stockée si multiples requetes lourdes pour une opération
- grouper les requetes et mettre en place un cache applicatif, réduire
le fetchsize, etc.
Souvent, le problème se traite en amont...
t'as deux solutions:
- utiliser une vue readonly
- utiliser un compte utilisateur différent dans lequel pour la table
t'as uniquement le droit de lecture...
create table as select ...
par contre, ca prend pas les index et contraintes d'intégrité diverses...
J'aimerai, si c'est possible, éviter de devoir créer
ben tu veux les créer comment ?
désolé, je connais pas assez bien les partitions... comme indiqué, j'ai
toujours trouvé des solutions en amont bien plus efficaces et moins
couteuse... de toute facon, chez moi, la BD elle s'ennuit en général
pendant que serveur applicatif mouline... en général, le problème, c'est
comment gérer le cache applicatif !
Le 04/10/2011 09:28, Etienne a écrit :
hélas le partitionnement sur PG n'est encore qu'embryonnaire, car une
bonne partie doit être fait à la main !
En effet contrairement aux solution de SQL Server ou Oracle, chacune des
partitions est en fait une table indépendant sur laquelle il faut
appliquer certaines commandes DDL sur toutes les partitions, si vous
voulez que vos différentes partitions (en faite table) aient la même
structure (colonnes, contraintes, index...).
De plus PostGreSQL ne sait pas faire de l'optimisation sémantique sans
qu'on soit obliger de lui demander... Ainsi une requête demandant des
données ne pouvant figurer dans la partition, ira quand même lire les
données de cette partition pour ne rien retourner, sauf si vous avez
pensé à mettre en place préalablement le bon indicateur (mais est-ce
pour la session ou pour le serveur ??? mystère !) :
SET constraint_exclusion = on;
Mais même dans ce cas, l'utilisation d'un partitionnement par dateheure
avec l'appel à une fonction de type CURRENT_DATE plante l'optimiseur qui
utilisera toutes les partitions...
Enfin, la gestion des espaces de stockage étant encore aussi
embryonnaire, il est impossible par exemple de pré réserver de l'espace
pour stocker les données sur les fichiers de stockage des différentes
tables de partitionnement. Il en résulte que PostGreSQL fera de
l'autogrowth, ce qui n'est pas folichon du fait de la fragmentation
physique des fichier qui en résultera !
Pour terminer il est extrêmement difficile de gérer les partitions. Par
exemple pour migrer les données d'une partition d'un espace de stockage
à l'autre , il faut :
1) démarrez une transaction au niveau SERIALIZABLE
2) créer une nouvelle table fille dans cet espace de stockage
3) migrer les données par un UPDATE
4) supprimer les données de l'ancienne table
5) ajouter les index
6) ajouter les règles
7) modifier la vue
8) terminez la transaction
je n'ai pas encore tester ce qui se passe si cette table est une
référence d'intégrité...
En comparaison, sous MS SQL Server, il suffit d'une commande :
ALTER TABLE ... SWITCH PARTITION ...
En sus on peut en une seule commande sous MS SQL Server fusionner ou
scinder une partition :
ALTER PARTITION ... SPLIT RANGE ...
ALTER PARTITION ... MERGE RANGE ...
Tout ceci limite pas mal l'intérêt du partitionnement sous PG...
J'ai d'ailleurs l'intention de poster un comparatif des solutions de
partitionnement PG et MS SQL Server dans quelques temps sur la partie
blog de mon site : http://sqlpro.developpez.com/
J'espère que ceci vous aura aidé...
A +
--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************
Oui.
Il est vrai que le concept même de partitions est un poil étrange.
Je pense qu'une base de données intelligente devrait être capable de
gérer le partitionnement toute seule sans qu'on ai besoin de le lui dire.
Il est un peu étrange de devoir de palucher à la main toute cette
segmentation alors que le SGBD devrait être capable de faire ça tout seul.
Bon dans mon cas j'ai pas trop le choix, je vais devoir m'y coller (et
pourtant ma table n'est pas très grande).
C'est une table qui contient les mails de l'entreprise où je bosse.
pour le moment il n'y a que 500.000 enregistrements et déjà j'ai
quelques problème de performances lorsque j'insère en même temps qu'un
autre user décide par exemple de déplacer 150 mails.
Les IO se mettent alors à exploser semble t-il.
Le problème ne semble arriver que si j'exécute des grosses requêtes
concurrentes sur une même table.
On m'a donc conseiller:
- de partitionner
- de séparer les champs static (sujet, contenu, ...) des champs
dymanique (is_mail_readed, idmailfolder, ...) dans deux tables séparées
afin de limiter l'impact des IO lors de la modification des champs
dynamique.
Donc ben même si c'est un gros boulot, je vais me lancer là dedans.
On vera bien:)
Etienne
Heu, là, je ne sais pas trop de quoi tu parles. je suis pas assez calé
en PostgreSQL pour comprendre.
Mais c'est pas une application monotache. Ca c'est sur.
La base contient 500.000 enregistrements.
C'est une table qui contient des mails.
Elle est assez grosse car je me sers d'un ts-vector pour faire des
recherches dans les mails.
Non c'est dejà indéxé.
Bon. je vais voir.
Ben je vais utiliser ton CREATE AS SELECT
si ca marche avec un SELECT * ca sera deja bien.
Merci.
Etienne.
ca n'a rien a voir avec PG, c'est de la BD purement généraliste: tu nous
parles de transactions et de concurrence alors qu'il n'y a pas de
concurrence ! C'est toi meme qui présuppose le cout des verrous et qui
sous entend que répartir ta données sur plusieurs tables a un intéret...
je rappelle ta question initiale:
"[...] les lectures et écritures concurrentes. je passe mon temps a
ajouter, modifier, supprimer et lire des enregistrement dans cette
table. Les transactions lock les tables. Donc je suppose que plus de
tables = moins de conflit non ? ".
Pour moi, l'intéret du partitionnement est ailleurs et il est
suffisamment rare pour qu'on ne se pose plein d'autres question
d'optimisation avant d'envisager quelque chose d'aussi lourd et complexe...
500 000 ligne c'est rien et ca mérite pas de partitionnement... une
table de 100 millions de lignes qui dépasse les 4 Go, la ca se
discute... j 'ai fait de l'indexation de texte avec 50 millions
d'enregistrement en me passant de tous ces artifices et ca tournait au
poil !
ton but c'est de faire un moteur de recherche dans des textes... une BD
c'est pas fait pour ca... il faut gérer une indexation au niveau
applicatif et utiliser le moteur relationnel pour des données
structurées et indexée proprement...
laisse tomber ton tsvector si c'est lui qui coute ! C'est un outil pour
ceux qui l'utilisent de facon accessoire.. toi c'est le coeur de métier
de ton applicatif vraissemblablement et les performance ne sont pas
suffisantes... alors travaille à optimiser cette fonctionnalité.
le partitionnement, ca va pas améliorer la fonction de recherche de ton
moteur... après si tu me dis que t'as 10 ans de mail et que tu veux
limiter la recherche aux X derniers mails ou à ceux des 30 derniers
jours, la en effet tu partitionne la donnée... ca allege le boulot pour
le SGBDR...