Re: [PERFORM] blocking index creation

[Scott Marlowe]

Try the queries here to check locks:

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

On Wed, Oct 11, 2017 at 7:35 PM, Neto pr  wrote:
> 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 :
>>
>> 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 :
>>>
>>>
>>>
>>> On 10/11/2017 04:11 PM, Neto pr wrote:
>>> >
>>> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe >> > >:
>>> >
>>> > 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  relationTRUE
>>> > (null)  3/71
>>> > AccessShareLock   pg_classrelationTRUE(null)
>>> > 3/71
>>> > AccessShareLock   pg_locksrelationTRUE(null)
>>> > 3/71
>>> > ShareLock lineitemrelationTRUE(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
>>
>>
>



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


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

Re: [PERFORM] blocking index creation

[Neto pr]

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 :

> 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 :
>
>>
>>
>> On 10/11/2017 04:11 PM, Neto pr wrote:
>> >
>> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe > > >:
>> >
>> > 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
>>  relationTRUE
>> > (null)3/71
>> > AccessShareLock   pg_class_relname_nsp_index  relation
>> TRUE(null)  3/71
>> > AccessShareLock   pg_class_oid_index  relationTRUE
>> (null)  3/71
>> > AccessShareLock   pg_classrelationTRUE(null)
>> 3/71
>> > AccessShareLock   pg_locksrelationTRUE(null)
>> 3/71
>> > ShareLock lineitemrelationTRUE(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
>>
>
>

Re: [PERFORM] blocking index creation

[Neto pr]

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 :

>
>
> On 10/11/2017 04:11 PM, Neto pr wrote:
> >
> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe  > >:
> >
> > 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  relationTRUE
> (null)  3/71
> > AccessShareLock   pg_classrelationTRUE(null)
> 3/71
> > AccessShareLock   pg_locksrelationTRUE(null)
> 3/71
> > ShareLock lineitemrelationTRUE(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
>

Re: [PERFORM] blocking index creation

[Tomas Vondra]

On 10/11/2017 04:11 PM, Neto pr wrote:
> 
> 2017-10-11 10:46 GMT-03:00 Laurenz Albe  >:
> 
> 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  relationTRUE
> (null)  3/71
> AccessShareLock   pg_class_oid_index  relationTRUE(null)  
> 3/71
> AccessShareLock   pg_classrelationTRUE(null)  3/71
> AccessShareLock   pg_locksrelationTRUE(null)  3/71
> ShareLock lineitemrelationTRUE(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

Re: [PERFORM] blocking index creation

[Neto pr]

2017-10-11 10:46 GMT-03:00 Laurenz Albe :

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

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

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

Best Regards
Neto

Re: [PERFORM] blocking index creation

[Laurenz Albe]

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

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 (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance