Archivo de la categoría: Databases

sobre mysql, postgresql y otras bases de datos que aparezcan …

Del por qué un desarrollador NO ES un Database Administrator?

O del por qué podría ser, pero debería primero cambiarse la camisa …

Este POST no busca explicar postgreSQL, ni siquiera es un artículo acerca de trucos o buenas prácticas, es simplemente una reflexión acerca de cómo pequeñas cosas que muchos pasan desapercibidas causan impacto profundo en el diseño de una aplicación.

Preámbulo

Tomé un servidor físico GNU/Linux que únicamente ejecutaba una base de datos y lo mudé a una máquina virtual restringida (¡conchale!, hay que ahorrar recursos!, pensé), pensando que todo quedaría bien.

Sin embargo, los usuarios del sistema (en producción) comenzaron a quejarse de lentitud, además, los administradores de sistemas comenzaron a notar excesivos picos de uso de CPU (¿en un equipo que sólo tiene postgreSQL?) e incluso en varias oportunidades se iba a SWAP.

Antes de devolver la base de datos al equipo físico, decidí hacer una revisión (les dije, “no solo monto sistemas, también sé de programación y mucho de base de datos, lo olvidan?”) y estas son las impresiones de la muy breve (pero exitosa) revisión.

El análisis

Lo primero que siempre hago cuando voy a analizar un sistema conectado a postgreSQL, es activar el slow log, esto es, comenzar a medir aquellos queries que consumen más del tiempo que uno supone debería asumir cierto tipo de consulta; paralelamente es daba una clase rápida de postgreSQL, de la importancia de entender el ciclo de parsing/análisis de una consulta SQL y más aún, de la importancia de entender SQL; por ejemplo, dos casos reales extraídos de este caso:

“fecha_inscrito” es un campo VARCHAR, donde almacenan algo como “12/04/2012″; si desean sacar el año, hacen un SPLIT de la cadena con un SUBSTR y sacan el tercer valor luego del “/” … ¿les parece eso correcto?, pues veamos el resultado:

postgres=# SELECT substr(’12/04/2012′, 6);
substr
——–
/2012
(1 fila)
Duración: 61,300 ms

Versus:

postgres=# SELECT EXTRACT(year from ‘2012-04-12′::date);
date_part
———–
2012
(1 fila)
Duración: 0,468 ms

Y comenzaron las alarmas!, 60 milisegundos menos!, pero chamo, a mi no me gusta trabajar con formato ANSI!, y luego les recuerdo que existe la variable de conexión DATESTYLE:

postgres=# SET datestyle to sql;
SET
Duración: 0,124 ms
postgres=# SELECT ‘2012-04-12′::date;
date
————
12/04/2012
(1 fila)
Duración: 0,154 ms

INSOLITO! ¿cómo es posible que una simple función SET cause que todas las fechas salgan con el formato que yo desee?, esto pasa cuando se desconocen los detalles de la base de datos con la que se trabaja.

Igual sucede cuando la gente comprende la diferencia entre usar ON y USING cuando se construyen JOINS (diferencia claro está, a nivel de planificador).

El tercer caso de no-entendimiento de postgreSQL ocurría con un “pseudo-ORM hecho a mano” por los mismos programadores, que no solamente escapaba mal las cadenas:

Esto:

(‘Empresa’, ‘   MATURIN’, ’15/04/2014′, …

No es igual a esto:

(‘Empresa’, ‘MATURIN’, ’15/04/2014′, …

Ya que la cadena ”    MATURIN” no es igual a “MATURIN”, a menos que se haga un TRIM adicional, afectando el performance de la consulta, sino que además, hacen cosas como esta:

’21’, ’32’, ’16’, ‘8’, …

¿Esos son cadenas o números?, luego de ejecutar un DESCRIBE a la tabla, encontramos con que son campos numéricos, y aunque postgreSQL hace la conversión, lanza un WARNING indicando que esos valores son “not integers”, lo cual obviamente no es para nada óptimo.

Esto es lo que pasa cuando los programadores, pensando como programadores, intentan diseñar bases de datos.

El extraño caso del CPU consumido

Me he encontrado con una consulta muy repetida, con esta forma:

SELECT * FROM sol_solvencia WHERE cod_aport = $1 and estatus = $2;

La consulta se repetía muchísimo (sobre todo en temporada de mucho uso de la aplicación web hecha en PHP) y los logs del postgreSQL reportaban que su duración, promedio, era de 550 milisegundos … grité (como gritó Emmet Brown cuando le dijeron que debía alimentar el D’lorean con 1.1Gigawatts) ¡550 milisegundos!, yo en 550 milisegundos corría una nómina y ellos sólo ejecutan un query!.

Luego de usar EXPLAIN ANALYZE nos encontramos con la sorpresa de que la tabla no posee índices de ningún tipo (alegan que es un sistema legado y viene así de informix) y en la actualidad posee unos 98 mil registros; por ende, postgreSQL no le queda de otra para hacer el WHERE que ejecutar un SEQUENTIAL SCAN; o lo que es lo mismo, postgreSQL iteró de manera secuencial por los 98 mil registros para sacar ámbos criterios, ¿en conclusión?, allí se van los 550 milisegundos.

Si el query se ejecuta a un ritmo de hasta 50 veces en un minuto, se imaginan el uso de CPU de postgreSQL para poder mantener ese ritmo.

Indizar, y sino, indizar también …

Luego de construir un par de índices sencillos:

CREATE INDEX idx_sol_solvencia_cod_aport ON sol_solvencia USING btree (cod_aport NULLS FIRST);

Y

CREATE INDEX idx_sol_solvencia_estatus ON sol_solvencia USING btree (cod_estatus);

El EXPLAIN cambia considerablemente:

Aggregate  (cost=32.19..32.20 rows=1 width=4)
  ->  Index Scan using idx_solvencia_fecha on sol_solvencia  (cost=0.00..31.96 rows=94 width=4)
        Index Cond: (fecha_now = ’11-04-2014′::date)
        Filter: ((estado = 1) OR (estado = 15))

Al realizar un escaneo sobre índices y luego una función de agregado sobre ámbos índices, la consulta se ejecuta en 1.7 milisegundos …

¡toda una mejora!, ¿no les parece?

Prepáralo, ponlo para llevar

Luego, les expliqué el concepto del planificador, del GeQo de postgreSQL, y de cómo se analizaban las consultas, luego de ello, les dije “¿y se imaginan que ese tiempo pudiera bajarse más?” … te miran con cara de gallina comiendo sal y tú les muestras PREPARE.

PREPARE genera sentencias preparadas, toma una consulta común, la pasa por el planificador, construye el plan en base a criterios pre-definidos y compila este plan, presto a simplemente recibir los parámetros, ¿y qué ganamos con esto?, ¡ahorrarnos milisegundos valiosos de planificación!.

PREPARE pln_obtenersolvencia (int, varchar) AS
SELECT * FROM sol_solvencia WHERE cod_aport = $1 and estatus = $2;

Como ven, la sentencia preparada es simplemente un QUERY tradicional, al cual hemos pasado sus condiciones (que deben ser criterios, no metadatos) como parámetros, así, postgreSQL puede analizar, planificar y compilar la sentencia y esperar únicamente a que pasemos los parámetros:

EXECUTE pln_obtenersolvencia(682185, 1);

Total runtime: 0.473 ms
(1 filas)

De 1.7 a 0.4 ha sido una mejora interesante, ¿no?.

Conclusiones

No busco “echarme de enemigos” a los desarrolladores, yo también soy uno (de diseñado APIs, frameworks, y módulos en python y PHP), pero, cuando se trata de bases de datos, los desarrolladores deben quitarse los audífonos, la sudadera geek y ponerse en la camisa y anteojos nerd de los DBA; porque al final del día, la aplicación no se está diseñando para yo sentirme cómodo para trabajar con fechas o agregar y agregar campos sin mantener un diccionario de datos simplemente porque me pidieron en un lugar meter el RIF como J310210423 en otro como J-31021042-3 y en otro el tipo de contribuyente por un campo y el código del mismo por otro; no se trata de nuestra comodidad (o de nuestro limitado conocimiento como desarrolladores) sino de la eficiencia y la funcionalidad que deben garantizar que sea el USUARIO FINAL (y sólo él, porque al final, es el que usará todos los días la aplicación) quien disfrute el uso de nuestra aplicación.

Por cierto, como colofón, habrán notado que la tabla se llama “solvencia”, si, es un sistema que genera solvencias al público en general, ahora las consultas duran menos de un milisegundo (y no casi un segundo en cargar) por lo que la mejora impacta a miles de personas que solicitan la solvencia en ese sistema …

… Y ya el CPU no se agota en la máquina virtual! …

 

[Linux] De las comparativas (benchmarks) y otras justificaciones

Introducción

En un comentario anterior, el compañero Piccoro, hace una remembranza de unas pruebas de rendimiento que hiciera hace ya mucho Sun Microsystems  (antes que la comprara Oracle Corp.) sobre sus conocidos equipos SunFire, estas pruebas de rendimiento (conocidas como las “SPECjAppServer2004″) realizadas en 2007, ahora no están al alcance del público (aunque pueden aún ser encontradas en mi dropbox) pero demuestran el rendimiento de una misma aplicación, optimizada en estos equipos para Oracle, postgreSQL y MySQL.

¿Qué descubrimos con esta prueba?, veamos!.

La prueba Java (antes que fuera de Oracle)

MySQL 5 fué instalado en un equipo con las siguientes características:

  • Sun Fire X4100 (2×285,4x2GB,2X73GB) (2)
  • Sun StorEdge 3320, 12x73GB, 1 RAID CONT
  • Single-Port PCI Ultra320 SCSI HBA

Costo de la Licencia: 0$
Costo de implantación : 59.260 US$
MySQL: 720 JOPS

PostgreSQL 8.2 fué instalado con estas características:

  • Server HP Integrity rx2660 1.6Ghz 18GB 4-core (2)
  • 12GB DDR2 memory pair
  • SAN Array 1000

Costo de la Licencia: PostgreSQL 8.2 (0 US$)
Costo: 70.701 US$
Puntaje: 778 JOPS

Y Oracle 10g fué instalado con las siguientes características (semejantes a PostgreSQL)

  • Server HP Integrity rx2660 1.6Ghz 18GB 4-core (2)
  • 12GB DDR2 memory pair
  • SAN Array 1000

Costo de implementación: 70.701 US$
Licencias: Oracle 10g Enterprise + Oracle App Server + Oracle Partition Option
Costo en Licencias: 120.000 US$
Puntaje: 874 JOPS

Las pruebas, aunque en el mismo hardware físico gana Oracle (por al menos unos 70 puntos), demuestran que necesitas gastar 200US$ por unidad adicional para poder implementar Oracle, es decir, que con el costo *únicamente* de las licencias Oracle podrías incorporar 2 nodos más al cluster postgreSQL; qué, manteniendo la proporción de crecimiento, implementar un único servidor Oracle a 874 JOPS cuesta lo mismo que implementar un cluster de 3 nodos postgreSQL (ejecutandose a un rendimiento de 2334 JOPS).-

¿Cierto o falso?, queda a criterio del lector, la EULA de Oracle prohibe terminantemente la realización de pruebas y publicación de resultados sin su autorización, so-pena de recibir acciones legales, pero como yo no hice el benchmark! ;)

pero revisemos otro benchmark.

Cuándo VMWare decidió no publicar “malos benchmarks”

En una aclaratoria en su blog, la gente de VMWare explica que la clausula de la EULA (End-User License Aggrement) donde se prohíbe terminantemente la publicación de benchmarks de su producto versus otros productos de virtualización, no es porque “ellos estén en contra de las pruebas”, sino que le “sugieren amablemente” a las empresas encargadas de hacerlas, a que optimicen su producto y sólo utilicen el hardware que a “ellos” les es más conveniente, ¿interesante, no?.

Hace ya algún tiempo, la gente de specs.org publicaron una comparativa entre VMware ESX, la solución de virtualización para datacenters, y Linux KVM sobre Red Hat 6.1, resumiendo la conclusión:

KVM: SPECvirt_sc2010 1820 @ 114 VMs

VMWare: SPECvirt_sc2010 2721 @ 168 VMs

114 máquinas virtuales versus 168 es bastante, ¿no?, 900 puntos de diferencia en cuánto a rendimiento, también, ¿verdad?, pero, veamos a los detalles:

Primero, el equipo “sugerido” por VMWare para la prueba:

CPU: Intel(R) Xeon(R) X7560
Velocidad: (MHz) 2266
Núcleos: 32 cores, 4 chips, 8 cores/chip, 2 threads/core

Y el equipo con Red Hat:

CPU: Intel Xeon E7-2870
Velocidad: (MHz) 2400
Núcleos: Cores 20 cores, 2 chips, 10 cores/chip, 2 threads/core

El último es un simple HP Proliant Generación 7 (a un costo de 16800US$), el primero es un equipo ensamblado “especificamente” para VMWare, por su partner Bull Novascale, el “Novascale Bullion” fué catalogado como “el equipo para datacenter x86 más rápido del mundo” en 2011 y tiene un costo de 25 mil dólares la unidad.

Si a eso le sumamos la licencia de VMWare ESXi (9200 dólares por cada 2 núcleos), la cosa quedaría *más o menos* así:

KVM: Red Hat Licencias (Server=6500$ + Virtualización=6700) = 13200$
Servidor: 16800$
Total: 30000 US$
Costo: 263 dólares por VM (máquina virtual)

VMWare: Licencia=9200$ x 16 cores = 147200US$
Servidor: 25000 US$
Total: 172200 US$
Costo:  1025 US$ por VM (máquina virtual)

Entonces, ¿dónde está el rendimiento y el abaratamiento de precios?, además, hagamos algo bastante “jocoso”, reemplacemos Red Hat por Debian Linux (que el valor de siu licencia es “0$” – se lee cero!-) y te darás cuenta que por el mismo precio de la implementación de VMWare en tu empresa podrías pagar 11 servidores Proliant con GNU/Linux, ¡En vez de un equipo, tendrías un datacenter!  donde podrías en promedio (y según las cifras de Debian) virtualizar 990 máquinas virtuales.

A mí, me enseñaron que 990 VMs > (se lee, “es mayor qué”) 168 VMs, ¿no creen?.

Saquen ustedes sus propias conclusiones.

[postgreSQL] Una instalación de postgreSQL básica (¡pero mejor!)

PostgreSQL: Introducción

PostgreSQL es una de las grandes maravillas del software libre, robusto, potente, altamente funcional, distribuido en miles de formas posibles (greenplum=clusterizador masivo, postgres Plus=”imitador” de Oracle,deepgreen=granjas de datawarehousing con postgreSQL, etc) puede ser optimizado (como todo lo que es software libre) de maneras inimaginables para cada necesidad específica. Entonces, ¿por qué hay gente que denigra de él? …

El primer error que comete la gente, es pretender que un sistema tan necesario como la base de datos, sea utilizado “directamente” luego de su instalación; un detalle de distribuciones Linux como Debian, es no optimizar para ningún aspecto (ya que son meta-distribuciones genéricas sin una orientación específica).

Mientras Oracle saca libros de 900 páginas de cómo optimizar al máximo hardware, sistema de archivos, sistema operativo y la base de datos como tal, mucha gente piensa “migrar” a postgreSQL ejecutando un “aptitude install postgresql” y dejándolo así … nada más perdido y lejos de la realidad.

Acá, ejecutaremos una instalación que debería ser “básica”, la más básica, para un entorno pequeño de datos, para que sus sistemas “rindan”.

Preámbulo

Uno de los aspectos más importantes es que postgreSQL “no debería” compartir acceso a disco con el sistema operativo, esto es, si es posible que postgreSQL esté en una partición distinta a “root” (incluso un disco separado, de ser recomendable); por lo que la opción “instalar y usar” no debería ser para instalaciones en producción de postgreSQL 9.1.

Hay que tomar en cuenta que postgreSQL (como cualquier otra base de datos) debería ser optimizada posteriormente a su instalación de manera correcta, una de las optimizaciones más necesarias (pero que casi nadie sigue) es gestionar los espacios de datos y separarlos del tablespace pg_default (que gestiona la DB “postgres”, la DB de “information_schema” y demás información, por lo general en “/var/lib/postgresql/9.1/main”); además, ambos deberían estar separados de la partición raíz donde está el sistema operativo.

Las optimizaciones acá realizadas son de las más sencillas a nombrar para postgreSQL, se tomó una máquina virtual en Xen 4.1 en una portátil y se optimizó de lo más básico, para demostrar, que hasta en los cambios más sencillos, pueden afectar el “performance” de aplicaciones diseñadas con postgreSQL.

Preparación primaria

Si estamos instalando un servidor de datos, lo primero que debemos pensar es en separar el montaje de /var del resto del sistema, de hecho, si podemos incluso separar /var/log sería muy apropiado; también es bueno separar /tmp (más 1Gb es innecesario) ya que si no separamos /tmp, Debian GNU/Linux utilizará un tmpfs montado en RAM para gestionar /tmp (restándonos un poco de RAM para trabajar, además que postgreSQL no utiliza la partición /tmp).

Un esquema básico podría ser:

  • / (raiz) (Debian GNU/Linux no ocupa más de 5Gb en este modo)
  • /tmp (1Gb como máximo)
  • swap (Lo necesario, aunque no mayor a 2GB en sistemas con más de 4Gb de RAM)
  • /var (2~4GB ya que será un servidor en producción)

Y de resto, un volumen lógico (LVM) que podemos modificar de tamaño de acuerdo a nuestra necesidad.

Luego de instalado el sistema, procedemos a instalar PostgreSQL.

Instalación

La instalación de postgreSQL 9.1 en Debian GNU/Linux es bastante sencilla:

  • Instalamos postgreSQL 9.1 (pero así no se debería quedar):
    apt-get install postgresql-9.1

PostgreSQL por defecto, creará una carpeta de configuración en: /etc/postgresql/9.1/main/

Y creará un espacio de datos en: /var/lib/postgresql/9.1/main/

Que no utilizaremos para nuestra base de datos, ya que crearemos un espacio propio.

Configuración inicial

Siempre es recomendable dejar el usuario “postgres” (el super-usuario de PostgreSQL) como un usuario “para accesos de emergencia”, ya que este usuario tiene garantizado el acceso a todas partes(si eres root en el sistema), es recomendable que NINGUNA base de datos tenga como “owner” el usuario postgres (y evitar en lo posible utilizarlo como usuario de acceso desde sistemas, aunque esto, obviamente lo he visto más de una vez ocurrir hasta en sistemas web).

  • Creamos un super-usuario para nuestras necesidades, primero cambiamos al usuario postgres:
    su postgres
  • Creamos un usuario, que será nuestro “super-usuario” para “nuestros” accesos, evitando así el usuario postgres:
    createuser -sPl jesuslara
    
    -- ingresamos nuestra contraseña
    Enter password for new role: 
    Enter it again:
  • Ejecutamos la consola SQL de postgreSQL:
    psql
  • Garantizamos al usuario que creaste acceso irrestricto sobre el la DB postgres:
    psql (9.1.3)
    Type "help" for help.
    
    postgres=# grant all on database postgres to jesuslara;
    GRANT

Y salimos de la consola:

postgres=#\quit

Configuración de postgreSQL

  • Accedemos al directorio /etc/postgresql/9.1/main
    cd /etc/postgresql/9.1/main
  • Si vamos a acceder de manera remota a nuestro postgreSQL, agregamos la siguiente línea al archivo pg_hba.conf:
    # la forma es:
    # host -> database (all: todas las db) -> usuario (all: todos los usuarios) -> subnet (de nuestra red) -> modo de clave
    host    all     jesuslara       192.168.100.0/24        md5
  • Habilitamos el acceso remoto en nuestro postgreSQL:

archivo /etc/postgresql/9.1/main/postgresql.conf

listen_addresses = '*'

Optimización del archivo postgresql.conf

  • Y cambiamos algunas opciones básicas del archivo postgresql.conf:
    shared_buffers = 256MB

‘shared_buffers': Es la memoria de trabajo compartida para todo el servidor postgreSQL, fíjese que por defecto en Debian GNU/Linux la opción es 24MB (y el valor por defecto si comentamos es 32MB), sin embargo, como esta es la memoria utilizada para trabajo de postgreSQL, es recomendable “al menos” el 25% de la RAM disponible (y jamás > 40%).

temp_buffers = 16MB

‘temp_buffers': La memoria temporal utilizada por cada sesión para las tablas temporarias y para apertura de tablas en cada sesión de cada base de datos, tome en cuenta que este valor dependerá obviamente de la cantidad de datos que carga cada sesión y dependerá muchísimo del sistema que se utiliza.

work_mem = 16MB

‘work_mem': uno de los valores más importantes y más despreciados, “work_mem” se refiere a la memoria temporal utilizada por cada sesión, para las operaciones de ordenamiento (ORDER BY) para las sesiones de diferenciación (GROUP … HAVING y DISTINCT) y para la gestión de hash (uniones HASH, indices HASH, hash_aggregations), si en nuestro sistema realizamos muchísimas consultas ordenadas, agrupadas, diferenciadas por cadenas, etc se crearán mucho de estos buffers de manera paralela, mientras más memoria asignemos, menos probabilidades hay que los ordenamientos y otras operaciones se hagan con archivos temporales en disco (más lentos que la memoria RAM).

max_stack_depth = 8MB

‘max_stack_depth': define el tamaño del espacio utilizado para cómputo de operaciones complejas, su valor está asociado al límite máximo que un usuario (en este caso, “postgres”) tiene derecho a reservar un stack, el valor soportado por nuestra distribución se determina con “ulimit -s”.

shared_preload_libraries = '$libdir/plpython2.so'

‘shared_preload_libraries': Permite cargar una librería específica cuando arranca el sistema, si utilizamos muchos procedimientos almacenados en un lenguaje específico (ej: python, perl, tcl, java, etc), es bueno pre-cargarla para que esté disponible cuando se utilice por primera vez. Nota: esta opción ralentiza un poco el reinicio del sistema.

bgwriter_delay = 500ms

‘bgwriter_delay': El background-writer es un proceso del servidor que se encarga de escribir a disco todos los “shared_buffers” modificados, este proceso conlleva una carga de I/O sobre el disco, su modificación permite o reducir el valor para evitar en lo más posible pérdidas de datos en equipos que pueden fallar, o su incremento permite reducir el I/O al disco duro en sistemas perfectamente protegidos.

Modificados estos parámetros básicos, vamos a modificar nuestro sistema operativo.

Optimización de Linux para postgreSQL

Una de las cosas que olvidamos “optimizar” (tunning) es nuestro sistema operativo GNU/Linux, con grupo de valores en el sysctl ya podemos ayudar “mucho” a nuestro postgreSQL.

  • Agregamos al archivo sysctl.conf

archivo: /etc/sysctl.conf

kernel.sem = 100 32000 100 128
kernel.shmall = 3279547
kernel.shmmax = 289128448
kernel.shmmni = 8192
fs.file-max = 287573
vm.dirty_bytes = 67108864
vm.dirty_background_bytes = 134217728

Nota: observe el valor de shmmax, la cantidad de “memoria máxima reservada para un shared_buffer” que puede crear una aplicación debe ser igual o mayor al valor del shared_buffer de postgreSQL, este valor está en bytes y es ~ 275MB.

La cantidad máxima de archivos que pueden abrirse en un sistema, dependerá obviamente del nivel de trabajo de la DB, durante una operación regular, la gente puede ejecutar “lsof | wc” para obtener la cantidad de archivos abiertos.

  • Y luego, las aplicamos:
    sysctl -p
    
    --
    kernel.sem = 100 32000 100 128
    kernel.shmall = 3279547
    kernel.shmmax = 289128448
    kernel.shmmni = 8192
    fs.file-max = 287573
    vm.dirty_bytes = 67108864
    vm.dirty_background_bytes = 134217728

Ya, con estos sencillos cambios, podemos reiniciar el postresql:

/etc/init.d/postgresql restart
Restarting PostgreSQL 9.1 database server: main.

Y estamos listos para crear una partición y tablespace para nuestra DB.

Creación del espacio de tablas

Creamos una partición del tamaño necesario para contener “al menos” nuestra base de datos (esta es una guía básica, no hablaremos de particiones adicionales para metadatos, para índices y demás).

Nota: en nuestro caso, la partición es /dev/xvdb1 y mide 10GB.

El “journal”, para quien no lo conoce, es la razón por la cual no existe software de “desfragmentación” en Linux, todos los sistemas operativos que lo soportan (ext3, ext4, jfs, reiserfs, xfs, zfs, etc) tienen servicios que se encargan de ordenar, desfragmentar y gestionar tanto la data como los metadatos (información acerca de los archivos y carpetas en sí), pero además, los journal cumplen otras funciones, entre ellas, recuperar desde sus logs la data que pudiera “haberse perdido” luego de un fallo de energía y/o de sistema.

En sistemas de base de datos, la data es contenida en uno o más (y diversos) tablespaces, espacios de tablas donde la data, metadata e índices es contenida, como es la base de datos la encargada de gestionar la posición de los datos en ellos, el Sistema Operativo no requiere la presencia de un journal, o al menos, de un journal más relajado y menos estricto.

Formateando la partición

  • Se formatea la partición (disco):
    mkfs.ext4 -E stride=32 -m 0 -O extents,uninit_bg,dir_index,filetype,has_journal,sparse_super /dev/xvdb1

Utilizamos ext4, porque en modo “writeback” tiene un mayor performance que XFS para almacenar los tablespaces y tiene menor propensión a fallos.

  • Habilita el journal en modo writeback:
    tune2fs -o journal_data_writeback /dev/xvdb1
  • Si simplemente desea eliminar el journal, ejecute:
    tune2fs -O ^has_journal /dev/xvdb1
  • Nota: utilice esta opción a su propio riesgo, recuerde que no tener un journal afecta de 2 modos:
  • La data no es colocada en orden en el disco, fragmentando el mismo
  • Ante un fallo de energía, el FS no podrá recuperar desde el journal las últimas actividades para recuperar esos datos.
  • Se ejecuta un chequeo de archivo básico:
    e2fsck -f /dev/xvdb1
  • Creamos la carpeta de postgresql:
    mkdir /srv/postgresql
  • Y luego se monta con las opciones que describiremos más abajo:
    mount -t ext4 /dev/xvdb1 /srv/postgresql -o  noatime,nouser_xattr,noacl,discard,nodelalloc,data=writeback,barrier=0,commit=300,nobh,i_version,inode_readahead_blks=64,errors=remount-ro

Las opciones son:

Opciones de FS Linux:

noatime

No guardar la información del timestamp del último acceso a los archivos, esta información no es necesaria ya que postgreSQL gestiona apropiadamente el acceso a los tablespaces.

nouser_xattr

Deshabilita el uso de atributos extendidos de usuario, esto es seguro en postgreSQL ya que la carpeta donde se guardan los tablespaces no requiere ninguno de esos atributos.

noacl

No utilizar atributos extendidos ni ACLs POSIX, no son necesarias ya que solamente postgreSQL tendrá acceso a los archivos en esta partición.

Opciones específicas de ext4:

nobh

ext4 asocia buffers de datos con las páginas de datos, esos bloques de cache proveen garantía de ordenamiento de los datos; “nobh” evita el uso de estos buffers de ordenamiento de datos (sólo activable con “data=writeback”).

data=writeback

No se preserva el ordenamiento de los datos, la data será escrita en el sistema de archivos solo después que la metadata ha sido guardada en el journal. Aunque hay personas que recomiendan desactivar el “journaling” del disco, esto no es recomendable pues, aunque postgreSQL gestiona correctamente los datos, los metadatos (información de los archivos y carpetas en el FS) es responsabilidad de mantenerla consistente el FS.

commit=seconds

Los datos y metadatos son escritos a disco cada “n” cantidad de segundos, el valor por defecto son 5 segundos (commit=0 es igual a dejar el valor por defecto), un valor más bajo puede mejorar la seguridad de los datos, un valor muy alto mejora el performance pero ante un fallo podría perderse datos.

barrier=0

Deshabilita el uso de barreras de escritura, las barreras de escritura fuerzan el uso de ordenamiento on-disk de los commits al journal, haciendo las caché de disco seguras de usar, pero un daño en el performance del disco.

inode_readahead_blks=n

Cantidad de inodes que el sistema de pre-lectura de ext4 lee al buffer caché, el valor por defecto de n es 32, pero un valor de 64 es normal para optimizar las lecturas.

discard

Permite decidir que realiza con los bloques que son liberados, por lo general ext4 ejecuta una operación de trim (limpieza), con esta opción, ellos simplemente son marcados como descartados, evitando la escritura innecesaria de bloques.

i_version

Permite indicar que los inodes serán de 64-bits, solo disponible si se está en un sistema a 64 bits.

  • Luego de montada de esta manera, lo fijamos en el /etc/fstab
# particion para postgresql
/dev/xvdb1 /srv/postgresql ext4 rw,noatime,errors=remount-ro,nouser_xattr,noacl,commit=300,barrier=0,i_version,nodelalloc,data=writeback,inode_readahead_blks=64,discard 0 0
  • Comprobamos:
    mount -a

Y ya estamos listos para crear el espacio de datos para nuestra DB!.

El espacio de tablas (tablespace)

Crearemos un simple espacio de tablas en nuestro optimizado sistema de archivos ext4 para contener nuestra base de datos:

  • cambiamos el propietario a la carpeta /srv/postgresql
    chown postgres.postgres /srv/postgresql
  • cambiamos al usuario “postgres” y abrimos la consola ‘psql':
    su postgres
    psql
  • En la consola, ejecutamos el comando para crear un espacio de tablas:
    postgres=# CREATE TABLESPACE db_sistema OWNER jesuslara LOCATION '/srv/postgresql';

Y listo!, ya tenemos un espacio de tablas disponible para crear bases de datos y optimizado!

Usando el espacio de datos optimizado

Para crear una DB que no esté asociada al espacio “por defecto” (pg_default) ejecutamos:

  • Crear una DB:
CREATE DATABASE sistema WITH ENCODING='UTF8' OWNER=jesuslara TEMPLATE=template0 TABLESPACE=db_sistema;

Y como verán, le pasamos el tablespace “db_sistema” que hemos creado anteriormente.

¿Alguna prueba de la eficiencia?

La configuración siguiente no se hizo en un sistema dedicado para tal, se realizó en una portátil, corriendo Xen 4.1 y en una VM con 1GB de RAM se instaló el postgreSQL con las opciones nombradas, sin embargo, es posible notar una mejora en el performance general de las consultas (y eso que son solamente optimizaciones básicas).

Para ello, creamos una DB adicional, de un sistema administrativo (migrado desde Oracle hasta postgreSQL) que un amigo amablemente me facilitó para esta prueba.

Para ello, se movieron algunas funciones de código “Visual Basic” a código PL/Python y PL/pgSQL y se creó una consulta semi-compleja, de unas 26 líneas de extensión, que unifica unas 6 tablas del sistema para calcular una simple pre-nómina (ivss, paro forzoso, caja de ahorros, faov, isrl, etc); hay que notar que en la versión “cliente-servidor” de la aplicación, la nómina de 13 mil empleados dura varias minutos hasta horas con múltiples conceptos; para nuestra versión “simplificada” (5 asignaciones y 3 deducciones y cálculo de salario integral); la consulta se ejecutó en: 33068ms Para 13674 registros.

Pero, lo mejor ocurre si lo ejecutas por segunda vez!, ya que los buffers de trabajo mantienen en cache las operaciones de hash_aggregate (necesarias para algunos de los cómputos de agregado realizados), la segunda ejecución fué: 3107 milisegundos (3 segundos)

¿13 mil cómputos de empleados en 3 segundos?, ¡Nada mal para ser una portátil!

Conclusiones

Estas optimizaciones no son ni la décima parte de las que podemos optimizar de postgreSQL, pero es un comienzo, esta guía surge de la necesidad de orientar a las personas, que creen que pueden poner un sistema en producción de un postgreSQL recién instalado, estas optimizaciones mínimas, que cualquiera puede seguir, son un ejemplo y un comienzo.

No se dejen engañar con esas personas que dicen que “postgreSQL no rinde como Oracle” y un largo etcétera de excusas baratas, si alguien en su sano juicio instala Oracle cambiando parámetros en el sysctl, modificando los valores de tunning del sistema operativo o del sistema de archivos, clusterizar al máximo e incluso hace cosas más “malandras” como generar índices “al vuelo” por aquellos DBA vagos que jamás piensan bien sus bases de datos; ¿por qué la gente no hace lo mismo con postgreSQL?, tal vez porque ser un DBA “certificado postgreSQL” es más difícil y hacer entender a la gente, cuando crean un sistema conectado a datos, que su principal preocupación no debería ser “si usar PHP o Python” sino ver de qué formas optimizarás el S.O, el sistema de archivos, las consultas, el planificador, el acceso a disco y la gestión de índices para lograr que te sea “inocuo” si la gente utiliza perl o Visual Basic como Front-End.

Al final, postgreSQL debe tener el mando de los datos de la aplicación, y aprender a “verdaderamente” instalarlo, es el primer paso!.

¡Happy Hacking!

[postgreSQL] Trabajando con fechas y generate_series

Un amigo me preguntó ¿cómo se hace en postgreSQL para obtener todos los días específicos de un mes?, ejemplo, “todos los días del primero al último en secuencia” o “el último día específico”, o “¿cuándo cae el black-friday en amazon este año?” o cosas como “¿cuántos lunes tuvo este mes?, debido a que llevaba mucho tiempo sin trabajar con postgreSQL, la pregunta me llevó por refrescar un poco …

Ahí recordé generate_series!

Una de las virtudes de “generate_series” es que nos permite obtener un conjunto consecutivo de valores enteros desde un valor “A”, a un valor “B” de manera muy sencilla:

SELECT generate_series(0,5)

Salida:

0
1
2
3
4
5

Esto, inicialmente no tiene mucho sentido asociado a trabajar con fechas, pero cuando te das cuenta que puedes operarlas vía intervalos y colocarlas en una consulta en el FROM, encontrarás cosas muy útiles cómo:

SQL: Obteniendo las fechas de cada día del mes en postgreSQL:

SELECT d.date, EXTRACT('month' from d.date) FROM
(SELECT generate_series(0,30) + first_day_month('2012-02-21'::date)::date as date) d
WHERE EXTRACT('month' from d.date) = EXTRACT('month' from first_day_month('2012-02-21'::date))

Nota: first_day_month es una función propia que retorna el primer día de un mes específico, lo pueden obtener con:

SELECT DATE_TRUNC('month', '2012-02-21'::date)

Como ven, la consulta genera un vector en el FROM, de una serie generada de sumar desde 0 al 30 a cada fecha desde la primera fecha del mes, si a ese vector le aplicamos un “WHERE” donde sólo me muestre los días que su MES coincida con el mes del primer día, tienes la función correcta que te retorna todas las fechas de un mes específico.

¿Cuántos lunes tiene el mes?

Para saber, vía “generate_series” las fechas en que cayeron todos los lunes del mes específico sería un simple “WHERE” contra la consulta anterior:

SELECT d.date, EXTRACT('month' from d.date) FROM
(SELECT generate_series(0,30) + first_day_month('2012-02-21'::date)::date as date) d
WHERE EXTRACT('month' from d.date) = EXTRACT('month' from first_day_month('2012-02-21'::date))
AND EXTRACT('dow'from d.date) = '1'

Verificando que “dow” (DAY OF WEEK”) es igual a 1 (Lunes).

Contarlos, es simplemente reemplazar el campo por un COUNT (GROUP implícito):

SELECT count(d.date) as n_lunes FROM
(SELECT generate_series(0,30) + first_day_month('2012-07-21'::date)::date as date) d
WHERE EXTRACT('month' from d.date) = EXTRACT('month' from first_day_month('2012-07-21'::date))
AND EXTRACT('dow'from d.date) = '1'

Y Julio tiene 5 lunes …

Computando fechas específicas

El Black-Friday (día interesante para los geeks que aprovechamos comprar cosas en Amazon xD) es una interesante fecha para calcular, es el día siguiente al “Día de Acción de Gracias” que son el cuarto jueves de Noviembre (el “black-friday” sería el 4 viernes de noviembre):

SELECT d.date FROM
(SELECT generate_series(0,30) + first_day_month('2012-11-21'::date)::date as date) d
WHERE EXTRACT('month' from d.date) = EXTRACT('month' from first_day_month('2012-11-21'::date))
AND EXTRACT('dow'from d.date) = '5' AND ((EXTRACT('day' from d.date)::integer+1)/7) = 3

Resultando que el “Black-friday” caerá el “2012-11-23″.

Claro, que simplemente podrías enumerar las semanas usando una función “ventana” (window functions) >

SELECT ROW_NUMBER() OVER (ORDER BY d.date) as position, d.date FROM
(SELECT generate_series(0,30) + first_day_month('2012-11-21'::date)::date as date) d
WHERE EXTRACT('month' from d.date) = EXTRACT('month' from first_day_month('2012-11-21'::date))
AND EXTRACT('dow'from d.date) = '5'

Y esto retorna:

1   |   “2012-11-02″
2   |  “2012-11-09″
3   |  “2012-11-16″
4   |  “2012-11-23″
5   |  “2012-11-30″

Conclusiones

Uno de los grandes problemass a la hora de utilizar un RDBMS, es tratar de mantenerse “genérico”, sin pensar en utilizar funciones específicas del lenguaje que podrían optimizar nuestro trabajo al máximo, de hecho, en Oracle la mayoría de los ejemplos acá presentes se hacen con PL/SQL o con cláusulas WITH más complejas que nuestra corta función SQL usando “generate_series”.

[Nota del día] Es mySQL GPL?

He visto en numerosos lugares y charlas donde la gente se refiere al sistema de “Dual Licenciamiento” de mySQL y en algunos casos, incluso que es una base de datos “propietaria” adquirida por Oracle.

Quería simplemente desmentir esos rumores …

http://www.mysql.com/about/legal/licensing/index.html

MySQL, como lo demuestra su esquema de licenciamiento, para todas las formas (open source projects, personal projects, dispositivos embebidos, gobierno, empresarial y comercial) es GPL, lo que si existe (además) es acceso a servicios, soporte, aplicaciones de diseño e índole empresarial (Workbench, etc) que pueden ser accedidas por una licencia comercial, además existe una versión comercial “Enterprise”:

http://www.mysql.com/products/enterprise/features.html

Que combina la RDBMS mySQL libre y abierta, con una serie de herramientas (mySQL Monitor, etc) y una serie de servicios (garantía de soporte 24/7 incluyendo soporte, administración y resolución de problemas de manera remota) y puede ser adquirida por empresas *si acaso* desean todos esos servicios.

Excepciones FOSS

mySQL y todos sus derivativos deben ser GPL, empresas, IT y Gobierno pueden usar mySQL de manera libre amparados por la GPL, las patentes implicadas por mySQL iban con una garantía de mySQL AB para ser usadas únicamente para defender a la comunidad del Software Libre y abierto de las empresas que demandan a las comunidades.

mySQL incluye excepciones al FOSS (Free and Open Source Software) para aquellas empresas que desean usar mySQL pero que no quieren liberar su código bajo la GPL (pero *SOLO* manteniendo una licencia OSI-Compatible, como por ejemplo BSD) pero estas excepciones al FOSS no afectan a la base de datos (GPLv.3) sino única y exclusivamente a las librerías de conexión (LGPLv.2) y a las librerías cliente de mySQL, para que una persona (programando por ejemplo en .NET) pueda usar mySQL como backend y usar las librerías de conexión sin tener que liberar su programa bajo la GPL.

Sin embargo, eso significa que debe liberarlo en alguna forma “compatible OSI” (como BSD) y jamás podrá distribuir el servidor de base de datos integramente con la DB, so pena de tener que liberarlo bajo la GPL:

“You obey the GPL in all respects for the Program and all portions (including modifications) of the Program included in the Derivative Work”

Aunque claro, si aún se sienten paranóicos por lo que está haciendo Oracle con Java (y Oracle es actual dueño de mySQL AB), pues cuentan con la protección de la FSF y de un software licenciado bajo la GPL (algo que nunca ocurrió con Java).

Para los aún más paranóicos y que no desean usar ya más a mySQL, cuentan con MariaDB, que es un fork de mySQL 5.1 hecha por su “mismisimo creador” Michael Widenius.-

La nueva próxima versión de postgreSQL: 9.0

De acuerdo a la estructura de discusión de versiones de postgreSQL; las versiones “saltan” a un número superior cuando se cuenta entre las mejoras un buen número de cualidades que merecen ser “resaltadas”; en este caso y a partir de discusiones hechas la semana pasada en:

http://archives.postgresql.org/pgsql-hackers/2010-01/msg02056.php

Se ha tomado la decisión que postgreSQL saltará de la versión 8.4 a la 9 directamente (el actual código en el GIT para 8.5 será renombrado a 9; 8.5 nunca será lanzado).

La decisión se toma en cuenta por una serie de mejoras incorporadas a postgreSQL que valen la pena ser destacadas.

Streaming Replication

Una notable mejora en el sistema WAL (Write-Ahead Logging) permite que el paso de los archivos del transaction-log sea hecho en “streaming” permitiendo una replicación “in-realtime”; por tanto el “archive” (archivado y transmisión) de los WAL records se hace continuamente permitiendo tener un sin-número de servidores “stand-by” replicados correctamente y actualizados.

Esto hace que cualquier cambio en el servidor primario; se verá reflejado en todos los Standby-Servers casi inmediatamente.

Hot Standby

Cuando un servidor primario está realizando Archivado (via WAL) o cuando se está recuperando vía un Archive Recovery; la recuperación puede ser realizada “en caliente” y se puede desde ya acceder a los datos de cualquier consulta “read-only” (SELECT); es decir, ahora se podrá consultar a una Database; aun cuando esta se esté enfrentando a un proceso de Recuperación (Archive Recovery).

Esto significa; que los servidores “stand-by” que están en espera como replicas del servidor primario, pueden ser utilizados para operaciones de consulta a la DB.

Domain Casting

Ya conocemos la posibilidad de postgreSQL de hacer “type-casting” (esto es, el forzado de un tipo) utilizando el signo de doble dos puntos:

SELECT fecha_nacimiento::date

Ahora; se podrá también hacer type-casting de dominios; ejemplo:

SELECT correo::email

Donde email es un dominio con validación vía REGEX.

Además entre otras cosas se prevee:

* Soporte para python 3 en PL/Python

* Posibilidad de “packages” a lo Oracle-Style

La “pre-alpha” aún es de postgreSQL 8.5; se debe esperar a que el comité vote a favor de cambiar a 9.0 y se creen los repositorios de la “pre-alpha” 9 para poder empezar a experimentar con esta nueva versión de postgreSQL.

mySQL vs. MariaDB: Michael Widenius crea fork “libre” de mySQL

Michael Widenius; conocido en los “bajos fondos” como Monty y creador de la popular base de datos MYSQL; no solamente se ha retirado de mySQL (o de Sun; algo que hizo en las visperas de la venta de Sun a principios de Marzo); sino que además ha lanzado un “Fork” de mySQL bajo los siguientes principios:

  • Completamente libre
  • Sin código privativo (u open source) de terceros sin una licencia libre
  • Sin que las razones comerciales sopesen a las técnicas y de diseño

Lo que destaca de sus motivos (en un articulo en su blog llamado “to be free or not to be free“; para crear un fork; habla de liberar a mySQL de codigos que no cumplan con libertades esenciales (me imagino que se refiere a codigos como los incluidos en versiones iniciales de falcon y en NDB), que Oracle no es una empresa a la cual confiar la continuidad de un proyecto de software libre (le falta mucho como empresa para ser verdaderamente un líder de proyectos libres) y aun así aceptando ser un líder de desarrollo de software libre; el mantener a Oracle privativo y tener a mySQL en su poder lo enfrentará a juicios Anti-monopolio (al tener una excesiva cuota sobre el mercado de bases de datos) y en algún momento mySQL sufrirá algún golpe nocivo de la cual no se recuperará; por ende, es mejor hacer un fork ahora.

Inicialmente me decantaba más por la idea de que Oracle comprara a Sun para acceder a su Hardware (los famosos Sun Spark, Sun Fire y Sun Blade), para acceder a OpenSolaris, a Java (que sin Java, Oracle es una porquería) y a una serie de tecnologías de mercados emergentes (como Virtualbox); sin embargo, no solamente el hecho que Michael Widenius se retire de Sun, sino que además desee hacer un fork “completamente libre” de mySQL pues muestra un camino “sombrio y preocupante” sobre lo que podría ser el futuro de mySQL en manos de Oracle en los próximos años.

MariaDB (recibiendo el nombre de su hija menor Maria, cumpliendo la tradición de seguir el nombre de sus hijos, my “la mayor” y Max “el varon”) es un fork de mySQL de la versión 5.1 de mySQL, contiene los siguientes cambios:

  • maria como storage por defecto (en reemplazo de myISAM)
  • Maria es un storage transaccional/no-transaccional que además es “crash-safe” y es un reemplazo seguro y más eficiente de myISAM (además de HEAP, merge y el resto de basadas en myISAM)
  • estreno de PBXT (primeBase XT); es un storage Transaccional MVCC muy semejante a postgreSQL, además permite “BLOB streaming” como una manera eficiente de almacenar cualquier tipo de binario de gran tamaño.
  • XtraDB como storage de reemplazo a innoDB (que Oracle es dueño de innoBase, desde hace un par de años).
  • Remoción de Berkeley DB (razones obvias, Oracle es también dueño de BDB)
  • Percona XtraDB es completamente compatible con InnoDB así que se podrán migrar facilmente todas las DB en innoDB en un paso
  • Maria planea ser MVCC y ACID compliant; pero a su vez con un small footprint y código bastante pequeño, lo que plantea ser no solamente un eficiente reemplazo de myISAM sino ser un fuerte competidor a SQLite para dispositivos embebidos
  • Falcon (el storage para sistemas de data crítica de alta velocidad) será por defecto un storage en mariaDB
  • mariaDB podrá ser multi-port (una misma instancia podría ocupar varios puertos para optimizar y mejorar el desempeño)
  • Storage Federated (que permite de una manera facil tener la data centralizada en un servidor y la metadata en indices en servidores alternos)
  • Mejoras en la velocidad y el diseño técnico versus las “features” comerciales

Es obvio que maria 1.5 está bastante fuerte; aunque la versión 2.0 del storage pretende ser muchisimo mejor que myISAM; la conclusión lógica es comenzar a “probar” y a ofrecer mejoras y bugs a mariaDB como se hacía con mySQL y en lo que Oracle comience a mostrar sus garras salir “corriendo” a su hermanito menor libre mariaDB.

Yo en mi caso ya hice lo mio y me di de alta en el grupo de developers de MariaDB.

No es hora para salir todos “corriendo en estampida” a mariaDB; aun esta algo “cruda” y tienen un largo camino que recorrer; sin embargo, no solamente porque siga o no libre mySQL, mariaDB vuelve a las manos del creador de mySQL con una visión más renovada, de hacer algo bien y libre, no pensando tanto en cosas comerciales sino en las virtudes técnicas (me imagino que Michael Widenius habrá instalado postgreSQL) y por ende, tendremos una nueva base de datos libre que testear, probar, usar e instalar!.

Si desean saber como es Maria (la hija de Monty); he aqui su foto:

Maria Widenius

Maria Widenius

Espero que mariaDB siempre mantenga esas caracteristicas y sea una DB tan linda como quien le dio el nombre.

Exportando datos en vivo desde mySQL hasta postgreSQL usando mysqldump

En muchos casos el proceso de migración de datos de una base de datos a otra se convierte en un tedioso proceso de convertir cosas de un punto a otro (ejemplo: crear archivos separados por coma o dumps SQL que debemos modificar) y muchas veces la gente no explota las capacidades de mySQL como “agente de migración” rápido y efectivo.

MySQL goza de una capacidad interesante para “emular” el comportamiento de otras bases de datos, esto se logra via la variable sql_mode; a nivel de la herramienta de migración mysqldump es a través del parámetro –compatible; con esta capacidad, podremos generar “volcados” (dumps) de tablas completas de una manera “compatible” con otras bases de datos, incluyendo postgreSQL.

La situación de ejemplo:

Tengo dos bases de datos, una en mysql llamada dpt_venezuela (División Político Territorial Venezolana), la obtuve a partir de interesantes transformaciones de la data que entrega el INE (Instituto Nacional de Estadística) que es una vulgar hoja de excel a la cual hay que hacerle muchas transformaciones en los datos (y que gracias a LOAD DATA INFILE en mySQL eso es un paseo); tengo las 4 tablas (entidades, municipios, parroquias y ciudades) en un schema “dpt” dentro de una DB en postgreSQL para un proyecto (iut); la idea ahora es llenar esas tablas sin tener que hacerlo a mano ni tener que generar un DUMP para trabajar con COPY, sino hacerlo directo y “en vivo”.

Describe de la tabla dpt_entidades (mySQL)

CREATE TABLE `dpt_entidades` (
  `id_entidad` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `entidad` varchar(50) DEFAULT NULL,
  `cod_entidad` char(3) DEFAULT NULL,
  `capital` varchar(128) DEFAULT NULL,
  `mostrar` tinyint(1) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id_entidad`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8

Tabla dpt.entidades (postgreSQL):

CREATE TABLE dpt.entidades
(
  id_entidad int4 NOT NULL,
  entidad varchar(65) NOT NULL,
  cod_entidad char(3) NOT NULL,
  capital varchar(128),
  mostrar int2 NOT NULL DEFAULT 1,
  CONSTRAINT dpt_entidades_pk PRIMARY KEY (id_entidad)
)
WITHOUT OIDS;
ALTER TABLE dpt.entidades OWNER TO postgres;

Entendiendo a mysqldump:

mysqldump puede generar archivos separados por coma, xml o en su defecto (que usaremos) sentencias INSERT INTO; como hay diferencias sintácticas entre mySQL y postgreSQL usaremos un parámetro de mysqldump conocido como “–compatible” y que recibe como valor la palabra “postgresql”, el resto de parámetros son útilitarios de mysqldump (–skip-opt elimina comentarios y opciones “solo mysql” y -n me permite obviar las sentencias de CREATE TABLE de la tabla respectiva, –tables me permite generar una lista de tablas que serán exportadas).

Nota: debemos agregar –default-character-set=utf8 ya que por defecto, mysql hará el dump en latin1 y postgreSQL es por defecto utf8.

Inicialmente, la sentencia de mysqldump quedaría asi:

mysqldump -u root --password=paswd001! --database dpt_venezuela -t -n --compact --skip-opt --default-character-set=utf8 --compatible=postgresql --tables dpt_entidades

El único problema que tenemos ahora, es que la sentencia INSERT INTO generada:

INSERT INTO "dpt_entidades" VALUES (1,'Distrito Capital','DCA','Caracas',1);

No es aún compatible con postgresql, ya que como verán, la relación public.dpt_entidades no existe, sino que es dpt.entidades (schema: dpt, tabla: entidades); pero no hay nada que el comando linux sed no pueda arreglar.

Arreglando la salida con sed:

sed es en resumidas cuentas un comando editor; nos permite hacer sustituciones y reemplazos sobre archivos o sobre la salida std de nuestra consola; por ende, podemos pasar toda la salida del comando mysqldump a través de sed y modificar lo que queramos, en nuestro caso, un reemplazo de “dpt_entidades” > dpt.entidades.

Para ello, adicionalmente usamos pipe ( | ) un “concatenador” de la consola Unix, que nos permite indicar que la salida de un comando irá integramente hacia la entrada del comando siguiente; de esta manera, nuestra sentencia mysqldump ahora dice:

mysqldump -u root --password=paswd001! --database dpt_venezuela -t -n --compact --skip-opt --default-character-set=utf8 --compatible=postgresql --tables dpt_entidades | sed -e "s|\"dpt_entidades\"|dpt.entidades|"

Ahora si vemos, nuestras sentencias INSERT tienen la forma que postgreSQL necesita:

INSERT INTO dpt.entidades VALUES (1,'Distrito Capital','DCA','Caracas',1);

Ahora solo falta entregarle estas sentencias a psql (el comando de entrada de postgreSQL).

psql y entrando datos a postgreSQL:

Usar psql es bastante simple, solamente convocamos la DB (iut) usando el usuario owner de la misma (jesuslara) y pidiendo password (-W), en nuestro caso, recibirá datos de entrada usando PIPE ( | ) que viene desde el comando mysqldump+sed anteriormente ejecutado:

psql -U jesuslara -W -h localhost -d iut

y la cosa al final queda:

mysqldump -u root --password=paswd001! --database dpt_venezuela -t -n --compact --skip-opt --default-character-set=utf8 --compatible=postgresql --tables dpt_entidades | sed -e "s|\"dpt_entidades\"|dpt.entidades|" | psql -U jesuslara -W -h localhost -d iut

Entrando los datos correctamente hasta la tabla dpt.entidades de la base de datos iut.

Repetimos esto para municipios, parroquias o ciudades (o de plano, hacemos un dump de todas las tablas, agrandando acorde el sed para transformar todas las salidas a la vez) y tendremos migrados nuestras tablas desde mysql a postgresql sin necesidad de archivos intermedios.

Usando mySQL como gestor de migraciones:

sql_mode y –compatible son grandes aliados a la hora de migrar datos; LOAD DATA INFILE es mucho más poderoso que COPY y por ejemplo, hay muchas cosas allá afuera en MS Access o MS Fox Pro que ameritan ser migradas y mySQL ayuda muchisimo para cargar (además de normalizar y transformar) la data que venga de orígenes heterogéneos y llevarla limpia y sin problemas a postgreSQL.

Que disfruten este truco de la abuela!.

Nota: recuerden que sed es un comando muy poderoso y pueden por ejemplo, aplicar expresiones regulares de reemplazo para la transformación de los datos y los metadatos.

[Ponencia] Linux Tweak & Tunning

Durante algún tiempo he agrupado una colección de trucos y detalles sobre los sistemas Linux y fue hace poco que decidí agruparlos en una presentación que he llamado “Linux Tweak & Tunning”; espero ir agrandándola a medida que vaya coleccionando trucos y nuevas cosas.

Agrego adicionalmente unas modificaciones a la charla de “integración de Servicios de infraestructura con openLDAP” con la integración hacia freeRadius y autenticación.

Charla: Linux Tweak & Tunning

Charla: Integración de servicios de infraestructura sobre openLDAP

[Ponencia] Bases de datos en Software Libre

El día 10 de abril (como en otros eventos de Emprendedores de Software Libre auspiciados por CNTI) me tocó realizar el cierre del evento en el estado Táchira con una ponencia que he dado en llamar “Base de datos en Software Libre”; en ella enfoco y desmiento la gran mayoría de mitos existentes en el software libre alrededor de los manejadores de bases de datos y expongo las bases de datos libres y de código abierto más comunes actualmente, algunos “benchmarks oficiales” y una que otra idea interesante.

La versión en PDF de la ponencia está aqui: PDF base de datos libres

Esta presentación corresponde a la teoría del Taller “Migración de bases de datos en SL” que realizaré en algunas sedes (como Maracaibo) del Congreso Nacional de Software Libre (CNSL) a partir del 18 de Abril.

Actualización: por migración de blog, el PDF de la ponencia pueden encontrarlo acá: PDF base de datos libres

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

Únete a otros 2.972 seguidores

%d personas les gusta esto: