public inbox for [email protected]help / color / mirror / Atom feed
blocking index creation 7+ messages / 4 participants [nested] [flat]
* blocking index creation @ 2017-10-11 12:58 Neto pr <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Neto pr @ 2017-10-11 12:58 UTC (permalink / raw) To: pgsql-performance 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 <http://kejser.org/wp-content/uploads/2014/06/image_thumb2.png>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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: blocking index creation @ 2017-10-11 13:46 Laurenz Albe <[email protected]> parent: Neto pr <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Laurenz Albe @ 2017-10-11 13:46 UTC (permalink / raw) To: Neto pr <[email protected]>; pgsql-performance 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: blocking index creation @ 2017-10-11 14:11 Neto pr <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Neto pr @ 2017-10-11 14:11 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; pgsql-performance 2017-10-11 10:46 GMT-03:00 Laurenz Albe <[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? 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: blocking index creation @ 2017-10-11 21:08 Tomas Vondra <[email protected]> parent: Neto pr <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Tomas Vondra @ 2017-10-11 21:08 UTC (permalink / raw) To: Neto pr <[email protected]>; Laurenz Albe <[email protected]>; pgsql-performance 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 -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: blocking index creation @ 2017-10-11 22:54 Neto pr <[email protected]> parent: Tomas Vondra <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Neto pr @ 2017-10-11 22:54 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-performance 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 > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: blocking index creation @ 2017-10-12 01:35 Neto pr <[email protected]> parent: Neto pr <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Neto pr @ 2017-10-12 01:35 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-performance 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 >> > > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: blocking index creation @ 2017-10-12 02:25 Scott Marlowe <[email protected]> parent: Neto pr <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Scott Marlowe @ 2017-10-12 02:25 UTC (permalink / raw) To: Neto pr <[email protected]>; +Cc: Tomas Vondra <[email protected]>; Laurenz Albe <[email protected]>; pgsql-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 ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-10-12 02:25 UTC | newest] Thread overview: 7+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-10-11 12:58 blocking index creation Neto pr <[email protected]> 2017-10-11 13:46 ` Laurenz Albe <[email protected]> 2017-10-11 14:11 ` Neto pr <[email protected]> 2017-10-11 21:08 ` Tomas Vondra <[email protected]> 2017-10-11 22:54 ` Neto pr <[email protected]> 2017-10-12 01:35 ` Neto pr <[email protected]> 2017-10-12 02:25 ` Scott Marlowe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox