Vous n'êtes pas identifié(e).
Bonjour,
Je réalise de nombreuses requêtes de mise en jour sur une base postgres. Les tables mises à jour possèdent plusieurs millions de lignes (entre 3 et 4 millions). Le temps de mise à jour me semble particulièrement long (plusieurs heures même pour mettre un seul champ à jour). Mais, est ce que je fais bien les choses ???
Pour exemple, voici une requête type :
WITH ident_surf_bati_uf AS (
SELECT edi_parc.id_uf,
SUM(edi_parc.surf_bati_dur) AS surf_bati_dur,
SUM(edi_parc.surf_bati_leger) AS surf_bati_leger,
SUM(edi_parc.surf_bati_total) AS surf_bati_tot,
SUM(edi_parc.surf_resultante_tt_bati) AS surf_resultante_tt_bati
FROM cad_2015.edi_parc
GROUP BY edi_parc.id_uf)
UPDATE cad_2015.edi_parc_uf
SET surf_bati_leger = ident_surf_bati_uf.surf_bati_leger,
surf_bati_dur = ident_surf_bati_uf.surf_bati_dur,
surf_bati_tot = ident_surf_bati_uf.surf_bati_tot,
surf_resultante_tt_bati = ident_surf_bati_uf.surf_resultante_tt_bati
FROM
ident_surf_bati_uf
WHERE
ident_surf_bati_uf.id_uf = edi_parc_uf.id_centr
;Un explain me renvoi ce résultat :
"Update on edi_parc_uf (cost=4198990.84..6334634.17 rows=850593 width=1510)"
" CTE ident_surf_bati_uf"
" -> GroupAggregate (cost=1073593.37..1130730.27 rows=850593 width=76)"
" -> Sort (cost=1073593.37..1081698.53 rows=3242065 width=76)"
" Sort Key: edi_parc.id_uf"
" -> Seq Scan on edi_parc (cost=0.00..290808.65 rows=3242065 width=76)"
" -> Hash Join (cost=3068260.56..5203903.90 rows=850593 width=1510)"
" Hash Cond: (ident_surf_bati_uf.id_uf = edi_parc_uf.id_centr)"
" -> CTE Scan on ident_surf_bati_uf (cost=0.00..17011.86 rows=850593 width=576)"
" -> Hash (cost=940953.14..940953.14 rows=13298914 width=1178)"
" -> Seq Scan on edi_parc_uf (cost=0.00..940953.14 rows=13298914 width=1178)"Suis-je fautif de mauvaises requêtes, de mauvais paramétrages de postgrès, ou bien ces résultats sont-ils "acceptables" ?
Ayant le serveur postgres en question d'installé sur ma station de travail, j'utilise aussi le moniteur de ressources pour observer le fonctionnement général de ma machine. Je constate que mon disque est constamment à 100% de son temps d'activité maximal... et donc qu'il semble être, notamment, un goulot d'étranglement.
Peut-être y a-t-il des paramètres de configuration de postgres à même d'optimiser son fonctionnement, notamment en s’appuyant plus sur la mémoire vive de la machine ?
Merci d'avance pour vos conseils.
Hors ligne
Bonjour,
En dehors de l'optimisation de la requête, le fait que votre disque est toujours à 100% est un signe que la RAM n'est pas ou peu utilisée (c'est ce que vous dites en tout cas).
Il faut donner de la mémoire à postgresql pour qu'il puisse travailler dans la RAM plutôt que sur disque ce qui est toujours très mauvais.
Selon la quantité de mémoire dont vous disposez, augmentez les paramètres shared_buffers et work_mem :
http://docs.postgresql.fr/9.4/runtime-c … ource.html
Cordialement.
Cordialement,
Sébastien.
Hors ligne
Bonjour et merci pour votre retour rapide,
En fonctionnement ma machine possède encore plusieurs Gigas de mémoire vive disponible, je peux donc l'exploiter.
J'avais déjà modifié certains paramètres en suivant des conseils de paramétrage et notamment pour :
shared_buffers : 500MB => est-ce suffisant ?
Concernant work_mem je constate que l'option n'est même pas cochée dans l'éditeur de configuration de pgAdmin. Je vais donc l'appliquer. Etant le seul utilisateur de la bdd, quelle valeur me conseilleriez-vous ? J'ai trouvé des conseils autour de 16MB, ce qui me semble peu...
Merci par avance.
Hors ligne
Bonjour,
si vous lisez l'anglais vous pouvez aller voir cette page pour vous aider dans vos réglages. Certains réglages varient en fonction de l'OS.
work_mem est fixé à une valeur relativement basse parce que c'est un paramètre qui s'applique à chaque session (même à chaque opération de tri - il peut y en avoir plusieurs simultanément sur une même session).
Vous pouvez probablement l'augmenter avec profit (car il est utilisé pour les tris qui apparaissent dans votre requête) si vous êtes sûr qu'il y aura un nombre restreint d'utilisateurs ou
pouvez baisser ce nombre (max_connections).
Éric
Hors ligne
Le meilleur moyen de vous aider serait que vous nous fournissiez un EXPLAIN (ANALYZE, BUFFERS), le EXPLAIN tout court donnant trop peu d'informations.
Quant au fait que cela vous semble long pour ne mettre à jour qu'un seul champ... peu importe le nombre de champs que vous mettez à jour, cela n'entre pas en compte.
Ceci étant dit, utiliser une CTE est généralement un monstrueux frein à toute optimisation que pourrait vouloir faire PostgreSQL. Surtout que là, vous pouvez l'éviter.
Guillaume.
Hors ligne
Bonjour et merci à tous pour vos conseils. J'ai mis un peu de temps à répondre, car pris par d'autres missions.
Je fais une réponse groupée.
J'ai donc apporté des modifications à la configuration de ma base de données :
Shared_buffers => 1GB
Work_mem => 200MB
J'ai redémarré la machine pour repartir sur des bases "saines".
Je viens de lancer un EXPLAIN (ANALYZE,BUFFERS) sur la requête citée précédemment (le disque repasse immédiatement à 100% de son temps d'activité maximal). Comme elle était longue à l'origine (plus de 3 heures), et malgré mes modifications de configuration, elle tourne encore. Je ferai remonter les résultats dès que disponibles.
Concernant le CTE, je suppose que vous faites référence au "WITH" que j'utilise. Je pensais, mais c'est apparemment une mauvaise méthode, que cela pourrait améliorer les types de requêtes que je réalise en prés-calculant en mémoire les données à injecter dans ma table à mettre à jour. Vous évoquez la possibilité de l'éviter, par quel moyen ?
Hors ligne
Bonjour,
Vous pouvez aussi nous donner le résultat de "select name, setting from pg_settings;"
Cordialement,
Sébastien.
Hors ligne
Bonjour,
Le voici :
"allow_system_table_mods";"off"
"application_name";"pgAdmin III - ??diteur de requ??tes"
"archive_command";"(disabled)"
"archive_mode";"off"
"archive_timeout";"0"
"array_nulls";"on"
"authentication_timeout";"60"
"autovacuum";"on"
"autovacuum_analyze_scale_factor";"0.1"
"autovacuum_analyze_threshold";"50"
"autovacuum_freeze_max_age";"200000000"
"autovacuum_max_workers";"3"
"autovacuum_naptime";"60"
"autovacuum_vacuum_cost_delay";"20"
"autovacuum_vacuum_cost_limit";"-1"
"autovacuum_vacuum_scale_factor";"0.2"
"autovacuum_vacuum_threshold";"50"
"backslash_quote";"safe_encoding"
"bgwriter_delay";"200"
"bgwriter_lru_maxpages";"100"
"bgwriter_lru_multiplier";"2"
"block_size";"8192"
"bonjour";"off"
"bonjour_name";""
"bytea_output";"escape"
"check_function_bodies";"on"
"checkpoint_completion_target";"0.5"
"checkpoint_segments";"3"
"checkpoint_timeout";"300"
"checkpoint_warning";"30"
"client_encoding";"UNICODE"
"client_min_messages";"notice"
"commit_delay";"0"
"commit_siblings";"5"
"config_file";"D:/PostgreSQL/9.3/data/postgresql.conf"
"constraint_exclusion";"partition"
"cpu_index_tuple_cost";"0.005"
"cpu_operator_cost";"0.0025"
"cpu_tuple_cost";"0.01"
"cursor_tuple_fraction";"0.1"
"data_directory";"D:/PostgreSQL/9.3/data"
"DateStyle";"ISO, DMY"
"db_user_namespace";"off"
"deadlock_timeout";"1000"
"debug_assertions";"off"
"debug_pretty_print";"on"
"debug_print_parse";"off"
"debug_print_plan";"off"
"debug_print_rewritten";"off"
"default_statistics_target";"100"
"default_tablespace";""
"default_text_search_config";"pg_catalog.french"
"default_transaction_deferrable";"off"
"default_transaction_isolation";"read committed"
"default_transaction_read_only";"off"
"default_with_oids";"off"
"dynamic_library_path";"$libdir"
"effective_cache_size";"16384"
"effective_io_concurrency";"0"
"enable_bitmapscan";"on"
"enable_hashagg";"on"
"enable_hashjoin";"on"
"enable_indexonlyscan";"on"
"enable_indexscan";"on"
"enable_material";"on"
"enable_mergejoin";"on"
"enable_nestloop";"on"
"enable_seqscan";"on"
"enable_sort";"on"
"enable_tidscan";"on"
"escape_string_warning";"on"
"event_source";"PostgreSQL"
"exit_on_error";"off"
"external_pid_file";""
"extra_float_digits";"0"
"from_collapse_limit";"8"
"fsync";"on"
"full_page_writes";"on"
"geqo";"on"
"geqo_effort";"5"
"geqo_generations";"0"
"geqo_pool_size";"0"
"geqo_seed";"0"
"geqo_selection_bias";"2"
"geqo_threshold";"12"
"gin_fuzzy_search_limit";"0"
"hba_file";"D:/PostgreSQL/9.3/data/pg_hba.conf"
"hot_standby";"off"
"hot_standby_feedback";"off"
"ident_file";"D:/PostgreSQL/9.3/data/pg_ident.conf"
"ignore_checksum_failure";"off"
"ignore_system_indexes";"off"
"integer_datetimes";"on"
"IntervalStyle";"postgres"
"join_collapse_limit";"8"
"krb_caseins_users";"off"
"krb_server_keyfile";""
"krb_srvname";"postgres"
"lc_collate";"French_France.1252"
"lc_ctype";"French_France.1252"
"lc_messages";"French_France.1252"
"lc_monetary";"French_France.1252"
"lc_numeric";"French_France.1252"
"lc_time";"French_France.1252"
"listen_addresses";"*"
"lo_compat_privileges";"off"
"local_preload_libraries";""
"lock_timeout";"0"
"log_autovacuum_min_duration";"-1"
"log_checkpoints";"off"
"log_connections";"off"
"log_destination";"stderr"
"log_directory";"pg_log"
"log_disconnections";"off"
"log_duration";"off"
"log_error_verbosity";"default"
"log_executor_stats";"off"
"log_file_mode";"0600"
"log_filename";"postgresql-%Y-%m-%d_%H%M%S.log"
"log_hostname";"off"
"log_line_prefix";"%t "
"log_lock_waits";"off"
"log_min_duration_statement";"-1"
"log_min_error_statement";"error"
"log_min_messages";"warning"
"log_parser_stats";"off"
"log_planner_stats";"off"
"log_rotation_age";"1440"
"log_rotation_size";"10240"
"log_statement";"none"
"log_statement_stats";"off"
"log_temp_files";"-1"
"log_timezone";"Europe/Brussels"
"log_truncate_on_rotation";"off"
"logging_collector";"on"
"maintenance_work_mem";"16384"
"max_connections";"100"
"max_files_per_process";"1000"
"max_function_args";"100"
"max_identifier_length";"63"
"max_index_keys";"32"
"max_locks_per_transaction";"64"
"max_pred_locks_per_transaction";"64"
"max_prepared_transactions";"0"
"max_stack_depth";"2048"
"max_standby_archive_delay";"30000"
"max_standby_streaming_delay";"30000"
"max_wal_senders";"0"
"password_encryption";"on"
"port";"5432"
"post_auth_delay";"0"
"pre_auth_delay";"0"
"quote_all_identifiers";"off"
"random_page_cost";"4"
"restart_after_crash";"on"
"search_path";""$user", public, sde"
"segment_size";"131072"
"seq_page_cost";"1"
"server_encoding";"UTF8"
"server_version";"9.3.2"
"server_version_num";"90302"
"session_replication_role";"origin"
"shared_buffers";"131072"
"shared_preload_libraries";""
"sql_inheritance";"on"
"ssl";"off"
"ssl_ca_file";""
"ssl_cert_file";"server.crt"
"ssl_ciphers";"DEFAULT:!LOW:!EXP:!MD5:@STRENGTH"
"ssl_crl_file";""
"ssl_key_file";"server.key"
"ssl_renegotiation_limit";"524288"
"standard_conforming_strings";"on"
"statement_timeout";"0"
"stats_temp_directory";"pg_stat_tmp"
"superuser_reserved_connections";"3"
"synchronize_seqscans";"on"
"synchronous_commit";"on"
"synchronous_standby_names";""
"syslog_facility";"none"
"syslog_ident";"postgres"
"tcp_keepalives_count";"0"
"tcp_keepalives_idle";"-1"
"tcp_keepalives_interval";"-1"
"temp_buffers";"1024"
"temp_file_limit";"-1"
"temp_tablespaces";""
"TimeZone";"Europe/Brussels"
"timezone_abbreviations";"Default"
"trace_notify";"off"
"trace_recovery_messages";"log"
"trace_sort";"off"
"track_activities";"on"
"track_activity_query_size";"1024"
"track_counts";"on"
"track_functions";"none"
"track_io_timing";"off"
"transaction_deferrable";"off"
"transaction_isolation";"read committed"
"transaction_read_only";"off"
"transform_null_equals";"off"
"unix_socket_directories";""
"unix_socket_group";""
"unix_socket_permissions";"0777"
"update_process_title";"on"
"vacuum_cost_delay";"0"
"vacuum_cost_limit";"200"
"vacuum_cost_page_dirty";"20"
"vacuum_cost_page_hit";"1"
"vacuum_cost_page_miss";"10"
"vacuum_defer_cleanup_age";"0"
"vacuum_freeze_min_age";"50000000"
"vacuum_freeze_table_age";"150000000"
"wal_block_size";"8192"
"wal_buffers";"2048"
"wal_keep_segments";"0"
"wal_level";"minimal"
"wal_receiver_status_interval";"10"
"wal_receiver_timeout";"60000"
"wal_segment_size";"2048"
"wal_sender_timeout";"60000"
"wal_sync_method";"open_datasync"
"wal_writer_delay";"200"
"work_mem";"204800"
"xmlbinary";"base64"
"xmloption";"content"
"zero_damaged_pages";"off"Hors ligne
Quelle est la taille de votre RAM ?
"effective_cache_size";"16384" >> positionnez le à 2/3 de votre RAM
"maintenance_work_mem";"16384" >> mettez 300 ou 400 Mo
"shared_buffers";"131072" >> valeur actuelle = 1Go, montez à 1/4 de votre RAM
"work_mem";"204800" >> valeur actuelle = 200Mo, si vous êtes tous seul à ouvrir des sessions, tentez d'allouer plus (faites le total de vos buffers manipulés par la requête remontés dans l'analyze que vous avez lancé et si ce n'est pas trop élevé, mettez cette valeur dans work_mem)
Cordialement,
Sébastien.
Hors ligne
Attention, comme il s'agit d'un serveur windows, je vous conseillerai de vérifier que l'augmentation du paramètre shared_buffers se traduit par une augmentation des performances. Si cela n'est pas le cas, revenez à une valeur plus modérée.
Julien.
https://rjuju.github.io/
En ligne
Je viens donc d'appliquer les recommandations de ruizsebastien, et pour la question ma station de travail est dotée de 8 giga de mémoire vive :
effective_cache size : 5GB
maintenance_work_mem : 400MB
shared_buffers : 2GB
work_mem : 500MB (n'ayant pas eu le retour de la rqte d'analyze je l'ai augmentée "au doigt mouillé" pour l'instant)
J'ai relancé la requête en EXPLAIN(ANALYZE,BUFFERS), j'attends les résultats...
Rjuju : ma machine est bien sous windows, j'attends de voir les résultats précédents pour me faire un avis sur les améliorations potentielles qu'elles pourront m'apporter.
Merci à tous, je vous tiens informé de l'évolution des choses dès que j'ai des résultats.
Hors ligne
Comme il s'agit d'une station de travail, ça m'étonnerait que PostgreSQL soit le seul logiciel utilisé, donc la règle des 2/3 - 1/4 - etc n'est pas à utiliser directement sur la quantité de RAM disponible. Mais peu importe. L'essentiel est l'EXPLAIN ANALYZE.
Guillaume.
Hors ligne
La requête s'est finalement terminée au bout de 2h30.
Voici le résultat de l'EXPLAIN ANALYSE :
"Update on edi_parc_uf (cost=837865.78..1330604.12 rows=959944 width=1518) (actual time=8613371.967..8613371.967 rows=0 loops=1)"
" Buffers: shared hit=32794495 read=771955 dirtied=4816855, temp read=157346 written=157332"
" CTE ident_surf_bati_uf"
" -> HashAggregate (cost=339150.04..348749.48 rows=959944 width=76) (actual time=24802.636..26038.480 rows=2064209 loops=1)"
" Buffers: shared hit=63581 read=266476"
" -> Seq Scan on edi_parc (cost=0.00..298627.24 rows=3241824 width=76) (actual time=13.321..18158.419 rows=3241824 loops=1)"
" Buffers: shared read=266209"
" -> Hash Join (cost=489116.30..981854.64 rows=959944 width=1518) (actual time=60348.342..248611.858 rows=2064208 loops=1)"
" Hash Cond: (ident_surf_bati_uf.id_uf = edi_parc_uf.id_centr)"
" Buffers: shared hit=167910 read=402743, temp read=157346 written=157332"
" -> CTE Scan on ident_surf_bati_uf (cost=0.00..19198.88 rows=959944 width=576) (actual time=24802.654..26909.001 rows=2064209 loops=1)"
" Buffers: shared hit=63581 read=266476"
" -> Hash (cost=156780.91..156780.91 rows=2064991 width=1186) (actual time=35545.419..35545.419 rows=2065094 loops=1)"
" Buckets: 65536 Batches: 8 Memory Usage: 131831kB"
" Buffers: shared hit=38502 read=135971, temp written=112375"
" -> Seq Scan on edi_parc_uf (cost=0.00..156780.91 rows=2064991 width=1186) (actual time=0.062..30886.513 rows=2065094 loops=1)"
" Buffers: shared hit=160 read=135971"
"Total runtime: 8613547.955 ms"
Hors ligne
Le modifications de configuration proposées par ruizsebastien ont donc sensiblement amélioré les résultats (gain de 30%).
Maintenant, et à la vue de l'EXPLAIN ANALYSE, considérez-vous que l'on soit dans un fonctionnement "normal" pour ce type de requête ? En sachant, bien entendu, que nous sommes sur une station de travail (processeur XEON) et pas un serveur et donc des disque à 7200 rpm.
Hors ligne
En terme de statistiques, tout semble aller bien. Il y a énormément de lectures disques (signe que le cache n'est pas suffisant), et que d'écritures disques, bizarrement pour aboutir à 0 lignes mises à jour.
La CTE est exécutée via un parcours séquentiel de la table edi_parc, suivi par un HashAggregate. Le parcours séquentiel ne trouve rien dans le cache, il doit tout demander au système d'exploitation, donc là, c'est la vitesse de Windows et des disques qui sont en cause. Le fait que PostgreSQL n'a rien en cache peut venir du fait que le shared_buffers n'est pas très grand mais de toute façon, sur une station de travail et sur du Windows, il n'est guère possible de monter le shared_buffers très haut. Et même en indiquant 2 Go (ce qui serait vraiment le max vu votre configuration mémoire et votre utilisation de Windows), cette table y tiendrait à peine. Bref, le seul moyen d'améliorer le SeqScan, c'est d'augmenter le paramètre shared_buffers mais il ne faut pas s'attendre à des miracles. En ce qui concerne le noeud HashAggregate, il n'utilise pas de fichiers temporaire, le work_mem est du coup bien configuré. Bref, sur la CTE, peu de moyens d'amélioration. Ceci étant dit, elle prend 26 secondes. Ce n'est rien par rapport aux 2h30 total.
Le HashJoin est le noeud de jointure entre la CTE et edi_parc_uf. Je ne vais pas faire le détail. Là aussi, ce n'est pas forcément très lent, on est à 4 minutes.
Donc le noeud qui prend beaucoup de temps, c'est l'Update lui-même. Donc l'opération de mise à jour des lignes. Et vu le nombre de blocs lus, modifiés, écrits, ce n'est pas très étonnant. Une des deux tables aurait-elle été alimentée avant cette mise à jour ? parce qu'autant d'écriture pour ne mettre à jour aucune ligne, c'est pour le moins étonnant.
Pour moi, le problème vient de deux points : la requête et la gestion des écritures dans les journaux de transactions et dans les fichiers de données.
Commencez par réécrire la requête ainsi :
WITH ident_surf_bati_uf AS (
SELECT edi_parc.id_uf,
SUM(edi_parc.surf_bati_dur) AS surf_bati_dur,
SUM(edi_parc.surf_bati_leger) AS surf_bati_leger,
SUM(edi_parc.surf_bati_total) AS surf_bati_tot,
SUM(edi_parc.surf_resultante_tt_bati) AS surf_resultante_tt_bati
FROM cad_2015.edi_parc
GROUP BY edi_parc.id_uf)
UPDATE cad_2015.edi_parc_uf
SET surf_bati_leger = ident_surf_bati_uf.surf_bati_leger,
surf_bati_dur = ident_surf_bati_uf.surf_bati_dur,
surf_bati_tot = ident_surf_bati_uf.surf_bati_tot,
surf_resultante_tt_bati = ident_surf_bati_uf.surf_resultante_tt_bati
FROM
ident_surf_bati_uf
WHERE
ident_surf_bati_uf.id_uf = edi_parc_uf.id_centr
AND
(surf_bati_leger <> ident_surf_bati_uf.surf_bati_leger OR
surf_bati_dur <> ident_surf_bati_uf.surf_bati_dur OR
surf_bati_tot <> ident_surf_bati_uf.surf_bati_tot OR
surf_resultante_tt_bati <> ident_surf_bati_uf.surf_resultante_tt_bati)
;pour être sûr de n'écrire que les lignes qui sont vraiment différentes.
Ensuite, il faudra jouer avec les paramètres checkpoint_segments et checkpoint_timeout (à augmenter tous les deux, genre 20 pour le premier et 15 minutes pour le second). Et bien considérer qu'avoir des performances avec PostgreSQL sous Windows, c'est pas forcément trivial.
Guillaume.
Hors ligne
Bonjour,
En complément de l'excellente analyse de Guillaume, checkez les traces de postgresql pour voir si vous avez des messages du genre : "LOG: checkpoints are occurring too frequently".
Si c'est le cas il faut augmenter les paramètres checkpoint_segments et checkpoint_timeout (comme indiqué par guillaume ci-dessus) jusqu'à ce que les messages disparaissent.
Des checkpoint trop fréquent génèrent trop d'accès disque.
Et au fait : pourquoi travaillez vous sur votre poste windows ? N'avez vous pas la possibilité d'avoir une machine linux ou même d'installer un linux (en plus de votre windows) sur votre poste de travail ?
Cordialement.
Cordialement,
Sébastien.
Hors ligne
Bonjour et merci pour ce retour très complet !!!
J'ai donc appliqué quelques modifications dans ma configuration :
shared_buffers => passé à 3GB (pour test et même si cela doit attribuer une grosse partie de mes ressources machines à postgres, au moins pour le temps de mes mises à jour, ce n'est pas un pb)
checkpoint_segments : 20
checkpoint_timeout : 15MIN
Dans les faits, mes tests se sont faits de multiples fois sur une table qui a donc déjà été mise à jour au fil des tests, je n'avais pas effacé le champ, ceci explique peut-être les fameuse 0 lignes mises à jour au final ? Mea culpa.
Aucune des tables n'a été alimentée avant cette mise à jour.
Je vais relancer des tests pour voir ce que mes dernières modifications donnent.
Pour ce qui est de la config de postgres sur un windows, je cherche juste à faire au mieux. A l'impossible nul n'est tenu ;-)
Hors ligne
Oups, je n'avais pas vu passer votre réponse ruizsebastien...
checkpoint_segments et checkpoint_timeout ... modifications et tests en cours.
Je travaille sur ma machine windows car c'est ma machine de tous les jours et qu'il est plus pratique pour moi de faire ainsi.
Je pourrais effectivement ajouter un Linux sur ma machine :
- install d'un double boot : oui, mais je ne peux plus travailler sur mes applications windows (je suis géomaticien)
- machnie virtuelle : oui, mais avec ma configuration mémoire actuelle (8GB) WINDOWS + LINUX + POSTGRES j'ai peur que ça commence à faire beaucoup... à moins bien entendu d'augmenter la mémoire. Le travail qui génère mes questions actuelles n'est que temporaire et je n'ai pas toujours à solliciter mon pc pour de telles tâches.
J'ai un serveur Linux avec postgres dessus, mais c'est un serveur qui est en charge. Je ne voudrais pas le paralyser pour mes traitements "persos" et bloquer tout le monde derrière, en sachant que j'ai plusieurs dizaines de requêtes du même type que celle qui nous occupe à réaliser. :-)
Hors ligne
Bonjour,
une alternative possible, intéressante ou pas en fonction de comment sont alimentées les tables sources (fréquences et volume), est de remplacer
ces calculs lourds par des triggers qui mettent à jour le ou les calculs au fil de l'eau. À voir...
Éric
Hors ligne
En effet.
Dans le cas présent il s'agit d'une base de données cadastrale qui est intégrée annuellement, d'un bloc. La création de nouveaux champs, et leur mise à jour se fait donc d'un bloc aussi.
En tous cas merci pour la proposition :-)
Hors ligne
Voici donc le résultat des dernières modifications (checkpoint_segments et checkpoint_timeout).
Celles-ci ont donc encore amélioré les choses. il faut désormais moins de 2h pour que la requête s'exécute.
"Update on edi_parc_uf (cost=835983.54..1332141.61 rows=977854 width=1522) (actual time=6838898.978..6838898.978 rows=0 loops=1)"
" Buffers: shared hit=33420101 read=780370 dirtied=4330486, temp read=157346 written=157332"
" CTE ident_surf_bati_uf"
" -> HashAggregate (cost=339152.96..348931.50 rows=977854 width=76) (actual time=60984.287..62757.712 rows=2064209 loops=1)"
" Buffers: shared hit=63581 read=266476"
" -> Seq Scan on edi_parc (cost=0.00..298628.54 rows=3241954 width=76) (actual time=6.372..52798.937 rows=3241824 loops=1)"
" Buffers: shared read=266209"
" -> Hash Join (cost=487052.04..983210.10 rows=977854 width=1522) (actual time=100525.498..242831.533 rows=2064208 loops=1)"
" Hash Cond: (ident_surf_bati_uf.id_uf = edi_parc_uf.id_centr)"
" Buffers: shared hit=167710 read=401011, temp read=157346 written=157332"
" -> CTE Scan on ident_surf_bati_uf (cost=0.00..19557.08 rows=977854 width=576) (actual time=60984.305..64211.110 rows=2064209 loops=1)"
" Buffers: shared hit=63581 read=266476"
" -> Hash (cost=154776.24..154776.24 rows=2064624 width=1190) (actual time=39541.053..39541.053 rows=2065094 loops=1)"
" Buckets: 65536 Batches: 8 Memory Usage: 131831kB"
" Buffers: shared hit=37971 read=134535, temp written=112375"
" -> Seq Scan on edi_parc_uf (cost=0.00..154776.24 rows=2064624 width=1190) (actual time=0.013..33909.582 rows=2065094 loops=1)"
" Buffers: shared read=134130"
"Total runtime: 6863807.552 ms"
J'imagine, vu le nombre de paramètres disponibles dans la configuration de postgres, qu'il est encore possible d'améliorer les choses, mais vous m'avez tous permis de sérieuses améliorations dans mes temps de calcul !!! 3h30 à moins de 2h...
Un grand merci à tous.
Après exploration des logs pour voir si celui-ci remonte des erreurs de type "checkpoints are occurring too frequently", celles-ci remontent en effet. Je vais tester une valeur à 60.
Hors ligne
Pensez à remettre les paramètres checkpoint_segments et checkpoint_timeout à des valeurs "normales" en dehors des périodes où vous faites vos update.
Cordialement,
Sébastien.
Hors ligne
Alors là c'est impressionnant... le checkpoint_segments monté à 60 me fait passer les requête de 2h à ... 10 min !!!
Si je comprends bien, cette valeur augmente sensiblement la rapidité des requêtes en réduisant la fréquence des accès disque, mais en prenant le risque de pertes plus importantes de données en cas de crash de la bdd et donc de plus de temps pour son redémarrage/reprise de son état avant crash ?
Dans mon cas (seul utilisateur de la bdd) s'il y a "crash", et données à rejouer, elles ne devraient concerner que la requête en cours d’exécution... Vu les gains de performance !!!
Hors ligne
Oui la fréquence des checkpoints est plus espacée ce qui fait qu'ils interfèrent moins avec les requêtes. Dans votre cas j'imagine que l'augmentation est telle qu'aucun checkpoint
n'intervient pendant la requête qui peut profiter à 100% de la bande passante ainsi libérée.
L'augmentation de la fréquence des checkpoints n'augmente pas a priori le risque de pertes de données. Par contre il augmente le volume de données que chaque checkpoint aura
à traiter (écrire sur disque). Son impact sera à ce moment là plus important (notamment sur les malheureuses requêtes qui auront la malchance d'être effectuées en même temps).
Et en cas de crash le volume à rejouer des journaux de transaction sera plus important parce que les données qu'ils contiennent n'auront pas été écrites dans les tables de données.
Par contre j'aimerais bien qu'on m'explique en quoi la requête présentée remplie le journal de trasaction au point de déclencher le chekpoint.
Éric
Hors ligne
La requête met à jour des lignes, donc les mises à jour sont stockées préalablement dans les journaux de transactions. Donc normal qu'un checkpoint finisse par s'exécuter.
L'autre gros intérêt de diminuer le nombre de checkpoints est de moins écrire dans les journaux de transactions. Après un checkpoint, PostgreSQL doit écrire le bloc complet pour chaque bloc modifié une première fois. Ensuite, il n'enregistre que des deltas de blocs. Donc plus on les espace, plus on enregistre des deltas. Plus on les rapproche, plus on écrit des blocs complets. Et cette différence joue beaucoup.
En ce qui me concerne, je garderais le checkpoint_segments à 60 en permanence. Ça ne représente aucun risque (en dehors d'un démarrage plus long après un crash).
Guillaume.
Hors ligne