Vous n'êtes pas identifié(e).
Bonjour,
Je rencontre un problème de temps et plan d'exécution de 2 mêmes requêtes sur 2 serveur différent. Le jeux de données est sensiblement le même sur les 2 serveur.
Les 2 postgresql.conf sont exactement les même :
# - Memory -
shared_buffers = 400MB # min 128kB or max_connections*16kB (change requires restart)
work_mem = 32MB # min 64kB
maintenance_work_mem = 128MB # min 1MB
# - Free Space Map -
max_fsm_pages = 2000000 # (change requires restart)
max_fsm_relations = 10000 # (change requires restart)
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Checkpoints -
checkpoint_segments = 5 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_warning = 30s # 0 is off
# - Archiving -
archive_command = 'cp "%p" /bases/postgresql/iha/var/journaux/"%f"' # command to use to archive a logfile segment
archive_timeout = 7200
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
effective_cache_size = 4384596 # typically 8KB each
random_page_cost = 4 # units are one sequential page fetch cost
Seul la quantité de ram est différente :
Sur mon serveur de test :
# free
total used free shared buffers cached
Mem: 16627288 11093916 5533372 0 492412 5081984
-/+ buffers/cache: 5519520 11107768
Swap: 4194288 116160 4078128
sur mon serveur de prod :
free
total used free shared buffers cached
Mem: 8306104 7932408 373696 0 183856 6854604
-/+ buffers/cache: 893948 7412156
Swap: 4194288 4 4194284
Explain de la requête sur le serveur de test :
iha=# EXPLAIN SELECT 1 FROM omc_cmd_entete cmd, omc_cmd_lignes ligne LEFT OUTER JOIN omc_fac_dist fac on fac.id_po_distribution = ligne.id_po_distribution WHERE cmd.po_header_id=21169 AND ligne.po_header_id=cmd.po_header_id ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=18.79..67300.53 rows=85 width=0)
-> Nested Loop (cost=0.00..110.89 rows=25 width=11)
-> Index Scan using idx_cmd_entete_header_id on omc_cmd_entete cmd (cost=0.00..8.28 rows=1 width=11)
Index Cond: (po_header_id = 21169::numeric)
-> Index Scan using idx_cmd_lignes_header_id on omc_cmd_lignes ligne (cost=0.00..102.35 rows=25 width=22)
Index Cond: (po_header_id = 21169::numeric)
-> Bitmap Heap Scan on omc_fac_dist fac (cost=18.79..2677.70 rows=791 width=11)
Recheck Cond: (fac.id_po_distribution = ligne.id_po_distribution)
-> Bitmap Index Scan on idx_fac_dist_po_distribution (cost=0.00..18.59 rows=791 width=0)
Index Cond: (fac.id_po_distribution = ligne.id_po_distribution)
(10 rows)
Temps de réponse = 0.2 s
Explain de la requête sur le serveur de prod:
iha=# EXPLAIN SELECT 1 FROM omc_cmd_entete cmd, omc_cmd_lignes ligne LEFT OUTER JOIN omc_fac_dist fac on fac.id_po_distribution = ligne.id_po_distribution WHERE cmd.po_header_id=21169 AND ligne.po_header_id=cmd.po_header_id;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=225.56..141542.25 rows=96 width=0)
-> Index Scan using idx_cmd_entete_header_id on omc_cmd_entete cmd (cost=0.00..8.29 rows=1 width=11)
Index Cond: (po_header_id = 21169::numeric)
-> Merge Right Join (cost=225.56..141533.00 rows=96 width=11)
Merge Cond: (fac.id_po_distribution = ligne.id_po_distribution)
-> Index Scan using idx_fac_dist_po_distribution on omc_fac_dist fac (cost=0.00..512728.56 rows=3499079 width=11)
-> Sort (cost=225.56..225.70 rows=56 width=22)
Sort Key: ligne.id_po_distribution
-> Index Scan using idx_cmd_lignes_header_id on omc_cmd_lignes ligne (cost=0.00..223.94 rows=56 width=22)
Index Cond: (po_header_id = 21169::numeric)
(10 rows)
Temps de réponse = 1.4 s !
J'avoue avoir un peu de mal à comprendre l'EXPLAIN. Pourquoi ne sont-il pas identique ?
Le problème ne viendrait-il pas du paramètre "effective_cache_size" ? Si oui, comment le dimensionner au mieux sur mon serveur de prod au vu de l'EXPLAIN ?
Merci d'avance
Hors ligne
Le plan du haut est meilleur, si vos données sont en cache. Celui du bas si les données n'y sont pas.
Pouvez vous redonner les explain, mais avec l'option ANALYZE ? => EXPLAIN ANALYZE SELECT… au lieu de EXPLAIN SELECT
Pour le effective_cache_size, dimensionnez le au 2/3 de la ram de votre serveur, s'il est dédié à PostgreSQL. Sinon, cela dépend de la quantité de cache système que vous estimez être utilisée par PostgreSQL. C'est à la grosse louche, c'est juste un paramètre pour corriger l'estimation du coût de certaines entrées-sorties, mais il a peu d'impact.
Marc.
Hors ligne
Merci pour votre réponse ( rapide !! :-) )
Voici le EXPLAIN analyse de mon serveur de test :
iha=# EXPLAIN analyse SELECT 1 FROM omc_cmd_entete cmd, omc_cmd_lignes ligne LEFT OUTER JOIN omc_fac_dist fac on fac.id_po_distribution = ligne.id_po_distribution WHERE cmd.po_header_id=21169 AND ligne.po_header_id=cmd.po_header_id;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=18.79..67300.53 rows=85 width=0) (actual time=46.147..46.158 rows=1 loops=1)
-> Nested Loop (cost=0.00..110.89 rows=25 width=11) (actual time=25.421..25.429 rows=1 loops=1)
-> Index Scan using idx_cmd_entete_header_id on omc_cmd_entete cmd (cost=0.00..8.28 rows=1 width=11) (actual time=14.936..14.938 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
-> Index Scan using idx_cmd_lignes_header_id on omc_cmd_lignes ligne (cost=0.00..102.35 rows=25 width=22) (actual time=10.476..10.479 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
-> Bitmap Heap Scan on omc_fac_dist fac (cost=18.79..2677.70 rows=791 width=11) (actual time=20.717..20.717 rows=0 loops=1)
Recheck Cond: (fac.id_po_distribution = ligne.id_po_distribution)
-> Bitmap Index Scan on idx_fac_dist_po_distribution (cost=0.00..18.59 rows=791 width=0) (actual time=20.712..20.712 rows=0 loops=1)
Index Cond: (fac.id_po_distribution = ligne.id_po_distribution)
Total runtime: 46.403 ms
(11 rows)
Celui de prod :
iha=# EXPLAIN analyse SELECT 1 FROM omc_cmd_entete cmd, omc_cmd_lignes ligne LEFT OUTER JOIN omc_fac_dist fac on fac.id_po_distribution = ligne.id_po_distribution WHERE cmd.po_header_id=21169 AND ligne.po_header_id=cmd.po_header_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=225.56..141542.25 rows=96 width=0) (actual time=1541.468..1541.491 rows=3 loops=1)
-> Index Scan using idx_cmd_entete_header_id on omc_cmd_entete cmd (cost=0.00..8.29 rows=1 width=11) (actual time=0.067..0.069 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
-> Merge Right Join (cost=225.56..141533.00 rows=96 width=11) (actual time=1541.395..1541.411 rows=3 loops=1)
Merge Cond: (fac.id_po_distribution = ligne.id_po_distribution)
-> Index Scan using idx_fac_dist_po_distribution on omc_fac_dist fac (cost=0.00..512728.56 rows=3499079 width=11) (actual time=0.045..1238.536 rows=317571 loops=1)
-> Sort (cost=225.56..225.70 rows=56 width=22) (actual time=0.053..0.055 rows=1 loops=1)
Sort Key: ligne.id_po_distribution
-> Index Scan using idx_cmd_lignes_header_id on omc_cmd_lignes ligne (cost=0.00..223.94 rows=56 width=22) (actual time=0.042..0.045 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
Total runtime: 1541.558 ms
(11 rows)
Hors ligne
J'avoue ne pas comprendre. En passant le paramètre effective_cache_size à 128 Mo ( donc une valeur ridicule ). Je retrouve les perfs de mon serveur de test :
iha=# set effective_cache_size to 16384 ;
SET
iha=# EXPLAIN analyse SELECT 1 FROM omc_cmd_entete cmd, omc_cmd_lignes ligne LEFT OUTER JOIN omc_fac_dist fac on fac.id_po_distribution = ligne.id_po_distribution WHERE cmd.po_header_id=21169 AND ligne.po_header_id=cmd.po_header_id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=19.42..176837.95 rows=96 width=0) (actual time=0.055..0.070 rows=3 loops=1)
-> Nested Loop (cost=0.00..232.79 rows=56 width=11) (actual time=0.030..0.034 rows=1 loops=1)
-> Index Scan using idx_cmd_entete_header_id on omc_cmd_entete cmd (cost=0.00..8.29 rows=1 width=11) (actual time=0.017..0.018 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
-> Index Scan using idx_cmd_lignes_header_id on omc_cmd_lignes ligne (cost=0.00..223.94 rows=56 width=22) (actual time=0.009..0.010 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
-> Bitmap Heap Scan on omc_fac_dist fac (cost=19.42..3142.55 rows=889 width=11) (actual time=0.020..0.025 rows=3 loops=1)
Recheck Cond: (fac.id_po_distribution = ligne.id_po_distribution)
-> Bitmap Index Scan on idx_fac_dist_po_distribution (cost=0.00..19.20 rows=889 width=0) (actual time=0.014..0.014 rows=3 loops=1)
Index Cond: (fac.id_po_distribution = ligne.id_po_distribution)
Total runtime: 0.132 ms
(11 rows)
Ca ne devrait pas être le contraire ?
Hors ligne
En réduisant effective_cache_size, vous rendez le tri plus coûteux, comparativement.
Votre base, elle est en grande partie en RAM ? Vous avez peu d'accès disque ?
Marc.
Hors ligne
Voici ce que j'ai en hit ratio sur le cache ( je n'ai malheureusement pas pg_buffercache d'installé )
Serveur de test (16Go)
iha=# SELECT datname, blks_read, blks_hit, round((blks_hit::float/(blks_read+blks_hit+1)*100)::numeric, 2) as cachehitratio FROM
iha-# pg_stat_database ORDER BY datname, cachehitratio ;
datname | blks_read | blks_hit | cachehitratio
-----------+-----------+----------+---------------
iha | 181330 | 3600496 | 95.21
postgres | 141 | 29124 | 99.51
template0 | 0 | 0 | 0.00
template1 | 0 | 0 | 0.00
(4 rows)
iha=# SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_database ORDER BY datname ;
datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit
-----------+-------------+-------------+---------------+-----------+----------
iha | 5 | 2572 | 93 | 181351 | 3617256
postgres | 0 | 541 | 0 | 141 | 29451
template0 | 0 | 0 | 0 | 0 | 0
template1 | 0 | 0 | 0 | 0 | 0
(4 rows)
Serveur de prod (8Go) :
iha=# SELECT datname, blks_read, blks_hit, round((blks_hit::float/(blks_read+blks_hit+1)*100)::numeric, 2) as cachehitratio FROM
iha-# pg_stat_database ORDER BY datname, cachehitratio ;
datname | blks_read | blks_hit | cachehitratio
-----------+-----------+-----------+---------------
iha | 53144253 | 579915082 | 91.61
postgres | 3153 | 32092 | 91.05
template0 | 0 | 0 | 0.00
template1 | 0 | 0 | 0.00
iha=# SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_database ORDER BY datname ;
datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit
-----------+-------------+-------------+---------------+-----------+-----------
iha | 6 | 44213 | 4048 | 53342883 | 581677032
postgres | 1 | 714 | 0 | 3153 | 32419
template0 | 0 | 0 | 0 | 0 | 0
template1 | 0 | 0 | 0 | 0 | 0
(4 rows)
Bien sur, il y a beaucoup plus d'activité sur le serveur de prod et donc plus d'IO.
Sachant que ma table omc_fac_dist fait plus de 3 millions de ligne sur les 2 serveur :
iha=# select count(*) from omc_fac_dist ;
count
---------
3499079
(1 row)
Hors ligne
À bien y regarder, pour commencer, la syntaxe est très bizarre: pourquoi ne pas écrire la jointure entre omc_cmd_entete cmd et omc_cmd_lignes avec une syntaxe JOIN ?
SELECT 1 FROM omc_cmd_entete cmd, omc_cmd_lignes ligne LEFT OUTER JOIN omc_fac_dist fac on fac.id_po_distribution = ligne.id_po_distribution WHERE cmd.po_header_id=21169 AND ligne.po_header_id=cmd.po_header_id;
Telle qu'écrite, vous demandez au moteur de joindre par left outer join, puis de faire la jointure inner. Suivant les versions du moteur, il peut plus ou moins bien comprendre.
Essayez déjà, avant d'aller plus loin, de l'écrire correctement :
À bien y regarder, pour commencer, la syntaxe est très bizarre: pourquoi ne pas écrire la jointure entre omc_cmd_entete cmd et omc_cmd_lignes avec une syntaxe JOIN ?
SELECT 1 FROM omc_cmd_entete cmd INNER JOIN omc_cmd_lignes ligne on ligne.po_header_id=cmd.po_header_id LEFT OUTER JOIN omc_fac_dist fac on fac.id_po_distribution = ligne.id_po_distribution WHERE cmd.po_header_id=21169
Marc.
Hors ligne
Merci pour le conseil concernant la jointure.
Cela n'explique pas pourquoi effective_cache_size a une telle incidence en réduisant sa valeur ?
Par contre, en passant le paramètre random_page_cost à 1 au lieux de 4, je résous le problème ce qui est plus logique :-)
iha=# set random_page_cost to 1 ;
SET
iha=# EXPLAIN analyse SELECT 1 FROM omc_cmd_entete cmd, omc_cmd_lignes ligne LEFT OUTER JOIN omc_fac_dist fac on fac.id_po_distribution = ligne.id_po_distribution WHERE cmd.po_header_id=21169 AND ligne.po_header_id=cmd.po_header_id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..42067.22 rows=96 width=0) (actual time=0.124..0.139 rows=3 loops=1)
-> Nested Loop (cost=0.00..59.96 rows=56 width=11) (actual time=0.092..0.096 rows=1 loops=1)
-> Index Scan using idx_cmd_entete_header_id on omc_cmd_entete cmd (cost=0.00..2.27 rows=1 width=11) (actual time=0.044..0.045 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
-> Index Scan using idx_cmd_lignes_header_id on omc_cmd_lignes ligne (cost=0.00..57.12 rows=56 width=22) (actual time=0.043..0.044 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
-> Index Scan using idx_fac_dist_po_distribution on omc_fac_dist fac (cost=0.00..739.02 rows=889 width=11) (actual time=0.027..0.031 rows=3 loops=1)
Index Cond: (fac.id_po_distribution = ligne.id_po_distribution)
Total runtime: 0.202 ms
(9 rows)
Hors ligne
effective_cache_size aide PostgreSQL à estimer la probabilité qu'une donnée soit dans le cache système, ou y reste. C'est entre autres utilisé pour estimer le nombre réel d'entrées-sorties disque que va faire le tri (le coût pour aller rechercher les données sur le disque à la fin du tri). S'il est gros, le moteur estime que le coût du tri est plus faible.
En voyant votre plan, je constate que PostgreSQL sur-estime très fortement le nombre d'enregistrements de fac.id_po_distribution = ligne.id_po_distribution. C'est ce qui entraîne le mauvais plan.
Est-ce que la réécriture a amélioré le plan, pour commencer ? (vous ne le précisez pas)
Marc.
Hors ligne
Effectivement, on note une légère amélioration (malheureusement, je ne pratique pas assez le sql pour comprendre la différence) :
iha=# EXPLAIN analyse SELECT 1 FROM omc_cmd_entete cmd, omc_cmd_lignes ligne LEFT OUTER JOIN omc_fac_dist fac on fac.id_po_distribution = ligne.id_po_distribution WHERE cmd.po_header_id=21169 AND ligne.po_header_id=cmd.po_header_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=225.56..141542.25 rows=96 width=0) (actual time=1664.421..1664.446 rows=3 loops=1)
-> Index Scan using idx_cmd_entete_header_id on omc_cmd_entete cmd (cost=0.00..8.29 rows=1 width=11) (actual time=0.073..0.075 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
-> Merge Right Join (cost=225.56..141533.00 rows=96 width=11) (actual time=1664.342..1664.359 rows=3 loops=1)
Merge Cond: (fac.id_po_distribution = ligne.id_po_distribution)
-> Index Scan using idx_fac_dist_po_distribution on omc_fac_dist fac (cost=0.00..512728.56 rows=3499079 width=11) (actual time=0.073..1359.309 rows=317571 loops=1)
-> Sort (cost=225.56..225.70 rows=56 width=22) (actual time=0.101..0.104 rows=1 loops=1)
Sort Key: ligne.id_po_distribution
-> Index Scan using idx_cmd_lignes_header_id on omc_cmd_lignes ligne (cost=0.00..223.94 rows=56 width=22) (actual time=0.073..0.074 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
Total runtime: 1664.645 ms
(11 rows)
iha=#
iha=#
iha=# EXPLAIN ANALYZE SELECT 1 FROM omc_cmd_entete cmd INNER JOIN omc_cmd_lignes ligne on ligne.po_header_id=cmd.po_header_id LEFT OUTER JOIN omc_fac_dist fac on fac.id_po_distribution = ligne.id_po_distribution WHERE cmd.po_header_id=21169 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=225.56..141542.25 rows=96 width=0) (actual time=1282.415..1282.440 rows=3 loops=1)
-> Index Scan using idx_cmd_entete_header_id on omc_cmd_entete cmd (cost=0.00..8.29 rows=1 width=11) (actual time=0.032..0.034 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
-> Merge Right Join (cost=225.56..141533.00 rows=96 width=11) (actual time=1282.378..1282.394 rows=3 loops=1)
Merge Cond: (fac.id_po_distribution = ligne.id_po_distribution)
-> Index Scan using idx_fac_dist_po_distribution on omc_fac_dist fac (cost=0.00..512728.56 rows=3499079 width=11) (actual time=0.047..978.052 rows=317571 loops=1)
-> Sort (cost=225.56..225.70 rows=56 width=22) (actual time=0.066..0.068 rows=1 loops=1)
Sort Key: ligne.id_po_distribution
-> Index Scan using idx_cmd_lignes_header_id on omc_cmd_lignes ligne (cost=0.00..223.94 rows=56 width=22) (actual time=0.045..0.047 rows=1 loops=1)
Index Cond: (21169::numeric = po_header_id)
Total runtime: 1282.525 ms
(11 rows)
Mais dès que je modifie random_page_cost à 1, c'est flagrant :
iha=# set random_page_cost to 1 ;
SET
iha=# EXPLAIN ANALYZE SELECT 1 FROM omc_cmd_entete cmd INNER JOIN omc_cmd_lignes ligne on ligne.po_header_id=cmd.po_header_id LEFT OUTER JOIN omc_fac_dist fac on fac.id_po_distribution = ligne.id_po_distribution WHERE cmd.po_header_id=21169 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..42067.22 rows=96 width=0) (actual time=0.128..0.143 rows=3 loops=1)
-> Nested Loop (cost=0.00..59.96 rows=56 width=11) (actual time=0.095..0.099 rows=1 loops=1)
-> Index Scan using idx_cmd_entete_header_id on omc_cmd_entete cmd (cost=0.00..2.27 rows=1 width=11) (actual time=0.047..0.048 rows=1 loops=1)
Index Cond: (po_header_id = 21169::numeric)
-> Index Scan using idx_cmd_lignes_header_id on omc_cmd_lignes ligne (cost=0.00..57.12 rows=56 width=22) (actual time=0.042..0.043 rows=1 loops=1)
Index Cond: (21169::numeric = po_header_id)
-> Index Scan using idx_fac_dist_po_distribution on omc_fac_dist fac (cost=0.00..739.02 rows=889 width=11) (actual time=0.030..0.036 rows=3 loops=1)
Index Cond: (fac.id_po_distribution = ligne.id_po_distribution)
Total runtime: 0.219 ms
(9 rows)
Encore merci pour votre aide
Hors ligne
Oui, mais le random_page_cost pourrait vous revenir assez violemment dans la figure pour une valeur différente de po_header_id, si cette valeur ramène beaucoup d'enregistrements de la table 'ligne', par exemple. Vous lui dites, avec ce paramétrage, qu'un bloc lu séquentiellement coûte autant qu'un bloc lu aléatoirement. Tant que vous êtes en cache c'est vrai. Pour une requête où les données ne sont pas en cache, cela peut être désastreux.
Le plan sur la requête réécrite est tout de même très étonnant. Vous n'avez pas dit quelle version de PostgreSQL vous utilisez ? Pouvez vous aussi me donner le type de po_header_id ?
Marc.
Hors ligne
Oui, effectivement, vu comme ça ... Mais vu la taille de cache disque que j'ai sur mon serveur ... Enfin, c'est à prendre en considération.
Qu'est ce que le po_header_id et comment l'obtient-on ?
Je suis en vacances à partir de ce soir et je n'aurai donc plus accès aux serveurs donc ne soyez pas étonné si je ne répond que dans 15 jours.
Passez de très bonnes fêtes optimisées et sans trop de parcours aléatoires ;-)
Hors ligne
po_header_id c'est une colonne de "omc_cmd_entete". Je voudrais savoir son type (via pgadmin, ou "\d omc_cmd_entete" sous psql).
Et n'oubliez pas la version (select version() )
Marc.
Hors ligne
