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

On Mon, Jun 13, 2016 at 03:22:17PM +0200, hubert depesz lubaczewski wrote:
> 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 :)

OK. I can't really wait to get it all done.

Ran it for ~ 44000 queries.
Sum on anonymous memory from smaps is 1337912 kB.

Main information looks like:

TopMemoryContext: 28073888 total in 3408 blocks; 18264 free (71 chunks); 28055624 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: 65536 total in 4 blocks; 34464 free (5 chunks); 31072 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
smgr relation table: 16769024 total in 11 blocks; 2725888 free (38 chunks); 14043136 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: 8380416 total in 10 blocks; 2996848 free (21 chunks); 5383568 used
CacheMemoryContext: 734127048 total in 2791 blocks; 5143808 free (7 chunks); 728983240 used
<101491 lines removed>
MdSmgr: 8380416 total in 10 blocks; 4141120 free (0 chunks); 4239296 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 (5 chunks); 32 used

The 101491 lines that I removed were looking like:
index_context_module_progressions_on_context_module_id: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
removing index/table name, and making a summary, I got:
34859 1024 total in 1 blocks; 152 free (0 chunks); 872 used
31877 1024 total in 1 blocks; 200 free (0 chunks); 824 used
10294 1024 total in 1 blocks; 16 free (0 chunks); 1008 used
5791 1024 total in 1 blocks; 64 free (0 chunks); 960 used
3221 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used
3165 3072 total in 2 blocks; 2008 free (2 chunks); 1064 used
3146 3072 total in 2 blocks; 1784 free (2 chunks); 1288 used
2240 3072 total in 2 blocks; 1960 free (1 chunks); 1112 used
1791 3072 total in 2 blocks; 1872 free (0 chunks); 1200 used
934 3072 total in 2 blocks; 1408 free (1 chunks); 1664 used
793 3072 total in 2 blocks; 1824 free (0 chunks); 1248 used
304 3072 total in 2 blocks; 1488 free (1 chunks); 1584 used
224 3072 total in 2 blocks; 1528 free (1 chunks); 1544 used
218 3072 total in 2 blocks; 1488 free (0 chunks); 1584 used
217 3072 total in 2 blocks; 1536 free (0 chunks); 1536 used
188 3072 total in 2 blocks; 736 free (1 chunks); 2336 used
164 1024 total in 1 blocks; 40 free (0 chunks); 984 used
158 3072 total in 2 blocks; 1216 free (0 chunks); 1856 used
156 3072 total in 2 blocks; 1328 free (0 chunks); 1744 used
128 3072 total in 2 blocks; 1576 free (1 chunks); 1496 used
121 3072 total in 2 blocks; 1648 free (1 chunks); 1424 used
121 3072 total in 2 blocks; 1296 free (0 chunks); 1776 used
118 7168 total in 3 blocks; 3064 free (1 chunks); 4104 used
115 3072 total in 2 blocks; 1632 free (0 chunks); 1440 used
115 3072 total in 2 blocks; 1336 free (1 chunks); 1736 used
110 3072 total in 2 blocks; 1792 free (0 chunks); 1280 used
108 3072 total in 2 blocks; 928 free (0 chunks); 2144 used
107 3072 total in 2 blocks; 792 free (1 chunks); 2280 used
105 3072 total in 2 blocks; 1184 free (1 chunks); 1888 used
94 3072 total in 2 blocks; 1192 free (1 chunks); 1880 used
91 3072 total in 2 blocks; 1032 free (1 chunks); 2040 used
79 3072 total in 2 blocks; 1736 free (0 chunks); 1336 used
64 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used
49 3072 total in 2 blocks; 1440 free (1 chunks); 1632 used
48 3072 total in 2 blocks; 2008 free (1 chunks); 1064 used
42 3072 total in 2 blocks; 1240 free (1 chunks); 1832 used
39 3072 total in 2 blocks; 1784 free (0 chunks); 1288 used
38 3072 total in 2 blocks; 1136 free (1 chunks); 1936 used
31 3072 total in 2 blocks; 1264 free (0 chunks); 1808 used
8 3072 total in 2 blocks; 784 free (1 chunks); 2288 used
8 3072 total in 2 blocks; 1696 free (1 chunks); 1376 used
4 3072 total in 2 blocks; 1744 free (0 chunks); 1328 used
3 3072 total in 2 blocks; 1680 free (0 chunks); 1392 used
3 3072 total in 2 blocks; 1384 free (1 chunks); 1688 used
2 3072 total in 2 blocks; 1376 free (0 chunks); 1696 used

First number is how many lines end with given information.

Does it help in any way?

depesz