Re: Loading table with indexed jsonb field is stalling - Mailing list pgsql-general
| From | Will Hartung |
|---|---|
| Subject | Re: Loading table with indexed jsonb field is stalling |
| Date | |
| Msg-id | 3A35A039-D8F5-4B40-9B41-42E70BEF2C2E@gmail.com Whole thread Raw |
| In response to | Re: Loading table with indexed jsonb field is stalling (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Loading table with indexed jsonb field is stalling
|
| List | pgsql-general |
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.
pgsql-general by date: