Configuracion Postgres - Tiuning Parametros

postgresql.conf

Los cambios que realicemos en este fichero afectaran a todas las bases de datos que tengamos definidas en nuestro cluster PostgreSQL. La mayoria de los cambios se pueden poner en produccion con un simple 'reload' (/usr/local/bin/pg_ctl -D /var/pgsql/data reload), otros cambios necesitan que arranquemos de nuevo nuestro cluster (/usr/local/bin/pg_ctl -D /var/pgsql/data restart).

Mas informacion sobre todos los parametros que podemos cambiar en este fichero, que afectan y como se pueden poner en produccion se puede encontrar en la seccion 17. Server Configuration de la documentacion oficial de PostgreSQL.

A continuacion vamos a ver los parametros mas importantes que deberiamos cambiar si empezamos a usar PostgreSQL para un uso serio y si queremos sacarle el maximo partido a nuestra maquina. Existen muchos mas parametros que se pueden y con el tiempo se deberan de ajustar (algunos de los cuales fueron nombrados en la entrada anterior : Parametros Interesantes ), aqui nos vamos a centrar en los mas importantes y los cuales deberiamos cambiar antes de empezar a utilizar PostgreSQL de una manera seria.

RESUMEN

max_connections: Numero maximo de clientes conectados a la vez a nuestras bases de datos. Deberiamos de incrementar este valor en proporcion al numero de clientes concurrentes en nuestro cluster PostgreSQL. Un buen valor para empezar es el 100:

max_connections = 100

shared_buffers: Este parametro es importantisimo y define el tamaño del buffer de memoria utilizado por PostgreSQL. No por aumentar este valor mucho tendremos mejor respuesta. En un servidor dedicado podemos empezar con un 25% del total de nuestra memoria. Nunca mas de 1/3 (33%) del total. Por ejemplo, en un servidor con 4Gbytes de memoria, podemos usar 1024MB como valor inicial.

shared_buffers = 1024MB

work_mem: Usada en operaciones que contengan ORDER BY, DISTINCT, joins, .... En un servidor dedicado podemos usar un 2-4% del total de nuestra memoria si tenemos solamente unas pocas sesiones (clientes) grandes. Como valor inicial podemos usar 8 Mbytes.

work_mem = 8MB

maintenance_work_mem: Usada en operaciones del tipo VACUUM, ANALYZE, CREATE INDEX, ALTER TABLE, ADD FOREIGN KEY. Su valor dependera mucho del tamaño de nuestras bases de datos. Por ejemplo, en un servidor con 4Gbytes de memoria, podemos usar 256MB como valor inicial.

maintenance_work_mem = 256MB

effective_cache_size: Parametro usado por el 'query planner' de nuestro motor de bases de datos para optimizar la lectura de datos. En un servidor dedicado podemos empezar con un 50% del total de nuestra memoria. Como maximo unos 2/3 (66%) del total. Por ejemplo, en un servidor con 4Gbytes de memoria, podemos usar 2048MB como valor inicial.

effective_cache_size = 2048MB

checkpoint_segments: Este parametro es muy importante en bases de datos con numerosas operaciones de escritura (insert,update,delete). Para empezar podemos empezar con un valor de 64. En grandes databases con muchos Gbytes de datos escritos podemos aumentar este valor hasta 128-256.

checkpoint_segments = 64

CONFIGURACION DE ESTOS PARAMETROS

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.

Entos los pasos a seguir para agrandar Shared_bufer serian los siguientes :

1. Ver el tamaño de un segmento de memoria compartida es de 32MB( valor por defecto ). Podemos comprobarlo haciendo (el resultado es en nº de bytes):
$ cat /proc/sys/kernel/shmmax

2.Cambiar tamaño segmento de memoria compartida

• shmmax debe ser mayor q : Shared_bufer* 8192 a asignar
  
• Si la queremos cambiar temporalmente:
• sysctl -w kernel.shmmax=111766938
• lternativamente, 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

• Si lo queremos cambiarde forma permanente en /etc/sysctl.conf, de forma que los cambios se conserven entre arranques:
• kernel.shmmax=111766938
• o: echo "kernel.shmmax=2147483648" >> /etc/sysctl.conf .

3. Cambiar el parametro de postgres

-----------------------------------------------------------------------------------------------
REFERENCIAS:
http://www.juancarlosmoral.es/index.php/postgresql-hardware-tunning/
http://www.linux-es.org/node/660

Configuracion Postgres - Parametros Interesantes

onVamos a ver ahora algunos parámetros que de base serán interesantes, cada caso ha de ser estudiado luego aparte del resto, para encontrar una configuracion óptima para él.

• authentication_timeout :
  Tiempo máximo que esperaremos una autenticación correcta del usuario, el valor por defecto es 60 segundos. Si pasados esos 60 segundos el usuario no se autentica correctamente el server cierra la conexión. Un valor adecuado serian entre 15 y 30 segundos, de esa forma nos evitaríamos usuarios que establecen conexiones sin autenticarse y que ocupan conexiones que no podrán ser usadas por otros usuarios.
• client_min_messages, server_min_messages :
  Ambos indican el nivel de información que se sacará del server, el primero indica la cantidad de información que le es enviada a un cliente conectado al sistema mientras que el segundo indica la información que se va a enviar al log del sistema. Los posibles valores y sus implicaciones podemos verlos en el apartado http://www.postgresql.org/docs/7.3/static/runtime-config.html Run-time Configuration de la documentación de PostgreSQL.
• fsync :
  Con esta opcion = on, PostgreSQL llama a fsync() para asegurarse de que los datos son grabados a disco físicamente después de cada commit por transaccion. Esto tiene ventajas e inconvenientes ya que en caso de un fallo en el sistema, si la base de datos se cierra inapropiadamente y los datos se han guardado físicamente a disco no será necesaria una raparación de la base de datos, con lo que el rearranque del sistema se agiliza muchísimo, aparte de que la perdida de datos se reduce casi a 0.
  El incoveniente mas grande es que dependiendo del disco que tengamos, el proceso de fsync() puede ralentizar al sistema, ya que cada vez que se llame a fsync() ha de esperar a que se escriba en el disco. fsync() ha sido mejorada mucho a partir de las versiones 7.1 de pgsql, por lo que la diferencia de velocidad con la opcion activada o no puede no ser realmente ventajosa en un entorno en el que el sistema se reinicie con frequencia.
• geqo :
  O Generic Query Optimization, con esta opción activamos o desactivamos el algoritmo que utiliza PostgreSQL para optimizar las querys al sistema. El valor por defecto es on (activado) y es más que aconsejable dejarlo activado. De todas formas, disponemos de opciones relacionadas con geqo, de forma que se pueden optimizar determinados parametros.
  Una vez mas podemos encontrar mas informacion sobre estos parametros en el apartado http://www.postgresql.org/docs/7.3/static/runtime-config.html Run-time Configuration de la documentación de PostgreSQL.
• hostname_lookup :
  Si queremos que en lugar de la direccion ip desde la que se conecta el usuario, se loguee el nombre del host (si resuelve) deberiamos de poner a on esta opcion. Es bastante desaconsejable, ya que ralentiza bastante el sistema en general.
• lc_* :
  Establecidas como vimos en el articulo anterior en el momento de crear el database cluster (el directorio de trabajo de postgresql), podemos cambiarlas ahora si fuese necesario. De todas formas, y como ya hemos visto, es mejor crear el database cluster con las locales adecuadas y olvidarnos de esto.
• log_* :
  Con estos parámetros configuramos el log del server, aquí le decimos si queremos que loguee las conexiones de los usuarios (log_connections), que marque cada linea del log con el pid del proceso y la fecha (log_pid y log_timestamp respectivamente), y si queremos que loguee tambien las sentencias SQL ejecutadas en todo momento en el servidor (log_statement).
