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

[PERFORM] blocking index creation

[Neto pr]

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