Subject: blocking index creation

From: Neto pr <[email-masked]>
Date: Wed, 11 Oct 2017 09:58:28 -0300

Hello all,

My scenario is: postgresql 10, Processor Xeon 2.8GHz / 4-core- 8gb Ram, OS
Debian 8.

When creating index on table of approximately 10GB of data, the DBMS hangs
(I think), because even after waiting 10 hours there was no return of the
command. It happened by creating Hash indexes and B + tree indexes.
However, for some columns, it was successfully (L_RETURNFLAG, L_PARTKEY).
The data environment is the LINEITEM table (TPC-H benchmark) of link 1
<http://kejser.org/wp-content/uploads/2014/06/image_thumb2.png>below. The
columns/indexes that caught the creation were: * Hash Index in column:
L_TAX * Btree Index in column: L_RECEIPTDATE.

If someone has a hint how to speed up index creation so that it completes
successfully. I know that PostgreSQL 10 has some parallelism features and
since my server is dedicated only to the DBMS, do I change the parameters:
force_parallel_mode, max_parallel_workers_per_gather could speed up index
creation on large tables? Any tip is welcome.

DDL comand :
L_ORDERKEY BIGINT NOT NULL, - references O_ORDERKEY
L_PARTKEY BIGINT NOT NULL, - references P_PARTKEY (compound fk to PARTSUPP)
L_SUPPKEY BIGINT NOT NULL, - references S_SUPPKEY (compound fk to PARTSUPP)
L_LINENUMBER INTEGER,
L_QUANTITY DECIMAL,
L_EXTENDEDPRICE DECIMAL,
L_DISCOUNT DECIMAL,
L_TAX DECIMAL,
L_RETURNFLAG CHAR (1),
L_LINESTATUS CHAR (1),
L_SHIPDATE DATE,
L_COMMITDATE DATE,
L_RECEIPTDATE DATE,
L_SHIPINSTRUCT CHAR (25),
L_SHIPMODE CHAR (10),
L_COMMENT VARCHAR (44),PRIMARY KEY (L_ORDERKEY, L_LINENUMBER)

1- http://kejser.org/wp-content/uploads/2014/06/image_thumb2.png

best Regards

Neto

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 11 Oct 2017 15:46:19 +0200

Neto pr wrote:
> When creating index on table of approximately 10GB of data, the DBMS hangs (I think),
> because even after waiting 10 hours there was no return of the command.
> It happened by creating Hash indexes and B + tree indexes.

> If someone has a hint how to speed up index creation so that it completes successfully.

Look if CREATE INDEX is running or waiting for a lock (check the
"pg_locks" table, see if the backend consumes CPU time).

Maybe there is a long-running transaction that blocks the
ACCESS EXCLUSIVE lock required.  It could also be a prepared
transaction.

Yours,
Laurenz Albe

-- 
Sent via pgsql-performance mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

---

From: Neto pr <[email-masked]>
Date: Wed, 11 Oct 2017 11:11:23 -0300

> "pg_locks" table, see if the backend consumes CPU time).
In this moment now, there is an index being created in the Lineitem table
(+ - 10 Gb), and apparently it is locked, since it started 7 hours ago.
I've looked at the pg_locks table and look at the result, it's with
"ShareLock" lock mode.
Is this blocking correct? or should it be another type?

Before creating the index, should I set the type of transaction lock? What?
-------------------------------------------------------------------------------------------
SELECT
      L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
virtualtransaction
FROM   pg_locks l, pg_class   c
where  c.oid = l.relation

-------------- RESULT
--------------------------------------------------------------
AccessShareLock pg_class_tblspc_relfilenode_index relation TRUE (null) 3/71
AccessShareLock pg_class_relname_nsp_index relation TRUE (null) 3/71
AccessShareLock pg_class_oid_index relation TRUE (null) 3/71
AccessShareLock pg_class relation TRUE (null) 3/71
AccessShareLock pg_locks relation TRUE (null) 3/71
ShareLock lineitem relation TRUE (null) 21/3769

> ACCESS EXCLUSIVE lock required.  It could also be a prepared

Best Regards
Neto

---

From: Tomas Vondra <[email-masked]>
Date: Wed, 11 Oct 2017 23:08:55 +0200

On 10/11/2017 04:11 PM, Neto pr wrote:
> 
> 2017-10-11 10:46 GMT-03:00 Laurenz Albe <[email-masked]
> <mailto:[email-masked]>>:
> 
>     Neto pr wrote:
> In this moment now, there is an index being created in the Lineitem
> table (+ - 10 Gb), and apparently it is locked, since it started 7 hours
> ago.

Yes, CREATE INDEX acquire SHARE lock, see

   https://www.postgresql.org/docs/9.1/static/explicit-locking.html

> Before creating the index, should I set the type of transaction lock? What?

Eeee? Not sure I understand. The command acquires all necessary locks
automatically.

Well, we see something is holding a SHARE lock on the "lineitem" table,
but we don't really know what the session is doing.

There's a PID in the pg_locks table, you can use it to lookup the
session in pg_stat_activity which includes the query (and also "state"
column that will tell you if it's active or waiting for a lock.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

---

From: Neto pr <[email-masked]>
Date: Wed, 11 Oct 2017 19:54:16 -0300

Hello all,
I ran the query on PG_STAT_ACTIVITY table (Select * From
pg_stat_activity),  see the complete result in this worksheet of the link
below.

https://sites.google.com/site/goissbr/img/Resultado_pg_stat_activity-create_index.xls

The CREATE INDEX command line is identified with the orange background.
At this point 18 hours have passed and the creation of a single index has
not yet been completed.
I have verified that the command is Active status, but I do not know if
it's waiting for anything, can you help me analyze the attached output.

Regards
Neto

2017-10-11 18:08 GMT-03:00 Tomas Vondra <[email-masked]>:

> > AccessShareLock       pg_class_tblspc_relfilenode_index       relation
>       TRUE
> > (null)        3/71

---

From: Neto pr <[email-masked]>
Date: Wed, 11 Oct 2017 22:35:53 -0300

Dear,
With alternative, I tested the creation using concurrency
(CREATE INDEX CONCURRENCY NAME_IDX ON TABLE USING HASH (COLUMN);

from what I saw the index already appeared in the query result, because
before this, the index did not even appear in the result, only the Lineitem
table:

SELECT

screen result after concurrency: https://i.stack.imgur.com/htzIY.jpg

Now, I'm waiting to finish creating the index.

2017-10-11 19:54 GMT-03:00 Neto pr <[email-masked]>:

>> > AccessShareLock       pg_class_tblspc_relfilenode_index
>>  relation        TRUE
>> > (null)        3/71

---

From: Scott Marlowe <[email-masked]>
Date: Wed, 11 Oct 2017 20:25:44 -0600

Try the queries here to check locks:

https://wiki.postgresql.org/wiki/Lock_Monitoring

-- 
To understand recursion, one must first understand recursion.