Subject: Re: blocking index creation
To: Neto pr <netopr9@gmail.com>, Laurenz Albe <laurenz.albe@cybertec.at>,
 postgres performance list <pgsql-performance@postgresql.org>
References: 
 <CA+wPC0NfZfrhwGCniFWctm_b5z+AyrPAveQtKt74uC44gWw_2Q@mail.gmail.com>
 <1507729579.2627.13.camel@cybertec.at>
 <CA+wPC0PC7PARUOF6GB3eQTU8x0fY1Cy4H1R0Bvm-x4fZb0gVXQ@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <ee8a58b0-f4d9-1744-d979-b388d0714a00@2ndquadrant.com>
Date: Wed, 11 Oct 2017 23:08:55 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+wPC0PC7PARUOF6GB3eQTU8x0fY1Cy4H1R0Bvm-x4fZb0gVXQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 10/11/2017 04:11 PM, Neto pr wrote:
> 
> 2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz.albe@cybertec.at
> <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


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