INSERTS waiting with wait_event is "transactionid"

From: Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com>
To: Psql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: INSERTS waiting with wait_event is "transactionid"
Date: 2021-04-08 20:14:21
Message-ID: 88291106.37961.1617912861916@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,
We are trying to load data around 1Bil records into one table with INSERT statements (not able to use COPY command) and they are been waiting for a lock and the wait_event is "transactionid", I didn't find any information in the documents. Queries have been waiting for hours.
Table DDL'sCREATE TABLE test_load(    billg_acct_cid_hash character varying(50) COLLATE pg_catalog."default" NOT NULL,    accs_mthd_cid_hash character varying(50) COLLATE pg_catalog."default" NOT NULL,    soc character varying(10) COLLATE pg_catalog."default" NOT NULL,    soc_desc character varying(100) COLLATE pg_catalog."default",    service_type_cd character varying(10) COLLATE pg_catalog."default",    soc_start_dt date,    soc_end_dt date,    product_eff_dt date,    product_exp_dt date,    curr_ind character varying(1) COLLATE pg_catalog."default",    load_dttm timestamp without time zone NOT NULL,    updt_dttm timestamp without time zone,    md5_chk_sum character varying(100) COLLATE pg_catalog."default",    deld_from_src_ind character(1) COLLATE pg_catalog."default",    orphan_ind character(1) COLLATE pg_catalog."default",    CONSTRAINT test_load_pk PRIMARY KEY (billg_acct_cid_hash, accs_mthd_cid_hash, soc));
query results from pg_locks ;
 SELECT COALESCE(blockingl.relation::regclass::text, blockingl.locktype) AS locked_item,    now() - blockeda.query_start AS waiting_duration,    blockeda.pid AS blocked_pid,    left(blockeda.query,7) AS blocked_query,    blockedl.mode AS blocked_mode,    blockinga.pid AS blocking_pid,    left(blockinga.query,7) AS blocking_query,    blockingl.mode AS blocking_mode   FROM pg_locks blockedl     JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid     JOIN pg_locks blockingl ON (blockingl.transactionid = blockedl.transactionid OR blockingl.relation = blockedl.relation AND blockingl.locktype = blockedl.locktype) AND blockedl.pid <> blockingl.pid     JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid  WHERE NOT blockedl.granted   order by blockeda.query_start
"transactionid" "18:20:06.068154" 681216 "INSERT " "ShareLock" 679840 "INSERT " "ExclusiveLock""transactionid" "18:19:05.504781" 679688 "INSERT " "ShareLock" 679856 "INSERT " "ExclusiveLock""transactionid" "18:18:17.30099" 679572 "INSERT " "ShareLock" 679612 "INSERT " "ShareLock""transactionid" "18:18:17.30099" 679572 "INSERT " "ShareLock" 679580 "INSERT " "ShareLock""transactionid" "18:18:17.30099" 679572 "INSERT " "ShareLock" 681108 "INSERT " "ExclusiveLock""transactionid" "18:14:17.969603" 681080 "INSERT " "ShareLock" 681204 "INSERT " "ExclusiveLock""transactionid" "18:13:41.531575" 681112 "INSERT " "ShareLock" 679636 "INSERT " "ExclusiveLock""transactionid" "18:04:16.195069" 679556 "INSERT " "ShareLock" 679776 "INSERT " "ExclusiveLock""transactionid" "17:58:54.284211" 679696 "INSERT " "ShareLock" 678940 "INSERT " "ExclusiveLock""transactionid" "17:57:54.220879" 681144 "INSERT " "ShareLock" 679792 "INSERT " "ExclusiveLock""transactionid" "17:57:28.736147" 679932 "INSERT " "ShareLock" 679696 "INSERT " "ExclusiveLock""transactionid" "17:53:48.701858" 679580 "INSERT " "ShareLock" 679572 "INSERT " "ShareLock"

query results from pg_stat_activity  ;

SELECT pg_stat_activity.pid,    pg_stat_activity.usename, pg_stat_activity.state,    now() - pg_stat_activity.query_start AS runing_time,    LEFT(pg_stat_activity.query,7) ,    pg_stat_activity.wait_event   FROM pg_stat_activity  ORDER BY (now() - pg_stat_activity.query_start) DESC;
|
| | | | | |
| 681216 | postgres | active | 07:32.7 | INSERT  | transactionid |
| 679688 | postgres | active | 06:32.2 | INSERT  | transactionid |
| 679572 | postgres | active | 05:44.0 | INSERT  | transactionid |
| 681080 | postgres | active | 01:44.6 | INSERT  | transactionid |
| 681112 | postgres | active | 01:08.2 | INSERT  | transactionid |
| 679556 | postgres | active | 51:42.9 | INSERT  | transactionid |
| 679696 | postgres | active | 46:20.9 | INSERT  | transactionid |
| 681144 | postgres | active | 45:20.9 | INSERT  | transactionid |
| 679932 | postgres | active | 44:55.4 | INSERT  | transactionid |
| 679580 | postgres | active | 41:15.4 | INSERT  | transactionid |
| 679400 | postgres | active | 39:51.2 | INSERT  | transactionid |
| 679852 | postgres | active | 37:05.3 | INSERT  | transactionid |
| 681188 | postgres | active | 36:23.2 | INSERT  | transactionid |
| 679544 | postgres | active | 35:33.4 | INSERT  | transactionid |
| 675460 | postgres | active | 26:06.8 | INSERT  | transactionid |

select version ();
PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit

CPU: v32RAM: 320 GBshared_buffers = 64GB
effective_cache_size = 160 GB

any comments on the issue?

Thanks,Rj

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Justin Pryzby 2021-04-08 21:38:13 Re: [PATCH] force_parallel_mode and GUC categories
Previous Message Szalontai Zoltán 2021-04-08 19:45:52 RE: procedure using CURSOR to insert is extremely slow