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

Поиск
Список
Период
Сортировка

TopMemoryContext - Configuration Mistake?

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

Re: TopMemoryContext - Configuration Mistake?

От
Tom Lane
Дата:
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

Re: TopMemoryContext - Configuration Mistake?

От
Edoardo Innocenti
Дата:
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 /> 

Re: TopMemoryContext - Configuration Mistake?

От
Tom Lane
Дата:
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

Re: TopMemoryContext - Configuration Mistake?

От
Edoardo Innocenti
Дата:
Il 17/08/2010 15:44, Tom Lane ha scritto: <blockquote cite="mid:9280.1282052697@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="">Query Plan:<br> 
"HashAggregate (cost=4910.06..4910.19 rows=1 width=659)"   </pre></blockquote><pre wrap="">
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.
 </pre></blockquote> We have pg_autovacuum enable and we launch vacuumdb -z every weeks. <br /> These tables do not
growfast, 300 record max. Is it possible that <br /> the statistics are the cause?<br /><blockquote
cite="mid:9280.1282052697@sss.pgh.pa.us"type="cite"><pre wrap="">BTW, you might want to think about turning off your
mailprogram's 
HTML option.  What other people are seeing looks like this:
<a class="moz-txt-link-freetext"
href="http://archives.postgresql.org/pgsql-admin/2010-08/msg00126.php">http://archives.postgresql.org/pgsql-admin/2010-08/msg00126.php</a>
and it's not pretty.
        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 />