Re: TopMemoryContext - Configuration Mistake? - Mailing list pgsql-admin

Il 16/08/2010 17:59, Tom Lane ha scritto: <blockquote cite="mid:5923.1281974354@sss.pgh.pa.us" type="cite"><pre
wrap="">EdoardoInnocenti <a class="moz-txt-link-rfc2396E"
href="mailto:edoardo.innocenti@savinodelbene.com"><edoardo.innocenti@savinodelbene.com></a>writes:
</pre><blockquotetype="cite"><pre wrap="">I got the following error during a select query.<br>
</pre></blockquote><prewrap=""> 
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
2007x86_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.70rows=1 width=655)" <br /> " Join Filter: (aer_d.zon_mar = zmar.codigo)" <br /> " -> Nested Loop
(cost=84.49..4900.03rows=1 width=601)" <br /> " Join Filter: (merca.grupo = tipmer.codigo)" <br /> " -> Nested Loop
(cost=84.49..4898.56rows=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.46rows=3 width=451)" <br /> " Join Filter: (aer_l.zon_mar = zmar3.codigo)" <br /> " -> Nested Loop
(cost=84.49..4727.44rows=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.45rows=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
LeftJoin (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=22050width=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.12rows=570 width=92)" <br /> " Filter: (azienda = '60'::bpchar)" <br /> " -> Hash (cost=8.27..8.27
rows=1width=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_keyon 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
onm_cli fab (cost=0.00..8.27 rows=1 width=60)" <br /> " Index Cond: (fab.codigo = oes.cod_fab)" <br /> " -> Index
Scanusing 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.27rows=1 width=70)" <br /> " Index Cond: (zmar2.codigo = aer_l.zon_mar)" <br /> " -> Seq Scan on
m_zonmarzmar3 (cost=0.00..1.30 rows=30 width=8)" <br /> " -> Index Scan using m_cli_pkey on m_cli agent
(cost=0.00..8.27rows=1 width=60)" <br /> " Index Cond: (agent.codigo = oes.agen_des)" <br /> " -> Index Scan using
m_merca_pkeyon 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_moem (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_cianavcia (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.1281974354@sss.pgh.pa.us" type="cite"><pre wrap=""></pre><blockquote
type="cite"><prewrap="">  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
fixit by increasing work_mem?<br /><br /> Thanks <br /><br /> PS: I cannot give you the data for privace issue<br /><br
/><blockquotecite="mid:5923.1281974354@sss.pgh.pa.us" type="cite"><pre wrap="">            regards, tom lane 
 </pre></blockquote><br /><br /><div class="moz-signature">-- <br /></div><pre class="moz-signature" cols="72"><font
face="Verdana"><b><fontcolor="#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><br /><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
aglialtri 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
ricevutoquesto 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'Aziendanon si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei
all'attivitàlavorativa o contrari a norme. </small></div><br />