Technique - général | Problème de partitionnement

Par Psycho le 01/11/2007 - 00:30

Bonjour,

Je créé actuellement un système représentant les cases d'un monde. Selon nos calculs, la table devrait contenir plus de 25.000.000 de records au départ (pour quelques 4 GO) et grandir petit à petit.
Après avoir regarder le forum, j'ai vu que la solution du partionnement semblait être une bonne chose à faire.

J'ai donc découper ma carte en 25 tables différents (plus facile, carte 5000 x 5000 découpé en 25 zone de 1000 x 1000, en gros, 1.000.000 de record par table).

J'ai voulu faire un peu des tests sur ca, j'ai donc remplit mes tables avec les deux façon différente.
Une solution avec partitionnement et une sans.

1) Première constatation, le replissage de la partie avec partitionnement m'as pris... beaucoup beaucoup plus de temps (12h contre 4h environ).
A la rigueur, le problème ne me gène pas plus que ca car les tables seront accédé principalement par SELECT.

2) Ensuite j'ai fais mes test de SELECT sur les table... et fort malheureusement, le SELECT sur les tables partitionnés prend plus de temps aussi dans la plupart des cas.

J'ai essayer de faire un EXPLAIN, et il semble que la clause constraint_exception ne soit pas pris en compte (j'ai mis ON dans le fichier postgres.conf et j'ai même vérifier en faisant SET constraint_exception = on avant ma requête).
Je suppose que si j'arrive à ce qu'il prenne en compte ce paramètre, ma solution avec partitionnement sera meilleur, mais pour l'instant... non.

(au passage, si vous avez des conseils de configuration pour ce genre de donnée, taille des buffers et autre, j'avoue ne pas m'y connaître encore beaucoup là dedans, je monte en compétence doucement...)

Les test fait, avec deux requêtes :
EXPLAIN ANALYZE SELECT *
FROM monde
WHERE x BETWEEN -1600 AND -1400
AND y BETWEEN 400 AND 600;
(cette requête va obliger le système avec partitionnement à aller chercher dans deux tables différentes)

Sans partitionnement :
1ère exécution : Total runtime: 40537.848 ms
2ième exécution : Total runtime: 191.484 ms

Avec partitionnement :
1ère exécution : Total runtime: 32166.446 ms
2ième exécution : Total runtime: 230.970 ms

EXPLAIN ANALYZE SELECT * FROM monde_test
WHERE x BETWEEN -1800 AND -1600
AND y BETWEEN -200 AND 0;
(la rechercher sera uniquement dans une seul table)

Sans partitionnement :
1ère exécution : Total runtime : 40735.029 ms (un petit peu moins en fait, j'ai pris le mauvais chiffre)
2ième exécution : Total runtime: 192.634 ms

Avec partitionnement :
1ère exécution : Total runtime: 32196.732 ms
2ième exécution : Total runtime: 216.911 ms

Le EXPLAIN ANALYZE (pour la première requete)

Sans partitionnement :
QUERY PLAN

Bitmap Heap Scan on monde_test (cost=37841.43..131175.92 rows=40494 width=18) (actual time=173.803..186.790 rows=40401 loops=1)
Recheck Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Bitmap Index Scan on monde_test_pkey (cost=0.00..37831.30 rows=40494 width=0) (actual time=173.659..173.659 rows=40401 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))

Total runtime: 191.484 ms

Avec partitionnement :
QUERY PLAN

