Re: capturing/viewing sort_mem utilization on a per query basis - Mailing list pgsql-general

From Lonni J Friedman
Subject Re: capturing/viewing sort_mem utilization on a per query basis
Date
Msg-id 7c1574a9050202091022bf6f@mail.gmail.com
Whole thread Raw
In response to Re: capturing/viewing sort_mem utilization on a per query basis  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
On Wed, 02 Feb 2005 11:17:59 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Lonni J Friedman <netllama@gmail.com> writes:
> > I've got a pesky J2EE based app that is using PostgreSQL-7.4.x on the
> > backend.  Lately i've been getting a disturbing large number of
> > PostgreSQL out of memory exceptions on the J2EE side when running
> > queries that have some huge joins in them.
>
> Such an error on the client side would indicate that the query retrieved
> more rows than the client app had memory for.  Perhaps your problem is
> incorrectly-constrained joins that are returning way more rows than
> you expect?

You might be right, i'm honestly not sure. What we're seeing on the
client side is:
org.postgresql.util.PSQLException: ERROR: out of memory

Although, looking in the log postgresql is generating, I'm seeing the
following at the same time as that OOM above so it certainly looks
like the DB is barfing:
###############
TopMemoryContext: 71448 total in 7 blocks; 22544 free (120 chunks); 48904 used
TopTransactionContext: 8192 total in 1 blocks; 8136 free (0 chunks); 56 used
DeferredTriggerXact: 0 total in 0 blocks; 0 free (0 chunks); 0 used
MessageContext: 1040384 total in 7 blocks; 395024 free (1021 chunks);
645360 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 800 free (0 chunks); 224 used
ExecutorState: 2121752 total in 9 blocks; 538864 free (12 chunks); 1582888 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 451796 total in 2 blocks; 260800 free (0 chunks); 190996 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: -1934683632 total in 295 blocks; 135760 free (879
chunks); -1934819392 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
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
DynaHashTable: 24576 total in 2 blocks; 11128 free (4 chunks); 13448 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
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
CacheMemoryContext: 2088960 total in 8 blocks; 1595032 free (3190
chunks); 493928 used
relationship_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
relation_type: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
relation_target: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
relation_origin: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
field_value_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
field_class_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
field_value_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
f_val_fid_val_idx: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
report_param_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
report_parm_report: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
projectmember_pk: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
projmember_member: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_opclass_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
role_user_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
role_user_proj_idx: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used
role_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
sfuser_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
sfuser_username: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
role_operation_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
role_oper_obj_oper: 2048 total in 1 blocks; 768 free (0 chunks); 1280 used
artifact_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
artifact_status: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
project_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
project_path: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
folder_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
folder_parent: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
folder_project: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
folder_path: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
item_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
item_name: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
item_folder: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_index_indrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_proc_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_cast_source_target_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
pg_type_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_operator_oprname_l_r_n_index: 2048 total in 1 blocks; 704 free (0
chunks); 1344 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 640 free (0
chunks); 384 used
pg_shadow_usesysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
pg_operator_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
pg_amop_opc_strategy_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
pg_class_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
pg_operator_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_amop_opc_strategy_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
MdSmgr: 8192 total in 1 blocks; 6120 free (0 chunks); 2072 used
DynaHash: 8192 total in 1 blocks; 6912 free (0 chunks); 1280 used
DynaHashTable: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used
DynaHashTable: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used
DynaHashTable: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used
DynaHashTable: 24576 total in 2 blocks; 15256 free (4 chunks); 9320 used
DynaHashTable: 8192 total in 1 blocks; 1960 free (0 chunks); 6232 used
DynaHashTable: 24576 total in 2 blocks; 13240 free (4 chunks); 11336 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ErrorContext: 49152 total in 4 blocks; 49088 free (27 chunks); 64 used
2005-02-02 01:33:51 ERROR:  out of memory
DETAIL:  Failed on request of size 364.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman                                    netllama@gmail.com
LlamaLand                       http://netllama.linux-sxs.org

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: capturing/viewing sort_mem utilization on a per query basis
Next
From: Tom Lane
Date:
Subject: Re: Invalid headers and xlog flush failures