RE: Bajo rendimiento en postgresql cuando se lanza un delete

Por favor, responde siempre con copia a la lista. Los demás podrán seguir y
participar en la conversación.

Cuesta leer el explain así. Por favor pegalo en http://explain.depesz.com/ y
envianos el link.

> -----Mensaje original-----
> De: Francisco Manuel Quintana Trujillo
> [mailto:fquintana(at)itccanarias(dot)org]
> Enviado el: Viernes, 31 de Julio de 2009 04:50
> Para: Fernando Hevia; Alvaro Herrera
> Asunto: RE: [pgsql-es-ayuda] Bajo rendimiento en postgresql
> cuando se lanza un delete
>
> Hola,
>
> El explain de la consulta
> explain delete from observation where observation_id not in
> (select distinct(observation_id) from quality)
>
>
> "Merge Join (cost=25200825.99..150924128441552.09
> rows=51304573 width=71)"
> " Merge Cond: ((offering.offering_id)::text =
> (public.observation.offering_id)::text)"
> " Join Filter: ((public.observation.time_stamp =
> offering.min_time) OR (offering.min_time IS NULL))"
> " -> Sort (cost=1.44..1.48 rows=15 width=63)"
> " Sort Key: offering.offering_id"
> " -> Seq Scan on offering (cost=0.00..1.15 rows=15 width=63)"
> " -> Index Scan using offobstable on observation
> (cost=25200824.53..150923922371254.53 rows=69960656 width=24)"
> " Filter: (NOT (subplan))"
> " SubPlan"
> " -> Materialize (cost=25200824.53..27029360.53
> rows=131490200 width=4)"
> " -> Unique (cost=23898249.33..24555700.33
> rows=131490200 width=4)"
> " -> Sort
> (cost=23898249.33..24226974.83 rows=131490200 width=4)"
> " Sort Key: quality.observation_id"
> " -> Seq Scan on quality
> (cost=0.00..2571108.00 rows=131490200 width=4)"
> " SubPlan"
> " -> Result (cost=3.99..4.00 rows=1 width=0)"
> " InitPlan"
> " -> Limit (cost=0.00..3.99 rows=1 width=8)"
> " -> Result (cost=0.00..557397853.23
> rows=139921312 width=8)"
> " One-Time Filter: (($0)::text = ($1)::text)"
> " -> Index Scan using
> observation_time_stamp_key on observation
> (cost=0.00..557397853.23 rows=139921312 width=8)"
> " Filter: (time_stamp IS NOT NULL)"
> ""
> "Merge Join (cost=25200825.99..150924128441548.09
> rows=51304572 width=71)"
> " Merge Cond: ((offering.offering_id)::text =
> (public.observation.offering_id)::text)"
> " Join Filter: ((public.observation.time_stamp =
> offering.max_time) OR (offering.max_time IS NULL))"
> " -> Sort (cost=1.44..1.48 rows=15 width=63)"
> " Sort Key: offering.offering_id"
> " -> Seq Scan on offering (cost=0.00..1.15 rows=15 width=63)"
> " -> Index Scan using offobstable on observation
> (cost=25200824.53..150923922371254.53 rows=69960656 width=24)"
> " Filter: (NOT (subplan))"
> " SubPlan"
> " -> Materialize (cost=25200824.53..27029360.53
> rows=131490200 width=4)"
> " -> Unique (cost=23898249.33..24555700.33
> rows=131490200 width=4)"
> " -> Sort
> (cost=23898249.33..24226974.83 rows=131490200 width=4)"
> " Sort Key: quality.observation_id"
> " -> Seq Scan on quality
> (cost=0.00..2571108.00 rows=131490200 width=4)"
> " SubPlan"
> " -> Result (cost=3.99..4.00 rows=1 width=0)"
> " InitPlan"
> " -> Limit (cost=0.00..3.99 rows=1 width=8)"
> " -> Result (cost=0.00..557397853.23
> rows=139921312 width=8)"
> " One-Time Filter: (($0)::text = ($1)::text)"
> " -> Index Scan Backward using
> observation_time_stamp_key on observation
> (cost=0.00..557397853.23 rows=139921312 width=8)"
> " Filter: (time_stamp IS NOT NULL)"
> ""
> "Seq Scan on observation
> (cost=25200824.53..150923459744785.94 rows=69960656 width=6)"
> " Filter: (NOT (subplan))"
> " SubPlan"
> " -> Materialize (cost=25200824.53..27029360.53
> rows=131490200 width=4)"
> " -> Unique (cost=23898249.33..24555700.33
> rows=131490200 width=4)"
> " -> Sort (cost=23898249.33..24226974.83
> rows=131490200 width=4)"
> " Sort Key: quality.observation_id"
> " -> Seq Scan on quality
> (cost=0.00..2571108.00 rows=131490200 width=4)"
>
>
> -----Mensaje original-----
> De: Fernando Hevia [mailto:fhevia(at)ip-tel(dot)com(dot)ar] Enviado el:
> jueves, 30 de julio de 2009 15:32
> Para: Francisco Manuel Quintana Trujillo;
> pgsql-es-ayuda(at)postgresql(dot)org
> Asunto: RE: [pgsql-es-ayuda] Bajo rendimiento en postgresql
> cuando se lanza un delete
>
>
> > -----Mensaje original-----
> > De: Francisco Manuel Quintana Trujillo
> >
> > Hola a todos,
> >
> > Hace unas semanas instalé postgresql 8.3.7 en un Windows xp sp3.
> > Especificaciones de la máquina, 2 Gb de Ram, 2 discos duros
> > sata de 150 Gb cada uno, procesador Pentium 4 dual core a 3.2Ghz.
> >
> > Un disco duro se utiliza para el sistema operativo y las
> > aplicaciones, incluido el postgresql y el otro disco se
> > utiliza para la base de datos la cual ocupa 105 Gb entre
> > índices y datos. Lo más destacado es que existen 2 tablas que
> > contienen 130 millones de registros cada una.
> >
>
> Mis recomendaciones de más importante a menos importante:
>
> 1. Tratá de tener 4 discos en RAID 10. (mejor si puedes
> instalar más discos)
> 2. Si no es posible comprar más discos, configura tus 2
> discos en RAID 1
> 3. Llevá la base a Linux o a Windows 2003 Server
>
> > La verdad es que todo funciona de maravillas si no tenemos en
> > cuenta la fragmentación que sufre el disco en las inserciones
> > pero que se resuelve con un simple defrag. El caso es que a
> > la hora de realizar un select los tiempos de respuesta son
> > más que aceptables pero no así cuando ejecuto un delete de este tipo
> >
>
> Se me ocurre que la fragmentación no debiera ser demasiado
> problemática.
> Quizá afecte algo en lecturas secuenciales sobre grandes cantidades de
> datos.
>
> > delete from observation where observation_id not in (select
> > distinct(observation_id) from quality) esto significa en
> > tiempos de cpu 72 horas y sin solución por el momento.
> >
>
> El delete debiera ser mejorable pero tendrías que mostrarnos
> el resultado
> del explain y estructuras de observation y quality como para
> poder darte
> alguna sugerencia.
> ¿No tendrás clave foráneas? Asegurate de tener índices sobre
> las columnas
> referenciada siempre! Sino cada registro a eliminar forzará un scan
> secuencial sobre la tabla referenciada a fines de verificar
> se cumpla la
> constraint.
>
> > Mis preguntas son:
> >
> > ¿Es normal?,
> >
>
> Digamos que normal no. Pero depende de la actividad del
> sistema, de cuantos
> registros estás eliminando, cuantos índices se ven afectados,
> si hay claves
> foráneas sobre la tabla afectada, etc.
>
> > ¿puede ser un problema de bloqueos? ¿cómo puedo averiguar si
> > la consulta no progresa?
> >
>
> Estás borrando registros. Definitivamente puede ser un
> problema de bloqueos
> si hay otros usuarios trabajando sobre los mismos.
> En postgres puedes ver los lockeos con esta consulta: select * from
> pg_locks;
> En Windows usa perfmon.exe para monitorear la actividad del sistema y
> tendrás una idea de si está trabajando o no. Para esta consulta en
> particular presta atención a la carga sobre los discos.
>
> > ¿Qué otra solución se puede dar a la fragmentación de disco?
> > ¿se puede forzar al postgresql a reservar espacio en disco?
> >
>
> Deja este tema para lo último. Dudo que sea decisivo.
>
> > He leído las optimizaciones que se pueden realizar:
> >
> > Separar las distintas bases de datos en discos duros
> > independientes así como sus índices, discos duros en raid,
> > realizar cluster de las tablas, por el momento no son
> > posibles. Además realizo vacuum cada 2 millones de inserciones.
> >
>
> Un Raid 1 no puede "no ser posible". Haz el esfuercito y no
> lo lamentarás.
>
> > Agradeciendo de antemano cualquier ayuda
> >
> > Saludos, Oliver
> >
>
> Saludos,
> Fernando.
>