Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 8538C63223B for ; Tue, 17 Aug 2010 03:59:32 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 09843-10 for ; Tue, 17 Aug 2010 06:59:24 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.sdb.it (mail.sdb.it [212.141.11.2]) by mail.postgresql.org (Postfix) with ESMTP id EFE6D6321BA for ; Tue, 17 Aug 2010 03:59:23 -0300 (ADT) Received: by mail.sdb.it (Postfix, from userid 500) id 31C67818C; Tue, 17 Aug 2010 08:59:22 +0200 (CEST) Received: from geronimo.sdb.it (geronimo.sdb.it [10.101.1.20]) by mail.sdb.it (Postfix) with ESMTP id 27A4B818B; Tue, 17 Aug 2010 08:59:22 +0200 (CEST) Received: from mailtech.sdb.it (sdbit-srv01.sdb.it [10.118.122.250]) by geronimo.sdb.it (Postfix) with ESMTP id 08C263808A2; Tue, 17 Aug 2010 08:59:03 +0200 (CEST) Received: from [127.0.0.1] (darkstar.sdb.it [10.118.101.90]) by mailtech.sdb.it (Postfix) with ESMTP id F26D058ABC; Tue, 17 Aug 2010 08:59:21 +0200 (CEST) Message-ID: <4C6A32F8.1070808@savinodelbene.com> Date: Tue, 17 Aug 2010 08:58:00 +0200 From: Edoardo Innocenti Reply-To: edoardo.innocenti@savinodelbene.com Organization: SDB Information Technology S.r.l. User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; it; rv:1.9.1.11) Gecko/20100711 Lightning/1.0b1 Thunderbird/3.0.6 MIME-Version: 1.0 To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: TopMemoryContext - Configuration Mistake? References: <4C6932DF.90102@savinodelbene.com> <5923.1281974354@sss.pgh.pa.us> In-Reply-To: <5923.1281974354@sss.pgh.pa.us> Content-Type: text/html; charset=ISO-8859-15 Content-Transfer-Encoding: 8bit X-Private-Material: Please read notice X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.524 tagged_above=-10 required=5 tests=BAYES_50=0.8, HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.723 X-Spam-Level: * X-Archive-Number: 201008/127 X-Sequence-Number: 35376 Il 16/08/2010 17:59, Tom Lane ha scritto:

Edoardo Innocenti <edoardo.innocenti@savinodelbene.com> writes:

I got the following error during a select query.<br>

What was the query, what does EXPLAIN show as the plan for it,
and which PG version is your server exactly?

