148607138 clases postgresql

Introduccion a PostgreSQL

Ing. Pedro Munoz del Rio

[email protected]

Lima, Peru

Introduccion

PostgreSQL es un gestor de bases de datos relacionales que empezo como un proyecto de la univer-sidad de Berkeley en California y su historia data desde los comienzos de las bases relacionales.

Posee funcionalidades de corte empresarial como funciones para crear vistas, funciones agregadaspara aplicar sobre las vistas entre otras. Tiene la capacidad de aceptar grandes cantidades de datos,como tablas con decenas de millones de registros sin mayor problema.

Ademas de un gestor de bases de datos, PostgreSQL es una plataforma para aplicaciones y permiteescribir procedimientos almacenados y funciones en diversos lenguajes como SQL, Python y Javaentre otros, agregando los modulos necesarios en el caso de los ultimos.

Conectarse a un web service a traves de Python, usar funciones estadsticas de R y consultar losresultados con SQL es posible mediante PostgreSQL.

Ademas, se pueden definir tipos propios de datos, instalar facilmente extensiones mediante una solainstruccion SQL y administrarlas con sencillez.

Y como si fuera poco, Postgresql es multiplataforma, por lo cual puede ser implementado tanto enLinux, BSD como Windows e incluso Mac OS, con binarios disponibles si no se desea compilar post-gresql.

Los lenguajes de programacion mas populares tienen libreras que les permiten comunicarse con Post-gresql y hacer consultas, entre los mas conocidos PHP, Python, Ruby, Java, .Net, Perl entre otros.

Hoy en da, PostgreSQL es una de las alternativas mas relevantes en el campo de las Bases de Datosrelacionales, de codigo abierto y utilizada por empresas de todo tamano a nivel mundial.

i

Indice

Introduccion I

1. Arquitectura del Sistema 11.1. Breve historia . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11.2. Quienes utilizan PostgreSQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1.2.1. Internacionales . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11.3. Resumen de la Arquitectura . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

1.3.1. Gestion de la data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.3.2. El procesamiento de una consulta . . . . . . . . . . . . . . . . . . . . . . . . . 5

1.4. Multi-Version Concurrency Control (MVCC) . . . . . . . . . . . . . . . . . . . . . . . 51.5. Write-Ahead Logging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61.6. Objetos mas utilizados en PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . 61.7. Nuevas funcionalidades en PostgreSQL 9.2 . . . . . . . . . . . . . . . . . . . . . . . . 71.8. Limitaciones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71.9. Instalacion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

2. Administracion 112.1. Configuracion de Postgresql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112.2. Creando Bases de Datos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

2.2.1. Comando createdb . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152.2.2. Creando una base de datos plantilla . . . . . . . . . . . . . . . . . . . . . . . . 162.2.3. Eliminar una Base de Datos . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

2.3. Creando Esquemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182.3.1. Permisos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212.3.2. Eliminar esquemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

2.4. Ruta de Busqueda de Esquemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252.4.1. Utilizacion de los esquemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

2.5. Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272.5.1. Crear un usuario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292.5.2. Atributos de los roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

2.6. Grupos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322.7. Control de Acceso . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

3. Transacciones y concurrencia 383.1. Definicion de Transaccion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

3.1.1. Savepoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403.2. Niveles de Aislamiento de las Transacciones . . . . . . . . . . . . . . . . . . . . . . . 41

3.2.1. Detalle de los niveles de aislamiento . . . . . . . . . . . . . . . . . . . . . . . . 41

ii

3.3. Descripcion del Control de Concurrencia . . . . . . . . . . . . . . . . . . . . . . . . . 503.4. Diferencias entre MVCC y Bloqueo . . . . . . . . . . . . . . . . . . . . . . . . . . . . 503.5. Ejemplo MVCC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

3.5.1. UPDATES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513.5.2. DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

4. Ajustes de rendimiento 544.1. Configuracion de Hardware . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

4.1.1. CPU . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554.1.2. Memoria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554.1.3. Discos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

4.2. Configuracion del S.O. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 564.2.1. Hdparm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 564.2.2. Sistemas de archivos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 564.2.3. Journaling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 564.2.4. Sistemas de archivos de Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . 574.2.5. Configuracion del sistema de archivos en Linux . . . . . . . . . . . . . . . . . 574.2.6. BSD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

4.3. Memoria para el cache de la Base de Datos . . . . . . . . . . . . . . . . . . . . . . . . 594.3.1. Unidades de memoria en postgresql.conf . . . . . . . . . . . . . . . . . . . . . 594.3.2. Cache de la Base de Datos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

4.4. Configuracion (postgresql.conf) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614.4.1. postgresql.conf . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624.4.2. Configuracion de logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

4.5. Configuracion de un nuevo servidor . . . . . . . . . . . . . . . . . . . . . . . . . . . . 684.5.1. Efectos del cache de la BD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

4.6. Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

5. Ajustes de rendimiento II: Optimizacion de las Bases de datos 715.1. Data de ejemplo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 715.2. Timing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 725.3. Explain y Explain Analyze . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 735.4. Las consultas y la cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

5.4.1. Efecto de la cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 745.4.2. Estructura del plan de la consulta . . . . . . . . . . . . . . . . . . . . . . . . . 755.4.3. Costo de computacion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

5.5. Optimizacion de consultas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 795.5.1. Armando conjuntos de registros . . . . . . . . . . . . . . . . . . . . . . . . . . 795.5.2. Procesando los nodos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 805.5.3. Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 835.5.4. Ordenamiento de Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 875.5.5. Tips para optimizar SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91

5.6. Parametros para el planeamiento de consultas . . . . . . . . . . . . . . . . . . . . . . 1035.6.1. effective cache size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1035.6.2. work mem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1035.6.3. Optimizando las consultas para la data en cache . . . . . . . . . . . . . . . . . 103

5.7. Comprobar la equivalencia de un query . . . . . . . . . . . . . . . . . . . . . . . . . . 1045.8. EXPLAIN ANALYZE grafico . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105

iii

6. Alta disponibilidad y replicacion 1076.1. Replicacion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107

6.1.1. Conceptos de replicacion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1076.1.2. Configurar una replica . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

7. Particionado de tablas 1117.1. Particionado . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1117.2. Metodos de Particionado . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112

7.2.1. Configuracion de Particionado . . . . . . . . . . . . . . . . . . . . . . . . . . . 1127.3. Crear las particiones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

7.3.1. Redirigir los INSERT a las particiones . . . . . . . . . . . . . . . . . . . . . . 1157.3.2. Utilizar reglas de particion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1187.3.3. Trigger para los Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1197.3.4. Migracion de la data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1207.3.5. Crear nuevas particiones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1257.3.6. Ventajas de las particiones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1257.3.7. Errores al particionar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125

8. Pooling de conexiones 1268.1. El pool de conexiones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1268.2. PgBouncer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126

8.2.1. Instalacion de PgBouncer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1278.2.2. Configurar PgBouncer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127

8.3. PgPool II . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1308.3.1. Instalar PgPool II . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1308.3.2. Configuracion de PgPool II . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1308.3.3. Preparar los nodos de bases de datos . . . . . . . . . . . . . . . . . . . . . . . 1318.3.4. Activar PgPool II . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1318.3.5. Conectarse a PgPool II . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

9. Lenguajes Procedurales 1339.1. Estructura de una funcion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1339.2. Funciones SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1359.3. Funciones PL/pgSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1399.4. Funciones en PL/Python . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139

9.4.1. Funcion python basica . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1409.4.2. Aspectos basicos de Python . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1449.4.3. Ejemplo de Python . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1449.4.4. Tipos basicos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1459.4.5. Operadores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1479.4.6. Colecciones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1489.4.7. Control de flujo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1509.4.8. Funciones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1529.4.9. Funciones Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152

10.Extensiones y contrib 15410.1. Instalar Extensiones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15410.2. Extensiones Comunes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155

iv

Lista de Graficos

1.1. Arquitectura de PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.2. Estructura del disco . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.3. Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.4. Procesamiento de una consulta [4] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51.5. Pantalla principal de Pgadmin3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

2.1. Asignacion de privilegios en Pgadmin3 . . . . . . . . . . . . . . . . . . . . . . . . . . 20

5.1. EXPLAIN ANALYZE grafico en PgAdmin . . . . . . . . . . . . . . . . . . . . . . . . 105

v

Captulo 1

Arquitectura del Sistema

1.1. Breve historia

PostgreSQL,originalmente llamada Postgres, fue creada en la UCB (Universidad de California, Ber-keley) por un profesor llamado Michael Stonebraker en 1986, como un sucesor de Ingres, un motorpropiedad de Computer Associates y con el objetivo de avanzar el estado del arte en bases de datosrelacionales.

En 1996, luego de anos de desarrollo en la academia, un equipo de desarrolladores fuera de Berkeleydecide liberar el proyecto y hacerse cargo de sus desarrollo, trabajando varios anos hasta liberar laprimera version 6.0 en 1997.

Desde el inicio fue un gestor de base de datos conocido por su estabilidad y con la ayuda de cientosde desarrolladores a nivel mundial, se fueron agregando nuevas funcionalidades como control de laconcurrencia, nuevos tipos de datos, mayor perfomance, etc.

1.2. Quienes utilizan PostgreSQL?

En el Peru y el mundo hay gran cantidad de empresas e instituciones que han confiado uno de susactivos mas valiosos, su informacion, a PostgreSQL, a continuacion algunos ejemplos:

1.2.1. Internacionales

U.S. Agency for International Development

U.S. Centers For Disease Control and Prevention

U.S. Department of Labor

U.S. General Services Administration

U.S. State Department

IMDB.com, The Internet Movie Database

1

Macworld

Sun Microsystems

Red Hat

Apple

Fujitsu

Cisco

Skype

2

1.3. Resumen de la Arquitectura

PostgreSQL tiene una arquitectura que incluye diversos modulos que interactuan entre si. En el nivelmas alto sigue un esquema cliente-servidor mientras que en el acceso a datos utiliza un esquema porcapas.

