From: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
---|---|
To: | Neto pr <netopr9(at)gmail(dot)com>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: blocking index creation |
Date: | 2017-10-11 21:08:55 |
Message-ID: | ee8a58b0-f4d9-1744-d979-b388d0714a00@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 10/11/2017 04:11 PM, Neto pr wrote:
>
> 2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at
> <mailto:laurenz(dot)albe(at)cybertec(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Neto pr | 2017-10-11 22:54:16 | Re: blocking index creation |
Previous Message | Pavel Stehule | 2017-10-11 19:06:42 | Re: Stored Procedure Performance |