The database running on a CenOS 5.1 (Linux xxxxxxx 2.6.18-53.el5 #1 SMP Mon Nov 12 02:14:55 EST 2007 x86_64 x86_64 x86_64 GNU/Linux ), PostgreSQL version is 8.3.8 (postgresql-8.3.8-1PGDG.rhel5, postgresql-contrib-8.3.8-1PGDG.rhel5, postgresql-libs-8.3.8-1PGDG.rhel5, compat-postgresql-libs-4-1PGDG.rhel5, postgresql-server-8.3.8-1PGDG.rhel5)

Query Plan:
"HashAggregate (cost=4910.06..4910.19 rows=1 width=659)"
" -> Nested Loop (cost=84.49..4909.99 rows=1 width=659)"
" -> Nested Loop (cost=84.49..4901.70 rows=1 width=655)"
" Join Filter: (aer_d.zon_mar = zmar.codigo)"
" -> Nested Loop (cost=84.49..4900.03 rows=1 width=601)"
" Join Filter: (merca.grupo = tipmer.codigo)"
" -> Nested Loop (cost=84.49..4898.56 rows=1 width=574)"
" -> Nested Loop (cost=84.49..4890.27 rows=1 width=530)"
" Join Filter: (zmar4.codigo = aer_d.zon_mar)"
" -> Nested Loop (cost=84.49..4888.60 rows=1 width=522)"
" -> Nested Loop (cost=84.49..4880.32 rows=1 width=514)"
" Join Filter: (oec.progr_ctnr = oem.progr_ctnr)"
" -> Nested Loop (cost=84.49..4807.00 rows=3 width=514)"
" -> Nested Loop (cost=84.49..4782.15 rows=3 width=494)"
" -> Nested Loop (cost=84.49..4757.31 rows=3 width=495)"
" -> Nested Loop (cost=84.49..4732.46 rows=3 width=451)"
" Join Filter: (aer_l.zon_mar = zmar3.codigo)"
" -> Nested Loop (cost=84.49..4727.44 rows=3 width=459)"
" -> Nested Loop (cost=84.49..4726.60 rows=3 width=389)"
" -> Nested Loop (cost=84.49..4705.74 rows=3 width=337)"
" -> Nested Loop (cost=84.49..4680.89 rows=3 width=293)"
" -> Nested Loop (cost=84.49..4680.16 rows=1 width=249)"
" -> Hash Join (cost=84.49..4676.45 rows=1 width=249)"
" Hash Cond: ((oes.aero_ori = oevi.port_loading) AND (oes.vessel_code = oevi.vessel_code))"
" -> Hash Left Join (cost=76.20..4502.78 rows=22050 width=233)"
" Hash Cond: ((oes.azienda = refs.azienda) AND (oes.hbl = refs.house) AND (oes.expediente = refs.reference))"
" -> Hash Left Join (cost=38.10..3637.70 rows=22050 width=180)"
" Hash Cond: ((oes.azienda = refs2.azienda) AND (oes.hbl = refs2.house) AND (oes.expediente = refs2.reference))"
" -> Seq Scan on oe_sped_t oes (cost=0.00..2772.62 rows=22050 width=119)"
" Filter: (azienda = '60'::bpchar)"
" -> Hash (cost=28.12..28.12 rows=570 width=92)"
" -> Seq Scan on ref_sales refs2 (cost=0.00..28.12 rows=570 width=92)"
" Filter: (azienda = '60'::bpchar)"
" -> Hash (cost=28.12..28.12 rows=570 width=92)"
" -> Seq Scan on ref_sales refs (cost=0.00..28.12 rows=570 width=92)"
" Filter: (azienda = '60'::bpchar)"
" -> Hash (cost=8.27..8.27 rows=1 width=20)"
" -> Index Scan using oevi_1 on oe_vessel_imbarco oevi (cost=0.00..8.27 rows=1 width=20)"
" Index Cond: ((departure >= '2010-07-01'::date) AND (departure <= '2010-07-31'::date))"
" Filter: (azienda = '60'::bpchar)"
" -> Index Scan using m_aeropu_pkey on m_aeropu aer_l (cost=0.00..3.70 rows=1 width=16)"
" Index Cond: (aer_l.codigo = oes.aero_ori)"
" -> Index Scan using oe_container_booking_nr_progr_ctnr_azienda_key on oe_container oec (cost=0.00..0.67 rows=5 width=48)"
" Index Cond: ((oec.booking_nr = oes.booking_nr) AND (oec.azienda = '60'::bpchar))"
" -> Index Scan using m_cli_pkey on m_cli fab (cost=0.00..8.27 rows=1 width=60)"
" Index Cond: (fab.codigo = oes.cod_fab)"
" -> Index Scan using oe_vessel_t_pkey on oe_vessel_t oev (cost=0.00..6.94 rows=1 width=64)"
" Index Cond: ((oev.vessel_code = oes.vessel_code) AND (oev.azienda = '60'::bpchar))"
" -> Index Scan using m_zonmar_pkey on m_zonmar zmar2 (cost=0.00..0.27 rows=1 width=70)"
" Index Cond: (zmar2.codigo = aer_l.zon_mar)"
" -> Seq Scan on m_zonmar zmar3 (cost=0.00..1.30 rows=30 width=8)"
" -> Index Scan using m_cli_pkey on m_cli agent (cost=0.00..8.27 rows=1 width=60)"
" Index Cond: (agent.codigo = oes.agen_des)"
" -> Index Scan using m_merca_pkey on m_merca merca (cost=0.00..8.27 rows=1 width=11)"
" Index Cond: (merca.codigo = oes.tip_mer)"
" -> Index Scan using m_cianav_pkey on m_cianav cia2 (cost=0.00..8.27 rows=1 width=24)"
" Index Cond: ((cia2.codigo = oev.carrier) AND (cia2.azienda = '60'::bpchar))"
" -> Index Scan using oe_sped_m_pkey on oe_sped_m oem (cost=0.00..24.36 rows=5 width=20)"
" Index Cond: ((oem.entry_nr = oes.entry_nr) AND (oem.azienda = '60'::bpchar))"
" -> Index Scan using m_aeropu_pkey on m_aeropu aer_d (cost=0.00..8.27 rows=1 width=16)"
" Index Cond: (aer_d.codigo = oes.aero_des)"
" -> Seq Scan on m_zonmar zmar4 (cost=0.00..1.30 rows=30 width=8)"
" -> Index Scan using m_cli_pkey on m_cli cons (cost=0.00..8.27 rows=1 width=60)"
" Index Cond: (cons.codigo = oes.cod_des)"
" -> Seq Scan on m_tipmer tipmer (cost=0.00..1.21 rows=21 width=37)"
" -> Seq Scan on m_zonmar zmar (cost=0.00..1.30 rows=30 width=70)"
" -> Index Scan using m_cianav_pkey on m_cianav cia (cost=0.00..8.27 rows=1 width=48)"
" Index Cond: ((cia.codigo = oev.carrier) AND (cia.azienda = '60'::bpchar))"

This is db parameters:

              name               |                setting
---------------------------------+---------------------------------------
add_missing_from                | off
allow_system_table_mods         | off
archive_command                 | (disabled)
archive_mode                    | off
archive_timeout                 | 0
N seconds.
array_nulls                     | on
authentication_timeout          | 1min
autovacuum                      | on
autovacuum_analyze_scale_factor | 0.1
eltuples.
autovacuum_analyze_threshold    | 50
autovacuum_freeze_max_age       | 200000000
autovacuum_max_workers          | 3
autovacuum_naptime              | 1min
autovacuum_vacuum_cost_delay    | 20ms
autovacuum_vacuum_cost_limit    | -1
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold     | 50
backslash_quote                 | safe_encoding
bgwriter_delay                  | 200ms
bgwriter_lru_maxpages           | 100
bgwriter_lru_multiplier         | 2
block_size                      | 8192
bonjour_name                    |
check_function_bodies           | on
checkpoint_completion_target    | 0.5
interval.
checkpoint_segments             | 3
checkpoint_timeout              | 5min
checkpoint_warning              | 30s
client_encoding                 | UTF8
client_min_messages             | notice
commit_delay                    | 0
isk.
commit_siblings                 | 5
config_file                     | /var/lib/pgsql/data/postgresql.conf
constraint_exclusion            | off
cpu_index_tuple_cost            | 0.005
n index scan.
cpu_operator_cost               | 0.0025
call.
cpu_tuple_cost                  | 0.01
custom_variable_classes         |
data_directory                  | /var/lib/pgsql/data
DateStyle                       | ISO, MDY
db_user_namespace               | off
deadlock_timeout                | 1s
debug_assertions                | off
debug_pretty_print              | off
debug_print_parse               | off
debug_print_plan                | off
debug_print_rewritten           | off
default_statistics_target       | 10
default_tablespace              |
default_text_search_config      | pg_catalog.english
default_transaction_isolation   | read committed
default_transaction_read_only   | off
default_with_oids               | off
dynamic_library_path            | $libdir
effective_cache_size            | 4048MB
enable_bitmapscan               | on
enable_hashagg                  | on
enable_hashjoin                 | on
enable_indexscan                | on
enable_mergejoin                | on
enable_nestloop                 | on
enable_seqscan                  | on
enable_sort                     | on
enable_tidscan                  | on
escape_string_warning           | on
explain_pretty_print            | on
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_selection_bias             | 2
geqo_threshold                  | 12
gin_fuzzy_search_limit          | 0
hba_file                        | /var/lib/pgsql/data/pg_hba.conf
ident_file                      | /var/lib/pgsql/data/pg_ident.conf
ignore_system_indexes           | off
integer_datetimes               | off
join_collapse_limit             | 8
krb_caseins_users               | off
ve.
krb_realm                       |
krb_server_hostname             |
krb_server_keyfile              | FILE:/etc/sysconfig/pgsql/krb5.keytab
krb_srvname                     | postgres
lc_collate                      | en_US.UTF-8
lc_ctype                        | en_US.UTF-8
lc_messages                     | en_US.UTF-8
lc_monetary                     | en_US.UTF-8
lc_numeric                      | en_US.UTF-8
lc_time                         | en_US.UTF-8
listen_addresses                | *
local_preload_libraries         |
log_autovacuum_min_duration     | -1
log_checkpoints                 | on
log_connections                 | on
log_destination                 | stderr
log_directory                   | pg_log
log_disconnections              | on
log_duration                    | on
log_error_verbosity             | default
log_executor_stats              | off
log_filename                    | postgresql-%Y-%m-%d_%H%M%S.log
log_hostname                    | on
log_line_prefix                 | %t [%p]: [%l-1]
log_lock_waits                  | on
log_min_duration_statement      | -1
log_min_error_statement         | error
log_min_messages                | notice
log_parser_stats                | off
log_planner_stats               | off
log_rotation_age                | 1d
log_rotation_size               | 100MB
log_statement                   | all
log_statement_stats             | off
log_temp_files                  | -1
log_timezone                    | Europe/Rome
log_truncate_on_rotation        | on
logging_collector               | on
maintenance_work_mem            | 16MB
max_connections                 | 640
max_files_per_process           | 1000
max_fsm_pages                   | 204800
max_fsm_relations               | 1000
max_function_args               | 100
max_identifier_length           | 63
max_index_keys                  | 32
max_locks_per_transaction       | 64
max_prepared_transactions       | 5
max_stack_depth                 | 2MB
password_encryption             | on
port                            | 5432
post_auth_delay                 | 0
pre_auth_delay                  | 0
random_page_cost                | 4
regex_flavor                    | advanced
search_path                     | "$user",public
seq_page_cost                   | 1
server_encoding                 | UTF8
server_version                  | 8.3.8
server_version_num              | 80308
session_replication_role        | origin
shared_buffers                  | 2GB
shared_preload_libraries        |
silent_mode                     | off
sql_inheritance                 | on
ssl                             | off
ssl_ciphers                     | ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH
standard_conforming_strings     | off
statement_timeout               | 0
superuser_reserved_connections | 3
synchronize_seqscans            | on
synchronous_commit              | on
syslog_facility                 | LOCAL0
syslog_ident                    | postgres
tcp_keepalives_count            | 0
tcp_keepalives_idle             | 0
tcp_keepalives_interval         | 0
temp_buffers                    | 2048
temp_tablespaces                |
TimeZone                        | Europe/Rome
timezone_abbreviations          | Default
trace_notify                    | off
trace_sort                      | off
track_activities                | on
track_counts                    | on
transaction_isolation           | read committed
transaction_read_only           | off
transform_null_equals           | off
unix_socket_directory           |
unix_socket_group               |
unix_socket_permissions         | 511
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_freeze_min_age           | 100000000
wal_buffers                     | 64kB
wal_sync_method                 | fdatasync
wal_writer_delay                | 200ms
work_mem                        | 16MB
xmlbinary                       | base64
xmloption                       | content
zero_damaged_pages              | off

  MessageContext: 2042626048 total in 256 blocks; 16072 free (7
chunks); 2042609976 used<br>

This looks like it could be a memory leak, but we'd need to be able
to reproduce the problem in order to investigate or fix it.

This issue is not deterministic, sometime it append (with TopMemoryContext) sometime not. Can I fix it by increasing work_mem?

Thanks

PS: I cannot give you the data for privace issue

			regards, tom lane

Edoardo Innocenti
Infrastructure Coordinator

SDB Information Technology
Phone: +39.055.3811222
Fax:   +39.055.5201411

disclaimer-sdb

Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.