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

Perdonad, ha sido un error..

Continuo más abajo...

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 donde te muestra estadísticas de los índices.
http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html.

Esta vista me permite extraer información de los diferentes INDEX, y
concretamente para la tabla que os comentaba, antes de hacer el purgado me
mostraba:

reltuples | relpages | otta | *tbloat* | wastedpages | wastedbytes |
*wastedsize* | iname | ituples | ipages | iotta | *ibloat*
| wastedipages | wastedibytes | wastedisize
----------+----------+--------+--------+-------------+-------------+------------+----------------------+----------+--------+--------+--------+--------------+--------------+-------------
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx | 13042717 | 82206 | 454115 | 0.2 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx1 | 13042717 | 156032 | 454115 | 0.3 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx10 | 13042717 | 71336 | 454115 | 0.2 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx11 | 13042717 | 106270 | 454115 | 0.2 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx12 | 3906294 | 58168 | 136008 | 0.4 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx13 | 916469 | 6804 | 31910 | 0.2 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx2 | 13042717 | 58704 | 454115 | 0.1 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx3 | 13042717 | 89979 | 454115 | 0.2 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx4 | 13042717 | 60249 | 454115 | 0.1 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx5 | 13042717 | 61480 | 454115 | 0.1 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx6 | 13042717 | 111558 | 454115 | 0.2 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx7 | 13042717 | 109711 | 454115 | 0.2 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx8 | 13042717 | 71100 | 454115 | 0.2 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_idx9 | 3456755 | 11757 | 120356 | 0.1 |
0 | 0 | 0 bytes
13042717 | 574197 | 529881 | 1.1 | 44316 | 363036672 | 346 MB
| m_transaccions_pkey | 13042717 | 55493 | 454115 | 0.1 |
0 | 0 | 0 bytes

Y después del purgado me muesta...

reltuples | relpages | otta | *tbloat* | wastedpages | wastedbytes |
*wastedsize* | iname | ituples | ipages | iotta | *ibloat* |
wastedipages | wastedibytes | wastedisize
----------+----------+-------+--------+-------------+-------------+------------+----------------------+---------+--------+-------+--------+--------------+--------------+------------
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx | 1917917 | 28878 | 66490 | 0.4 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx1 | 1917917 | 52218 | 66490 | 0.8 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx10 | 1917917 | 28881 | 66490 | 0.4 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx11 | 1917917 | 52205 | 66490 | 0.8 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx12 | 1917917 | 42442 | 66490 | 0.6 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx2 | 1917917 | 28878 | 66490 | 0.4 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx20 | 1917917 | 64200 | 66490 | 1.0 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx3 | 1917917 | 32742 | 66490 | 0.5 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx4 | 1917917 | 28882 | 66490 | 0.4 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx5 | 1917917 | 28880 | 66490 | 0.4 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx6 | 1917917 | 40556 | 66490 | 0.6 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx7 | 1917917 | 52210 | 66490 | 0.8 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx8 | 1917917 | 28879 | 66490 | 0.4 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_idx9 | 527428 | 7667 | 18285 | 0.4 |
0 | 0 | 0 bytes
1917917 | 439238 | 76980 | 5.7 | 362258 | 2967617536 | 2830 MB
| m_transaccions_pkey | 1917917 | 28877 | 66490 | 0.4 |
0 | 0 | 0 bytes

Siento mi ignorancia, pero me cuesta extraer la información de esta vista,
pero por lo que puedo observar ha habido un "desajuste" en los
índices(ibloat), (y en la tabla??(tbloat)??). Con lo que deduzco que si NO
realizo ningun REINDEX/VACUUM/(no tengo ni idea..), poco a poco irà
"deteriorandose" a nivel de optimización, es correcto??

Bueno, un poco más de información os pongo después de haber realizado un
VACUMM i un REINDEX de esta tabla(BBDD de prueba, en producción no ya que
me bloquea tabla):

reltuples | relpages | otta | *tbloat* | wastedpages | wastedbytes |
wastedsize | iname | ituples | ipages | iotta | *ibloat* |
wastedipages | wastedibytes | wastedisize
----------+----------+--------+--------+-------------+-------------+------------+----------------------+---------+--------+--------+--------+--------------+--------------+-------------
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx | 3394508 | 9310 | 117679 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx1 | 3394508 | 16830 | 117679 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx10 | 3394508 | 9310 | 117679 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx11 | 3394508 | 16828 | 117679 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx12 | 3394508 | 20607 | 117679 | 0.2 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx2 | 3394508 | 9310 | 117679 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx20 | 3394508 | 20611 | 117679 | 0.2 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx3 | 3394508 | 10313 | 117679 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx4 | 3394508 | 9310 | 117679 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx5 | 3394508 | 9310 | 117679 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx6 | 3394508 | 13073 | 117679 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx7 | 3394508 | 16830 | 117679 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx8 | 3394508 | 9310 | 117679 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_idx9 | 918427 | 2521 | 31840 | 0.1 |
0 | 0 | 0 bytes
3394508 | 439238 | 136246 | 3.2 | 302992 | 2482110464 | 2367
MB | m_transaccions_pkey | 3394508 | 9310 | 117679 | 0.1 |
0 | 0 | 0 bytes

No sé si es que estoy malinterpretando los datos, pero *wastedsize*
multiplica por 10 su valor después de realizar el purgado. Los índices
vuelven a su estado inicial después del REINDEX, i la *tbloat *passa de 1.1
a 5.7 después del purgado i de 5.7 a 3.2 después del reindex.

Quizás esté dando demasiadas vueltas al tema, pero me preocupa tener una
BBDD en producción sin su correcto mantenimineto...

Finalmente, vuelvo a las preguntas de mi primer MAIL para hacer resumen de
lo hablado:

- Para mantener una BBDD eficiente i correcta, después de hacer un
purgado masivo(de golpe o pausado), i que esté funcionando en producción:
- Se han de realizar REINDEX??? o hacer un DROP INDEX i un CREATE
INDEX??
- En caso de ser necesario (en algun momento o después del
purgado), que és mejor para no entorpecer producción??
- REINDEX??
- DROP/CREATE INDEX??
- Crear los mismos índices con otro nombre i borrar los
antiguos??
- ...
- Se ha de hacer VACUUM?? o con el "autoVACUUM" es suficiente?
- Los INDEX se "reajustan" a medida que se van haciendo DELETEs o
UPDATEs??

Siento tanto rollo, pero he llegado a la conclusión que vuestra experiencia
siempre será mejor que no ir buscando infinitamente en GOOGLE, ya que hay
demasiada información como para obtener una respuesta clara.

Mil gracias por adelantado

Un Saludo.

2014-11-17 13:24 GMT+01:00 Ruben Fitó <r(dot)fito(at)ubiquat(dot)com>:

> 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.
>

--
*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.