Forums PostgreSQL.fr

gom

Membre

Paralléliser le recalcul des index

Bonjour,

Je travaille sur un entrepôt de données sous PostgreSQL 8.4.4 (sous Redhat 4.1.2-46 64 bits).

Mon problème est simple à expliquer mais je ne vois clairement pas comment le résoudre !

Je fais un REINDEX d'une base de données Décisionnel chaque weekend ainsi :

REINDEX DATABASE "Mon_DWH"

Le problème : environ 42 heures d'exécution alors que je suis sensé lancer le REINDEX à 3h00 le samedi matin et les bases sont sauvegardées à froid à 20h00 le dimanche soir.

Un coup sur 2 mon REINDEX ne passe pas !

Gôm

Dernière modification par gom (26/10/2012 16:30:00)

rjuju

Administrateur

Re : Paralléliser le recalcul des index

Bonjour,
avez-vous vraiment besoin de réindexer toute la base chaque semaine ? Si vous avez des problèmes de fragmentation, vous pouvez essayer de ne réindexer qu'une partie des index. Vous pouvez sinon jouer sur la paramètre maintenance_work_mem (sur cette transaction éventuellement) afin d'accélérer le traitement.

---

Julien.
https://rjuju.github.io/

gom

Membre

Re : Paralléliser le recalcul des index

Bonjour,

Oui je suis obligé (du moins c'est ce que je pense) car mes données en tables bougent énormément chaque semaine.

Certaines tables sont entièrement vidées et réalimentées, les autres sont mises à jour (INSERT de nouvelles données et UPDATE des anciennes données).

Comment savoir quels index sont trop fragmentés et doivent être réindexés ? Peut-être est-ce la solution à mon problème, non ?

Sinon je ne peux que lancer des instructions SQL donc je ne pourrai pas modifier maitenance_work_mem à la volée.

Gôm

rjuju

Administrateur

Re : Paralléliser le recalcul des index

En 8.4, la nécessité de faire un REINDEX est en général lié au VACUUM FULL. Constatez-vous un gain de performances après ce REINDEX ? Le moyen le plus simple de voir si le reindex est efficace est de comparer la taille des index avant et après (select relname,pg_relation_size(oid) from pg_class where relkind = 'i' order by 2 desc);

De plus, les tables aussi peuvent également être fragmentées. Est-ce que l'autovacuum est activé ?

Quelle est la valeur actuelle du paramètre maintenance_work_mem ? Vous pouvez changer ce paramètre en sql. Par exemple: SET maintenance_work_mem to '500B';

---

Julien.
https://rjuju.github.io/

SQLpro

Membre

Re : Paralléliser le recalcul des index

Pour paralléliser l'indexation, le moyen le plus simple est de lancer plusieurs processus en même temps. En effet, PostGreSQL est incapable de multithreader une même requête quelle qu'elle soit y compris un CREATE INDEX ou un REBUILD, contrairement à Oracle ou SQL Server qui permettent non seulement de faire de l'indexation multithreadée, mais aussi ONLINE, c'est à dire sans bloquer les mises ou jours ou les sauvegardes... (création de l'index en parallèle de l'existant, même si mise à jour il y a).

C'est pourquoi je déconseille souvent PostGreSQL pour les bases de données de type VLDB et en particulier les gros DW !

Néanmoins, dans la 4e édition de mon livre sur SQL , j'ai donné une requête permettant d'évaluer la fragmentation des index d'une base PG...

WITH
T1 AS
(
SELECT current_setting('block_size')::numeric AS bs,
       CASE WHEN substring(v,12,3) IN ('8.0', '8.1', '8.2') 
               THEN 27 
            ELSE 23 
       END AS hdr,
       CASE WHEN v LIKE '%mingw32%' 
               THEN 8 
            ELSE 4 *
       END AS ma
FROM   (SELECT version() AS v) AS T
),
T2 AS
(
SELECT schemaname, tablename, hdr, ma, bs,
       SUM((1-null_frac)*avg_width) AS datawidth,
       MAX(null_frac) AS maxfracsum,hdr
          + (SELECT 1+count(*)/8
             FROM   pg_stats s2
             WHERE  null_frac<>0 
               AND  s2.schemaname = s.schemaname 
               AND s2.tablename = s.tablename) AS nullhdr
FROM pg_stats s, (SELECT * FROM T1) AS constants
GROUP BY schemaname, tablename, hdr, ma, bs
),
T3 AS 
(
SELECT ma,bs,schemaname,tablename,
       (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 
                                   THEN ma 
                                ELSE hdr%ma 
                           END)))::numeric AS datahdr,
       (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 
                                        THEN ma 
                                     ELSE nullhdr%ma 
                                END))) AS nullhdr2
