Trigger ON EACH ROW x ON EACH STATEMENT

[Mozart Hasse]

Dúvida:

Deparei-me com uma trigger em Postgres que para um determinado caso está
horrivelmente lenta. O cenário é o seguinte: temos uma tabela de
lançamentos, com uso pesado e simultâneo, que atualiza via trigger uma
tabela de saldos, usando trigger functions do tipo ON EACH ROW, que determinam
a diferença de valores entre o OLD e o NEW para saber o que atualizar na
tabela de saldos.
Isso funciona de maneira aceitável com inclusões e alterações, que
normalmente são individuais ou têm um esquema próprio para aumentar
desempenho. Também funciona de maneira razoável quando eu excluo até 50
registros de uma vez.
O problema é que a tabela tem 130000 registros e o cliente agora resolveu
mandar várias atualizações com média de 3000 registros de uma vez, o que
para um trigger do tipo ON EACH ROW está sendo um desastre.
O SQL Server tem um modo de indicar o que foi modificado através das tabelas
virtuais 'inserted' e 'deleted', por isso a mesma rotina em SQL Server
(ceteris paribus) exclui os 3000 registros e executa via trigger os
recálculos necessários em 5 ou 6 segundos, enquanto que o Postgres leva
*horas* (mais de 10, não tivemos paciência de cronometrar) recalculando
registro a registro com o ON EACH ROW.

Alguém conhece um jeito de simular as tabelas virtuais 'inserted' e 'deleted'
no postgres para eu usar um ON EACH STATEMENT? A documentação não me
pareceu nada animadora:

http://www.postgresql.org/docs/8.4/static/trigger-definition.html
"Statement-level triggers do not currently have any way to examine the
individual row(s) modified by the statement."

Há alguma previsão de implementar isso no futuro?

Adiantando: mover os dados para uma tabela auxiliar (com BEFORE DELETE ON EACH
ROW) para depois processá-los uma só vez com AFTER DELETE ON EACH STATEMENT
tem diversos inconvenientes, pois há validações ON EACH STATEMENT que eu
preciso fazer antes de autorizar a alteração e eu teria de colocar campos
extras na tabela auxiliar para evitar que transações concorrentes misturem
registros umas das outras. Teoricamente até funciona, mas ainda deixaria o
processo lento e mais complicado do que já é.

Atenciosamente,

Mozart Hasse


_______________________________________________
pgbr-geral mailing list
pgbr-geral@...
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

[robertomello]

2009/10/29 Mozart Hasse <mozart.hasse@...>:
>
> Deparei-me com uma trigger em Postgres que para um determinado caso está
> horrivelmente lenta. O cenário é o seguinte: temos uma tabela de
> lançamentos, com uso pesado e simultâneo, que atualiza via trigger uma
> tabela de saldos, usando trigger functions do tipo ON EACH ROW, que determinam
> a diferença de valores entre o OLD e o NEW para saber o que atualizar na
> tabela de saldos.

Um dos motivos por que eu prefiro não usar tabelas cujos dados são
resultados de cálculos de outras tabelas. Prefiro usar VIEWs.

> O problema é que a tabela tem 130000 registros e o cliente agora resolveu
> mandar várias atualizações com média de 3000 registros de uma vez, o que
> para um trigger do tipo ON EACH ROW está sendo um desastre.

Não me parece tanto assim. Aqui vão algumas perguntas:

1) Quão complexos são esses cálculos?

2) Quantas consultas em outras tabelas tem que ser feitas nesses cálculos?

3) Já passaste essas consultas por seus devidos EXPLAIN para analisar
como elas estão sendo planejadas?

4) Como está a sua work_mem e parâmetros relacionados de memória? Como
estão seus parâmetros de checkpoints? Será que o PG não está gastando
muito tempo fazendo checkpoints, ou tendo pouca memória para
trabalhar?

5) Como é a sua configuração de disco? Logs estão em discos separados?
Tabelas mais usadas, como essa de saldo, estão em tablespaces
separados?

> O SQL Server tem um modo de indicar o que foi modificado através das tabelas
> virtuais 'inserted' e 'deleted', por isso a mesma rotina em SQL Server
> (ceteris paribus) exclui os 3000 registros e executa via trigger os
> recálculos necessários em 5 ou 6 segundos, enquanto que o Postgres leva
> *horas* (mais de 10, não tivemos paciência de cronometrar) recalculando
> registro a registro com o ON EACH ROW.

Tem algo errado aí. Será que isso é um caso da aplicação estar sendo
desenvolvida (e otimizada) no SQL Server e portada sem o mesmo cuidado
para PG?

Se os cálculos são complexos e tem que ser feitos ANTES dos resultados
serem enviados ao disco e transação COMMITted, como pode o SQL Server
fazer em 5 segundos? Se o SQL Server estiver apenas criando as tabelas
"virtuais" e retornando controle ao usuário, para depois executar a
checagem e escrita em disco usando as tabelas virtuais, então o
comportamento está errado, de acordo com o que você disse que não pode
acontecer abaixo (usando uma tabela separada)

Roberto
_______________________________________________
pgbr-geral mailing list
pgbr-geral@...
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

[Mozart Hasse]

Olá Roberto,

> > Deparei-me com uma trigger em Postgres que para um determinado caso está
> > horrivelmente lenta. O cenário é o seguinte: temos uma tabela de
> > lançamentos, com uso pesado e simultâneo, que atualiza via trigger uma
> > tabela de saldos, usando trigger functions do tipo ON EACH ROW, que
determinam
> > a diferença de valores entre o OLD e o NEW para saber o que atualizar na
> > tabela de saldos.
>
> Um dos motivos por que eu prefiro não usar tabelas cujos dados são
> resultados de cálculos de outras tabelas. Prefiro usar VIEWs.

Uma VIEW totalizando saldos sobre uma tabela de 130000 registros?!?!?!?!
Meu cliente não tem tempo ocioso para esperar servidor SQL nenhum ficar
somando essa tralha cada vez que ele resolver consultar o saldo de alguma
coisa. A consulta de saldos é muito mais frequente do que quaisquer
atualizações.
Minhas tabelas de saldos são 3, apenas com as chaves e valores (portanto
fisicamente muito menores). Elas tem 43000+11000+3700 registros, portanto
qualquer consulta sobre elas será sempre muito mais rápida do que qualquer
view sobre a tabela original.

> Não me parece tanto assim. Aqui vão algumas perguntas:
> 1) Quão complexos são esses cálculos?

Soma e subtração da diferença entre o valor antes e depois da alteração
em alguns registros das 3 tabelas de saldos. Cada registro individual alterado
na tabela de origem gera de 3 a 7 atualizações em cada tabela de saldos.
Quando maior o número de registros afetados na origem, maior a chance de
alterar várias vezes os mesmos registros nas tabelas de saldos.
Pouco posso fazer se não há maneira de identificar o que foi alterado a fim
do postgres atualizar cada registro uma vez só.

> 2) Quantas consultas em outras tabelas tem que ser feitas nesses cálculos?

Umas 3 consultas, uma simples e as outras 2 têm de 5 a 15 inner joins.
 
> 3) Já passaste essas consultas por seus devidos EXPLAIN para analisar
> como elas estão sendo planejadas?

As consultas individualmente são rápidas e têm todos os índices que
poderiam ajudar.
A proporção de registros trazida por cada código de conta varia pouco, o
plano de execução pode perfeitamente ser o mesmo para todas elas, tanto que
não há casos de baixo desempenho para atualizações em registros
individuais.
 
> 4) Como está a sua work_mem e parâmetros relacionados de memória? Como
> estão seus parâmetros de checkpoints? Será que o PG não está gastando
> muito tempo fazendo checkpoints, ou tendo pouca memória para
> trabalhar?

Padrão do Postgres 8.4, arquivo de configuração montado pelo assistente de
instalação.
A máquina de testes é um mísero Windows XP com 2 giga de RAM e um
processadorzinho de 1.5Ghz. A máquina do cliente é um servidor muito mais
potente, só que o problema de lentidão permanece e é reproduzido nas duas.

> 5) Como é a sua configuração de disco? Logs estão em discos separados?
> Tabelas mais usadas, como essa de saldo, estão em tablespaces
> separados?

Não, tudo está socado num só disco IDE com um só tablespace. O SQL Server
também.
Não consigo ver relevância na distribuição física dos arquivos ou na
quantidade de discos, ao menos não a ponto de algo que leva 10 horas cair
para 5 segundos por causa de um disco extra. Se dois discos fizessem o
desempenho dobrar (circunstância ideal inatingível), eu ainda teria algo
3600 vezes pior do que o esperado.

> Tem algo errado aí. Será que isso é um caso da aplicação estar sendo
> desenvolvida (e otimizada) no SQL Server e portada sem o mesmo cuidado
> para PG?

