Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e2HlB-0004ry-BH for pgsql-performance@arkaria.postgresql.org; Wed, 11 Oct 2017 14:13:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e2HlA-0006Zd-UH for pgsql-performance@arkaria.postgresql.org; Wed, 11 Oct 2017 14:13:20 +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 1e2HjL-0007rL-OO for pgsql-performance@postgresql.org; Wed, 11 Oct 2017 14:11:27 +0000 Received: from mail-qt0-x22b.google.com ([2607:f8b0:400d:c0d::22b]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e2HjJ-0007aV-8J for pgsql-performance@postgresql.org; Wed, 11 Oct 2017 14:11:26 +0000 Received: by mail-qt0-x22b.google.com with SMTP id 1so5535952qtn.3 for ; Wed, 11 Oct 2017 07:11:25 -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; bh=01PseIz0LmSyyQuOVjkLBuRPFLVABmMwDl/8WFPDack=; b=fQUckkKrubIJHnJp1/dkhmD9qnVvgBe+cU7iwxNPdP7hkv4xtBgqwQFLoGxWNxqNoA Me2mKuunHj5/Ws2EHcziAcyW+q8kS3Oye/rCy511RZ5ghxKaKtdRphkTd+ZmMv82nVE8 LDS9qmK9tHVJhNQREVqauZryr5/QpVU/9BMkSIybqoTeOqMCwTPBWgZPvXAzYGxwzhkl 4wmIuFbKtkTuneaDKlklyuGHuNYgwGf+ieZnOXo5ZHbw8GismiDX1Xl+ywdOVQtvCgAM TGVlV2eF5tGC5+1XaD26sYQMeGJsOuATPbYeSaDBsN4QUwLaDZqQRL8nyjy+On5YDivT jS8Q== 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; bh=01PseIz0LmSyyQuOVjkLBuRPFLVABmMwDl/8WFPDack=; b=tS36Lh4TW0un0LQXb4dSUNtfh/LTeINexOKxD9x0c3fJzsFIDjAxeW+Hzi5yDcawUH CkBJ+e5P/xmXNkhtpn19g1wp8WOCIfBWSOnNZlz5kXAbb7JiYmgIHvc8mCoYL5S7bUsg MBxwHmqEd2ppCBmR49kkBfIg9fCxDDam8lbaogtDNpA9sPVwuYFbFvVxvmYdDhhm/tWW CzTWYPzQlcrWdn36iLZ7aRljlfNoFmn6UHLJ+X44Dqj/ZXqEokw3F7wSciGtXZIv/RJ6 jAk1+bzB0domBCtCj3T4rgSHSEWY6M5QqaQhlIuq6X2w5tw1uQaUMRzgx20wUOx/ByT3 YnlQ== X-Gm-Message-State: AMCzsaU1bXli5H46XgWMusbISXuBASlnunH7I4GZiGUMuo/LlM+KNXZ1 JUIDQQ9/HloRyx5uD27XuwXNueho102gqrn3cDBqTA== X-Google-Smtp-Source: AOwi7QDDm429kOz67JToHdvmzb0+ZjwkV3EIL36fDNdoq9Qia16BO5/NeeufO6tw8KE/NvNf7nkW2XWiePRH06aNIKs= X-Received: by 10.37.212.207 with SMTP id m198mr4431287ybf.41.1507731084153; Wed, 11 Oct 2017 07:11:24 -0700 (PDT) MIME-Version: 1.0 Received: by 10.37.107.69 with HTTP; Wed, 11 Oct 2017 07:11:23 -0700 (PDT) In-Reply-To: <1507729579.2627.13.camel@cybertec.at> References: <1507729579.2627.13.camel@cybertec.at> From: Neto pr Date: Wed, 11 Oct 2017 11:11:23 -0300 Message-ID: Subject: Re: blocking index creation To: Laurenz Albe , postgres performance list Content-Type: multipart/alternative; boundary="94eb2c07dd3ab0194b055b45ffca" 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 --94eb2c07dd3ab0194b055b45ffca Content-Type: text/plain; charset="UTF-8" 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 --94eb2c07dd3ab0194b055b45ffca Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

= 2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz.albe@cybertec.at>:
Neto pr wrote:
> When creating index on table of approximately 10GB of data, the DBMS h= angs (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_PARTKE= Y).

> If someone has a hint how to speed up in= dex 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 creat= ed 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 block= ing correct? or should it be another type?

Before creating the index= , should I set the type of transaction lock? What?
---------------------= ----------------------------------------------------------------------
<= /div>
SELECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 L.mode, c.relname, lock= type,=C2=A0 l.GRANTED, 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 ----------------------------------= ----------------------------
=09 =09 =09 =09 =09 =09
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
=C2=A0
Maybe there is a long-running transaction that blocks the
ACCESS EXCLUSIVE lock required.=C2=A0 It could also be a prepared
transaction.

Yours,
Laurenz Albe

Best Regards
Neto
--94eb2c07dd3ab0194b055b45ffca--