postgresql-jdbc-partition-table-highmemroy.txt
text/plain
Filename: postgresql-jdbc-partition-table-highmemroy.txt
Type: text/plain
Part: 0
we have 18 tables copy from Oracle DB, but there is no much data, even empty table, we still got high memory.
most of tables have 256 HASH partitions
partition key is part of primary key ,
meetinginstanceid | character varying(128) | | not null |
and
the update and delete looks like this
update ... set ... where siteid=$1(bigint) and meetinginstanceid=$2(it's partition key and part of primary key) .
copy from part of context memory stats dumped to pglog when "out of memory" , this table has a update trigger that call a PL/PGSQL function so it's has that,
but enven other tables with 256 HASH parition ,no any trigger no any pl/pgsql, it's still fail into out of memory error and consume similar memory 160MB/per backend process.
TopMemoryContext: 1593168 total in 31 blocks; 127440 free (87 chunks); 1465728 used
TopTransactionContext: 8192 total in 1 blocks; 7160 free (1 chunks); 1032 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
pgstat TabStatusArray lookup hash table: 131072 total in 5 blocks; 58744 free (12 chunks); 72328 used
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: trigger_fct_common_lmt()
Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used
PLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
PL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 used: trigger_fct_common_lmt()
Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
Prepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 used
TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
MessageContext: 18874368 total in 14 blocks; 2544 free (6 chunks); 18871824 used
partition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
smgr relation table: 524288 total in 7 blocks; 247016 free (24 chunks); 277272 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used:
Relcache by OID: 131072 total in 5 blocks; 36024 free (10 chunks); 95048 used
CacheMemoryContext: 17586560 total in 78 blocks; 2655312 free (2 chunks); 14931248 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetingtranscriptmap_p31
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetingtranscriptmap_p31_siteid_tsobjectuuid_idx
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetingtranscriptmap_p31_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstanceinvitee_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceinvitee_p22_siteid_uid__meetinginstancei_idx4
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceinvitee_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticiprelation_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticiprelation_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticiprelation_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmtginstanceparticipantaudio_p223
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmtginstanceparticipantaudio_p223_siteid_idx
index info: 3072 total in 2 blocks; 968 free (1 chunks); 2104 used: wbxmtginstanceparticipantaudio_p223_pkey
partition constraint: 1024 total in 1 blocks; 120 free (0 chunks); 904 used: wbxmeetinginstanceparticipant_p223
index info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 used: wbxmeetinginstanceparticipan_siteid_uid__meetinginstance_idx223
index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: wbxmeetinginstanceparticipant_p223_pkey
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p62_pkey
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p62
CachedPlan: 131072 total in 8 blocks; 38072 free (0 chunks); 93000 used: UPDATE WBXMEETINGINSTANCEREPORTINFO SET ITEMVALUE = $1 WHERE ITEMNAME = $2 AND MEE...
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p13_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p13_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p12_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p12_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p6_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p6_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p4_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p4_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p3_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p3_pkey
index info: 2048 total in 2 blocks; 872 free (0 chunks); 1176 used: wbxmeetinginstancereportinfo_p2_siteid_idx
index info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: wbxmeetinginstancereportinfo_p2_pkey
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 used: SELECT CLOCK_TIMESTAMP()
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 used: SELECT CLOCK_TIMESTAMP()
CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxpostmeetingcontainermap_p117
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_siteid_meetinginstanceid_key
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: wbxpostmeetingcontainermap_p117_pkey
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 8192 total in 4 blocks; 1264 free (0 chunks); 6928 used: INSERT INTO WBXPOSTMEETINGWEBLINKMAP(SITEID, LINKUUID, MEETINGINSTANCEID, ACCESSTYPE, PAYLOAD) ...
CachedPlan: 4204544 total in 13 blocks; 489400 free (4 chunks); 3715144 used: UPDATE WBXMEETINGINSTTopMemoryContext: 2036392 total in 47 blocks; 62344 free (19 chunks); 1974048 usedAN
CE MTopTransactionContext: 8192 total in 1 blocks; 7352 free (3 chunks); 840 usedE
TADA TPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedA: t r i g g e r _ fScEtT _AcCoLmUmRoLn _=l $1m t ( )
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used : t r i g g e r _ f c,t _KcEoYmUmRoL n=_ l$m2t ( )
pgstat TabStatusArray lookup hash table: 262144 total in 6 blocks; 129552 free (14 chunks); 132592 used...
CachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : StELrEiCgT gCeLOrC_Kf_TcItME_ScTAoMmPm(o)
n_ l mCachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedt: (S)E
LECT PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedCL: OtCrKi_gTgIeMrE_SfTcAtM_Pc(o)m
m o n _CachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 usedl
m t (SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used)
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used : PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedwb: xtprositgmgeeert_ifncgtc_ocntoamimneornm_alpm_tp(4)2
PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: : twrbxipgogsetrm_efectti_ncgocmomnotna_ilnemrtm(a)p
_p4 2_PL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 useds: itteriid_gmgeeert_ifncgti_ncsotmamnocne_ildm_tk(e)y
index info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedPL/pgSQL function: 16384 total in 2 blocks; 8792 free (1 chunks); 7592 used: : wtbrxipgogsetrm_efectti_ncgocmonmtoani_nlemrtma(p)_
p4 2Type information cache: 24616 total in 2 blocks; 2616 free (0 chunks); 22000 used_
pk ePLpgSQL cast info: 8192 total in 1 blocks; 7928 free (0 chunks); 264 usedy
PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 usedCachedPlan: 8192 total in 4 blocks; 1920 free (0 chunks); 6272 used
: IN SPL/pgSQL function: 16384 total in 2 blocks; 8712 free (4 chunks); 7672 usedE: RtT rIiNgTgOe rW_BfXcMtE_EcToImNmGoInN_SlTmAtN(CE)R
EP ORendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedR
TIN FPLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 usedO
(S IPrepared Queries: 32768 total in 3 blocks; 18888 free (9 chunks); 13880 usedT
EI DTableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used,
ME EOperator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 usedT
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
IN GIRowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 usedN
ST AMessageContext: 16777216 total in 12 blocks; 19240 free (6 chunks); 16757976 usedN
CE I Dpartition directory: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used,
I TCFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedE
MN AOperator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 usedM
E, smgr relation table: 1048576 total in 8 blocks; 518320 free (28 chunks); 530256 usedI
TEM VTransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 usedA
LU EPortal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used)
TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
PortalContext: 1024 total in 1 blocks; 368 free (1 chunks); 656 used :
VA...
Relcache by OID: 262144 total in 6 blocks; 87104 free (12 chunks); 175040 used
partition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 used: wbxmeetinginstancereportinfo_p5
index info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 used: wbxmeetinginstancereportinfo_p5_siteid_idx
CacheMemoryContext: 35269048 total in 1095 blocks; 5938376 free (93 chunks); 29330672 usedindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used
: w b xindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedm: eewtbixnmgeientsitnagnicnesrteapnocretiinnfvoi_tpe5_ep_kpe2y5
_ s iindex info: 2048 total in 2 blocks; 792 free (0 chunks); 1256 usedt: ewbixdm_euetiidn_g_imnesettaincnegrienpsotrtainncfeoi__pi1d_xs6i
te i dindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used_: iwdbxx
m e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 usedt: iwnbgximnesettainncgeiinnsvtiatnecee_rpe2po5r5t_ipnkfeoy_
p1 _ pindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedk: ewyb
x m epartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usede: twibnxgmienesttianngcienisntvaintceeree_ppo2r5ti_nsfiot_ep1i
d _ uCachedPlan: 2048 total in 2 blocks; 416 free (0 chunks); 1632 usedi: dS_E_LmEeCeTt iCnLOgCiKn_sTtIaMnEcSeTiA_MiPd(x)5
CachedPlanSource: 2048 total in 2 blocks; 448 free (0 chunks); 1600 usedindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: S: EwLbExCmTe eCtLiOCnKg_iTnIsMtESaTnAcMePi(n)v
i t e eCachedPlanQuery: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used
_ p 2SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used5
4 _ ppartition constraint: 1024 total in 1 blocks; 152 free (0 chunks); 872 usedk: ewyb
xp o sindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usedtm: ewebtximnegectoinntgaiinnsetramnacpe_pi2n1v7i
t e eindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used_: pw2b5x_psoisttemiede_tuiindg_c_omneteatiinnergmianps_tpa2n1c7e_is_iitdexid4_
m e etindex info: 2048 total in 2 blocks; 576 free (1 chunks); 1472 usedi: nwgbixnmseteatnicnegiidn_skteayn
c e iindex info: 2048 total in 2 blocks; 416 free (1 chunks); 1632 used: nwbvxiptoeset_mpe2e5t3i_npgkceoyn
ta i nindex info: 3072 total in 2 blocks; 1048 free (1 chunks); 2024 usede: rmwabpx_mpe2e1t7i_npgkienys