O que a aplicação faz é dar um DELETE que afeta 3000 registros. Rodando por
dentro ou por fora dela, o desempenho é o mesmo. Fiz os testes com uma única
conexão ao servidor, para excluir lentidão por acesso concorrente.
Quanto à trigger, ela faz em SQL Server exatamente as mesmas coisas que a sua
versão em Postgres. As consultas são exatamente as mesmas, as ordem de
atualização também. A única coisa que não consegui igualar foi a
existência das tabelas 'inserted' e 'deleted', que simulei movendo os dados
atualizados para tabelas auxiliares (no SQL Server também movi, só que para
tabelas temporárias). Dessa forma, apenas trocando os nomes mantive a mesma
lógica nos dois bancos de dados, com exatamente os mesmos joins sobre as
tabelas contendo os dados modificados pela transação.

> Se os cálculos são complexos e tem que ser feitos ANTES dos resultados
> serem enviados ao disco e transação COMMITted, como pode o SQL Server
> fazer em 5 segundos?

Porque ele executa essa lógica toda uma única vez, com a tabela 'deleted'
contendo 3000 registros. No Postgres eu não tenho em nenhum lugar a lista de
registros modificados, e por isso a trigger do postgres precisa fazer o
processo 3000 vezes, cada uma delas com as tabelas auxiliares tendo um
registro. Sempre soubemos desde a primeira implementação que havia essa
diferença, simplesmente convivemos com ela até achar opção melhor. Quando
alguém tiver uma, aceitarei de bom grado.

> Se o SQL Server estiver apenas criando as tabelas
> "virtuais" e retornando controle ao usuário, para depois executar a
> checagem e escrita em disco usando as tabelas virtuais, então o
> comportamento está errado, de acordo com o que você disse que não pode
> acontecer abaixo (usando uma tabela separada)

Não, é que ele é rápido assim mesmo. Temos essas triggers rodando há anos
em diversos clientes nos dois bancos de dados e em todas as incontáveis
conferências que fizemos sempre encontramos os mesmos resultados (exceto que
um leva muito mais tempo que o outro).


Mozart Hasse


_______________________________________________
pgbr-geral mailing list
pgbr-geral@...
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

[Leandro DUTRA]

Coisa velha, mas achei interessante...


2009/10/30 Mozart Hasse <mozart.hasse@...>:
> Uma VIEW totalizando saldos sobre uma tabela de 130000 registros?!?!?!?!
> Meu cliente não tem tempo ocioso para esperar servidor SQL nenhum ficar
> somando essa tralha cada vez que ele resolver consultar o saldo de alguma
> coisa. A consulta de saldos é muito mais frequente do que quaisquer
> atualizações.

Parece um caso para visões materializadas.


--
skype:leandro.gfc.dutra?chat      Yahoo!: ymsgr:sendIM?lgcdutra
[phone]              gTalk: xmpp:leandrod@...
[phone]        ICQ/AIM: aim:GoIM?screenname=61287803
BRAZIL GMT-3  MSN: msnim:chat?contact=leandro@...
Sent from Sao Paulo, SP, Brazil
_______________________________________________
pgbr-geral mailing list
pgbr-geral@...
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

[Mozart Hasse]

Dileto Dutra,

> 2009/10/30 Mozart Hasse <mozart.hasse@...>:
> > Uma VIEW totalizando saldos sobre uma tabela de 130000 registros?!?!?!?!
> > Meu cliente não tem tempo ocioso para esperar servidor SQL nenhum ficar
> > somando essa tralha cada vez que ele resolver consultar o saldo de alguma
> > coisa. A consulta de saldos é muito mais frequente do que quaisquer
> > atualizações.
 
> Parece um caso para visões materializadas.

Sim, e no meu entendimento é na prática o que estou fazendo. Cada
alteração na tabela principal atualiza via triggers os saldos em 3 tabelas
auxiliares, que nada mais são do que a totalização de saldos dos registros
originais.

O problema aqui é que o Postgres não me dá opção de fazer isso com
desempenho satisfatório no caso das exclusões em bloco (3000 registros, por
exemplo), pois a bendita trigger ON EACH ROW dispara 3000 vezes, uma para cada
registro. Se eu apelar para trigger disparando por comando (ON EACH
STATEMENT), fico igualmente com um problema de lentidão, pois qualquer
atualização implicaria em recalcular os saldos da tabela inteira, já que o
Postgres não sabe me dizer quais registros foram alterados.

No momento as opções do cliente são:
1. Conformar-se com a lentidão do Postgres.
2. Trocar para SQL Server.
3. Esperar a gente resolver alterar a aplicação inteira para usar uma stored
procedure (function) para excluir um ou mais registros ao invés de um
inocente comando DELETE, com todas as implicações e complicações de
segurança e manutenção que isso irá nos causar. Comparando com o custo de
colocar SQL Server no cliente, não tenho nem dúvida que isso não vai
acontecer.

Atenciosamente,

Mozart Hasse


