pg.ddx.io pgsql-general@postgresql.org mailing list archive help / color / mirror / Atom feed From: Will Hartung <willhartung@gmail.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Will Hartung <willhartung@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Loading table with indexed jsonb field is stalling
Date: Mon, 20 May 2019 14:14:16 -0700
Message-ID: <3A35A039-D8F5-4B40-9B41-42E70BEF2C2E@gmail.com> (raw )
In-Reply-To: <cdc049f0-1980-f5a8-726a-629345b41ef0@aklaver.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 >
> On May 20, 2019, at 11:13 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> What do the below show:
>
> 1) ps ax | grep postgres
$ ps -ax | grep postgres
1171 ? S 0:04 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf
1420 ? Ds 0:21 postgres: 10/main: checkpointer process
1421 ? Ss 0:38 postgres: 10/main: writer process
1422 ? Ss 0:22 postgres: 10/main: wal writer process
1423 ? Ss 0:04 postgres: 10/main: autovacuum launcher process
1424 ? Ss 0:04 postgres: 10/main: stats collector process
1425 ? Ss 0:00 postgres: 10/main: bgworker: logical replication launcher
15917 tty1 S 0:00 su - postgres
16300 ? Rs 10:39 postgres: 10/main: postgres willtest 10.35.60.64(54594) COPY
16444 ? Ss 0:08 postgres: 10/main: autovacuum worker process willtest waiting
16633 tty1 S+ 0:00 /usr/lib/postgresql/10/bin/psql
16641 ? Ss 0:00 postgres: 10/main: postgres postgres [local] idle
> 2) As superuser:
> select * from pg_stat_activity ;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+---------------------------------------+---------------------
| | 1425 | 10 | postgres | | | | | 2019-05-17 12:00:17.659235-07 | | | | Activity | LogicalLauncherMain | | | | | background worker
| | 1423 | | | | | | | 2019-05-17 12:00:17.658936-07 | | | | Activity | AutoVacuumMain | | | | | autovacuum launcher
16384 | willtest | 16444 | | | | | | | 2019-05-20 12:16:14.564982-07 | 2019-05-20 12:16:14.641913-07 | 2019-05-20 12:16:14.641913-07 | 2019-05-20 12:16:14.641914-07 | Lock | page | active | 624 | 623 | autovacuum: ANALYZE public.eis_entity | autovacuum worker
13051 | postgres | 16889 | 10 | postgres | psql | | | -1 | 2019-05-20 13:44:50.84062-07 | 2019-05-20 13:46:17.209382-07 | 2019-05-20 13:46:17.209382-07 | 2019-05-20 13:46:17.209387-07 | | | active | | 623 | select * from pg_stat_activity; | client backend
16384 | willtest | 16300 | 10 | postgres | psql | 10.35.60.64 | | 54594 | 2019-05-20 11:24:59.865383-07 | 2019-05-20 12:15:42.494372-07 | 2019-05-20 12:15:42.494372-07 | 2019-05-20 12:15:42.494378-07 | LWLock | WALWriteLock | active | 623 | 612 | COPY eis_entity FROM STDIN ; | client backend
| | 1421 | | | | | | | 2019-05-17 12:00:17.557683-07 | | | | Activity | BgWriterMain | | | | | background writer
| | 1420 | | | | | | | 2019-05-17 12:00:17.557992-07 | | | | | | | | | | checkpointer
| | 1422 | | | | | | | 2019-05-17 12:00:17.554268-07 | | | | | | | | | | walwriter
(8 rows)
It’s been running for about an hour and a half when I took these.
Also to note, I tried just loading the table with no indexes, and I was getting a solid 22MB/s via iostat of just raw data load (just to proof that I/O system, while certainly not extraordinary, was functional).
Thanks.
view thread (30+ messages) latest in thread
Message-ID: <3A35A039-D8F5-4B40-9B41-42E70BEF2C2E@gmail.com>
Permalink: ../3A35A039-D8F5-4B40-9B41-42E70BEF2C2E@gmail.com/
Also on: postgresql.org/message-id/3A35A039-D8F5-4B40-9B41-42E70BEF2C2E@gmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: willhartung@gmail.com, adrian.klaver@aklaver.com, tgl@sss.pgh.pa.us, pgsql-general@lists.postgresql.org
Subject: Re: Loading table with indexed jsonb field is stalling
In-Reply-To: <3A35A039-D8F5-4B40-9B41-42E70BEF2C2E@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox