TopMemoryContext - Configuration Mistake?

TopMemoryContext - Configuration Mistake?

am 16.08.2010 14:45:19 von Edoardo Innocenti





9-15">


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

=A0 TopTransactionContext: 8192 total in 1 blocks; 7568 free (0 chunks);
624 used

=A0 Type information cache: 24576 total in 2 blocks; 11888 free (5
chunks); 12688 used

=A0 Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks);
6512 used

=A0 Operator lookup cache: 24576 total in 2 blocks; 11888 free (5
chunks); 12688 used

=A0 MessageContext: 2042626048 total in 256 blocks; 16072 free (7
chunks); 2042609976 used

  =A0 JoinRelHashTable: 1040384 total in 7 blocks; 512384 free (12
chunks); 528000 used

=A0 smgr relation table: 24576 total in 2 blocks; 9776 free (4 chunks);
14800 used

=A0 TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0
chunks); 32 used

=A0 Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used<=
br>
=A0 PortalMemory: 8192 total in 1 blocks; 8160 free (1 chunks); 32 used r>
=A0 Relcache by OID: 24576 total in 2 blocks; 12832 free (3 chunks);
11744 used

=A0 CacheMemoryContext: 2549344 total in 23 blocks; 986984 free (1
chunks); 1562360 used

  =A0 gen_porti_sched_pkey: 2048 total in 1 blocks; 656 free (0 chunk=
s);
1392 used

  =A0 m_zondes_pkey: 2048 total in 1 blocks; 704 free (0 chunks); 134=
4
used

  =A0 oevi_1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<=
br>
  =A0 oe_vessel_imbarco_pkey: 2048 total in 1 blocks; 632 free (0
chunks); 1416 used

  =A0 m_zonmar_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 129=
6
used

  =A0 m_tipmer_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 129=
6
used

  =A0 m_cianav_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 139=
2
used

  =A0 oev_1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used r>
  =A0 oe_vessel_t_pkey: 2048 total in 1 blocks; 656 free (0 chunks); =
1392
used

  =A0 mmerca_cod_emb: 2048 total in 1 blocks; 752 free (0 chunks); 12=
96
used

  =A0 m_merca_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296=
used

  =A0 m_aeropu_pkey: 2048 total in 1 blocks; 704 free (0 chunks); 134=
4
used

  =A0 mcli_nome: 2048 total in 1 blocks; 752 free (0 chunks); 1296 us=
ed

  =A0 mcli_acro: 2048 total in 1 blocks; 752 free (0 chunks); 1296 us=
ed

  =A0 <m_cli_x1>: 2048 total in 1 blocks; 752 free (0 chunks); =
1296
used

  =A0 m_cli_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 u=
sed

  =A0 oec_2: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used r>
  =A0 oec_1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used r>
  =A0 oe_container_booking_nr_progr_ctnr_azienda_key: 2048 total in 1
blocks; 632 free (0 chunks); 1416 used

  =A0 oe_container_pkey: 2048 total in 1 blocks; 632 free (0 chunks);
1416 used

  =A0 oem_x1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<=
br>
  =A0 oe_sped_m_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 14=
16
used

  =A0 ref_sales_pkey: 2048 total in 1 blocks; 440 free (0 chunks); 16=
08
used

  =A0 oes_x7: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<=
br>
  =A0 oes_x6: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<=
br>
  =A0 oes_x5: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<=
br>
  =A0 oes_x4: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<=
br>
  =A0 oes_x3: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<=
br>
  =A0 oes_x2: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<=
br>
  =A0 oes_x1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used<=
br>
  =A0 oe_sped_t_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 13=
92
used

  =A0 navig_save_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1=
392
used

  =A0 navig_fields_pkey: 2048 total in 1 blocks; 632 free (0 chunks);
1416 used

  =A0 navig_subarea_pkey: 2048 total in 1 blocks; 656 free (0 chunks)=
;
1392 used

  =A0 navig_area_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1=
296
used

  =A0 navig_left_table_pkey: 2048 total in 1 blocks; 752 free (0 chun=
ks);
1296 used

  =A0 gnp_cod_tipo_par: 2048 total in 1 blocks; 608 free (0 chunks); =
1440
used

  =A0 gen_param_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 14=
16
used

  =A0 glchart_groups_pk_gr: 2048 total in 1 blocks; 752 free (0 chunk=
s);
1296 used

  =A0 pg_attrdef_oid_index: 2048 total in 1 blocks; 752 free (0 chunk=
s);
1296 used

  =A0 empresa_pkey: 2048 total in 1 blocks; 704 free (0 chunks); 1344=
