Para programadores: Limpiar la base de datos

Estuve trabajando ayer y hoy con la limpieza de datos sin usar del RSS Reader y noté que había un exceso de material inútil ocupando megas y megas de datos, así que pensé en hacer un poco de limpieza y explicar algunos truquitos a usar antes de que se te llene la base de datos.

Como es de esperar, es un post típico para programadores así que lo dejo chiquito y pase el que quiera o esté interesado en el tema Guiño y si no entendés nada pero te interesa el tema, te puedo explicar en los comentarios Guiño



Estrategia

Primero que nada, obviamente cada esquema de base de datos es distinto, yo tengo una estructura muy particular que sale de años de trabajar armando bases sin mucha planificación, por ende soy de agregar campos e índices que se que a futuro me van a ayudar muchísimo, trato de ser previsor.

El trabajar sin un plan maestro implica un gran riesgo pero a la vez una mayor velocidad de desarrollo, claro, hasta que llegás al nudo del problema.

Luego de ver los problemas para escalar que tenía en un hosting compartido me alquilé mi propio VPS y pude ajustar a gusto los parámetros de mi MySQL, podría haber aprovechado y pasar a un PostgreSQL o MariaDB pero me quedé en el viejo y conocido MySQL a mi riesgo. (bueh, a futuro se puede cambiar a cualquier cosa, el esquema es sencillo).

Pero lo que noté es que hacer un lector de feeds para múltiples usuarios traía un gran problema, la cantidad de datos a almacenar y la velocidad con la que hay que entregárselos al usuario.

Esto implica que traer 100 registros de una tabla con 300.000 se empieza a poner pesado, pero claro, si todos esos 300.000 registros se usan ¿cómo negárselos? el hecho real es que difícilmente los esten usando, es más, nadie los está usando, el 75% de esos registros nunca serán vistos. Es parte de cómo funciona el RSS, uno ve lo último, no lo de hace meses, salvo que lo marque como favorito :P

Entonces el dilema estaba en, primero, poner un límite ficticio, segundo, borrar el excedente.

Con la incorporación del importador de feeds al menos cuatro usuarios subieron 1000 feeds distintos en total, si, cuatro tipos de los cuales uno al menos "lee" 350 feeds él solo, está claro que no lee un carajo y que me estaba llenando la base de datos de registros que nunca serían leídos e impactaban en todos los demás usuarios.

La base de datos pasó de pesar unos 250Mb a 800Mb en un día.

Antes de entrar en pánico lo primero es detectar que se está usando. Por suerte fui previsor, mantengo un registro de feeds que tienen suscriptores y no leo los que ya no tienen suscriptores, es simple, achica mucho el costo.

Segundo, hay que darle un límite, yo lo establecí en 50 items "olvidados", es decir, items que ya leyeron ,que no tienen a ningún lector nuevo esperandolos, guardo 50 porque si se suscribe uno nuevo le doy esos 50, total, casi ningún archivo de RSS tiene más de 25-30 registros, tenía colchón suficiente.

Pero esto no alcanza, hay tipos que se suscriben a un feed y esperan acumular 1000, 2000 items sin leer ¿para qué? ¿de qué me sirve? así que para ellos decidí ponerles un tope de 200. Si no te gusta, andá a otro feed reader que te permita más, pero no se dan una idea del costo desmesurado en espacio y velocidad que implica que cada tipo tenga más de 300 items en un feed sin leer y que ¡nunca leerá!

Cualquier feed de noticias te tira fácil 200 items por día, si sos de leer el fin de semana esos feeds tampoco podés consumirlos, tendrías 1400 para ver el domingo, te revienta la cabeza.

Para borrar el excedente viejo fue fácil, puse una cantidad máxima, luego busco la fecha a partir de la cual llego a ese máximo, desde esa fecha hacia atrás borro todo .

Este proceso lo tengo que hacer al menos una vez al día o cada dos días, da igual, es limpieza pura. Pasé de tener 850Mb a menos de 200Mb, venía acumulando basura a lo lindo.

MySQL

Ahora bien, aquí vamos a lo técnico, encontré que MySQL tiene algunas limitaciones con los subquerys y para identificar sobrantes en una base de datos los necesitaba, un truco que encontré para poder usar un order by dentro de un subquery es envolverlo en otro query:

DELETE FROM ldv_rss_feed_item WHERE id_item IN(
SELECT X.estan FROM 
(SELECT A.fecha_item, A.id_item AS 'estan', B.id_item AS 'noestan'
FROM `ldv_rss_feed_item` A
LEFT JOIN ldv_rss_item_usuario B ON A.id_item = B.id_item
WHERE B.id_item IS NULL 
AND A.id_feed = $id_feed AND A.contadorfavs = 0 
GROUP BY 1 , 2
ORDER BY A.fecha_item desc
LIMIT 50 , 1000) X) 

¿no me digan que no es fabulosamente cochino?

Leí todo lo que pude de MySQL y esa es la solución oficial, meter el query que vas a usar como subquery adentro de otro subquery para que no reviente todo, lo genial es que funciona y es bastante rápido.

Cuales items borro está dentro del subquery, allí compar odos cosas, primero leo los items de tal feed, además me fijo que no sea favorito de nadie, luego lo cruzo con un LEFT JOIN con la tabla de items de cada usuario, estos vendrían a ser los "no leídos", si no lo encuentra esa columna vuelve como NULL.

Este truquito de hacer una subtabla con una columna con datos y la otra en NULL es una de las cosas geniales del LEFT JOIN para encontrar lo que NO tenés, caso típico en SQL donde siempre encontrás lo que hay pero no sabés como cuerno buscar lo que no existe.

Comparando la tabla principal, donde estan todos los items de los feeds que colecciono, con la particular de los usuarios, obtengo los que estan al pedo ocupando espacio en la base de datos.

Pongo el LIMIT en 50,1000 para borrar a partir del 50 y de ahí en más 1000 para exagerar un poco y levantar todo (en un caso tenía 3500 items que nadie iba a leer jamás!)

El ahorro en espacio de la base de datos fue de un 60% con este recurso.

El otro era en dos partes, primero detectar la fecha del límite que impongo:

select min(X.fecha_item) as fechalimite
from (SELECT A.id_item, B.fecha_item FROM `ldv_rss_item_usuario` A
join ldv_rss_feed_item B on A.id_item = B.id_item
where A.id_usuario = '$id_usuario' and A.id_feed = '$id_feed'
group by A.id_item
order by B.fecha_item DESC
limit 0, 200
) X 

Si, lo se, un query grosero y mejorable :D

Es bastante sencillo, busco el más viejo de los primeros 200 que encuentro, así me quedo con esos 200 actuales y borro lo viejo, lo siento mucho si es que querías leer el 201 :D

Una vez obtenido el valor empiezo a borrar feed por feed:

DELETE P FROM `ldv_rss_item_usuario` AS P
JOIN (
select A.id_item, A.id_usuario, A.id_feed FROM `ldv_rss_item_usuario` A 
join ldv_rss_feed_item B on A.id_item = B.id_item
where A.id_usuario = '$id_usuario' and A.id_feed = '$id_feed' and B.fecha_item < '$fechalimite' and B.contadorfavs = 0 
group by A.id_item
order by B.fecha_item DESC
) LIM
ON P.id_item = LIM.id_item and P.id_usuario = LIM.id_usuario and P.id_feed = LIM.id_feed 

Por cada usuario, por cada feed y cuando la fecha sea menor a la que encontré antes, que no sea favorito, le borro todo lo antiguo, en el caso del JOIN no tuve que hace subquery como en el primer caso, se lo bancó perfectamente bien. El tema de subquery de subquery parece que es sólo para cuando se usa la cláusula IN que, ya lo se, es una negrada pero me servía Guiño

Al hacer todo esto recuperé un 70% del espacio, la tabla que antes tenía más de 350.000 registros pasó a tener 52.000 y pesar menos de 200Mb, la velocidad de respuesta se mantiene en fracciones de segundo, nunca superando los 0.2 seg actualizando cada hora unos 1500 feeds distintos.

Obviamente hago mucho uso de caché de querys e índices, de otra forma se moriría, pero mantener a raya los datos al pedo es imprescindible para cualquier sistema que tenga exceso de querys como lo es un lector de feeds.

Lo mismo tuve que hacer en LinksDV para los enlaces, los votos se graban en una tabla pero ¿para qué conservar los votos de un link ya cerrado? una segunda tabla de archivo los almacena y constantemente sólo estan los votos de las últimas dos semanas.

Este tipo de ordenamiento de la base de datos, de prolijidad si quieren llamarlo así, te permite mayor performance porque no hay datos ridículos y sin uso interrumpiendo la función principal de las tablas más expuestas.

Tip para developers, mantener en la base lo que se usa, lo que no, a otra tabla o borralo, nadie lo echará de menos. Y nunca, nunca subestimes la capacidad de tus usuarios para saturarte el sitema :D

Categoría: Programación Etiquetas:  datos masivos dba mysql programación sql trucos
Otros posts que podrían llegar a gustarte...

Comentarios

  • Che negro, la verdad que TL;DR pero, pensaste en usar Solr o Lucene para hacer algun tipo de "Cache" o para servir el contenido?

    Te lo digo porque el dia de mañana vas a querer incluir busquedas, o tener mayor data todavia... (yo al menos sigo como a 300 feeds y actualizan mucho a diario)...

    Te lo recomiendo, cualquier cosa consultame y te tiro mas data

    • Responder
    • Citar
    • Comentado:
  • quería comentar sobre esto:

    "Lo mismo tuve que hacer en LinksDV para los enlaces, los votos se graban en una tabla pero ¿para qué conservar los votos de un link ya cerrado? una segunda tabla de archivo los almacena y constantemente sólo estan los votos de las últimas dos semanas."

    entiendo que los votos los guardes en una tabla aparte para conocer qué usuario votó qué.
    pero mejoraría mucho la lectura si en cada LDV tuvieras un campo que fuera el numero de votos (lo que se conoce como un contador desnormalizado).
    cuando añadas o retires un voto, además de modificar la tabla de votos haces update en la tabla de links y cambias el valor del contador. de este modo lees muy rápidamente el contador de votos sin tener que hacer count sobre la tabla de votos.
    cuando el link haya expirado, puedes borrar todos los votos de la tabla de votos, y mantener el valor del campo contador.

    saludos

    • Responder
    • Citar
    • Comentado:
  • order by null dijo:

    quería comentar sobre esto:

    &quot;Lo mismo tuve que hacer en LinksDV para los enlaces, los votos se graban en una tabla pero ¿para qué conservar los votos de un link ya cerrado? una segunda tabla de archivo los almacena y constantemente sólo estan los votos de las últimas dos semanas.&quot;

    entiendo que los votos los guardes en una tabla aparte para conocer qué usuario votó qué.
    pero mejoraría mucho la lectura si en cada LDV tuvieras un campo que fuera el numero de votos (lo que se conoce como un contador desnormalizado).
    cuando añadas o retires un voto, además de modificar la tabla de votos haces update en la tabla de links y cambias el valor del contador. de este modo lees muy rápidamente el contador de votos sin tener que hacer count sobre la tabla de votos.
    cuando el link haya expirado, puedes borrar todos los votos de la tabla de votos, y mantener el valor del campo contador.

    saludos


    eso ya está hecho! esa "desnormalización" la hago siempre, la aprendí a la fuerza, no podés llevar un contador que se tenga que calcular cada vez que cargás la página

    cada registro de cada link tiene su campo de votos a favor y votos en contra, para evitar tener que leer un log.

    el log es un registro para saber quien votó, para que no vote dos veces, cuando uno intenta falsear un voto te lo impide porque ya hay un registro del mismo

    eso ya lo hice en este blog cuando quise ponerme a contar comentarios y visitas al post, me acuerdo de tantos profesores en la facultad diciendo que debíamos normalizar todo y "lo que es calculable no debe ir en la tabla", si, minga, cuando un JOIN te suma 20 segundos en un query te quiero ver Guiño



    Ezequiel Calderara dijo:
    Che negro, la verdad que TL;DR pero, pensaste en usar Solr o Lucene para hacer algun tipo de &quot;Cache&quot; o para servir el contenido?

    Te lo digo porque el dia de mañana vas a querer incluir busquedas, o tener mayor data todavia... (yo al menos sigo como a 300 feeds y actualizan mucho a diario)...

    Te lo recomiendo, cualquier cosa consultame y te tiro mas data


    no conozco ni SOLR ni Lucene así que bienvenido cualquier tip al respecto.

    las búsquedas en el RSS no las incluí porque, justamente, me la paso borrando contenido, no tenía demasiado sentido, son datos pasajeros.

    en cambio en los Links, donde el contenido queda permanente, tengo que implementar algo, ya sea por DuckDuckGo (opción más uqe válida ya que todo el contenido es indexable) o Google o, en el peor de los casos, una búsqueda por MySQL

    si hay mejores opciones, bienvenidas sean!

    • Responder
    • Citar
    • Comentado:
  • timfonzz    

    mysql es la base de datos mas chota que conocí.

    • Responder
    • Citar
    • Comentado:
  • timfonzz dijo:

    mysql es la base de datos mas chota que conocí.


    pero si apenas empezaste a programar, ratón, no sabés de DB :D

    MySQL es muy potente, muy útil, muy barata y rinde bastante bien... si hacés bien tu trabajo de SQL, no sirve hacer las cosas mal o no manejar índices Guiño

    He usado SQL Server, Oracle, DB2, PostgreSQL y SQL Lite además de MySQL, cada uno tiene su aplicación propia

    • Responder
    • Citar
    • Comentado:
  • eduqlm    

    Agregar "cantidad de vistas" a los links es complicado??

    • Responder
    • Citar
    • Comentado:
  • eduqlm dijo:

    Agregar &quot;cantidad de vistas&quot; a los links es complicado??


    el dato está, sólo haría falta mostrarlo, pero como no quería que eso influyese lo dejé como dato extra guardadito

    • Responder
    • Citar
    • Comentado:
  • Leo    

    Si queres mejorar un poco mysql sin cambiarte a mariadb o postgre instalate percona, son 5 minutos y aparentemente cambia bastante.

    • Responder
    • Citar
    • Comentado:
  • timosss    

    Fabio Baccaglioni dijo:

    timfonzz dijo:
    mysql es la base de datos mas chota que conocí.


    pero si apenas empezaste a programar, ratón, no sabés de DB :D

    MySQL es muy potente, muy útil, muy barata y rinde bastante bien... si hacés bien tu trabajo de SQL, no sirve hacer las cosas mal o no manejar índices Guiño

    He usado SQL Server, Oracle, DB2, PostgreSQL y SQL Lite además de MySQL, cada uno tiene su aplicación propia

    debe ser que intento hacer lo mismo que hacía en Oracle o Sql server en Mysql y la verdad es que es para otra cosa.
    igual no me pagan para andar depurando mysql asi que que la chupen.

    • Responder
    • Citar
    • Comentado:
  • timosss dijo:

    Fabio Baccaglioni dijo:
    timfonzz dijo:
    mysql es la base de datos mas chota que conocí.


    pero si apenas empezaste a programar, ratón, no sabés de DB :D

    MySQL es muy potente, muy útil, muy barata y rinde bastante bien... si hacés bien tu trabajo de SQL, no sirve hacer las cosas mal o no manejar índices Guiño

    He usado SQL Server, Oracle, DB2, PostgreSQL y SQL Lite además de MySQL, cada uno tiene su aplicación propia

    debe ser que intento hacer lo mismo que hacía en Oracle o Sql server en Mysql y la verdad es que es para otra cosa.
    igual no me pagan para andar depurando mysql asi que que la chupen.


    jaja, mirá, manejo este blog con 5474 posts y 141964 comentarios y unos 4000 tipos por día... sin caché, te parece que MySQL no se la banca?

    por ahí no aprendiste a crear índices y eso jode mucho a la performance

    • Responder
    • Citar
    • Comentado:
  • timosss    

    Fabio Baccaglioni dijo:

    timosss dijo:
    Fabio Baccaglioni dijo:
    timfonzz dijo:
    mysql es la base de datos mas chota que conocí.


    pero si apenas empezaste a programar, ratón, no sabés de DB :D

    MySQL es muy potente, muy útil, muy barata y rinde bastante bien... si hacés bien tu trabajo de SQL, no sirve hacer las cosas mal o no manejar índices Guiño

    He usado SQL Server, Oracle, DB2, PostgreSQL y SQL Lite además de MySQL, cada uno tiene su aplicación propia

    debe ser que intento hacer lo mismo que hacía en Oracle o Sql server en Mysql y la verdad es que es para otra cosa.
    igual no me pagan para andar depurando mysql asi que que la chupen.


    jaja, mirá, manejo este blog con 5474 posts y 141964 comentarios y unos 4000 tipos por día... sin caché, te parece que MySQL no se la banca?

    por ahí no aprendiste a crear índices y eso jode mucho a la performance

    eso explica un par de cosas :P
    bueno, pero no es mi responsabilidad, no me pagan por hacer eso.

    • Responder
    • Citar
    • Comentado:
  • Lelale    

    Fabio Baccaglioni dijo:

    timfonzz dijo:
    mysql es la base de datos mas chota que conocí.


    pero si apenas empezaste a programar, ratón, no sabés de DB :D

    MySQL es muy potente, muy útil, muy barata y rinde bastante bien... si hacés bien tu trabajo de SQL, no sirve hacer las cosas mal o no manejar índices Guiño

    He usado SQL Server, Oracle, DB2, PostgreSQL y SQL Lite además de MySQL, cada uno tiene su aplicación propia


    A mi me gusta especialmente PostgreSQL, creo que para transacciones tremendamente complicadas y querys donde en general en otras bases de datos tardarían mas, PostgreSQL se las arregla con el cálculo previo y ayuda en velocidad; pero es para cosas complicadas.
    MySQL no le metas algo raro en el medio por que a veces te deja a pata, aunque para leer datos es a veces lo mas rápido que hay.
    Oracle es lo mas robusto y potente a la hora de tener datos en los que se requiere que tengas ciertos cuidados, aunque admito que me parece tremendamente complicado ser DBA con Oracle.
    SQLite lo he usado poco, pero lo poco que lo he usado me gustó, no se definir bien por qué, capaz que por lo mismo que MySQL, lo simple para hacer algunas cosas, por ejemplo el Limit de MySQL, PostgreSQL y SQLite es sencillo de usar, pero un embole con Oracle y SQL Server.
    SQL Server me pareció una cagada en algunos aspectos, por ejemplo el tema de los logs, no hay forma de cortar que por cada query se apague el log. Ya se, SQL Server luego puede, con el log, reconstruir la base de datos, estilo un dump, pero no he encontrado la manera de que no haga eso, ocasionando así que para grandes cantidades de datos tarde una eternidad.

    Las bases que si recomiendo, aunque no sean relacionales y que la verdad son fáciles de usar, son las orientadas a objetos basadas en BTree, como por ejemplo ZODB, una pelotudez usarla, el archivo donde guarda todo es fácil de manejar, tarda lo mismo o quizás hasta un poco mas rápido que con PostgreSQL, pero tiene la limitación de que no podés mezclar fácilmente peras con manzanas, para ello lo tenés que manejar todo desde la lógica del programa y no desde la capa de la base de datos.

    Yendo al post ¡Mugriento! :D Me hacés acordar a mis querys

    • Responder
    • Citar
    • Comentado:
    • Revisado: 10/08/2013 - 01:38:32
  • Lelale    

    Al estilo "No se olviden de cabezas" digo "no nos olvidemos de Firebird", es un poco mas chota que PostgreSQL, pero se la banca.

    • Responder
    • Citar
    • Comentado:

Deje su comentario:

Tranquilo, su email nunca será revelado.
La gente de bien tiene URL, no se olvide del http/https
Para evitar bots, si se tardó mucho en leer la nota seguramente no sirva y tenga que intentar dos veces

Negrita Cursiva Imagen Enlace


comentarios ofensivos o que no hagan al enriquecimiento del post serán borrados/editados por el administrador