Continuamos aumentando nuestros conocimientos de PostgreSQL, uno de los manejadores de bases de datos open source más populares del mundo. En esta cuarta entrega de los mini tutoriales, trataremos un tema bastante interesante como es el tuning de PostgreSQL, lo cual nos permitirá mejorar el performace de nuestro motor de base de datos.
Mini Curso de PostgreSQL: Tuning de PostgreSQL
Optimización de PostgreSQL
En su configuración por defecto, PostgreSQL no es ciertamente la base de datos más rápida, sin embargo, esto solo ocurre porque su configuración por defecto está diseñada para «trabajar en todas partes» y brindar a los usuarios (avanzados o no) un rápido startup de la base de datos sobre cualquier sistema operativo.
Afortunadamente solo nos tomará unos cuantos minutos ajustar y hacer un tuning de postgreSQL para que pueda enfrentarse como un campeón a proyectos de altos volúmenes de data.
Las recomendaciones que daremos están enfocadas para la optimización de un servidor de base de datos que se enfrentará a entornos de gran escala de información, pero los mismos principios aplican para entornos de mediano o bajo volumen de data, permitiendo que tu servidor PostgreSQL corra más rápido, independientemente de la cantidad de consultas y carga en el servicio.
Hardware
Las bases de datos son unos de los servicios que más consumen hardware, esto por el gran volumen de data que puedan manejar. Contar con varios procesadores y de gran velocidad ayudará al performace de la base de datos, así como también grandes cantidades de memoria RAM. Pero más importante que eso, sobre todo para las operacionesde lectura-escritura, es contar con discos rápidos, ya que serán un punto clave para el performance y capacidad de la base de datos.
Antes de Comenzar
Las modificaciones del tuning de PostgreSQL que realizaremos serán efectuadas en el archivo «postgresql.conf», por lo que sugerimos hacer respaldo del mismo.
cp postgresql.conf{,.bak} shared_buffers
La edición de esta opción es la forma más sencilla de mejorar el rendimiento del servidor de base de datos, de hecho por defecto el valor es bastante bajo para un entorno de producción. Por lo que siguiendo las recomendaciones de la comunidad de los expertos, estableceremos en Mb el shared_buffer a un 25% de la memoria RAM disponible en el sistema.
shared_buffer = Memoria RAM Disponible * 0.25
NOTA: cambiar «Memoria RAM disponible * 0.25» por el resultado de dicha multiplicación.
NOTA 2: Hay parámetros del kernel que necesitan ser ajustados para permitir grandes valores de shared_buffers, ya que el mismo pone límites a la cantidad de memoria compartida se puede asignar (para evitar accidentes). Estos serían los cálculos que tendríamos que efectuar para la asignación en bytes de los parámetros a continuación:
kernel.shmmax = shared_buffer * 1024 * 1024 kernel.shmall = kernel.shmmax / 4096
Una vez realizados los cálculos procedemos a editar el archivo /etc/sysctl.conf y podemos aplicar los cambios ya sea reiniciando el servidor o mediante el siguiente comando
sysctl -p
work_mem
Esta opción se utiliza para controlar la cantidad de memoria utilizada en operaciones de ordenación y tablas hash. Esto no es un parámetro de todo el sistema, se refiere a los recursos utilizados por una operación. Así que si una consulta compleja tiene varias operaciones de ordenación entonces esta utilizará múltiples unidades «work_mem».
El valor a asignar al work_mem podremos calcularlo de la siguiente manera:
work_mem = Memoria RAM Disponible * 0.15
effective_cache_size
Este valor indica a PostgreSQL cuánta memoria tiene disponible para los datos de almacenamiento en caché y ayuda en determinar si es o no viable utilizar un índice. Entonces se debe establecer la cantidad de memoria asignada a «shared_buffers» más la cantidad de caché de sistema operativo disponible. A menudo esto es más que 50% de la memoria total del sistema.
Podremos calcular el valor mediante la siguiente fórmula:
effective_cache_size = Memoria RAM Disponible * 0.75
maintenance_work_mem
Como su nombre lo indica, este es el equivalente de «work_mem», pero para consultas de tipo «mantenimiento» (VACÍO, CREATE INDEX, entre otras).
Dado que estas consultas no se ejecutan con mucha frecuencia, se recomienda la ejecución de las mismas cuando el servidor presente una carga baja, ya que por lo general estas tareas de mantenimiento necesitan bastante tiempo para ejecutarse.
Asignar un buen valor a este parámetro te ayudará a garantizar que las consultas de mantenimiento terminen tan rápido como sea posible.
Calcular el valor del parámetro de la siguiente manera:
effective_cache_size = Memoria RAM Disponible / 8
Con estos simples consejos de tuning de PostgreSQL ahora nuestro servidor de bases de datos será más veloz y robusto, pudiendo desenvolverse en entornos de alto volumen de data y de alta exigencia. Recuerda siempre que el proceso de tuning de PostgreSQL consiste en mantener una armonía entre el hardware y la parametrización del servicio. Todavía queda mucho más por aprender, así que si quieres conocer más sobre PostgreSQL sugerimos que estés atento a este Mini Curso.
¡En breve publicaremos la Parte 5 del Mini Curso de PostgreSQL!