used

  =A0 pg_attrdef_adrelid_adnum_index: 2048 total in 1 blocks; 608 fre=
e (0
chunks); 1440 used

  =A0 pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0
chunks); 1344 used

  =A0 pg_ts_dict_oid_index: 3072 total in 2 blocks; 1744 free (3 chun=
ks);
1328 used

  =A0 pg_aggregate_fnoid_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used

  =A0 pg_language_name_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used

  =A0 pg_statistic_relid_att_index: 3072 total in 2 blocks; 1600 free=
(2
chunks); 1472 used

  =A0 pg_ts_dict_dictname_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used

  =A0 pg_namespace_nspname_index: 3072 total in 2 blocks; 1696 free (=
2
chunks); 1376 used

  =A0 pg_opfamily_oid_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used

  =A0 pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free (2 chun=
ks);
1376 used

  =A0 pg_ts_parser_prsname_index: 3072 total in 2 blocks; 1648 free (=
2
chunks); 1424 used

  =A0 pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2
chunks); 1688 used

  =A0 pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1576 free=
(3
chunks); 1496 used

  =A0 pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1600 f=
ree
(2 chunks); 1472 used

  =A0 pg_cast_source_target_index: 3072 total in 2 blocks; 1600 free =
(2
chunks); 1472 used

  =A0 pg_auth_members_role_member_index: 3072 total in 2 blocks; 1648
free (2 chunks); 1424 used

  =A0 pg_attribute_relid_attnum_index: 3072 total in 2 blocks; 1600 f=
ree
(2 chunks); 1472 used

  =A0 pg_ts_config_cfgname_index: 3072 total in 2 blocks; 1648 free (=
2
chunks); 1424 used

  =A0 pg_authid_oid_index: 3072 total in 2 blocks; 1696 free (2 chunk=
s);
1376 used

  =A0 pg_ts_config_oid_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used

  =A0 pg_conversion_default_index: 3072 total in 2 blocks; 1432 free =
(3
chunks); 1640 used

  =A0 pg_language_oid_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used

  =A0 pg_enum_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks)=
;
1328 used

  =A0 pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1576 fr=
ee
(3 chunks); 1496 used

  =A0 pg_ts_parser_oid_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used

  =A0 pg_database_oid_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used

  =A0 pg_conversion_name_nsp_index: 3072 total in 2 blocks; 1648 free=
(2
chunks); 1424 used

  =A0 pg_class_relname_nsp_index: 3072 total in 2 blocks; 1600 free (=
2
chunks); 1472 used

  =A0 pg_attribute_relid_attnam_index: 3072 total in 2 blocks; 1648 f=
ree
(2 chunks); 1424 used

  =A0 pg_class_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks=
);
1376 used

  =A0 pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1384 free (2
chunks); 1688 used

  =A0 pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1384 f=
ree
(2 chunks); 1688 used

  =A0 pg_index_indexrelid_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used

  =A0 pg_type_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks)=
;
1376 used

  =A0 pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1648 fre=
e (2
chunks); 1424 used

  =A0 pg_authid_rolname_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used

  =A0 pg_auth_members_member_role_index: 3072 total in 2 blocks; 1600
free (2 chunks); 1472 used

  =A0 pg_enum_typid_label_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used

  =A0 pg_constraint_oid_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used

  =A0 pg_conversion_oid_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used

  =A0 pg_ts_template_tmplname_index: 3072 total in 2 blocks; 1648 fre=
e (2
chunks); 1424 used

  =A0 pg_ts_config_map_index: 3072 total in 2 blocks; 1624 free (3
chunks); 1448 used

  =A0 pg_namespace_oid_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used

  =A0 pg_type_typname_nsp_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used

  =A0 pg_operator_oid_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used

  =A0 pg_amop_opr_fam_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used

  =A0 pg_proc_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks)=
;
1376 used

  =A0 pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1624 fre=
e (3
chunks); 1448 used

  =A0 pg_ts_template_oid_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used

=A0 MdSmgr: 8192 total in 1 blocks; 5664 free (0 chunks); 2528 used

=A0 LOCALLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592
used

=A0 Timezones: 53584 total in 2 blocks; 3744 free (0 chunks); 49840 used<=
br>
=A0 ErrorContext: 24576 total in 3 blocks; 24480 free (18 chunks); 96 use=
d