Grafico 1.1: Arquitectura de PostgreSQL

El modulo Libpq es el encardado de gestionar las comunicaciones entre el cliente y el postmaster(servicio de PostgreSQL en el servidor).

El servidor esta compuesto por 2 grandes modulos, el Postmaster que es el responsable deaceptar las comunicaciones con el cliente, autentificar y dar acceso. El Postgres se encarga deadministrar los querys y comandos enviados por el cliente. PostgreSQL trabaja bajo el conceptode process per user, eso significa un solo proceso cliente por conexion.

El Storage Manager (Gestor de almacenamiento) es responsable de la gestion del almacena-miento de los datos, controlar todos los trabajos del back-end incluido la administracion delbuffer, archivos, bloqueos y control de la consistencia de la informacion.

Cuando se guarda la data en disco, esta es utilizada para consultas. Al leer la data, se extraedel disco para pasarla a la RAM, y al escribir se transfiere de la RAM al disco. [4]

1.3.1. Gestion de la data

La data en todo Gestor de Bases de Datos se guarda en bloques de disco llamados paginas cuyotamano vara entre 8KB y 32KB, las cuales se guardan en diferentes posiciones fsicas del disco, sise dispersan por el disco, causan menor rendimiento de la base de datos.

En PostgreSQL cuando se realizan operaciones de lectura y/o escritura primero se consulta al Buffer(RAM) si contiene la pagina, en caso no ser as, se obtiene del disco.

Toda operacion hace que PostgreSQL agregue data. Cuando se elimina un registro o se modifica,PostgreSQL almacena una copia invisible hasta que ejecuta VACUUM y se libera de toda la datasobrante.

3

Grafico 1.2: Estructura del disco

Indices

En los discos la data se almacena en bloques de datos llamados paginas, estos bloques son accedidospor entero (operacion atomica). Estos bloques estan estructurados como listas enlazadas, contienenuna seccion de data y un puntero a la localizacion del siguiente nodo.

Debido a que los registros se buscan por campos, buscar en un conjunto de campos es necesario paraencontrar los registros que se buscan en una consulta, pero al buscar en un campo cuyos datos noestan ordenados el tiempo de busqueda escala junto con el tamano de la data.

Un ndice es un archivo donde esta parte de la data y estructura de una tabla con las search keyde busqueda. Al crear un ndice en una tabla se crea otra estructura de datos que contiene el valorordenado del campo y apunta al registro que lo contiene. Es recomendable crear ndices sobre datosque se repitan lo menos posible entre si.

Los ndices representan un gran aumento en el rendimiento de un gestor de base de datos pero con-sumen espacio de disco por cada ndice que se cree. Debido a su capacidad de disminuir el tiempode busqueda en grandes tablas, solo debe ser utilizado con ese fin.

Grafico 1.3: Indices

4

1.3.2. El procesamiento de una consulta

Grafico 1.4: Procesamiento de una consulta [4]

El cliente (libpq) se comunica con el servicio del postmaster para pasarle una cadena de texto conla consulta.

El parser transforma la consulta en una serie de instrucciones que la base de datos puede interpretar,por eso es importante escribir bien las consultas.

A continuacion se analiza que lo escrito sea sintacticamente correcto y se descompone por token laconsulta para pasarla a la estructura que le corresponde (select, update, grant, etc).

El modulo Traffic Cop contiene al controlador principal del proceso del PostgreSQL, ademas se encar-ga de las comunicaciones entre el Parser, Optimizer, Executor y commands functions. Las consultascomplejas pasan al Rewriter (select,insert, etc.), las que no, se pasan al Utility Commands, en general

5

consulta simples (alter, create, vacuum, etc).

El modulo planner es el encargado de generar el plan de ejecucion, esto es estimar la mejor vapara resolver el query, maneja mediante formulas matematicas avanzadas la forma de busqueda dedatos y la forma de resolver las relaciones entre tablas. Luego que el planner calcula la forma maseficiente de ejecutar la consulta se la pasa al Executor que la lleva a cabo. [4]

1.4. Multi-Version Concurrency Control (MVCC)

Una decision de diseno importante que se debe tomar en toda BD es como gestionar la interaccionde multiples clientes con la misma data. PostgreSQL utiliza un enfoque llamado Multiversion Con-currency Control (control multiversion de la concurrencia) el cual es tambien utilizado en otras Bdscomo Oracle.

Mediante el control de concurrencia el gestor permite que muchos usuarios puedan acceder a la mis-ma data al mismo tiempo. Cada proceso de usuario ejecuta transacciones las que a su vez puedencontener una o mas operaciones.

Las transacciones deben cumplir el criterio ACID:

A tomicity: todas las acciones en la transaccion se cumplen o no se cumple ninguna.

C onsistency: la transaccion solo termina si la data es consistente.

I solation: la transaccion es independiente de otras transacciones.

D urability: cuando la transaccion termina el resultado de la misma permanece.

Bajo el MVCC, la implementacion de concurrencia de PostgreSQL, las transacciones ven una imagende la data al momento de empezar (para eso la data se versiona con un timestamp), esto protege latransaccion de inconsistencia de data cuando llegan varias operaciones de Lectura/escritura sobre elmismo registro.

La data no se modifica o elimina, solo se agregan nuevos registros y los antiguos pasan a ser invisibles.La nueva data no es visible para otras transacciones hasta que no termina la actual transaccion y esenviada (committed) a la base de datos.

1.5. Write-Ahead Logging

Es el metodo estandar en los gestores de bases de datos para asegurar la integridad de la data. Elconcepto central de WAL es que los cambios a los archivos de data (donde las tablas y los ndicesresiden) solo deben ser escritos despues que los cambios han sido registrados, esto es, despues de quelos registros de logs describiendo los cambios se hayan guardado de forma permanente. Esto sirvepara que en caso de un desastre, se pueda recuperar la Bd utilizando el log del servidor.

El parametro wal level de postgresql.conf determina cuanta informacion es registrada en el WAL.Las alternativas son tres: minimal (opcion por defecto), archive y hot standby.

6

En el nivel minimal, no se registran algunas operaciones como CREATE TABLE AS o CREATEINDEX pero este nivel no guarda suficiente informacion como para reconstruir la data de la BDdesde el WAL.

Para poder llevar a cabo replicacion se necesita utilizar los niveles archive o hot standby. La dife-rencia entre ambos es que hot standby no solo registra todos los cambios en la data sino ademas lastransacciones as sea de solo lectura

1.6. Objetos mas utilizados en PostgreSQL

El servidor PostgreSQL tambien conocido como servidor o demonio, se puede tener mas deuno en un servidor siempre y cuando utilicen diferentes puertos o ips y almacenen su data enubicaciones diferentes.

Base de datos, cada servidor puede tener varias bases de datos.

Tablas, son la principal herramienta de toda base de datos.

Esquemas, son parte del estandar ANSI-SQL, y son los contenedores logicos de tablas y otrosobjetos. Cada base de datos puede tener diferentes esquemas.

Tablespace, es la localizacion fsica donde la data es almacenada. Postgresql permite que segestionen de forma independiente, lo que significa que se pueden mover las bases de datos aotras particiones o discos con pocos comandos.

Vistas, se utilizan para abstraer las consultas y en PostgreSQL ademas pueden ser actualizadas.

Funciones, en Postgresql pueden retornar un solo valor o un set de registros.

Operador, son funciones simbolicas que tienen el respaldo de una funcion, en PostgreSQL sepueden definir por el usuario.

Cast, permite convertir de un tipo a otro y es soportado por funciones que realmente hacen laconversion. En PostgreSQL los usuarios pueden crear sus propias funciones de conversion.

Sequence, controlan los numeros autoincrementales en las definiciones de las tablas. Se creanautomaticamente cuando se define una columna serial.

Triggers, son los disparadores de acciones al detectar cambios en la data.

Data externa, en postgresql se puede hacer consultas fuentes externa de data ya sea que esafuente sea otra BD relacional, un archivo plano, una Bd NoSql, un web service, etc.

Extensiones, agrupan funciones, tipos, casts, indices en una sola unidad para mayor manteni-bilidad. Es sobre todo usar para instalar modulos adicionales.

1.7. Nuevas funcionalidades en PostgreSQL 9.2

Algunas de las nuevas funcionalidades de Postgresql 9.2 son: [3]

Acelerar la consulta de columnas pertenecientes a un index.

7

Mejoras en el ordenamiento que optimizan operaciones de ordenamiento en memoria hasta un20 %.

Mejoras en el planeamiento de consultas.

Replicacion en cascada ahora soporta streaming de un esclavo a otro esclavo.

ALTER TABLE IF EXISTS sintaxis para hacer cambios en tablas.

Mas opciones para ALTER TABLE.

Mas opciones para crear y restaurar backups.

La posibilidad de crear funciones en javascript mediante plv8js.

El tipo JSON como tipo nativo de Postgresql.

Las funciones SQL pueden referirse a los argumentos por nombre y no por numero.

1.8. Limitaciones

PostgreSQL por su naturaleza de BD para servidores, no es aplicable para ser embebida como SQLiteo Firebird. Ademas, en muchos hostings por uno u otro motivo no esta presente PostgreSQL, aunqueese es un problema que se esta solucionando con el tiempo.

1.9. Instalacion

Para instalar en Ubuntu la version 9.2 de PostgreSQL llevamos a cabo los siguientes pasos:

Instalar las libreras requeridas

sudo apt-get install libpq-dev

Agregar el repositorio donde esta ubicado PostgreSQL 9.2 (no esta aun en los repositoriosoficiales).

sudo add-apt-repository ppa:pitti/postgresql

Actualizar la lista de paquetes disponibles:

sudo apt-get update

Instalar el servidor

sudo apt-get install postgresql-9.2

Poner a punto el servidor:

Ingresar en el template1 de Postgresql para cambiar la contrasena del usuario por defecto.

sudo su postgres -c psql template1

Cambiar la contrasena del usuario postgres.

8

postgres=# ALTER USER postgres WITH PASSWORD qwerty;

Salimos del cliente psql.

\qEliminamos la contrasena del usuario postgres en el sistema

sudo passwd -d postgres

Se utiliza su para cambiar el password del usuario postgres

sudo su postgres -c passwd

Se crea el usuario pedro con el usuario del servidor postgres

sudo -u postgres createuser -D -A -P pedro

Crear la DB

sudo -u postgres createdb -O pedro Bd prueba

Para instalar Pgadmin:

sudo apt-add-repository ppa:voronov84/andreyv

sudo apt-get update

sudo apt-get install pgadmin3

Grafico 1.5: Pantalla principal de Pgadmin3

9

Auto Explain

Otra opcion muy utilizada despues de la version 8.4 de PostgreSQL es el modulo auto explain quepermite analizar la duracion de una consultada viendo su plan EXPLAIN asociado.

Para habilitarlo se debe agregar en postgresql.conf los siguientes parametros y reiniciar el servidor:

shared preload libraries = auto explaincustom variable classes = auto explainauto explain.log min duration = 1s

Esta configuracion va a ejecutar auto explain en toda consulta que dure mas de un segundo re-gistrandola con un plan EXPLAIN completo.

10

Ejercicios:

1. Instalar el servidor Postgresql.

2. Crear una base de datos llamada Base curso.

3. Crear un usuario llamado usuario curso.

4. Crear la tabla Persona de propiedad del usuario usuario curso con los siguientes campos:

5. nombre, apellidop, apellidom.

6. Insertar el nombre y apellidos de tres alumnos en la tabla.

11

Captulo 2

Administracion

2.1. Configuracion de Postgresql

Los archivos utilizados para configurar postgresql son tres: [3]

postgresql.conf : El principal archivo de configuracion de postgresql, controla configuracionesgenerales como la cantidad de memoria RAM a utilizar, la localizacion fsica de las bases dedatos, las ips a las que escucha postgresql, la configuracion de los logs, etc.

pg hba.conf : controla la seguridad, gestiona el acceso al servidor indicando que usuarios pue-den acceder a que BD, que Ips o grupos de Ips estan permitidos de conectarse y el esquema deautenticacion esperado.

pg ident.conf : Es el archivo que mapea los usuarios del SO con los usuarios del servidor

Para conocer donde estan localizados en el sistema, se puede utilizar un superusuario del servidorpara ejecutar la siguiente consulta:

$ sudo su postgres -c psql -d Bd prueba

Y luego:

SELECT name, setting

FROM pg_settings

WHERE category = File Locations;

Una forma sencilla de ver las configuraciones en postgresql.conf es consultar la tabla pg settings, porejemplo la siguiente consulta devuelve los valores de seis parametros de postgresql.conf.

12

SELECT name, context, setting, boot_val, reset_val FROM pg_settings

WHERE name

in (listen_addresses,max_connections,shared_buffers,effective_cache_size,

work_mem, maintenance_work_mem)

ORDER BY context,name;

Tambien se puede utilizar el comando show para ver cada parametro de configuracion por separadocon su valor respectivo, por ejemplo:

show maintenance_work_mem;

show work_mem;

show all;

2.2. Creando Bases de Datos

La forma mas sencilla de crear una base de datos es ingresando a un cliente (como psql) y utilizandoel comando:

CREATE DATABASE mi_bd;

El dueno de la Bd sera el usuario en el sistema y la BD sera una copia de template1. Es potestad delcreador de una Base de Datos eliminarla posteriormente, lo cual elimina a su vez todos sus objetoscomo tablas, ndices, funciones, etc as tengan otros duenos.

La primera base de datos en ser creada al instalarse PostgreSQL es postgres y luego template0 ytemplate1 que son plantillas de bases de datos desde las cuales se copian las nuevas bases en sercreadas. Todo cambio que se haga en template1 se replicara en toda nueva Base de datos, por lo quese recomienda ser muy prudente con las plantillas.

Si se desea crear una base de datos con otro dueno fuera del usuario que la esta creando, se agregael parametro OWNER.

Por ejemplo:

CREATE DATABASE nombre_db OWNER usuario_curso;

Solo el superusuario puede crear una base de datos para otro usuario.

13

2.2.1. Comando createdb

Como conveniencia se puede utilizar el comando createdb desde la terminal:

createdb nombre db

Lo que hace createdb es conectarse a la base de datos postgres y ejecuta CREATE DATABASE uti-lizando el usuario del sistema desde el cual se le ejecuta.

Mediante parametros se puede utilizar createdb de forma mas versatil, con -O se indica el owner debase de datos creada, con -U el usuario con el se conectara a postgres entre otros parametros.

Por ejemplo:

createdb -U postgres -O usuario curso nueva bd

Va a crear una base de datos llamada nueva bd mediante el usuario postgres y va a ser propiedad deusuario curso.

2.2.2. Creando una base de datos plantilla

Una base de datos de plantilla (Template DB) es una base de datos que sirve de plantilla para crearotras bases de datos. Se puede crear una Bd a partir de cualquier otra Bd, pero PostgreSQL permiteque se definan Bds especficamente de plantilla. La principal diferencia es que una Bd definida comotemplate no puede ser eliminada y puede ser utilizada por cualquier usuario con capacidad de crearbases de datos como plantilla para una nueva BD.

La principal base de datos plantilla es template1 a partir de la cual se crean todas las bases de datosen caso no se mencione otra plantilla. Si se agregan objetos a template1, se replicaran en todas lasnuevas bases que se creen teniendo a template1 como plantilla.

Existe una segunda plantilla, template0, que contiene el mismo contenido inicial que template1. Adiferencia de la segunda, template0 nunca debe ser modificada, ya que al crear una base de datostomando como plantilla template0, se crea una base limpia de todo cambio posterior, lo cual es es-pecialmente valioso al restaurar un backup de pg dump ya que debe ser restaurado sobre una basede datos sin modificacion alguna.

Para crear una copia de template0 se ejecuta:

CREATE DATABASE dbname TEMPLATE template0;

O desde la terminal:

createdb -T template0 dbname

Ademas de las plantillas predeterminadas, se pueden utilizar otras bases de datos como plantillas,con la limitante de que ninguna otra sesion puede estar conectada a la base de datos fuente mientrases copiada.

14

Si se ha disenado una Bd y se quiere convertirla en una plantilla, se ejecuta el siguiente comandocomo superusuario:

UPDATE pg_database SET datistemplate=true WHERE datname=mi_bd;

2.2.3. Eliminar una Base de Datos

Las bases de datos se destruyen con el comando DROP DATABASE.

DROP DATABASE nombre_db;

Solo el dueno de una base de datos puede eliminarla. Eliminar una Base de datos implica destruirtodo su contenido y es una accion que no puede ser deshecha.

No se puede ejecutar el comando DROP DATABASE mientras se esta conectado a la base de datosa ser eliminada, se debe hacerconectado desde otra base de datos.

Existe un comando de consola, dropdb, para eliminar bases de datos.

dropdb nombre db

2.3. Creando Esquemas

Los esquemas son una forma logica de partir una base de datos en mini contenedores. Se puedendividir los esquemas por funcionalidad, usuarios o cualquier otro atributo que se desee. Ademas dela particion logica, proveen una forma sencilla de repartir privilegios.

Para crear un esquema en una BD, nos conectamos a la Bd y ejecutamos el comando:

CREATE SCHEMA mi_esquema;

Para acceder a un objeto en un esquema se escribe el nombre del esquema seguido por el nombreddel objeto separados por un punto.

schema.table

Esta forma de nombrar una tabla funciona para toda aplicacion, incluyendo comandos para modificarla tabla, leer y escribir datos.

Para crear una tabla en el nuevo esquema se debe utilizar:

CREATE TABLE mi_esquema.mitabla (...);

15

La ruta por defecto (search path) definida en postgresql.conf es $user, public. Lo cual signifi-ca que si hay un esquema con el mismo nombre que el del usuario en el sistema, entonces todos losobjetos van a revisar primero el esquema con el mismo nombre del usuario y luego el esquema publico.

Los esquemas son tambien utilizados para abstraer los nombres de las tablas, debido a que el nombresolo debe ser unico dentro del esquema y muchas aplicaciones explotan esta caracterstica creandotablas con el mismo nombre en diferentes esquemas de tal manera que se carga una diferente depen-diendo del usuario en el sistema.

Los esquemas sirven por ejemplo para incluir modulos externos (contrib) dentro de un esquema detal manera que los nombres de sus objetos no puedan entrar en conflicto con el nombre de los objetosde la BD.

Se puede pensar en esquemas como en directorio pero sin la posibilidad de ser anidados.

Otra forma de asignar permisos es mediante Pgadmin3, el cual tiene una completa interfaz paraasignar permisos.

Grafico 2.1: Asignacion de privilegios en Pgadmin3

16

2.3.1. Permisos

Si se quiere dar permisos a un esquema que recien se ha creado, se van a utilizar los comandosALTER DEFAULT PRIVILEGES y GRANT.

Por ejemplo, para que todos los usuarios de una BD tengan acceso a EXECUTE y SELECT entodas las tablas y funciones de un esquema que se creen a partir del momento en que se ejecuten seutilizaran los siguientes comandos:

GRANT USAGE ON SCHEMA contrib TO public;

ALTER DEFAULT PRIVILEGES IN SCHEMA contrib

GRANT SELECT, REFERENCES, TRIGGER ON TABLES

TO public;


GRANT SELECT, UPDATE ON SEQUENCES

TO public;


GRANT EXECUTE ON FUNCTIONS

TO public;


GRANT USAGE ON TYPES

TO public;

Si el esquema ya esta definido con sus tablas y funciones, se pueden dar permisos uno por uno odarle permisos a todos mediante GRANT .. ALL .. IN SCHEMA.

