Tunear Postgresql. Ubuntu

Se describe como sacar el máximo rendimiento a una instalación de Postgresql en Ubuntu.

Sólo con le objetivo de comparar, vamos a ver los resultados que se dan al ejecutar unas consultas simples en una instalación por defecto.

Resultados de una instalación por defecto

  1. Abrimos una base de datos desde psql.
  2. Activamos el contador de tiempo para que nos diga cuanto tarda en ejecutar cada consulta: \timing
  3. Creamos una nueva tabla para pruebas: create table test (id integer primary key); Le llevó 606,553 ms.
  4. Insertamos de forma automática 100000 registros: insert into test values (generate_series(1,100000)); Le llevó 2417,026 ms.
  5. Ejecutamos una consulta que nos devuelve el número de registros en la tabla: select count(*) from test; Le llevó 42,605 ms.
  6. Una vez tomados los tiempos borramos la tabla. drop table test; Le llevó 68,064 ms

Proceso y resultados del Tuneo

  1. Todas las personalizaciones necesarias se hacen el el fichero /etc/postgresql/9.1/main/postgresql.conf (Como se puede ver, estoy usando la versión 9.1, para cualquier otra versión es exactamente la misma ruta simplemente substituyendo el 9.1 por la versión correcta).
  2. Lo primero que cambiaremos será el parámetro shared_buffers que se corresponde con cuanta memoria está dedicada a PostgreSQL para datos en caché. Para servidores dedicados al alojamiento del motor de bases de datos, se recomienda un 25% de la RAM, en el caso de un sistema no dedicado, como es mi caso, se recomienda un 10% de la RAM. En mi caso para 2GB de RAM le asignaré 201MB.
  3. Lo segundo, si lo vemos necesario, será cambiar el parámetro max_connections, que es el número máximo de conexiones que postgresql soporta, por defecto, viene con 100, este número dependerá completamente del uso que se le de a la base de datos, en mi caso opté por dejarlo tal cual.
  4. Una vez tengamos esto, guardamos y probamos a reiniciar postgresql. sudo /etc/init.d/postgresql restart
  5. Posiblemente nos dará un error similar a este:

    Failed system call was shmget(key=5432001, size=443899904, 03600).
    2013-09-09 18:46:00 CEST HINT:  This error usually means that PostgreSQL’s request for a shared memory segment exceeded your kernel’s SHMMAX parameter.  You can either reduce the request size or reconfigure the kernel with larger SHMMAX.  To reduce the request size (currently 443899904 bytes), reduce PostgreSQL’s shared memory usage, perhaps by reducing shared_buffers or max_connections.
    If the request size is already small, it’s possible that it is less than your kernel’s SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
    The PostgreSQL documentation contains more information about shared memory configuration.

  6. Esto es debido a que por defecto el kernel de linux tiene un límite bastante bajo referente a la compartición de memoria, lo podemos solucionar momentáneamente o definitivamente, en este momento del proceso vamos a solucionarlo momentáneamente, y una vez veamos el resultado y estemos conformes con el, lo ponemos fijo:
    • Para 1GB de RAM. (para más cantidad, simplemente multiplicar).
    • sudo sysctl -w kernel.shmmax=1073741824
    • sudo sysctl -w kernel.shmall=262144
    • En mi caso para 2GB (Multiplicamos por 2)
    • sudo sysctl -w kernel.shmmax=2147483648
    • sudo sysctl -w kernel.shmall=524288
    • ** Lo único a tener en cuenta es que para sistemas de 32 bits nunca va a aceptar más de 4GB – 1byte es decir 4294967295 en el shmmax, para sistemas de 64bits no tenemos este problema. **
  7. Una vez ampliados los límites, volvemos a intentar iniciar postgresql y no tendríamos que tener ahora ningún problema. sudo /etc/init.d/postgresql start
  8. Ejecutamos free -m para saber cuanta memoria hay disponible para la cache del sistema. En mi caso 725MB.
  9. Con este dato volvemos a entrar en el fichero de configuración de postgresql. /etc/postgresql/9.1/main/postgresql.conf
  10. Editamos ahora el parámetro effective_cache_size con el resultado del valor que tiene ahora el parámetro shared_buffers + la memoria disponible para la cache del sistema. En mi caso 926MB. (El valor se debería corresponder más o menos con el 50% de la RAM).
  11. Ahora el parámetro work_mem con el resultado de (la memoria disponible para la cache del sistema / el parámetro max_connections) / 2. En mi caso 4MB.
  12. Ahora el parámetro maintenance_work_mem con 50MB por cada GB de RAM, en mi caso para 2GB de RAM, 100MB.
  13. Ahora el parámetro checkpoint_segments con un valor de 10.
  14. Y por último el parámetro wal_buffers con un valor de 16MB.
  15. Guardamos el fichero y reiniciamos el postgresql /etc/init.d/postgresql restart
  16. Con la nueva configuración vamos a volver a hacer las pruebas de arriba
    1. Abrimos una base de datos desde psql.
    2. Activamos el contador de tiempo para que nos diga cuanto tarda en ejecutar cada consulta: \timing
    3. Creamos una nueva tabla para pruebas: create table test (id integer primary key); Le llevó 580,387 ms. (Bajó 26ms)
    4. Insertamos de forma automática 100000 registros: insert into test values (generate_series(1,100000)); Le llevó 927,466 ms. (Bajó un segundo y medio 1500ms)
    5. Ejecutamos una consulta que nos devuelve el número de registros en la tabla: select count(*) from test; Le llevó 21,694 ms. (Bajó 21 ms)
    6. Una vez tomados los tiempos borramos la tabla. drop table test; Le llevó 30,944 ms (Bajó 37ms)
  17. Como podemos ver, en consultas grandes hay una diferencia muy grande entre la configuración por defecto y la recomendada.
  18. Por último, si estamos contentos con los resultados tendremos que fijar los valores de SHMMAX y SHMALL para que al reiniciar el equipo no se vuelvan a poner por defecto, la manera de hacerlo es añadiendo al fichero /etc/sysctl.conf las dos siguiente lineas (En mi caso 2GB):
  • kernel.shmall = 524288
  • kernel.shmmax = 2147483648

 

Más información:

http://www.postgresql.org/docs/9.2/static/runtime-config-resource.html

Leave a Reply