Re: [pgsql-es-ayuda] Purgado Tablas en producción

Gracias Eduardo y Álvaro por vuestras respuestas,

Perdonad por la demora en la respuesta, pero entre viajes y el anàlisi que
he estado haciendo segun vuestras respuestas, me he entretenido, pero
finalmente he obtenido mis primeros resultados.

Primeramente os expongo con más exactitud nuestro caso y seguidamente el
resultado obtenido:

- Tenemos una tabla(la más importante) de unas 15 millones de filas y 43
columnas.
- En esta tabla existen15 índices.
- Debemos purgar unas 8 millones de filas en 2 tablas. --> 2011 y 2012.
- El procedure a seguir ha sido de:
- Por cada 1000 filas por segundo (LIMIT 1000) :
- INSERT a tabla historica
- DELETE de la tabla principal.
- Mientras tanto, se han ido realizando las TX normalmente.
- NO se ha hecho VACUUM, ni VACUUM FULL...
- NO se ha hecho REINDEX, (Ni DROP/CREATE INDEX).

El resultado obtenido es que:

- Se ha trasladado todo correctamente.
- La BBDD ni se ha inmutado.
- El tiempo de respuesta de la BBDD respecto las transacciones
demandadas por el servidor de aplicaciones, sigue siendo la de siempre.

Partiendo de estos resultados, podemos decir que hemos obtenido un
resultado deseado, pero más allá de la operativa actual, he ido buscando
más información en SAN GOOGLE, i en varias de ellas coinciden en un purgado
pausado, pero han resaltado en varias ocasiones el concepto de "bloat",
referente al espacio desaprovechado de los índices. Continuado con ello,
existe una consulta qdonde te muestra la estadísticas:

SELECT
current_database(), schemaname, tablename, /*reltuples::bigint,
relpages::bigint, otta,*/
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS
tbloat,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END
AS wastedbytes,
iname, /*ituples::bigint, ipages::bigint, iotta,*/
ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric
END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma
END))+nullhdr2+4))/(bs-20::float)) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples,
COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta
-- very rough approximation, assumes all cols
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma
END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE
nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND
s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE
23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname =
rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC

2014-11-04 15:59 GMT+01:00 Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:

> Ruben Fitó escribió:
>
> Hola,
>
> > cuál és la mejor manera para hacer un purgado de tablas de un gran nombre
> > de filas mientras se está en producción?? Nota: Antes de hacer purgado se
> > hace una copia en otras tablas.
> >
> > Los problemas que puedo visualizar son:
> >
> > - Bloqueo de la tabla, esto se podría solventar haciendo un bucle que
> me
> > hiciera un delete por fila con su propio bloque transaccional. Es mas
> lento
> > pero no hay problema para eso.
>
> DELETE no bloquea la tabla, así que no necesitas poner una transacción
> por cada registro. Si de todas maneras lo hicieras yo recomendaría
> synchronous_commit=off
>
> > - INDEX. Se han de reindexar??
>
> No
>
> > - VACUMM FULL. Obligatorio??
>
> No, pero haz VACUUM a medida que avanzas para ir liberando el espacio.
> Considera poner vacuum_cost_delay para evitar exceso de I/O
>
> > - Rendimiento... Nota: Nuestro servidor de aplicaciones que conecta
> con
> > la BBDD ha de devolver respuesta en un màximo de 2 segundos.
>
> Un delete demasiado grande puede ocupar mucho I/O; considera usar
> transacciones en bucle para borrar en bloques, quizás 100000 registros
> cada vez o así (sync_commit=off).
>
> Si vas a hacer esto frecuentemente, considera usar particiones; de esa
> forma el borrado se limita a ALTER TABLE / NO INHERIT, seguido por
> DROP TABLE.
>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

--
*Ruben Fitó *
Software Engineer
[image: Ubiquat Technologies, SL] r(dot)fito(at)ubiquat(dot)com
<j(dot)catarineu(at)ubiquat(dot)com>
www.ubiquat.com
Tota la informació continguda en aquest document i arxius adjunts és
CONFIDENCIAL protegida per llei de secret comercial. Si l'ha rebut per
error, si us plau elimini'l i posi's en contacte amb l'emissor.

All information contained in this document and any attachments are
CONFIDENTIAL and protected under trade secret laws. If you receive this
message by mistake, please delete it and notify it immediately to the
sender.