Plantage REINDEX par manque d'espace disque

gom · 25/02/2011 16:35:14

Bonjour,

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

J'ai un traitement automatique (dans l'ETL de Business Objects : "BO Data Integrator") qui lance les commandes SQL suivantes tous les weekends car j'alimente mon entrepôt de données le vendredi et j'ai besoin que mon entrepôt soit "propre" pour que mes utilisateurs puissent requêter via Business Objects (jusqu'au vendredi suivant : prochaine alimentation).

Script SQL :

print('<===============================================>');
print('\=== Début Instructions Exploitation');
print('  ==> Vacuum DWH');
print('<===============================================>');

$Resultat = sql('PRD_DWH', 'VACUUM ANALYZE VERBOSE');

print('<===============================================>');
print('  ==> Vacuum ODS');
print('<===============================================>');

$Resultat = sql('PRD_ODS', 'VACUUM ANALYZE VERBOSE');

print('<===============================================>');
print('  ==> Reindex DWH');
print('<===============================================>');

$Resultat = sql('PRD_DWH', 'REINDEX DATABASE "DWH"');

print('<===============================================>');
print('\=== Fin Instructions Exploitation : Ok');
print('<===============================================>');

Trace Error :

(11.7) 02-19-11 19:32:09 (E) (6120:4504) DBS-070404: |Session Job_Ordo_Exploit
                                                     SQL submitted to ODBC data source <DWH Linux> resulted in error <ERROR: could not extend relation
                                                     pg_tblspc/16395/16400/59020: wrote only 4096 of 8192 bytes at block 1700627;
                                                     Error while executing the query>. The SQL submitted is <REINDEX DATABASE "DWH">.
 (11.7) 02-19-11 19:32:09 (E) (6120:4504) RUN-050304: |Session Job_Ordo_Exploit
                                                     Function call <sql ( PRD_DWH, REINDEX DATABASE "DWH" ) > failed, due to error <70404>: <SQL submitted to ODBC data
                                                     source <DWH Linux> resulted in error <ERROR: could not extend relation pg_tblspc/16395/16400/59020: wrote only 4096 of
                                                     8192 bytes at block 1700627;
                                                     Error while executing the query>. The SQL submitted is <REINDEX DATABASE "DWH">.>.

J'ai constaté en monitorant mon serveur Linux (Redhat) que l'espace disque saturait à 282,... Go alors que j'ai bien 300 Go de disponible ?! 300 Go de dédié à mon répertoire "var/lib/postgresql/main/base".

Pourquoi mon réindexage ne continue pas jusqu'au 300 Go ? Mes Tablespaces ne sont pas censés s'étendre "jusqu'à l'infini" ?

Comment se fait-il que le REINDEX crée de l'écriture sur disque jusqu'à la fin du réindexage ? Je pensais que cela allait osciller d'un réindexage d'une table à un autre ?

Gôm qui comme vous l'aurez compris ... n'a toujours pas eu la formation PostgreSQL demandée !

Dernière modification par gom (25/02/2011 16:41:00)

daamien · 25/02/2011 19:10:59

Il faut déjà commencer par identifier de quel tablespace il s'agit :

Que donnent les requêtes suivantes :

select oid,* from pg_tablespace;
select spclocation from pg_tablespace where oid = 16395;

gom · 25/02/2011 19:20:35

Merci de me répondre !

/var/lib/postgresql/main/base/tablespaces/PRD/PRD_DWH/FACT/INDEX

Gôm

daamien · 25/02/2011 19:22:01

et c'est la partition qui est à 282Go ?

gom · 25/02/2011 19:24:44

Oui, à 282Go/300Go.

daamien · 25/02/2011 19:29:28

La taille de votre index est probablement supérieur à l'espace disponible sur le tablespace... Vous pouvez connaitre facilement cette taille avec la commande :

SELECT pg_size_pretty(pg_relation_size(votre_index));

Je ne connais pas exactement le comportement de REINDEX mais il est probable que si vous voulez réindexer un index volumineux ( disons 3 Go ) . PostgreSQL va mettre un verrou en écriture sur l'index et en créer un nouveau . Vous aurez donc besoin de 3Go d'espace libre puisque les deux index vont coexister sur le disque pendant la durée du REINDEX.

Je vois plusieurs solutions :

a/ agrandir la partition
b/ placer l'index sur une partition avec assez d'espace libre
c/ faire un DROP INDEX puis CREATE INDEX

A noter que stricto sensu, la solution c/ n'est pas équivalente à un REINDEX

http://docs.postgresql.fr/9.0/sql-reindex.html

gom · 25/02/2011 19:39:01

Malheureusement :

a/ impossible --> plus de place disque dans le SAN
b/ impossible --> même raison !

c/ C'est ce que je vais effectivement être obligé de faire

Que pensez-vous de :

DROP INDEX IF EXISTS mon_idx1;
DROP INDEX IF EXISTS mon_idx2;

VACUUM FULL ANALYSE;

CREATE INDEX mon_idx_1;
CREATE INDEX mon_idx_2;

Parce que "In fact, it is often faster to drop all indexes, VACUUM FULL, and recreate the indexes.", lu ici : http://www.postgresql.org/docs/8.4/stat … acuum.html.

Gôm

Dernière modification par gom (25/02/2011 19:39:54)

Marc Cousin · 25/02/2011 20:48:12

Sinon, ça sent les 5% de blocs réservés sur un système de fichiers EXT cette histoire

gom · 25/02/2011 21:02:22

Tu peux être plus explicite s'il te plaît.

Marc Cousin · 25/02/2011 21:54:22

Désolé. Par défaut, les systèmes de fichier ext (2,3,4) sous Linux ont 5% de blocs réservés à root. 5% de 300Go, c'est 15Go. Donc si tu as un FS de 300Go, il y en a en fait 285 d'utilisables par les utilisateurs. Ce qui expliquerait ton problème je pense.

daamien · 26/02/2011 20:40:42

Effectivement la solution à court terme consiste à détruire/recréer les index...

Par contre, si vous n'avez plus de place dans la baie SAN alors votre soucis dépasse largement le problème du REINDEX....Notamment si la partition qui héberge les WALs est saturée vous allez au devant d'un sérieux plantage.

Un disque de 500Go coute 50€, je comprends pas ce qui vous empêche d'augmenter votre volume de stockage..

Marc Cousin · 26/02/2011 21:29:16

Parce que ça coute 50€ si on achète un disque SATA chez materiel.net. Dans un SAN, c'est pas le même tarif (du tout).

daamien · 27/02/2011 22:51:17

ok ok ! Disons plutot 500€ ...

J'essaie juste de dire à gom que s'il n' a plus de place sur son SAN alors il y a des priorités plus importantes que cette histoire de ré-indexation

gom · 04/03/2011 10:35:47

Marc Cousin a écrit :

Désolé. Par défaut, les systèmes de fichier ext (2,3,4) sous Linux ont 5% de blocs réservés à root. 5% de 300Go, c'est 15Go. Donc si tu as un FS de 300Go, il y en a en fait 285 d'utilisables par les utilisateurs. Ce qui expliquerait ton problème je pense.

OK merci pour l'explication. Pour info ... http://eric.quinton.free.fr/spip.php?article36

daamien a écrit :

Effectivement la solution à court terme consiste à détruire/recréer les index...
Par contre, si vous n'avez plus de place dans la baie SAN alors votre soucis dépasse largement le problème du REINDEX.... Notamment si la partition qui héberge les WALs est saturée vous allez au devant d'un sérieux plantage.
Un disque de 500Go coute 50€, je comprends pas ce qui vous empêche d'augmenter votre volume de stockage..

Les "WALs" ? Une petite explication sur les risques ?

Sinon, est-ce qu'il existe une commande (sql ? pgsql ? curseur ?) qui permet de supprimer et recréer mes index dynamiquement ? Mon objectif est que si quelqu'un ajoute, modifie ou supprime un index sans que je sois au courant, alors mon script de maintenance prendra automatiquement en compte ces changements.

Du genre :

1/ Lecture d'une table système qui contient toutes les informations nécessaires sur l'ensemble des index actuellement présents dans mon entrepôt de données afin de créer un script "recréation de tous mes index" ;
2/ Suppression de tous mes index en générant des DROP INDEX via la table système énoncée ci-dessus ;
3/ VACUUM FULL ANALYSE;
4/ Création de mes index tout juste supprimés grâce à mon script "recréation de tous mes index" créé en 1/.

Le rêve quoi ...

Gôm

Dernière modification par gom (04/03/2011 10:36:46)

gom · 04/03/2011 11:05:16

Je viens de trouver ce script, mais je ne le comprends pas donc si vous pouviez me donner quelques explications ce serait merveilleux !

DECLARE @indexName sysname --Changed to sysname, since that's what it was
DECLARE @tableName sysname
DECLARE @dropIndexSql NVARCHAR(4000)

DECLARE tableIndexes CURSOR FOR
SELECT name,OBJECT_NAME(ID) FROM sysindexes
WHERE OBJECTPROPERTY(ID,N'IsTable') = 1 
AND OBJECTPROPERTY(ID,N'IsMSShipped') = 0
AND indid 0 --Is this right? Wouldn't this attempt to drop clustered PK?
AND indid < 255
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
OPEN tableIndexes
FETCH NEXT FROM tableIndexes INTO @indexName, @tableName
WHILE @@fetch_status = 0
BEGIN
SET @dropIndexSql = N' DROP INDEX ' + @tableName + '.'  + @indexName
EXEC sp_executesql @dropIndexSql
FETCH NEXT FROM tableIndexes INTO @indexName, @tableName
END
CLOSE tableIndexes
DEALLOCATE tableIndexes

Source : http://bytes.com/topic/sql-server/answe … s-database

Gôm

Dernière modification par gom (04/03/2011 11:07:53)

gom · 04/03/2011 11:14:17

Nouvelle découverte !

A partir de cette requête SQL je suis capable de lister toutes les tables de mon entrepôt de données qui possèdent au moins un index et donc pour lesquelles je dois supprimer et recréer le ou les index !

SELECT * 
FROM pg_tables 
WHERE schemaname not in ('information_schema', 'pg_catalog', 'public') 
and hasindexes;

Gôm

Dernière modification par gom (04/03/2011 11:29:39)

Marc Cousin · 04/03/2011 11:44:44

Les "WALs" ? Une petite explication sur les risques ?
=> Si le système de fichiers contenant les WAL est plein, le moteur s'arrête. Et impossible de le redémarrer tant qu'il n'y aura pas de place libre. Pas de corruption, mais une bonne interruption de service…

Sinon, est-ce qu'il existe une commande (sql ? pgsql ? curseur ?) qui permet de supprimer et recréer mes index dynamiquement ?
=> REINDEX table xxx; Mais je présume que ce n'est pas la réponse souhaitée

Pour le script, ça va être difficile: ce n'est pas un script PostgreSQL.

Pour ce qui est de recréer des index, il y a bien plus simple :

SELECT pg_get_indexdef(oid) 
from pg_class 
where relkind='i' and relnamespace not in 
    (select oid 
     from pg_namespace 
     where nspname in ('pg_catalog','pg_toast')
    ) 
and oid not in (select indexrelid from pg_index where indisprimary );

=> Va retourner la liste des create index à faire pour regénérer tous les index qui ne sont pas des clés primaires

Ensuite il suffit de faire un

SELECT 'DROP INDEX ' || relname || ';'
from pg_class 
where relkind='i' and relnamespace not in 
    (select oid 
     from pg_namespace 
     where nspname in ('pg_catalog','pg_toast')
    ) 
and oid not in (select indexrelid from pg_index where indisprimary );

Pour un script qui drop tous ces index.

On peut évidemment faire quelque chose du même genre pour les primary keys. C'est juste un peu plus dur (c'est à coup de pg_get_constraintdef, sur la table pg_constraint)

gom · 26/10/2012 16:07:50

Petit retour ... au final j'ai fait comme ça :

SELECT 'DROP INDEX IF EXISTS ' || nspname || '.' || relname || ';' as "DROP INDEX"
from pg_class c, pg_namespace 
where relkind='i' and relnamespace not in 
    (select oid 
     from pg_namespace 
     where nspname in ('pg_catalog','pg_toast')
    ) 
and c.oid not in (select indexrelid from pg_index where indisprimary ) 
and c.relowner = nspowner 
order by nspname;

VACUUM FULL ANALYZE;

SELECT pg_get_indexdef(oid) || ';' as "CREATE INDEX"
from pg_class 
where relkind='i' and relnamespace not in 
    (select oid 
     from pg_namespace 
     where nspname in ('pg_catalog','pg_toast')
    ) 
and oid not in (select indexrelid from pg_index where indisprimary);

Gôm

Dernière modification par gom (26/10/2012 16:08:40)

Forums PostgreSQL.fr

#1 25/02/2011 16:35:14

Plantage REINDEX par manque d'espace disque

#2 25/02/2011 19:10:59

Re : Plantage REINDEX par manque d'espace disque

#3 25/02/2011 19:20:35

Re : Plantage REINDEX par manque d'espace disque

#4 25/02/2011 19:22:01

