BLOQUEOS DE SEQUENCIA

como bloquear sequencias

DECLARE
id INTEGER ;
oidseq integer;

BEGIN

select c.oid::integer
into oidseq
from pg_class c
where relname = secuencia;

perform pg_advisory_lock(oidseq);

select into id nextval(secuencia);

execute 'ALTER SEQUENCE ' || secuencia || ' RESTART ' || (id + cantidad)::text;

perform pg_advisory_unlock(oidseq);

return id;
end;


Nota:
* Tiene que estar creado el lenguage plpgsql: create language plpgsql
* Tener en cuenta que el usuario que largue la funcion almacenada tiene que ser ownere de la sequencia
* Probar procedimiento: select get_rango_id_tabla('"boleta_id_seq"',1);

------------------------------------------------------------------
referencia:
http://www.depesz.com/index.php/2008/03/20/getting-multiple-values-from-sequences/

------------------------------------------------------------------

Activar Log

Errores / Seguimiento

Para activar el log os dos parámetros que hay que setear son

redirect_stderr = on

log_min_error_statement = error

se tiene que hacer en el archivo postgresql.conf y reiniciar el servidor

Consultar parámetros relacionados

select name, setting from pg_catalog.pg_settings s

where s.name in (

'log_destination',

'redirect_stderr',

'log_directory',

'log_filename',

'log_truncate_on_rotation',

'log_rotation_age',

'log_rotation_size',

'syslog_facility',

'syslog_ident',

'client_min_messages',

'log_min_messages',

'log_error_verbosity',

'log_min_error_statement',

'log_min_duration_statement',

'silent_mode'

)

Lugar de seguimiento

log_destination = 'stderr' existen varios métodos para emitir los mensajes del servidor (stderr, syslog y eventlog)

fichero de configuración

redirect_stderr = on permite enviar los errores enviados a stderr a los ficheros de seguimiento

puesta en marcha del servidor

log_directory = 'pg_log' si el parámetro anterior está habilitado determina el directorio donde se crean los ficheros de seguimiento

puesta en marcha del servidor

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' si redirect_stderr = on, nombre de los ficheros de seguimiento

log_truncate_on_rotation = off establece la rotación de ficheros, aunque se pueden usar alternativas

log_rotation_age = 1440 si redirect_stderr = on establece la duración máxima de cada fichero de seguimiento, con 0 deshabilita esta opción

log_rotation_size = 10240 tamaño de los ficheros rotados

syslog_facility = 'LOCAL0' si el seguimiento lo hace syslog, aquí se determina qué utilidad se usa

syslog_ident = 'postgres' si se usa syslog este es el nombre del programa utilizado para identificar los

mensajes de PostgreSQL.

Cuándo efectuar el seguimiento

en algunas opciones, se establecen níveles de mensaje, que en el propio fichero de configuración están los posibles valores,

elegir un determinado nivel incluye todos los niveles que se encuentran por debajo. Los niveles que existen son:

 DEBUG[1-5]: información para desarrolladores.

 INFO: información implícita sobre operaciones del usuario, por ej. durante VACUUM VERBOSE.

 NOTICE: información que puede ayudar a los usuarios, por ej., en la creación de índices como parte de la clave

primaria.

 WARNING: avisos al usuario, caso de un commit fuera de un bloque de

transacción.

 ERROR: informa de un error que ha causado que aborte un comando.

 LOG: información interesante para los administradores, por ej, la actividad de los puntos de verificación

(checkpoints).

 FATAL: errores que han producido que aborte la sesión.

 PANIC: errores que han producido que aborten todas las sesiones.

client_min_messages = notice establece el nivel de los mensajes que serán enviados a los clientes puesta en marcha del servidor

log_min_messages = notice controla el nivel de los mensajes que son escritos en el fichero de seguimiento puesta en marcha del servidor

log_error_verbosity = default controla el detalle de la información que se escribe en el fichero de seguimiento (terse, default, verbose), cada nivel añade más campos

log_min_error_statement = panic controla si la instrucción SQL que ha provocado el error debe ser recordada o no el el fichero de seguimiento. En caso de querer hacer un seguimiento, conviene cambiarla a ERROR. puesta en marcha del servidor

log_min_duration_statement = -1 registra las instrucciones y su duración si su ejecución tarda más que el indicado aquí.

Con 0 registra todas y con -1 ninguna

puesta en marcha del servidor

silent_mode = off la salida estándar y los errores se envían a /dev/null

6	81
name setting client_min_messages notice log_destination stderr log_directory pg_log log_error_verbosity default log_filename postgresql-%a.log log_min_duration_statement -1 log_min_error_statement panic log_min_messages debug log_rotation_age 1440 log_rotation_size 0 log_truncate_on_rotation on redirect_stderr off silent_mode off syslog_facility LOCAL0 syslog_ident postgres	name setting client_min_messages notice log_destination stderr log_directory pg_log log_error_verbosity default log_filename postgresql-%a.log log_min_duration_statement -1 log_min_error_statement error log_min_messages notice log_rotation_age 1440 log_rotation_size 0 log_truncate_on_rotation on redirect_stderr on silent_mode off syslog_facility LOCAL0 syslog_ident postgres

Estilo de Fecha y juego de caracteres

Cambio del juego de caracteres:

- para ver que juego de caracteres tiene seteado el servidor y el cliente fijarse los parametros server_encoding y client_encoding con la siguiente consulta:

select * from pg_catalog.pg_settings swhere s.name in ('client_encoding','server_encoding','lc_collate',
'lc_ctype',
'lc_messages',
'lc_messages',
'lc_numeric',
'lc_monetary',
'lc_numeric',
'lc_time')

- El juego de caracteres del servidor no se puede cambiar, se setea cuando creamos el cruster para las base de datos con initdb
-El juego de caracteres del cliente , se puede cambiar para un usuario o para la sesion :
- usuario:
alter user POSTGRES SET client_encoding to LATIN1;
- sesion:
SET client_encoding to LATIN1;

Cambio del estilo de las fechas (datestyle):
-Cambio del datestyle del servidor :
- del archivo postgresql.conf cambiar el parametro datestyle
- Reiniciar el servidor
-ver el datestyle del usuario :
SHOW DATESTYLE;
-Cambiar el dateStyle del usuario:
alter user test SET DATESTYLE to 'iso,dmy';
alter user postgres SET DATESTYLE to 'iso,dmy';

- Problema que me he encontrado:
tenia una aplicacion desarrollada en java que no me tomaba el datestyle del usuario, me tomaba derecho el del motor. Pero tenia otra en delfi que si me lo tomaba.

Funciones de formato de fecha:
http://es.tldp.org/Postgresql-es/web/navegable/user/x2569.html

Monitoreo

Para ver el estado del servidor, desde una consola, puedes utilizar:

- top (supongo que ya lo conoces). Podrás ver la carga de CPU,
memòria, procesos ...
- vmstat 1 : Información de memòria, bloques i/o, cpu, ...
- iostat -k 1 : Información i/o a disco. (apt-get install sysstat).

Como herramienta gràfica tienes gkrellm (apt-get install gkrellmd
gkrellm). gkrell te permite visualizar gráficas de CPU, i/o a disco,
tráfico ethernet, ... Nosotros lo utilizamos y con él rápidamente
vemos un estado general del servidor.

Si te interessa saber que está haciendo postgres tienes (creo que esto
no lo preguntas pero ya que estamos):

- pgAdmin III : Puedes ver la conexiones y que está haciendo cada una de ellas.
- Mirate los capítulos 23 i 24 (version 7.4, supongo que en la 8 seran
los mismos capítulos)
- También tienes estos querys:


OPEN LOCKS:

SELECT r.relname, l."mode", count(*) AS numlocks
FROM pg_locks l, pg_class r
WHERE r.oid = l.relation
GROUP BY r.relname, l."mode"
ORDER BY count(*) DESC;


RUNNING QUERIES (AND HOW LONG FOR):

SELECT pg_stat_activity.usename, round(date_part('epoch'::text, now() -

pg_stat_activity.query_start)) AS duration,

pg_stat_activity.current_query
FROM pg_stat_activity
ORDER BY round(date_part('epoch'::text, now() -

pg_stat_activity.query_start)) DESC;


QUERY PLAN:

SELECT id, gameid, forumid, subject
FROM threads WHERE nuked = 0
ORDER BY nuked DESC, lastpost DESC LIMIT 8;

Cómo matar un proceso en PostgreSQL de forma segura

Bienvenido(a) a DbRunas
lunes, abril 28 2008 @ 10:59 EDT

Cómo matar un proceso en PostgreSQL de forma segura

PostgreSQLSi algún proceso se queda colgado (aunque no sea debido directamente a postgres) se puede cancelar sin causar males mayores al postmaster. Primero averiguamos el id del proceso gestionando nuestra query y luego le mandamos un SIGTERM (-15):


psql template1
template1=# select procpid, datname, usename, waiting from pg_stat_activity ;
procpid | datname | usename | waiting
---------+----------+----------+---------
27702 | template1 | postgres | f
27710 | template1 | postgres | f
27756 | template1 | postgres | f
27720 | template1 | postgres | t
(4 rows)
q
su -
kill -15 27720

Fuente: http://s1lence.net

tunning sql

EXISTS vs DISTINCT

Departamentos que tengan empleado:

 EXISTS es una alternativa mejor, porque cuando el RDMS se da cuenta de que cuando la
subquery se cumple, no necesita seguir, y sigue con la siguiente fila.
La elección de una opción u otra, depende de la cardinalidad de los datos.
Otra diferencia entre las dos opciones radica en que en el DISTINCT la BD debe
realizar una ordenación.

IN / UNION vs OR

 IN / UNION en la mayoría de casos es preferible a OR.
Usar OR en una fila indexada causa que el optimizador haga un FAST FULL SCAN.

NOT EXIST vs NOT IN

 En un Subquery NOT IN causa una ordenación.
Reemplazarlo por un NOT EXIST puede ser mejor.

Reduciendo accesos a la BBDD

Table Joins vs EXISTS

 Join es más recomendable que Exists siempre que el número de
filas coicidentes sea alto. Por ejemplo:

 Exists es más eficiente si el número de filas coincidentes es bajo:
Por ejemplo:

------------------------------------------------------------------------------
------------------------------------------------------------------------------
--fuente: http://www.wikioracle.es/doku.php?id=oracle_tuning:tuning_sql--
------------------------------------------------------------------------------
------------------------------------------------------------------------------
http://developer.postgresql.org/pgdocs/postgres/performance-tips.html#USING-EXPLAIN
------------------------------------------------------------------------------
------------------------------------------------------------------------------

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