_______________________________________________
pgbr-geral mailing list
pgbr-geral@...
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

[Rafael Domiciano]

Olá Mozart,

Eu faço algo parecido com o que você mencionou em mais de uma situação, e o resultado é muito satisfatório.
Você faz os 3000 mil registros dentro de uma única transação sem commit? E se você fizer cada registro uma transação?!
Você está com o vacuum / reindex atualizado nessa base? Há mais algum processo que rode nesse servidor além do Postgres?

Os testes que eu fiz com Postgres rodando em Windows não me foram nenhum pouco satisfatório. A mesma máquina, rodando Linux, me foi até 10 vezes melhor. Existe possibilidade da migração?

Att,

Rafael Domiciano

2009/11/9 Mozart Hasse <mozart.hasse@...>

Dileto Dutra,

> 2009/10/30 Mozart Hasse <mozart.hasse@...>:
> > Uma VIEW totalizando saldos sobre uma tabela de 130000 registros?!?!?!?!
> > Meu cliente não tem tempo ocioso para esperar servidor SQL nenhum ficar
> > somando essa tralha cada vez que ele resolver consultar o saldo de alguma
> > coisa. A consulta de saldos é muito mais frequente do que quaisquer
> > atualizações.

> Parece um caso para visões materializadas.

Sim, e no meu entendimento é na prática o que estou fazendo. Cada
alteração na tabela principal atualiza via triggers os saldos em 3 tabelas
auxiliares, que nada mais são do que a totalização de saldos dos registros
originais.

O problema aqui é que o Postgres não me dá opção de fazer isso com
desempenho satisfatório no caso das exclusões em bloco (3000 registros, por
exemplo), pois a bendita trigger ON EACH ROW dispara 3000 vezes, uma para cada
registro. Se eu apelar para trigger disparando por comando (ON EACH
STATEMENT), fico igualmente com um problema de lentidão, pois qualquer
atualização implicaria em recalcular os saldos da tabela inteira, já que o
Postgres não sabe me dizer quais registros foram alterados.

No momento as opções do cliente são:
1. Conformar-se com a lentidão do Postgres.
2. Trocar para SQL Server.
3. Esperar a gente resolver alterar a aplicação inteira para usar uma stored
procedure (function) para excluir um ou mais registros ao invés de um
inocente comando DELETE, com todas as implicações e complicações de
segurança e manutenção que isso irá nos causar. Comparando com o custo de
colocar SQL Server no cliente, não tenho nem dúvida que isso não vai
acontecer.

Atenciosamente,

Mozart Hasse

_______________________________________________
pgbr-geral mailing list
pgbr-geral@...
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

_______________________________________________
pgbr-geral mailing list
pgbr-geral@...
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

[Leandro DUTRA]

2009/11/9 Mozart Hasse <mozart.hasse@...>:
>> Parece um caso para visões materializadas.
>
> Sim, e no meu entendimento é na prática o que estou fazendo.

Desculpa não saber de cor nem poder pesquisar agora, mas sugiro
verificar um bom guia sobre visões materializadas em PostgreSQL como,
por exemplo, http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views
— o Google deve apontar vários outros.  Embora não tenhamos as
MATERIALIZED VIEWs do ISO SQL, temos maneiras de atender a mesma
necessidade e, talvez, um desses guias aponte uma técnica melhor.

Acho teus problemas sempre interessantes, dei falta de ti na última PgConBR.


> Se eu apelar para trigger disparando por comando (ON EACH
> STATEMENT), fico igualmente com um problema de lentidão, pois qualquer
> atualização implicaria em recalcular os saldos da tabela inteira, já que o
> Postgres não sabe me dizer quais registros foram alterados.

Não sei se entendi a questão, mas realmente o SGBD não tem o encargo
de dizer que tuplas foram alteradas — devo ter entendido errado?


--
skype:leandro.gfc.dutra?chat      Yahoo!: ymsgr:sendIM?lgcdutra
[phone]              gTalk: xmpp:leandrod@...
[phone]        ICQ/AIM: aim:GoIM?screenname=61287803
BRAZIL GMT-3  MSN: msnim:chat?contact=leandro@...
Sent from Sao Paulo, SP, Brazil
_______________________________________________
pgbr-geral mailing list
pgbr-geral@...
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

[Mozart Hasse]

Rafael,
 
> Você faz os 3000 mil registros dentro de uma única transação sem commit?
E
> se você fizer cada registro uma transação?!

Eu faço em uma única transação para levar apenas algumas horas. Se eu
adicionar a isso o custo de rede de ficar mandando start transaction / commit
ou de ficar dando delete em cada registro, vai demorar mais ainda.

> Você está com o vacuum / reindex atualizado nessa base? Há mais algum
> processo que rode nesse servidor além do Postgres?

Servidor dedicado. O cliente não tem como fazer um vacuum full toda hora, o
processo em questão é apenas uma etapa num processo enorme em que as outras
partes não podem parar. O cliente foi orientado a rodar o vacuum com mais
frequência, mas isso não fez muita diferença, continuamos medindo o
desempenho em horas, não em segundos.
 
> Os testes que eu fiz com Postgres rodando em Windows não me foram nenhum
> pouco satisfatório. A mesma máquina, rodando Linux, me foi até 10 vezes
> melhor. Existe possibilidade da migração?

O cliente usa Linux, e nós usamos Windows no nosso ambiente de testes. A
lentidão é a mesma.

Atenciosamente,

Mozart Hasse


_______________________________________________
pgbr-geral mailing list
pgbr-geral@...
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

[Mozart Hasse]

Grande Leandro,

> Embora não tenhamos as
> MATERIALIZED VIEWs do ISO SQL, temos maneiras de atender a mesma
> necessidade e, talvez, um desses guias aponte uma técnica melhor.

Já vi esse guia várias vezes...
Os requisitos de negócio não me permitem dar ao cliente visões
inconsistentes ou incompletas, logo a única opção (usando a terminologia do
link) é a 'eager materialized view'. As atualizações são tão leves quanto
possível. O volume de consultas é muito maior e não tem outra opção
conhecida de ser atendido senão pelas tabelas auxiliares atualizadas via
trigger. Atualizações individuais ou em grupos pequenos vão bem, o problema
é que o perfil desse cliente o torna o único a mexer em milhares de
registros de cada vez.

> Acho teus problemas sempre interessantes, dei falta de ti na última
PgConBR.

Hehe, quem sabe eu vá na próxima. ;-)
 

> Não sei se entendi a questão, mas realmente o SGBD não tem o encargo
> de dizer que tuplas foram alteradas ? devo ter entendido errado?

Não, você não entendeu errado, na realidade acertou na mosca, este é
e-x-a-t-a-m-e-n-t-e  o meu problema!
O Postgres *não* me diz de forma eficiente quais tuplas foram alteradas por
um comando quando quero tratar todos os registros alterados usando uma trigger
FOR EACH STATEMENT. Como consequência, a única opção que me resta é a FOR
EACH ROW, que quando usada para uma atualização de 3000 registros é um
desastre.

Atenciosamente,

Mozart Hasse


_______________________________________________
pgbr-geral mailing list
pgbr-geral@...
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

[Leandro DUTRA]

2009/11/11 Mozart Hasse <mozart.hasse@...>:
> Já vi esse guia várias vezes...

Mil perdões...


> Os requisitos de negócio não me permitem dar ao cliente visões
> inconsistentes ou incompletas, logo a única opção (usando a terminologia do
> link) é a 'eager materialized view'. As atualizações são tão leves quanto
> possível. O volume de consultas é muito maior e não tem outra opção
> conhecida de ser atendido senão pelas tabelas auxiliares atualizadas via
> trigger. Atualizações individuais ou em grupos pequenos vão bem, o problema
> é que o perfil desse cliente o torna o único a mexer em milhares de
> registros de cada vez.

Parece um problema interessante... talvez até um pedido de melhoria ou
correção de defeito para a próxima versão.  Claro que eu precisaria
conhecer mais, para dizer se é o caso.


>> Não sei se entendi a questão, mas realmente o SGBD não tem o encargo
>> de dizer que tuplas foram alteradas ? devo ter entendido errado?
>
> Não, você não entendeu errado, na realidade acertou na mosca, este é
> e-x-a-t-a-m-e-n-t-e  o meu problema!

Eu tinha entendido certo, mas até me expressei errado... de qualquer
modo, entendido.  Não sei qual seria 'a solução correta', no caso.
Talvez alguém mais ainda apareça, dizendo se realmente é um defeito ou
algo a ser completado no SGBD.

Já pensou em levar esse problema à comunidade global?  É o que eu
faria, se ninguém aqui responder... ou, até, no nosso IRC.


--
skype:leandro.gfc.dutra?chat      Yahoo!: ymsgr:sendIM?lgcdutra
[phone]              gTalk: xmpp:leandrod@...
[phone]        ICQ/AIM: aim:GoIM?screenname=61287803
BRAZIL GMT-3  MSN: msnim:chat?contact=leandro@...
Sent from Sao Paulo, SP, Brazil
_______________________________________________
pgbr-geral mailing list
pgbr-geral@...
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral