Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Header And Logo
|
Site Navigation
Problème d'update et de performance
- From: Valérie SCHNEIDER <valerie(dot)schneider(at)meteo(dot)fr>
- To: pgsql-fr-generale <pgsql-fr-generale(at)postgresql(dot)org>
- Cc: Pierre <pierre(dot)dupre(at)meteo(dot)fr>, AMOI <valerie(dot)schneider(at)meteo(dot)fr>
- Subject: Problème d'update et de performance
- Date: Fri, 16 May 2008 09:15:51 +0000
- Message-id: <1210929352(dot)23096(dot)215(dot)camel(at)nazar(dot)meteo(dot)fr>
Bonjour,
Nous observons un comportement curieux d'une série d'update sur une base
PG. Je suis preneur d'explication si vous en avez ...
Voilà : il s'agit d'une base PG 8.3.1 sur serveur linux RedHat 5.1 64
bit avec 4 Go de RAM :
Date: mer mai 14 09:38:14 GMT 2008
Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64
GNU/Linux
Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
Version Postgresql: 8.3.1
Au niveau de postgresql .conf :
# - Memory -
shared_buffers = 1024MB # min 128kB or
max_connections*16kB
# - Checkpoints -
checkpoint_segments = 10 # in logfile segments, min 1,
16MB each
# pour les vacuum
maintenance_work_mem = 256MB # min 1MB
# Pour les operations de tri
work_mem = 16MB # min 64kB
# memoire partagee utilisee par une transaction typique.
wal_buffers = 1024kB # min 32kB
autovacuum = off # enable autovacuum subprocess?
Un cron effectue des analyze sur les tables à intervalles choisis.
On effectue un update sur une table de 5 millions de lignes, de taille
environ 3Go, portant sur environ 50000 lignes, selon des critères
utilisant un index.
En exécutant plusieurs fois à la suite le même update (donc à partir du
second plus aucune ligne n'est mise à jour) on observe des temps très
longs pour finalement tomber à quelques millisecondes (qui est le
résultat attendu).
Que se passe-t'il d'après vous ?
Ci-dessous en pièce jointe la description de la table, des index, et une
série d'explain analyze update.
Merci !
Valérie.
--
********************************************************************
* Les points de vue exprimes sont strictement personnels et *
* n'engagent pas la responsabilite de METEO-FRANCE. *
********************************************************************
* Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
* METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
* 42, avenue G. Coriolis Email : Valerie(dot)Schneider(at)meteo(dot)fr *
* 31057 TOULOUSE Cedex 1 - FRANCE http://www.meteo.fr *
********************************************************************
Date: mer mai 14 09:38:14 GMT 2008
Système Linux: Linux TDIFINTG 2.6.18-53.el5xen #1 SMP
Wed Oct 10 16:48:44 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
Redhat-Release: Red Hat Enterprise Linux Server release 5.1 (Tikanga)
Version Postgresql: 8.3.1
Description de la table diffusion_2008_05_13:
Table « archive.diffusion_2008_05_13 »
Colonne | Type | Modificateurs
---------------------------------+-----------------------------+---------------
id | bigint | not null
id_product | bigint | not null
diffusion_externalid | character varying(120) |
subscriber_id | bigint |
subscriber_name | character varying(50) |
client_id | bigint |
client_name | character varying(50) |
check_diffusion | character varying(1) | not null
priority | integer | not null
format | character varying(30) |
diffusion_format | character varying(30) |
diffusion_standby_format | character varying(30) |
auto_convert_format | character varying(30) |
auto_convert_standby_format | character varying(30) |
file_name | character varying(256) | not null
file_size | bigint | not null
format_id | bigint | not null
format_part_number | integer | not null
format_archive | character varying(1) | not null
allow_auto_convert | character varying(1) | not null
ogc_product_id | character varying(100) |
rhapsodie_product_id | character varying(100) |
other_product_id | character varying(100) |
return_mode | character varying(10) | not null
return_ftp_host | character varying(60) |
return_ftp_user | character varying(100) |
return_ftp_passwd | character varying(200) |
return_ftp_directory | character varying(512) |
return_ftp_use_size | character varying(1) |
return_ftp_passive | character varying(1) |
return_ftp_port | integer |
return_email_adress | character varying(160) |
return_email_secure_out1 | character varying(160) |
return_email_secure_out2 | character varying(160) |
second_return_error | character varying(1) | not null
second_return_acq | character varying(1) | not null
second_return_mode | character varying(10) | not null
second_return_ftp_host | character varying(60) |
second_return_ftp_user | character varying(100) |
second_return_ftp_passwd | character varying(200) |
second_return_ftp_directory | character varying(512) |
second_return_ftp_use_size | character varying(1) |
second_return_ftp_passive | character varying(1) |
second_return_ftp_port | integer |
second_return_email_adress | character varying(160) |
second_return_email_secure_out1 | character varying(160) |
second_return_email_secure_out2 | character varying(160) |
check_cron | character varying(1) | not null
cron_tz | character varying(6) |
cron_minute | character varying(180) |
cron_hour | character varying(72) |
cron_day_of_month | character varying(93) |
cron_month | character varying(36) |
cron_day_of_week | character varying(24) |
cron_open_delay | integer |
dont_send_between_tz | character varying(6) |
dont_send_between_begin | character varying(5) |
dont_send_between_end | character varying(5) |
channel | character varying(30) | not null
media | character varying(15) | not null
pdif1 | character varying |
pdif2 | character varying |
pdif3 | character varying |
pdif4 | character varying |
pdif5 | character varying |
pdif6 | character varying |
pdif7 | character varying |
pdif8 | character varying |
pdif9 | character varying |
pdif10 | character varying |
pdif11 | character varying |
pdif12 | character varying |
pdif13 | character varying |
pdif14 | character varying |
pdif15 | character varying |
pdif16 | character varying |
pdif17 | character varying |
pdif18 | character varying |
pdif19 | character varying |
pdif20 | character varying |
standby_channel | character varying(30) |
standby_media | character varying(15) |
switch_method_medias_ftp | character varying(15) |
standby_switch_try_number | integer |
standby_switch_try_date | timestamp without time zone |
standby_pdif1 | character varying |
standby_pdif2 | character varying |
standby_pdif3 | character varying |
standby_pdif4 | character varying |
standby_pdif5 | character varying |
standby_pdif6 | character varying |
standby_pdif7 | character varying |
standby_pdif8 | character varying |
standby_pdif9 | character varying |
standby_pdif10 | character varying |
standby_pdif11 | character varying |
standby_pdif12 | character varying |
standby_pdif13 | character varying |
standby_pdif14 | character varying |
standby_pdif15 | character varying |
standby_pdif16 | character varying |
standby_pdif17 | character varying |
standby_pdif18 | character varying |
standby_pdif19 | character varying |
standby_pdif20 | character varying |
state | integer | not null
state_date | timestamp without time zone | not null
last_try_date | timestamp without time zone |
next_try_date | timestamp without time zone |
try_number | integer | not null
next_try_channel | character varying(30) | not null
next_try_key | character varying(2048) | not null
next_try_is_main_channel | character varying(1) | not null
diffusion_date | timestamp without time zone |
provider_diffusion_date | timestamp without time zone |
diffusion_size | bigint | not null
diffusion_time | integer |
diffusion_format_id | bigint | not null
diffusion_format_part_number | integer | not null
diffusion_log | character varying |
Index :
« pk_diffusion_2008_05_13_id » PRIMARY KEY, btree (id)
« indx_diffusion_2008_05_13_channel » btree (state, next_try_channel varchar_pattern_ops, next_try_key varchar_pattern_ops, priority, next_try_date)
« indx_diffusion_2008_05_13_id_product » btree (id_product), tablespace « archive »
« indx_diffusion_2008_05_13_idx1 » btree (next_try_channel, state, next_try_key), tablespace « archive »
« indx_diffusion_2008_05_13_state_format » btree (state, format varchar_pattern_ops, priority, state_date), tablespace « archive »
« indx_diffusion_2008_05_13_subscriber_name » btree (lower(subscriber_name::text) varchar_pattern_ops), tablespace « archive »
Contraintes de vérification :
« ck_diffusion_2008_05_13_allow_auto_convert » CHECK (allow_auto_convert::text = ANY (ARRAY['0'::character varying, '1'::character varying]::text[]))
« ck_diffusion_2008_05_13_check_cron » CHECK (check_cron::text = ANY (ARRAY['0'::character varying, '1'::character varying]::text[]))
« ck_diffusion_2008_05_13_check_diffusion » CHECK (check_diffusion::text = ANY (ARRAY['0'::character varying, '1'::character varying]::text[]))
« ck_diffusion_2008_05_13_cron_tz » CHECK (cron_tz::text ~ '^([+-][01][0-9]:[0-5][0-9]|[+-]2[0-3]:[0-5][0-9])$'::text)
« ck_diffusion_2008_05_13_diffusion_externalid » CHECK (diffusion_externalid::text ~ '^[a-zA-Z0-9][a-zA-Z0-9_,+\\.-]+$'::text)
« ck_diffusion_2008_05_13_diffusion_format_id » CHECK (diffusion_format_id > 0)
« ck_diffusion_2008_05_13_format_archive » CHECK (format_archive::text = ANY (ARRAY['0'::character varying, '1'::character varying]::text[]))
« ck_diffusion_2008_05_13_format_id » CHECK (format_id > 0)
« ck_diffusion_2008_05_13_id » CHECK (id > 0)
« ck_diffusion_2008_05_13_next_try_is_main_channel » CHECK (next_try_is_main_channel::text = ANY (ARRAY['0'::character varying, '1'::character varying]::text[]))
« ck_diffusion_2008_05_13_priority » CHECK (priority >= 81 AND priority <= 89)
« ck_diffusion_2008_05_13_return_mode » CHECK (return_mode::text = ANY (ARRAY['FTP'::character varying, 'EMAIL'::character varying, 'NONE'::character varying]::text[]))
« ck_diffusion_2008_05_13_second_return_mode » CHECK (second_return_mode::text = ANY (ARRAY['NONE'::character varying, 'FTP'::character varying, 'EMAIL'::character varying]::text[]))
« ck_diffusion_2008_05_13_state » CHECK (state > 0)
Contraintes de clés étrangères :
« fk_diffusion_2008_05_13_id_product » FOREIGN KEY (id_product) REFERENCES product_2008_05_13(id)
Tablespace : « archive »
================================================================================
prompt-unix> psql difmet archive
Bienvenue dans psql 8.3.1, l'interface interactive de PostgreSQL.
difmet=> explain analyze update diffusion_2008_05_13 set state = 3001 where state = 2101 and next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a+datos_a+21';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using indx_diffusion_2008_05_13_channel on diffusion_2008_05_13 (cost=0.00..10.41 rows=1 width=7379) (actual time=12.712..169200.761 rows=48041 loops=1)
Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~ 'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND ((next_try_key)::text ~<~ 'uatot'::text))
Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND ((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text))
Total runtime: 858616.959 ms
(4 lignes)
difmet=> explain analyze update diffusion_2008_05_13 set state = 3001 where state = 2101 and next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a+datos_a+21';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using indx_diffusion_2008_05_13_channel on diffusion_2008_05_13 (cost=0.00..10.43 rows=1 width=7379) (actual time=11440.118..11440.118 rows=0 loops=1)
Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~ 'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND ((next_try_key)::text ~<~ 'uatot'::text))
Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND ((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text))
Total runtime: 11440.215 ms
(4 lignes)
difmet=> explain analyze update diffusion_2008_05_13 set state = 3001 where state = 2101 and next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a+datos_a+21';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using indx_diffusion_2008_05_13_channel on diffusion_2008_05_13 (cost=0.00..10.43 rows=1 width=7379) (actual time=365160.217..365160.217 rows=0 loops=1)
Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~ 'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND ((next_try_key)::text ~<~ 'uatot'::text))
Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND ((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text))
Total runtime: 365160.313 ms
(4 lignes)
difmet=> explain analyze update diffusion_2008_05_13 set state = 3001 where state = 2101 and next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a+datos_a+21';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using indx_diffusion_2008_05_13_channel on diffusion_2008_05_13 (cost=0.00..10.43 rows=1 width=7379) (actual time=2.859..2.859 rows=0 loops=1)
Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~ 'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND ((next_try_key)::text ~<~ 'uatot'::text))
Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND ((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text))
Total runtime: 2.954 ms
(4 lignes)
difmet=> explain analyze update diffusion_2008_05_13 set state = 3001 where state = 2101 and next_try_channel like 'FTP' and next_try_key like 'uatos_a+host_atos_a+datos_a+21';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using indx_diffusion_2008_05_13_channel on diffusion_2008_05_13 (cost=0.00..10.43 rows=1 width=7379) (actual time=2.591..2.591 rows=0 loops=1)
Index Cond: ((state = 2101) AND ((next_try_channel)::text ~=~ 'FTP'::text) AND ((next_try_key)::text ~>=~ 'uatos'::text) AND ((next_try_key)::text ~<~ 'uatot'::text))
Filter: (((next_try_channel)::text ~~ 'FTP'::text) AND ((next_try_key)::text ~~ 'uatos_a+host_atos_a+datos_a+21'::text))
Total runtime: 2.680 ms
(4 lignes)
- Follow-Ups:
- Re: Problème d'update et de performance
- From: Cédric Villemain
- Re: Problème d'update et de performan ce
- From: Jean-Max Reymond
- Re: Problème d'update et de performance
- From: Valérie SCHNEIDER
- Re: Problème d'update et de performance
- From: Alain
- Re: Problème d'update et de performance
- Prev by Date: RMLL 2008
- Next by Date: Re: Problème d'update et de performance
- Previous by thread: RMLL 2008
- Next by thread: Re: Problème d'update et de performance
- Indexes: