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
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 |