| From: | Edoardo Innocenti <edoardo(dot)innocenti(at)savinodelbene(dot)com> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | pgsql-admin(at)postgresql(dot)org |
| Subject: | Re: TopMemoryContext - Configuration Mistake? |
| Date: | 2010-08-17 06:58:00 |
| Message-ID: | 4C6A32F8.1070808@savinodelbene.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-15"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Il 16/08/2010 17:59, Tom Lane ha scritto:
<blockquote cite="mid:5923(dot)1281974354(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">Edoardo Innocenti <a class="moz-txt-link-rfc2396E" href="mailto:edoardo(dot)innocenti(at)savinodelbene(dot)com"><edoardo(dot)innocenti(at)savinodelbene(dot)com></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">I got the following error during a select query.<br>
</pre>
</blockquote>
<pre wrap="">
What was the query, what does EXPLAIN show as the plan for it,
and which PG version is your server exactly?
</pre>
</blockquote>
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)<br>
<br>
Query Plan:<br>
"HashAggregate (cost=4910.06..4910.19 rows=1 width=659)"
<br>
" -> Nested Loop (cost=84.49..4909.99 rows=1 width=659)"
<br>
" -> Nested Loop (cost=84.49..4901.70 rows=1 width=655)"
<br>
" Join Filter: (aer_d.zon_mar = zmar.codigo)"
<br>
" -> Nested Loop (cost=84.49..4900.03 rows=1 width=601)"
<br>
" Join Filter: (merca.grupo = tipmer.codigo)"
<br>
" -> Nested Loop (cost=84.49..4898.56 rows=1 width=574)"
<br>
" -> Nested Loop (cost=84.49..4890.27 rows=1 width=530)"
<br>
" Join Filter: (zmar4.codigo = aer_d.zon_mar)"
<br>
" -> Nested Loop (cost=84.49..4888.60 rows=1 width=522)"
<br>
" -> Nested Loop (cost=84.49..4880.32 rows=1 width=514)"
<br>
" Join Filter: (oec.progr_ctnr = oem.progr_ctnr)"
<br>
" -> Nested Loop (cost=84.49..4807.00 rows=3 width=514)"
<br>
" -> Nested Loop (cost=84.49..4782.15 rows=3 width=494)"
<br>
" -> Nested Loop (cost=84.49..4757.31 rows=3 width=495)"
<br>
" -> Nested Loop (cost=84.49..4732.46 rows=3 width=451)"
<br>
" Join Filter: (aer_l.zon_mar = zmar3.codigo)"
<br>
" -> Nested Loop (cost=84.49..4727.44 rows=3 width=459)"
<br>
" -> Nested Loop (cost=84.49..4726.60 rows=3 width=389)"
<br>
" -> Nested Loop (cost=84.49..4705.74 rows=3 width=337)"
<br>
" -> Nested Loop (cost=84.49..4680.89 rows=3 width=293)"
<br>
" -> Nested Loop (cost=84.49..4680.16 rows=1 width=249)"
<br>
" -> Hash Join (cost=84.49..4676.45 rows=1 width=249)"
<br>
" Hash Cond: ((oes.aero_ori = oevi.port_loading) AND (oes.vessel_code =
oevi.vessel_code))"
<br>
" -> Hash Left Join (cost=76.20..4502.78 rows=22050 width=233)"
<br>
" Hash Cond: ((oes.azienda = refs.azienda) AND (oes.hbl = refs.house)
AND (oes.expediente = refs.reference))"
<br>
" -> Hash Left Join (cost=38.10..3637.70 rows=22050 width=180)"
<br>
" Hash Cond: ((oes.azienda = refs2.azienda) AND (oes.hbl = refs2.house)
AND (oes.expediente = refs2.reference))"
<br>
" -> Seq Scan on oe_sped_t oes (cost=0.00..2772.62 rows=22050
width=119)"
<br>
" Filter: (azienda = '60'::bpchar)"
<br>
" -> Hash (cost=28.12..28.12 rows=570 width=92)"
<br>
" -> Seq Scan on ref_sales refs2 (cost=0.00..28.12 rows=570
width=92)"
<br>
" Filter: (azienda = '60'::bpchar)"
<br>
" -> Hash (cost=28.12..28.12 rows=570 width=92)"
<br>
" -> Seq Scan on ref_sales refs (cost=0.00..28.12 rows=570
width=92)"
<br>
" Filter: (azienda = '60'::bpchar)"
<br>
" -> Hash (cost=8.27..8.27 rows=1 width=20)"
<br>
" -> Index Scan using oevi_1 on oe_vessel_imbarco oevi
(cost=0.00..8.27 rows=1 width=20)"
<br>
" Index Cond: ((departure >= '2010-07-01'::date) AND (departure
<= '2010-07-31'::date))"
<br>
" Filter: (azienda = '60'::bpchar)"
<br>
" -> Index Scan using m_aeropu_pkey on m_aeropu aer_l
(cost=0.00..3.70 rows=1 width=16)"
<br>
" Index Cond: (aer_l.codigo = oes.aero_ori)"
<br>
" -> Index Scan using oe_container_booking_nr_progr_ctnr_azienda_key
on oe_container oec (cost=0.00..0.67 rows=5 width=48)"
<br>
" Index Cond: ((oec.booking_nr = oes.booking_nr) AND (oec.azienda =
'60'::bpchar))"
<br>
" -> Index Scan using m_cli_pkey on m_cli fab (cost=0.00..8.27
rows=1 width=60)"
<br>
" Index Cond: (fab.codigo = oes.cod_fab)"
<br>
" -> Index Scan using oe_vessel_t_pkey on oe_vessel_t oev
(cost=0.00..6.94 rows=1 width=64)"
<br>
" Index Cond: ((oev.vessel_code = oes.vessel_code) AND (oev.azienda =
'60'::bpchar))"
<br>
" -> Index Scan using m_zonmar_pkey on m_zonmar zmar2
(cost=0.00..0.27 rows=1 width=70)"
<br>
" Index Cond: (zmar2.codigo = aer_l.zon_mar)"
<br>
" -> Seq Scan on m_zonmar zmar3 (cost=0.00..1.30 rows=30 width=8)"
<br>
" -> Index Scan using m_cli_pkey on m_cli agent (cost=0.00..8.27
rows=1 width=60)"
<br>
" Index Cond: (agent.codigo = oes.agen_des)"
<br>
" -> Index Scan using m_merca_pkey on m_merca merca (cost=0.00..8.27
rows=1 width=11)"
<br>
" Index Cond: (merca.codigo = oes.tip_mer)"
<br>
" -> Index Scan using m_cianav_pkey on m_cianav cia2
(cost=0.00..8.27 rows=1 width=24)"
<br>
" Index Cond: ((cia2.codigo = oev.carrier) AND (cia2.azienda =
'60'::bpchar))"
<br>
" -> Index Scan using oe_sped_m_pkey on oe_sped_m oem
(cost=0.00..24.36 rows=5 width=20)"
<br>
" Index Cond: ((oem.entry_nr = oes.entry_nr) AND (oem.azienda =
'60'::bpchar))"
<br>
" -> Index Scan using m_aeropu_pkey on m_aeropu aer_d
(cost=0.00..8.27 rows=1 width=16)"
<br>
" Index Cond: (aer_d.codigo = oes.aero_des)"
<br>
" -> Seq Scan on m_zonmar zmar4 (cost=0.00..1.30 rows=30 width=8)"
<br>
" -> Index Scan using m_cli_pkey on m_cli cons (cost=0.00..8.27
rows=1 width=60)"
<br>
" Index Cond: (cons.codigo = oes.cod_des)"
<br>
" -> Seq Scan on m_tipmer tipmer (cost=0.00..1.21 rows=21 width=37)"
<br>
" -> Seq Scan on m_zonmar zmar (cost=0.00..1.30 rows=30 width=70)"
<br>
" -> Index Scan using m_cianav_pkey on m_cianav cia (cost=0.00..8.27
rows=1 width=48)"
<br>
" Index Cond: ((cia.codigo = oev.carrier) AND (cia.azienda =
'60'::bpchar))"<br>
<br>
<br>
This is db parameters:<br>
<br>
name |
setting <br>
---------------------------------+---------------------------------------<br>
add_missing_from |
off <br>
allow_system_table_mods |
off <br>
archive_command |
(disabled) <br>
archive_mode |
off <br>
archive_timeout |
0 <br>
N seconds.<br>
array_nulls |
on <br>
authentication_timeout |
1min <br>
autovacuum |
on <br>
autovacuum_analyze_scale_factor |
0.1 <br>
eltuples.<br>
autovacuum_analyze_threshold |
50 <br>
autovacuum_freeze_max_age |
200000000 <br>
autovacuum_max_workers |
3 <br>
autovacuum_naptime |
1min <br>
autovacuum_vacuum_cost_delay |
20ms <br>
autovacuum_vacuum_cost_limit |
-1 <br>
autovacuum_vacuum_scale_factor |
0.2 <br>
autovacuum_vacuum_threshold |
50 <br>
backslash_quote |
safe_encoding <br>
bgwriter_delay |
200ms <br>
bgwriter_lru_maxpages |
100 <br>
bgwriter_lru_multiplier |
2 <br>
block_size |
8192 <br>
bonjour_name
| <br>
check_function_bodies |
on <br>
checkpoint_completion_target |
0.5 <br>
interval.<br>
checkpoint_segments |
3 <br>
checkpoint_timeout |
5min <br>
checkpoint_warning |
30s <br>
client_encoding |
UTF8 <br>
client_min_messages |
notice <br>
commit_delay |
0 <br>
isk.<br>
commit_siblings |
5 <br>
config_file |
/var/lib/pgsql/data/postgresql.conf <br>
constraint_exclusion |
off <br>
cpu_index_tuple_cost |
0.005 <br>
n index scan.<br>
cpu_operator_cost |
0.0025 <br>
call.<br>
cpu_tuple_cost |
0.01 <br>
custom_variable_classes
| <br>
data_directory |
/var/lib/pgsql/data <br>
DateStyle | ISO,
MDY <br>
db_user_namespace |
off <br>
deadlock_timeout |
1s <br>
debug_assertions |
off <br>
debug_pretty_print |
off <br>
debug_print_parse |
off <br>
debug_print_plan |
off <br>
debug_print_rewritten |
off <br>
default_statistics_target |
10 <br>
default_tablespace
| <br>
default_text_search_config |
pg_catalog.english <br>
default_transaction_isolation | read
committed <br>
default_transaction_read_only |
off <br>
default_with_oids |
off <br>
dynamic_library_path |
$libdir <br>
effective_cache_size |
4048MB <br>
enable_bitmapscan |
on <br>
enable_hashagg |
on <br>
enable_hashjoin |
on <br>
enable_indexscan |
on <br>
enable_mergejoin |
on <br>
enable_nestloop |
on <br>
enable_seqscan |
on <br>
enable_sort |
on <br>
enable_tidscan |
on <br>
escape_string_warning |
on <br>
explain_pretty_print |
on <br>
external_pid_file
| <br>
extra_float_digits |
0 <br>
from_collapse_limit |
8 <br>
fsync |
on <br>
full_page_writes |
on <br>
geqo |
on <br>
geqo_effort |
5 <br>
geqo_generations |
0 <br>
geqo_pool_size |
0 <br>
geqo_selection_bias |
2 <br>
geqo_threshold |
12 <br>
gin_fuzzy_search_limit |
0 <br>
hba_file |
/var/lib/pgsql/data/pg_hba.conf <br>
ident_file |
/var/lib/pgsql/data/pg_ident.conf <br>
ignore_system_indexes |
off <br>
integer_datetimes |
off <br>
join_collapse_limit |
8 <br>
krb_caseins_users |
off <br>
ve.<br>
krb_realm
| <br>
krb_server_hostname
| <br>
krb_server_keyfile |
<a class="moz-txt-link-freetext" href="FILE:/etc/sysconfig/pgsql/krb5.keytab">FILE:/etc/sysconfig/pgsql/krb5.keytab</a> <br>
krb_srvname |
postgres <br>
lc_collate |
en_US.UTF-8 <br>
lc_ctype |
en_US.UTF-8 <br>
lc_messages |
en_US.UTF-8 <br>
lc_monetary |
en_US.UTF-8 <br>
lc_numeric |
en_US.UTF-8 <br>
lc_time |
en_US.UTF-8 <br>
listen_addresses |
* <br>
local_preload_libraries
| <br>
log_autovacuum_min_duration |
-1 <br>
log_checkpoints |
on <br>
log_connections |
on <br>
log_destination |
stderr <br>
log_directory |
pg_log <br>
log_disconnections |
on <br>
log_duration |
on <br>
log_error_verbosity |
default <br>
log_executor_stats |
off <br>
log_filename |
postgresql-%Y-%m-%d_%H%M%S.log <br>
log_hostname |
on <br>
log_line_prefix | %t [%p]:
[%l-1] <br>
log_lock_waits |
on <br>
log_min_duration_statement |
-1 <br>
log_min_error_statement |
error <br>
log_min_messages |
notice <br>
log_parser_stats |
off <br>
log_planner_stats |
off <br>
log_rotation_age |
1d <br>
log_rotation_size |
100MB <br>
log_statement |
all <br>
log_statement_stats |
off <br>
log_temp_files |
-1 <br>
log_timezone |
Europe/Rome <br>
log_truncate_on_rotation |
on <br>
logging_collector |
on <br>
maintenance_work_mem |
16MB <br>
max_connections |
640 <br>
max_files_per_process |
1000 <br>
max_fsm_pages |
204800 <br>
max_fsm_relations |
1000 <br>
max_function_args |
100 <br>
max_identifier_length |
63 <br>
max_index_keys |
32 <br>
max_locks_per_transaction |
64 <br>
max_prepared_transactions |
5 <br>
max_stack_depth |
2MB <br>
password_encryption |
on <br>
port |
5432 <br>
post_auth_delay |
0 <br>
pre_auth_delay |
0 <br>
random_page_cost |
4 <br>
regex_flavor |
advanced <br>
search_path |
"$user",public <br>
seq_page_cost |
1 <br>
server_encoding |
UTF8 <br>
server_version |
8.3.8 <br>
server_version_num |
80308 <br>
session_replication_role |
origin <br>
shared_buffers |
2GB <br>
shared_preload_libraries
| <br>
silent_mode |
off <br>
sql_inheritance |
on <br>
ssl |
off <br>
ssl_ciphers |
ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH <br>
standard_conforming_strings |
off <br>
statement_timeout |
0 <br>
superuser_reserved_connections |
3 <br>
synchronize_seqscans |
on <br>
synchronous_commit |
on <br>
syslog_facility |
LOCAL0 <br>
syslog_ident |
postgres <br>
tcp_keepalives_count |
0 <br>
tcp_keepalives_idle |
0 <br>
tcp_keepalives_interval |
0 <br>
temp_buffers |
2048 <br>
temp_tablespaces
| <br>
TimeZone |
Europe/Rome <br>
timezone_abbreviations |
Default <br>
trace_notify |
off <br>
trace_sort |
off <br>
track_activities |
on <br>
track_counts |
on <br>
transaction_isolation | read
committed <br>
transaction_read_only |
off <br>
transform_null_equals |
off <br>
unix_socket_directory
| <br>
unix_socket_group
| <br>
unix_socket_permissions |
511 <br>
update_process_title |
on <br>
vacuum_cost_delay |
0 <br>
vacuum_cost_limit |
200 <br>
vacuum_cost_page_dirty |
20 <br>
vacuum_cost_page_hit |
1 <br>
vacuum_cost_page_miss |
10 <br>
vacuum_freeze_min_age |
100000000 <br>
wal_buffers |
64kB <br>
wal_sync_method |
fdatasync <br>
wal_writer_delay |
200ms <br>
work_mem |
16MB <br>
xmlbinary |
base64 <br>
xmloption |
content <br>
zero_damaged_pages |
off <br>
<br>
<br>
<br>
<blockquote cite="mid:5923(dot)1281974354(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap=""></pre>
<blockquote type="cite">
<pre wrap=""> MessageContext: 2042626048 total in 256 blocks; 16072 free (7
chunks); 2042609976 used<br>
</pre>
</blockquote>
<pre wrap="">
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.
</pre>
</blockquote>
This issue is not deterministic, sometime it append (with
TopMemoryContext) sometime not. Can I fix it by increasing work_mem?<br>
<br>
Thanks <br>
<br>
PS: I cannot give you the data for privace issue<br>
<br>
<blockquote cite="mid:5923(dot)1281974354(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap=""> regards, tom lane
</pre>
</blockquote>
<br>
<br>
<div class="moz-signature">-- <br>
<title></title>
<pre class="moz-signature" cols="72"><font face="Verdana"><b><font
color="#000000">Edoardo Innocenti
</font></b><small><font color="#000000">Infrastructure Coordinator</font></small>
<font color="#000000">
<b>SDB Information Technology</b>
<small>Phone: +39.055.3811222
Fax: +39.055.5201411</small></font></font></pre>
</div>
<br>
<html>
<head>
<meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
<title>disclaimer-sdb</title>
</head>
<body>
<div style="text-align: center; width: 620px; font-family: Verdana;">
<small>
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 <a href="http://www.savinodelbene.com/news/privacy.html">http://www.savinodelbene.com/news/privacy.html</a>
<br>
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale <a href="http://www.savinodelbene.com/codice_penale_616.html">http://www.savinodelbene.com/codice_penale_616.html</a>
<BR>
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
</small>
</div>
</body>
</html>
</P>
</BODY>
</HTML>
<br>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 22.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2010-08-17 13:44:57 | Re: TopMemoryContext - Configuration Mistake? |
| Previous Message | Scott Marlowe | 2010-08-17 02:38:05 | Re: Is regular vacuuming with autovacuum needed? |