Vous n'êtes pas identifié(e).
En fait, nous passons par un logiciel de sauvegarde (Simpana de Comvault) et c'est lui qui doit sauvegarder les données pour pouvoir bénéficier des avantages de la déduplication. Donc nous devons passer par pg_start_backup et pg_stop_backup.
Pour les modes de sauvegardes et restaurations, je suis d'accord mais il nous manquait la possibilité de faire des sauvegardes à chaud sans journalisation.
Ah oui, effectivement, si l'on peut changer archive command à chaud ... c'est encore plus simple !! Ca ne m'était même pas venu à l'esprit ! Je vais tester ça.
Quand au pg_dump, l'idée est d’avoir un seul mode de sauvegarde (et de restauration) pour toutes nos bases, surtout celle avec une grosse volumétrie. Mais si il n'en tenait qu'à moi ...
Bonjour à tous, j'ai été confronté à une base de données qui journalisait énormément (plus de 100 Go de log par jours) et pour rien, car l'application pouvait très bien se contenter d'une restauration à j-1.
Nous étions contraint d'activer la journalisation uniquement pour faire des sauvegardes à chaud, la base ne pouvant pas être arrêtée.
J'ai donc réfléchi à comment activer la journalisation uniquement pendant la phase de pg_start_backup et pg_stop_backup mais comme vous le savez déjà, il faut redémarrer l'instance pour pouvoir prendre en compte le paramètre "archive_mode".
J'ai donc crée un fichier tag "backup_in_progress" avant chaque backup, ce qui me permet d'archiver les journaux qui seront strictement nécessaire à la restauration de l'instance. Une fois le backup des datas terminé, je supprime le fichier tag et je sauvegarde mes logs.
Ca se traduit par ça dans ma ligne de commande d'archivage :
archive_command = 'test -f /var/lib/pgsql/var/log/backup_in_progress && gzip < %p > /var/lib/pgsql/var/journaux/%f || cp %p /dev/null '
J'ai fait des tests de sauvegardes restaurations, ça fonctionne très bien pour une restauration "à la date du dernier backup".
J'aimerais généraliser cette solution pour les mêmes cas de figures (base avec des besoins de haute dispo mais sans besoin de journalisation). On retrouverait ainsi le mode de fonctionnement de SQL Server qui permet de faire de la sauvegarde à chaud sans journaliser les logs.
Est ce que vous voyez des effets de bords potentielles auquel je n'aurais pas pensé ?
Bonjour Gleu, effectivement, il y avait une erreur dans la commande gunzip.
La bonne commande est : restore_command = 'gunzip < /mnt/server/archivedir/%f > %p'
Un grand merci !
Je fais un simple tar.gz de mes datas pour mes tests entre mon pg_start_backup et pg_stop_backup.
En suite, je fais un tar.gz de mes logs.
A noter que je fais ça quand mon instance est en charge histoire de générer des logs.
Pour la restauration, je supprime mon pgdata et mon répertoire de log et je restaure mes tar.gz.
Bonjour, je fais des tests de sauvegarde à chaud et restauration en 9.6 (je découvre cette version) et je rencontre un problème à la restauration.
J'ai restauré mes données data (PGDATA)
Je supprimer le contenu de pg_xlog :
ROOT@sv004285:/var/lib/pgsql/data/pg_xlog# ll
total 212996
-rw-------. 1 postgres postgres 16777216 Sep 21 10:14 00000001000000020000007D
-rw-------. 1 postgres postgres 16777216 Sep 21 10:14 00000001000000020000007E
-rw-------. 1 postgres postgres 16777216 Sep 21 10:14 00000001000000020000007F
-rw-------. 1 postgres postgres 16777216 Sep 21 10:15 000000010000000200000080
-rw-------. 1 postgres postgres 16777216 Sep 21 10:15 000000010000000200000081
-rw-------. 1 postgres postgres 16777216 Sep 21 10:15 000000010000000200000082
-rw-------. 1 postgres postgres 16777216 Sep 21 10:15 000000010000000200000083
-rw-------. 1 postgres postgres 16777216 Sep 21 10:15 000000010000000200000084
-rw-------. 1 postgres postgres 16777216 Sep 21 10:15 000000010000000200000085
-rw-------. 1 postgres postgres 16777216 Sep 21 10:15 000000010000000200000086
-rw-------. 1 postgres postgres 16777216 Sep 21 10:16 000000010000000200000087
-rw-------. 1 postgres postgres 16777216 Sep 21 10:16 000000010000000200000088
-rw-------. 1 postgres postgres 16777216 Sep 21 10:16 000000010000000200000089
drwx------. 2 postgres postgres 4096 Sep 21 10:16 archive_status
ROOT@sv004285:/var/lib/pgsql/data/pg_xlog# rm -Rf *
ROOT@sv004285:/var/lib/pgsql/data/pg_xlog# ll
total 0
J’ai restauré mes journaux :
ROOT@sv004285:/var/lib/pgsql/data/pg_xlog# ll ../../var/journaux/
total 87036
-rw-------. 1 postgres postgres 7810683 Sep 21 10:15 000000010000000200000081
-rw-------. 1 postgres postgres 290153 Sep 21 10:15 000000010000000200000082
-rw-------. 1 postgres postgres 10025179 Sep 21 10:15 000000010000000200000083
-rw-------. 1 postgres postgres 209 Sep 21 10:19 000000010000000200000083.0003E5A0.backup
-rw-------. 1 postgres postgres 10131125 Sep 21 10:15 000000010000000200000084
-rw-------. 1 postgres postgres 9780943 Sep 21 10:15 000000010000000200000085
-rw-------. 1 postgres postgres 9155957 Sep 21 10:15 000000010000000200000086
-rw-------. 1 postgres postgres 7750724 Sep 21 10:16 000000010000000200000087
-rw-------. 1 postgres postgres 6688723 Sep 21 10:16 000000010000000200000088
-rw-------. 1 postgres postgres 6216454 Sep 21 10:17 000000010000000200000089
-rw-------. 1 postgres postgres 5947763 Sep 21 10:18 00000001000000020000008A
-rw-------. 1 postgres postgres 4155467 Sep 21 10:18 00000001000000020000008B
-rw-------. 1 postgres postgres 5542338 Sep 21 10:18 00000001000000020000008C
-rw-------. 1 postgres postgres 5603097 Sep 21 10:19 00000001000000020000008D
La commande de restauration dans le recovery.conf :
restore_command = 'gunzip < /var/lib/pgsql/var/journaux/%f "%p"'
Je démarre l'instance :
systemctl start postgresql.service
et c'est le fail :
2017-09-21 11:51:07 CEST [26371]: [1-1] LOG: 00000: database system was interrupted; last known up at 2017-09-21 10:15:21 CEST
2017-09-21 11:51:07 CEST [26371]: [2-1] LOCATION: StartupXLOG, xlog.c:6025
2017-09-21 11:51:07 CEST [26371]: [3-1] LOG: 00000: creating missing WAL directory "pg_xlog/archive_status"
2017-09-21 11:51:07 CEST [26371]: [4-1] LOCATION: ValidateXLOGDirectoryStructure, xlog.c:3926
2017-09-21 11:51:07 CEST [26371]: [5-1] LOG: 00000: starting archive recovery
2017-09-21 11:51:07 CEST [26371]: [6-1] LOCATION: StartupXLOG, xlog.c:6098
gzip: pg_xlog/RECOVERYXLOG.gz: No such file or directory
2017-09-21 11:51:07 CEST [26371]: [7-1] LOG: 00000: invalid checkpoint record
2017-09-21 11:51:07 CEST [26371]: [8-1] LOCATION: ReadCheckpointRecord, xlog.c:7781
2017-09-21 11:51:07 CEST [26371]: [9-1] FATAL: XX000: could not locate required checkpoint record
2017-09-21 11:51:07 CEST [26371]: [10-1] HINT: If you are not restoring from a backup, try removing the file "/var/lib/pgsql/data/backup_label".
2017-09-21 11:51:07 CEST [26371]: [11-1] LOCATION: StartupXLOG, xlog.c:6164
2017-09-21 11:51:07 CEST [26368]: [4-1] LOG: 00000: startup process (PID 26371) exited with exit code 1
2017-09-21 11:51:07 CEST [26368]: [5-1] LOCATION: LogChildExit, postmaster.c:3495
2017-09-21 11:51:07 CEST [26368]: [6-1] LOG: 00000: aborting startup due to startup process failure
2017-09-21 11:51:07 CEST [26368]: [7-1] LOCATION: reaper, postmaster.c:2769
C'est quoi ce fichier RECOVERYXLOG ?
Est ce que j'ai manqué qq chose ?
Merci pour votre aide !
Sinon, que penses tu de la méthode de sauvegarde / restauration ? Est ce que je peux tomber sur un os ?
Mais pourquoi je n'y ai pas pensé !! Un grand merci ! :-)
Sébastien, le problème, c'est que si je restore le snapshot, c'est avec le service postgresql en enable ... et comme je suis bien obligé de redémarrer la VM ... pgs démarre ... je suis coincé ?
Merci pour ta réponse, c'est plus clair maintenant ! :-)
Est ce que tu aurais une idée pour faire en sorte que mon instance ne redémarre pas automatiquement au reboot à la suite de la restauration du snapshoot (dans un état du coup potentiellement incohérente) ?
Bonjour à tous, je me greffe à la discutions car j'ai le même problème à résoudre.
Nous souhaitons faire des sauvegardes à chaud de nos instances via snapshoot VMWAre.
J'ai trouvé une solution : Script pg_start_backup --> Snap VMWare VMDK 0 « OS + Instance pgs » script pg_stop_backup --> Snap VMWare VMDK 1 « WAL Archivés » --> purge des journaux générés avant pg_start_backup
J'ai ainsi décorrélé la sauvegarde des data et des journaux ce qui me permet en théorie de pouvoir, en restaurant mes 2 VMDK de ma VM, lancer un recovery de la base "propre" puisqu'il aura les WAL générés après le pg_stop_backup.
En pratique, ça fonctionne sauf que ... lorsque que je redémarre ma VM, l'instance pgs redémarre automatiquement (alors qu'il y a le fichier postmaster.pid sous PGDATA !!) ce qui m'empêche de pouvoir configurer le fichier recovery.conf.
Est ce que vous auriez une idée pourquoi pgs démarre quand même alors que le postmaster.pid est présent ? Sachant que j'utilise systemd sous RHEL 7.
Et sinon, que pensez vous de ma méthode de sauvegarde / restauration ?
Bonjour,
Merci pour ta réponse. J'ai du effectivement mettre à jour notre repository yum avec la dernière version et tout est rentré dans l'ordre.
Il y a donc bien une version 1.0.1 de openssl dispo chez redhat :-)
Bonjour, je rencontre un pb tordu lors de l'installation du rpm postgresql93-server.x86_64 sur un serveur rhel 6.3 64bits
Je tiens à préciser que j'ai copier les rpm sur mon dépot yum interne.
ROOT@sv001500:/usr/lib64# yum install postgresql93-server.x86_64
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql93-server.x86_64 0:9.3.2-1PGDG.rhel6 will be installed
--> Processing Dependency: postgresql93 = 9.3.2-1PGDG.rhel6 for package: postgresql93-server-9.3.2-1PGDG.rhel6.x86_64
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-server-9.3.2-1PGDG.rhel6.x86_64
--> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql93-server-9.3.2-1PGDG.rhel6.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql93-server-9.3.2-1PGDG.rhel6.x86_64
--> Running transaction check
---> Package postgresql93.x86_64 0:9.3.2-1PGDG.rhel6 will be installed
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-9.3.2-1PGDG.rhel6.x86_64
---> Package postgresql93-libs.x86_64 0:9.3.2-1PGDG.rhel6 will be installed
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-libs-9.3.2-1PGDG.rhel6.x86_64
--> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql93-libs-9.3.2-1PGDG.rhel6.x86_64
---> Package postgresql93-server.x86_64 0:9.3.2-1PGDG.rhel6 will be installed
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-server-9.3.2-1PGDG.rhel6.x86_64
--> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql93-server-9.3.2-1PGDG.rhel6.x86_64
--> Finished Dependency Resolution
Error: Package: postgresql93-libs-9.3.2-1PGDG.rhel6.x86_64 (addons_SPE_LGD)
Requires: libcrypto.so.10(libcrypto.so.10)(64bit)
Error: Package: postgresql93-server-9.3.2-1PGDG.rhel6.x86_64 (addons_SPE_LGD)
Requires: libssl.so.10(libssl.so.10)(64bit)
Error: Package: postgresql93-libs-9.3.2-1PGDG.rhel6.x86_64 (addons_SPE_LGD)
Requires: libssl.so.10(libssl.so.10)(64bit)
Error: Package: postgresql93-server-9.3.2-1PGDG.rhel6.x86_64 (addons_SPE_LGD)
Requires: libcrypto.so.10(libcrypto.so.10)(64bit)
Error: Package: postgresql93-9.3.2-1PGDG.rhel6.x86_64 (addons_SPE_LGD)
Requires: libssl.so.10(libssl.so.10)(64bit)
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest
Hors, quand je regarde sous /usr/lib64, j'ai bien les librairies présentes mais sous forme de liens symbolique :
ROOT@sv001500:/usr/lib64# ll |grep -e crypto -e ssl
-rw-r--r-- 1 root root 65 May 15 2012 .libcrypto.so.1.0.0.hmac
lrwxrwxrwx 1 root root 24 Feb 18 11:51 .libcrypto.so.10.hmac -> .libcrypto.so.1.0.0.hmac
-rw-r--r-- 1 root root 65 May 15 2012 .libssl.so.1.0.0.hmac
lrwxrwxrwx 1 root root 21 Feb 18 11:51 .libssl.so.10.hmac -> .libssl.so.1.0.0.hmac
lrwxrwxrwx 1 root root 18 Feb 18 11:25 libcrypto.so -> libcrypto.so.1.0.0
-rwxr-xr-x 1 root root 1665328 May 15 2012 libcrypto.so.1.0.0
lrwxrwxrwx 1 root root 18 Feb 18 11:51 libcrypto.so.10 -> libcrypto.so.1.0.0
lrwxrwxrwx 1 root root 30 Feb 18 11:25 libk5crypto.so -> ../../lib64/libk5crypto.so.3.1
lrwxrwxrwx 1 root root 15 Feb 18 11:25 libssl.so -> libssl.so.1.0.0
-rwxr-xr-x 1 root root 375352 May 15 2012 libssl.so.1.0.0
lrwxrwxrwx 1 root root 15 Feb 18 11:51 libssl.so.10 -> libssl.so.1.0.0
-rwxr-xr-x 1 root root 244872 Apr 16 2012 libssl3.so
drwxr-xr-x. 3 root root 4096 May 15 2012 openssl
Une idée ?
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 ;-)
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
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)
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)
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 ?
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)
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
Bonjour, j'ai eu effectivement le même echo de la part d'un admin linux. Et vue que je reste dans la même sous version...
De toute façon, vue que j'ai des machines en RHEL 5.2 et 5.4, il me semble compliqué de gérer 2 rpm différent pour une même version de postgres.
Encore merci
Ok merci pour votre réponse.
Bonjour,
Je dois migrer une base pgs 8.4 d'un linux RHEL5.2 vers RHEL5.4. Me conseillez vous de recompiler le noyau Postgres quoi qu'il arrive sachant que j'ai testé et mon noyau sur 5.2 fonctionne "à première vue" très bien sur la 5.4 ?
Dois-je craindre des pb de stabilités ou autre à long terme ?
Merci
Tout s'explique ...
Merci pour l'info.
Bonjour, merci pour ta réponse.
Je viens de refaire un test avec non pas la base "postgres" mais une base "ig9d" et toutes les infos drop database etc sont bien présente dans mon dump.
Peut-être était ce lié au fait que j'utilisais la base par défaut "postgres" ?
Bref, ne pas utiliser la base "postgres" par défaut est la solution pour moi.
Encore merci