Result (cost=0.00..78658.27 rows=35415 width=18) (actual time=0.212..224.933 rows=40401 loops=1)
-> Append (cost=0.00..78658.27 rows=35415 width=18) (actual time=0.210..205.276 rows=40401 loops=1)
-> Index Scan using monde_pkey on monde (cost=0.00..36677.99 rows=15754 width=18) (actual time=0.210..59.137 rows=20100 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_m2p2_pkey on monde_m2p2 monde (cost=0.00..3388.55 rows=1 width=18) (actual time=16.332..16.332 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_m1p2_pkey on monde_m1p2 monde (cost=0.00..3217.28 rows=1 width=18) (actual time=17.147..17.147 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_c0p2_pkey on monde_c0p2 monde (cost=0.00..8.46 rows=1 width=18) (actual time=0.083..0.083 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_p1p2_pkey on monde_p1p2 monde (cost=0.00..8.46 rows=1 width=18) (actual time=0.062..0.062 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_p2p2_pkey on monde_p2p2 monde (cost=0.00..8.46 rows=1 width=18) (actual time=0.060..0.060 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_m2p1_pkey on monde_m2p1 monde (cost=0.00..8.38 rows=1 width=18) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_m1p1_pkey on monde_m1p1 monde (cost=0.00..8.38 rows=1 width=18) (actual time=0.018..0.018 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Bitmap Heap Scan on monde_c0p1 monde (cost=171.52..267.32 rows=25 width=18) (actual time=0.099..0.099 rows=0 loops=1)
Recheck Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Bitmap Index Scan on monde_c0p1_pkey (cost=0.00..171.51 rows=25 width=0) (actual time=0.097..0.097 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_p1p1_pkey on monde_p1p1 monde (cost=0.00..8.38 rows=1 width=18) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_p2p1_pkey on monde_p2p1 monde (cost=0.00..8.38 rows=1 width=18) (actual time=0.018..0.018 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Bitmap Heap Scan on monde_m2c0 monde (cost=2858.69..9744.68 rows=8989 width=18) (actual time=15.991..19.954 rows=10100 loops=1)
Recheck Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Bitmap Index Scan on monde_m2c0_pkey (cost=0.00..2856.44 rows=8989 width=0) (actual time=15.909..15.909 rows=10100 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Bitmap Heap Scan on monde_m1c0 monde (cost=3385.20..10203.41 rows=10625 width=18) (actual time=16.620..20.841 rows=10201 loops=1)
Recheck Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Bitmap Index Scan on monde_m1c0_pkey (cost=0.00..3382.55 rows=10625 width=0) (actual time=16.566..16.566 rows=10201 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_c0c0_pkey on monde_c0c0 monde (cost=0.00..8.46 rows=1 width=18) (actual time=0.075..0.075 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_p1c0_pkey on monde_p1c0 monde (cost=0.00..8.46 rows=1 width=18) (actual time=0.062..0.062 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_p2c0_pkey on monde_p2c0 monde (cost=0.00..8.46 rows=1 width=18) (actual time=0.062..0.062 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_m2m1_pkey on monde_m2m1 monde (cost=0.00..3423.90 rows=1 width=18) (actual time=15.608..15.608 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_m1m1_pkey on monde_m1m1 monde (cost=0.00..3670.22 rows=1 width=18) (actual time=14.538..14.538 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_c0m1_pkey on monde_c0m1 monde (cost=0.00..8.46 rows=1 width=18) (actual time=0.076..0.076 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_p1m1_pkey on monde_p1m1 monde (cost=0.00..8.46 rows=1 width=18) (actual time=0.061..0.061 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_p2m1_pkey on monde_p2m1 monde (cost=0.00..8.46 rows=1 width=18) (actual time=0.060..0.060 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_m2m2_pkey on monde_m2m2 monde (cost=0.00..3309.03 rows=1 width=18) (actual time=15.785..15.785 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_m1m2_pkey on monde_m1m2 monde (cost=0.00..4620.84 rows=1 width=18) (actual time=16.625..16.625 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_c0m2_pkey on monde_c0m2 monde (cost=0.00..8.50 rows=1 width=18) (actual time=0.100..0.100 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_p1m2_pkey on monde_p1m2 monde (cost=0.00..8.50 rows=1 width=18) (actual time=0.074..0.074 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))
-> Index Scan using monde_p2m2_pkey on monde_p2m2 monde (cost=0.00..8.38 rows=1 width=18) (actual time=0.023..0.023 rows=0 loops=1)
Index Cond: ((x >= -1600) AND (x <= -1400) AND (y >= 400) AND (y <= 600))

[ sujet précédent | sujet suivant ]

Il faudrait qu'on ait le sch

Guillaume Lelarge/ = 4 Novembre, 2007 - 10:18

Il faudrait qu'on ait le schéma de la base, ou au moins des tables impactées par la pemière requête pour pouvoir vous aider.

--
Guillaume Lelarge.
Administrateur de bases de données
http://dalibo.com - http://dalibo.org

[ Vous devez vous connecter pour poster des commentaires ]

Description des tables

Psycho/ = 4 Novembre, 2007 - 18:23

TABLE MONDE (x,y,z, id_type_case, ponderation, temperature, humidite, altitude);
Clé primaire (x,y,z)
Contrainte X entre -2500 et 2500 et y entre -2500 et 2500
Clé étrangère sur id_type_case (non utilisé dans mes requêtes)

Après, découpage de cette table en 25 tables ayant pour contrainte à chaque fois environ 1000 x 1000 cases.
Exemple, la table du centre MONDE_c0c0 aura comme contrainte X entre -500 et 500 (pareil pour Y).

(voir image pour plus de précision : Image explication)

J'ai donc une contrainte pour chaque table et une règle par table sur insertion sur la table MONDE qui effectue l'insertion dans la bonne "sous-table" en fonction d'une contrainte.
La contrainte étant exactement la même que celle de la table.
(j'ai donc 25 règles également)

Au passage, c'est pour le partitionnement, sans partitionnement c'est tout dans la table MONDE.

---

Les tables impactées par la première requêtes sont donc :
MONDE_m2c0, MONDE_m1c0, MONDE_m1p1 et MONDE_m2p1

Et je m'aperçoit que je ne comprend pas cette ligne :
-> Bitmap Heap Scan on monde_c0p1 monde (cost=171.52..267.32 rows=25 width=18) (actual time=0.099..0.099 rows=0 loops=1)
Je comprend pas pourquoi ca va chercher dans cette table.
Et je ne vois pas trop d'accès à MONDE_m1p1 et MONDE_m2p1

Table impactée par la deuxième requête :
MONDE_m2c0

[ Vous devez vous connecter pour poster des commentaires ]

Relance

Psycho/ = 12 Novembre, 2007 - 16:06

Désolé d'insister mais je n'ai toujours pas résolu mon problème, est-ce qu'ils vous manquent des informations pour "diagnostiquer" ?

[ Vous devez vous connecter pour poster des commentaires ]

Je ne compreds pas tout..

Jean-Paul Argudo/ = 13 Novembre, 2007 - 15:14

Bonjour,

Plusieurs questions me viennent à l'esprit:

La table maître (ou mère) ne doit contenir que:
- les colonnes de la table
- les rules d'insertion dans les bonnes sous-tables
C'est à dire qu'absolument *tout* le reste *doit* se trouver sur les tables "filles" (index, clés primaires, étrangères, contraintes).
Est-ce bien le cas?
que donne un select count(*) from only monde? Cette requête retourne le nombre d'enregistrements éventuellement contenus (à tort!: soit on ne l'a pas vidée, soit les rules sont défaillantes et ne prévoient pas tous les cas...) dans la table mère.
il s'agit de constraint_exclusion.
Pour être absolument certain que vous avez le bon paramétrage, merci de requêter directement pg_settings comme suit:
select setting from pg_settings
where name='constraint_exclusion';
Pouvez-vous m'assurer que vous aviez bien on en résultat à cette requête?
Avez-vous lancé un ANALYZE suite à votre partitionnement pour mettre à jour les "données" de l'optimiseur? Si non, faites-le, et relancez vos tests.
Il se peut que ces contre-performances soient explicables par un mauvais paramétrage de PostgreSQL. Merci de nous donner des informations essentielles comme:
- la version de PostgreSQL utilisée
- le système d'exploitation utilisé
- tous les paramètres non commentés de votre fichier postgresql.conf
Enfin, il me faudrait le code complet SQL de la table MONDE ainsi que d'une sous-table de MONDE. Vous pouvez obtenir cela très facilement avec la commande:
pg_dump --schema-only mabase > monscript.sql.
Editez ensuite monscript.sql pour isoler les ordres SQL dont j'ai besoin pour vous répondre en détails.(soit create table, index, sequences, rules, triggers, contraintes, etc..)

Merci :)

--
Jean-Paul ARGUDO
http://dalibo.com | http://dalibo.org

[ Vous devez vous connecter pour poster des commentaires ]

Nous sommes bien d'accord sur

Psycho/ = 21 Novembre, 2007 - 19:42

Nous sommes bien d'accord sur les contraintes, clés, etc...

Je me suis refait une base un peu plus propre que je suis en train de remplir à nouveau (j'ai enlever les clés étrangère et contrainte de table pour accélérer les insertions)

Il faut que je refasse des tests

Voilà une partie du schema de la base : Fichier SQL

- La table monde est bien vide, aucun problème
- contraint_exclusion est bien à On

- Pour ce qui est du ANAlYZE, non je ne l'avais pas fait, j'avais fait un VACUUM. J'avoue avoir des problèmes pour bien comprendre la différence entre les deux.
Au passage, sur ma nouvelle base, le ANALYZE me donne pas des résultats incorrect de nombre d'enregistrement tandis ce que le VACUUM si.
(mais problème avec le VACUUM :
NOTICE: number of page slots needed (191776) exceeds max_fsm_pages (20000)
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 191776.
VACUUM)

- Pour la configuration de Postgres, j'utilise un package d'installation : Entropy
Et j'avoue ne pas avoir (encore) les connaissances suffisante pour modifier la configuration...

Je vous transmet les configs actuelle :

max_connections = 20 # (change requires restart) <- il va falloir que je monte cette valeur pour plus tard
shared_buffers = 2400kB # min 128kB or max_connections*16kB
max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
constraint_exclusion = on
datestyle = 'iso, mdy'
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting

- Système d'exploitation, je fais mes test sous Mac Os X 10.4 mais le but final est sur un Mac Os X Server 10.4 (je ne peut pas me permettre de faire les test sur le serveur de production).
- Postgres 8.2.3

Le serveur de production n'est pas une grosse machines, ce n'est pas un serveur à la base d'ailleurs
Vitesse du processeur 733 MHz
Modèle de lordinateur Power Mac G4
Mémoire 1 Go
Nombre de processeurs 1
Disque dur 60 Go (1 disque)

De toute façon, vu les quantités de données (+de 25 millions de records), cela vous semble t'il judicieux de faire un partitionnement ?
Il y a t'il des configurations un peu particulière que je doivent faire ?

Autre petite question annexe,
Si je monte mon nombre de connexion à 80 ou 100, faut il que je fasse attention à certaine choses ?
Sachant que je vais désormais utilisé les connexions persistantes (ce que je ne fesait pas avant) donc je ne sais pas si c'est utile.

Merci à vous.

[ Vous devez vous connecter pour poster des commentaires ]

Ouverture de session

Navigation

Contactez-nous

Liens

Recherche

Sujets du forum

Sujets actifs

Nouveaux sujets:

Accéder aux archives

Syndication

Sondage

Problème de partitionnement

Technique - général | Problème de partitionnement

Options d'affichage des commentaires

Il faudrait qu'on ait le sch

Description des tables

Relance

Je ne compreds pas tout..

Nous sommes bien d'accord sur