Esta guiá es simplemente una recopilación de información recogida por Internet y pequeños cambios que favorecen el rendimiento de este magnifico motor de bases de datos.
Lo primero a tener en cuenta es que independientemente del sistema operativo donde tengamos instalado PostgreSQL las configuraciones referentes al rendimiento de las bases de datos las realizaremos en el archivo llamado postgres.conf
shared_buffers
Por defecto el valor es 1000
Lo ideal o recomendable es usar un 10% del valor total de memoria ram del sistema, copio pego un ejemplo realizado para 1GB de RAM
El 10% de 1 GB: (1048576 KB/10) = 104857 KB
shared_buffers: (104857 KB/8 KB) = 13107
Despues de realizar este cambio guardaremos y reiniciaremos la base de datos, si arranca sin problemas, realizaremos las pruebas necesarias para ver si el rendimiento ha mejorado, de lo contrario, revisaremos el log a ver porque no ha podido arrancar.
Una de las causas frecuentes es que nos pasemos esta tan bien explicado aqui que solo lo voy a copiar por si se perdiera el blog original.
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 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
CONFIGURACION DE EJEMPLO
# Valor máximo de conexiones permitidas
max_connections = 300
## Tamaño del buffer utilizado por postgresql
## para mantener data en cache representa el 25% de RAM
## Se debe alterar valor de SHMMAX
shared_buffers = 4096MB
##memoria temporal utilizada por cada sesión
##para las tablas temporarias y para apertura de tablas en cada sesión de cada BD
temp_buffers = 16MB
##Utilizada para las ordenes ORDEN BY, DISTINCT,
## joins, … Valor razonable 2-4% de la memoria
## Se colocó 3%
work_mem = 245MB
#Usada en operaciones del tipo VACUUM,
#ANALYZE, CREATE INDEX, …
maintenance_work_mem = 256MB
##Optimizar punto de chequeo WALL.
##Cada segmento es normalmente 16 MB.
## En este caso 64 * 16 = 1024MB punto de chequeo
checkpoint_segments = 64
##Optimizar la lectura de datos
## Valor razonable 50% de la memoria
effective_cache_size = 4096MB
En caso de llegar a las max_connection
##y se requiera entrar, se reserva para superusuario
superuser_reserved_connections = 3
OTRO EJEMPLO DE CONF
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
ENLACES
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server/es
https://pgtune.leopard.in.ua/#/
https://www.pgpool.net/mediawiki/index.php/Main_Page
http://pgcluu.darold.net/
ELementos de optimización (Tunning) PostgreSQL