2010-08-03 10:48:03 CEST [31005]: [658-1] ERROR:=A0 out of memory



--



 color=3D"#000000">Edoardo Innocenti
Infrastructure Coordinator t>

SDB Information Technology
Phone: +39.055.3811222
Fax: +39.055.5201411







-type">
disclaimer-sdb




Utilizziamo i dati personali che la riguardano esclusivamente per nostre =
finalità amministrative e contabili, anche quando li comunichiamo a=
terzi. Informazioni dettagliate, anche in ordine al Suo diritto di acces=
so e agli altri Suoi diritti, sono riportate alla pagina //www.savinodelbene.com/news/privacy.html">http://www.savino delbene.com/n=
ews/privacy.html



Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo =
al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 6=
16 codice penale 6.html">http://www.savinodelbene.com/codice_penale_616.html


L'Azienda non si assume alcuna responsabilità giuridica qualora per=
vengano da questo indirizzo messaggi estranei all'attivit=E0 lavorativa o=
contrari a norme.











Re: TopMemoryContext - Configuration Mistake?

am 16.08.2010 17:59:14 von Tom Lane

Edoardo Innocenti writes:
> I got the following error during a select query.


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


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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: TopMemoryContext - Configuration Mistake?

am 17.08.2010 08:58:00 von Edoardo Innocenti




http-equiv=3D"Content-Type">


Il 16/08/2010 17:59, Tom Lane ha scritto:


Edoardo Innocenti 
f=3D"mailto:edoardo.innocenti@savinodelbene.com"><edoardo.innocenti@sa=
vinodelbene.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=3D4910.06..4910.19 rows=3D1 width=3D659)"


" -> Nested Loop (cost=3D84.49..4909.99 rows=3D1 width=3D659)"


" -> Nested Loop (cost=3D84.49..4901.70 rows=3D1 width=3D655)"


" Join Filter: (aer_d.zon_mar =3D zmar.codigo)"


" -> Nested Loop (cost=3D84.49..4900.03 rows=3D1 width=3D601)"


" Join Filter: (merca.grupo =3D tipmer.codigo)"


" -> Nested Loop (cost=3D84.49..4898.56 rows=3D1 width=3D574)"


" -> Nested Loop (cost=3D84.49..4890.27 rows=3D1 width=3D530)"


" Join Filter: (zmar4.codigo =3D aer_d.zon_mar)"


" -> Nested Loop (cost=3D84.49..4888.60 rows=3D1 width=3D522)"


" -> Nested Loop (cost=3D84.49..4880.32 rows=3D1 width=3D514)"


" Join Filter: (oec.progr_ctnr =3D oem.progr_ctnr)"


" -> Nested Loop (cost=3D84.49..4807.00 rows=3D3 width=3D514)"


" -> Nested Loop (cost=3D84.49..4782.15 rows=3D3 width=3D494)"


" -> Nested Loop (cost=3D84.49..4757.31 rows=3D3 width=3D495)"


" -> Nested Loop (cost=3D84.49..4732.46 rows=3D3 width=3D451)"


" Join Filter: (aer_l.zon_mar =3D zmar3.codigo)"


" -> Nested Loop (cost=3D84.49..4727.44 rows=3D3 width=3D459)"


" -> Nested Loop (cost=3D84.49..4726.60 rows=3D3 width=3D389)"


" -> Nested Loop (cost=3D84.49..4705.74 rows=3D3 width=3D337)"


" -> Nested Loop (cost=3D84.49..4680.89 rows=3D3 width=3D293)"


" -> Nested Loop (cost=3D84.49..4680.16 rows=3D1 width=3D249)"


" -> Hash Join (cost=3D84.49..4676.45 rows=3D1 width=3D249)"


" Hash Cond: ((oes.aero_ori =3D oevi.port_loading) AND (oes.vessel_code =3D
oevi.vessel_code))"


" -> Hash Left Join (cost=3D76.20..4502.78 rows=3D22050 width=3D233)"


" Hash Cond: ((oes.azienda =3D refs.azienda) AND (oes.hbl =3D refs.house)
AND (oes.expediente =3D refs.reference))"


" -> Hash Left Join (cost=3D38.10..3637.70 rows=3D22050 width=3D180)"


" Hash Cond: ((oes.azienda =3D refs2.azienda) AND (oes.hbl =3D refs2.hous=
e)
AND (oes.expediente =3D refs2.reference))"


" -> Seq Scan on oe_sped_t oes (cost=3D0.00..2772.62 rows=3D22050
width=3D119)"