• max_expr_depth :
  Este parámetro establece el numero máximo de subsentencias que se va a poder alcanzar, por ejemplo subselects, como máximo. El valor por defecto es 10000, lo cual en principio y si no tenemos ninguna necesidad fuera de lo normal, es mas que suficiente.
• password_encryption:
  Si activamos esta opción, al hacer un create user with password, pero sin ponerle encrypted, nos encryptara el password de todas formas, evitando asi el tener passwords en plain text en el sistema. **
• silent_mode :
  Con esta opción activada, el servidor no suelta ningún tipo de mensaje ni por stderr ni por stdout. Esta opción es util si tenemos configurado PostgreSQL para loguear por syslog.
• ssl :
  Esta opción ha de estar activada para que podamos establecer conexiones encryptadas al servidor (la opcion hostssl de pg_hba.conf). Para poder activar esta opción, tendremos que generar un certificado y configurar un par de cosas. **
  
• statement_timeout :
  Con esto podemos definir un número máximo de segundos que una sentencia puede estar ejecutandose. Por defecto es 0, lo que la desactiva. Es una opción interesante en entornos en los que no nos interese que un usuario pueda ejecutar consultas demasiado largas que bloqueen recursos del servidor demasiado tiempo.
• stats_start_collector :
  Con esta opción, activamos o desactivamos el statistics collector de PostgreSQL, que sirve para monitorizar numerosos parámetros del servidor. En próximos articulos veremos como beneficiarse de estas estadísticas para ir afinando el servidor a nuestras necesidades. **
  
• syslog :
  Con esta opción establecemos el tipo de logueo que hará el server:
  • 0 - stdout/stderr solamente
  • 1 - loguea a syslog y por stdout/stderr
  • 2 - syslog, aunque algunos errores saldran a stderr a menos que activemos la opcion silent_mode que vimos antes.
• syslog_ident :
  El string con el que se identificará ante el syslog (por defecto postgres).
• unix_socket_* :
  Estos parámetros nos permiten definir en que directorio se va a crear el socket unix mediante el que se hacen las conexiones locales (unix_socket_directory), el grupo dueño de ese fichero (unix_socket_group), y los permisos con los que será creado el fichero (unix_socket_permissions), por defecto 511 (usuario lectura y ejecucion, grupo y otros lectura).
• virtual_host :
  Con este parámetro podemos definir para que dirección ip o nombre de host escuchará el PostgreSQL peticiones por tcp/ip, por defecto escucha en todas las interfaces de las que disponga la maquina.
• max_connections y shared_buffers :
  Estas dos las he dejado para el final por que son bastante especiales, además de ser dependientes una de la otra. Para cada valor de max_connections (que indica el numero maximo de conexiones simultaneas que manejara el servidor) tenemos que tener al menos el doble para shared_buffers (buffers de memoria utilizados por el servidor).
  A la hora de establecer estas opciones, tendremos que tener en cuenta el uso de SYSV semaphores del PostgreSQL; es un tema espinoso, ya que dependiendo de como se encuentren configurados determinados parámetros del kernel de nuestro servidor, veremos que se nos permite un determinado máximo en max_connections o no. Para más información sobre esto, podemos recurrir al apartado http://www.postgresql.org/docs/7.3/static/kernel-resources.html Managing Kernel Resources de la documentación de PostgreSQL.

info sacada de : http://wiki.e-shell.org/PostgreSQL7XOptimizationEs

Configuracion Postgres - pg_hba.conf

pg_hba.conf

Este fichero se utiliza para definir como, donde y desde que sitio un usuario puede utilizar nuestro cluster PostgreSQL. Todas las lineas que empiezen con el caracter # se interpretan como comentarios. El resto debe de tener el siguiente formato:

[Tipo de conexion][database][usuario][IP][Netmask][Tipo de autentificacion][opciones]

Dependiendo del tipo de conexion y del tipo de autentificacion, [IP],[Netmask] y [opciones] pueden ser opcionales. Vamos a explicar un poco como definir las reglas de acceso. El tipo de conexion puede tener los siguientes valores, local, host, hostssl y hostnossl. El tipo de metodo puede tener los siguientes valores, trust, reject, md5, crypt, password, krb5, ident, pam o ldap

Una serie de ejemplos nos ayudaran a comprender mejor como podemos configurar diferentes accesos al cluster PostgreSQL.

Ejemplo 1 .- Acceso por tcp/ip (red) a la base de datos test001, como usuario test desde el ordenador con IP 10.0.0.100, y metodo de autentificacion md5:

host    test001   test  10.0.0.100 255.255.255.255   md5

Esta misma entrada se podria escribir tambien con la mascara de red en notacion CIDR:

host    test001   test  10.0.0.100/32  md5

Ejemplo 2 .- Acceso por tcp/ip (red) a la base de datos test001, como usuario test desde todos los ordenadores de la red 10.0.0.0, con mascara de red 255.255.255.0 (254 ordenadores en total) y metodo de autentificacion md5:

host    test001   test  10.0.0.0 255.255.255.0   md5

Esta misma entrada se podria escribir tambien con la mascara de red en notacion CIDR:

host    test001   test  10.0.0.0/24  md5

Ejemplo 3 .- Acceso por tcp/ip (red), encriptado, a todas las bases de datos de nuestro cluster, como usuario test desde el ordenador con IP 10.0.0.100, y el ordenador 10.1.1.100 y metodo de autentificacion md5 (necesitamos dos entradas en nuestro fichero pg_hba.conf:

hostssl    all   test  10.0.0.100 255.255.255.255   md5
hostssl    all   test  10.1.1.100 255.255.255.255   md5

Ejemplo 4.- Denegar el acceso a todos las bases de datos de nuestro cluster al usuario test, desde todos los ordenadores de la red 10.0.0.0/24 y dar accesso al resto del mundo con el metodo md5:

host       all   test  10.0.0.0/24   reject
host       all   all  0.0.0.0/0     md5

Asi podriamos seguir jugando con todas las posibilidades que nos brinda este fichero de configuracion. Por supuesto que las bases de datos y usuarios usados en este fichero tienen que existir en nuestro cluster para que todo funcione y algunos de los parametros solo se pueden usar si hemos compilado con las opciones pertinentes en el proceso de instalacion (por ejemplo, hostssl, pam, krb5)

Para poder en produccion los cambios en este fichero tendremos que decirle a PostgreSQL que vuelva a leerlo. Basta con un simple 'reload' (/usr/local/bin/pg_ctl -D /var/pgsql/data reload) desde la linea de comandos o con la funcion pg_reload_conf() como usuario postgres desde psql, el cliente PostgreSQL.

[postgres@servidor]# /usr/local/bin/psql

Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
     \h for help with SQL commands
     \? for help with psql commands
     \g or terminate with semicolon to execute query
     \q to quit

postgres=# SELECT pg_reload_conf();

pg_reload_conf
----------------
t
(1 row)

postgres=#

Para una documentacion detallada sobre el fichero pg_hba.con, pasaros por la seccion Chapter 20. Client Authentication de la documentacion oficial de PostgreSQL.

Configuracion Postgres - Inicio

No es la primera vez que algun asuario protesta o esta super preocupado de lo mal y lo lento que funciona su cluster de base de datos PostgreSQL en un servidor ultimo modelo con muchisima memoria. Normalmente el problema es que PostgreSQL no ha sido configurado para trabajar con el volumen de datos y usuarios con el que lo estamos usando. No es una gran ayuda tener un servidor con varios GBytes de memoria RAM si le hemos dicho a PostgreSQL, por ejemplo, que no utilice más de 32MBytes.

Tambien tenemos que decir que cualquier base de datos que se este usando activamente, no solo PostgreSQL, es un elemento dinamico y vivo en el que estamos cambiando los datos constantemente y donde el tamaño de los datos almacenados suele ir creciendo con el tiempo. Esto significa que una configuracion que funcione bien con ciertos valores hoy, puede que no funcione tan bien despues de un año de uso y que necesite ajustarse para que funcione optimalmente.

Configuración

El comportamiento de PostgreSQL en nuestro sistema se puede controlar con tres ficheros de configuración que se encuentran en el directorio de datos donde inicializamos nuestro cluster PostgreSQL (En nuestro caso /var/pgsql/data). Estos tres ficheros son:

• pg_hba.conf: Este fichero se utiliza para definir los diferentes tipos de accesos que un usuario tiene en el cluster.
• pg_ident.conf: Este fichero se utiliza para definir la información necesaria en el caso que utilicemos un acceso del tipo ident en pg_hba.conf .
• postgresql.conf: En este fichero podemos cambiar todos los parametros de configuracion que afectan al funcionamiento y al comportamiento de PostgreSQL en nuestra maquina.

En otra entrada de este blog explicare los cambios mas importantes que podemos hacer en algunos de estos ficheros.

Veremos que hay algunas opciones que podremos cambiar on-the-fly con simples sentencias SQL, y veremos que hay otras configuraciones que requieren que le enviemos una señal SIGHUP al servidor. Las opciones que requieren que reiniciemos el sistema las vamos a establecer en el fichero $PGDATA/postgresql.conf, siendo $PGDATA la ruta al directorio donde se almacena la info de la base de datos.

Cambios dinámicos - pg_settings

Mediante la vista pg_settings, tenemos acceso a varios parámetros de configuración interesantes de PostgreSQL, algunos de ellos podremos incluso cambiarlos on-the-fly con el server corriendo.

La vista pg_settings se compone de dos campos, name y settings. El primero es el nombre del parámetro, mietras el segundo es el valor que va a tener el parámetro.

select * from pg_settings;

Para una explicacion detallada de todas las opciones cambiables dinámicamente y lo que hace cada una, lo mejor es echarle un ojo a la documentacion del administrador de bases de datos PostgreSQL, en el apartado http://www.postgresql.org/docs/7.3/static/runtime.html Server Run-time Environment. Tambien pueden ver esta entrada donde se explican los mas comunes:

http://postgresqlnovatos.blogspot.com/2008/02/configuracion-postgres-parametros.html

Esto es aplicable para casi todos los parámetros de configuración, lo que nos permite cambiar al momento determinados parámetros dependiendo del uso que el PostgreSQL esté haciendo del servidor.
De todas formas, veremos ahora que hay parametros como max_connections, que no permiten cambios con el servidor corriendo.

Cambios estáticos - postgresql.conf y parámetros de postmaster

postgresql.conf

En algunos casos, los parámetros de configuración del PostgreSQL no pueden cambiarse haciendo un update en la vista pg_settings, si no que hemos de cambiarlo en el fichero postgresql.conf, y lanzarle un SIGHUP al servidor, para reiniciarlo y que releea los ficheros de configuración.
El fichero postgresql.conf tiene un formato sencillo, de la forma:

nombre = valor

Siguiendo el mismo estilo que en la vista pg_settings. El archivo es como cualquier fichero de configuracion de unix, o shell script, de forma que todo lo que aparezca en una linea a partir del caracter # es considerado un comentario.
Un ejemplo de valor que debemos de establecer en el fichero postgresql.conf es el max_connections, que tiene un valor por defecto de 32 conexiones:

max_connections = 32

Podriamos cambiarlo facilmente al numero que quisieramos (Con algunas limitaciones como veremos más adelante):

max_connections = 1024

Una vez cambiado el parámetro, tendríamos que enviarle un SIGHUP al server, o sea podriamos hacer en una consola de linux:

services postgresql restart