GRANT USAGE ON SCHEMA contrib TO public;

GRANT SELECT, REFERENCES, TRIGGER

ON ALL TABLES IN SCHEMA contrib

TO public;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA contrib TO public;

GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA contrib TO public;

Es comun buscar que un esquema sea propiedad de otro usuario, para asi restringir las actividadesde los usuarios a namespaces restringidos. La sintaxis es:

CREATE SCHEMA nuevo_esquema AUTHORIZATION usuario_curso;

Los esquemas cuyo nombre empieza con pg son utilizados el sistema y no se recomienda su creacionpor los usuarios debido a que futuras versiones de Postgresql pueden utilizar el nombre elegido.

17

Cuando no se define explcitamente un esquema, el objeto creado entra en el esquema public, porlo cual es igual escribir:

CREATE TABLE productos ( ... );

Y

CREATE TABLE public.productos ( ... );

2.3.2. Eliminar esquemas

Para eliminar un esquema este debe estar vacio (todos los objetos en su interior deben haber sidoeliminados) y se utiliza:

DROP SCHEMA myschema;

Para eliminar un esquema incluyendo todos sus objetos utilizar:

DROP SCHEMA myschema CASCADE;

2.4. Ruta de Busqueda de Esquemas

Los nombres completos incluyendo el del esquema son tediosos de escribir, ademas de que no es buenohardcodear los nombres de los esquemas en las aplicaciones. Al llamar a las aplicaciones solo porsu nombre, el sistema determina que tabla es buscando en todos los esquemas presentes. El primerobjeto con el nombre indicado que encuentre es escogido como el buscado.

El primer esquema en la ruta de busqueda (search path) es llamado el esquema actual (current sche-ma), ademas de ser el primer esquema en la ruta de busqueda es tambien el esquema por defectodonde se crearan las nuevas tablas si no se especifica un esquema en particular.

Para ver la actual ruta de busqueda se utiliza:

SHOW search_path;

Para colocar un nuevo esquema en la ruta de busqueda:

SET search_path TO miesquema,public;

18

Lo cual hara que el nuevo esquema sea el primero en la ruta de busqueda, si queremos dejar deconsiderar public en la ruta de busqueda se utiliza:

SET search_path TO miesquema;

2.4.1. Utilizacion de los esquemas

Los esquemas se pueden utilizar para organizar la data de muchas formas diferentes. Algunos patronesde uso son recomendados:

Si no se crean esquemas todos los usuarios acceden al esquema public. Esta configuracion esrecomendada cuando hay solo un usuario o pocos usuarios cooperantes en la base de datos.

Se puede crear un esquema para cada usuario con su propio nombre. Si se usa esta configuracionse pueden revocar los permisos de los usuarios sobre el esquema public de tal manera que solopuedan utilizan sus propios esquemas.

Para instalar aplicaciones compartidas es recomendable colocarlas en sus propios esquemas ydar permisos a todos los usuarios para acceder a ese esquema. Los usuarios pueden referirsea los objetos agregados por su nombre completo o agregar el nuevo esquema en la ruta debusqueda.

2.5. Roles

En PostgreSQL solo existe un tipo de cuenta y es la de rol. Si el rol puede ingresar a la BD, es unusuario. Los roles puede ser parte de otros roles y cuando un rol tiene miembros, es llamado un grupo.

Los roles pueden ser duenos de objetos en las bases de datos (tablas por ejemplo) y pueden asignarprivilegios a otros roles en esos objetos para controlar quien accede. Ademas, es posible hacer a unrol miembro de otros roles de tal manera que utilice sus privilegios.

Los roles de PostgreSQL no son iguales a los usuarios del sistema operativo, en la practica puede serutill mantener la correspondencia entre ambos pero no es necesario.

Para crear un rol se utiliza CREATE ROLE :

CREATE ROLE name;

Y para eliminarlo se usa DROP ROLE:

DROP ROLE name;

Para saber que roles han sido creados utilizar:

19

SELECT rolname FROM pg_roles;

O el comando \du.

2.5.1. Crear un usuario

Al terminar de instalar el gestor de Bds, se crea por defecto una cuenta llamada postgres con unabase de datos llamada postgres. Antes de hacer nada, se debe entrar con este usuario mediante psqlo pgadmin y crear otros usuarios. La diferencia entre un rol y un usuario es que el usuario es creadocon el atributo LOGIN que le permite ingresar a una base de dato.

$ sudo su postgres -c psql -d postgresMediante este comando SQL se crea un usuario con capacidad de crear bases de datos.

CREATE ROLE leo LOGIN PASSWORD qwerty CREATEDB VALID UNTIL infinity;

Infinity es una opcion por defecto por lo que no necesita ser considerada. Se puede incluir una fechaen la cual se espera que la cuenta desaparezca.

Con otro comando SQL se crea un super usuario pero con un tiempo de vida limitado. Solo se puedecrear un superusuario siendo un superusuario.

CREATE ROLE regina LOGIN PASSWORD 123456 SUPERUSER VALID UNTIL 2020-10-20 23:00;

2.5.2. Atributos de los roles

Un rol puede tener diversos atributos que definan sus privilegios.

Login: solo los roles que tienen el atributo LOGIN pueden ser utilizados como el nombre iniciala utilizar en una conexion a una base de datos.

Superuser: Un superusuario de una base de datos pasa por encima de toda restriccion excepto alingresar al sistema. Es un privilegio peligroso y debe ser otorgado con mucho cuidado. Para crearun nuevo superusuario utilizar: CREATE ROLE name SUPERUSER. Solo un superusuariopuede crear otro superusuario.

database creation: A un rol se le debe dar de forma explcita el permiso para crear bases dedatos. Para lograrlo se utiliza CREATE ROLE name CREATEDB.

role creation: Para que un rol pueda crear otros roles se utiliza CREATE ROLE name CREA-TEROLE.

initiating replication: Para que un rol pueda iniciar una replicacion se debe dar permisos dereplicacion de login CREATE ROLE name REPLICATION LOGIN.

20

password: Un password solo es importante si el metodo de autenticarse exige su utilizacion. Losmetodos de autenticacion password y md5. Para crear un password se utiliza CREATE ROLEname PASSWORD string.

Cambiar atributos

Para cambiar los atributos de un rol se utiliza ALTER ROLE.

Por ejemplo:

ALTER ROLE davide WITH PASSWORD hu8jmn3;

ALTER ROLE miriam CREATEROLE CREATEDB;

ALTER ROLE worker_bee SET maintenance_work_mem = 100000;

2.6. Grupos

Con frecuencia se agrupan usuarios para facilitar la gestion de sus privilegios. De esta manera losprivilegios pueden ser otorgados o revocados en grupo.

Los roles de grupo son roles que no tienen permiso para ingresar en el sistema pero tienen otros rolescomo miembros. Son en general una forma de que un conjunto de usuarios comparta permisos encomun.

Se puede crear un rol de grupo con:

CREATE ROLE jungle INHERIT;

Y asignar otro usuario a ese grupo mediante:

GRANT jungle TO leo;

Tal como se muestra, al crear un rol para convertirlo en un grupo, este debe ser capaz de heredarsus permisos.

Para remover miembros de un grupo se utiliza REVOKE.

REVOKE leo FROM jungle;

Los miembros de un grupo pueden utilizar sus privilegios de dos formas:

Los miembros de un grupo pueden utilizar SET ROLE para convertirse momentaneamente en elgrupo. En este estado la sesion tiene acceso a los privilegios del role de grupo en vez de los privilegios

21

originales con los que se registro y cualquier objeto creado sera propiedad del rol de grupo.

En segundo lugar los roles miembros que tengan el atributo INHERIT tienen acceso a los privilegiosde los roles de los que son miembros, incluyendo cualquier privilegio heredado por esos roles.

Por ejemplo:

CREATE ROLE joe LOGIN INHERIT;

CREATE ROLE admin NOINHERIT;

CREATE ROLE wheel NOINHERIT;

GRANT admin TO joe;

GRANT wheel TO admin;

Al conectarse como joe, la sesion tiene los permisos de admin pero no los de wheel. Si se ejecuta:

SET ROLE admin;

La sesion solo tendra los privilegios de admin debido a que no hereda.

2.7. Control de Acceso

PostgreSQL dispone de varios metodos de autenticacion para validar usuarios. El tipo de metodo sedefine en pg hba.conf. Los cinco tipos son:

trust: Confia en el usuario que se conecta. Solo valida que su IP y usuario sean correctos sinimportar la contrasena. Es la mas comun en bases de datos instaladas en una computadorapara un solo usuario.

Md5: Es el mas comun de los metodos y requiere un password cifrado mediante md5.

Password: significa autenticarse mediante un password en texto plano.

Ident: utiliza el SO para identificar si existe la cuenta del usuario intentando conectarse en elSO. No utiliza password.

Para definir el tipo de autenticacion se utiliza el archivo pg hba.conf, este archivo de configuracioncontrola que usuarios pueden conectarse al servidor PostgreSQL.

22

Ejercicios:

1. Configurar PostgreSQL para que pueda ser accedido desde toda IP.

2. Crear una base de datos llamada Base plantilla y convertirla en una plantilla.

3. Agregar dos tablas a Base plantilla.

4. Crear una base de datos llamada base copia tomando como base a Base plantilla.

5. Crear un esquema llamado esquema curso propiedad de usuario curso.

6. Crear un usuario llamado usuario prueba.

7. Crear un esquema llamado esquema prueba propiedad de usuario prueba.

8. Crear una tabla Producto en ambos esquemas con los campos nombre, precio y cantidad.

9. Insertar datos diferentes en ambas tablas.

10. Ordenar los esquemas de manera tal que al escribir SELECT * FROM Productos se vea enpantalla la tabla Productos de esquema prueba.

11. Crear un usuario llamado usuario creador y darle permiso para crear bases de datos, esquemasy roles.

12. Crear bases de datos, esquemas y roles utilizando a usuario creador.

