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 dbname 127.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 dbname 127.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 dbname 127.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 dbname 127.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 dbname 127.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