Re : Plantage REINDEX par manque d'espace disque

#5 25/02/2011 19:24:44

Re : Plantage REINDEX par manque d'espace disque

#6 25/02/2011 19:29:28

Re : Plantage REINDEX par manque d'espace disque

#7 25/02/2011 19:39:01

Re : Plantage REINDEX par manque d'espace disque

#8 25/02/2011 20:48:12

Re : Plantage REINDEX par manque d'espace disque

#9 25/02/2011 21:02:22

Re : Plantage REINDEX par manque d'espace disque

#10 25/02/2011 21:54:22

Re : Plantage REINDEX par manque d'espace disque

#11 26/02/2011 20:40:42

Re : Plantage REINDEX par manque d'espace disque

#12 26/02/2011 21:29:16

Re : Plantage REINDEX par manque d'espace disque

#13 27/02/2011 22:51:17

Re : Plantage REINDEX par manque d'espace disque

#14 04/03/2011 10:35:47

Re : Plantage REINDEX par manque d'espace disque

#15 04/03/2011 11:05:16

Re : Plantage REINDEX par manque d'espace disque

#16 04/03/2011 11:14:17

Re : Plantage REINDEX par manque d'espace disque

#17 04/03/2011 11:44:44

Re : Plantage REINDEX par manque d'espace disque

#18 26/10/2012 16:07:50

Re : Plantage REINDEX par manque d'espace disque

Pied de page des forums