" Filter: (azienda =3D '60'::bpchar)"


" -> Hash (cost=3D28.12..28.12 rows=3D570 width=3D92)"


" -> Seq Scan on ref_sales refs2 (cost=3D0.00..28.12 rows=3D570
width=3D92)"


" Filter: (azienda =3D '60'::bpchar)"


" -> Hash (cost=3D28.12..28.12 rows=3D570 width=3D92)"


" -> Seq Scan on ref_sales refs (cost=3D0.00..28.12 rows=3D570
width=3D92)"


" Filter: (azienda =3D '60'::bpchar)"


" -> Hash (cost=3D8.27..8.27 rows=3D1 width=3D20)"


" -> Index Scan using oevi_1 on oe_vessel_imbarco oevi
(cost=3D0.00..8.27 rows=3D1 width=3D20)"


" Index Cond: ((departure >=3D '2010-07-01'::date) AND (departure
<=3D '2010-07-31'::date))"


" Filter: (azienda =3D '60'::bpchar)"


" -> Index Scan using m_aeropu_pkey on m_aeropu aer_l
(cost=3D0.00..3.70 rows=3D1 width=3D16)"


" Index Cond: (aer_l.codigo =3D oes.aero_ori)"


" -> Index Scan using oe_container_booking_nr_progr_ctnr_azienda_key
on oe_container oec (cost=3D0.00..0.67 rows=3D5 width=3D48)"


" Index Cond: ((oec.booking_nr =3D oes.booking_nr) AND (oec.azienda =3D
'60'::bpchar))"


" -> Index Scan using m_cli_pkey on m_cli fab (cost=3D0.00..8.27
rows=3D1 width=3D60)"


" Index Cond: (fab.codigo =3D oes.cod_fab)"


" -> Index Scan using oe_vessel_t_pkey on oe_vessel_t oev
(cost=3D0.00..6.94 rows=3D1 width=3D64)"


" Index Cond: ((oev.vessel_code =3D oes.vessel_code) AND (oev.azienda =3D
'60'::bpchar))"


" -> Index Scan using m_zonmar_pkey on m_zonmar zmar2
(cost=3D0.00..0.27 rows=3D1 width=3D70)"


" Index Cond: (zmar2.codigo =3D aer_l.zon_mar)"


" -> Seq Scan on m_zonmar zmar3 (cost=3D0.00..1.30 rows=3D30 width=3D8=
)"


" -> Index Scan using m_cli_pkey on m_cli agent (cost=3D0.00..8.27
rows=3D1 width=3D60)"


" Index Cond: (agent.codigo =3D oes.agen_des)"


" -> Index Scan using m_merca_pkey on m_merca merca (cost=3D0.00..8.27
rows=3D1 width=3D11)"


" Index Cond: (merca.codigo =3D oes.tip_mer)"


" -> Index Scan using m_cianav_pkey on m_cianav cia2
(cost=3D0.00..8.27 rows=3D1 width=3D24)"


" Index Cond: ((cia2.codigo =3D oev.carrier) AND (cia2.azienda =3D
'60'::bpchar))"


" -> Index Scan using oe_sped_m_pkey on oe_sped_m oem
(cost=3D0.00..24.36 rows=3D5 width=3D20)"


" Index Cond: ((oem.entry_nr =3D oes.entry_nr) AND (oem.azienda =3D
'60'::bpchar))"


" -> Index Scan using m_aeropu_pkey on m_aeropu aer_d
(cost=3D0.00..8.27 rows=3D1 width=3D16)"


" Index Cond: (aer_d.codigo =3D oes.aero_des)"


" -> Seq Scan on m_zonmar zmar4 (cost=3D0.00..1.30 rows=3D30 width=3D8=
)"


" -> Index Scan using m_cli_pkey on m_cli cons (cost=3D0.00..8.27
rows=3D1 width=3D60)"


" Index Cond: (cons.codigo =3D oes.cod_des)"


" -> Seq Scan on m_tipmer tipmer (cost=3D0.00..1.21 rows=3D21 width=3D=
37)"


" -> Seq Scan on m_zonmar zmar (cost=3D0.00..1.30 rows=3D30 width=3D70=
)"


" -> Index Scan using m_cianav_pkey on m_cianav cia (cost=3D0.00..8.27
rows=3D1 width=3D48)"


" Index Cond: ((cia.codigo =3D oev.carrier) AND (cia.azienda =3D
'60'::bpchar))"





This is db parameters:



            =A0 name          =
     |              =A0
