Vous n'êtes pas identifié(e).
Pages : 1
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)
Hors ligne
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/
Hors ligne
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
Hors ligne
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/
Hors ligne
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 * * * * *
Hors ligne
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)
Hors ligne
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;
> onSHOW maintenance_work_mem;
> 512MB512 Mo est suffisant pour un serveur disposant de 8 Go de RAM.
Gôm
Hors ligne
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/
Hors ligne
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
Hors ligne
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/
Hors ligne
Pages : 1