Ejemplos de configuración de PostgreSQL

Recopilado por Prof. Josué Ramírez

Comprendiendo el buffer compartido

Inspeccionando la cache del buffer
PostgreSQL prove una extensión para ver lo que está en la cache del buffer.

1) Loguearse a psql
2) Crear bases de datos
CREATE DATABASE test;
CREATE DATABASE mydb;

3) Conectarse a la base de datos test y ejecutar:

CREATE EXTENSION pg_buffercache;
CREATE EXTENSION

Nota:
Cargar una extension practicamente equivale a ejecutar el script de la
extensión, copiándose así los objetos a nuestra base de datos. El
script podría crear nuevos objetos SQL como funciones, tipos de
datos y métodos para soportar operadores e índices.

4) Ir al directorio /usr/local/pgsql/share/extension
5) Ver el contenido del siguiente archivo: more pg_buffercache--[Link]
/* contrib/pg_buffercache/pg_buffercache--[Link] */
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_buffercache" to load this file. \quit
-- Register the function.
CREATE FUNCTION pg_buffercache_pages()
RETURNS SETOF RECORD
AS 'MODULE_PATHNAME', 'pg_buffercache_pages'
LANGUAGE C;

-- Create a view for convenient access.

CREATE VIEW pg_buffercache AS
SELECT P.* FROM pg_buffercache_pages() AS P
 (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid,
relforknumber int2, relblocknumber int8, isdirty bool, usagecount int2);

-- Don't want these to be available to public.

REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
REVOKE ALL ON pg_buffercache FROM PUBLIC;

6) Nos conectamos a la base de datos y observamos lo que está presente en la

cache del buffer
psql –U user -d test
user: nombre del usuario que desea usar para la conexión.

test=# SELECT DISTINCT reldatabase FROM pg_buffercache;

reldatabase
-------------
12896
0
24741

Lo anterior representan algunas partes de dos bases de datos en la cache

(ver comando \! oid2name) . Estas bases de datos son la base de datos test
(a la que estamos conectados) y la base de datos postgres. El registro con
0 representa buffers que no se están usando todavía:
test=# \! oid2name –U user
All databases:
Oid Database Name Tablespace
----------------------------------------------------
16440 mydb pg_default
12896 postgres pg_default
12891 template0 pg_default
1 template1 pg_default
24741 test pg_default

a) Relacionemos lo anterior con una vista para obtener una mejor panorámica:
SELECT
[Link],
count(*) AS buffers
FROM pg_class c
JOIN pg_buffercache b
ON [Link]=[Link]
INNER JOIN pg_database d
 ON ([Link]=[Link] AND [Link]=current_database())
GROUP BY [Link]
ORDER BY 2 DESC;

relname | buffers
-----------------------------------+---------
pg_operator 13
pg_depend_reference_index | 11
pg_depend |9

b) Modificamos ligeramente la vista anterior para no mostrar los objetos del

diccionario de datos (los que están precedidos del prefijo pg):

SELECT
[Link],
count(*) AS buffers
FROM pg_class c
JOIN pg_buffercache b
ON [Link]=[Link]
JOIN pg_database d
ON ([Link]=[Link] AND [Link]=current_database())
WHERE [Link] NOT LIKE 'pg%' GROUP BY [Link]
ORDER BY 2 DESC;

relname | buffers
---------+---------
(0 rows)

c) Ahora llenaremos el buffer con una tabla creada por el usuario. Primero nos
conectamos a la base de datos test, creamos la tabla y luego insertamos un
registro:

psql –U user -d test

CREATE TABLE emp(id serial, first_name varchar(50));

INSERT INTO emp(first_name) VALUES('Jayadeva');

SELECT * FROM emp;

id | first_name
----+------------
1 | Jayadeva
(1 row)
 Nota: La palabra “serial” se refiere un tipo entero autoincrementable. Al usar esta palabra
se creará automáticamente un generador de secuencia de números (SEQUENCE) y la columna
(id en emp) será llenada con esta secuencia.

