De todos es sabido que PostgreSQL es el motor de base de datos open-source que tienes que utilizar si necesitas músculo para mover grandes cantidades de datos. Es la alternativa gratuita a monstruos como Oracle ó SQL Server, con la ventaja de que no te obliga a tener un Windowze por debajo. Para todo lo demás, MySQL/MariaDB o Bases de Datos No Relacionales.
La evolución de la herramienta a lo largo de estos años ha sido fantástica, pero lo cierto es que la potencia sin control no sirve de nadaTM, y aún a día de hoy, la instalación por defecto de la herramienta (sudo apt-get install postgresql) en sus distintos sabores dista mucho de su aprovechamiento óptimo.
Particionando el espacio
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 lo ideal. También es bueno separar /tmp ya que si no lo hacemos, Debian GNU/Linux utilizará un tmpfs montado en RAM para gestionar /tmp, restándonos un poco de RAM para trabajar (aparte que PostgreSQL no utiliza la partición /tmp).
Un esquema básico de particionado podría ser:
/ 1GB (o más)[SWAP] 2GB or 2*tamaño RAM/var 10GB/usr 10GB/usr/local 15GB/tmp 2GB
/home 10GB
-------------------------------------------------------------
(Total SO) 50 GB
[PGDATA] directorio de datos de PostgreSQL ("el resto")
Si nos centramos ahora en PGDATA, podríamos tener:
[PGDATA] 5-10GB[PGDATA]/pg_xlog 5-10GB (Más si el volumen de logs de transacciones esperado va a ser alto)[PGDATA]/base Suficientemente grande para la Base de Datos[PGDATA]/[Tablespace] Opcional - Separamos tablespaces de alto volumen de tráfico.[PGDATA]/xlog_archive Lo suficientemente grande para volcados e históricos de logs.
Continuando con este último punto, una de las optimizaciones más necesarias (pero que casi nadie realiza) es gestionar los espacios de datos y separarlos del tablespace pg_default (que gestiona la BD “postgres”, la BD de “information_schema” y demás información, por lo general en /var/lib/postgresql/9.x/main); además, ambos deberían estar separados de la partición raíz donde está el sistema operativo.
Y para el resto, un volumen lógico (LVM) que podemos modificar de tamaño de acuerdo a nuestras necesidades.
Otra acción rápida que podemos realizar es mover el directorio pg_xlog a otro disco. Sólo con mover los ficheros WAL, bien usando un punto de montaje o un enlace simbólico desde el directorio pg_xlog a un disco o partición distinta del que se encuentran los ficheros principales de la Base de Datos. En Bases de datos de altas tasas de escritura, WAL debería tener su propio disco o RAID para asegurar accesos continuos de alta velocidad. En arrays RAID muy grandes o dispositivos SAN/NAS ésto ya se hace automáticamente gracias a sus sistemas de gestión interna.
Para concluir, uno de los aspectos más importantes que tenemos que tener en cuenta es que PostgreSQL no debería compartir acceso a disco con el sistema operativo, esto es, lo ideal es que PostgreSQL esté en una partición distinta a “root” (y si es en incluso en un disco separado, mejor).
Optimización de Linux para PostgreSQL
En el caso de que apostemos por una instalación sobre un sistema operativo GNU/Linux, una de las cosas que olvidamos “optimizar” es un grupo de valores en el sysctl que pueden ayudar mucho a nuestro SGBD:
Agregamos al 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: obsérvese que 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 BD, durante una operación regular (si ejecutamos lsof | wc podremos obtener el número de archivos abiertos).
Por último, aplicamos los cambios:
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
Finalmente, reiniciaremos PostgreSQL:
/etc/init.d/postgresql restart
Restarting PostgreSQL 9.x database server: main.Retocando el fichero de configuración postgresql.conf
PostgreSQL maneja dos ficheros de configuración principales: pg_hba.conf y postgresql.conf, ambos dentro de /etc/postgresql/9.x/main/
En esta entrada nos centraremos en el segundo, mientras que recomiendo al lector que se estudie también el primero, ya que nos permitirá retocar valores por defecto de nuestra instalación de PostgreSQL tan importantes como determinar las IPs desde las que está escuchando el motor de Base de Datos.
A continuación, listo los parámetros y recomiendo los valores que deben de tener para ajustar nuestro servicio al hardware sobre el que va montado:
-
effective_cache_size → 3/4 memoria sistema -
shared_buffers → 1/4 memoria sistema -
checkpoint_segments → 128 -
checkpoint_completion_target→ 0.9 -
log_statement → ddl -
work_mem → 50MB -
maintenance_work_mem →Memoria Disponible / 8 -
wal_buffers → 8MB -
random_page_cost →2.0 -3.0 -
max_fsm_pages »»1000 -
max_fsm_relations »»1000 -
cpu_tuple_cost = 0.0030 -
cpu_index_tuple_cost = 0.0010 -
cpu_operator_cost = 0.0005 -
log_min_duration_statement = 100
Herramientas
Hay multitud de herramientas que nos permitirán detectar, medir y mejorar el rendimiento de nuestra instalación de PostgreSQL; entre ellas:
-
pgTune (nos hace una propuesta en función del hardware que tenemos. RECOMENDADA.)
Conclusiones
En esta entrada sólo he querido perfilar algunas de las operaciones más comunes que se abordan a la hora de intentar optimizar el rendimiento del motor de Base de Datos PostgreSQL, pero me he dejado en el tintero muchas más (REINDEX, AUTOVACUUM) que animo al lector a explorar. En concreto, los siguientes artículos me han resultado muy útiles al respecto:
- https://wiki.postgresql.org/wiki/Performance_Optimization
- https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server/es
- http://www.anchor.com.au/hosting/dedicated/Tuning_PostgreSQL_on_your_Dedicated_Server
- https://linux.org.au/conf/2007/att_data/Miniconfs(2f)PostgreSQL/attachments/advanced.pdf
- http://www.revsys.com/writings/postgresql-performance.html