Base de datos enormes con firebird. ¿Cómo la estructuro?

[jachguate]

Empezado por Delphius

El "cálculo" de las Temp_Min, Temp_Max, Tem_Promedio. las realizarías una vez finalidado el ejercicio? O directamente con algún Tigger After Insert de la tabla Temperatura?

Siempre depende.... para no sobrecargar con disparar un trigger tantas veces, podría ser la aplicación la que, cada vez que detecte un cambio de hora, dispare un stored procedure que valide aquellas horas que no se han procesado y realice el cálculo, o podría ser otra aplicación diseñada específicamente para realizar esta tarea. En este tipo de situaciones... firebird aún carece de maquinaria mas pesada. En Oracle, por ejemplo, podes definir una "tarea", que la propia base de datos dispara en base a un calendario... es como un timer, pero dentro de la propia BD... simplemente fantástico.

Empezado por Delphius

¿pero cual eligirías en esta ocasión?

Aún no lo se... pero si tuviera que tomar una decisión ahora mismo, con la información que tengo, lo haría en la aplicación que carga los datos por una razón: menos tiempo de desarrollo

Empezado por lepe

¿double precision jachguate? ... yo es que le tengo manía a ese tipo de dato, prefiero los numeric(10,2) por aquello de no perder precisión y poder encontrar una temperatura exacta.

Lo dije sin pensarlo mucho, tomando en cuenta que, bueno, son temperaturas... no monedas. Para encontrar temperaturas exactas, siempre queda el recurso de redondear, moldear como numeric o hacer un between entre un rango de "tolerancia" para la precisión (¡que exactitud!)

Hasta luego.

[[Lepe]]

Está claro que Casimiro se guarda algún "as" debajo de la manga.

En cuanto al error de precisión, no es que lo diga yo, sino los creadores de interbase en la documentación.

Quizás hace unos años estaba justificado el uso de double precision (porque ocupan menos bytes en la BD al almacenarlo), y puede que en casos como este, donde se guardarán muchísimos valores, haya que tenerlo en cuenta. Me pareció oportuno comentarlo, aunque no quiero "desvirtualizar" el hilo (¿verdad AzidRain?).

Sabido el detalle, podemos continuar con el tema original .

Saludos

[Angel Fernández]

Mi idea ahora mismo es la siguiente, componiendo en mi cabeza todo lo que me estáis diciendo:

Tabla Humedad y Temperatura
---------------------------
Dado que el tipo de dato que almacenan es parecido (rango 0-100 y 0-400 respectivamente) y los datos de H y T se estudian conjuntamente, los junto los dos en una sola tabla.
Campos
ID (bigint como me dice jachguate) IDPROYECTO (smallint) IDSENSOR (integer) FECHA (date) HORA (time) FECHAYHORA(timestamp según apunta Lepe) DATO (smallint)

IDPROYECTO enlazará con una tabla donde se indique descripción del proyecto y otros datos
IDSENSOR enlazará con la tabla de sensores

Tabla sensores
--------------
IDSENSOR TIPO(HUM, TEM, LLU, ENC) UBICACION

Tabla Lluvia y Encharcamiento
------------------------------
Por el mismo motivo junto lluvia y encharcamiento.
Campos:
ID IDPROYECTO IDSENSOR FECHA HORA FECHAYHORA DATO(true/false)

El tipo de consulta que quiero realizar, al menos para temperatura y humedad, es datos agrupados por hora para realizar gráficas. En código sql (más o menos, lo escribo de cabeza)

Código SQL [-]
select idsensor, extract(year from FECHAYHORA) as anyo, extract(month from FECHAYHORA) as mes,
extract(day from FECHAYHORA) as dia, extract(hour from FECHAYHORA) as hora,  count(DATO) as cont, min(DATO) as Minimo, max(DATO) as Maximo,
avg(DATO) as Media
from tabla1
group by idsensor, anyo, mes, dia, hora
order by idsensor, anyo, mes, dia, hora