setting              =A0

---------------------------------+-------------------------- -------------=


=A0add_missing_from            =A0=A 0=A0 |
off                  =A0     =A0=
          

=A0allow_system_table_mods         |
off                  =A0     =A0=
          

=A0archive_command                  |
(disabled)                =A 0    =
      

=A0archive_mode              =A0      =
|
off                  =A0     =A0=
          

=A0archive_timeout                  |
0                  =A0=A 0    =
            

=A0N seconds.

=A0array_nulls              =A0= A0    =
|
on                  =A0= A0    =
          =A0

=A0authentication_timeout        =A0 |
1min                  =A 0    =
          

=A0autovacuum              =A0=A 0    =
=A0 |
on                  =A0= A0    =
          =A0

=A0autovacuum_analyze_scale_factor |
0.1                  =A0     =A0=
          

eltuples.

=A0autovacuum_analyze_threshold  =A0 |
50                  =A0= A0    =
          =A0

=A0autovacuum_freeze_max_age       |
200000000                =A0     =A0=
      

=A0autovacuum_max_workers        =A0 |
3                  =A0=A 0    =
            

=A0autovacuum_naptime            =A0 |
1min                  =A 0    =
          

=A0autovacuum_vacuum_cost_delay  =A0 |
20ms                  =A 0    =
          

=A0autovacuum_vacuum_cost_limit  =A0 |
-1                  =A0= A0    =
          =A0

=A0autovacuum_vacuum_scale_factor=A0 |
0.2                  =A0     =A0=
          

=A0autovacuum_vacuum_threshold     |
50                  =A0= A0    =
          =A0

=A0backslash_quote                  |
safe_encoding              =A0=A 0    =
    

=A0bgwriter_delay              = A0   |
200ms                  = A0    =
        =A0

=A0bgwriter_lru_maxpages           |
100                  =A0     =A0=
          

=A0bgwriter_lru_multiplier         |
2                  =A0=A 0    =
            

=A0block_size              =A0=A 0    =
=A0 |
8192                  =A 0    =
          

=A0bonjour_name              =A0     
|                  =A0=A 0    =
              

=A0check_function_bodies           |
on                  =A0= A0    =
          =A0

=A0checkpoint_completion_target  =A0 |
0.5                  =A0     =A0=
          

interval.

=A0checkpoint_segments             |
3                  =A0=A 0    =
            

=A0checkpoint_timeout            =A0 |
5min                  =A 0    =
          

=A0checkpoint_warning            =A0 |
30s                  =A0     =A0=
          

=A0client_encoding                  |
UTF8                  =A 0    =
          

=A0client_min_messages             |
notice                       =A0=
        

=A0commit_delay              =A0      =
|
0                  =A0=A 0    =
            

isk.

=A0commit_siblings                  |
5                  =A0=A 0    =
            

=A0config_file              =A0= A0    =
|
/var/lib/pgsql/data/postgresql.conf  

=A0constraint_exclusion          =A0 |
off                  =A0     =A0=
          

=A0cpu_index_tuple_cost          =A0 |
0.005                  = A0    =
        =A0

n index scan.

=A0cpu_operator_cost            =A0= A0 |
0.0025                       =A0=
        

=A0call.

=A0cpu_tuple_cost              = A0   |
0.01                  =A 0    =
          

=A0custom_variable_classes        
|                  =A0=A 0    =
              

=A0data_directory              = A0   |
/var/lib/pgsql/data            =A0=A 0    =


=A0DateStyle                     =A0=
=A0 | ISO,
MDY                  =A0     =A0=
    =A0

=A0db_user_namespace            =A0= A0 |
off                  =A0     =A0=
          

=A0deadlock_timeout            =A0=A 0=A0 |
1s                  =A0= A0    =
          =A0

=A0debug_assertions            =A0=A 0=A0 |
off                  =A0     =A0=
          

=A0debug_pretty_print            =A0 |
off                  =A0     =A0=
          

=A0debug_print_parse            =A0= A0 |
off                  =A0     =A0=
          

=A0debug_print_plan            =A0=A 0=A0 |
off                  =A0     =A0=
          

=A0debug_print_rewritten           |
off                  =A0     =A0=
          

=A0default_statistics_target       |
10                  =A0= A0    =
          =A0

=A0default_tablespace            =A0
|                  =A0=A 0    =
              

=A0default_text_search_config    =A0 |
pg_catalog.english                   =A0=


=A0default_transaction_isolation   | read
committed                =A0     =A0=
=A0

