Ok merci.
Bah ce que je vais faire, c'est repartir sur une base vide avec cette nouvelle politique de delete/vacuum/analyse et voir si je reproduit le problème.

Sinon il est peut être possible que je mette le vacuum sur la table en question en "verbose" et analyser ce qu'il dit ? Ca donnera peut être des pistes.

Bonjour,

Je relance un peu mon post

Ca marche un peu près bien le fait d'insérer 40 000 enregistrements toutes les 5 minutes et faire aussitôt après un ANALYSE sur la table
(A condition d'avoir default_statistics_target à 10 sinon l'ANALYSE est trop long).
Ma purge est 1 fois par heure: DELETE de 480 000 enregistrements suivit d'un VACUUM mais pas besoin d'ANALYSE puisqu'il est fait toutes les 5 minutes.

Mais maintenant je suis confronté à un nouveau problème: pendant un DELETE ou un VACUUM, je peux très bien être en train de faire un ANALYSE vu qu'il a lieu toutes les 5 minutes.
Ce qui me donne des temps de traitement loin d'être constant et je ne maitrise pas la non parallélisation de ces commandes.

Du coup j'ai changé de technique:

J'insère 40 000 enregistrements toutes les 5 minutes et aussitôt après un ANALYSE, donc comme avant.
Par contre, Une fois tous les douze cycles, aussitot après l'insert et avant l'analyse, je fais un DELETE des 480 000 enregistrements les plus anciens puis VACUUM sur la table.
Comme ca, je suis sur que le DELETE ou VACUUM n'a pas lieu pendant l'INSERT ou l'ANALYSE.

Ca a l'air de marcher pas trop mal mais je suis confronté à un nouveau problème.

L'insert de 40 000 enregistrements dure 18s en général dans ma table de 20 millions.
Quand a lieu le DELETE puis VACUUM et enfin ANALYSE, l'insert du cycle d'apres dure 267s
et le cycle suivant, l'insert revient à 18s.

J'ai mit un EXPLAIN ANALYSE sur la requete en question car c'est un INSERT d'un SELECT mais le résultat est le même que ce soit pour la requete de 18s
ou celle de 267s.

Requete de 18s
Subquery Scan "*SELECT*"  (cost=7200.00..7690.00 rows=4000 width=400) (actual time=320.344..692.228 rows=40000 loops=1)
  ->  HashAggregate  (cost=7200.00..7640.00 rows=4000 width=392) (actual time=320.332..596.716 rows=40000 loops=1)
        ->  Seq Scan on table_minute_1102221930  (cost=0.00..2400.00 rows=40000 width=392) (actual time=0.014..24.270 rows=40000 loops=1)
Total runtime: 17921.476 ms

Requete de 267s
Subquery Scan "*SELECT*"  (cost=7200.00..7690.00 rows=4000 width=400) (actual time=294.598..714.618 rows=40000 loops=1)
  ->  HashAggregate  (cost=7200.00..7640.00 rows=4000 width=392) (actual time=294.586..592.264 rows=40000 loops=1)
        ->  Seq Scan on table_minute_1102221935  (cost=0.00..2400.00 rows=40000 width=392) (actual time=0.014..22.726 rows=40000 loops=1)
Total runtime: 267104.377 ms
Delete result:
Insert result: 0
Execution time: 267

Donc j'imagine que c'est l'INSERT qui pose problème.

Une idée ?

Ah bah ca, je voudrais bien être en 9.0 mais le soft a plein de limitations qui font qu'on est bloqué en 8.2, bref, c'est une autre histoire.

Donc la je suis presque à 1 journée de traitement, mes tables de données sont à 10 760 000.
Et l'analyse prend de plus en plus de temps: 38s pour analyser une table toutes les 5 minutes, ca commence à devenir critique.
Je vais jamais réussir à tenir 2 jours d'historique (30 000 000 d'enregistrements dans mes tables)

Il y a pas une technique pour booster un peu l'analyse ou lui dire d'analyser que les enregistrements les plus récents.
ou alors je diminue le default_statistics_target ? mais je sais pas si le gain sera significatif ?

Le choix de refaire le schéma de base avec du partitionnement devient de plus en plus évident mais pour l'instant je suis bloqué avec le schéma actuel

Alors mon paramètre default_statistics_target est déja à 100.
Pour l'index composé, je peux pas car je n'ai pas seulement subtotal comme colonne sur laquelle je peux faire des filtres mais plein d'autres.

Donc la j'ai relancé mon application. Et au lieu de faire un ANALYSE sur toute la base une fois par heure.
Je fais un ANALYSE sur mes tables table1 et table2 juste après l'insertion des 40 000 lignes qui a lieu toutes les 5 minutes.

Et pour l'instant, ca a l'air de plutôt pas mal fonctionner

Depuis que j'ai relancé avec cette modif, j'ai des temps de traitement complètement bizarre alors que j'ai mit un explain analyse que sur la requete récalcitrante.
Bref. J'ai quand même pu isoler dans mes logs la requête qui s'exécute rapidement et 5 minutes après, exactement la même mais qui prend 9mn.
Et en effet, on voit bien que le plan a changé:

Alors la requete:

INSERT INTO table3
(id_alarm,critical_level,acknowledgement,ta,ta_value,up_down,na,na_value,alarm_type)
SELECT
'sdas.0983a5406846f981070700ed15e818cd',
'major',
'0',
'minute',
1102081945,
'up',
'na',
t0.na,
'static'
FROM table1 t0, table2 t1
WHERE t0.na = t1.na
AND t0.minute = t1.minute
AND  t0.minute = 1102081945
AND  t0.na IS NOT NULL
AND t1.subtotal<50
AND t0.total>=100 ORDER BY na

Version rapide

Subquery Scan "*SELECT*"  (cost=2808.17..2808.29 rows=6 width=36) (actual time=16.787..16.787 rows=0 loops=1)
  ->  Sort  (cost=2808.17..2808.18 rows=6 width=46) (actual time=16.784..16.784 rows=0 loops=1)
        Sort Key: t0.na
        ->  Nested Loop  (cost=4.57..2808.09 rows=6 width=46) (actual time=16.768..16.768 rows=0 loops=1)
              ->  Index Scan using idx_table2_minute on table2 t1  (cost=0.00..2527.91 rows=5 width=50) (actual time=2.362..16.453 rows=3 loops=1)
                    Index Cond: (1102081945 = "minute")
                    Filter: (subtotal < 50::double precision)
              ->  Bitmap Heap Scan on table1 t0  (cost=4.57..55.87 rows=13 width=50) (actual time=0.100..0.100 rows=0 loops=3)
                    Recheck Cond: (t0.na = t1.na)
                    Filter: (("minute" = 1102081945) AND (total >= 100::double precision))
                    ->  Bitmap Index Scan on idx_table1_na  (cost=0.00..4.57 rows=13 width=0) (actual time=0.054..0.054 rows=13 loops=3)
                          Index Cond: (t0.na = t1.na)
Total runtime: 16.921 ms

Version lente

Subquery Scan "*SELECT*"  (cost=16.70..16.72 rows=1 width=36) (actual time=524459.648..524459.648 rows=0 loops=1)
  ->  Sort  (cost=16.70..16.70 rows=1 width=46) (actual time=524459.644..524459.644 rows=0 loops=1)
        Sort Key: t0.na
        ->  Nested Loop  (cost=0.00..16.69 rows=1 width=46) (actual time=524459.620..524459.620 rows=0 loops=1)
              Join Filter: (t0.na = t1.na)
              ->  Index Scan using idx_table1_minute on table1 t0  (cost=0.00..8.34 rows=1 width=50) (actual time=0.022..164.323 rows=39988 loops=1)
                    Index Cond: ("minute" = 1102081950)
                    Filter: ((na IS NOT NULL) AND (total >= 100::double precision))
              ->  Index Scan using idx_table2_minute on table2 t1  (cost=0.00..8.34 rows=1 width=50) (actual time=1.014..13.107 rows=1 loops=39988)
                    Index Cond: (1102081950 = "minute")
                    Filter: (subtotal < 50::double precision)
Total runtime: 524461.695 ms

Bon j'ai suivi une fausse piste, je recommence mes investigations à 0.

En espérant etre le plus compréhensible possible:

Je démarre mon application base vide.
j'insere 40 000 enregistrements toutes les 5 minutes dans table1 et table2.
Ce qui fait qu'au bout d'1 heure, j'ai environ 480 000 enregistrements dans ces tables.

Toutes les 5 minutes, je fais également une requete (qu'on va appeler requete1) de calcul sur ces tables en les joignant (insert d'un select join)
ce qui m'insere environ 1000 enregistrements dans une autre table qu'on va appeler table3

Toutes les heures, je fais un ANALYSE sur toute la base de données pour mettre à jour les statistiques.
Cette commande prend environ 25s

Pendant presque 2 heures, ma requete1 marche sans soucis et s'execute en moins d'1 seconde.

Juste apres (pas pendant) le 2ème ANALYSE qui prend environ 25s encore, ma requete1 prend cette fois 9mn sans raison particulière.

Pendant qu'elle a prit 9mn, j'ai observé le nombre de lock sur la base avec la commande suivante
check_postgres.pl --action=locks --dbname=mabase --warning=1 --critical=5
que j'ai lancé toutes les minutes:

