References: 
 <CAKMEDdxE95SC76wadMzrH454HpvCV4tOdEmuZRO=pwArEw6YVg@mail.gmail.com>
User-agent: mu4e 1.3.1; emacs 26.2
From: Tim Cross <theophilusx@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Loading table with indexed jsonb field is stalling
Message-ID: <878sv4bhhj.fsf@gmail.com>
In-reply-to: 
 <CAKMEDdxE95SC76wadMzrH454HpvCV4tOdEmuZRO=pwArEw6YVg@mail.gmail.com>
Date: Sat, 18 May 2019 12:53:53 +1000
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk


Will Hartung <willhartung@gmail.com> writes:

> I am trying to load data in to a table with a jsonb field that is indexed
> as gin (field jsonb_path_ops).
>
> It's a large table, and I'm loading it 100K rows at a time. Eventually, it
> would be 30M+ rows in the table.
>
> Originally I simply loaded the table and then tried to create the index,
> but it never finished.
>
> So, I'm trying to load it incrementally.
>
> I have 2.8M rows in the table so far, the jsonb field size is, on average,
> 1600 bytes, with the largest (of the 2.8M loaded) 1930. Simply, these are
> not large structures.
>
> The first batches to load took various times for each file. Most of them <
> 1m, some took 1/2 hr.
>
> The current file is "stuck", pushing past 20hrs so far.
>
> The VM only has 4G of RAM, it is certainly "busy", but it is not swapping
> (not at the OS level).
>
> Here is a recent top:
>
> top - 11:34:01 up 1 day,  1:49,  2 users,  load average: 5.84, 4.94, 4.52
> Tasks: 103 total,   1 running,  59 sleeping,   0 stopped,   0 zombie
> %Cpu(s):  0.0 us,  1.0 sy,  0.0 ni,  0.0 id, 95.3 wa,  0.0 hi,  3.7 si,
> 0.0 st
> KiB Mem :  4040212 total,   152336 free,   181792 used,  3706084 buff/cac=
he
> KiB Swap:  4194300 total,  4189948 free,     4352 used.  3443628 avail Mem
>
> Postgres is pretty much default configurations, I have not tweaked any of
> the memory settings (such as work memory).
>
> My Mac OS host isn=E2=80=99t that busy either, but the VM adds some load,=
 and it's
> not thrashing.
>
> While I was loading the file in 100K row chunks, here are the times of ea=
ch
> respective chunk to actually load:
>
> 0:46s
> 3:17s
> 8:12s
> 9:54s
> 14:09s
> 12:07s
> 18:50s
> 9:01s
> 25:28s
> 38:49s
> 25:24s
> 1:21s
> 0:47s
> 0:32s
> 0:39s
> 0:31s
> 0:31s
> 0:28s
> 0:29s
> 0:28s
> 0:21s
> 0:27s
> 0:36s
> 0:22s
> 0:27s
> 0:20s
> 0:21s
> 0:19s
> 2:16:21s  <=E2=80=94 the last to date, but this was yesterday, now it's p=
ast 20hrs
>
> It stalled early, but then rocketed to the stalling cliff staring climax
> that it's at now.
>
> The only reason this is on a Linux VM is that I saw similar behavior
> running native Postgres 9.6 on Mac OS (the host). It didn=E2=80=99t make =
any sense
> that Mac OS would be causing this, but, who knows. Try it and see.
>
> Since the start of the load of the stalled piece, something has consumed
> over 800M of storage, I can=E2=80=99t say what, I did not check with any =
higher
> fidelity as to where the storage was going.
>
> I do not understand why this suddenly falls over a cliff. The JSONs are n=
ot
> large, so I don=E2=80=99t see how any individual one could crush the memo=
ry
> subsystem. I have to assume that PG is somehow internally thrashing or
> paging or something. I appreciate that the db is not tuned, but I would n=
ot
> expect that it would struggle so to create this index, with these values,
> and such small JSON payloads.
>
> Also, it=E2=80=99s not unique to the 29th piece. I=E2=80=99ve tried in th=
e past to skip
> those, and it still failed. This has been happening for some time (months=
),
> but I keep putting it away.
>
> Any insight is helpful. My biggest fear is that for whatever reason we wi=
ll
> not be able to reload this table during any particular crisis in the futu=
re
> should it come to that.
>
> Thanks.

Which version of postgres?

How are you loading the data? (application, psql, pg_restore) using
(insert, copy)?

--=20
Tim Cross