=A0default_transaction_read_only   |
off                  =A0     =A0=
          

=A0default_with_oids            =A0= A0 |
off                  =A0     =A0=
          

=A0dynamic_library_path          =A0 |
$libdir                =A0=A 0    =
        

=A0effective_cache_size          =A0 |
4048MB                       =A0=
        

=A0enable_bitmapscan            =A0= A0 |
on                  =A0= A0    =
          =A0

=A0enable_hashagg              = A0   |
on                  =A0= A0    =
          =A0

=A0enable_hashjoin                  |
on                  =A0= A0    =
          =A0

=A0enable_indexscan            =A0=A 0=A0 |
on                  =A0= A0    =
          =A0

=A0enable_mergejoin            =A0=A 0=A0 |
on                  =A0= A0    =
          =A0

=A0enable_nestloop                  |
on                  =A0= A0    =
          =A0

=A0enable_seqscan              = A0   |
on                  =A0= A0    =
          =A0

=A0enable_sort              =A0= A0    =
|
on                  =A0= A0    =
          =A0

=A0enable_tidscan              = A0   |
on                  =A0= A0    =
          =A0

=A0escape_string_warning           |
on                  =A0= A0    =
          =A0

=A0explain_pretty_print          =A0 |
on                  =A0= A0    =
          =A0

=A0external_pid_file            =A0= A0
|                  =A0=A 0    =
              

=A0extra_float_digits            =A0 |
0                  =A0=A 0    =
            

=A0from_collapse_limit             |
8                  =A0=A 0    =
            

=A0fsync                =A0= A0    =
     |
on                  =A0= A0    =
          =A0

=A0full_page_writes            =A0=A 0=A0 |
on                  =A0= A0    =
          =A0

=A0geqo                =A0=A 0    =
    =A0 |
on                  =A0= A0    =
          =A0

=A0geqo_effort              =A0= A0    =
|
5                  =A0=A 0    =
            

=A0geqo_generations            =A0=A 0=A0 |
0                  =A0=A 0    =
            

=A0geqo_pool_size              = A0   |
0                  =A0=A 0    =
            

=A0geqo_selection_bias             |
2                  =A0=A 0    =
            

=A0geqo_threshold              = A0   |
12                  =A0= A0    =
          =A0

=A0gin_fuzzy_search_limit        =A0 |
0                  =A0=A 0    =
            

=A0hba_file                = A0    =
   |
/var/lib/pgsql/data/pg_hba.conf      

=A0ident_file              =A0=A 0    =
=A0 |
/var/lib/pgsql/data/pg_ident.conf    

=A0ignore_system_indexes           |
off                  =A0     =A0=
          

=A0integer_datetimes            =A0= A0 |
off                  =A0     =A0=
          

=A0join_collapse_limit             |
8                  =A0=A 0    =
            

=A0krb_caseins_users            =A0= A0 |
off                  =A0     =A0=
          

ve.

=A0krb_realm                     =A0=
=A0
|                  =A0=A 0    =
              

=A0krb_server_hostname            
|                  =A0=A 0    =
              

=A0krb_server_keyfile            =A0 |
..keytab">FILE:/etc/sysconfig/pgsql/krb5.keytab

=A0krb_srvname              =A0= A0    =
|
postgres                =A0= A0    =
      =A0

=A0lc_collate              =A0=A 0    =
=A0 |
en_US.UTF-8                = A0    =
    =A0

=A0lc_ctype                = A0    =
   |
en_US.UTF-8                = A0    =
    =A0

=A0lc_messages              =A0= A0    =
|
en_US.UTF-8                = A0    =
    =A0

=A0lc_monetary              =A0= A0    =
|
en_US.UTF-8                = A0    =
    =A0

=A0lc_numeric              =A0=A 0    =
=A0 |
en_US.UTF-8                = A0    =
    =A0

=A0lc_time                =A 0    =
  =A0 |
en_US.UTF-8                = A0    =
    =A0

=A0listen_addresses            =A0=A 0=A0 |
*                  =A0=A 0    =
            

=A0local_preload_libraries        
|                  =A0=A 0    =
              

=A0log_autovacuum_min_duration     |
-1                  =A0= A0    =
          =A0

=A0log_checkpoints                  |
on                  =A0= A0    =
          =A0

=A0log_connections                  |
on                  =A0= A0    =
          =A0

=A0log_destination                  |
stderr                       =A0=
        

=A0log_directory              =A 0  =A0 |
pg_log                       =A0=
        

=A0log_disconnections            =A0 |
on                  =A0= A0    =
          =A0

=A0log_duration              =A0      =
|
on                  =A0= A0    =
          =A0

=A0log_error_verbosity             |
default                =A0=A 0    =
        

=A0log_executor_stats            =A0 |
off                  =A0     =A0=
          

=A0log_filename              =A0      =
|
postgresql-%Y-%m-%d_%H%M%S.log      =A0

=A0log_hostname              =A0      =
|
on                  =A0= A0    =
          =A0

=A0log_line_prefix                  | %t [=
%p]:
[%l-1]                        =


=A0log_lock_waits              = A0   |
on                  =A0= A0    =
          =A0

=A0log_min_duration_statement    =A0 |
-1                  =A0= A0    =
          =A0

=A0log_min_error_statement         |
error                  = A0    =
        =A0

=A0log_min_messages            =A0=A 0=A0 |
notice                       =A0=
        

=A0log_parser_stats            =A0=A 0=A0 |
off                  =A0     =A0=
          

=A0log_planner_stats            =A0= A0 |
off                  =A0     =A0=
          

=A0log_rotation_age            =A0=A 0=A0 |
1d                  =A0= A0    =
          =A0

=A0log_rotation_size            =A0= A0 |
100MB                  = A0    =
        =A0

=A0log_statement              =A 0  =A0 |
all                  =A0     =A0=
          

=A0log_statement_stats             |
off                  =A0     =A0=
          

=A0log_temp_files              = A0   |
-1                  =A0= A0    =
          =A0

=A0log_timezone              =A0      =
|
Europe/Rome                = A0    =
    =A0

=A0log_truncate_on_rotation      =A0 |
on                  =A0= A0    =
          =A0

=A0logging_collector            =A0= A0 |
on                  =A0= A0    =
          =A0

=A0maintenance_work_mem          =A0 |
16MB                  =A 0    =
          

=A0max_connections                  |
640                  =A0     =A0=
          

=A0max_files_per_process           |
1000                  =A 0    =
          

=A0max_fsm_pages              =A 0  =A0 |
204800                       =A0=
        

=A0max_fsm_relations            =A0= A0 |
1000                  =A 0    =
          

=A0max_function_args            =A0= A0 |
100                  =A0     =A0=
          

=A0max_identifier_length           |
63                  =A0= A0    =
          =A0

=A0max_index_keys              = A0   |
32                  =A0= A0    =
          =A0

=A0max_locks_per_transaction       |
64                  =A0= A0    =
          =A0

=A0max_prepared_transactions       |
5                  =A0=A 0    =
            

=A0max_stack_depth                  |
2MB                  =A0     =A0=
          

=A0password_encryption             |
on                  =A0= A0    =
          =A0

=A0port                =A0=A 0    =
    =A0 |
5432                  =A 0    =
          

=A0post_auth_delay                  |
0                  =A0=A 0    =
            

=A0pre_auth_delay              = A0   |
0                  =A0=A 0    =
            

=A0random_page_cost            =A0=A 0=A0 |
4                  =A0=A 0    =
            

=A0regex_flavor              =A0      =
|
advanced                =A0= A0    =
      =A0

=A0search_path              =A0= A0    =
|
"$user",public              =A0= A0    =
  =A0

=A0seq_page_cost              =A 0  =A0 |
1                  =A0=A 0    =
            

=A0server_encoding                  |
UTF8                  =A 0    =
          

=A0server_version              = A0   |
8.3.8                  = A0    =
        =A0

=A0server_version_num            =A0 |
80308                  = A0    =
        =A0

=A0session_replication_role      =A0 |
origin                       =A0=
        

=A0shared_buffers              = A0   |
2GB                  =A0     =A0=
          

=A0shared_preload_libraries      =A0
|                  =A0=A 0    =
              

=A0silent_mode              =A0= A0    =
|
off                  =A0     =A0=
          

=A0sql_inheritance                  |
on                  =A0= A0    =
          =A0

=A0ssl                       =A0=
    =A0 |
off                  =A0     =A0=
          

=A0ssl_ciphers              =A0= A0    =
|
ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH    

=A0standard_conforming_strings     |
off                  =A0     =A0=
          

=A0statement_timeout            =A0= A0 |
0                  =A0=A 0    =
            

=A0superuser_reserved_connections=A0 |
3                  =A0=A 0    =
            

=A0synchronize_seqscans          =A0 |
on                  =A0= A0    =
          =A0

