Skip site navigation (1) Skip section navigation (2)

Odd out of memory problem.

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Odd out of memory problem.
Date: 2012-03-26 15:03:45
Message-ID: 4F708551.4020304@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-hackers
I'm not sure if this is a bug, but I have wrestling with this problem 
for a client.

Platform is Windows Servers 2003 64 bit, PostgreSQL 8.4.8., 4Gb RAM, 
running on an Amazon VM.

Shared buffers: 512Mb, work_mem: 25Mb. There are only a handful of 
connections to the database, and no other activity.

We are seeing the error shown below. The table in question has two 
columns (Oid, int) and roughly 43m rows. The only other thing remarkable 
about the settings is that effective_cache_size is set to 5Gb, which is 
clearly too high, but surely that shouldn't cause a memory error.

I'm really perplexed as to why this fairly simple query should cause an 
out of memory error:

    select loid, max(pageno) from ldata group by loid order by 2 desc
    limit 10;

I can't see what I might be missing.


cheers

andrew

    TopMemoryContext: 49816 total in 6 blocks; 5384 free (7 chunks);
    44432 used
       TopTransactionContext: 8192 total in 1 blocks; 7696 free (0
    chunks); 496 used
       Record information cache: 8192 total in 1 blocks; 1800 free (0
    chunks); 6392 used
       Type information cache: 8192 total in 1 blocks; 1800 free (0
    chunks); 6392 used
       Operator class cache: 8192 total in 1 blocks; 3848 free (0
    chunks); 4344 used
       Operator lookup cache: 24576 total in 2 blocks; 14072 free (6
    chunks); 10504 used
       MessageContext: 40960 total in 3 blocks; 29920 free (6 chunks);
    11040 used
       smgr relation table: 8192 total in 1 blocks; 2816 free (0
    chunks); 5376 used
       TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0
    chunks); 16 used
       Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
       PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
         PortalHeapMemory: 1024 total in 1 blocks; 920 free (0 chunks);
    104 used
           ExecutorState: 8192 total in 1 blocks; 2144 free (1 chunks);
    6048 used
             TupleSort: 40984 total in 3 blocks; 24208 free (10 chunks);
    16776 used
             ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
             AggContext: 864018432 total in 127 blocks; 3400 free (110
    chunks); 864015032 used
               TupleHashTable: 619175960 total in 95 blocks; 821528 free
    (331 chunks); 618354432 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
       Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks);
    4816 used
       CacheMemoryContext: 667696 total in 20 blocks; 169960 free (2
    chunks); 497736 used
         pg_shdepend_reference_index: 1024 total in 1 blocks; 240 free
    (0 chunks); 784 used
         pg_depend_depender_index: 1024 total in 1 blocks; 152 free (0
    chunks); 872 used
         pg_depend_reference_index: 1024 total in 1 blocks; 152 free (0
    chunks); 872 used
         pg_largeobject_loid_pn_index: 1024 total in 1 blocks; 280 free
    (0 chunks); 744 used
         pg_database_datname_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0
    chunks); 720 used
         pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 152 free
    (0 chunks); 872 used
         pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 344
    free (0 chunks); 680 used
         pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
    680 used
         pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0
    chunks); 784 used
         pg_foreign_server_oid_index: 1024 total in 1 blocks; 344 free
    (0 chunks); 680 used
         pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free
    (0 chunks); 784 used
         pg_cast_source_target_index: 1024 total in 1 blocks; 240 free
    (0 chunks); 784 used
         pg_language_name_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0
    chunks); 720 used
         pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0
    chunks); 936 used
         pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0
    chunks); 720 used
         pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free
    (0 chunks); 744 used
         pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0
    chunks); 832 used
         pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0
    chunks); 720 used
         pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 344
    free (0 chunks); 680 used
         pg_auth_members_member_role_index: 1024 total in 1 blocks; 280
    free (0 chunks); 744 used
         pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_conversion_default_index: 1024 total in 1 blocks; 128 free
    (0 chunks); 896 used
         pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 88
    free (0 chunks); 936 used
         pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240
    free (0 chunks); 784 used
         pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0
    chunks); 744 used
         pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_user_mapping_oid_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free
    (0 chunks); 832 used
         pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
    720 used
         pg_aggregate_fnoid_index: 1024 total in 1 blocks; 304 free (0
    chunks); 720 used
         pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free
    (0 chunks); 744 used
         pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0
    chunks); 744 used
         pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0
    chunks); 744 used
         pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0
    chunks); 720 used
         pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0
    chunks); 720 used
         pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_amop_opr_fam_index: 1024 total in 1 blocks; 240 free (0
    chunks); 784 used
         pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0
    chunks); 744 used
         pg_auth_members_role_member_index: 1024 total in 1 blocks; 280
    free (0 chunks); 744 used
         pg_type_typname_nsp_index: 1024 total in 1 blocks; 240 free (0
    chunks); 784 used
         pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_class_oid_index: 1024 total in 1 blocks; 304 free (0
    chunks); 720 used
         pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152
    free (0 chunks); 872 used
         pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240
    free (0 chunks); 784 used
         pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
    720 used
         pg_language_oid_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_namespace_oid_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_database_oid_index: 1024 total in 1 blocks; 304 free (0
    chunks); 720 used
         pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0
    chunks); 936 used
         pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0
    chunks); 720 used
         pg_foreign_server_name_index: 1024 total in 1 blocks; 344 free
    (0 chunks); 680 used
         pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 240
    free (0 chunks); 784 used
         pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0
    chunks); 680 used
         pg_user_mapping_user_server_index: 1024 total in 1 blocks; 280
    free (0 chunks); 744 used
         pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free
    (0 chunks); 744 used
       MdSmgr: 8192 total in 1 blocks; 4240 free (0 chunks); 3952 used
       LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks);
    4280 used
       Timezones: 79320 total in 2 blocks; 5968 free (0 chunks); 73352 used
       ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
    2012-03-25 17:18:01 EDT ERROR:  out of memory
    2012-03-25 17:18:01 EDT DETAIL:  Failed on request of size 20.
    2012-03-25 17:18:01 EDT STATEMENT:  select loid, max(pageno) from
    ldata group by loid order by 2 desc limit 10;


Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2012-03-26 15:06:18
Subject: Re: heap_freeze_tuple locking requirements
Previous:From: Robert HaasDate: 2012-03-26 14:40:59
Subject: Re: Weak-memory specific problem in ResetLatch/WaitLatch (follow-up analysis)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group