Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables - Mailing list pgsql-bugs

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