POSTGRES_LOCKS WARNING: DB "mabase" total locks: 17 | time=0.01  'mabase.total'=17;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 17 | time=0.00  'mabase.total'=17;10;20
POSTGRES_LOCKS CRITICAL: DB "mabase" total locks: 28 | time=0.00  'mabase.total'=28;10;20
POSTGRES_LOCKS CRITICAL: DB "mabase" total locks: 24 | time=0.00  'mabase.total'=24;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 18 | time=0.01  'mabase.total'=18;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 18 | time=0.01  'mabase.total'=18;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 18 | time=0.00  'mabase.total'=18;10;20
POSTGRES_LOCKS CRITICAL: DB "mabase" total locks: 28 | time=0.01  'mabase.total'=28;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 18 | time=0.00  'mabase.total'=18;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 18 | time=0.01  'mabase.total'=18;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 17 | time=0.01  'mabase.total'=17;10;20

Merci pour vos réponses
Gleu, c'est exactement le genre de tip que je cherchais mais je suis en PostgreSQL 8.2 et je n'ai apparemment pas ce module.

Plus d'infos sur la requete:

INSERT INTO table2 (id_alarm,critical_level,acknowledgement,ta,ta_value,up_down,na,na_value,alarm_type)
SELECT
'sdas.0983a5406846f981070700ed15e818cd',
'major',
'0',
'minute',
1102031950,
'up',
'sgsnrncipggsnip',
t0.na,
'static'
FROM table0 t0, table1 t1
WHERE t0.na = t1.na
AND t0.minute = t1.minute
AND t0.minute = 1102031950
AND t0.na IS NOT NULL
AND t1.subtotal<50
AND t0.total>=100 ORDER BY na

Avec t0.na et t1.na indexé (champ text), t0.minute et t1.minute indexé (champ text).
t1.subtotal et t0.total sont de type real et non indexé.
Et table2 (18 champs) qui va accueillir les inserts, il y a 10 index dont certains sur plusieurs champs en même temps. (Sic, ce n'est pas moi qui a conçu le modèle de données...)

Plus d'info sur moi:

Je suis le développeur de l'application et je travaille sur une base de test,
je stress l'application (un maximum de données à traiter) pour valider sa tenue en charge.

La requete que j'ai lancé dans pgadmin est un copier coller de la requête issu des logs de l'application.

Un peu plus d'infos sur l'application:

Une purge des données est lancé toutes les heures et un vacuum puis analyse est effectué.
La purge ne fonctionne qu'à partir de 2 jours (taille de l'historique)

Mon problème survenant dés la deuxième heure environ, cela signifie qu'aucun vacuum n'a été effectué
mais seulement 1 ou 2 ANALYSE complet sur toute la base.

En partant du principe que ca peut être un élement exterieur qui perturbre ma requete, j'ai décidé de désactiver la purge.
(Tache dans la crontab)
J'ai lancé l'application tout le week end et je n'ai pas eu le problème.

Donc maintenant j'ai 2 pistes:

Soit c'est le ANALYSE l'élément perturbateur.
Soit c'est la requete de purge qui travaille sur les memes tables que la requete l'élément perturbateur.

Je relance mon application avec la tache de purge et les vacuums / analyse qui vont se déclencher mais sans la requête de purge qui travaille sur les memes tables que ma requete.

J'ai l'impression qu'il y a une histoire de lock sur les tables.

Il y a pas possibilité de rendre PostgreSQL encore plus verbeux, par exemple DEBUG5 pour savoir ce qu'il est en train de faire ou ce qui le gène pendant le problème ?

A part pgfouine et le module auto-explain que je ne dispose pas, vous me conseillez quoi d'autre comme outil ?

Ok donc en gros j'ai mit en place un script de purge lancé toutes les heures par la crontab.

Si le DELETE du script a supprimé quelque chose, il lance un "VACUUM;" et sinon DELETE ou pas, il lance un "ANALYSE;" juste après

Merci pour votre aide.

Plus qu'à voir comment ca évolue dans le temps.

Merci pour vos réponses.

Alors j'ai relancé avec les paramètres suivants:

max_fsm_pages = 1000000  au lieu de 153600
max_fsm_relations = 2000 au lieu de 6000
et j'ai programmé un VACUUM ANALYSE apres la purge.

Pour Marc, j'ai la version 8.2.4 et l'autovacuum doit être à OFF car j'ai cette ligne dans le fichier conf:
#autovacuum = off

Pour Guillaume, le partitionnement logiciel est une super solution mais c'est trop tard pour l'envisager vu l'avancement de mon projet

Sinon pour info, ma base vide prend 14mo sur le disque, il y a 1122 objets et le sum(relpage) renvoie 1140

Donc la je viens de relancer mon application, à voir comment ca va évoluer dans les jours à venir.

Question: Quand on fait que des inserts sans jamais d'update ou delete sur une table, il est nécessaire malgré tout de lancer un VACUUM ANALYSE ?