En cambio lo de lluvia y encharcamiento no sé muy bien qué quiere el cliente (será objeto de un próximo encuentro) pero lo que comentaba Delphius me ha hecho pensar mucho. Aquí en Valencia (España) donde yo vivo, podemos tirarnos meses sin llover. ¿Para qué guardar un dato de lluvia cada minuto que sea false, false, false,... durante meses? Y lo mismo para encharcamiento.

Tendremos que definir qué queremos hacer con esos datos (quizá una consulta por mes sea suficiente) o tal vez sea para activar alguna alarma si hay encharcamiento. En este último caso, un dato cada 10 minutos como mucho sea suficiente.

De lo que me comentáis me surgen algunas dudas. ¿Las consultas las puedo hacer cada vez que las necesite o mejor guardarlas en tablas como me dice jachguate? ¿Si tengo un campo FECHAYHORA (timestamp), necesito también FECHA (date) y HORA (time), como dice Lepe, para hacer mejor las consultas por SQL?

Gracias a todos por vuestra dedicación.

Saludos.

[mamcx]

Muy linda la teoria, pero la practica es superior.

Lo mejor que puedes hacer es crear una BD, y llenarla con la cantidad de registros presupuestada, y ver que tal anda.

Ademas, el diseño es poco optimo para la lectura, como dicen la gente de digg "Datos normalizados son para niñitas" (http://www.niallkennedy.com/blog/uploads/flickr_php.pdf - el termino real es mas ofensivo!-).

Cuando se tiene un conjunto masivo de informacion y se quiere optimizar la lectura se debe hacer todo lo inverso a la logica normal:Se desnormalizan los datos.

Eso es lo que se hace para los sistemas OLAP. El ejemplo tipico es la fecha. En ves de ingresar un datetime se hace:

Fecha, Año, Mes, Dia, Hora, Min, Sec

Todo separado. Eso es genial porque puedes indexar de forma MUY eficiente.

Tambien en la medida de lo posible se precalcula todo. Por ejemplo, en este foro para sacar el numero de post, en vez de hacer un SELECT COUNT(.... se hace un campo contador en la table padre y se aumenta y decrementa de acuerdo a las repuestas del thread.

Para hacer bases de datos muy enormes, y se optimiza la lectura:

- Se separan las tablas
- Se separan los archivos de la BD enh diferentes discos. Para cosas realmente inmensas se separan en discos de red, pero este no es el caso
- Los archivos de indices se ponen en discos aparte de los de datos
- Se desnormalizan los datos y se precalculan las sumas y los conteos
- Se arman vistas, y si el motor lo soporta, se indexan.

Eso es a grandes rasgos. Pero como digo, lo mejor es que metas datos de prueba y mires antes de ir por este o aquel camino.

[[fjcg02]]

Hola a todos,
interesante el hilo.
Bajo mi punto de vista, mamcx lleva razones de bastante peso en su planteamiento. Cuando se preparan bbdds de semejante tamaño, lo más importante es tunear la bbdd, para que el rendimiento sea óptimo.
Las aportaciones de todos los participantes me han parecido MUY BUENAS, pero además de todo eso, que es importante, lo es tanto o más el tema de la configuración de la bbdd y del propio srv.
A saber, discos scsi de alta velocidad, tablas en unos discos, indices en otros, nº de indices exactos tras el estudio del rendimiento, tamaño de la página exacto, ...

Bueno, la particularidad del sistema, la particularidad del presupuesto, y la particularidad de los conocimientos de quien lo desarrolla, también influye ( y mucho ), aunque más vale tener todo en cuenta para luego no llevarse sorpresas.

Saludos a todos

[Angel Fernández]

Empezado por mamcx

Eso es lo que se hace para los sistemas OLAP. El ejemplo tipico es la fecha. En ves de ingresar un datetime se hace:

Fecha, Año, Mes, Dia, Hora, Min, Sec

Todo separado. Eso es genial porque puedes indexar de forma MUY eficiente.

Eso me viene muy bien saberlo, porque los datos que yo recibo en formato texto, vienen así, el año por un lado, el mes por otro, el día por otro, etc.

Como voy a agrupar frecuentemente por hora y presumo que también por mes, puedo crear una columna HORA_STR tipo CHAR(10) cuyo dato sea tal que así:

2008011508 (Año 2008, mes 01, día 15, hora 08) y ponerle un índice.

Y otra MES_STR tipo CHAR(6) con datos:

200801 (año 2008, mes 01) y ponerle otro índice.

De esta forma, al agrupar por hora no tengo que hacer un extract(hour from fecha) sino sencillamente agrupar por el campo HORA_STR.

¿Os parece buena idea o es una tontería?

Lo que dice fjcg02 me acongoja un poco... mis conocimientos son bastante pobres.

Un saludo.

[[fjcg02]]

Empezado por Angel Fernández

Lo que dice fjcg02 me acongoja un poco... mis conocimientos son bastante pobres.

No quería acongojar a nadie. Simplemente recordar que a veces el rendimiento de una bbdd no depende únicamente del análisis y el desarrollo, sino de algo que a veces se nos escapa, que es la configuración del srv de bbdd y la propia bbdd. Yo nunca he tenido que llegar a estos extremos porque nunca he tenido bbdd's tan grandes, pero en ocasiones - sobre todo cuando manejas volumenes tan grandes - es la solución a esos problemas de rendimiento.
Además, soy el primero que utiliza firebird - y cualquier otro motor - según se instala, sin cambiar nada.

Saludos

[Angel Fernández]

Lo que dije, fcjg02, supongo habrás entendido que era broma. Por supuesto que cualquier aportación será bien recibida, incluso si es un tirón de orejas.

Saludos.

[mamcx]

Es mejor dejar el año, mes, dia en columnas separadas que meterlas en un solo campo. La razon es que en vez de tener un solo indice tenes 3>, se hace muy optimo las consultas y es mas flexible porque tampoco tienes que hacer substring (por ejemplo, para llegar al dia) y ademas los campos se pueden dejar tipo INTEGER que es mas optimo que tipo STRING.

Por lo demas, tampoco es de enredarse mucho la pita. Hay un principio (el de Paretto) que dice que el 80% del esfuerzo se concentra en el 20% de las cosas. Determina que es lo mas probable y optimiza para ello, pero no te pongas a complicarte (igual, la optimizacion prematura causa mas lios que lo que mejora).

En terminos generales, tunear la BD, tener suficiente RAM y si se puede contar con discos en RAID o al menos 2 separados (1 para el OS otro para la BD) es mas que suficiente en la mayoria de los casos.

[tocomi]

Empezado por Angel Fernández

¿Os parece buena idea o es una tontería?

Personalmente creo que es una buena idea, de hecho si estuviera en tu situacion la consideraria seriamente

Ahora bien respecto del volumen de datos, y ya que se planteo por ahi el crear una tabla por cada sensor, por que no crear una tabla por año??, serian un numero de registros conocidos 565000 aprox y para el primer intento se podrian poblar con datos ficticios, hacer un par de consultas, y ver que como responde la aplicacion y el servidor

Definitivamente le recomendaria al cliente linux como sistema operativo, tanto por costo como por estabilidad (estoy desarrollando una aplicacion para windows que usa una base de datos Mysql en linux y al hacer las pruebas el rendimiento es bastante bueno, casi pareciera que estuviese local, la base esta en un servidor HP SCSI)

[Angel Fernández]

Gracias Tocomi por tu opinión. De hecho la base ya la tengo estructurada (con posibilidad de hacerle cambios) y estoy buscando formas de meterle los datos desde ficheros CSV. Lo de estructurarla por años, no me parece mala idea, pero me he enterado que el proyecto va a durar 5 años de recogida de datos y con esta cantidad de datos, firebird los aguanta perfectamente según he leído en la documentación que me han recomendado en este hilo.

Sí que he añadido los índices para anyo, mes, día y hora, los he puesto tipo integer como me recomendaba mamcx. Y también como me recomendaba mamcx, he decidido no "enredarme mucho la pita". He decidido un diseño de acuerdo a lo que me habéis indicado y lo he puesto en marcha.

Ya os iré diciendo qué tal va...

[jachguate]

Empezado por Angel Fernández

estoy buscando formas de meterle los datos desde ficheros CSV.

En ese caso, te recomiendo que leas, en mi blog, la entrada Exportar Datos de Firebird, que habla de como exportar/importar datos desde un csv de manera sencilla a firebird.

Empezado por Angel Fernández

Ya os iré diciendo qué tal va...

Siempre son bienvenidas las historias de éxito sobre firebird...

Un saludo

[Casimiro Notevi]

Respecto al tamaño de la base de datos, me gustaría comentar algo que me ha pasado hoy atendiendo a un cliente que hacía tiempo no tenía noticias de él, la conversación fue más o menos así:

cliente: sí, he metido la pata y ahora hay artículos en almacenes equivocados y el stock está descontrolado, ¿puédes arreglarlo?
yo: sí, claro, puedo ejecutar un script que te lo arregle, aunque antes de hacerlo en tus datos reales, me gustaría hacer una prueba en una copia de tu base de datos, me sirve tu último backup, por ejemplo el de ayer.
cliente: bien, cómo te lo envío.
yo: me lo comprimes y yo me lo tráigo por aquí mismo (conexión ultravnc)
cliente: bueno, verás, es que ocupa casi 11 Gigas
yo: no te preocupes, el fbk ocupa menos, y luego lo comprimimos con zip, rar o 7z y queda en mucho menos.
cliente: ya, si, pero es el zip del fbk el que ocupa los 11 Gigas, la fdb ocupa mucho más.
yo: , bueno, irá por ahí alguien a recogerlo en un disco duro portátil

Pues eso, tenemos clientes, la mayoría, que sus bases de datos ocupan varios gigas, pero es el primero del que tengo noticias que pasa los 10 Gbs... y sin problemas.

[Angel Fernández]

Empezado por jachguate

En ese caso, te recomiendo que leas, en mi blog, la entrada Exportar Datos de Firebird, que habla de como exportar/importar datos desde un csv de manera sencilla a firebird.

Gracias Juan Antonio por tu sugerencia. Sin embargo, he visitado la entrada de tu blog y, si mal no he entendido, allí tratas el tema de exportar de Firebird a csv. Yo ahora mismo lo que pretendo es justamente lo contrario, importar de csv a delphi. Tengo los datos en un fichero csv separados por punto y coma y quiero leerlos y ponerlos en una tabla de firebird.

En la entrada dices que la importación lo dejas para una próxima entrada, la cual estoy ya impaciente por leerla. Lo que voy a intentar hacer es un readln de cada línea del fichero csv, después con la utilidad strman.pas (completísima unidad con infinidad de utilidades para trabajar con cadenas de texto -en torry está-) le voy quitando lo que hay delante de cada ; y lo asigno al campo correspondiente.

De tu blog me he descargado la hoja de trucos para Firebird. Interesante, pero echo de menos funciones estadísticas (min, max, avg, etc)

Una cosa, he intentado añadir tu blog a Google Reader y no he encontrado la sindicación de RSS. ¿No la encuentro o no existe?

En cuanto a lo que comenta Casimiro, me alegra saber que hay gente con bases de firebird muy muy grandes sin problemas.

Un saludo a todos.

[tocomi]

Empezado por Angel Fernández

Lo que voy a intentar hacer es un readln de cada línea del fichero csv, después con la utilidad strman.pas (completísima unidad con infinidad de utilidades para trabajar con cadenas de texto -en torry está-) le voy quitando lo que hay delante de cada ; y lo asigno al campo correspondiente.

Te dejo esto por si te sirve

 
function Buscandocol(lintmp:string;nrocol:integer):string;
var
i:integer;
hasta:integer;
largo:integer;
dato:string;
valor:integer;
begin
lintmp:=trim(lintmp);
For i:=1 to nrocol do
   begin
   largo:=length(lintmp);
   hasta:=pos(';',lintmp);
   dato:=copy(lintmp,1,hasta-1);
   lintmp:=copy(lintmp,hasta+1,largo);
end;
result:=trim(dato);
end;

Esta funcion lee la linea de texto y te devuelve la columna que le indicas, obviamente las columnas estan separadas por un punto y coma, lo tuve que hacer porque habia un cliente que enviaba los datos en formato CSV, y tenia que separarlos para poder incorporarlos a la base

En terminos practicos haces el readln, lo dejas en una variable string, por ejemplo "linea" y para buascar la quinta columna la sintaxis seria:

Buscandocol(linea,5);

Te devolveria lo que esta en la quinta columna, en formato String

Nos cuentas como te va

[[RONPABLO]]

De tu blog me he descargado la hoja de trucos para Firebird. Interesante, pero echo de menos funciones estadísticas (min, max, avg, etc)

esas funciones las tiene FIrebird


una duda viendo la herramienta para exportar/importar... yo puedo crear/usar un archivo de texto que tenga el siguiente formato:

Empezado por formato tabulado y sin comillas

juan[tabulador]Colombia[tabulador]1

en vez de:

Empezado por formato por comas y con comillas

"juan","Colombia","1"

[Angel Fernández]

Gracias Tocomi por compartir tu código. Voy a probar a ver qué tal.

Da gusto con gente como vosotros.

[jachguate]

Empezado por Angel Fernández

Gracias Juan Antonio por tu sugerencia. Sin embargo, he visitado la entrada de tu blog y, si mal no he entendido, allí tratas el tema de exportar de Firebird a csv. Yo ahora mismo lo que pretendo es justamente lo contrario, importar de csv a delphi. Tengo los datos en un fichero csv separados por punto y coma y quiero leerlos y ponerlos en una tabla de firebird.

Cierto que trato solamente el tema de la exportación, por ahora, sin embargo te queda el utilitario, al que basta ver la ayuda para figurarse cómo sería la importación.

Empezado por Angel Fernández

En la entrada dices que la importación lo dejas para una próxima entrada, la cual estoy ya impaciente por leerla.

Efectivamente.... yo también estoy impaciente por escribirla..

Empezado por Angel Fernández

Una cosa, he intentado añadir tu blog a Google Reader y no he encontrado la sindicación de RSS. ¿No la encuentro o no existe?

Claro que si!

http://jachguate.wordpress.com/feed/
http://jachguate.wordpress.com/comments/feed/

Está en la sección meta.... pero ahora que lo mencionas, quizás no esté lo suficientemente visible o bien identificada... me daré a la tarea de ponerla mas visible en cuanto me sea posible.

Hasta luego.

[tocomi]

Empezado por Angel Fernández

Gracias Tocomi por compartir tu código. Voy a probar a ver qué tal.

Da gusto con gente como vosotros.

De nada, yo tambien he sacado ideas de aqui, asi que me parecio bien hacer un pequeño aporte de vuelta

[rastafarey]

Yo uso un libro. De base de datos ahorita no estoy en la casa asi que no recuerdo el nombre. Pero puedes buscar en google digrama de entidad relacion o diñeno de diagramas ER.

Ahora te doy una idea de como se te van dando las cosas.

1._ Ver que es lo que necesitas.
2._ Diseño del sistema.
3._ Diagrama de procesos.
4._ Diagrama ER.
5._ Llevar el diagramas ER a tablas.
6._ Escojer el SGBD que mejor se ajuste a tus necesidades(firebird es perfecto).
7._ Preparar el manejo de la data ya sea usando disparadores vistas u otras
cosas que te permita hacer el manejar.
8._ Lenguaje de programacion.
9.- Echar codigo.

Una cosa que te recomiendo encarecidamente es que hagas todo le trabajo de la base de datos en la base de datos y no le dejes el trabajo al lenguaje de programacion.

Sacale el maximo provecho al manejador de base de datos no lo uses solo para saca y meter consultar datos. Usa la integridad refencial has tus respectivas validaciones en la bd no importa qu etambien debas ahcerla antes de que los datos se envien al servidor. Si sigues estas reglas no tenf¡dras grandes complicaciones.

Lo que te digo es con conocimiento de cuasa ya qe ahorita estamos haciendo un sistema que tiene 329 tablas. y aun faltas como 60 mas. Donde el diseño de los procesos nos tomo casi 1 año.