=A0synchronous_commit            =A0 |
on                  =A0= A0    =
          =A0

=A0syslog_facility                  |
LOCAL0                       =A0=
        

=A0syslog_ident              =A0      =
|
postgres                =A0= A0    =
      =A0

=A0tcp_keepalives_count          =A0 |
0                  =A0=A 0    =
            

=A0tcp_keepalives_idle             |
0                  =A0=A 0    =
            

=A0tcp_keepalives_interval         |
0                  =A0=A 0    =
            

=A0temp_buffers              =A0      =
|
2048                  =A 0    =
          

=A0temp_tablespaces            =A0=A 0=A0
|                  =A0=A 0    =
              

=A0TimeZone                = A0    =
   |
Europe/Rome                = A0    =
    =A0

=A0timezone_abbreviations        =A0 |
Default                =A0=A 0    =
        

=A0trace_notify              =A0      =
|
off                  =A0     =A0=
          

=A0trace_sort              =A0=A 0    =
=A0 |
off                  =A0     =A0=
          

=A0track_activities            =A0=A 0=A0 |
on                  =A0= A0    =
          =A0

=A0track_counts              =A0      =
|
on                  =A0= A0    =
          =A0

=A0transaction_isolation           | read
committed                =A0     =A0=
=A0

=A0transaction_read_only           |
off                  =A0     =A0=
          

=A0transform_null_equals           |
off                  =A0     =A0=
          

=A0unix_socket_directory          
|                  =A0=A 0    =
              

=A0unix_socket_group            =A0= A0
|                  =A0=A 0    =
              

=A0unix_socket_permissions         |
511                  =A0     =A0=
          

=A0update_process_title          =A0 |
on                  =A0= A0    =
          =A0

=A0vacuum_cost_delay            =A0= A0 |
0                  =A0=A 0    =
            

=A0vacuum_cost_limit            =A0= A0 |
200                  =A0     =A0=
          

=A0vacuum_cost_page_dirty        =A0 |
20                  =A0= A0    =
          =A0

=A0vacuum_cost_page_hit          =A0 |
1                  =A0=A 0    =
            

=A0vacuum_cost_page_miss           |
10                  =A0= A0    =
          =A0

=A0vacuum_freeze_min_age           |
100000000                =A0     =A0=
      

=A0wal_buffers              =A0= A0    =
|
64kB                  =A 0    =
          

=A0wal_sync_method                  |
fdatasync                =A0     =A0=
      

=A0wal_writer_delay            =A0=A 0=A0 |
200ms                  = A0    =
        =A0

=A0work_mem                = A0    =
   |
16MB                  =A 0    =
          

=A0xmlbinary                     =A0=
=A0 |
base64                       =A0=
        

=A0xmloption                     =A0=
=A0 |
content                =A0=A 0    =
        

=A0zero_damaged_pages            =A0 |
off                  =A0     =A0=
          










=A0 MessageContext: 2042626048 total in 256 blocks; 16=
072 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







--



 color=3D"#000000">Edoardo Innocenti
Infrastructure Coordinator t>

SDB Information Technology
Phone: +39.055.3811222
Fax: +39.055.5201411







-type">
disclaimer-sdb




Utilizziamo i dati personali che la riguardano esclusivamente per nostre =
finalità amministrative e contabili, anche quando li comunichiamo a=
terzi. Informazioni dettagliate, anche in ordine al Suo diritto di acces=
so e agli altri Suoi diritti, sono riportate alla pagina //www.savinodelbene.com/news/privacy.html">http://www.savino delbene.com/n=
ews/privacy.html



Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo =
al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 6=
16 codice penale 6.html">http://www.savinodelbene.com/codice_penale_616.html


L'Azienda non si assume alcuna responsabilità giuridica qualora per=
vengano da questo indirizzo messaggi estranei all'attivit=E0 lavorativa o=
contrari a norme.











Re: TopMemoryContext - Configuration Mistake?

am 17.08.2010 15:44:57 von Tom Lane

Edoardo Innocenti writes:
> Query Plan:

> "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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: TopMemoryContext - Configuration Mistake?

am 17.08.2010 17:10:22 von Edoardo Innocenti




http-equiv="Content-Type">


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


Edoardo Innocenti  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:

and it's not pretty.

regards, tom lane







--



 color="#000000">Edoardo Innocenti
Infrastructure Coordinator

SDB Information Technology
Phone: +39.055.3811222
Fax: +39.055.5201411








disclaimer-sdb




Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina


Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale


L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.