PostgreSQL: HW Tunning

Holla buscando por ahi encontre este articulo

Doc Original: http://www.juancarlosmoral.es/postgresql-hardware-tunning/


Introducción

Creo que muchas de las personas que utilizan PostgreSQL, ya sea a nivel particular o en proyectos de explotación, desconocen que este formidable servidor parte inicialmente de una configuración tan mínima que resulta inadecuada en la mayoría de los casos. Esto se hace para asegurar que el servidor arrancará casi con cualquier configuración hardware, aunque, por supuesto, no es válida para gestionar tus bases de datos de manera eficiente.

Se puede mejorar el rendimiento de PostgreSQL atacando desde dos frentes:

1. Hardware: configurando y optimizando los recursos hardware de que va a disponer el servidor, como por ejemplo la memoria.
2. Software: utilizando técnicas basadas en software como el uso de índices, optimización de consultas, vacuuming, etc.

Es muy importante comenzar por ajustar el hardware para, una vez optimizado, olvidarnos de él y poner todas nuestras luces en la afinación sotfware. En este artículo pretendo explicar de manera sencilla cómo se puede pasar de un rendimiento del 0% al 100% modificando tan sólo un par de parámetros de su fichero de configuración ( postgresql.conf). Me he basado en el excelente documento de Bruce Momjian, PostgreSQL Hardware Performance Tunning, para escribir esta pequeña guía práctica.

La intención es la de ayudar al lector en este proceso. Va dirigido a cualquier persona que ya ha instalado PostgreSQL en su sistema GNU/Linux y ahora se dispone a extraer toda la potencia de este magnífico RDBMS. El enfoque utilizado es eminentemente práctico: seguiremos todos los pasos para hacer un ajuste real. Asumiremos que disponemos de un servidor PostgreSQL 8.x, 1 GB de RAM y un máximo de 100 conexiones a la BD.



Agradecimiento:

Agradezco a los miembros de la lista plpgsql-es-ayuda y en especial a Álvaro Herrera, la ayuda recibida en la revisión y actualización de este artículo.

Memoria RAM y paginación

Antes de comenzar, es preciso recordar aunque sea de forma somera, el papel de la memoria RAM en un ordenador, así como los efectos indeseados de la paginación.

Podemos imaginarnos la memoria RAM como un recurso limitado divido en rodajas o segmentos. Los segmentos, de un tamaño fijo, se agrupan formando páginas de memoria. En la memoria se guarda todo lo que la CPU necesita para hacer su trabajo, esto incluye programas, datos requeridos por los programas, el kernel,… y por supuesto, las zonas de trabajo de postgres.

Para optimizar el uso del espacio disponible en la memoria, las páginas que hace algún tiempo no se utilizaron son expulsadas por el S.O. al disco, a una zona denominada swap (intercambio). Esta actividad se denomina swap pageout y no supone un inconveniente, ya que se produce en periodos de inactividad de la CPU.

Lo malo viene cuando hay que recuperar una página desde la swap (que recientemente había sido expulsada de la memoria), porque el programa que la requiere tendrá que esperar hasta que se encuentre de nuevo allí. Este efecto adverso, que crece a medida que hay más páginas que se tienen que traer de la swap, se conoce con el nombre swap pagein o paginación.

El reto de nuestra afinación va a consistir en optimizar el uso de memoria para postgres, minimizando en lo posible el número de intercambios con la swap (pagein). El mejor ajuste de los parámetros de configuración será aquél que obtenga la máxima disponibilidad en memoria para la BD, sin perjudicar al resto de elementos, que también deben permanecer en memoria.

SHARED_BUFFERS

El número de shared_buffers es el parámetro que más afecta al rendimiento de PostgreSQL. Este valor, de tipo entero, indica el número de bloques de memoria o buffers de 8KB (8192 bytes) que postgres reservará, como zona de trabajo, en el momento del arranque para procesar las consultas. De forma predeterminada (en postgresql.conf), su valor es de 1000. Un número claramente insuficiente para conseguir un rendimiento mínimamente aceptable.

Estos buffers se ubican dentro de los denominados segmentos de memoria compartida. Es importante saber que el espacio ocupado por el número de buffers que pretendamos asignar, nunca podrá exceder al tamaño máximo que tengan los segmentos de memoria. En caso contrario, postgres se negará a arrancar avisando con un error que no puede reservar el espacio solicitado.

Llegados a este punto, te preguntarás:

* ¿Cuántos shared_buffers puedo asignar?
* ¿Cómo sé cual es el tamaño de un segmento?
* ¿Qué puedo hacer si se supera el tamaño máximo del segmento?

Vamos a resolver todas estas cuestiones de forma práctica, así que coge una taza de café y siéntate frente a tu servidor. El proceso consiste en los siguientes pasos:

1. Considerar un número superior al actual de shared buffers (comenzaremos por un 10% del total de la memoria)
2. Modificar el tamaño del segmento si no cabe el número de buffers
3. Comprobar el rendimiento y paginación
4. En función del resultado obtenido, aumentar o disminuir el porcentaje de memoria y empezar de nuevo

Una buena recomendación (lee el artículo de B. Momjian) es la de empezar asignando un 10% del total de la memoria RAM para shared_buffers y a partir de ahí, ir aumentando o disminuyendo dicho porcentaje en función del rendimiento y la paginación.

Para comprobar el rendimiento, aplica EXPLAIN a tus consultas. Para ver la paginación del servidor, puedes usar herramientas como vmstat o ipcs (consulta sus páginas man).