13. Crear un grupo llamado grupo prueba.

14. Hacer a usuario prueba miembro de grupo prueba.

23

Captulo 3

Transacciones y concurrencia

PostgreSQL provee las herramientas para gestionar el acceso concurrente a la data. La consistenciade la data es mantenida internamente utilizando un modelo de multiversion (Multiversion Concu-rrency Control, MVCC). Esto significa que al consultar una Base de Datos cada transaccion ve unainstantanea de la data tal como era algun tiempo antes, sin importar el estado actual de la datasubyacente. Esto protege a la transaccion de ver data inconsistente que puede haber sido causada porotras transacciones concurrentes en los mismos registros. Proveyendo aislamiento de la transaccionpara cada sesion en la BD.

La principal ventaja de utilizar el modelo MVCC de control de la concurrencia en vez del bloqueode registros es que en MVCC los bloqueos por leer data no interfieren con los bloqueos por escribirdata en un registro as las operaciones no se bloquean entre si. [2]

3.1. Definicion de Transaccion

Las transacciones son uno de los conceptos fundamentales de todos los sistemas de bases de datos.El punto esencial es que una transaccion engloba un multiples pasos en una sola operacion donde seejecuta todo o nada. Los pasos intermedios antes de culminar la transaccion no son visibles para lastransacciones concurrentes y si alguna falla ocurre, que evita que la transaccion se complete, ningunode los pasos previos afecta a la base de datos.

Si un conjunto de operaciones de una transaccion afectan a varios tablas en una base de datos, siuna sola de las operaciones falla, todas las demas operaciones quedan sin efecto y la base de datosno es modificada por la transaccion.

Por ejemplo, si en un banco se realiza una transferencia entre dos cuentas y una operacion restadinero de una cuenta pero otra operacion no puede a sumarlo al destinatario, toda la transaccionse cancela. Una transaccion es atomica, desde el punto de vista de otras transacciones o se lleva acabo completamente o no en absoluto, ademas, antes de darse por terminada la transaccion, todaslas actualizaciones hechas la BD son guardadas en almacenamiento permanente (p. ej. disco duro).

Dadas las siguientes operaciones:

UPDATE accounts SET balance = balance - 100.00

24

WHERE name = Alice;

UPDATE branches SET balance = balance - 100.00

WHERE name = (SELECT branch_name FROM accounts WHERE name = Alice);

UPDATE accounts SET balance = balance + 100.00

WHERE name = Bob;

UPDATE branches SET balance = balance + 100.00

WHERE name = (SELECT branch_name FROM accounts WHERE name = Bob);

Los detalles de los comandos no son importantes, lo crucial es que hay varias actualizaciones envueltasen una sola operacion.El banco por supuesto desea estar seguro de que las transacciones realizadastengan exito todas o ninguna se lleve a cabo, caso contrario la data guardada no sera coherente. Paragarantizar que situaciones as no se produzcan se agrupan las operaciones en transacciones.

Una transaccion ademas debe de ser registrada y guardada de forma permanente antes de reportarsecomo terminada.

Otra propiedad importante de las transacciones es el aislamiento: cuando multiples transaccionesestan ejecutandose al mismo tiempo, cada una no debe de ser capaz de ver los cambios incompletosllevados a cabo por otros. Por ejemplo, si una transaccion esta ocupada cuadrando los balances decaja no debera ver solo la transaccion iniciada por Alice o Bob, sino el resultado final de toda latransaccion. Es por esto que las transacciones deben ser todo o nada no solo en sus efectos perma-nentes en la base de datos sino en la visibilidad de sus resultados a medida que avanza. Es por estoque los resultados de una transaccion son invisibles para otras transacciones hasta que la transacciontermina, donde todos los cambios a la data se vuelven visibles simultaneamente.

En PostgreSQL una transaccion se lleva a cabo utilizando los comandos BEGIN y COMMIT al inicioy fin de la transaccion, por ejemplo:

BEGIN;


WHERE name = Alice;

-- etc etc

COMMIT;

Si en algun punto de la transaccion decidimos no hacer commit, podemos eliminar todos los cambiosmediante el comando ROLLBACK.

Por defecto, PostgreSQL trata a cada comando SQL como una transaccion, colocando BEGIN alinicio de la transaccion y COMMIT al final si es exitosa.

25

3.1.1. Savepoints

Es posible controlar las operaciones en una trasaccion de una forma mas fina mediante el uso de save-points. Los savepoints permiten descartar selectivamente partes de una transaccion y enviar (commit)el resto a la BD. Despues de definir un Savepoint en una transaccion , se puede hacer ROLLBACKTO de regreso al savepoint. Todos los cambios a la base de datos entre la definicion del Savepoint yel rollbak son descartadas, pero los cambios previos al Savepoint son guardados.

Se puede regresar a un savepoint varias veces en el transcurso de una transaccion.

Regresando al ejemplo del banco, la transaccion podra ser la siguiente:

BEGIN;


WHERE name = Alice;

SAVEPOINT my_savepoint;


WHERE name = Bob;

-- En realidad mejor utilizamos la cuenta de Wally

ROLLBACK TO my_savepoint;


WHERE name = Wally;

COMMIT;

ROLLBACK es la unica manera de recuperar el control de una transaccion que ha sido bloqueadapor el sistema debido a un error en una de sus transacciones.

3.2. Niveles de Aislamiento de las Transacciones

Existen cuatro niveles de aislamiento de las transacciones en el estandar SQL, cada uno de los cualespermite que se lleven o no a cabo tres comportamientos.

Los comportamientos son:

Dirty read: Una transaccion lee data escrita por una transaccion aun no enviada (commited).

Nonrepeatable read: Una transaccion vuelve a leer data que ha leido previamente y encuentraque ha sido modificada por otra transaccion que envo data despues de enviada la actualtransaccion.

Phantom read: Una transaccion vuelve a ejecutar una consulta que devuelve un conjunto deregistros que satisfacen determinada condicion de busqueda y encuentra que este conjunto deregistros a cambiado debido a otra transaccion concurrente.

Los cuatro niveles y sus respectivos comportamientos segun PostgreSQL:

26

Isolation Level Dirty Read Nonrepeatable Read Phantom Read

Read uncommitted Not possible Possible Possible

Read committed Not possible Possible Possible

Repeatable read Not possible Not possible Not possible

Serializable Not possible Not possible Not possible

Tabla 3.1: Niveles de aislamiento

Como se puede observar, Read uncommitted y Read commited son iguales segun la implementacionde PostgreSQL y el valor adicional de Serializable sobre Repeteable Read es el monitoreo para evitarque transacciones concurrentes no puedan ser serializadas. Read uncommitted se mantiene para finesde compatibilidad.

3.2.1. Detalle de los niveles de aislamiento

Los niveles de aislamiento (isolation levels) son un aspecto central en el manejo de las transaccionespor lo cual lo abordaremos con detalle.

Para establecer el nivel de aislamiento de una transaccion se utiliza SET TRANSACTION.

SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ

| READ COMMITTED | READ UNCOMMITTED };

Una vez realizada la primera consulta, ya no se puede cambiar el nivel de aislamiento de la transaccion.

Read commited

Es el nivel de aislamiento por defecto en PostgreSQL. Cuando una transaccion utiliza este nivel,un SELECT solo ve data enviada antes de que la consulta empiece, nunca ve data no enviada (un-committed) o cambios enviados por transacciones concurrentes durante la ejecucion de la consulta.Sin embargo, SELECT si ve los cambios efectuados dentro de la transaccion as no hayan sido aunenviados. Ademas, cada SELECT ve datos diferentes si otra transaccion ha cambiado los datos antesde que empiece a ejecutarse, por lo que diferentes SELECT dentro de una misma transaccion puedenver diferente data en la BD.

Por ejemplo, en una sesion A ejecutar los siguientes comandos sobre una tabla existente.

BEGIN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM tabla_ejemplo;

Luego en una sesion B ejecutar:

27

UPDATE tabla_ejemplo SET .... WHERE ...;

En la sesion A volver a ejecutar un SELECT y terminar la transaccion:

SELECT * FROM tabla_ejemplo;

END;

Se puede observar que los dos select en la misma transaccion devolvieron resultados diferentes.

Los comandos de escritura (UPDATE, DELETE, SELECT FOR UPDATE y SELECT FOR SHA-RE) se comportan igual que SELECT al buscar registros: solo van a encontrar los registros que yaestaban enviados (committed) al empezar el comando. Si el registro ha sido modificado despues deque empezo el comando, el comando va a esperar a que los cambios se enven o se descarten. Si loscambios de la transaccion anterior se envan, la segunda transaccion va a ignorar el registro si hasido eliminado por la primera o va a tomar en cuenta los cambios si ha sido modificado, reevaluandola condicion de busqueda (WHERE) para comprobar si se sigue aplicando.

Por ejemplo, abrir una sesion A y ejecutar:

BEGIN:



En otra sesion B ejecutar:

BEGIN:



Mientras la primera transaccion no termine, la segunda transaccion se quedara esperando el resultado.Debido a lo explicado, el modo Read committed puede llevar a ver una instantanea inconsistentede la BD: puede ver los efectos de actualizaciones concurrentes en las registros que esta procurandoactualizar pero no ve los efectos de estos comando en otros registros de la BD.

Usos complejos de la Bd pueden producir resultados indeseados en el modo Read Committed, porejemplo un comando DELETE que opere en data que ha sido agregada y removida de su criterio debusqueda por otro comando, como en la siguiente transaccion.

BEGIN;

UPDATE website SET hits = hits + 1;

-- Execute in other sesion: DELETE FROM website WHERE hits = 10;

28

COMMIT;

Los valores que tengan 10 antes o despues de la actualizacion no van a ser afectados, ya que los quetienen 9 antes de la actualizacion siguen siendo vistos como 9 por el DELETE concurrente y los quetienen 10 estan siendo modificados por la primera transaccion por lo que el DELETE debe esperara que termine la actualizacion y en ese momento su valor sera de 11.

Es debido a esta caracterstica que Read Committed no es adecuado para todos los casos.1

Repeatable Read

Este nivel solo ve ve data enviada antes de que la transaccion haya empezado, nunca ve data noenviada o enviada despues de que la transaccion haya empezado. Si embargo la transaccion si ve loscambios enviados dentro de si aunque aun no hayan sido enviados. Este nivel de aislamiento previenetodos los comportamientos mencionados en la tabla anterior.2

En este nivel, los SELECT dentro de una transaccion ven todos la misma data en la BD, data ante-rior a que la transaccion empezara a ejecutarse.

Por ejemplo, llevar a cabo la misma prueba con el SELECT que con Read commited, pero definiendoRepeatable Read como nivel de aislamiento utilizando:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Los comandos que actualizan data (UPDATE, DELETE, SELECT FOR UPDATE y SELECT FORSHARE) tienen el mismo comportamiento del SELECT al buscar registros pero en caso de que unregistro este siendo modificado por una transaccion concurrente van a esperar que acabe y si el re-gistro ha sido modificado, no se va a poder llevar a cabo la operacion y toda la transaccion va a sercancelada con el siguiente mensaje:

ERROR: could not serialize access due to concurrent update

Cuando una aplicacion recibe este mensaje de error debe cancelar la actual transaccion y volverlaa empezar de cero. Esta segunda vez la transaccion vera las actualizaciones llevadas a cabo en losregistros por las otras transacciones.

Este nivel de aislamiento provee una rigurosa garanta de que cada transaccion vera una imagenestable de la BD dentro de si. Aun as, este nivel puede tener problemas debido a transaccionesconcurrentes que provoquen continuas cancelaciones de una transaccion.

Serializable

Este nivel provee el aislamiento mas estricto para las transacciones. Este nivel emula ejecucion serialde las transacciones, como si hubieran sido ejecutadas una despues de la otra, de forma serial envez de concurrente. Aun as, las aplicaciones deben estar listas para cancelar transacciones debido a

1[2] pp. 3422[2] pp. 343

29

fallas en la serializacion.

Este nivel trabaja igual que Repeatable Read a excepcion de que monitorea las condiciones quepueden hacer que un conjunto serializable de transacciones concurrentes se comporte de manera in-consistente con todas las posibles ejecuciones seriales de estas transacciones.

Para garantizar la serializacion PostgreSQL utiliza predicate locking o bloqueo por predicados, elcual consiste en analizar las transacciones para verificar si el orden de ejecucion es consistente con elresultado.

Por ejemplo, dada la tabla mytab:

Class Value

1 10

1 20

2 100

2 200

Tenemos dos transacciones, la transaccion A es:

INSERT INTO t VALUES (2, (SELECT SUM(value) FROM mytab WHERE class = 1));

Y luego inserta el resultado (30) en el campo value de un nuevo registro cuyo campo class = 2.Concurrentemente, la transaccion B es:

INSERT INTO t VALUES (1, (SELECT SUM(value) FROM mytab WHERE class = 2));

Y obtiene el resultado 300 el cual inserta en un registro cuyo campo class = 1. Luego ambas transac-ciones hacen commit, como no hay un orden serial de ejecucion consistente con el resultado, el modoSerializado va a permitir a una transaccion enviar su data a la BD y la otra va a ser cancelada conel mensaje:

ERROR: could not serialize access due to read/write dependencies among transactions

Esto se debe a que si A se ejecuta antes de B, B hubiera obtenido un resultado de 330 no 300 y alreves hubiera resultado en A con el valor de 330 como resultado.

El bloqueo por predicados en PostgreSQL, esta basado en la data accedida por la transaccion. Lagaranta de que un conjunto de transacciones concurrentes serializables va a tener el mismo efectoque si se ejecutaran una tras otra, significa que si se puede demostrar que cada transaccion por sepa-rado va a tener el resultado esperado al ejecutarse, se puede tener confianza de que va a hacerlo bienen un conjunto de transacciones aun sin informacion sobre lo que el resto de transacciones va a hacer.

30

3.3. Descripcion del Control de Concurrencia

Al crear o modificar un registro, el nuevo registro as creado guarda el ID de la transaccionen un campo llamado XID tambien llamado xmin, el mnimo XID capaz de ver este trozo deinformacion una vez ha sido enviado.

Cuando una consulta es ejecutada utiliza el ID de la actual transaccion como un lmite para loque puede considerarse visible. Los registros cuyo xmin es menor que el ID de la transaccion yhan sido enviados son considerados para ser mostrados en la consulta.

Un mecanismo similar gestiona la eliminacion. Cada registro tiene un XID de eliminacion,tambien llamado el xmax, que empieza vaco para indicar que el registro no ha sido borrado.Cuando se borra un registro, el ID de la actual transaccion se convierte en su xmax , paraindicar que no va a ser mas visible despues de ese punto en el tiempo (o mejor dicho en elhistorico de transacciones). Al ejecutarse una consulta, solo incluye un registro con xmax sieste es anterior al XID de la consulta.

Xmin y xmax son en esencia el tiempo de vida visible del registro en terminos de Ids. El registrosolo es visible desde una consulta cuyo XID este entre ambos.

El registro original va a ser eliminado despues de un tiempo por VACUUM, el modulo dePostgreSQL que elimina periodicamente los registros que ya no van a ser utilizados. [5]

Para ver los ids en funcionamiento se puede utilizar la funcion txid current snapshot().

3.4. Diferencias entre MVCC y Bloqueo

PostgreSQL provee varios modos de bloqueo para para controlar el acceso concurrente a la data enlas tablas. En general todas las consultas llevan a cabo alguna forma de bloqueo sobre la data a laque acceden.

Un bloqueo es ejecutado por un comando como SELECT o UPDATE para tener acceso a la datacon la que estan trabajando, en cambio MVCC es un modelo de trabajo del gestor de base de datospara permitir que varias transacciones al mismo tiempo (concurrencia) puedan acceder a la mismadata en la BD.

La principal ventaja de utilizar el modelo MVCC sobre bloqueo es que los bloqeuos de MVCC paraescribir data no entran en conflicto con los bloqueos para leer data. PostgreSQL mantiene la garantade que no va a necesitar bloquear ambos aun al proveer los mas estrictos niveles de aislamiento dela transaccion.

Bloqueo a nivel de tablas y registro esta disponible en PostgreSQL para aplicaciones que generalmenteno necesitan aislar completamente las transacciones y prefieren manejar gestionar explicitamente lospuntos de conflicto.

31

3.5. Ejemplo MVCC

3.5.1. UPDATES

Si dos sesiones al mismo tiempo procuran alterar un registro una de las dos esperara hasta que laotra termine. Una vez que la primera sesion termina, dependiendo de la configuracion del servidorse toma una decision.

Una configuracion es el modo por defecto Read Committed, en el cual la existencia de otrasesion que modifica el mismo registro solo provoca que se verifique si las condiciones paracambiar el registro aun existen en el registro cambiado por la primera transaccion, si ese es elcaso se procede con el cambio, caso contrario la segunda transaccion no afecta al registro.

El otro modo de configuracion es la Serializacion. Si otra sesion intenta modificar un registroque ya esta siendo modificado espera a que termine la primera transaccion, si la transaccionoriginal se termina, la segunda transaccion no puede modificar el registro y aparece un error:

ERROR: could not serialize access due to concurrent updates

Y la sesion no tiene otra opcion que eliminar la transaccion en curso.

Este modo se utiliza cuando se requiere que la sesion opere con una vista identica de laBD.

Para probar creese una tabla y un registro en la BD:

1. $ psql -d Bd prueba

2. CREATE TABLE t (s SERIAL, i INTEGER);

3. INSERT into t(i) values (0)

4. Para ver los valores xmin y xmax

SELECT xmin,xmax from t;

5. Para ver el id de la actual transaccion.

SELECT txid current();

Luego desde otra sesion inciese una transaccion mediante Begin y actualcese el registro pero no seenve (commit) aun.

1. Para iniciar la transaccion.

Begin;

2. Para ver el id de la ctual transaccion

select txid current();

3. Actualizar el registro

4. UPDATE t SET i=100 WHERE s=1;

Desde la perspectiva de la segunda sesion el registro se ha actualizado, pero si hacemos un select enla primera sesion, no se va a ver el cambio hecho en la segunda hasta que sea enviado.

32

3.5.2. DELETE

Al eliminar un registro hay algunas diferencias con modificarlo. Al eliminar un registro, este no sepuede ir hasta que toda sesion que lo pueda necesitar haya terminado. Al eliminar un registro, esteno cambia el registro en si, sino su informacion de visibilidad para que ya no sea visible por otrassesiones.

33

Ejercicios:

1. Crear una tabla llamada tabla transacciones.

2. Crear tres registros en la tabla.

3. Abrir dos sesiones con el mismo usuario.

4. Crear una transaccion en cada sesion, ambas deben actualizar el mismo registro. Observar elresultado.

5. Crear otras dos transacciones, una debe eliminar un registro y la otra actualizarlo.

6. Crear dos transacciones de tipo SERIALIZABLE, en una agregar un registro y en la otraeliminar un registro (diferentes).

7. Crear dos transacciones de tipo REPEATABLE READ, en ambas eliminar diferentes registros.

8. Crear dos transacciones, una de tipo REPEATABLE READ y la otra con el nivel por defecto.La primera debe leer una tabla y la segunda debe insertar registro en esa tabla.

34

Captulo 4

Ajustes de rendimiento

PostgreSQL es una gran plataforma sobre la cual desarrollar aplicaciones y soluciones empresariales,pero optimizar el rendimiento no ha sido una tarea facil. Se necesitan adecuadas reglas empricaspara comenzar as como supervision y mantenimiento para mantener el sistema ejecutandose sinproblemas y con el rendimiento esperado.

En general para ajustar el rendimiento de una Base de Datos en PostgreSQL se siguen los siguientespasos:

Seleccionar el hardware donde ejecutar la BD, idealmente se debe probar el hardware paraestar seguro de que su rendimiento es el esperado.

Configurar toda la implementacion del hardware y software de disco: nivel RAID, sistema dearchivos.

Optimizar la configuracion del Servidor.

Supervisar el rendimiento del servidor y cuan bien se estan ejecutando las consultas.

Mejorar las consultas para que se ejecuten de forma mas eficiente y agregar ndices para ace-lerarlas.

Introducir pool de conexiones y cache.

Replicar la data en multiples servidores y distribuir las lecturas entre todos.

Partir grandes tablas en secciones, eventualmente, las mas grandes pueden ser divididas entrevarios servidores.

4.1. Configuracion de Hardware

Una razon importante para utilizar un gestor de codigo abierto como PostgreSQL es que cada dolarahorrado en el software se puede invertir en hardware. Los tres principales componentes a consideraren el presupuesto son CPU, memoria y discos.

35

4.1.1. CPU

Actualmente los CPUs tienen dos o mas nucleos, lo cual lleva a los desarrolladores a preguntar doscuestiones esenciales:

1. Que familia de procesadores es mejor?

2. Que es mejor?, mas nucleos o nucleos mas rapidos?

En general se considera que Intel produce nucleos mas rapidos y AMD provee mas nucleos por dolary sobre todo sus servidores tienen capacidad para tener mayor cantidad de nucleos que los de lacompetencia.

Para saber si se requieren mas nucleos o mas rapidos es necesario supervisar los procesos existentesen el sistema. Si hay pocos procesos utilizando cada uno un CPU, entonces el servidor se beneficiariaen tener nucleos mas rapidos, esto tiende a pasar cuando se tienen procesos batch donde se procesangrandes cantidades de data. Pero si estan activos todos los CPUs con muchos procesos concurrentes,entonces el sistema mejorara su rendimiento utilizando mas nucleos, lo cual es comun en aplicacionescon gran cantidad de usuarios accediendo a la BD.

Otra situacion donde es util tener nucleos mas veloces es al exportar o importar grandes cantidadesde datos de la BD, proceso en el cual el cuello de botella puede ser la CPU.

4.1.2. Memoria

Cuanta memoria es necesaria para para una aplicacion depende del tamano de la data de trabajocon el cual se ejecutan el comun de operaciones. Generalmente agregar RAM mejora el rendimientopero hay casos en los que no:

Si el conjunto de data es suficiente para entrar en la RAM existente, mas RAM no va a producirgrandes beneficios, mejor rendimiento se va a obtener agregando procesadores mas veloces.

Cuando la data es tan grande que no puede entrar en cantidad de memoria alguna, por lo quees mas util aumentar la velocidad de los discos.

Aumentar la memoria ayuda as no toda la data entre, ya que el cache aumenta y por lo tanto losbloques mas utilizados van a permanecer en memoria, aumentado as el rendimiento.

4.1.3. Discos

Los discos SAS son mas confiables y rapidos que los SATA.

Los discos enterprise SATA son mejores para RAID ya que informan rapidamente de los errores yel controlador RAID reconstruye la data errada. Los discos SAS en cambio procuran arreglar por simismos el problema, ralentizando el arreglo de discos.

Es buena practica poner en produccion solo discos que tengan tiempo en el mercado de tal maneraque sus fallas ya hayan sido reportadas y corregidas.

Los discos de estado solido son mas veloces que los discos magneticos tradicionales, sobre todo albuscar informacion, pero aun no tienen la capacidad de almacenamiento de los discos magneticos yson considerablemente mas caros.

36

4.2. Configuracion del S.O.

La configuracion del Sistema Operativo para soportar una base de datos PostgreSQL incluye elsistema de archivos y los parametros de disco.

4.2.1. Hdparm

En el caso de los *nix, para hacer mas seguro el sistema para una Base de Datos, se debe inhabilitarel cache de escritura mediante el comando:

sudo hdparm -W 0 /dev/sda

4.2.2. Sistemas de archivos

La escritura en sistemas de archivos tienen dos componentes principales:

Los bloques de datos que se escriben en el disco.

La metadata del sistema de archivos.

Al agregar un bloque de data en un archivo existente, se llevan a cabo las siguientes operaciones:

Agregar la informacion del nuevo bloque a la metadata de espacio de disco utilizado.

Escribir el bloque de data.

Escribir la metadata del archivo referenciando el uso del nuevo bloque.

En caso de que se caiga el sistema a mitad de la operacion, no se va a sobrescribir el nuevo bloquedebido a que desde un inicio fue tomado en cuenta como un bloque utilizado.

4.2.3. Journaling

La forma actual de trabajar de los sistemas de archivos es mediante journaling, tecnica por la cual seescribe el inicio y fin de cada escritura en el journal, se escribe la metadata del sistema de archivosy del archivo, el bloque de datos y su utilizacion.

El journaling es algo pesado debido a que por cada operacion en disco se debe escribir varias vecesen el journal. Este se lleva a a cabo de la siguiente manera:

Escribe la metadata del inicio de la transaccion en el journal.

Escribe la metadata del cambio de espacio utilizado en el journal.

Escribe el cambio en el bloque de datos en el journal.

Escribe el cambio en la metadata del archivo en el journal.

Agregar el bloque de data a la metadata de la lista de espacio utilizado.

Escribe el bloque de data.

Escribe metadata del archivo referenciando el uso del bloque.

37

Escribe la metadata del fin de la transaccion en el journal.

Con estas operaciones se obtiene la habilidad de recuperar el sistema de cualquier caida. Si no se llegaa terminar una transaccion de escritura el sistema de archivos puede ignorar o deshacer cualquiertrabajo parcial hecho hasta el momento.

Este enfoque hace que sea pesado utilizar el journaling completo con una base de datos, ya que todatransaccion es escrita cuatro veces. Para evitar la sobrecarga de trabajo, con la base de datos seutiliza solo journaling de la metadata, ya que PostgreSQL se encarga de la integridad de su data.

4.2.4. Sistemas de archivos de Linux

Ext

Las versiones modernas de Ext (3 y 4) tienen tres niveles de journaling, que se definen como opcionesal montarse el sistema de archivos:

data=writeback : Los cambios en la data no son guardados en el journal, solo los cambios en lametadata, pero el orden en que son guardados relativo a los bloques de data no es garantizado.Despues de una caida de sistema, los archivos pueden quedar con basura al final por escriturasincompletas y se puede tener una mezcla de data vieja y nueva sobre los archivos.

data=ordered : La metadata es guardada en el journal pero no los cambios en la data, peroen todos los casos la metadata es escrita solo despues de que los bloques de data hayan sidoescritos. Despues de una cada del sistema, no van a haber archivos de tamanos incorrectos.

data=journal : Journaling completo Los cambios en la data y la metadata son escritos en eljournal antes de que el sistema de archivos sea tocado.

Debido a que realiza su propio chequeo de la integridad de los datos, en PostgreSQL no es necesarioutilizar journaling completo, por lo que la opcion ordered va a permitir mayores velocidades.

El tamano maximo para las particiones es de 16TB y para los archivos es de 2TB en ext3, lmite quebusca ser superado por ext4.

XFS

XFS es mas veloz que los ext debido a que fue disenado para ser eficiente en el journaling, pero soloregistra los cambios de la metadata en el journal, por lo que se parece al modo writeback de ext3 yes considerado inseguro. Para superar este problema se debe colocar:

full page writes = on

en postgresql.conf.

XFS tiene la ventaja de que es mas eficiente al ser utilizado con RAID y ademas puede soportararchivos de mas de un millon de TB, muy superior a los sistemas de archivos ext.

4.2.5. Configuracion del sistema de archivos en Linux

Sin importar el sistema de archivos que se utilice, existen configuraciones que pueden mejorar surendimiento.

38

Read ahead

El primer parametro a configurar en Linux es el read-ahead de los dispositivos de almacenamiento.Cuando se hacen lecturas secuenciales que se mueven hacia adelante, esta funcionalidad hace queel Sistema Operativo solicite los bloques del disco antes de que la aplicacion los solicite, ahorrandoas mucho tiempo.

Para ver el estado actual de read-ahead se ejecuta el comando:

$ blockdev getra /dev/sda

Para mejorar este parametro se le aumenta a un valor entre 4096 y16384 mediante el siguiente co-mando:

$ blockdev setra 4096 /dev/sda

Tiempo de acceso a los archivos (File access times)

Cada vez que se accede a un archivo en Linux, se actualiza un atributo del archivo llamado el tiempodel ultimo acceso (atime). Esta sobrecarga se vuelve grande cuando se hacen muchas lecturas de unarchivo, lo cual no es deseable.

Se puede eliminar este comportamiento agregando el parametro noatime a las opciones de montajeen /etc/fstab.

/dev/sda1 ext3 noatime,errors=remount-ro 0 1

Cache de lectura e intercambio de paginas

Linux procura utilizar todo espacio extra de RAM para poner en cache el sistema de archivos, al igualque PostgreSQL, compitiendo ambos por el recurso. Cuando el sistema tiene poca RAM, debe optarpor reducir el tamano del cache o incrementar el intercambio de paginas con el disco (swapping).Este comportamiento est controlado por el parametro swappiness del kernel.

Para ver el valor actual del parametro, se accede a /proc/sys/vm/swappiness mediante el comando:

sudo less /proc/sys/vm/swappiness

Y la forma mas facil de ajustarlo es agregar la siguiente lnea en /etc/sysctl.conf:

vm.swappiness=0

El valor de 0 disminuye el tamno del cache del S.O en vez de aumentar el swapping, lo cual redundaen un incremento de la perfomance en la mayora de casos.

Otro parametro relacionado es el que controla la tendencia de Linux a permitir que los procesosseparen mas memoria de la que necesitan, la cual se puede desactivar mediante el parametro en/etc/sysctl.conf:

vm.overcommit memory=2

39

4.2.6. BSD

Los sistemas BSD, en especial FreeBSD han sido conocidos por su alta calidad, sobre todo en laimplementacion de servidores. Entre los sistemas de archivos de FreeBSD, ZFS es el mas utilizado.

ZFS

En ZFS, por defecto se utilizan registros de 128KB de tamano, lo cual es util cuando se leen grandescantidades de informacion pero ineficiente cuando las lecturas son mas pequenas y al azar.

En caso de que las lecturas sean de pequenas cantidades de informacion al azar, se recomienda reducirel tamano del registro de ZFS para igualarlo al de PostgreSQL de 8KB. Para llevarlo a cabo se utilizael siguiente comando:

$ zfs set recordsize=8K zp1data

Esta configuracion se debe hacer antes de crear ninguna Bd en el dispositivo de almacenamiento.

ZFS tiene funcionalidades que lo hacen muy apto para las bases de datos como implementar sumas decomprobacion en toda lectura y escritura de bloques de datos y mayor velocidad para copiar grandescantidades de informacion.

4.3. Memoria para el cache de la Base de Datos

Cuando se inicia un servidor PostgreSQL, este reserva una cantidad fija de bloques de memoria. Adi-cionalmente todo cliente que se conecta utiliza una cantidad de memoria, aumentandola a medida queel cliente utiliza recursos y realiza operaciones como ordenamientos y guarda data de transaccionesen espera del commit.

Algunos parametros de la base de datos pueden ser definidos por los clientes a medida que se co-nectan. Por ejemplo, el parametro work mem limita la cantidad de memoria que puede ser utilizadapara ordenamiento y puede ser incrementado por el cliente luego de conectarse, utilizando memoriano ocupada por otros procesos.

El mayor componente de la memoria compartida es la cache, la que se define por un parametrollamado shared buffers. Monitorear y optimizar como se usa esta memoria es el objetivo de la presenteseccion.

4.3.1. Unidades de memoria en postgresql.conf

Para indicar la memoria que se quiere asignar a un parametro hay que especificar la unidad de me-moria, por ejemplo, si se desea especificar el tamano del parametro wal buffers que controla cuantamemoria utiliza usar para el buffer del WAL, se escribe lo siguiente en postgresql.conf:

wal buffers = 64 KB

La base de datos internamente convierte el valor en sus propias unidades internas, que para esteparametro son bloques de 8K.

40

La vista pg settings de la base de datos sirve para ver las configuraciones y la funcion current setting()puede ser usada para mostrar informacion sobre los parametros, al igual que SHOW, pero ademaspuede ser utilizada en una consulta.

Por ejemplo:

show wal_buffers;

SELECT name,setting,unit,current_setting(name) FROM pg_settings

WHERE name=wal_buffers;

4.3.2. Cache de la Base de Datos

El cache de la base de datos es el espacio de memoria donde PostgreSQL almacena los resultados delas consultas mas utilizadas, de tal manera que pueda responder con mayor velocidad a las nuevasconsultas que se presenten.

Como regla general se puede establecer que darle un 25 % de la memoria RAM del sistema al parame-tro shared buffers es un numero razonable para el cache de la base de datos.

En caso de que la memoria RAM supere los 8GB de y la version de PostgreSQL en ejecucion seauna de 32 bits, no se aconseja incrementar shared buffers sobre los 2GB debido a que se puede sinmemoria virtual.

Se puede explorar el cache de la Base de Datos utilizando el modulo pg buffercache, uno de los demodulos contrib disponibles con PostgreSQL. En un servidor de produccion no es vital pero sirvepara aprender como funciona la base de datos con su memoria compartida con el fin de aprender aoptimizarlo.

4.4. Configuracion (postgresql.conf)

Las principales opciones de configuracion de PostgreSQL estan en el archivo postgresql.conf. Al cam-biar las configuraciones se puede requerir reiniciar el servidor o recargar el archivo de configuracion.

Cada parametro de configuracion tiene un contexto asociado donde puede ser cambiado. Para sabercual es, se consulta a la base de datos, por ejemplo:

select name,context from pg_settings;

Los contextos son los siguientes:

internal: Son parametros sobre todo internos establecidos en tiempo de compilacion. No puedenser cambiados sin recompilar el servidor.

postmaster Solo se actualizan reiniciando el servidor. Todos los parametros referidos a me-moria son de este tipo.

41

sighup: Enviar al servidor una senal HUP va a causar que recargue postgresql.conf y todos loscambios hechos a estos parametros estaran inmediatamente activos.

backend: Estos parametros son similares a los de sighup excepto que los cambios hechos novan a afectar a las sesiones ejecutandose.

superuser: Pueden ser modificados por cualquier superusuario en cualquier momento y seactivan sin recargar. La mayora de parametros en este esquema se refieren a la configuracionde los logs del sistema.

user: Las sesiones individuales pueden ajustar estos parametros en todo momento. Sus cambiossolo impactaran en la sesion. La mayora de estos parametros alteran como se ejecutan lasconsultas.

Para reiniciar el servidor se debe reiniciar el servicio, lo cual vara en cada S.O. En ubuntu y otrosS.O de la familia debian se utiliza el siguiente comando:

sudo /etc/init.d/postgresql restart

Para recargar el archivo postgresql.conf en el servidor, es necesario conectarse como superusuario yejecutar la funcion pg reload conf:

postgres=# SELECT pg reload conf();

Se puede tambien enviar una senal HUP utilizando el comando kill.

ps -eaf grep postgres -D

$ kill -HUP 11185

El anterior comando envia la senal HUP al servidor postgresql de id 11185.

4.4.1. postgresql.conf

Algunos de los parametros mas importantes son:

listen addresses: Indica a PostgreSQL que ips escuchar, por defecto es localhost pero se puedeindicar una lista de ips o * para indicar todas.

Port: Por defecto es 5432 pero puede ser cambiado por otro.

max connections: Es el maximo numero de conexiones permitidas. Como cada conexionutiliza una pequena cantidad de memoria, es posible para sistemas con poca memoria nopermitir tantas conexiones. Es importante no establecer este parametro muy por encima delo que se necesita. Se desperdicia memoria compartida, lo cual es muy oneroso en caso de quesea pequena.

shared buffers: define la cantidad de memoria que se comparte a traves de todas las conexionespara guardar las paginas recientemente accedidas. Tiene efecto sobre todo en la perfomance delas consultas. Es deseable que este a un valor alto, por lo menos un 25 % de la memoria.

42

effective cache size: Es un estimado de cuanta memoria se espera este disponible en loscaches del SO y PostgreSQL. Es utilizado por el planificador de consultas para deducir si losplanes considerados entraran en la RAM o no. Si se tiene un servidor dedicado este valor debeser un 50 % de la RAM.

work mem: Controla la cantidad maxima de memoria asignada a cada operacion como orde-namiento, joins, etc. La cantidad optima de memoria depende del tipo de trabajo que se llevea cabo, la cantidad de memoria de la que se disponga entre otros. Si el tipo de trabajo es ligeroentonces este valor debe ser bajo, caso contrario debe ser mas alto. Es una de las maneras masefectivas de incrementar la velocidad del servidor. La forma generica de asignarlo es considerarcuanto RAM hay fuera de shared buffers dividida entre max connections y tomar un porcentajedel resultado, la mitad sera una cantidad elevada.

maintenance work mem: Es el total de memoria asignada para labores de mantenimiento.Aproximadamente cada proceso de mantenimiento (VACUUM, CREATE INDEX, ALTERTABLE ADD FOREIGN KEY) no necesita mas de del 5 % de la RAM, lo que es 50MB porcada GB de memoria.

wal buffers: El valor por defecto es de 64KB, lo cual es muy bajo para los tamanos actualesde la RAM, por lo que incrementarlo a 16MB es normal actualmente.

effective cache size: Al hacer operaciones que requieren gran uso de memoria, la base dedatos compara su tamano con el de todos los caches reunidos (propio y del Sistema operativo).Este parametro no reserva memoria, solo sirve para que la Base de datos compare el tamanode las operaciones a realizar para decidir el curso de accion. En UNIX se calacula sumando losvalores de free y cached que se ven en los comandos free o top y sumando ademas el valor deshared buffers.

4.4.2. Configuracion de logs

La generacion de Logs es importante debido a que permite conocer las acciones de la BD y los usua-rios. Dependiendo del nivel de Logs, se va a generar mayor o menor cantidad de informacion quepermitira conocer las acciones de la Bd y sus usuarios en un periodo determinado de tiempo.

El archivo de logs generalmente esta en /var/log en los sistemas basados en Unix.

La configuracion de los logs se realiza en postgresql.conf (generalmente localizado en /etc/postgresqlen maquinas *nix).

Las configuraciones por defecto de logs en postgresql.conf son las siguientes:

La opcion de configuracion log destination dirige los errores hacia la salida estandar, se puedenredirigir hacia un archivo utilizando pg ctl -l al iniciar el servidor.

log destination = stderr:

El parametro logging collector si se pone en off significa que no se quiere recoger la salida de erroresestandar para escribirla en otro sitio. Si se pone en on se crea un archivo de logs por da.

logging collector = off

El parametro log line prefix se puede configurar para agregar datos al inicio de toda lnea del log. Siesta vaco no se agrega nada.

43

log line prefix =

El parametro log directory indica el directorio donde se crean los logs.

log directory = pg log

Y log filename es el formato del nombre de los archivos de logs de Postgresql, utilizando fecha y hora.

log filename = postgresql- %Y- %m- %d %H %M %S.log

El sistema operativo no se encarga del mantenimiento de los logs, por lo que el administrador del siste-ma sera el responsable de hacerlo, de preferencia utilizando algun metodo automatico como un script.

Detalle de cada parametro de configuracion:

log line prefix

Este parametro de configuracion esta vaco por defecto, se le pueden anadir parametros para que seagreguen al inicio de

148607138 clases postgresql

Documents