INSTALACIÓN DE POSTGRESQL - UBUNTU
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!.
Comentarios