FROM T2
),
T4 AS 
(
SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs,
       CEIL((cc.reltuples*((datahdr+ma-
            (CASE WHEN datahdr%ma=0 
                     THEN ma 
                  ELSE datahdr%ma 
             END))+nullhdr2+4))/(bs-20::float)) AS otta,
       COALESCE(c2.relname,'?') AS iname, 
       COALESCE(c2.reltuples,0) AS ituples, 
       COALESCE(c2.relpages,0) AS ipages,
       COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta 
FROM   T3
       INNER JOIN pg_class cc 
             ON cc.relname = T3.tablename
       INNER JOIN pg_namespace nn 
             ON cc.relnamespace = nn.oid 
                AND nn.nspname = T3.schemaname
       LEFT OUTER JOIN pg_index i 
            ON indrelid = cc.oid
       LEFT OUTER JOIN pg_class c2 
            ON c2.oid = i.indexrelid
)
SELECT schemaname, tablename, reltuples::bigint, relpages::bigint, otta,
       ROUND(CASE WHEN otta=0 
                     THEN 0.0 
                  ELSE T4.relpages/otta::numeric 
             END,1) AS tbloat,
       relpages::bigint - otta AS wastedpages,
       bs*(T4.relpages-otta)::bigint AS wastedbytes,
       pg_size_pretty((bs*(relpages-otta))::bigint) AS wastedsize,
       iname, ituples::bigint, ipages::bigint, iotta,
       ROUND(CASE WHEN iotta=0 OR ipages=0 
                     THEN 0.0 
                  ELSE ipages/iotta::numeric 
             END,1) AS ibloat,
       CASE WHEN ipages < iotta 
               THEN 0 
            ELSE ipages::bigint - iotta 
       END AS wastedipages,
       CASE WHEN ipages < iotta 
               THEN 0 
            ELSE bs*(ipages-iotta) 
       END AS wastedibytes,
       CASE WHEN ipages < iotta 
               THEN pg_size_pretty(0) 
            ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) 
       END AS wastedisize
FROM    T4 
WHERE   T4.relpages - otta > 0 OR ipages - iotta > 10
ORDER   BY wastedbytes DESC, wastedibytes DESC;

Elle est inspirée de celle utilisée par le superviseur Nagios à travers le projet Bucardo (check_postgres).

A +

Dernière modification par SQLpro (26/10/2012 18:36:03)

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *

Postgres.0

Membre

Re : Paralléliser le recalcul des index

Bonjour,

très clairement, en terme de perf, quel est l'interet de faire des WITH?

Dernière modification par Postgres.0 (29/10/2012 14:22:25)

gom

Membre

Re : Paralléliser le recalcul des index

En 8.4, la nécessité de faire un REINDEX est en général lié au VACUUM FULL. Constatez-vous un gain de performances après ce REINDEX ? Le moyen le plus simple de voir si le reindex est efficace est de comparer la taille des index avant et après (select relname,pg_relation_size(oid) from pg_class where relkind = 'i' order by 2 desc);

De plus, les tables aussi peuvent également être fragmentées. Est-ce que l'autovacuum est activé ?

Quelle est la valeur actuelle du paramètre maintenance_work_mem ? Vous pouvez changer ce paramètre en sql. Par exemple: SET maintenance_work_mem to '500B';

Oui je constate un gain de performance lorsque je fais un REINDEX. Le problème est que je ne peux plus faire de REINDEX DATABASE "Ma_base".

Oui l'autovacuum est activé.

show autovacuum;
> on

SHOW maintenance_work_mem;
> 512MB

512 Mo est suffisant pour un serveur disposant de 8 Go de RAM.

Gôm

rjuju

Administrateur

Re : Paralléliser le recalcul des index

