Bom dia Pessoal, Estou com um problema no postgres aqui na empresa, tem umas querys que estão consumindo o recurso do servidor, e sua performance caiu muito, tanto que esta ocorrendo diversos timeouts, estou desconfiado de umas rotinas do Hibernate, cujo o framework é utilizado pela equipe de desenvolvimento.
fiz um monitoramento e cheguei na seguinte analise: Esses são as querys mais utilizadas: tempo(ms) quantidade tablespace(usuario) comando 9996.995 128271 nextproddb(portal) EXECUTE <unnamed> [PREPARE: create local temporary table HT_promotion_rule (id int8 not null) on commit drop 9967.797 1245 nextproddb(portal) EXECUTE <unnamed> [PREPARE: create local temporary table HT_promotion_prize (id int8 not null) on commit drop 6575.679 793 nextproddb(portal) EXECUTE <unnamed> [PREPARE: select promotionp0_.id as id32_, promotionp0_.creation_time as creation2_32_, promotionp0_.delivered as delivered32_, promotionp0_.msisdn as msisdn32_, promotionp0_.visible as visible32_, promotionp0_.enabled as enabled32_, promotionp0_.reminder_sent as reminder7_32_, promotionp0_.wappush_accepted as wappush8_32_, promotionp0_.rule_id as rule9_32_, promotionp0_.expiration_time as expiration10_32_, promotionp0_1_.delivered_items as delivered2_33_, promotionp0_1_.number_of_items as number3_33_, promotionp0_1_.promotional_category_id as promotio4_33_, case when promotionp0_1_.id is not null then 1 when promotionp0_.id is not null then 0 end as clazz_ from promotion_prize promotionp0_ left outer join category_prize promotionp0_1_ on promotionp0_.id=promotionp0_1_.id where promotionp0_.enabled=true and promotionp0_.reminder_sent=false and promotionp0_.rule_id=$1 and promotionp0_.creation_time<$2 8815.257 397 nextproddb(portal) EXECUTE <unnamed> [PREPARE: update promotion_rule set last_reminder_sent_time=$1 where (id) IN (select id from HT_promotion_rule) 4759.379 147 nextproddb(portal) EXECUTE <unnamed> [PREPARE: drop table HT_promotion_rule 5512.508 137 nextproddb(portal) EXECUTE <unnamed> [PREPARE: select id from portal where id = $1 for update Gostaria de tentar otimizar esse tempo de 10s para a criacao de uma tabela temporaria, alguem ja passou por algo semelhante e poderia me ajudar a achar uma solucao? Fico no aguardo Obrigado Denis Hoffmeister Villegas Analista de BI Desenvolvimento Tel: (11)3191-0200 R293 Cel: (11)9282-2954 SupportComm S.A. _______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