d) Repetimos la consulta c) y chequeamos si el buffer contiene los objetos recien

creados (la tabla y su secuencia):

relname | buffers
------------+---------
emp_id_seq | 1
emp |1

e) Modificamos un poco la consulta c) para que nos indique el estatus del buffer:
SELECT
[Link],
[Link]
FROM pg_class c JOIN pg_buffercache b
ON [Link]=[Link]
JOIN pg_database d
ON ([Link]=[Link] AND [Link]=current_database())
WHERE [Link] not like 'pg%';

relname | isdirty
------------+---------
emp_id_seq | f
emp |f

f) Note que la bandera isdirty vale f (false). Ahora hagamos una UPDATE:
UPDATE emp SET first_name ='Newname';
UPDATE 1

g) Si repetimos la consulta f) obtenemos lo siguiente:

relname | isdirty
------------+-----------------
emp_id_seq |f
emp |t

h) La salida anterior nos dice que el buffer está sucio. Ahora forcemos un
checkpoint:
CHECKPOINT;
i) Si repetimos la consulta f):
 relname | isdirty
------------+---------
emp_id_seq | f
emp |f
(1 row)

Ahora el buffer ya no está sucio.

PostgreSQL opera con bloques

PostgreSQL siempre lee y escribe en bloques. Considere la tabla emp que tiene
sólo un registro. Los datos en este registro deberían aumentar un poco la
cantidad de byes de la tabla. Sin embargo, esta tabla consumirá 8k en el disco
porque PostgreSQL trabaja con bloques (o páginas) de 8k. A continuación
verifiquemos esto:

1) Nos aseguramos que nuestra table contiene sólo un registro:

SELECT * FROM emp;

id | first_name
----+------------
1 | Newname
(1 row)

2) Luego encontramos el nombre del archivo que representa a la tabla emp:

SELECT pg_relation_filepath('emp');

pg_relation_filepath
----------------------
base/24741/24742
(1 row)

3) Ahora chequeamos el tamaño del archivo:

\! ls -l /pgdata/9.5/base/24741/24742
-rw-------. 1 postgres postgres 8192 Nov 15 11:33 /pgdata/9.5/base/24741/24742

8192 bytes = 8K. So, a table with just one record

4) Tratamos de insertar algunos datos para ver qué sucede:

INSERT INTO emp(id , first_name) SELECT generate_series(1,5000000), 'A longer name ';
INSERT 0 5000000
 5) Después de ejecutar la sentencia anterior algunas veces, chequeamos el
tamaño de los archivos desde la consola:
# ls -lh 24742*
-rw-------. 1 postgres postgres 1.0G Nov 17 16:14 24742
-rw-------. 1 postgres postgres 42M Nov 17 16:14 24742.1
-rw-------. 1 postgres postgres 288K Nov 17 16:08 24742_fsm
-rw-------. 1 postgres postgres 16K Nov 17 16:06 24742_vm

De modo que tenemos directorios para las bases de datos y archivos para
las tablas. Dentro de los archivos, los datos son administrados en bloques.

WAL y el proceso de escritura del WAL

1) En el directorio pg_xlog, los segmentos WAL son de 16 MB de tamaño cada

uno:
$ cd /pgdata/9.5/pg_xlog

$ pwd
/pgdata/9.5/pg_xlog

$ ls -alrt
total 16396
drwx------. 2 postgres postgres 4096 Oct 13 13:23 archive_status
drwx------. 3 postgres postgres 4096 Oct 13 13:23 .
drwx------. 15 postgres postgres 4096 Nov 15 20:17 ..
-rw-------. 1 postgres postgres 16777216 Nov 15 20:17 000000010000000000000001

2) Podemos encontrar el segmento que PostgreSQL está escribiendo en este

momento al usar la función pg_current_ xlog_location:
a) $ psql –U postgres
psql (9.5.0)
Type "help" for help.

b) postgres=# SELECT pg_xlogfile_name(pg_current_xlog_location());

pg_xlogfile_name
--------------------------
000000010000000000000001
(1 row)

El nombre no es un conjunto de digitos y números al azar. Está compuesto

de tres partes de 8 caracteres cada una:
000000010000000000000001
Los digitos se clasifican de la siguiente forma:
• Los primeros 8 digitos idenfican la línea de tiempo.
• Los siguientes 8 digitos identifican el archivo xlog lógico.
• Los últimos 8 representan el archivo xlog físico (segmento).

Nota:
Cada segmento contiene bloques de 8K.
Por lo general, PostgreSQL cambia de un segmento al próximo
cuando se llena, es decir, los 16 MB son ocupados. Sin embargo, es
posible disparar el cambio.
Por ejemplo, pg_switch_xlog cambia al próximo archivo log de
transacción (si se ha registrado alguna actividad en el log),
permitiendo que el log actual sea archivado.
Si no ha habido ninguna actividad en el log de transacción desde el
último cambio del log de transacción, pg_switch_xlog no cambia al
próximo archivo de log y retorna la ubicación de inicio del archivo de
log de transacción actualmente en uso.

El proceso de autovacuum launcher

Veamos el efecto de vaciado usando un ejemplo:

1) Conectese a PostgreSQL y cree una base de datos

createdb –U user vac;

2) Obtener el directorio de esta base de datos usando el comando

oid2name
oid2name –U user | grep vac

340081 vac pg_default

3) cd /pgdata/9.5/base/340081

4) /psql –U user -d vac

5) Los siguientes commandos se ejecutan en el prompt de psql, estando
conectado a la base de datos vac:
a) \! pwd
/pgdata/9.3/base/340081

b) \! ls | head -5
12629
12629_fsm
12629_vm
12631
12631_fsm

c) CREATE TABLE myt(id integer);

CREATE TABLE

d) SELECT pg_relation_filepath('myt');
pg_relation_filepath
----------------------
base/340081/340088
(1 row)

e) \! ls -lt 34*
-rw------- 1 postgres postgres 0 Aug 18 11:19 340088

El archivo creado es 340088 y podemos verlo en el sistema de archivos:

SELECT pg_total_relation_size('myt');
pg_total_relation_size
------------------------
0
(1 row)

f) INSERT INTO myt SELECT generate_series (1,100000);

INSERT 0 100000

g) SELECT pg_total_relation_size('myt');
pg_total_relation_size
------------------------
3653632
(1 row)

h) vac=# \! ls -lt | head -5

total 10148
-rw------- 1 postgres postgres 3629056 Aug 18 11:23 340088
-rw------- 1 postgres postgres 24576 Aug 18 11:23 340088_fsm
-rw------- 1 postgres postgres 122880 Aug 18 11:22 12629
 -rw------- 1 postgres postgres 65536 Aug 18 11:22 12658
El archivo ha crecido de cero a 3629056 bytes, debido a los datos que
fueron insertados.

i) Luego borramos algunos datos de la tabla:

DELETE FROM myt WHERE id> 5 AND id <100000;

DELETE 99994

SELECT pg_total_relation_size('myt');
pg_total_relation_size
------------------------
3653632
(1 row)

j) VACUUM myt;
VACUUM

SELECT pg_total_relation_size('myt');
pg_total_relation_size
------------------------
3661824
(1 row)

Podemos apreciar que el tamaño de la tabla realmente no ha disminuido.

k) Ahora, insertaremos datos y verificaremos si el tamaño de la tabla

aumenta:

INSERT INTO myt SELECT generate_series (1,1000);

INSERT 0 1000

select pg_total_relation_size('myt');
pg_total_relation_size
------------------------
3661824

El tamaño de la tabla no ha aumentado, aunque hayamos insertado 1000

registros:
\! ls -lt 34*
-rw------- 1 postgres postgres 8192 Aug 18 11:25 340088_vm
-rw------- 1 postgres postgres 3629056 Aug 18 11:23 340088
-rw------- 1 postgres postgres 24576 Aug 18 11:23 340088_fsm

l) VACUUM FULL myt;

VACUUM
 SELECT pg_total_relation_size('myt');
pg_total_relation_size
------------------------
40960 (1 row)

m) El tamaño de la tabla ha disminuido considerablemente:

SELECT pg_relation_filepath('myt');
pg_relation_filepath
----------------------
base/340081/340091
\! ls -lt | head -5
total 6588
-rw------- 1 postgres postgres 0 Aug 18 11:29 340088
-rw------- 1 postgres postgres 40960 Aug 18 11:29 340091
-rw------- 1 postgres postgres 122880 Aug 18 11:28 12629
-rw------- 1 postgres postgres 32768 Aug 18 11:28 12634

El archivo de la tabla ha cambiado de 340088 a 340091. El comando

VACUUM FULL crea un archivo nuevo vacío en el cual copia todos los
registros que no están muertos y vacía el archivo original de la tabla.

VACUUM FULL adicionalmente a marcar el espacio como reutilizable, también

elimina los registros borrados o actualizados (los de respaldo) y reordena los datos en
la tabla.

El proceso de logging

1) Ubiquemonos en el directorio pgdata, editemos el archivo

[Link] y hagamos los siguientes cambios:
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_min_duration_statement = 0

2) pg_ctl restart
waiting for server to shut down.... done
server stopped
server starting
 3) ps f -U postgres
PID TTY STAT TIME COMMAND
2581 pts/2 S 0:00 -bash
3201 pts/2 R+ 0:00 \_ ps f -U postgres
2218 pts/1 S+ 0:00 -bash
3186 pts/2 S 0:00 /usr/local/pgsql/bin/postgres
3187 ? Ss 0:00 \_ postgres: logger process
3189 ? Ss 0:00 \_ postgres: checkpointer process
3190 ? Ss 0:00 \_ postgres: writer process
3191 ? Ss 0:00 \_ postgres: wal writer process
3192 ? Ss 0:00 \_ postgres: autovacuum launcher process
3193 ? Ss 0:00 \_ postgres: stats collector process

Auditar usando las etiquetas por defecto o usar la auditoria usando RAISE LOG:

1) Connect to test database and create a function:

CREATE OR REPLACE FUNCTION audit_tbl()

RETURNS trigger AS
$BODY$
DECLARE
aud_data text;
BEGIN
aud_data =NEW.first_name;
RAISE LOG 'Audit data : %', aud_data;
RETURN NEW;
END;$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER emp_trg

BEFORE INSERT
ON emp
FOR EACH ROW
EXECUTE PROCEDURE audit_tbl();

INSERT INTO emp (first_name) values ('Scott');

Ahora, el archivo de log tiene la siguiente entrada:

time=2013-11-17 12:26:35 IST:db=test;user=postgres type=INSERT LOG: Audit data : Scott
La variable aud_ data puede tener valores concatenados de diferentes valores de “NEW” y otras
descripciones que se deseen agregar.
Algunos otros parámetros que se pueden definir para el proceso de logging:
log_line_prefix = 'time=%t:db=%d;user=%u type=%i '

El proceso recolector de estadísticas (stats collector)

1) Conéctese como el usuario postgres
2) Para mostrar todas las tablas de estadísticas ejecute: \d pg_stat*
3) La lista de tablas mostradas serán:
pg_stat_activity pg_statio_all_sequences pg_statio_user_tables pg_stat_user_functions
pg_stat_all_indexes pg_statio_all_tables pg_statistic pg_stat_user_indexes
pg_stat_all_tables pg_statio_sys_indexes pg_statistic_relid_att_inh_index pg_stat_user_tables
pg_stat_bgwriter pg_statio_sys_sequences pg_stat_replication pg_stat_xact_all_tables
pg_stat_database pg_statio_sys_tables pg_stats pg_stat_xact_sys_tables
pg_stat_database_conflicts pg_statio_user_indexes pg_stat_sys_indexes pg_stat_xact_user_functions
pg_statio_all_indexes pg_statio_user_sequences pg_stat_sys_tables pg_stat_xact_user_tables

4) postgres=# \d+ pg_statio_user_tables

View "pg_catalog.pg_statio_user_tables"
Column | Type | Modifiers | Storage | Description
-----------------+--------+-----------+---------+-------------
relid | oid | | plain |
schemaname | name | | plain |
relname | name| | plain |
…

5) postgres=# \d+ pg_statio_all_tables

View "pg_catalog.pg_statio_all_tables"
Column | Type | Modifiers | Storage | Description
-----------------+--------+-----------+---------+-------------
relid | oid | | plain |
schemaname | name | | plain |
relname | name | | plain |
heap_blks_read | bigint | | plain |
…
tidx_blks_hit | bigint | | plain |

View definition:
SELECT [Link] AS relid,
[Link] AS schemaname,
 [Link],
pg_stat_get_blocks_fetched([Link]) –
…

6) Para ver las estadísticas de la tablas de los usuarios hagamos lo siguiente:

test=# SELECT relname, n_tup_ins FROM pg_stat_user_tables;
relname | n_tup_ins
---------+-----------
emp | 2
dept | 0
(2 rows)
El resultado indica que se han insertado dos tuplas en la tabla empleado.

7) Para resetear todos los contadores de estadísticas de la base de datos a cero ejecute:
test=# SELECT pg_stat_reset();
pg_stat_reset
---------------
(1 row)

test=# SELECT relname, n_tup_ins FROM pg_stat_user_tables;

relname | n_tup_ins
---------+-----------
emp | 0
dept | 0
(2 rows)
Todos los contadores han sido reiniciados a cero.

test=# INSERT INTO emp(first_name) VALUES ('Scottnew');

INSERT 0 1

test=# SELECT relname, n_tup_ins FROM pg_stat_user_tables;

relname | n_tup_ins
---------+-----------
emp | 1
dept | 0
(2 rows)
Ordenando en la memoria usando el parámetro work_mem
El valor de work_mem representa la cantidad de memoria usada de los buffer de memoria
compartida para operaciones internas de ordenamiento y tablas de hash antes de hacer el
intercambio a los archivos temporales en disco. Por lo tanto, si no se asigna suficiente memoria
esto ocasionará operaciones de E/S que degradarán el tiempo de respuesta. Por otro lado si se
aumenta mucho el valor de work_mem se terminará usando mucha memoria compartida para
el caso de multiples usuarios conectados haciendo operaciones de ordenamiento significativas.
La solución a esto es incrementar el valor de work_mem a nivel de la sesión de usuario en caso
de que las operaciones de ordenamiento vayan a generar una carga signifitiva. Veamos el
siguiente ejemplo:

1) grep work [Link]

#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB

2)
postgres=# CREATE TABLE myt (id serial);
postgres=# INSERT INTO myt select generate_series (1,1000000);
postgres=# SET work_mem = '64kB';
postgres=# SELECT temp_files, temp_bytes FROM pg_stat_database WHERE
datname = 'postgres';
temp_files | temp_bytes
------------+------------
9 | 284303360
(1 row)

postgres=# SELECT * FROM ( SELECT * FROM myt ORDER BY id ) t limit 1000;

id
-----
1

postgres=# SELECT temp_files, temp_bytes FROM pg_stat_database WHERE datname

= 'postgres';
temp_files | temp_bytes
------------+------------
10 | 312320000
Notemos que la operación de ordenamiento creó un archivo temporal por la escasa
memoria. Aumentemos la memoria a 1MB:

postgres=# SET work_mem to '1MB';

SET
 postgres=# SHOW work_mem;
work_mem
----------
1MB
(1 row)

postgres=# SELECT * FROM ( SELECT * FROM myt ORDER BY id ) t limit 1000;

id
-----
1
.....
postgres=# SELECT temp_files, temp_bytes FROM pg_stat_database WHERE datname =
'postgres';
temp_files | temp_bytes
------------+------------
10 | 312320000
(1 row)

En este último caso la operación de ordenamiento no generó más archivos

temporales.