Comenzamos:

El 10% de 1 GB: (1048576 KB/10) = 104857 KB
shared_buffers: (104857 KB/8 KB) = 13107

Asignamos este valor a la variable shared_buffers (postgresql.conf) y reiniciamos el servidor. ¿Qué ocurre? Lo peor. Ahora… ¡ni siquiera arranca!.
Bueno, no te pegues un tiro todavía y mira el log (generalmente postgresql.log):

FATAL: no se pudo crear el segmento de memoria compartida: Argumento inválido
DETALLE: La llamada a sistema fallida fue shmget(key=5432001, size=112009216,
03600).
HINT: Este error generalmente significa que una petición de PostgreSQL para
obtener un segmento de memoria compartida excedió el parámetro SHMMAX del
kernel. Puede reducir el tamaño de la petición o reconfigurar el kernel con
un SHMMAX superior. Para reducir el tamaño de la petición (actualmente
112009216 bytes), reduzca el parámetro de PostgreSQL shared_buffers
(actualmente 13107) y/o el parámetro max_connections (actualmente 100).
Si el tamaño de la petición ya es pequeño, es posible que sea inferior
al parámetro SHMMIN del kernel, en cuyo caso se requiere alzar el
tamaño de la petición o disminuir SHMMIN.
La documentación de PostgreSQL contiene más información acerca
de la configuración de memoria compartida.

Está claro, ¿no?. El tamaño ocupado por los 13107 buffers que has pedido reservar, simplemente no caben en el tamaño actual de segmento (SHMMAX). Postgres en el arranque intenta reservar este espacio, pero al no poder hacerlo, desiste. ¿Qué podemos hacer?, sigue leyendo.

En Linux, de forma predeterminada, el tamaño de un segmento de memoria compartida es de 32MB. Podemos comprobarlo haciendo (el resultado es en nº de bytes):

$ cat /proc/sys/kernel/shmmax
$ 33554432

Y el espacio que requiere el número de buffers, es superior al tamaño del segmento:

13107 buffers ocupan 107372544 bytes (13107 * 8192 bytes/bloque)
107372544 > 33554432

La solución está en modificar el tamaño máximo del segmento de memoria compartida. Esto lo hacemos asignando un nuevo valor al parámetro del kernel SHMMAX. ¿Qué valor? Si volvemos atrás, al mensaje de error, avisa exactamente de cual es el tamaño mínimo que postgres necesita para arrancar. El tamaño de SHMMAX debe ser, como mínimo, ese valor.



NOTA:

En la documentación de la versión 7.4, se ofrecía una fórmula para calcular un valor razonable del parámetro SHMMAX en función del nº de buffers, de las conexiones concurrentes y de varias constantes. Esta fórmula ya no se utiliza en la versión 8.x.

Podemos cambiar fácilmente el valor usando el programa sysctl:

# sysctl -w kernel.shmmax=112009216

Alternativamente, si tu S.O. no dispone de ese programa, puedes usar la forma clásica:

# echo 111766938 > /proc/sys/kernel/shmmax
# echo 111766938 > /proc/sys/kernel/shmall



Nota:

Puede guardar este valor de forma permanente en /etc/sysctl.conf, de forma que los cambios se conserven entre arranques: kernel.shmmax=111766938

Reinicia postgres. ¿Funcionó esta vez?. ¡Bien!. Haz nuevas pruebas de rendimiento y sigue con el algoritmo.



NOTA:

El resto de parámetros del kernel de Linux, como por ejemplo SHMALL, están generosamente dimensionados con sus valores default y normalmente no requieren cambios. Si, de todos modos, quieres modificar el tamaño máximo de la memoria compartida (SHMALL), debes tener en cuenta que hay que expresarlo en número de páginas (y no de bytes), usando la siguiente fórmula: ceil(SHMMAX/PAGE_SIZE) El tamaño de página (PAGE_SIZE) para Linux 2.4 y 2.6, es de 4KB.

WORK_MEM

Este parámetro configura el espacio de memoria que postgres utiliza para realizar ordenaciones de tablas o de resultados parciales de consultas, sobre todo en cláusulas ORDER BY, CREATE INDEX o MERGE JOIN.

Este valor es más dificil de configurar porque depende, por un lado, de lo grande que sean las tablas o resultados que hay que ordenar, y por otro, del número de peticiones simultáneas para esa misma consulta (para cada una se empleará la misma cantidad de memoria).

Una buen comienzo es asignar entre un 2% y un 4% del total de la memoria si prevemos pocos accesos simultáneos a grandes sesiones de ordenación y mucho menor, si esperamos muchos accesos simultáneos a sesiones de ordenación pequeñas. Como antes, lo mejor es ir probando distintos valores y ver en qué pueden afectar a la paginación adversa (swap pagein). El valor hay que expresarlo en KB.

En nuestro ejemplo, hemos optado por usar un 4% de la memoria:

El 4% de 1 GB: 41943 KB (1048576 KB*4)/100
work_men = 41943

Referencias


* PostgreSQL Hardware Performance Tunning – http://www.ca.postgresql.org/docs/momjian/hw_performance/
* Managing Kernel Resources – http://developer.postgresql.org/docs/postgres/kernel-resources.html
* Lista de correo pgsql-es-ayuda – http://archives.postgresql.org/pgsql-es-ayuda/2005-11/msg00563.php