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.camel@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