Re: blocking index creation

[Neto pr <netopr9@xxxxxxxxx>]

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
      L.mode, c.relname, locktype, l.GRANTED, l.transactionid, virtualtransaction
FROM pg_locks l, pg_class c
where c.oid = l.relation

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 <netopr9@xxxxxxxxx>:

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 <tomas.vondra@xxxxxxxxxxxxxxx>:

On 10/11/2017 04:11 PM, Neto pr wrote:
>
> 2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz.albe@xxxxxxxxxxx
> <mailto:laurenz.albe@cybertec.at>>:
>
>     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.
>     > However, for some columns, it was successfully (L_RETURNFLAG, L_PARTKEY).
>
>     > 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).
>
>
> 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?
>

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.

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

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