Re: out of memory error - Mailing list pgsql-admin

Tom Lane ha scritto:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>
>> Silvio Brandani <silvio.brandani@tech.sdb.it> wrote:
>>
>>> a query on our production database give following errror:
>>>
>>> 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR:  out of memory
>>> 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL:  Failed on
>>> request of size 48.
>>>
>
>
>> What query?  On what OS?  Is this a 32-bit or 64-bit build of
>> PostgreSQL?  How long does it run before failing.  What does memory
>> usage look like before and during the run?
>>
>
> Also, out-of-memory should result in a memory usage map getting dumped
> to the postmaster log.  That would be useful to see too.
>
>             regards, tom lane
>
>
TopMemoryContext: 178680 total in 14 blocks; 7312 free (16 chunks);
171368 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: 3409969152 total in 417 blocks; 17496 free (10
chunks); 3409951656 used
    JoinRelHashTable: 2088960 total in 8 blocks; 851696 free (15
chunks); 1237264 used
  smgr relation table: 24576 total in 2 blocks; 11840 free (4 chunks);
12736 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; 1004136 free (0
chunks); 1545208 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; 752 free (0 chunks); 1296 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; 5792 free (0 chunks); 2400 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-05 15:20:00 CEST [10349]: [262-1] ERROR:  out of memory
2010-08-05 15:20:00 CEST [10349]: [263-1] DETAIL:  Failed on request of
size 16.

The query:
BEGIN;SELECT MAX(oec.ctnr_nr) ::char(13) as Ctnr_nr,MAX(oec.file_ref)
::char(7) as File_Ref,MAX(oec.move_type) ::char(5) as
Ctnr_type,MAX(oec.ct_feet) ::char(3) as feet,MAX(cons.nombre) ::char(51)
as Consignee,MAX(refs.name_sales) ::char(51) as
Salesman,MAX(refs2.name_principal) ::char(51) as
Cargo_principal,MAX(fab.nombre) ::char(51) as Shipper,MAX(agent.nombre)
::char(51)
 as Dest_Agent,MAX(zmar2.nombre) ::char(61) as
Ocean_Area,MAX(aer_l.codigo) ::char(7) as Port_Code_L,MAX(zmar3.codigo)
::char(7)
 as Ocean_Area_L_Code,MAX(zmar.nombre) ::char(61) as
Ocean_Area,MAX(aer_d.codigo) ::char(7) as Port_Code_D,MAX(zmar4.codigo)
::char(7)
 as Ocean_Area_D_Code,MAX(tipmer.descripcio) ::char(31) as
Comm_Group,MAX(oev.vessel_name) ::char(31) as
 Vessel_Name,MAX(oev.vessel_voy) ::char(11) as
Vessel_Voy,MAX(oevi.departure) as Departure,MAX(cia.nombre) ::char(31)
 as SS_Line,MAX(cia2.nom_cod) ::char(5) as Scac_Code,MAX(oes.hbl)
::char(16) as HBL,MAX(oes.mbl) ::char(16) as BL,SUM(oem.volume)
 as Volume ,MAX(oes.booking_nr) as key1, MAX(oem.progr_ctnr) as key2
FROM oe_sped_t oes LEFT OUTER JOIN ref_sales refs
 ON oes.hbl =refs.house AND oes.expediente = refs.reference and
oes.azienda = refs.azienda LEFT OUTER JOIN ref_sales refs2
 ON oes.hbl =refs2.house AND oes.expediente = refs2.reference and
oes.azienda = refs2.azienda,oe_sped_m oem, oe_container
 oec,m_cli cons,m_cli fab,m_cli agent,m_aeropu aer_l,m_aeropu
aer_d,m_merca merca,oe_vessel_t oev,m_cianav cia,m_cianav
 cia2,m_tipmer tipmer,m_zonmar zmar,m_zonmar zmar2,m_zonmar
zmar3,m_zonmar zmar4,oe_vessel_imbarco oevi
 WHERE oes.entry_nr = oem.entry_nr AND oes.booking_nr = oec.booking_nr
AND oem.progr_ctnr = oec.progr_ctnr
 AND oes.azienda = oem.azienda AND oes.azienda = oec.azienda AND
oem.azienda = oec.azienda  AND oes.azienda
 IN ('60') AND oevi.departure Between '7/1/2010' AND '7/31/2010' AND
oes.cod_des = cons.codigo AND
 oes.cod_fab = fab.codigo AND oes.agen_des = agent.codigo AND
oes.aero_ori = aer_l.codigo AND oes.aero_des = aer_d.codigo
 AND oes.tip_mer = merca.codigo AND oes.vessel_code = oev.vessel_code
AND oes.azienda = oev.azienda AND aer_d.zon_mar = zmar.codigo
 AND aer_d.zon_mar = zmar4.codigo AND aer_l.zon_mar = zmar2.codigo AND
aer_l.zon_mar = zmar3.codigo AND merca.grupo=tipmer.codigo
 AND oes.vessel_code = oevi.vessel_code AND oes.aero_ori =
oevi.port_loading and oes.azienda = oevi.azienda AND
 oev.carrier = cia.codigo and oev.azienda=cia.azienda AND oev.carrier=
cia2.codigo and oev.azienda = cia2.azienda
 GROUP BY oes.azienda,oes.booking_nr,oem.progr_ctnr

 linux cento5 64 bit with 8G ram .

when run teh query the cpu go to 100% and the memory go high to 8G then
the out of memory

Silvio B.
---






Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche
quandoli comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi
diritti,sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html 
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli
eventualiallegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html 
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei
all'attivitàlavorativa o contrari a norme. 
--