Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables

On Mon, Jun 13, 2016 at 08:56:56AM -0400, Peter Eisentraut wrote:
> On 6/9/16 11:46 AM, hubert depesz lubaczewski wrote:
> > Basically it looks that postgresql "caches" query plans? parsed elements? for
> > queries, but doesn't put any kind of limit to size of this cache. Which means
> > that if our app is using LOTS of different queries, the memory usage will grow
> > in time.
>
> If you compile with -DSHOW_MEMORY_STATS, it will print out memory allocation
> after every command, so you might be able to see where the memory is going.

Compiled, am running it now, but it is much slower now. And the output
is huge.

After ~5k queries, it looks like:
TopMemoryContext: 5892000 total in 701 blocks; 18344 free (68 chunks); 5873656 used
TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used
Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
MessageContext: 32768 total in 3 blocks; 6216 free (5 chunks); 26552 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
smgr relation table: 4186112 total in 9 blocks; 1374144 free (31 chunks); 2811968 used
TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used
Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
PortalMemory: 8192 total in 1 blocks; 8160 free (1 chunks); 32 used
Relcache by OID: 2088960 total in 8 blocks; 1008496 free (16 chunks); 1080464 used
CacheMemoryContext: 182443144 total in 422 blocks; 2623776 free (1 chunks); 179819368 used
MdSmgr: 1040384 total in 7 blocks; 192512 free (0 chunks); 847872 used
ident parser context: 3072 total in 2 blocks; 1416 free (1 chunks); 1656 used
hba parser context: 130048 total in 7 blocks; 42496 free (2 chunks); 87552 used
LOCALLOCK hash: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 used
Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
ErrorContext: 8192 total in 1 blocks; 8160 free (6 chunks); 32 used

I'll try to get to the end of the test (~70k tables), and then show the
same info, plus some statistics about "CacheMemoryContext" if it helps
(I'd rather not show *all* of it, though :)

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/