[pgbr-geral] Demora absurda processamento comando UPDATE
Bom dia ! Estou rodando 3.180 comandos UPDATE ( UPDATE areas SET mun_geocodigo = '3204906' WHERE num_prop = '23611'; por exemplo), numa tabela com as mesmas 3.180 linhas, e isso está demorando horas; no último teste que fiz numa tabela com a metade do tamanho : Query returned successfully: 1909 rows affected, *75451361* ms execution time. Meu computador é localhost; ninguém mais usando o banco; banco com 2 funções de gatilho para cópia de registros entre tabelas ligadas; enfim, um banco super singelo que roda em intranet. Fiz testes com os mesmos números de comandos insert e delete nas mesmas tabelas e demorou alguns segundos apenas. A configuração de meu computador que está como servidor é: Processador: AMD Phenom II X4 945 3.00 GHz 8 gb de ram Win 7 professional. Vi um outro post na lista sobre o mesmo tema, mas que tratava sobre um banco com 20 milhões de registros . Mesmo assim dei uma conferida nas minhas conf's e não tem nada de anormal; já fiz coisa pior no passado e nunca demorou mais que 10 seg o processamento. Alguma dica ? Grato -- Giuliano Grigolin Geógrafo CREA: SC-0760890/D Analista SIG SEGE / DTCAR * * ** ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Demora absurda processamento comando UPDATE
Em 13 de novembro de 2012 11:21, Giuliano Grego grego...@gmail.com escreveu: Bom dia ! Bom dia! Estou rodando 3.180 comandos UPDATE ( UPDATE areas SET mun_geocodigo = '3204906' WHERE num_prop = '23611'; por exemplo), numa tabela com as mesmas 3.180 linhas, e isso está demorando horas; no último teste que fiz numa tabela com a metade do tamanho : Query returned successfully: 1909 rows affected, 75451361 ms execution time. Você não conseguiria trocar estes 3.180 comandos por apenas um? Ou a essência da modificação de dados requer que cada linha receba valores diferentes? Um único comando UPDATE é mais rápido que disparar vários. Se possível faça isso. Meu computador é localhost; ninguém mais usando o banco; banco com 2 funções de gatilho para cópia de registros entre tabelas ligadas; enfim, um banco super singelo que roda em intranet. Sem ter o conteúdo (código) dos gatilhos não há como ajudar muito. A rede neste caso não interfere em nada. Para o PostgreSQL 3000 registros não representam grande massa de dados, o SGBD tira de letra. O que pode estar interferindo e causando a demora são estes TRIGGERs. Já parou para revisar seu código? Fiz testes com os mesmos números de comandos insert e delete nas mesmas tabelas e demorou alguns segundos apenas. Mais um argumento para culpar os TRIGGERs - se eles forem BEFORE/AFTER UPDATE. A configuração de meu computador que está como servidor é: Processador: AMD Phenom II X4 945 3.00 GHz 8 gb de ram Win 7 professional. Esqueceu da especificação principal: disco. Em uma operação de alteração de registros o gargalo ficará no disco, exceto se existir uma rotina mal escrita em TRIGGER que abuse de memória e CPU. Vi um outro post na lista sobre o mesmo tema, mas que tratava sobre um banco com 20 milhões de registros . Mesmo assim dei uma conferida nas minhas conf's e não tem nada de anormal; já fiz coisa pior no passado e nunca demorou mais que 10 seg o processamento. Como assim? Os mesmos comandos UPDATE no passado levavam 10 seg? Os TRIGGERs que você se referiu já existiam n'aquele tempo? -- TIAGO J. ADAMI http://www.adamiworks.com @tiadami ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Demora absurda processamento comando UPDATE
Estou rodando 3.180 comandos UPDATE ( UPDATE areas SET mun_geocodigo = '3204906' WHERE num_prop = '23611'; por exemplo), numa tabela com as mesmas 3.180 linhas, e isso está demorando horas; no último teste que fiz numa tabela com a metade do tamanho : Query returned successfully: 1909 rows affected, 75451361 ms execution time. 1- Você não conseguiria trocar estes 3.180 comandos por apenas um? Ou a essência da modificação de dados requer que cada linha receba valores diferentes? Um único comando UPDATE é mais rápido que disparar vários. Se possível faça isso. resp: Cada linha recebe um valor diferente. Meu computador é localhost; ninguém mais usando o banco; banco com 2 funções de gatilho para cópia de registros entre tabelas ligadas; enfim, um banco super singelo que roda em intranet. 2- Sem ter o conteúdo (código) dos gatilhos não há como ajudar muito. A rede neste caso não interfere em nada. Para o PostgreSQL 3000 registros não representam grande massa de dados, o SGBD tira de letra. O que pode estar interferindo e causando a demora são estes TRIGGERs. Já parou para revisar seu código? resp: eis os códigos das 2 funções / gatilhos: CREATE OR REPLACE FUNCTION calcula_area_ha() RETURNS TRIGGER LANGUAGE plpgsql AS 'BEGIN UPDATE public.areas SET area_shp = (SELECT ST_Area(geom)/1 FROM public.propriedades WHERE public.areas.num_prop = public.propriedades.num_prop AND public.areas.mun_geocodigo = public.propriedades.mun_geocodigo); RETURN OLD; END;' ; CREATE TRIGGER calcula_area_ha AFTER INSERT OR UPDATE OR DELETE ON public.propriedades FOR EACH ROW EXECUTE PROCEDURE calcula_area_ha(); CREATE OR REPLACE FUNCTION fn_insert_area_num_prop() RETURNS trigger LANGUAGE plpgsql AS 'begin insert into public.areas (num_prop) values (new.num_prop); return new; end; '; CREATE TRIGGER tg_insert_area_num_prop AFTER INSERT ON public.propriedades FOR EACH ROW EXECUTE PROCEDURE fn_insert_area_num_prop(); e o sql das 2 tabelas: CREATE TABLE propriedades ( id_num_prop serial NOT NULL, num_prop integer NOT NULL, ficha character varying(15), processo_dtc_num character varying(15), localidade character varying(50), distrito character varying(50), mun_geocodigo integer NOT NULL, estado character(2) DEFAULT 'ES'::bpchar, proprietario character varying(100), cpf character varying(11), cgc character varying(15), endereco character varying(200), cidade character varying(50), mun_end_proprt character varying(50), estado_end_proprt character(2), confront_n character varying(200), confront_s character varying(200), confront_l character varying(200), confront_o character varying(200), descricao_sumaria character varying(500), id_situacao integer, geom geometry, CONSTRAINT pk_propriedades PRIMARY KEY (num_prop , mun_geocodigo ), CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2), CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL), CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 31984) ) WITH ( OIDS=FALSE, autovacuum_enabled=true, toast.autovacuum_enabled=true CREATE TABLE areas ( id_areas serial NOT NULL, num_prop integer NOT NULL, foto character varying(15), documento character varying(15), planta character varying(15), dp character varying(15), foto1 character varying(15), documento1 character varying(15), planta1 character varying(15), dp1 character varying(15), foto2 character varying(15), documento2 character varying(15), planta2 character varying(15), dp2 character varying(15), foto3 character varying(15), documento3 character varying(15), planta3 character varying(15), dp3 character varying(15), foto4 character varying(15), documento4 character varying(15), planta4 character varying(15), dp4 character varying(15), foto5 character varying(15), documento5 character varying(15), planta5 character varying(15), dp5 character varying(15), area_shp numeric(11,4), mun_geocodigo integer, CONSTRAINT areas_pkey PRIMARY KEY (id_areas ), CONSTRAINT areas_fkey FOREIGN KEY (num_prop, mun_geocodigo) REFERENCES propriedades (num_prop, mun_geocodigo) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH ( OIDS=FALSE, autovacuum_enabled=true ); Fiz testes com os mesmos números de comandos insert e delete nas mesmas tabelas e demorou alguns segundos apenas. Mais um argumento para culpar os TRIGGERs - se eles forem BEFORE/AFTER UPDATE. A configuração de meu computador que está como servidor é: Processador: AMD Phenom II X4 945 3.00 GHz 8 gb de ram Win 7 professional. Esqueceu da especificação principal: disco. Em uma operação de alteração de registros o gargalo ficará no disco, exceto se existir uma rotina mal escrita em TRIGGER que abuse de memória e CPU. resp: 2 HDs: C: 35 GB livres de 117 GB (disco do sistema) D: 95 GB livres de 348 GB Vi um outro
Re: [pgbr-geral] Demora absurda processamento comando UPDATE
RETURNS TRIGGER LANGUAGE plpgsql AS 'BEGIN UPDATE public.areas SET area_shp = (SELECT ST_Area(geom)/1 FROM public.propriedades WHERE public.areas.num_prop = public.propriedades.num_prop AND public.areas.mun_geocodigo = public.propriedades.mun_geocodigo); RETURN OLD; END;' ; aqui, acho que tem que ser return old para delete, e new para insert e update; CREATE TRIGGER calcula_area_ha AFTER INSERT OR UPDATE OR DELETE ON public.propriedades FOR EACH ROW EXECUTE PROCEDURE calcula_area_ha(); ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Demora absurda processamento comando UPDATE
2012/11/13 Giuliano Grego grego...@gmail.com Estou rodando 3.180 comandos UPDATE ( UPDATE areas SET mun_geocodigo = '3204906' WHERE num_prop = '23611'; por exemplo), numa tabela com as mesmas 3.180 linhas, e isso está demorando horas; no último teste que fiz numa tabela com a metade do tamanho : Query returned successfully: 1909 rows affected, 75451361 ms execution time. Vários fatores podem estar influenciando, eu começaria com a verificação de locks, usando as consultas em [1] e [2]. 1- Você não conseguiria trocar estes 3.180 comandos por apenas um? Ou a essência da modificação de dados requer que cada linha receba valores diferentes? Um único comando UPDATE é mais rápido que disparar vários. Se possível faça isso. resp: Cada linha recebe um valor diferente. É informado por usuário ou é calculado/gerado? Meu computador é localhost; ninguém mais usando o banco; banco com 2 funções de gatilho para cópia de registros entre tabelas ligadas; enfim, um banco super singelo que roda em intranet. 2- Sem ter o conteúdo (código) dos gatilhos não há como ajudar muito. A rede neste caso não interfere em nada. Para o PostgreSQL 3000 registros não representam grande massa de dados, o SGBD tira de letra. O que pode estar interferindo e causando a demora são estes TRIGGERs. Já parou para revisar seu código? resp: eis os códigos das 2 funções / gatilhos: CREATE OR REPLACE FUNCTION calcula_area_ha() RETURNS TRIGGER LANGUAGE plpgsql AS 'BEGIN UPDATE public.areas SET area_shp = (SELECT ST_Area(geom)/1 FROM public.propriedades WHERE public.areas.num_prop = public.propriedades.num_prop AND public.areas.mun_geocodigo = public.propriedades.mun_geocodigo); RETURN OLD; END;' ; Não sei quão seletivo é esse WHERE do UPDATE, mas pra mim parece estranho atualizar registros (de areas) sem que a consulta seja basead em NEW e OLD. Não? CREATE TRIGGER calcula_area_ha AFTER INSERT OR UPDATE OR DELETE ON public.propriedades FOR EACH ROW EXECUTE PROCEDURE calcula_area_ha(); CREATE OR REPLACE FUNCTION fn_insert_area_num_prop() RETURNS trigger LANGUAGE plpgsql AS 'begin insert into public.areas (num_prop) values (new.num_prop); return new; end; '; CREATE TRIGGER tg_insert_area_num_prop AFTER INSERT ON public.propriedades FOR EACH ROW EXECUTE PROCEDURE fn_insert_area_num_prop(); Não entendi. Você disse que dá um UPDATE na tabela areas e essas triggers estão na tabela propriedades, elas não poderiam influenciar dessa forma. Tem mais alguma trigger em areas? Como você executa esse UPDATE? Pela aplicação? Tem como testar direto no banco? e o sql das 2 tabelas: CREATE TABLE propriedades ( id_num_prop serial NOT NULL, num_prop integer NOT NULL, ficha character varying(15), processo_dtc_num character varying(15), localidade character varying(50), distrito character varying(50), mun_geocodigo integer NOT NULL, estado character(2) DEFAULT 'ES'::bpchar, proprietario character varying(100), cpf character varying(11), cgc character varying(15), endereco character varying(200), cidade character varying(50), mun_end_proprt character varying(50), estado_end_proprt character(2), confront_n character varying(200), confront_s character varying(200), confront_l character varying(200), confront_o character varying(200), descricao_sumaria character varying(500), id_situacao integer, geom geometry, CONSTRAINT pk_propriedades PRIMARY KEY (num_prop , mun_geocodigo ), CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2), CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL), CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 31984) ) WITH ( OIDS=FALSE, autovacuum_enabled=true, toast.autovacuum_enabled=true CREATE TABLE areas ( id_areas serial NOT NULL, num_prop integer NOT NULL, foto character varying(15), documento character varying(15), planta character varying(15), dp character varying(15), foto1 character varying(15), documento1 character varying(15), planta1 character varying(15), dp1 character varying(15), foto2 character varying(15), documento2 character varying(15), planta2 character varying(15), dp2 character varying(15), foto3 character varying(15), documento3 character varying(15), planta3 character varying(15), dp3 character varying(15), foto4 character varying(15), documento4 character varying(15), planta4 character varying(15), dp4 character varying(15), foto5 character varying(15), documento5 character varying(15), planta5 character varying(15), dp5 character varying(15), area_shp numeric(11,4), mun_geocodigo integer, CONSTRAINT areas_pkey PRIMARY KEY (id_areas ), CONSTRAINT areas_fkey FOREIGN KEY (num_prop, mun_geocodigo) REFERENCES propriedades (num_prop,
Re: [pgbr-geral] Demora absurda processamento comando UPDATE
On Tue, Nov 13, 2012 at 3:58 PM, Jean Domingues ejdom...@yahoo.com.brwrote: RETURNS TRIGGER LANGUAGE plpgsql AS 'BEGIN UPDATE public.areas SET area_shp = (SELECT ST_Area(geom)/1 FROM public.propriedades WHERE public.areas.num_prop = public.propriedades.num_prop AND public.areas.mun_geocodigo = public.propriedades.mun_geocodigo); RETURN OLD; END;' ; aqui, acho que tem que ser return old para delete, e new para insert e update; A trigger é do tipo after (veja abaixo), então não fará diferença nesse caso. CREATE TRIGGER calcula_area_ha AFTER INSERT OR UPDATE OR DELETE ON public.propriedades FOR EACH ROW EXECUTE PROCEDURE calcula_area_ha(); []'s -- Matheus de Oliveira Analista de Banco de Dados PostgreSQL Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
