INSERTS waiting with wait_event is "transactionid" - Mailing list pgsql-performance
| From | Nagaraj Raj |
|---|---|
| Subject | INSERTS waiting with wait_event is "transactionid" |
| Date | |
| Msg-id | 88291106.37961.1617912861916@mail.yahoo.com Whole thread Raw |
| Responses |
Re: INSERTS waiting with wait_event is "transactionid"
|
| List | 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's
CREATE 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: v32
RAM: 320 GB
shared_buffers = 64GB
effective_cache_size = 160 GB
any comments on the issue?
Thanks,
Rj
pgsql-performance by date: