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

On Sun, Jun 12, 2016 at 01:50:48PM -0700, Jeff Janes wrote:
> You should probably use pgbouncer's server_lifetime to force
> connections to be discarded and recreated every now and then.  That
> parameter seems to exist specifically for dealing with this kind of
> problem.

While I know I can deal with it with server_lifetime, I still think it's
a problem in Pg - the amount of memory used for this cache should be
limitable/configurable.

> Is all of that necessary?  Can't you reproduce the problem just as
> well with just "select count(*) from <table>;" ?

No.

I'd rather not count(*) as some of these tables are large'ish, but
I did:
select * from table limit 1
And the results are:

 1 | =$ ./bad.pl
 2 |      109 24536  0.0  0.0 6821072 6312 ?        Ss   09:32   0:00 postgres: depesz-rw dbname 127.0.0.1(45788) idle
 3 | 74002 tables; press enter to continue:
 4 |
 5 | 1000: (87524 kB anon)      109 24536 65.5  0.8 6905176 530844 ?      Ss   09:32   0:01 postgres: depesz-rw dbname
127.0.0.1(45788)idle 
 6 | 2000: (115648 kB anon)      109 24536 90.0  1.0 6937684 667540 ?      Ss   09:32   0:01 postgres: depesz-rw dbname
127.0.0.1(45788)idle 
 7 | 3000: (143460 kB anon)      109 24536 77.3  1.2 6962660 794880 ?      Ss   09:32   0:02 postgres: depesz-rw dbname
127.0.0.1(45788)idle 
 8 | 4000: (170640 kB anon)      109 24536 94.3  1.4 6995196 905052 ?      Ss   09:32   0:02 postgres: depesz-rw dbname
127.0.0.1(45788)idle 
 9 | 5000: (199388 kB anon)      109 24536 58.1  1.6 7020896 1028180 ?     Ss   09:32   0:03 postgres: depesz-rw dbname
127.0.0.1(45788)idle 
10 | 6000: (226852 kB anon)      109 24536 47.4  1.8 7045140 1159604 ?     Ss   09:32   0:04 postgres: depesz-rw dbname
127.0.0.1(45788)idle 
11 | 7000: (254836 kB anon)      109 24536 38.9  2.0 7076732 1300960 ?     Ss   09:32   0:05 postgres: depesz-rw dbname
127.0.0.1(45788)idle 
12 | 8000: (286072 kB anon)      109 24536 37.5  2.2 7103824 1435416 ?     Ss   09:32   0:06 postgres: depesz-rw dbname
127.0.0.1(45788)idle 
13 | 9000: (312956 kB anon)      109 24536 34.1  2.4 7139348 1545560 ?     Ss   09:32   0:06 postgres: depesz-rw dbname
127.0.0.1(45788)idle 
14 | 10000: (339100 kB anon)      109 24536 33.0  2.6 7162500 1646176 ?     Ss   09:32   0:07 postgres: depesz-rw
dbname127.0.0.1(45788) idle 
15 | 11000: (365104 kB anon)      109 24536 32.2  2.7 7185596 1742468 ?     Ss   09:32   0:08 postgres: depesz-rw
dbname127.0.0.1(45788) idle 
16 | 12000: (391628 kB anon)      109 24536 31.6  2.9 7218820 1838912 ?     Ss   09:32   0:09 postgres: depesz-rw
dbname127.0.0.1(45788) idle 
17 | 13000: (424096 kB anon)      109 24536 31.4  3.1 7251908 1959756 ?     Ss   09:32   0:10 postgres: depesz-rw
dbname127.0.0.1(45788) idle 
18 | 14000: (458424 kB anon)      109 24536 30.6  3.3 7277756 2083952 ?     Ss   09:32   0:11 postgres: depesz-rw
dbname127.0.0.1(45788) idle 


Line #2 shows output of ps nh uww -p <backend_pid> before start of work.
There are, in total, 74002 tables, and then I iterate over list of them,
and for each, I do the select I mentioned.

Every 1000 tables, I get stats - ps output, and (in parent) sum of
"Anonymous:" lines from /proc/<backend_pid>/smaps.

As you can see - we're getting ~ 32kB of cache per table.

While I do appreciate caching of metadata, it is causing serious
problems, which we will alleviate with server_lifetime, but I would much
prefer a setting like:

internal_cache_limit = 256MB

or something similar.

Best regards,

depesz