Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Subject: Re: Loading table with indexed jsonb field is stalling
From: Will Hartung <willhartung@gmail.com>
In-Reply-To: <22234.1558398707@sss.pgh.pa.us>
Date: Tue, 21 May 2019 16:26:20 -0700
Cc: Will Hartung <willhartung@gmail.com>,
 Adrian Klaver <adrian.klaver@aklaver.com>,
 Ron <ronljohnsonjr@gmail.com>,
 pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <999C358F-D7BD-4DED-9ED6-AC4E42157427@gmail.com>
References: 
 <CAKMEDdxE95SC76wadMzrH454HpvCV4tOdEmuZRO=pwArEw6YVg@mail.gmail.com>
 <20505.1558202555@sss.pgh.pa.us>
 <90835D2B-1FBA-4F26-8157-7C831C24B266@gmail.com>
 <cdc049f0-1980-f5a8-726a-629345b41ef0@aklaver.com>
 <3A35A039-D8F5-4B40-9B41-42E70BEF2C2E@gmail.com>
 <806d4915-f05d-8e75-0a42-e11b48596ab5@gmail.com>
 <12E9FEDD-37E9-4D51-9BA7-94FF5039BB1B@gmail.com>
 <563e995f-d7b1-7e2f-c1f1-b00db7a18589@gmail.com>
 <3EF519CA-17FE-4B68-8B7A-78200E0A408F@gmail.com>
 <7c0a001e-d3d5-c521-1a27-77d05f547fef@aklaver.com>
 <375E86FF-5B4D-4896-B4ED-FCA8C1D3A60B@gmail.com>
 <703a6c76-854d-3d1c-8be6-30864c873d05@aklaver.com>
 <52ACFC7C-47B8-4BF1-BDF1-8810F02BD297@gmail.com>
 <22234.1558398707@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk


> On May 20, 2019, at 5:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> Well, you're the only one who's seen this problem, and none of the
> rest of us have any idea how to reproduce it.  So if you want =
something
> to get done in a timely fashion, it's up to you to show us a test =
case.

So, we had success.

We looked in to maintenance_work_mem.

By default, this value is 64MB.

Our test on AWS was against a small machine, 2 CPU, 16G.

My VM is however many cores it gets (not that it matters) and 4G of RAM.

My VM used the 64MB value for maintenance_work_mem. The AWS small VM =
used 247M.

We killed the rebuild on the small machine, it was pushing 67 hours.

I was running a load on my machine, and it was still making progress =
over night, but was at, like 17 hours. But it was moving, but had only =
loaded 2.2M rows in that time.

We grabbed one of the huge AWS instances. 64 CPU, 488G of ram. Just, =
silly.

But, that=E2=80=99s fine =E2=80=94 I=E2=80=99m good with silly.

It=E2=80=99s mainteance_work_mem was ~8G.

And we loaded all of my files on that instance in about 5.5 hours, about =
9M rows per hour.

So, obviously, maintenance_work_mem was the smoking gun. Since I don=E2=80=
=99t know the details of GIN indexing, its not clear to me how the =
maintenance_work_mem is utilized with GIN index builds, but, obviously =
64M is =E2=80=9Cnot enough=E2=80=9D, nor is 247M. And 8G is certainly =
enough.

We=E2=80=99re cautious just setting these values =E2=80=9Cto 11=E2=80=9D =
because of the fact that multiple connections can utilize them, so it =
seems to me that it=E2=80=99s important that they be =E2=80=9Cgenerous =
enough=E2=80=9D, but not overly generous.

So this is good news, no bug, and, perhaps, left to its own devices, the =
DB would have eventually built this index. Whether it would have done so =
before universal heat death, is a different question.

Can anyone discuss how the maintenance_work_mem is utilized during GIN =
index creation? On our production systems, this value is set to 1G. And =
we don=E2=80=99t seem to have any problems for day to day work. This is =
a very busy table, and we have thousands of inserts/updates daily which =
seem to proceed well enough. I have not tried to rebuild this index on =
this system, so I can=E2=80=99t say if 1G is enough to rebuild this =
index efficiently or not. But its efficient enough for our transaction =
load.

Now, that said, should I ever be in that situation of having to recover =
this table like this, I=E2=80=99d have no problem cranking that value up =
high since it would be the only real connection on the system anyway.

But I=E2=80=99m still curious how the memory is utilized during index =
builds just to have a better understanding of the nuances of the system.

Thanks all for your help.