ELementos de optimización (Tunning) PostgreSQL

EL presente instructivo tiene como finalidad configurar algunos elementos que optimizarán el funcionamiento de PostgreSQL en nuetro servidor.

Requisitos:

  • Se debe contar con suficiente privilegios para la configuración de aplicativos

Plataforma:

  • Equipos de arquitectura 64 bits
  • Sistema operativo ‘GNU/Linux’ Debian versión 6.0 (actualmente estable)

Aplicaciones:

  • PostgreSQL Versión 9.1

Nota: Parámetros establecidos con la finalidad de lograr un mejor desempeño de postgresql.
Las siguientes configuraciones se encuentran ajustadas para un equipo con 8GB de RAM

Configuración

1. Editar archivo de configuración de postgreSQL

root@server:/~# nano /etc/postgresql/9.1/main/postgresql.conf


2. Efectuar los siguientes cambios:

    # 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

Nota Importante(PostgreSQL versión 9.5):
Para postgreSQL versión 9.5 fue eliminada la variable de configuración “checkpoint_segments”, correspondiente al manejo del punto de chequeo de los wal y fue sustituida por “max_wal_size” y “min_wal_size”

  #tamaño máximo de Wal
  max_wal_size=XX
  #tamaño minimo Wal
  min_wal_size=XX
  
  #Punto de control o Checkpoint WAL
  checkpoint_timeout=XX

  #
  checkpoint_completion_target=XX

Release PostgreSQL 9.5 Date: 2016-01-07


Las siguientes opciones son de tipo “auditoras” y es solo para verificar el tiempo de duración de algunas sentencias. Se requiere registrar en el log las sentencias SQL de tipo Ddl (comandos de definición de datos, como CREATE, ALTER, DROP, ..) que tengan una duración mayor a los 10 seg

    ## tiempo de duracion de la setencias ddl
    ## para registrar los logs 
    ## 10000 milisengundos equivale a 10 segundos
    log_min_duration_statement = 10000

    ## Permite grabar algunas variable al registro de bitacoras

    log_line_prefix = '%t %r %u %d %p '
    
    # Donde :
    # - %u = nombre usuario
    # - %d = nombre base de datos
    # - %r = host remoto y puerto de conexión 
    # - %p = Número de proceso ID
    # - %t = timestamp without milliseconds

    ## activar el registro de log para las
    ## sentencias de tipo ddl
    log_statement = 'ddl'

3. Reiniciar el servicio:

root@server:/~# /etc/init.d/postgresql restart

Configuración Memoria Compartida del Kernel:

1. Calcular los valores requeridos. Con un valor de 4096MB en shared_buffers se requiere:

shmmax = (4096MB * 1024 * 1024) = 4294967296 bytes

2. Editar en archivo sysctl.conf:

 root@server:/~# nano /etc/sysctl.conf

Efectuar los siguientes cambios:

    ## tamaño maximo de memoria compartida
    kernel.shmmax=4294967296
 

3. Incluir los cambios en “caliente” al Kernel

root@server:/~# sysctl -p /etc/sysctl.conf

4. Revisar los cambios:

root@server:/~# cat /proc/sys/kernel/shmmax
   
    4294967296

Configuración modificar el tiempo y reciclar conexiones Time_wait en Linux

TIME-WAIT – representa el tiempo de espera para asegurarse de que el TCP remoto recibió el acuse de recibo de su petición de terminación de conexión.

1. Comprobar el tiempo por defecto

root@server:/~# sysctl net.ipv4.tcp_fin_timeout
net.ipv4.tcp_fin_timeout = 60

2. Reducir el tiempo a 30 segundos, para ello editar archvio “/etc/sysctl.conf”

root@server:/~# nano /etc/sysctl.conf

Efectuar el siguiente cambio:

net.ipv4.tcp_fin_timeout=30

3. Activar opción para “Reciclar” conexiones, para ello editar archivo “/etc/sysctl.conf”

root@server:/~# nano /etc/sysctl.conf

Efectuar los siguiente cambios:

 net.ipv4.tcp_tw_reuse = 1
 net.ipv4.tcp_tw_recycle = 1
 

4. Incluir los cambios en “caliente” al Kernel:

root@server:/~# sysctl -p /etc/sysctl.conf

5. Comprobar los cambios:

root@server:/~# sysctl net.ipv4.tcp_fin_timeout
root@server:/~# sysctl net.ipv4.tcp_tw_reuse
root@server:/~# sysctl net.ipv4.tcp_tw_recycle

Opciones File System Linux EXT4:

noatime:
Desahibilita que el sistema guarde el registro del ultimo acceso a los ficheros, postgre se encarga de realizar esa labor.

barrier=0:
Deshabilita el uso de barreras de escritura, las barreras de escritura fuerzan el uso de ordenamiento on-disk de los commits al journal, haciendo las caché de disco seguras de usar, pero un daño en el performance del disco.

Data=writeback
Con esto los datos serán escrito al disco sólo si se guardaron en el journal.

discard:
Por defecto ext4 realiza una operación llamada trim, para aquellos bloques que son liberados, con esta opción lo que haremos es decirle que únicamente los marque como libres y no realice la limpieza.

Opciones Fstab

/dev/sda1 /opt/db/postgresql ext4 rw,noatime,barrier=0,data=writeback,discard   0	0

Seguridad:
Permitir sólo conexiones IPV4 (desactivar IPV6)

1. Editar archivo configuración postgresql:

root@server:/~# nano /etc/postgresql/9.1/main/postgresql.conf

Efectuar los siguientes cambios:

 listen_addresses = '0.0.0.0'
 

Nota: La entrada 0.0.0.0 permite escuchar todas las direcciones IPv4

2. Editar el archivo de menejo de conexiones de postgresql:

root@server:/~# nano /etc/postgresql/9.1/main/pg_hba.conf

Comentar la siguiente línea:

 #IPv6 local connections:
 #host    all             all             ::1/128                 md5listen_addresses = '0.0.0.0'
 

3. Reiniciar el servicio:

root@server:/~# /etc/init.d/postgresql restart

Enlaces:
Tuning Your PostgreSQL Server/es
postgresql-una-instalacion-de-postgresql-basica-pero-mejor
Configuration calculator for PostgreSQL
PostgreSQL Configuration Tool 2.0 beta
Resumen – Instalación de PostgreSQL 9.2 optimizada para sistemas de producción
TRANSMISSION CONTROL PROTOCOL
Maximum segment lifetime
Reduce TIME_WAIT socket connections

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s