Quand vous dites sauvegarde à froid, c'est une sauvegarde base de donnée arrêtée ? Si oui, le fait d'arrêter la base vide le shared_buffers, et tant que celui-ci n'est pas rempli les performances seront bien évidemment moindre. Il est bien entendu possible de sauvegarder la base à chaud.

Quel est le volume total de vos index ? (SELECT sum(pg_relation_size(oid)) FROM pg_class WHERE relkind = 'i'; )

Pour optimiser ce traitement, il faut d'abord identifier la source de contention. Si c'est le cpu, il faut effectivement essayer de lancer plusieurs REINDEX en parallèle. Il est plus probable que la contention vienne des disques, ceux-ci étant fortement sollicités lors d'un REINDEX (lecture de la table si non présent en cache, création des index sur disque et génération d'une grand quantité de wal).

Une des premières possibilités d'optimisation est d'utiliser un filesystem séparé et rapide pour le répertoire pg_xlog, afin de pouvoir paralléliser les écritures des wals et des fichiers de données.

---

Julien.
https://rjuju.github.io/

gom

Membre

Re : Paralléliser le recalcul des index

Quand vous dites sauvegarde à froid, c'est une sauvegarde base de donnée arrêtée ? Si oui, le fait d'arrêter la base vide le shared_buffers, et tant que celui-ci n'est pas rempli les performances seront bien évidemment moindre. Il est bien entendu possible de sauvegarder la base à chaud.

Quels inconvénients à sauvegarder à chaud ? Si aucun, alors puis-je recommander systématiquement des sauvegardes à chaud ?

Quel est le volume total de vos index ? (SELECT sum(pg_relation_size(oid)) FROM pg_class WHERE relkind = 'i'; )

73189769216 octets = 68.1632843 gigabytes

Pour optimiser ce traitement, il faut d'abord identifier la source de contention. Si c'est le cpu, il faut effectivement essayer de lancer plusieurs REINDEX en parallèle. Il est plus probable que la contention vienne des disques, ceux-ci étant fortement sollicités lors d'un REINDEX (lecture de la table si non présent en cache, création des index sur disque et génération d'une grand quantité de wal).

Une des premières possibilités d'optimisation est d'utiliser un filesystem séparé et rapide pour le répertoire pg_xlog, afin de pouvoir paralléliser les écritures des wals et des fichiers de données.

Comment savoir si le point de contention est le CPU ou le disque ?

Je croyais qu'il était impossible avec PostgreSQL de lancer des REINDEX est parallèle ?!

Gôm

rjuju

Administrateur

Re : Paralléliser le recalcul des index

Quels inconvénients à sauvegarder à chaud ? Si aucun, alors puis-je recommander systématiquement des sauvegardes à chaud ?

Pas vraiment d'inconvénients. Comme les fichiers seront copiés, il y aura une surcharge des IO, mais pas pire qu'un arrêt complet. De plus vous pouvez limiter cette surcharge si besoin, quitte à avoir  une copie plus longue. Cela nécessite par contre l'archivage des journaux de transaction (archive_mode à on et archive_command configuré). Voir http://docs.postgresqlfr.org/9.1/contin … iving.html pour plus de détails.

73189769216 octets = 68.1632843 gigabytes

Vous pouvez donc raisonnablement penser que cela génère un minimum de 136Go de données (index + wal). Comme je vous l'ai dit, mettre des disques plus rapides, mettre plus de disques en raid 10 par exemple, mettre les wal sur une grappe de disque séparée augmenterait les performances.

Comment savoir si le point de contention est le CPU ou le disque ?

Je croyais qu'il était impossible avec PostgreSQL de lancer des REINDEX est parallèle ?! neutral

Monitorez les iowait et l'activité cpu durant le reindex pour voir lequel bride l'opéation.

Postgres n'est pas multithreadé. L'inconvénient est qu'une requête ne s'exécute que sur un seul cœur. Cela a d'autres avantages, notamment une meilleur stabilité. Ainsi un REINDEX ne se lancera pas sur plusieurs processeurs, mais vous pouvez lancer simultanément plusieurs REINDEX de tables/index différents afin d'utiliser tous les processeurs, en supposant que ce soit lui le point de contention.

---

Julien.
https://rjuju.github.io/