Обсуждение: INSERTS waiting with wait_event is "transactionid"
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
On Thu, 2021-04-08 at 20:14 +0000, Nagaraj Raj wrote: > 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. That means that your statement is stuck behind a row lock. Row locks are stored on the table row itself and contain the transaction ID. So the process has to wait until the transaction goes away, which is implemented as waiting for a lock on the transaction ID. There must be a long running transaction that locks a row that is needed for the INSERT. It could be a row in a different table that is referenced by a foreign key. Make that long running transaction go away. Transactions should never last that long. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Hi Laurenz, Thanks for the response.
Yeah understand that, but I'm trying to figure out why it is taking too long. there is foreign key relation to this table.
Thanks,
Rj
On Friday, April 9, 2021, 02:16:08 AM PDT, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2021-04-08 at 20:14 +0000, Nagaraj Raj wrote:
> 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.
That means that your statement is stuck behind a row lock.
Row locks are stored on the table row itself and contain the transaction ID.
So the process has to wait until the transaction goes away, which is implemented
as waiting for a lock on the transaction ID.
There must be a long running transaction that locks a row that is needed for
the INSERT. It could be a row in a different table that is referenced by a
foreign key.
Make that long running transaction go away. Transactions should never last that long.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
> 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.
That means that your statement is stuck behind a row lock.
Row locks are stored on the table row itself and contain the transaction ID.
So the process has to wait until the transaction goes away, which is implemented
as waiting for a lock on the transaction ID.
There must be a long running transaction that locks a row that is needed for
the INSERT. It could be a row in a different table that is referenced by a
foreign key.
Make that long running transaction go away. Transactions should never last that long.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com