Travamento na criação de índices

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Travamento na criação de índices

Neto pr
Olá pessoal

Meu cenário é: postgresql 10, Processador Xeon 2.8GHz/4-core- 8gb Ram, SO Debian 8.

Ao criar indices em uma tabela de +- 10GB de dados o SGBD trava (eu acho), pois mesmo depois de esperar 10 horas não houve retorno do comando.  Aconteceu criando índices Hash e índices B+ tree. No entanto, para algumas colunas, foi com sucesso (L_RETURNFLAG, L_PARTKEY). 
O ambiente de dados que estou me referindo é a tabela LINEITEM (benchmark TPC-H) do link [1] abaixo. As colunas/indices que travaram na criação foram: 
* Índice Hash em: L_TAX
( Índice Btree em: L_RECEIPTDATE.

Se alguém tiver uma dica de como proceder para agilizar a criação de índices, de forma que seja concluído com sucesso. Sei que o PostgreSQL 10 tem alguns recursos de paralelismo e como meu servidor é dedicado somente para o SGBD, será que alterar os parâmetros: force_parallel_mode, max_parallel_workers_per_gather poderia agilizar a criação de índices em tabelas grandes?
Qualquer dica é bem vinda. 

DDL TABELA: 
CREATE TABLE LINEITEM (
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)  

[]'s Neto

Livre de vírus. www.avast.com.

_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Reply | Threaded
Open this post in threaded view
|

Re: Travamento na criação de índices

Neto pr
Ola Pessoal

Ainda estou com o problema de travamento na criacão de índices. Mas somente quando são razoavelmente grandes.

Neste momento existe um índice sendo criado na tabela Lineitem (+- 10 Gb), e aparentemente está travado, pois iniciou-se a 7 horas atrás.
Eu consultei a tabela pg_locks e olha o resultado, está com bloqueio ShareLock = true, que a principio é o correto para create index.
Mas nao sei se quem obteve o bloqueio da tabela Lineitem, foi o comando Create Index, mas como só tem essa aplicacão rodando, é quase certeza que sim.

Antes de criar o índice, eu deveria definir o tipo de bloqueio de transacão explicito?
Qualquer dica é bem vinda.

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


Em 10 de outubro de 2017 09:25, Neto pr <[hidden email]> escreveu:
Olá pessoal

Meu cenário é: postgresql 10, Processador Xeon 2.8GHz/4-core- 8gb Ram, SO Debian 8.

Ao criar indices em uma tabela de +- 10GB de dados o SGBD trava (eu acho), pois mesmo depois de esperar 10 horas não houve retorno do comando.  Aconteceu criando índices Hash e índices B+ tree. No entanto, para algumas colunas, foi com sucesso (L_RETURNFLAG, L_PARTKEY). 
O ambiente de dados que estou me referindo é a tabela LINEITEM (benchmark TPC-H) do link [1] abaixo. As colunas/indices que travaram na criação foram: 
* Índice Hash em: L_TAX
( Índice Btree em: L_RECEIPTDATE.

Se alguém tiver uma dica de como proceder para agilizar a criação de índices, de forma que seja concluído com sucesso. Sei que o PostgreSQL 10 tem alguns recursos de paralelismo e como meu servidor é dedicado somente para o SGBD, será que alterar os parâmetros: force_parallel_mode, max_parallel_workers_per_gather poderia agilizar a criação de índices em tabelas grandes?
Qualquer dica é bem vinda. 

DDL TABELA: 
CREATE TABLE LINEITEM (
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)  

[]'s Neto

Livre de vírus. www.avast.com.


_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Reply | Threaded
Open this post in threaded view
|

Re: Travamento na criação de índices

Ilton Junior
Desculpe a intromissão.. Mas está usando o concurrently? Esse índice é grande e dependendo do seu servidor ele vai demorar. 

Em 11 de out de 2017 11:25 AM, "Neto pr" <[hidden email]> escreveu:
Ola Pessoal

Ainda estou com o problema de travamento na criacão de índices. Mas somente quando são razoavelmente grandes.

Neste momento existe um índice sendo criado na tabela Lineitem (+- 10 Gb), e aparentemente está travado, pois iniciou-se a 7 horas atrás.
Eu consultei a tabela pg_locks e olha o resultado, está com bloqueio ShareLock = true, que a principio é o correto para create index.
Mas nao sei se quem obteve o bloqueio da tabela Lineitem, foi o comando Create Index, mas como só tem essa aplicacão rodando, é quase certeza que sim.

Antes de criar o índice, eu deveria definir o tipo de bloqueio de transacão explicito?
Qualquer dica é bem vinda.

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


Em 10 de outubro de 2017 09:25, Neto pr <[hidden email]> escreveu:
Olá pessoal

Meu cenário é: postgresql 10, Processador Xeon 2.8GHz/4-core- 8gb Ram, SO Debian 8.

Ao criar indices em uma tabela de +- 10GB de dados o SGBD trava (eu acho), pois mesmo depois de esperar 10 horas não houve retorno do comando.  Aconteceu criando índices Hash e índices B+ tree. No entanto, para algumas colunas, foi com sucesso (L_RETURNFLAG, L_PARTKEY). 
O ambiente de dados que estou me referindo é a tabela LINEITEM (benchmark TPC-H) do link [1] abaixo. As colunas/indices que travaram na criação foram: 
* Índice Hash em: L_TAX
( Índice Btree em: L_RECEIPTDATE.

Se alguém tiver uma dica de como proceder para agilizar a criação de índices, de forma que seja concluído com sucesso. Sei que o PostgreSQL 10 tem alguns recursos de paralelismo e como meu servidor é dedicado somente para o SGBD, será que alterar os parâmetros: force_parallel_mode, max_parallel_workers_per_gather poderia agilizar a criação de índices em tabelas grandes?
Qualquer dica é bem vinda. 

DDL TABELA: 
CREATE TABLE LINEITEM (
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)  

[]'s Neto

Livre de vírus. www.avast.com.


_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Reply | Threaded
Open this post in threaded view
|

Re: Travamento na criação de índices

Neto pr

Inton,

Em 11 de outubro de 2017 12:27, Ilton Junior <[hidden email]> escreveu:
Desculpe a intromissão.. Mas está usando o concurrently? Esse índice é grande e dependendo do seu servidor ele vai demorar. 

Não estou usando concurrency não. Mas poderia me indicar, com e usaria concurrency .
Neste momento já se passaram 18 horas e ainda não foi concluido a criacao de um unico indice.

Fiz mais algumas pesquisas e descobri que a query está com estado ativo na tabela pg_stat_activity. Vejam o resultado completo nesta planilha do link abaixo:

https://sites.google.com/site/goissbr/img/Resultado_pg_stat_activity-create_index.xls

A linha do comando CREATE INDEX está identificada com o fundo laranja.
EU verifiquei que o comando está com estado Ativo, mas não sei se está aguardando algo, podem me ajudar a análisar o resultado anexo.

[]`s Neto

Em 11 de out de 2017 11:25 AM, "Neto pr" <[hidden email]> escreveu:
Ola Pessoal

Ainda estou com o problema de travamento na criacão de índices. Mas somente quando são razoavelmente grandes.

Neste momento existe um índice sendo criado na tabela Lineitem (+- 10 Gb), e aparentemente está travado, pois iniciou-se a 7 horas atrás.
Eu consultei a tabela pg_locks e olha o resultado, está com bloqueio ShareLock = true, que a principio é o correto para create index.
Mas nao sei se quem obteve o bloqueio da tabela Lineitem, foi o comando Create Index, mas como só tem essa aplicacão rodando, é quase certeza que sim.

Antes de criar o índice, eu deveria definir o tipo de bloqueio de transacão explicito?
Qualquer dica é bem vinda.

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


Em 10 de outubro de 2017 09:25, Neto pr <[hidden email]> escreveu:
Olá pessoal

Meu cenário é: postgresql 10, Processador Xeon 2.8GHz/4-core- 8gb Ram, SO Debian 8.

Ao criar indices em uma tabela de +- 10GB de dados o SGBD trava (eu acho), pois mesmo depois de esperar 10 horas não houve retorno do comando.  Aconteceu criando índices Hash e índices B+ tree. No entanto, para algumas colunas, foi com sucesso (L_RETURNFLAG, L_PARTKEY). 
O ambiente de dados que estou me referindo é a tabela LINEITEM (benchmark TPC-H) do link [1] abaixo. As colunas/indices que travaram na criação foram: 
* Índice Hash em: L_TAX
( Índice Btree em: L_RECEIPTDATE.

Se alguém tiver uma dica de como proceder para agilizar a criação de índices, de forma que seja concluído com sucesso. Sei que o PostgreSQL 10 tem alguns recursos de paralelismo e como meu servidor é dedicado somente para o SGBD, será que alterar os parâmetros: force_parallel_mode, max_parallel_workers_per_gather poderia agilizar a criação de índices em tabelas grandes?
Qualquer dica é bem vinda. 

DDL TABELA: 
CREATE TABLE LINEITEM (
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)  

[]'s Neto

Livre de vírus. www.avast.com.


_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral


_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Reply | Threaded
Open this post in threaded view
|

Re: Travamento na criação de índices

Euler Taveira
Em 11 de outubro de 2017 20:04, Neto pr <[hidden email]> escreveu:

A linha do comando CREATE INDEX está identificada com o fundo laranja.
EU verifiquei que o comando está com estado Ativo, mas não sei se está aguardando algo, podem me ajudar a análisar o resultado anexo.

Você não informou o sistema operacional mas se for Linux:

# strace -p 1234

onde 1234 é o PID da conexão (que você pode obter no pg_stat_activity). Se for outro sistema operacional tais como FreeBSD, AIX ou Solaris, use truss.


--
   Euler Taveira                                   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Reply | Threaded
Open this post in threaded view
|

Re: Travamento na criação de índices

Ilton Junior
Amigão! Utilize da seguinte forma:
"Create index concurrently..."
Se estiver usando Linux indico você a executar tal comando diretamente do bash do seu servidor, dessa forma as tabelas não são bloqueadas, estamos falando de um índice relativamente grande, então, vale também fazer um tunning, da uma lida sobre "indice condicional", dependendo dos dados seja o seu caso. 

Obs:
CONCURRENTLY

When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done. There are several caveats to be aware of when using this option — see Building Indexes Concurrently


Em 11 de out de 2017 8:20 PM, "Euler Taveira" <[hidden email]> escreveu:
Em 11 de outubro de 2017 20:04, Neto pr <[hidden email]> escreveu:

A linha do comando CREATE INDEX está identificada com o fundo laranja.
EU verifiquei que o comando está com estado Ativo, mas não sei se está aguardando algo, podem me ajudar a análisar o resultado anexo.

Você não informou o sistema operacional mas se for Linux:

# strace -p 1234

onde 1234 é o PID da conexão (que você pode obter no pg_stat_activity). Se for outro sistema operacional tais como FreeBSD, AIX ou Solaris, use truss.


--
   Euler Taveira                                   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Reply | Threaded
Open this post in threaded view
|

Re: Travamento na criação de índices

Neto pr


Em 11 de outubro de 2017 20:46, Ilton Junior <[hidden email]> escreveu:
Amigão! Utilize da seguinte forma:
"Create index concurrently..."

Eu estou usando linux, mas esse comando é executado dentro de uma aplicacao JAVA e deverá continuar assim, pela regras internas.

Eu testei a criacão usando o concurrency e pelo que vi o índice já apareceu no resultado da consulta, pois antes, o indice nem aparecia no resultado, somente a tabela Lineitem:

SELECT
      L.mode, c.relname, locktype,  l.GRANTED, l.transactionid, virtualtransaction
FROM   pg_locks l, pg_class   c
where  c.oid = l.relation

tela resultado apos concurrency:    https://i.stack.imgur.com/htzIY.jpg

Agora, estou aguardando terminar a criacão do índice.
 
 
Se estiver usando Linux indico você a executar tal comando diretamente do bash do seu servidor, dessa forma as tabelas não são bloqueadas, estamos falando de um índice relativamente grande, então, vale também fazer um tunning, da uma lida sobre "indice condicional", dependendo dos dados seja o seu caso. 

Obs:
CONCURRENTLY

When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done. There are several caveats to be aware of when using this option — see Building Indexes Concurrently


[ ]`s Neto
 
Em 11 de out de 2017 8:20 PM, "Euler Taveira" <[hidden email]> escreveu:
Em 11 de outubro de 2017 20:04, Neto pr <[hidden email]> escreveu:

A linha do comando CREATE INDEX está identificada com o fundo laranja.
EU verifiquei que o comando está com estado Ativo, mas não sei se está aguardando algo, podem me ajudar a análisar o resultado anexo.

Você não informou o sistema operacional mas se for Linux:

# strace -p 1234

onde 1234 é o PID da conexão (que você pode obter no pg_stat_activity). Se for outro sistema operacional tais como FreeBSD, AIX ou Solaris, use truss.


--
   Euler Taveira                                   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral


_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Reply | Threaded
Open this post in threaded view
|

Re: Travamento na criação de índices

Ilton Junior
Legal, fique monitorando o processo com top ou strace. 

Em 11 de out de 2017 10:29 PM, "Neto pr" <[hidden email]> escreveu:


Em 11 de outubro de 2017 20:46, Ilton Junior <[hidden email]> escreveu:
Amigão! Utilize da seguinte forma:
"Create index concurrently..."

Eu estou usando linux, mas esse comando é executado dentro de uma aplicacao JAVA e deverá continuar assim, pela regras internas.

Eu testei a criacão usando o concurrency e pelo que vi o índice já apareceu no resultado da consulta, pois antes, o indice nem aparecia no resultado, somente a tabela Lineitem:

SELECT
      L.mode, c.relname, locktype,  l.GRANTED, l.transactionid, virtualtransaction
FROM   pg_locks l, pg_class   c
where  c.oid = l.relation

tela resultado apos concurrency:    https://i.stack.imgur.com/htzIY.jpg

Agora, estou aguardando terminar a criacão do índice.
 
 
Se estiver usando Linux indico você a executar tal comando diretamente do bash do seu servidor, dessa forma as tabelas não são bloqueadas, estamos falando de um índice relativamente grande, então, vale também fazer um tunning, da uma lida sobre "indice condicional", dependendo dos dados seja o seu caso. 

Obs:
CONCURRENTLY

When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done. There are several caveats to be aware of when using this option — see Building Indexes Concurrently


[ ]`s Neto
 
Em 11 de out de 2017 8:20 PM, "Euler Taveira" <[hidden email]> escreveu:
Em 11 de outubro de 2017 20:04, Neto pr <[hidden email]> escreveu:

A linha do comando CREATE INDEX está identificada com o fundo laranja.
EU verifiquei que o comando está com estado Ativo, mas não sei se está aguardando algo, podem me ajudar a análisar o resultado anexo.

Você não informou o sistema operacional mas se for Linux:

# strace -p 1234

onde 1234 é o PID da conexão (que você pode obter no pg_stat_activity). Se for outro sistema operacional tais como FreeBSD, AIX ou Solaris, use truss.


--
   Euler Taveira                                   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral


_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

_______________________________________________
pgbr-geral mailing list
[hidden email]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Previous Thread Next Thread