Re: capturing/viewing sort_mem utilization on a per query basis
| От | Lonni J Friedman |
|---|---|
| Тема | Re: capturing/viewing sort_mem utilization on a per query basis |
| Дата | |
| Msg-id | 7c1574a9050202091022bf6f@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: capturing/viewing sort_mem utilization on a per query basis (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | 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 по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: capturing/viewing sort_mem utilization on a per query basis