Re: Postgres Connections Requiring Large Amounts of Memory - Mailing list pgsql-performance

I installed postgres with debug compiled in and ran the same tests.

I attached gdb to a connection using just over 400MB( according to top) and ran "MemoryContextStats(TopMemoryContext)"

Here's the output:

TopMemoryContext: 49176 total in 6 blocks; 16272 free (44 chunks); 32904 used
TopTransactionContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
TransactionCommandContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 3072 total in 2 blocks; 864 free (0 chunks); 2208 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 3072 total in 2 blocks; 720 free (0 chunks); 2352 used
SPI Plan: 1024 total in 1 blocks; 184 free (0 chunks); 840 used
SPI Plan: 261120 total in 8 blocks; 20416 free (0 chunks); 240704 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 261120 total in 8 blocks; 18456 free (0 chunks); 242664 used
SPI Plan: 7168 total in 3 blocks; 4016 free (5 chunks); 3152 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 31744 total in 5 blocks; 15024 free (0 chunks); 16720 used
SPI Plan: 1024 total in 1 blocks; 184 free (0 chunks); 840 used
SPI Plan: 523264 total in 9 blocks; 80504 free (0 chunks); 442760 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 523264 total in 9 blocks; 79992 free (0 chunks); 443272 used
SPI Plan: 7168 total in 3 blocks; 4016 free (5 chunks); 3152 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 7168 total in 3 blocks; 1816 free (0 chunks); 5352 used
SPI Plan: 1024 total in 1 blocks; 184 free (0 chunks); 840 used
SPI Plan: 261120 total in 8 blocks; 130824 free (3 chunks); 130296 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 261120 total in 8 blocks; 130032 free (0 chunks); 131088 used
SPI Plan: 7168 total in 3 blocks; 4016 free (5 chunks); 3152 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 130048 total in 7 blocks; 36512 free (0 chunks); 93536 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 1024 total in 1 blocks; 184 free (0 chunks); 840 used
SPI Plan: 130048 total in 7 blocks; 37976 free (0 chunks); 92072 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 130048 total in 7 blocks; 34688 free (0 chunks); 95360 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 3072 total in 2 blocks; 864 free (0 chunks); 2208 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 3072 total in 2 blocks; 536 free (0 chunks); 2536 used
SPI Plan: 7168 total in 3 blocks; 4016 free (5 chunks); 3152 used
SPI Plan: 7168 total in 3 blocks; 4016 free (5 chunks); 3152 used
SPI Plan: 7168 total in 3 blocks; 4016 free (5 chunks); 3152 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 31744 total in 5 blocks; 15024 free (0 chunks); 16720 used
SPI Plan: 1024 total in 1 blocks; 184 free (0 chunks); 840 used
SPI Plan: 130048 total in 7 blocks; 26120 free (0 chunks); 103928 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 130048 total in 7 blocks; 24232 free (0 chunks); 105816 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 3072 total in 2 blocks; 128 free (0 chunks); 2944 used
SPI Plan: 7168 total in 3 blocks; 4016 free (5 chunks); 3152 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 7168 total in 3 blocks; 1816 free (0 chunks); 5352 used
SPI Plan: 7168 total in 3 blocks; 1816 free (0 chunks); 5352 used
SPI Plan: 7168 total in 3 blocks; 1816 free (0 chunks); 5352 used
SPI Plan: 1024 total in 1 blocks; 184 free (0 chunks); 840 used
SPI Plan: 64512 total in 6 blocks; 24688 free (0 chunks); 39824 used
SPI Plan: 64512 total in 6 blocks; 23792 free (0 chunks); 40720 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 64512 total in 6 blocks; 23464 free (0 chunks); 41048 used
SPI Plan: 31744 total in 5 blocks; 10088 free (0 chunks); 21656 used
SPI Plan: 7168 total in 3 blocks; 3904 free (4 chunks); 3264 used
SPI Plan: 31744 total in 5 blocks; 8576 free (0 chunks); 23168 used
SPI Plan: 7168 total in 3 blocks; 4016 free (5 chunks); 3152 used
SPI Plan: 3096 total in 2 blocks; 8 free (0 chunks); 3088 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 1024 total in 1 blocks; 184 free (0 chunks); 840 used
SPI Plan: 31744 total in 5 blocks; 6672 free (0 chunks); 25072 used
SPI Plan: 3072 total in 2 blocks; 1840 free (0 chunks); 1232 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
QueryContext: 24576 total in 2 blocks; 15304 free (56 chunks); 9272 used
DeferredTriggerSession: 0 total in 0 blocks; 0 free (0 chunks); 0 used
PortalMemory: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
CacheMemoryContext: 2108952 total in 10 blocks; 1070136 free (3338 chunks); 1038816 used
bss_pkey: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_sensor_10_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_sensor_9_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_sensor_8_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_sensor_7_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_sensor_6_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_sensor_5_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_sensor_4_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_sensor_3_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_sensor_2_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_sensor_1_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_bss_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
station_epochint_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
station_epoch_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
station_sensor_10_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_sensor_9_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_sensor_8_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_sensor_7_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_sensor_6_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_sensor_5_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_sensor_4_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_sensor_3_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_sensor_2_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_sensor_1_idx: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_lastseenint_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
station_lastseen_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
station_firstseenint_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
station_firstseen_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
station_pkey_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
station_cfg_view_pkey: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
bss_cfg_view_pkey: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
sensor_cfg_view_pk: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
station_epoch_sum_pk_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_index_indrelid_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_relcheck_rcrelid_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_shadow_usesysid_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_trigger_tgrelid_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_language_oid_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_proc_oid_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_aggregate_name_type_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_type_oid_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_proc_proname_narg_type_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_amop_opc_strategy_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_operator_oid_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_type_typname_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_class_oid_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_class_relname_index: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
MdSmgr: 8192 total in 1 blocks; 4072 free (1 chunks); 4120 used
DynaHash: 8192 total in 1 blocks; 6944 free (0 chunks); 1248 used
DynaHashTable: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used
DynaHashTable: 42008 total in 2 blocks; 6112 free (0 chunks); 35896 used
DynaHashTable: 8192 total in 1 blocks; 6112 free (0 chunks); 2080 used
DynaHashTable: 8192 total in 1 blocks; 3000 free (0 chunks); 5192 used
DynaHashTable: 8192 total in 1 blocks; 3000 free (0 chunks); 5192 used
DynaHashTable: 24576 total in 2 blocks; 13224 free (4 chunks); 11352 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ErrorContext: 8192 total in 1 blocks; 8176 free (1 chunks); 16 used

Is there any other information I could provide that would be useful? I'm going to try to enable SHOW_MEMORY_STATS next.

-Dawn

On Mon, 2003-06-16 at 21:34, Tom Lane wrote:

> What would be useful to try to narrow things down is to look at the
> output of "MemoryContextStats(TopMemoryContext)" in a backend that's
> grown to a large size.  This is a fairly primitive routine
> unfortunately; there is no built-in way to invoke it other than by
> calling it manually with a debugger, and it is only bright enough
> to write to stderr, not syslog.  If you have stderr going somewhere
> useful (not /dev/null) and you built with debugging symbols, then you
> could attach to a running backend right now with gdb and get some useful
> info.  If you don't have debugging symbols then you'll need to either
> rebuild with 'em, or create some other way to call the function.
> (There is a bit of stub code marked #ifdef SHOW_MEMORY_STATS in
> postgres.c that might be worth enabling, but I think it's only a sketch
> and won't compile as-is, since I don't see a ShowStats variable
> anywhere.)
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster