Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables - Mailing list pgsql-bugs
From | hubert depesz lubaczewski |
---|---|
Subject | Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables |
Date | |
Msg-id | 20160613164509.GA26282@depesz.com Whole thread Raw |
In response to | Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables (Jeff Janes <jeff.janes@gmail.com>) |
List | pgsql-bugs |
On Mon, Jun 13, 2016 at 09:27:40AM -0700, Jeff Janes wrote: > On Mon, Jun 13, 2016 at 2:39 AM, hubert depesz lubaczewski > <depesz@depesz.com> wrote: > > On Sun, Jun 12, 2016 at 01:50:48PM -0700, Jeff Janes wrote: > > > > > 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. > > What hardware and OS are you using? I only get a bit over 8kB per > table, and that amount doesn't change much between 9.3 and 9.6. These are 64bit aws virtual boxes (ec2, not rds) using ubuntu trusty. Pg is from ubuntu repo. > Can you share a representative table definition, including constraints? There are ~ 180 different tables, each looking different, and they are copied (schema, not data) across ~ 400 schemas. some tables have 1 index, some have up to 17. The 17 index table looks like this (sorry, had to redact it heavily): Column | Type | Modifiers --------------------------------------+-----------------------------+--------------------------------------------------------------------------- id | bigint | not null default nextval('...................................'::regclass) .... | character varying(255) | .......... | bigint | not null ...................... | character varying(255) | .............. | character varying(255) | not null .... | character varying(255) | ........ | timestamp without time zone | ........... | timestamp without time zone | ................... | bigint | ......... | boolean | ........................ | boolean | .......... | timestamp without time zone | .......... | timestamp without time zone | ............................ | boolean | ............. | text | ............................... | boolean | default false .......................... | character varying(255) | ....... | bigint | .............................. | boolean | default true ........... | character varying(255) | ............ | character varying(255) | default '....'::character varying .................. | bigint | ............... | bigint | not null .................. | bigint | not null ............. | character varying(255) | ............ | bigint | ............... | boolean | ............. | bigint | ................. | text | ................... | boolean | ................. | text | ............... | boolean | ....... | character varying(255) | ....... | boolean | .................................... | boolean | .................. | bigint | ...... | character varying(255) | ........ | text | ..................... | bigint | ................ | text | .................. | text | .................... | character varying(255) | ..................... | integer | .............. | character varying(255) | ......... | character varying(255) | .............. | character varying(255) | ........... | bigint | and on this there are 17 indexes, 7 fkeys, and it is being referenced by 15 other tables. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/
pgsql-bugs by date: