In a database of one of our customers we sometimes get out of memory errors. Below I have copy pasted one of these very long messages.
The error doesn't always occur, when I copy paste the query and run it manually it works.
The current server is an OpenSUSE 12.2 with postgresql 9.2.1 (we also had it with OpenSUSE 11.3 and 9.0 so we moved the DB to the knew server in the hope that would solve it).
It has 8GB of RAM
Memory parameters are:
shared_buffers = 4GB
temp_buffers = 32MB
work_mem = 32MB
maintenance_work_mem = 256MB
Checked the memory usage in the OS and it is fine (0 KiB in swap)
Any suggestions what we can do about this?
TopMemoryContext: 149952 total in 17 blocks; 8568 free (8 chunks); 141384 used
TopTransactionContext: 8192 total in 1 blocks; 7392 free (1 chunks); 800 used
Type information cache: 24240 total in 2 blocks; 3744 free (0 chunks); 20496 used
Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used
MessageContext: 1048576 total in 8 blocks; 526360 free (7 chunks); 522216 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
smgr relation table: 24576 total in 2 blocks; 5696 free (4 chunks); 18880 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; 7888 free (0 chunks); 304 used
PortalHeapMemory: 1024 total in 1 blocks; 824 free (0 chunks); 200 used
ExecutorState: 189424 total in 11 blocks; 6848 free (5 chunks); 182576 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 19128368 total in 13 blocks; 747952 free (5 chunks); 18380416 used
TupleSort: 32816 total in 2 blocks; 7584 free (0 chunks); 25232 used
TupleSort: 32816 total in 2 blocks; 5408 free (7 chunks); 27408 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: 8192 total in 1 blocks; 8016 free (0 chunks); 176 used
TupleHashTable: 8192 total in 1 blocks; 3744 free (0 chunks); 4448 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8088 free (3 chunks); 104 used
ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
AggContext: 122880 total in 4 blocks; 32 free (0 chunks); 122848 used
TupleHashTable: 516096 total in 6 blocks; 179184 free (20 chunks); 336912 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8016 free (3 chunks); 176 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Unique: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 24576 total in 2 blocks; 12832 free (3 chunks); 11744 used
CacheMemoryContext: 1342128 total in 21 blocks; 201888 free (1 chunks); 1140240 used
voorraad_filiaal_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
voorraad_artikel_nr_idx: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
voorraad_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
voorraad_filiaal_id_key: 2048 total in 1 blocks; 576 free (0 chunks); 1472 used
pg_toast_2619_index: 2048 total in 1 blocks; 528 free (0 chunks); 1520 used
idx_artikel_uitvoering_barcode: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
idx_artikel_uitvoering_artikel_nr: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
artikel_uitvoering_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
artikel_ts_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
artikel_productnr_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
artikel_omschrijving_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
artikel_pkey: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
webshopcategoriefilter_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
webshopartikelgroep_meta_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
pg_toast_20705_index: 2048 total in 1 blocks; 528 free (0 chunks); 1520 used
webshoppagina_taal_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
pg_toast_20696_index: 2048 total in 1 blocks; 528 free (0 chunks); 1520 used
webshoppagina_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
webshopkorting_gevolg_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
webshopkorting_conditie_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
webshopkorting_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
webshopartikelafbeelding_artikel_nr_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
webshopartikelafbeelding_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
artikelgroep_order_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
artikelgroep_pkey: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
artikelgroep_artikel_artikelgroep_nr_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
artikelgroep_artikel_artikel_nr_idx: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
artikelgroep_artikel_pkey: 2048 total in 1 blocks; 528 free (0 chunks); 1520 used
webshopland_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
webshoptaal_pkey: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
pg_attrdef_adrelid_adnum_index: 2048 total in 1 blocks; 528 free (0 chunks); 1520 used
artikelcat_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
configkassa_pkey: 2048 total in 1 blocks; 192 free (0 chunks); 1856 used
webshopsettings_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
pg_index_indrelid_index: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
pg_db_role_setting_databaseid_rol_index: 2048 total in 1 blocks; 576 free (0 chunks); 1472 used
pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
pg_foreign_data_wrapper_name_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_enum_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_class_relname_nsp_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
pg_foreign_server_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_statistic_relid_att_inh_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
pg_cast_source_target_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
pg_language_name_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_collation_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_amop_fam_strat_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
pg_index_indexrelid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_ts_template_tmplname_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
pg_ts_config_map_index: 3072 total in 2 blocks; 1272 free (2 chunks); 1800 used
pg_opclass_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_foreign_data_wrapper_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_ts_dict_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_conversion_default_index: 3072 total in 2 blocks; 1272 free (2 chunks); 1800 used
pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
pg_enum_typid_label_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
pg_ts_config_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_user_mapping_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1272 free (2 chunks); 1800 used
pg_foreign_table_relid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_type_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_aggregate_fnoid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_constraint_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
pg_ts_parser_prsname_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
pg_ts_config_cfgname_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
pg_ts_parser_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_operator_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_namespace_nspname_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_ts_template_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_amop_opr_fam_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
pg_default_acl_role_nsp_obj_index: 3072 total in 2 blocks; 1272 free (2 chunks); 1800 used
pg_collation_name_enc_nsp_index: 3072 total in 2 blocks; 1272 free (2 chunks); 1800 used
pg_range_rngtypid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_ts_dict_dictname_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
pg_type_typname_nsp_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
pg_opfamily_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_class_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
pg_attribute_relid_attnum_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
pg_proc_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_language_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_namespace_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
pg_foreign_server_name_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_attribute_relid_attnam_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
pg_conversion_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
pg_user_mapping_user_server_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
pg_conversion_name_nsp_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
pg_authid_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_auth_members_member_role_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
pg_tablespace_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_database_datname_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_auth_members_role_member_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
pg_database_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
pg_authid_rolname_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
MdSmgr: 8192 total in 1 blocks; 6560 free (0 chunks); 1632 used
tokenize file cxt: 0 total in 0 blocks; 0 free (0 chunks); 0 used
hba parser context: 7168 total in 3 blocks; 3760 free (5 chunks); 3408 used
LOCALLOCK hash: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 used
Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
ErrorContext: 8192 total in 1 blocks; 8160 free (4 chunks); 32 used
2012-12-10 13:51:13 CET 250929 250929_webshop [unknown] 109.72.90.156 ERROR: out of memory
2012-12-10 13:51:13 CET 250929 250929_webshop [unknown] 109.72.90.156 DETAIL: Failed on request of size 6.
2012-12-10 13:51:13 CET 250929 250929_webshop [unknown] 109.72.90.156 STATEMENT: SELECT DISTINCT artikel.productnr, MIN(omschrijving) AS naam, MIN(artikelgroep_artikel.volgnr) AS volgnr, MAX(product_voorraad.product_voorraad) FROM artikel LEFT JOIN artikel_uitvoering ON artikel.nr = artikel_uitvoering.artikel_nr LEFT JOIN artikelgroep_artikel ON artikel.nr = artikelgroep_artikel.artikel_nr JOIN (SELECT artikel.productnr, SUM(voorraad.aantal - voorraad.inorder - voorraad.inwsorder) AS product_voorraad FROM artikel JOIN voorraad ON voorraad.artikel_nr = artikel.nr WHERE voorraad.filiaal_id = 1 AND voorraad.aantal >= (voorraad.inorder + voorraad.inwsorder) GROUP BY artikel.productnr ORDER BY artikel.productnr) AS product_voorraad ON product_voorraad.productnr = artikel.productnr WHERE TRUE AND (artikel.productnr IN (SELECT DISTINCT artikel.productnr FROM artikel LEFT JOIN artikel_uitvoering ON artikel.nr = artikel_uitvoering.artikel_nr LEFT JOIN artikelgroep_artikel ON artikel.nr = artikelgroep_artikel.artikel_nr WHERE TRUE AND ((artikelgroep_artikel.artikelgroep_nr = 442)))) GROUP BY artikel.productnr HAVING MAX(product_voorraad) > 0 ORDER BY volgnr asc LIMIT 50 OFFSET 0
Regards,
Eelke Klein
Eelke Klein
Bolt Afrekensystemen
Mplus Software
Mplus Software
