Re: Providing catalog view to pg_hba.conf file - Patch submission

On Mon, Mar 2, 2015 at 7:51 PM, Greg Stark <stark(at)mit(dot)edu> wrote:
> Nobody's allocating anything that big. It's a list of 25,000 pointers
> to 472-byte structs. That should add up to about 11MB. Instead the
> memory context is a total of 954606152 bytes which is still under a
> gigabyte and the database does start up. It drives my laptop to a
> crawl and Autovacuum crashes when it tries to start but the postmaster
> is mostly happy. That's about 38k per line or about 80x as much as it
> should be taking.

Hm. Well it seems my laptop was just messed up from having run out of
memory. This seems to have affected both ext4fs and the shared memory
system in weird ways such that Postgres wasn't reading the new config
when I thought (and thought I had confirmed) it was.

Now that the filesystem is behaving properly Postgres seems to be
behaving more reasonably. It seems to be allocating between 1kB and
1.6kB per hba line including the rawline string, the list of databases
and roles which contain structs pointing to "all". That still seems
highish but not insane.

And now that my machine is behaving better here are the timings for
the new function using SFRM_Materialize:

::***# select count(*) from pg_hba_settings();
┌───────┐
│ count │
├───────┤
│ 10002 │
└───────┘
(1 row)

Time: 31.931 ms

...

::***# select count(*) from pg_hba_settings();
┌───────┐
│ count │
├───────┤
│ 80002 │
└───────┘
(1 row)

Time: 277.376 ms

And the total memory used for the 80k lines is about 83MB.
hba parser context: 83885056 total in 22 blocks; 1322232 free (13
chunks); 82562824 used

Using the n^2 approach it's:

::# select count(*) from pg_hba_settings;
┌───────┐
│ count │
├───────┤
│ 10002 │
└───────┘
(1 row)

Time: 595.397 ms

...

::***# select count(*) from pg_hba_settings;
┌───────┐
│ count │
├───────┤
│ 20002 │
└───────┘
(1 row)

Time: 2441.081 ms

--
greg