public inbox for [email protected]
help / color / mirror / Atom feedFrom: Scott Marlowe <[email protected]>
To: Neto pr <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: postgres performance list <[email protected]>
Subject: Re: blocking index creation
Date: Wed, 11 Oct 2017 20:25:44 -0600
Message-ID: <CAOR=d=2dRp1Hs=sfgoqCZ7ZPYTzWBXds8OkB_dyGD-RTgNjDpA@mail.gmail.com> (raw)
In-Reply-To: <CA+wPC0OkbiXMg1Jj6K-hwbBW7iOaKrOv_Uk-XhSvY-kDgUsL7Q@mail.gmail.com>
References: <CA+wPC0NfZfrhwGCniFWctm_b5z+AyrPAveQtKt74uC44gWw_2Q@mail.gmail.com>
<[email protected]>
<CA+wPC0PC7PARUOF6GB3eQTU8x0fY1Cy4H1R0Bvm-x4fZb0gVXQ@mail.gmail.com>
<[email protected]>
<CA+wPC0MBDAGi7enJbAT2RSmjhzg8FU0d9JXkQwRAW+RNk-sDwg@mail.gmail.com>
<CA+wPC0OkbiXMg1Jj6K-hwbBW7iOaKrOv_Uk-XhSvY-kDgUsL7Q@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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 <[email protected]> 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 <[email protected]>:
>>
>> 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 <[email protected]>:
>>>
>>>
>>>
>>> On 10/11/2017 04:11 PM, Neto pr wrote:
>>> >
>>> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe <[email protected]
>>> > <mailto:[email protected]>>:
>>> >
>>> > 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
>>
>>
>
--
To understand recursion, one must first understand recursion.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: blocking index creation
In-Reply-To: <CAOR=d=2dRp1Hs=sfgoqCZ7ZPYTzWBXds8OkB_dyGD-RTgNjDpA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox