Обсуждение: TopMemoryContext - Configuration Mistake?

I got the following error during a select query.

May it be a config error, or is it a bug?

TopMemoryContext: 186872 total in 15 blocks; 16544 free (41 chunks); 170328 used
  TopTransactionContext: 8192 total in 1 blocks; 7568 free (0 chunks); 624 used
  Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
  Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
  MessageContext: 2042626048 total in 256 blocks; 16072 free (7 chunks); 2042609976 used
    JoinRelHashTable: 1040384 total in 7 blocks; 512384 free (12 chunks); 528000 used
  smgr relation table: 24576 total in 2 blocks; 9776 free (4 chunks); 14800 used
  TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used
  Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  PortalMemory: 8192 total in 1 blocks; 8160 free (1 chunks); 32 used
  Relcache by OID: 24576 total in 2 blocks; 12832 free (3 chunks); 11744 used
  CacheMemoryContext: 2549344 total in 23 blocks; 986984 free (1 chunks); 1562360 used
    gen_porti_sched_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used
    m_zondes_pkey: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
    oevi_1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oe_vessel_imbarco_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used
    m_zonmar_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    m_tipmer_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    m_cianav_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used
    oev_1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oe_vessel_t_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used
    mmerca_cod_emb: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    m_merca_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    m_aeropu_pkey: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
    mcli_nome: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    mcli_acro: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    <m_cli_x1>: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    m_cli_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oec_2: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oec_1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oe_container_booking_nr_progr_ctnr_azienda_key: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used
    oe_container_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used
    oem_x1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oe_sped_m_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used
    ref_sales_pkey: 2048 total in 1 blocks; 440 free (0 chunks); 1608 used
    oes_x7: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oes_x6: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oes_x5: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oes_x4: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oes_x3: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oes_x2: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oes_x1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    oe_sped_t_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used
    navig_save_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used
    navig_fields_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used
    navig_subarea_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used
    navig_area_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    navig_left_table_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    gnp_cod_tipo_par: 2048 total in 1 blocks; 608 free (0 chunks); 1440 used
    gen_param_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used
    glchart_groups_pk_gr: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    pg_attrdef_oid_index: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    empresa_pkey: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
    pg_attrdef_adrelid_adnum_index: 2048 total in 1 blocks; 608 free (0 chunks); 1440 used
    pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
    pg_ts_dict_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
    pg_aggregate_fnoid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_language_name_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
    pg_statistic_relid_att_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
    pg_ts_dict_dictname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
    pg_namespace_nspname_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_opfamily_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
    pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_ts_parser_prsname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
    pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2 chunks); 1688 used
    pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1576 free (3 chunks); 1496 used
    pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
    pg_cast_source_target_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
    pg_auth_members_role_member_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
    pg_attribute_relid_attnum_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
    pg_ts_config_cfgname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
    pg_authid_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_ts_config_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
    pg_conversion_default_index: 3072 total in 2 blocks; 1432 free (3 chunks); 1640 used
    pg_language_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
    pg_enum_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
    pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1576 free (3 chunks); 1496 used
    pg_ts_parser_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
    pg_database_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_conversion_name_nsp_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
    pg_class_relname_nsp_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
    pg_attribute_relid_attnam_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
    pg_class_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1384 free (2 chunks); 1688 used
    pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1384 free (2 chunks); 1688 used
    pg_index_indexrelid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_type_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
    pg_authid_rolname_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_auth_members_member_role_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
    pg_enum_typid_label_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
    pg_constraint_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
    pg_conversion_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
    pg_ts_template_tmplname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
    pg_ts_config_map_index: 3072 total in 2 blocks; 1624 free (3 chunks); 1448 used
    pg_namespace_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_type_typname_nsp_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
    pg_operator_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_amop_opr_fam_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
    pg_proc_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
    pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1624 free (3 chunks); 1448 used
    pg_ts_template_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
  MdSmgr: 8192 total in 1 blocks; 5664 free (0 chunks); 2528 used
  LOCALLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
  Timezones: 53584 total in 2 blocks; 3744 free (0 chunks); 49840 used
  ErrorContext: 24576 total in 3 blocks; 24480 free (18 chunks); 96 used
2010-08-03 10:48:03 CEST [31005]: [658-1] ERROR:  out of memory

Edoardo Innocenti
Infrastructure Coordinator

SDB Information Technology
Phone: +39.055.3811222
Fax:   +39.055.5201411

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?

> � 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.

            regards, tom lane

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

Edoardo Innocenti <edoardo.innocenti@savinodelbene.com> writes:
> Query Plan:<br>
> "HashAggregate (cost=4910.06..4910.19 rows=1 width=659)"

Hm ... the planner seems to think that this is a small query that isn't
going to take long, which is a bit at odds with the fact that you're
running out of memory.  I suspect that these rowcount estimates are
far too low, which would suggest that you need to make sure your
ANALYZE statistics are up-to-date, and perhaps raise the statistics
targets.  Try to get the plan's estimated rowcounts to approximate
reality.

BTW, you might want to think about turning off your mail program's
HTML option.  What other people are seeing looks like this:
http://archives.postgresql.org/pgsql-admin/2010-08/msg00126.php
and it's not pretty.

            regards, tom lane

Il 17/08/2010 15:44, Tom Lane ha scritto:

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

Query Plan:<br>
"HashAggregate (cost=4910.06..4910.19 rows=1 width=659)"   

Hm ... the planner seems to think that this is a small query that isn't
going to take long, which is a bit at odds with the fact that you're
running out of memory.  I suspect that these rowcount estimates are
far too low, which would suggest that you need to make sure your
ANALYZE statistics are up-to-date, and perhaps raise the statistics
targets.  Try to get the plan's estimated rowcounts to approximate
reality.
 

We have pg_autovacuum enable and we launch vacuumdb -z every weeks.
These tables do not grow fast, 300 record max. Is it possible that
the statistics are the cause?

BTW, you might want to think about turning off your mail program's
HTML option.  What other people are seeing looks like this:
http://archives.postgresql.org/pgsql-admin/2010-08/msg00126.php
and it's not pretty.
		regards, tom lane
 

Edoardo Innocenti
Infrastructure Coordinator

SDB Information Technology
Phone: +39.055.3811222
Fax:   +39.055.5201411