Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e2SPo-0000uJ-Ru for pgsql-performance@arkaria.postgresql.org; Thu, 12 Oct 2017 01:36:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e2SPo-0006so-DF for pgsql-performance@arkaria.postgresql.org; Thu, 12 Oct 2017 01:36:00 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e2SPm-0006r5-SR for pgsql-performance@postgresql.org; Thu, 12 Oct 2017 01:35:59 +0000 Received: from mail-qt0-x230.google.com ([2607:f8b0:400d:c0d::230]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e2SPj-0004yk-BC for pgsql-performance@postgresql.org; Thu, 12 Oct 2017 01:35:57 +0000 Received: by mail-qt0-x230.google.com with SMTP id 8so10855533qtv.1 for ; Wed, 11 Oct 2017 18:35:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=djdRx607cbiLMsNDb9gxyzyGYoU2GsP8zmMo3MezEz4=; b=rI8Io3vgHNxgWTvXLIkpCNiwESrcqpgFH0BdjeenXyruJIzQaw3MOL7GturzWf+gZ6 /JDmh19LzIsCWM8ofeqLexAZlCi7+VXoRguTYkocmfnzoZR3BoqdTEJWLd2bvgO2xtJm N9sCdlxD1A6H7E5dAA4maEZaBnQsEy97dVSLc760ftMIxloj51ddKKVjcJBzBMBFilDz iqZ+oe96eBXmYiWzSBxzHZ78uq1cTXox6fDW2zvdx3kDXN94AWVwq+YRQFniVjpNUpc+ bE9NNyu5jhvXE7n42yzmp876BmQFvRSRI3J46y4rZ6euK7ePOp3T9ahr/MybYe2IWDlS x8Zw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=djdRx607cbiLMsNDb9gxyzyGYoU2GsP8zmMo3MezEz4=; b=pkwnGZS4/obh95QMl3SIm9mhJn6XzRa44I8D0dT1bVDG15W7//oqewOLJzJeU/Ahpa 8QXbZzKDsW1RXqrUOieYPFGSub353kDszN8D7ksTQO4uEAY4vv5BxwF4/YRlz0oQ/2OQ GbavhV9XjqmVYeQhmmmTHPNnuK5/uz+1CaI77/kxAKH4HRR8KrEtiS0uzf6ymcPgCtbz dyPgL275AYuuJmbLIuniSGR0Vu4c/qBjFBV3XfGLYGGBdOCLhSANJY5XaMDbDztlhKu6 sjl2uqhmxlqAjZkUCE0Ro2VckU8VvQE9/fJbA9es2ny0BWwXrKC71aOjdjlHTsVXqnjL b/ww== X-Gm-Message-State: AMCzsaUEJDSzIUFUi+1eS/dNmbW7R+KrrukvfCdB2GX41xl8Hl/yvPVd tB3f+XyQKE1TNS74WsijOmi3rfD9J81ov5466GQ= X-Google-Smtp-Source: AOwi7QAw5KKdmuKKHczvmIsTb1SaUdCF1tLHR8506D/e4nuyVUxo847Qpiq6pYMdELjB2L2/fqlMLh5hdHf3U4ko/nI= X-Received: by 10.13.204.209 with SMTP id o200mr828322ywd.375.1507772154133; Wed, 11 Oct 2017 18:35:54 -0700 (PDT) MIME-Version: 1.0 Received: by 10.37.107.69 with HTTP; Wed, 11 Oct 2017 18:35:53 -0700 (PDT) In-Reply-To: References: <1507729579.2627.13.camel@cybertec.at> From: Neto pr Date: Wed, 11 Oct 2017 22:35:53 -0300 Message-ID: Subject: Re: blocking index creation To: Tomas Vondra Cc: Laurenz Albe , postgres performance list Content-Type: multipart/alternative; boundary="001a114e6608a641dd055b4f8fa8" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a114e6608a641dd055b4f8fa8 Content-Type: text/plain; charset="UTF-8" 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 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 >> > > --001a114e6608a641dd055b4f8fa8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Dear,
With alternative, I tested the creation using con= currency
(CREATE INDEX CONCURRENCY NAME_IDX ON TABLE USING HASH (COLUMN= );

from what I saw the index already appeared in the query result, b= ecause before this, the index did not even appear in the result, only the L= ineitem table:

SELECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 L.mode, c.re= lname, locktype, l.GRANTED, l.transactionid, virtualtransaction
FROM pg_= locks l, pg_class c
where c.oid =3D l.relation

screen result afte= r concurrency: https://i.st= ack.imgur.com/htzIY.jpg

Now, I'm waiting to finish creating = the index.

2017-10-11 19:54 GMT-03:00 Neto pr <netopr9@gmail.com>:
Hello all,
I ran the q= uery on PG_STAT_ACTIVITY table (Select * From pg_stat_activity),=C2=A0 see = the complete result in this worksheet of the link below.

https://sites.google.com/site/goissbr/im= g/Resultado_pg_stat_activity-create_index.xls

The CREATE IN= DEX 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 c= ompleted.
I have verified that the command is Active status, but I do no= t know if it's waiting for anything, can you help me analyze the attach= ed output.

RegardsNeto

2017-10-11 18:08 GMT-03= :00 Tomas Vondra <tomas.vondra@2ndquadrant.com>:


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>>:
>
>=C2=A0 =C2=A0 =C2=A0Neto pr wrote:
>=C2=A0 =C2=A0 =C2=A0> When creating index on table of approximately = 10GB of data, the DBMS hangs (I think),
>=C2=A0 =C2=A0 =C2=A0> because even after waiting 10 hours there was = no return of the command.
>=C2=A0 =C2=A0 =C2=A0> It happened by creating Hash indexes and B + t= ree indexes.
>=C2=A0 =C2=A0 =C2=A0> However, for some columns, it was successfully= (L_RETURNFLAG, L_PARTKEY).
>
>=C2=A0 =C2=A0 =C2=A0> If someone has a hint how to speed up index cr= eation so that it completes successfully.
>
>=C2=A0 =C2=A0 =C2=A0Look if CREATE INDEX is running or waiting for a lo= ck (check the
>=C2=A0 =C2=A0 =C2=A0"pg_locks" table, see if the backend cons= umes 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 hou= rs
> 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

=C2=A0 =C2=A0https://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 lock= s
automatically.

> ------------------------------------------------------------= -------------------------------
> SELECT
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 L.mode, c.relname, locktype,=C2=A0 l.GR= ANTED, l.transactionid,
> virtualtransaction
> FROM=C2=A0=C2=A0 pg_locks l, pg_class=C2=A0=C2=A0 c
> where=C2=A0 c.oid =3D l.relation
>
> -------------- RESULT
> ------------------------------------------------------------= --
> AccessShareLock=C2=A0 =C2=A0 =C2=A0 =C2=A0pg_class_tblspc_relfilenode_= index=C2=A0 =C2=A0 =C2=A0 =C2=A0relation=C2=A0 =C2=A0 =C2=A0 =C2=A0 TR= UE
> (null)=C2=A0 =C2=A0 =C2=A0 =C2=A0 3/71
> AccessShareLock=C2=A0 =C2=A0 =C2=A0 =C2=A0pg_class_relname_nsp_index= =C2=A0 =C2=A0 =C2=A0 relation=C2=A0 =C2=A0 =C2=A0 =C2=A0 TRUE=C2=A0 =C2=A0 = (null)=C2=A0 3/71
> AccessShareLock=C2=A0 =C2=A0 =C2=A0 =C2=A0pg_class_oid_index=C2=A0 =C2= =A0 =C2=A0 relation=C2=A0 =C2=A0 =C2=A0 =C2=A0 TRUE=C2=A0 =C2=A0 (null)=C2= =A0 3/71
> AccessShareLock=C2=A0 =C2=A0 =C2=A0 =C2=A0pg_class=C2=A0 =C2=A0 =C2=A0= =C2=A0 relation=C2=A0 =C2=A0 =C2=A0 =C2=A0 TRUE=C2=A0 =C2=A0 (null)=C2=A0 = 3/71
> AccessShareLock=C2=A0 =C2=A0 =C2=A0 =C2=A0pg_locks=C2=A0 =C2=A0 =C2=A0= =C2=A0 relation=C2=A0 =C2=A0 =C2=A0 =C2=A0 TRUE=C2=A0 =C2=A0 (null)=C2=A0 = 3/71
> ShareLock=C2=A0 =C2=A0 =C2=A0lineitem=C2=A0 =C2=A0 =C2=A0 =C2=A0 relat= ion=C2=A0 =C2=A0 =C2=A0 =C2=A0 TRUE=C2=A0 =C2=A0 (null)=C2=A0 21/3769
>
> =C2=A0

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&= quot;
column that will tell you if it's active or waiting for a lock.

regards

--
Tomas Vondra=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--001a114e6608a641dd055b4f8fa8--