Re: memory usage of pg_upgrade

On Mon, Sep  9, 2013 at 07:39:00PM -0400, Bruce Momjian wrote:
> > In the case of tablespaces, I should have thought you could keep a
> > hash table of the names and just store an entry id in the table
> > structure. But that's just my speculation without actually looking
> > at the code, so don't take my word for it :-)
>
> Yes, please feel free to improve the code.  I improved pg_upgrade CPU
> usage for a lerge number of objects, but never thought to look at memory
> usage.  It would be a big win to just palloc/pfree the memory, rather
> than allocate tones of memory.  If you don't get to it, I will in a few
> weeks.

Thanks you for pointing out this problem.  I have researched the cause
and the major problem was that I was allocating the maximum path length
in a struct rather than allocating just the length I needed, and was not
reusing string pointers that I knew were not going to change.

The updated attached patch significantly decreases memory consumption:

    tables        orig      patch        % decrease
    ----
    1         27,168 kB    27,168 kB     0
    1k         46,136 kB    27,920 kB    39
    2k         65,224 kB    28,796 kB    56
    4k        103,276 kB    30,472 kB    70
    8k        179,512 kB    33,900 kB    81
    16k        331,860 kB    40,788 kB    88
    32k        636,544 kB    54,572 kB    91
    64k      1,245,920 kB    81,876 kB    93

As you can see, a database with 64k tables shows a 93% decrease in
memory use.  I plan to apply this for PG 9.4.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +