Archivo del sitio

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! …

 

[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!

Proyecto: Colección “Dame luz!”

Siempre me ha gustado colaborar, por algo estoy en la comunidad de software y conocimiento libre (porque no todo es software), por lo general cada vez que hago algo voy tomando nota y llevando bitácoras, a veces acumulo muchas y por el trabajo y diferentes obligaciones nunca las publico; ahora vamos a cambiar eso!.

“Dame Luz!”  siempre ha sido una expresión interesante cuando la gente está perdida y necesita orientación.

Prólogo

Hoy fué un día interesante, estaba escribiendo algunas cosas, más que todo para la documentación en mi trabajo cuando varios amigos a la vez se conectaron a preguntarme cosas; me llamó la atención particularmente cuando a uno le dije: “epale, por que no usas net sam provision, yo dejé de usar smbldap-tools hace tiempo” y fué como un balde de agua fría para él!, dijo que no sabía nada al respecto y recordé obviamente lo que me habia costado a mí, encontrar información …

Esa fué la chispa que movió la iniciativa …

¿De qué se trata la colección?

Voy a iniciar un proceso de “regularización” de mis bitacoras, la gente en Flickr se inventa los “Proyectos 365″ (cada día, una foto) y yo inventaré un Proyecto 52 técnico; cada semana prepararé y tendré listo un HOWTO, paper técnico, una bitacora de trabajo, un artículo netamente técnico y orientado en alguno de estos 5 aspectos (que llamaré colecciones):

  1. OpenLDAP
  2. Servicios
  3. Virtualización con Xen y openVZ
  4. Bases de datos (postgreSQL, mySQL, MariaDB, BerkeleyDB)
  5. Programación (PHP, Python)

La idea general es regularizar esas bitácoras que tengo por ahí, ordenarlas y colocarlas acá para referencia de todas esas personas como mi amigo, que desconocían ciertas características de esas aplicaciones y que por mi trabajo, debo estar haciendo eso todos los días.

¿Por qué no un Wiki?

Inicialmente usaré esta plataforma mientras pueda planificar de forma ordenada como inyectarlas en Wikia (un sitio libre para tener wikis), Weyúu (que significa “Luz” en dialecto Maquiritare/Yekuana) es donde residirán los artículos, pero ya la gente lee y está pendiente de este blog así que la plataforma Wikia será usada como lugar para ordenar todo. No me gusta mucho wikia por su gran cantidad de publicidad (que a un artículo técnico le salga en la base un banner de pokemon no lo hace muy serio) pero es con lo más que cuento mientras pueda costear los dólares de un servidor para una plataforma como esta.

¿De qué consta la colección “Luz|Weyú”?

La colección tendrá un ciclo “ordenado” de artículos, que ya tengo listos y preparados, eso no significa para nada que la gente no puede comentar acá para solicitar un nuevo artículo o que les hable de algún problema técnico; la idea es hacer crecer naturalmente esta iniciativa.

Tampoco será muy rígida en las entregas; formalmente entregaré una semanal, pero puede que alguna otra sea entregada antes por solicitud de la gente o porque decida adelantar su publicación, descuiden, hay más de 52 artículos planificados.

En la Colección “OpenLDAP” se encuentran listos:

  • Árboles y Bosques LDAP: diseñando tu DIT (Directory Information Tree)
  • Extendiendo openLDAP: Creando tu propio ObjectClass
  • Extendiendo openLDAP: módulos y backends
  • Extendiendo openLDAP: Scripting, automatización y monitoreo
  • Overlays y Constraints en openLDAP
  • Extrayendo toda la información (incluyendo passwords) de un Active Directory a openLDAP

En la colección “Virtualización” están listos:

  • Bridges y Bonding de manera práctica con Xen
  • Aprovechando al máximo un DL 380 HP con openVZ
  • Creando personalizaciones (Roles) de VMs con Xen y Debian Lenny

En la colección “Servicios” tenemos:

  • Conectando openLDAP y Samba sin usar smbldap-tools
  • Automatizando Samba: Scripting
  • Implementando alias, listas, buzones compartidos y públicos con Postfix y Dovecot

En la colección “Bases de datos” tenemos:

  • Presentando a mariaDB: primeras pruebsa de migración desde mySQL
  • Integración Heterogénea: ETL para postgreSQL con Apatar
  • Implementación geográfica básica: postgreSQL y mySQL

Hay varias ideas por ahí rondando (como presentar Continuent Tungsten), pero aún están en prueba de concepto.

Por mis obligaciones he escrito muy poco de programación, aunque los HOWTO en prueba de concepto que están más cerca son:

  • Plataforma de envío de mensajes SMS usando GSM y Python

(de mi necesidad de que los NAGIOS envíen mensajes de texto a los jefes de informática)

Y, ¿Cuándo empiezo?

La fecha que escogí para publicar será todos los viernes uno, de acuerdo a los comentarios de la gente, adelantaré algunos o publicaré otros en otras fechas, esta lista se estará actualizando constantemente, agregando nuevos temas (sin salirse claro está de los 5 aspectos definidos, ya que son mis áreas donde poseo experiencia).

Espero poder contribuir con el avance y éxito de la utilización de software libre en Venezuela y espero contar con el apoyo de las personas que me siguen.

ACTUALIZACION

He logrado gracias al grandisimo apoyo de Octavio Rossell de Proyecto GNU/CNSL un espacio para un Wiki Colaborativo (sin tener que usar Wikia, que me llena de publicidad innecesaria) con el cual podré contar con un wiki para los manuales.

La dirección es: Phenobarbital con Wiki!

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.

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.

Seguir

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

Únete a otros 2.969 seguidores

%d personas les gusta esto: