I received a complaint that each backend consumes a lot of
backend-private memory, even if it's completely idle. "a lot" is of
course very subjective and how much memory is actually used depends
heavily on the application. In this case, the database is fairly small,
but they have 250 connections. 'top' output says that each backend is
consuming roughly 3MB of memory (RES - SHR). That's 750 MB of
backend-private memory, which is a significant chunk of total RAM.
So I spent some time analyzing backend memory usage, looking for any
low-hanging fruit. This isn't *that* big an issue, so I don't think we'd
want to do any big rearchitecting for this.
On my laptop, just starting psql, the backend uses 1632 KB of private
memory. Running a simple query like "select * from foo where i = 1"
makes no noticeable difference, but after "\d" (which I'm using to
represent a somewhat more complicated query), it goes up to 1960 KB.
The largest consumer of that memory is the relcache and syscaches. After
starting psql, without running any queries, MemoryContextStats says:
CacheMemoryContext: 817840 total in 20 blocks; 134824 free (4 chunks);
683016 used
plus there is one sub-memorycontext for each index in the relcache, each
using about 1KB. After "\d":
CacheMemoryContext: 1342128 total in 21 blocks; 517472 free (1 chunks);
824656 used
Another thing that can consume a lot of memory is PrivateRefCount lookup
table. It's an array with one int32 for each shared buffer, ie. 512 KB
for each GB of shared_buffers. See previous discussion here:
http://www.postgresql.org/message-id/flat/1164624036.3778.107.ca...@silverbirch.site.
That discussion didn't lead to anything, but I think there's some
potential in turning PrivateRefCount into a tiny hash table or simply a
linear array. Or even simpler, change it from int32 to int16, and accept
that you will get an error if you try to hold more than 2^16 pins one a
buffer in one backend.
One fairly simple thing we could do is to teach catcache.c to resize the
caches. Then we could make the initial size of all the syscaches much
smaller. At the moment, we use fairly caches for catalogs like pg_enum
(256 entries) and pg_usermapping (128), even though most databases don't
use those features at all. If they could be resized on demand, we could
easily allocate them initially with just, say, 4 entries.
Attached is a patch for that. That saves about 300 KB, for a backend
that does nothing. Resizing the caches on demand also has the benefit
that if you have a lot more objects of some type than usual, lookups
won't be bogged down by a too small cache. I haven't tried to measure
that, though.
- Heikki
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index cca0572..36fbc67 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -734,9 +734,8 @@ InitCatCache(int id,
int i;
/*
- * nbuckets is the number of hash buckets to use in this catcache.
- * Currently we just use a hard-wired estimate of an appropriate size for
- * each cache; maybe later make them dynamically resizable?
+ * nbuckets is the initial number of hash buckets to use in this catcache.
+ * It will be enlarged later if it becomes too full.
*
* nbuckets must be a power of two. We check this via Assert rather than
* a full runtime check because the values will be coming from constant
@@ -775,7 +774,8 @@ InitCatCache(int id,
*
* Note: we rely on zeroing to initialize all the dlist headers correctly
*/
- cp = (CatCache *) palloc0(sizeof(CatCache) + nbuckets * sizeof(dlist_head));
+ cp = (CatCache *) palloc0(sizeof(CatCache));
+ cp->cc_bucket = palloc0(nbuckets * sizeof(dlist_head));
/*
* initialize the cache's relation information for the relation
@@ -814,6 +814,44 @@ InitCatCache(int id,
}
/*
+ * Enlarge a catcache, doubling the number of buckets.
+ */
+static void
+RehashCatCache(CatCache *cp)
+{
+ dlist_head *newbucket;
+ int newnbuckets;
+ int i;
+
+ elog(DEBUG1, "rehashing cache with id %d for %s; %d tups, %d buckets",
+ cp->id, cp->cc_relname, cp->cc_ntup, cp->cc_nbuckets);
+
+ /* Allocate a new, larger, hash table. */
+ newnbuckets = cp->cc_nbuckets * 2;
+ newbucket = (dlist_head *) MemoryContextAllocZero(CacheMemoryContext, newnbuckets * sizeof(dlist_head));
+
+ /* Move all entries from old hash table to new. */
+ for (i = 0; i < cp->cc_nbuckets; i++)
+ {
+ while (!dlist_is_empty(&cp->cc_bucket[i]))
+ {
+ dlist_node * node = dlist_pop_head_node(&cp->cc_bucket[i]);
+ CatCTup *ct = dlist_container(CatCTup, cache_elem, node);
+ int hashIndex;
+
+ hashIndex = HASH_INDEX(ct->hash_value, newnbuckets);
+
+ dlist_push_head(&newbucket[hashIndex], &ct->cache_elem);
+ }
+ }
+
+ /* Switch to the new array */
+ pfree(cp->cc_bucket);
+ cp->cc_nbuckets = newnbuckets;
+ cp->cc_bucket = newbucket;
+}
+
+/*
* CatalogCacheInitializeCache
*
* This function does final initialization of a catcache: obtain the tuple
@@ -1684,6 +1722,13 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp,
cache->cc_ntup++;
CacheHdr->ch_ntup++;
+ /*
+ * If the cache has grown too large, enlarge the buckets array. Quite
+ * arbitrarily, we enlarge when fill factor > 2.
+ */
+ if (cache->cc_ntup > cache->cc_nbuckets * 2)
+ RehashCatCache(cache);
+
return ct;
}
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index 1ff2f2b..624db44 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -122,7 +122,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 32
+ 2
},
{AccessMethodRelationId, /* AMNAME */
AmNameIndexId,
@@ -155,7 +155,7 @@ static const struct cachedesc cacheinfo[] = {
Anum_pg_amop_amopfamily,
0
},
- 64
+ 32
},
{AccessMethodOperatorRelationId, /* AMOPSTRATEGY */
AccessMethodStrategyIndexId,
@@ -166,7 +166,7 @@ static const struct cachedesc cacheinfo[] = {
Anum_pg_amop_amoprighttype,
Anum_pg_amop_amopstrategy
},
- 64
+ 32
},
{AccessMethodProcedureRelationId, /* AMPROCNUM */
AccessMethodProcedureIndexId,
@@ -177,7 +177,7 @@ static const struct cachedesc cacheinfo[] = {
Anum_pg_amproc_amprocrighttype,
Anum_pg_amproc_amprocnum
},
- 64
+ 32
},
{AttributeRelationId, /* ATTNAME */
AttributeRelidNameIndexId,
@@ -188,7 +188,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 2048
+ 512
},
{AttributeRelationId, /* ATTNUM */
AttributeRelidNumIndexId,
@@ -199,7 +199,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 2048
+ 512
},
{AuthMemRelationId, /* AUTHMEMMEMROLE */
AuthMemMemRoleIndexId,
@@ -210,7 +210,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 128
+ 16
},
{AuthMemRelationId, /* AUTHMEMROLEMEM */
AuthMemRoleMemIndexId,
@@ -221,7 +221,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 128
+ 16
},
{AuthIdRelationId, /* AUTHNAME */
AuthIdRolnameIndexId,
@@ -232,7 +232,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 128
+ 8
},
{AuthIdRelationId, /* AUTHOID */
AuthIdOidIndexId,
@@ -243,7 +243,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 128
+ 8
},
{
CastRelationId, /* CASTSOURCETARGET */
@@ -255,7 +255,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 256
+ 128
},
{OperatorClassRelationId, /* CLAAMNAMENSP */
OpclassAmNameNspIndexId,
@@ -266,7 +266,7 @@ static const struct cachedesc cacheinfo[] = {
Anum_pg_opclass_opcnamespace,
0
},
- 64
+ 32
},
{OperatorClassRelationId, /* CLAOID */
OpclassOidIndexId,
@@ -277,7 +277,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 64
+ 32
},
{CollationRelationId, /* COLLNAMEENCNSP */
CollationNameEncNspIndexId,
@@ -299,7 +299,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 64
+ 16
},
{ConversionRelationId, /* CONDEFAULT */
ConversionDefaultIndexId,
@@ -310,7 +310,7 @@ static const struct cachedesc cacheinfo[] = {
Anum_pg_conversion_contoencoding,
ObjectIdAttributeNumber,
},
- 128
+ 8
},
{ConversionRelationId, /* CONNAMENSP */
ConversionNameNspIndexId,
@@ -321,7 +321,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 128
+ 8
},
{ConstraintRelationId, /* CONSTROID */
ConstraintOidIndexId,
@@ -332,7 +332,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 1024
+ 16
},
{ConversionRelationId, /* CONVOID */
ConversionOidIndexId,
@@ -343,7 +343,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 128
+ 8
},
{DatabaseRelationId, /* DATABASEOID */
DatabaseOidIndexId,
@@ -365,7 +365,7 @@ static const struct cachedesc cacheinfo[] = {
Anum_pg_default_acl_defaclobjtype,
0
},
- 256
+ 4
},
{EnumRelationId, /* ENUMOID */
EnumOidIndexId,
@@ -376,7 +376,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 256
+ 4
},
{EnumRelationId, /* ENUMTYPOIDNAME */
EnumTypIdLabelIndexId,
@@ -387,7 +387,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 256
+ 4
},
{EventTriggerRelationId, /* EVENTTRIGGERNAME */
EventTriggerNameIndexId,
@@ -398,7 +398,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 8
+ 4
},
{EventTriggerRelationId, /* EVENTTRIGGEROID */
EventTriggerOidIndexId,
@@ -409,7 +409,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 8
+ 4
},
{ForeignDataWrapperRelationId, /* FOREIGNDATAWRAPPERNAME */
ForeignDataWrapperNameIndexId,
@@ -420,7 +420,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 8
+ 2
},
{ForeignDataWrapperRelationId, /* FOREIGNDATAWRAPPEROID */
ForeignDataWrapperOidIndexId,
@@ -431,7 +431,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 8
+ 2
},
{ForeignServerRelationId, /* FOREIGNSERVERNAME */
ForeignServerNameIndexId,
@@ -442,7 +442,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 32
+ 4
},
{ForeignServerRelationId, /* FOREIGNSERVEROID */
ForeignServerOidIndexId,
@@ -453,7 +453,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 32
+ 4
},
{ForeignTableRelationId, /* FOREIGNTABLEREL */
ForeignTableRelidIndexId,
@@ -464,7 +464,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 128
+ 4
},
{IndexRelationId, /* INDEXRELID */
IndexRelidIndexId,
@@ -475,7 +475,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 1024
+ 128
},
{LanguageRelationId, /* LANGNAME */
LanguageNameIndexId,
@@ -508,7 +508,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 256
+ 4
},
{NamespaceRelationId, /* NAMESPACEOID */
NamespaceOidIndexId,
@@ -519,7 +519,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 256
+ 4
},
{OperatorRelationId, /* OPERNAMENSP */
OperatorNameNspIndexId,
@@ -530,7 +530,7 @@ static const struct cachedesc cacheinfo[] = {
Anum_pg_operator_oprright,
Anum_pg_operator_oprnamespace
},
- 1024
+ 512
},
{OperatorRelationId, /* OPEROID */
OperatorOidIndexId,
@@ -541,7 +541,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 1024
+ 512
},
{OperatorFamilyRelationId, /* OPFAMILYAMNAMENSP */
OpfamilyAmNameNspIndexId,
@@ -552,7 +552,7 @@ static const struct cachedesc cacheinfo[] = {
Anum_pg_opfamily_opfnamespace,
0
},
- 64
+ 32
},
{OperatorFamilyRelationId, /* OPFAMILYOID */
OpfamilyOidIndexId,
@@ -563,7 +563,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 64
+ 32
},
{ProcedureRelationId, /* PROCNAMEARGSNSP */
ProcedureNameArgsNspIndexId,
@@ -574,7 +574,7 @@ static const struct cachedesc cacheinfo[] = {
Anum_pg_proc_pronamespace,
0
},
- 2048
+ 512
},
{ProcedureRelationId, /* PROCOID */
ProcedureOidIndexId,
@@ -585,7 +585,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 2048
+ 512
},
{RangeRelationId, /* RANGETYPE */
RangeTypidIndexId,
@@ -596,7 +596,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 64
+ 4
},
{RelationRelationId, /* RELNAMENSP */
ClassNameNspIndexId,
@@ -607,7 +607,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 1024
+ 256
},
{RelationRelationId, /* RELOID */
ClassOidIndexId,
@@ -618,7 +618,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 1024
+ 256
},
{RewriteRelationId, /* RULERELNAME */
RewriteRelRulenameIndexId,
@@ -629,7 +629,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 1024
+ 64
},
{StatisticRelationId, /* STATRELATTINH */
StatisticRelidAttnumInhIndexId,
@@ -640,7 +640,7 @@ static const struct cachedesc cacheinfo[] = {
Anum_pg_statistic_stainherit,
0
},
- 1024
+ 128
},
{TableSpaceRelationId, /* TABLESPACEOID */
TablespaceOidIndexId,
@@ -651,7 +651,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0,
},
- 16
+ 4
},
{TSConfigMapRelationId, /* TSCONFIGMAP */
TSConfigMapIndexId,
@@ -662,7 +662,7 @@ static const struct cachedesc cacheinfo[] = {
Anum_pg_ts_config_map_mapseqno,
0
},
- 4
+ 2
},
{TSConfigRelationId, /* TSCONFIGNAMENSP */
TSConfigNameNspIndexId,
@@ -673,7 +673,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 16
+ 2
},
{TSConfigRelationId, /* TSCONFIGOID */
TSConfigOidIndexId,
@@ -684,7 +684,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 16
+ 2
},
{TSDictionaryRelationId, /* TSDICTNAMENSP */
TSDictionaryNameNspIndexId,
@@ -695,7 +695,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 16
+ 2
},
{TSDictionaryRelationId, /* TSDICTOID */
TSDictionaryOidIndexId,
@@ -706,7 +706,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 16
+ 2
},
{TSParserRelationId, /* TSPARSERNAMENSP */
TSParserNameNspIndexId,
@@ -717,7 +717,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 4
+ 2
},
{TSParserRelationId, /* TSPARSEROID */
TSParserOidIndexId,
@@ -728,7 +728,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 4
+ 2
},
{TSTemplateRelationId, /* TSTEMPLATENAMENSP */
TSTemplateNameNspIndexId,
@@ -739,7 +739,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 16
+ 2
},
{TSTemplateRelationId, /* TSTEMPLATEOID */
TSTemplateOidIndexId,
@@ -750,7 +750,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 16
+ 2
},
{TypeRelationId, /* TYPENAMENSP */
TypeNameNspIndexId,
@@ -761,7 +761,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 1024
+ 512
},
{TypeRelationId, /* TYPEOID */
TypeOidIndexId,
@@ -772,7 +772,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 1024
+ 512
},
{UserMappingRelationId, /* USERMAPPINGOID */
UserMappingOidIndexId,
@@ -783,7 +783,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 128
+ 2
},
{UserMappingRelationId, /* USERMAPPINGUSERSERVER */
UserMappingUserServerIndexId,
@@ -794,7 +794,7 @@ static const struct cachedesc cacheinfo[] = {
0,
0
},
- 128
+ 2
}
};
diff --git a/src/include/utils/catcache.h b/src/include/utils/catcache.h
index b6e1c97..524319a 100644
--- a/src/include/utils/catcache.h
+++ b/src/include/utils/catcache.h
@@ -66,8 +66,8 @@ typedef struct catcache
long cc_lsearches; /* total # list-searches */
long cc_lhits; /* # of matches against existing lists */
#endif
- dlist_head cc_bucket[1]; /* hash buckets --- VARIABLE LENGTH ARRAY */
-} CatCache; /* VARIABLE LENGTH STRUCT */
+ dlist_head *cc_bucket; /* hash buckets */
+} CatCache;
typedef struct catctup
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
