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 estadısticas 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 librerıas que les permiten comunicarse con Post-gresql y hacer consultas, entre los mas conocidos PHP, Python, Ruby, Java, .Net, Perl entre otros.

Hoy en dıa, 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

Capıtulo 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 varıa entre 8KB y 32KB, las cuales se guardan en diferentes posiciones fısicas 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 key”de 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 vıapara 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 fısica 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 librerıas 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.

• \q

Eliminamos 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 explain’custom variable classes = ’auto explain’auto 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

Capıtulo 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 fısica 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 especıficamente 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 caracterıstica 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 explıcitamente 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 bueno“hardcodear” 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 postgres”Mediante 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 explıcita 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

Capıtulo 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 una“instantanea” 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 deberıa 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;



-- 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 podrıa ser la siguiente:

BEGIN;



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 envıo 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 envıen o se descarten. Si loscambios de la transaccion anterior se envıan, 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 caracterıstica 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 garantıa 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. Lagarantıa 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 mınimo 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 lımite 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 vacıo 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 garantıade 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 inıciese una transaccion mediante Begin y actualıcese el registro pero no seenvıe (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

Capıtulo 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 empıricaspara 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 mejorarıa 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 caıda 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, lımite 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 lınea 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 mayorıa 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 mayorıa 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 mayorıa de estos parametros alteran como se ejecutan lasconsultas.

Para reiniciar el servidor se debe reiniciar el servicio, lo cual varıa 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 serıa 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 dıa.

logging collector = off

El parametro log line prefix se puede configurar para agregar datos al inicio de toda lınea del log. Siesta vacıo 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 vacıo por defecto, se le pueden anadir parametros para que seagreguen al inicio de cada lınea del log.

%t: Timestamp

%u: Database user name

%r: Remote host connection is from

%d: Database connection is to

%p: Process ID of connection

Aunque al inicio no se sepa porque se desean estos valores, son muy utiles para rastrear comporta-mientos.

Para hacer los logs de postgreSQL compatibles con pgFouine (una herramienta para analizar logs)se puede utilizar alguna de estas lıneas.

log line prefix = ’ %t [ %p]: [ %l-1]’

log line prefix = ’ %t [ %p]: [ %l-1] user= %u,db= %d’

log line prefix = ’ %t [ %p]: [ %l-1] user= %u,db= %d,remote= %r’

log statement

Para decidir el detalle del log que se va a guardar se utiliza el parametro de configuracion log statement.

None: ningun log

ddl: Solo data definition language como create y drop.

Mod: Registra todas las acciones que implican cambio de un valor, lo cual en la practica estodo menos las consultas SELECT.

All: Toda accion, es en general impractico ya que genera un crecimiento muy rapido del tamanodel log (he tenido logs que en dıas alcanzaron los 20GB).

44

log min duration statement

En caso se desee solo registrar transacciones que esten durando mas de lo esperado, se puede utilizarla opcion de configuracion log min duration statement para registrar solo registrar las acciones queduren mas de una cantidad de tiempo medido en milisegundos.

Por ejemplo, log min duration statement=1000 significa que se registraran las acciones que durenmas de un segundo, muy util para ubicar problemas de perfomance.

Logs en CSV

Un formato reciente (presente desde postgreSQL 8.3) son los logs guardados como archicos csv. Estetipo de logs permiten que se puedan analizar con diversas herramientas e incluso importarlos en lamisma BD para llevar a cabo su analisis mediante consultas SQL.

Para activar esta funcionalidad, se debe ajustar el destino de los logs y poner en on el colector.

log destination = ’csvlog’logging collector = on

Luego de efectuar los cambios, se debe reiniciar el servidor y de ahı en adelante los logs se guardaranen archivos .csv en vez de .log.

Una vez generado un log en csv, secrea la tabla postgres log con la estructura del csv:

CREATE TABLE postgres_log

(

log_time timestamp(3) with time zone,

user_name text,

database_name text,

process_id integer,

connection_from text,

session_id text,

session_line_num bigint,

command_tag text,

session_start_time timestamp with time zone,

virtual_transaction_id text,

transaction_id bigint,

error_severity text,

sql_state_code text,

message text,

detail text,

45

hint text,

internal_query text,

internal_query_pos integer,

context text,

query text,

query_pos integer,

location text,

application_name text,

PRIMARY KEY (session_id, session_line_num)

);

Y se importa a la Bd mediante:

COPY postgres_log FROM

’/home/postgres/data/pg_log/postgresql-2010-03-28_215404.csv’ WITH CSV;

Una vez importado se pueden ejecutar queries que lo analicen, como por ejemplo:

SELECT min(log_time),max(log_time) FROM postgres_log WHERE command_tag=’COMMIT’;

Comando sql que muestra el primer y ultimo commit enviado a la BD.

Analisis de Logs

Una vez se tienen los logs se necesita analizarlos para conocer la perfomance de nuestra BD.

pg stat statements:

Es un modulo contrib que se instala utilizando:

CREATE EXTENSION pg_stat_statements;

Y se habilita configurando el siguiente parametro en postgresql.conf.

shared preload libraries = ’pg stat statements’

Luego de reiniciar el servidor y ejecutar consultas, la vista pg stat statements se va a llenar coninformacion la cual puede ser consultada como cualquier otra vista.

SELECT total_time, query FROM pg_stat_statements ORDER BY total_time DESC;

46

4.5. Configuracion de un nuevo servidor

La optimizacion inicial de un servidor puede ser un proceso mecanico:

Ajustar los logs para ser mas verbosos.

Utilizar un 25 % para shared buffers.

Estimar generosamente el maximo numero de conexiones, ya que a partir de ese numero elsistema rechazara nuevas.

Iniciar el servidor y asignar effective cache size sumando shared buffers y la cache del SistemaOperativo.

Calcular work mem dividiendo el cache del S.O entre max connections y luego entre dos.

Establecer maintenance work mem a un valor de 50MB por cada GB de RAM.

Incrementar wal buffers a 16 MB.

4.5.1. Efectos del cache de la BD

Es importante resaltar que cuando se ejecuta una consulta varias veces, las consultas posteriores vana ser mas rapidas debido a que los resultados estan en la cache de la base de datos.

Para eliminar el efecto del cache al probar varias consultas con un mismo fin, se debe ejecutar variasveces las consultas de tal modo que la data este en cache y ası deje de impactar en la comparacion.

4.6. Indices

Un ındice es una lista organizada de valores que aparece en una o mas columnas de una tabla. Si sedesea un subconjunto de los registros de la tabla, una consulta puede usar el ındice para determinarque filas son las que busca en vez de examinar cada fila. Ademas, debido a que un ındice tiene unorden, sirven para incrementar la velocidad del ordenamiento de los registros de una tabla.

Los ındices ayudan a la base de datos a disminuir la cantidad de data que necesita procesar paraejecutar una consulta.

Para crear un ındice en una tabla se ejecuta el siguiente comando:

CREATE INDEX i ON t(v);

Donde i es el nombre del ındice y v es el campo de la tabla con el que se va a crear el ındice.

Los ındices pueden ser muy grandes y su mantenimiento ser costoso en recursos para la base de datos,por lo que su creacion debe estar justificada por importantes mejoras en la velocidad de las consultas.

Mediante EXPLAIN ANALYZE se puede hallar si fue util o no la creacion del ındice.

47

Cuando un campo es definido como Primary Key al ser creada la tabla, automaticamente se le creaun UNIQUE INDEX para asegurar que todos sus valores sean unicos. Para que no se acepten valoresnulos es una buena practica agregar NOT NULL al campo.

48

Ejercicios:

1. Determinar si su servidor necesita mas RAM.

2. Determinar si su servidor necesita mas nucleos o nucleos mas potentes.

3. Determinar que sistema de archivos sera mejor para su servidor.

4. Calcular el tamano ideal de su parametro shared buffers.

5. Calcular el tamano ideal de su parametro work mem.

6. Calcular el tamano ideal de su parametro effective cache size.

7. Ejecute un SELECT sobre una tabla y analice su plan.

8. Haga lo mismo para un UPDATE y un INSERT.

49

Capıtulo 5

Ajustes de rendimiento II: Optimizacion

de las Bases de datos

Para algunos administradores de Bases de datos y desarrolladores, optimizar las consultas es la partemas importante de la optimizacion de una base de datos.

5.1. Data de ejemplo

Para optimizar consultas, se necesita data que analizar, para lo cual se utilizara la base de datosDell Store 2, originalmente creata por Dell y luego portada a PostgreSQL. Se puede descargar dehttp://pgfoundry.org/projects/dbsamples/.

$ wget http://pgfoundry.org/frs/download.php/543/dellstore2-normal-1.0.tar.gz$ tar xvfz dellstore2-normal-1.0.tar.gz$ cd dellstore2-normal-1.0/$ sudo su postgres -c“createdb dellstore2”$ sudo su postgres -c “psql -f dellstore2-normal-1.0.sql -d dellstore2”$ sudo su postgres -c “psql -d dellstore2 -c “VACUUM VERBOSE ANALYZE””

Para conocer el tamano de la base de datos ejecutar:

SELECT pg_size_pretty(pg_database_size(’dellstore2’));

Los ejemplos en este capıtulo se daran a partir de Dell Store 2, a menos que se indique lo contrario. [5]

La estructura de la data es sencilla:

Hay un numero de productos que la tiendas vende, cada uno de los cuales entra en una categorıa.

La tienda tiene clientes.

Los clientes dan ordenes de compra.

Cada orden tiene un numero de lıneas, cada una de las cuales referencia productos siendocomprados.

50

Una historia de cliente es salvada listando todos los productos que el cliente alguna vez haordenado.

5.2. Timing

Para conocer cuanto demora en ejecutarse una consulta se utiliza el comando timing antes de ejecutarla consulta de tal manera que mida el tiempo requerido.

dellstore2=# \timing

dellstore2=# SELECT count(*) FROM customers;

count

-------

20000

Time: 9,245 ms

Para desactivar volver a escribir \timing.

5.3. Explain y Explain Analyze

Para conocer el porque de velocidad de ejecucion de una consulta se utiliza el comando EXPLAINantes de la consulta lo cual muestra el resultado esperado de dicha consulta o query plan (plan de laconsulta).

Si se utiliza EXPLAIN ANALYZE se obtiene la estimacion describiendo lo que espera el planificadorde PostgreSQL y ademas lo que realmente sucede despues de ejecutar la consulta.

Por ejemplo, si se ejecuta:

EXPLAIN ANALYZE DELETE * FROM t;

No solo se va a obtener el plan sino efectivamente se va a eliminar todo el contenido de la tabla t.

5.4. Las consultas y la cache

Cuando la data necesaria para responder a una consulta esta en la cache de la base de datos o delsistema operativo, la consulta se lleva a cabo mas rapido debido a que ya no se necesita recuperar ladata del disco, solo obtenerla de la memoria.

Si se ejecuta dos veces una consulta, la segunda vez va a ejecutarse mas rapido. Cuando la con-sulta empieza a ejecutarse siempre con la misma duracion, significa que su data en la cache se haestabilizado y ya no indice en el resultado. [5]

51

5.4.1. Efecto de la cache

Para identificar el impacto de la cache, se va a limpiar la cache del sistema y luego se volvera aejecutar la consulta:

Limpiar la cache:

$ sudo /etc/init.d/postgresql stop$ sudo su# sync# echo 3 ¿/proc/sys/vm/drop caches# exit$ sudo /etc/init.d/postgresql start

Se ejecuta la consulta:

$ psql -d dellstore2

\timing

SELECT count(*) FROM customers;

count

-------

20000

(1 fila)

Duracion: 350,276 ms

SELECT count(*) FROM customers;

count

-------

20000

(1 fila)

Como se puede apreciar, la cache influencia en gran medida el rendimiento de las consultas en elsistema. Para eliminar el efecto de la cache sobre una consulta, esta debe ser ejecutada varias veceshasta que sus tiempo de ejecucion sean similares.

5.4.2. Estructura del plan de la consulta

La salida del comando EXPLAIN esta organizada en una serie de nodos. Cada lınea de la salida esun nodo. Al mas bajo nivel estan los nodos que analizan tablas e ındices. Los nodos de mas alto nivelcogen la salida de los nodos de bajo nivel y operan sobre esta.

52

Por ejemplo:

# EXPLAIN ANALYZE SELECT * FROM customers;

QUERY PLAN

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

Seq Scan on customers (cost=0.00..676.00 rows=20000 width=268)

(actual time=0.010..27.434 rows=20000 loops=1)

Total runtime: 52.451 ms

(2 filas)

Al ejecutarse con EXPLAIN ANALYZE la duracion aumenta varias veces. En casos de querys mascomplejos la sobrecarga producida por el analisis es menor, aun ası no se debe de confiar completa-mente en los tiempo producidos por EXPLAIN ANALYZE.

Este plan tiene un nodo, un nodo Seq Scan

cost=0.00..676.00: El primer costo es el costo de iniciar el nodo. Esto es cuanto trabajo esestimado antes de que este nodo produzca su primera fila de salida. En este caso es 0 ya queuna busqueda secuencial devuelve resultados de forma inmediata. El segundo numero es elcosto estimado de ejecutar todo el nodo hasta que termine.

rows=20000: El numero de filas que este nodo espera como salida.

width=268 : El numero esperado de bytes de cada fila de la salida.

Los numeros actuales muestran como realmente se llevo a cabo la consulta:

actual time=0.011..34.489: El actual costo de inicio no fue cero, se necesito una pequena fraccionde tiempo para empezar. Una vez que empezo necesito 34.489 segundos para terminar.

Rows=20000: Tal como se esperaba fueron 20000 filas.

loops=1: Algunos nodos, como los que hacen joins, se ejecutan mas de una vez. En ese caso elvalor de loops va a ser mas de uno y el tiempo y la cantidad de filas van a ser referidas a cadaloop no al total.

5.4.3. Costo de computacion

La labor basica del optimizador de consultas es generar planes que puedan utilizarse para ejecutaruna consulta y escoger el que tenga el menor costo de ejecucion. El costo es hecho de forma arbitraria.

seq page cost: Cuanto demora leer una sola pagina desde el disco en forma secuencial. El restode parametros son relativos a este cuyo valor es 1.0.

random page cost: El costo de lectura cuando los registros estan dispersos por varias paginas.El valor por defecto es 4.0.

cpu tuple cost: Cuanto cuesta procesar un solo registro, su valor por defecto 0.1.

53

cpu index tuple cost: El costo de procesar una sola entrada de un ındice. El valor por defectoes 0.005, menor de lo que cuesta procesar un registro debido a que los registros tienen muchamas informacion de cabecera (como xmin y xmax ).

cpu operator cost: El costo esperado de procesar una funcion o un operador (la suma de dosnumeros por ejemplo) y por defecto es 0.0025.

Se puede comparar en una tabla:

Parametro Valor por Defecto Velocidad relativa

seq page cost 1.0 Reference

random page cost 4.0 4X slower

cpu tuple cost 0.01 100X faster

cpu index tuple cost 0.005 200X faster

cpu operator cost 0.0025 400X faster

Todos estos valores se pueden encontrar en postgresql.conf.

Se pueden utilizar estos numeros para calcular el costo mostrado en el ejemplo previo. Una busquedasecuencial de la tabla “customers” debe leer cada pagina en la tabla y procesar cada registro. Sepueden combinar los costos internos utilizados por PostgreSQL con las estadısticas utilizadas por eloptimizador.

SELECT

relpages,

current_setting(’seq_page_cost’) AS seq_page_cost,

relpages *

current_setting(’seq_page_cost’)::decimal AS page_cost,

reltuples,

current_setting(’cpu_tuple_cost’) AS cpu_tuple_cost,

reltuples *

current_setting(’cpu_tuple_cost’)::decimal AS tuple_cost

FROM pg_class WHERE relname=’customers’;

Se obtiene:

relpages | seq_page_cost | page_cost | reltuples | cpu_tuple_cost | tuple_cost

----------+---------------+-----------+-----------+----------------+------------

476 | 1 | 476 | 20000 | 0.01 | 200

54

Agregar el costo de leer la pagina (page cost) al costo de procesar los registros (tuple cost) y seobtiene 676, el costo mostrado por EXPLAIN.

Los parametros de costo se pueden configurar, una configuracion comun cuando se sabe que granparte de la base de datos puede entrar en la memoria es reducir random page cost a 2, debido a que unelevado porcentaje de las paginas esta en la cache y es encontrada con mayor facilidad que en el disco.

Para saber que columnas se estan utilizando en una consulta, se puede utilizar el modo verboso:

EXPLAIN VERBOSE SELECT * FROM customers;

5.5. Optimizacion de consultas

Para optimizar las consultas en primer lugar se explicara como mejorar el rendimiento de la obtencionde los registros y luego de las operaciones con los registros.

5.5.1. Armando conjuntos de registros

Para optimizar la seleccion de los registros buscados por una consulta se pueden tomar en cuentadiversas optimizaciones.

Id de los registros

Todo registro tiene un Id, el que se puede ver en la columna ctid. Se puede utilizar en una mismatransaccion para referirse a un registro que se repite varias veces y ası acelerar su busqueda. Noes util en diferentes transacciones ya que puede cambiar con una actualizacion. Tambien sirve paradistinguir entre registros identicos, por ejemplo al eliminar registros duplicados.

Un ejemplo de ctid:

EXPLAIN SELECT customerid FROM customers WHERE ctid=’(0,1)’;

QUERY PLAN

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

Tid Scan on customers (cost=0.00..4.01 rows=1 width=4)

TID Cond: (ctid = ’(0,1)’::tid)

Indices

Cuando se ejecutan consultas que incluyen LIMIT es util buscar mediante un campo que este inde-xado, ya que ası el ordenamiento sera mas rapido.

55

5.5.2. Procesando los nodos

Una vez que se tiene un conjunto de registros, el siguiente tipo de nodo que se va a encontrar cuandose usa una sola tabla son aquellos que procesan el conjunto de varias formas. Estos nodos por logeneral cogen un conjunto de registros y devuelven otro.

Ordenamiento - Sort

Nodos de ordenamiento aparecen cuando se utiliza ORDER BY en las consultas:

EXPLAIN ANALYZE SELECT customerid FROM customers ORDER BY zip;

QUERY PLAN

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

Sort (cost=2104.77..2154.77 rows=20000 width=8)


Sort Key: zip

Sort Method: external sort Disk: 352kB

-> Seq Scan on customers (cost=0.00..676.00 rows=20000 width=8)



Las operaciones de ordenamiento se pueden ejecutar en memoria (mas rapido) o en disco (mas lento).En el ejemplo podemos ver que a pesar de la pequena cantidad de data (352kB) esta ha sido calculadaen el disco. Aun cuando es menor que el valor por defecto de work mem (1MB), el algoritmo quick-sort (utilizado en la memoria) necesita mas memoria que la utilizada en el disco duro al combinararchivos previamente ordenados. Si el parametro work mem es incrementado, la operacion se lleva acabo en memoria. [5]

SET work_mem=’2MB’;

EXPLAIN ANALYZE SELECT customerid FROM customers ORDER BY zip;

QUERY PLAN

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

Sort (cost=2104.77..2154.77 rows=20000 width=8)


Sort Key: zip

Sort Method: quicksort Memory: 1294kB



56


Se puede apreciar que el valor por defecto no era suficiente y se necesitaba aumentar work mem paraque la operacion se llevara a cabo en la memoria.

Funciones de agregacion - Aggregate

Las funciones de agregacion reciben una serie de valores y producen una sola salida. Algunos ejmeplosson AVG(), COUNT(), EVERY(), MIN(), MAX(), STDDEV() y SUM(). Para calcular una funcionde agregacion, se leen todos los registros y luego se pasan por por el nodo agregado para calcular elresultado:

EXPLAIN ANALYZE SELECT max(zip) FROM customers;

QUERY PLAN

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

Aggregate (cost=726.00..726.01 rows=1 width=4)





No siempre este el caso, al utilizar ındices el tiempo disminuye considerablemente:

EXPLAIN ANALYZE SELECT max(customerid) FROM customers;

QUERY PLAN

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

Result (cost=0.03..0.04 rows=1 width=0)


InitPlan 1 (returns $0)

-> Limit (cost=0.00..0.03 rows=1 width=4)


-> Index Only Scan Backward using customers_pkey on customers

(cost=0.00..534.25 rows=20000 width=4)


Index Cond: (customerid IS NOT NULL)

Heap Fetches: 0

57

Total runtime: 0.120 ms $

5.5.3. Joins

Las tareas mas complejas del planificador de consultas son las que tienen que ver con unir tablasentre si. Cada vez que se agrega una tabla al conjunto que se le aplicara join, el numero de posibi-lidades crece de forma dramatica. Si solo son tres tablas, el planificar considerara todo posible planpara seleccionar el optimo, pero si son veinte tablas, la cantidad de posibilidades es muy grande paraconsiderarlas todas. [5]

En un CROSS JOIN se multiplican todos los registros de una tabla por los de otra, multiplicandode forma anidada cada registro por toda la otra tabla. En pseudocodigo:

for each outer row:

for each inner row:

if join condition is true:

output combined row

Se puede observar algo similar en data real si es que se unen dos tablas sin un WHERE de por medio.

EXPLAIN ANALYZE SELECT * FROM products,customers;

QUERY PLAN

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

Nested Loop (cost=0.00..2500899.00 rows=200000000 width=317)




-> Materialize (cost=0.00..248.00 rows=10000 width=49)


-> Seq Scan on products (cost=0.00..198.00 rows=10000 width=49)



Bucle anidado con busqueda mediante ındices

Lo mas comun en un bucle anidado donde una tabla solo esta devolviendo un numero limitado deregistros. Si existe un ındice en una de las tablas, el optimizador lo utilizara para limitar el numerode registros y hacer el tiempo de ejecucion menor.

58

Considerar el caso donde se esta buscando una sola orden utilizando un campo sin ındice (por lo quela busqueda debe ser secuencial) pero haciendo un join con su respectiva orden de compra.

EXPLAIN ANALYZE SELECT * FROM orders,orderlines WHERE

orders.totalamount=329.78 AND orders.orderid=orderlines.orderid;

QUERY PLAN

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



-> Seq Scan on orders (cost=0.00..244.00 rows=1 width=30)


Filter: (totalamount = 329.78)

Rows Removed by Filter: 11999

-> Index Scan using ix_orderlines_orderid on orderlines

(cost=0.00..15.36 rows=5 width=18)


Index Cond: (orderid = orders.orderid)


En caso de que ambas tablas utilicen un ındice la consulta es aun mas rapida:

EXPLAIN ANALYZE SELECT * FROM orders,orderlines WHERE orderlines.

orderid=1000 AND orders.orderid=orderlines.orderid;

QUERY PLAN

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



-> Index Scan using orders_pkey on orders (cost=0.00..8.27 rows=1 width=30)


Index Cond: (orderid = 1000)

-> Index Scan using ix_orderlines_orderid on orderlines

(cost=0.00..15.34 rows=5 width=18)


59

Index Cond: (orderid = 1000)


Merge Join

Este tipo de join requiere que ambas entradas esten ordenadas, luego busca a traves de las dos enorden moviendose un registro a la vez a traves de las tablas mientras los valores las columnas utili-zadas para el join cambian. Una tabla puede ser revisada varias veces si la tabla por la que se buscatiene valores repetidos.

Para ver un merge join se necesita una condicion de igualdad como la mostrada en un reporte dondese indica cuanto ha comprado cada cliente:

EXPLAIN ANALYZE SELECT C.customerid,sum(netamount) FROM customers C,

orders O WHERE C.customerid=O.customerid GROUP BY C.customerid;

QUERY PLAN

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

GroupAggregate (cost=0.03..1536.49 rows=12000 width=10)


-> Merge Join (cost=0.03..1356.49 rows=12000 width=10)


Merge Cond: (c.customerid = o.customerid)

-> Index Only Scan using customers_pkey on customers c

(cost=0.00..484.25 rows=20000 width=4)


Heap Fetches: 0

-> Index Scan using ix_order_custid on orders o

(cost=0.00..672.24 rows=12000 width=10)



La consulta se realiza siguiendo los ındices de customerid tanto en customers como en orders paraencajar ambas tablas entre si de forma ordenada. Luego el ejecutor puede utilizar un Merge Join deforma eficiente para combinar ambos.

Para optimizar esta consulta es util no hacer el join con campos cuyas columnas tengan tablasrepetidas.

60

Hash Join

La principal alternativa utilizada por PostgreSQL para un Merge Join es un Hash Join. Este tipode Join no ordena sus entradas, en vez de eso, crea una tabla Hash para cada registro de una ta-bla con los registros correspondientes de la otra tabla. La salida no necesariamente va a estar en orden.

Una consulta para encontrar los productos que en algun punto han sido ordenados por cualquiercliente muestra un Hash Join.

EXPLAIN ANALYZE SELECT prod_id,title FROM products p WHERE EXISTS

(SELECT 1 FROM orderlines ol WHERE ol.prod_id=p.prod_id);

QUERY PLAN

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

Hash Join (cost=1328.43..1773.80 rows=9736 width=19)


Hash Cond: (p.prod_id = ol.prod_id)

-> Seq Scan on products p (cost=0.00..198.00 rows=10000 width=19)


-> Hash (cost=1206.73..1206.73 rows=9736 width=4)


Buckets: 1024 Batches: 1 Memory Usage: 234kB

-> HashAggregate (cost=1109.38..1206.73 rows=9736 width=4)


-> Seq Scan on orderlines ol (cost=0.00..958.50 rows=60350 width=4)



Dependiendo del tipo de entrada puede ser mejro o peor que Merge Join. Si la entrada no esta or-denada puede ser mejor que el Merge Join. Ademas, los Hash Joins requieren memoria para guardartodos los registros, lo cual puede requerir que se amplie el parametro work mem.

Este tipo de join es utilizado sobre todo cuando una de las tablas es pequena y construir la tablahash es poco costoso.

5.5.4. Ordenamiento de Joins

A medida que se incrementa el numero de joins y la cantidad de registros, controlar la complejidadse vuelve mas importante para optimizar las consultas y el rendimiento de PostgreSQL.

61

Forzar el orden de los joins

Se puede forzar a que el planificar de PostgreSQL utilice el orden de querys establecido en una con-sulta. Por ejemplo, una consulta que utiliza joins de forma explıcita:

SELECT * FROM cust_hist h INNER JOIN products p ON (h.prod_id=p.prod_id)

INNER JOIN customers c ON (h.customerid=c.customerid);

Esta consulta es igual a una realizada utilizando joins implıcitos:

SELECT * FROM cust_hist h,products p,customers c WHERE h.prod_id=p.prod_

id AND h.customerid=c.customerid;

En ambos casos el planificador puede elegir planes que ejecuten primero los joins entre cust hist yproducts o entre products y customers.

En caso de que el desarrollador sepa que hay una manera eficiente de hacer el join, puede indicarloa PostgreSQL de tal forma que no pierda tiempo buscando un plan. Para hacerlo se debe reducir elparametro join collapse limit de su valor por defecto de 8. Para impedir que se reordene la consulta,establecer el parametro a 1. De esta forma la consulta empezara por cust hist, seguira por productsy terminara en customers.

SET join_collapse_limit = 1;

SELECT * FROM cust_hist h INNER JOIN products p ON (h.prod_id=p.prod_id)

INNER JOIN customers c ON (h.customerid=c.customerid);

Eliminar Joins

Una funcionalidad de PostgreSQL es la de eliminar Joins (Left o Right) innecesarios. Por ejemplo elsiguiente es un LEFT JOIN que lista productos con su inventario.

SELECT * FROM products LEFT JOIN inventory ON products.prod_id=inventory.prod_id;

Pero algunas consultas no necesitan el JOIN, por ejemplo:

EXPLAIN ANALYZE SELECT products.title FROM products LEFT JOIN inventory

ON products.prod_id=inventory.prod_id;

QUERY PLAN

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

62

Seq Scan on products (cost=0.00..198.00 rows=10000 width=19)



Debido a que es un LEFT JOIN y la consulta solo busca valores de la tabla products, no es necesarioel JOIN y por lo tanto PostgreSQL lo deja de lado.

Debido a esta funcionalidad, si se puede utilizar LEFT O RIGHT JOIN, se deben utilizar, ya que encaso no sean necesarios, PostgreSQL los eliminara para optimizar la consulta. Este hecho puede serpoco importante en consultas simples, pero en estructuras mas complicadas con vistas y consultasgeneradas automaticamente es posible que sea una mejora significativa.

UPDATES

Para realizar actualizaciones de forma optima, se debe especificar con claridad en la clausula WHE-RE a que registros afectar. Por ejemplo, si por algun motivo se quisiera hacer que todos los clientesfueran de Maryland, se efectuarıa la siguiente consulta:

UPDATE customers SET state=’MD’ WHERE NOT state=’MD’;

La condicion colocada impide la redundancia de volver a escribir donde el estado ya fuera Maryland.

5.5.5. Tips para optimizar SQL

Para escribir consultas SQL, existen diversos consejos y reglas practicas que pueden ayudar a hacerlasmas eficientes.

Usar expresiones simples

En lo posible evitar los JOINS en favor de argumentos simples de busqueda, por ejemplo una consultacomo la siguiente:

SELECT *

FROM Students AS S1, Rides AS R1

WHERE S1.town = R1.town

AND S1.town = ’Atlanta’;

Puede ser simplificada a:

SELECT *

FROM Students AS S1, Rides AS R1

WHERE S1.town = ’Atlanta’

63

AND S1.town = ’Atlanta’;

La segunda version asegura que de ambas tablas se va a utilizar la menor cantidad de registros porlo que el JOIN no va a ser costoso.

Asumiendo que hubieran diez estudiantes de cien que fueran para Atlanta y cinco de cien ofrecieranviajes a Atlanta, si el JOIN se ejecutara primero, se tendrıan 100 * 100 = 10000 registros en elCROSS JOIN.

En la segunda version, se tendria una tabla de diez registros y otra tabla de cinco registros para elCROSS JOIN.

Otra regla practica es que cuando se tiene un conjunto de condiciones unidas por un AND, colocarlas mas restrictivas primero. Por ejemplo:

SELECT *

FROM Students

WHERE sex = ’female’

AND grade = ’A’;

Esta consulta va a correr mas lento que la siguiente:

SELECT *

FROM Students

WHERE grade = ’A’

AND sex = ’female’;

Debido a que hay menos estudiantes con nota A que estudiantes mujeres.

Otra aplicacion del mismo concepto es un truco con predicados que involucran dos columnas paraforzar la eleccion del ındice a ser utilizado. Colocar la tabla con el menor numero de registros alfinal de la clausula FROM y colocar la expresion que la utiliza al inicio de la clausula WHERE. Porejemplo, considerar dos tablas, una mas grande para ordenes y otra mas pequena que traduce uncodigo a ingles, cada una con un ındice en la columna JOIN.

SELECT *

FROM Orders AS O1, Codes AS C1

WHERE C1.code = O1.code;

Esta consulta probablemente va a utilizar una estrategia de combinar los ındices, pero si se utilizauna expresion sin efecto se puede forzar a PostgreSQL a utilizar el ındice de la tabla mas pequena.

64

SELECT *

FROM Orders AS O1, Codes AS C1

WHERE O1.ordertype >= ’00’

AND C1.code = O1.code;

Si se asume que todos los codigos son mayores a 00, entonces la primera condicion siempre es ciertay se va a utilizar el ındice en Orders.

El operador <> tiene algunos problemas. El optimizador puede preferir una busqueda secuencial yno usar un ındice. Por ejemplo:

SELECT *

FROM Ireland

WHERE religion <> ’Catholic’;

Para romper la condicion se puede utilizar:

SELECT *

FROM Ireland

WHERE religion < ’Catholic’

OR religion > ’Catholic’;

Para que la segunda version sea mas veloz, se necesita que tenga un ındice en el campo ’religion’.

Otro truco es evitar el predicado IS NOT NULL y utilizar un valor constante mınimo en su reem-plazo. Los NULLS son guardados en diferente espacio fısico que sus columnas, por lo que el motordebe hacer mas trabajo. Por ejemplo:

SELECT *

FROM Sales

WHERE alphacode IS NOT NULL;

Puede ser escrito de man era que evite las lecturas extra:

SELECT *

FROM Sales

WHERE alphacode >= ’AAA’;

Otra tecnica es utilizar COUNT() con un ındice para hacer mas rapida la respuesta. Por ejemplo:

65

SELECT COUNT(invoice_nbr)

FROM Sales;

Es mas veloz que la version COUNT(*) si invoice nbr es una llave primaria u otra columna conındice.

Entregar informacion extra a las consultas

Un gestor de base de datos no siempre va a llegar a la misma conclusion, a mas informacion en laconsulta mayor opcion a que el planificador encuentre un mejar plan. Por ejemplo, para usar JOINen tres tablas diferentes sobre una misma columna se puede escribir:

SELECT *

FROM Table1, Table2, Table3

WHERE Table2.common = Table3.common

AND Table3.common = Table1.common;

Pero se le entregarıa mas informacion al planificador para que decida que tabla es mas pequena yempezar por esta.

SELECT *

FROM Table1, Table2, Table3

WHERE Table1.common = Table2.common

AND Table2.common = Table3.common

AND Table3.common = Table1.common;

El predicado IN

Hay dos formas para uitlizar el IN, pasar una lista explıcita de valores o una subconsulta que genereesta lista de valores.

El motor de base de datos no tiene estadısticas acerca de la relativa frecuencia de los valores en unalista de constantes por lo que asumira que la lista estara en el orden en que sera utilizada por lo que esmejor ordenar una lista de los valores mas utilizados a los menos utilizados. Ademas, tampoco tienesentido utilizar valores duplicados en una lista de constantes, ya que solose elegira uno, el primeroque se encuentre.

Evitar expresiones matematicas en columnas con ındices

Si una columna aparece en una expresion matematica, el gestor no podra utilizar sus ındices. Porejemplo, dada una consulta que busca las tareas que demoran tres dıas de completar en 1994:

66

SELECT task_nbr

FROM Tasks

WHERE (finish_date - start_date) = INTERVAL ’3’ DAY

AND start_date >= CAST (’2005-01-01’ AS DATE);

Pero si se tiene un ındice en la columna finish date (debido a lo frecuente de su uso) no se estarıautilizando, por lo que la siguiente consulta se ejecutarıa mas rapido:

SELECT task_nbr

FROM Tasks

WHERE finish_date = (start_date + INTERVAL ’3’ DAY)

AND start_date >= CAST (’2005-01-01’ AS DATE);

Sin embargo, la primera consulta puede ser buena para columnas en tablas pequenas en las que solose requiera busqueda secuencial.

Evitar el ordenamiento

En lo posible evitar operaciones de ordenamiento como SELECT DISTINCT y ORDER BY. Enel caso de DISTINCT, su hay columnas mcon valores unicos como un primary key, PostgreSQL loconvertira en un SELECT simple. [1]

Se puede reemplazar un SELECT DISTINCT con un EXIST(), por ejemplo, si se quiere hallar losestudiantes que estudian ciencia:

SELECT DISTINCT S1.name

FROM Students AS S1, ScienceDepts AS D1

WHERE S1.dept = D1.dept;

Esta consulta puede ser reemplazada por:

SELECT S1.name

FROM Students AS S1

WHERE EXISTS

(SELECT *

FROM ScienceDepts AS D1

WHERE S1.dept = D1.dept);

67

5.6. Parametros para el planeamiento de consultas

Algunos parametros pueden ser establecidos en una transaccion para ayudar a mejorar el rendimiento.

5.6.1. effective cache size

El valor por defecto es 128MB y es un parametro utilizado para representar cuanta espacio de cachehay para la base de datos. Generalmente es mas de la mitad de la memoria RAM en un servidordedicado. Este parametro no reserva memoria solo sirve como un valor de referencia para el planifi-cador sobre la capacidad disponible.

Lo unico para lo que se utiliza es para estimar cuando un Indice va a entrar en memoria, siendo laalternativa una busqueda secuencial. Otras consultas donde este parametro influye son los CROSSJOINS que utilizan un Indice.

Para utilizar effective cache size es mas efectivo definirlo para las grandes transacciones y dejar unvalor conservador por defecto.

5.6.2. work mem

Al igual que effective cache size, este parametro puede ser definido por cada transaccion, asignandolevalores grandes para las transacciones que lo requieran y un valor conservador por defecto.

5.6.3. Optimizando las consultas para la data en cache

PostgreSQL asume que la data no esta en cache al hacer el planeamiento de una consulta por loque todo acceso a un Indice o una tabla requerira acceso a disco. Los parametros del planificadorseq page cost y random page cost reflejan este pesimismo.

Si la data que se va a leer se espera que este en cache, se pueden volver mas optimistas losparametros del planificador, haciendolos mas cercanos al costo de acceder al nodo de un ındice(cpu index tuple cost).

Dado que ambos parameros pueden ser establecidos en cada transaccion, se pueden tener valoresconservadores como valor por defecto y solo a las consultas mas frecuentes, con alta probabilidadde que sus resultados ya esten en memoria, asignarles valores mas optimistas, incluso iguales acpu index tuple cost.

SET seq_page_cost=0.005;

SET random_page_cost=0.005;

5.7. Comprobar la equivalencia de un query

En caso se haya escrito una consulta que se considera equivalente a otra pero mas rapida, para com-probar si dos consultas son equivalentes se utiliza EXCEPT para ver que registros devueltos por la

68

primera consulta no son devueltos por la segunda.

Por ejemplo:

SELECT prod_id FROM products p WHERE NOT EXISTS (SELECT 1 FROM orderlines

ol WHERE ol.prod_id=p.prod_id)

EXCEPT

SELECT prod_id FROM inventory WHERE sales=0;

5.8. EXPLAIN ANALYZE grafico

El administrador grafico de PostgreSQL, PgAdmin, posee la capacidad de explicar de forma grafi-ca los resultados de EXPLAIN ANALYZE. Proporciona un sustituto facil de leer e interpretar quepuede ser utilizado en conjunto con la salida de texto de EXPLAIN ANALYZE.

Para utilizar esta funcionalidad:

1. Abrir PgAdmin.

2. Hacer click en el ıcono de SQL.

3. Escribir una consulta.

4. presionar F7.

Grafico 5.1: EXPLAIN ANALYZE grafico en PgAdmin

69

Ejercicios

1. Ejecutar las consultas relativas a dellstore2 en el analizador visual de PgAdmin.

70

Capıtulo 6

Alta disponibilidad y replicacion

En ocasiones, cuando se busca mejorar el rendimiento de una Base de Datos, lo mas practico esagregar mas copias de la data y dividir la carga de trabajo entre todas.

6.1. Replicacion

Se pueden enumerar diversas razones por las cuales replicar la data, pero en esencia son dos las masimportantes: Disponibilidad y escalabilidad.

Si el servidor principal se cae se espera que otro asuma de inmediato su rol. Debido al gran tamanode muchas bases de datos no se puede esperar copiar la data en el acto, en cambio se debe tenerreplicado el servidor.

La escalabilidad significa que agregando servidores se pueda incrementar el numero de accesos a labase de datos sin por eso perjudicar el rendimiento.

6.1.1. Conceptos de replicacion

Para entender la replicacion se deben comprender los conceptos utilizados.

Maestro: Es el servidor fuente de la data replicada y donde todas las actualizaciones suceden.Solo se puede tener un solo maestro al utilizar las funcionalidades de replicacion de PostgreSQL.

Esclavo: Un servidor esclavo es aquel donde se copia la data. PostgreSQL solo soporta esclavosde solo lectura.

Write Ahead Log (WAL): Es el log que registra todas las transacciones. Es frecuentementereferido como el registro de transacciones en otras Bds. Para la replicacion, PostgreSQL haceel registro disponible para los esclavos, una vez que los accceden solo necesitan ejecutar lastransacciones en si mismos.

Sıncrono: Una transaccion en el maestro no se considera completa hasta que todos los esclavosno se han actualizado.

Asıncrono: Una transaccion en el maestro se va a realizar ası los esclavos no se hayan actuali-zado. Esto es util en el caso de servidores distantes donde la latencia de la red es una dificultad.La desventaja es que la data en el esclavo puede quedar desactualizada.

71

Streaming: El modelo de replicacion por streaming fue introducido en la version 9.0. Norequiere acceso directo a los archivos entre maestro y esclavo y se basa en el protocolo de deconexion de PostgreSQL para transmitir los WALs.

Replicacion en cascada: Introducido en la version 9.2, los esclavos pueden recibir logs deotros esclavos, lo cual permite a un esclavo actuar como un maestro pero nada mas que paraconsultas de solo lectura.

6.1.2. Configurar una replica

Para el ejemplo de replicacion se utilizara la replicacion por Streaming de manera tal que el maestroy el esclavo se conecten mediante el protocolo de conexion de PostgreSQL.

Configurar el maestro

Los pasos basicos para configurar el maestro son:

1. El parametro listen addresses de postgresql.conf debe permitir conexiones del esclavo.

2. Crear una cuenta de usuario para la replica.

CREATE ROLE pgrepuser REPLICATION LOGIN PASSWORD ’woohoo’;

3. Alterar las siguientes configuraciones es postgresql.conf.

wal level = hot standbyarchive mode = onmax wal senders = 10

4. Usar el parametro archive command para indicar donde el WAL va a ser salvado. Con strea-ming se puede escoger cualquier directorio pero debe asegurarse de que el usuario postgrespueda escribir en este.

En *nix el parametro tendra un aspecto como el siguiente:

archive command = ’cp %p ../archive/ %f’

Y en Windows:

archive command = ’copy %p ..\\archive\\%f’

5. En pg hba.conf, se coloca una regla para permitir que los esclavos sean agentes de replicacion.Como ejemplo, la siguiente regla va a permitir a una cuenta de usuario llamada pgrepuser en lared privada con una IP en un rango entre 192.168.0.1 y 192.168.0.254 que utiliza una contrasenaen md5.

host replication pgrepuser 192.168.0.0/24 md5

72

6. Desactivar el servicio Postgresql y copiar del directorio de datos (se puede ver la ruta deldirectorio de la instalacion especıfica en el parametro data directory de postgresql.conf ) todoslos directorios al directorio de datos del esclavo EXCEPTO los directorios pg xlog y pg log.

Configurar el esclavo

Para minimizar problemas, el esclavo debe tener la misma configuracion del maestro, especialmentesi se va a utilizar como reemplazo en caso de una caıda. Ademas de la configuracion, para ser unesclavo necesita reproducir las transacciones en el WAL (journal de la BD) del maestro. Para lo cualse necesitan las siguientes instrucciones en el postgresql.conf de la Bd.

1. Crear una nueva instancia de PostgreSQL con la misma version que el maestro y ademas elmismo S.O. Esto no es un requerimiento estricto pero es recomendable.

2. Desactivar PostgreSQL.

3. Reescribir los archivos del directorio de datos con los archivos copiados del maestro.

4. Establecer el siguiente parametro en postgresql.conf.

hot standby = on

5. Se puede utilizar el puerto por defecto o no, no es necesario que utilice el mismo del maestro.

6. Crear un nuevo archivo en el folder de datos llamado recovery.conf que contenga las siguienteslıneas.

standby mode = ’on’primary conninfo = ’host=192.168.0.1 port=5432 user=pgrepuser password=woohoo’trigger file = ’failover.now’

El nombre del Host, el IP y el puerto deben ser los del maestro.

7. Agregar al archivo recovery.conf la siguiente lınea dependiendo del S.O,pero debe asegurarsede que el usuario postgres pueda escribir en este:

En un *nix:

restore command = ’cp %p ../archive/ %f’

En Windows:

restore command = ’copy %p ..\\archive\\%f

Estos comando solo son necesarios si el esclavo no puede reproducir el WAL del maestro consuficiente velocidad, por lo cual necesita ponerlos en cache.

73

Iniciar el proceso de replicacion

1. Iniciar el esclavo primero, si da un error sobre la falta de maestro, ignorarlo.

2. Iniciar el maestro.

Cuando se desee liberar el esclavo, se crea un archivo en blanco llamado failover.now en el directoriode datos del esclavo. Luego de crear este archivo PostgreSQL va a renombrar el archivo recover.confa recover.done y va a convertirse en un servidor independiente a partir de ese momento.

74

Capıtulo 7

Particionado de tablas

Al crecer una base de datos, es comun que alguna tabla se convierta en inmanejable por su tamano.Si la tabla es mas grande que la memoria del sistema el tiempo de consultas puede hacerse cadavez mayor. Una forma de lidiar con grandes tablas es particionarlas, dividiendolas en tablas maspequenas y manejables. Para llevar a cabo esta tarea, no se necesita cambiar la aplicacion debido aque Postgresql se hace cargo de gestionar las tablas.

7.1. Particionado

Para saber cuando particionar una tabla, los criterios que se consideran como buenas practicas sonhacerlo cuando la tabla sea mas grande que la memoria fısica o cuando alcance unos 100 millones deregistros.

Por ejemplo, considerando la tabla orders de la base de datos dellstore2 :

\d orders;

orderid | integer | not null valor por omision

nextval(’orders_orderid_seq’::regclass)

orderdate | date | not null

customerid | integer |

netamount | numeric(12,2) | not null

tax | numeric(12,2) | not null

totalamount | numeric(12,2) | not null

Si despues de anos de trabajo la tienda ha recibido tantas ordenes que las consultas a la tabla sehacen muy lentas, hay dos potenciales maneras de partir la data en partes mas pequenas. La primeraserıa partir la tabla de acuerdo a su id (orderid). Otra forma es partirlo por fechas (orderdata).

La diferencia entre ambos es que al partirlo por orderid si se eliminan los antiguos pedidos de labase de datos, dejarıa una gran cantidad de registros para ser limpiados lo que harıa mas lenta la

75

base de datos. En cambio, si se utilizan fecha para partir la data, al querer eliminar data antigua,simplemente se borran las particiones que la contienen y el rendimiento de la base de datos no sufre.

Otro criterio importante al partir una tabla es que para beneficiarse de partir una tabla en pedazos,las consultas deben realizarse sobre un subconjunto util de la data. Se deben tomar en cuentas loscampos utilizados en la clausula WHERE como elementos para tomar la decision de que campoutilizar para dividir la tabla.

7.2. Metodos de Particionado

7.2.1. Configuracion de Particionado

Un util paso inicial antes de efectuar la particion es obtener el rango de datos que contiene la tablaen relacion al campo por el que se desea particionar y su tamano.

SELECT min(orderdate),max(orderdate) FROM orders;

2004-01-01 | 2004-12-31

SELECT relname,relpages FROM pg_class WHERE relname LIKE ’orders%’

ORDER BY relname;

orders | 94

orders_orderid_seq | 1

orders_pkey | 29

7.3. Crear las particiones

Particionar en PostgreSQL se basa en las capacides de herencia de la tabla. Esto permite a una tablatener hijos que hereden sus columnas. En este ejemplo se partira la tabla orders en una particionpor mes.

CREATE TABLE orders_2004_01 (

CHECK ( orderdate >= DATE ’2004-01-01’ and orderdate < DATE ’2004-02-01’)

) INHERITS (orders);

...

76

...

CREATE TABLE orders_2004_12 (

CHECK ( orderdate >= DATE ’2004-12-01’ and orderdate < DATE ’2005-01-01’)

) INHERITS (orders);

Pero solo se hereda la estructura de columnas de la tabla, para agregar ındices, restricciones y ajustarpermisos se debe de hacer de forma manual:

ALTER TABLE ONLY orders_2004_01

ADD CONSTRAINT orders_2004_01_pkey PRIMARY KEY (orderid);

...


ADD CONSTRAINT orders_2004_12_pkey PRIMARY KEY (orderid);

SQLs que van a crear un ındice en orderid.

Ademas un ındice manual en customerid es necesario:

CREATE INDEX ix_orders_2004_01_custid ON orders_2004_01 USING btree (customerid);

...

...

CREATE INDEX ix_orders_2004_12_custid ON orders_2004_12 USING btree (customerid);

Cada orden contiene una restriccion a la llave foranea para asegurar que el cliente referenciado seavalido. Debe ser aplicada a cada particion:


ADD CONSTRAINT fk_2004_01_customerid FOREIGN KEY (customerid)

REFERENCES customers(customerid) ON DELETE SET NULL;

...

...

77


ADD CONSTRAINT fk_2004_12_customerid FOREIGN KEY (customerid)

REFERENCES customers(customerid) ON DELETE SET NULL;

7.3.1. Redirigir los INSERT a las particiones

Luego de tener la estructura lista, el siguiente paso es hacer que los registros insertados en la tablapadre vayan a la particion apropiada. La forma recomendada de hacerlo es con un trigger.

CREATE OR REPLACE FUNCTION orders_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF (NEW.orderdate >= DATE ’2004-12-01’ AND

NEW.orderdate < DATE ’2005-01-01’ ) THEN

INSERT INTO orders_2004_12 VALUES (NEW.*);

ELSIF ( NEW.orderdate >= DATE ’2004-11-01’ AND



...




ELSE

RAISE EXCEPTION ’Error in orders_insert_trigger(): date out of range’;

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

Para hacerla mas corta solo se utilizara la correspondiente a Enero,pero es una practica recomendada

78

empezar la funcion en el fin del rango (Diciembre), debido a que en escenarios de negocios, lo masprobable es que se inserte data al final del rango:

CREATE OR REPLACE FUNCTION orders_insert_trigger()


BEGIN

IF (NEW.orderdate >= DATE ’2004-02-01’ AND






ELSE

RAISE EXCEPTION ’Error in orders_insert_trigger(): date out of range’;

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

Una vez que la funcion ha sido creada, necesita ser llamada cada vez que un registro se inserte:

CREATE TRIGGER insert_orders_trigger

BEFORE INSERT ON orders

FOR EACH ROW EXECUTE PROCEDURE orders_insert_trigger();

La funcion puede necesitar actualizarse a medida que se agregan nuevas particiones, pero el triggerutilizara la nueva funcion de manera automatica una vez creada.

Para probar si funciona insertamos un registro en el rango de fechas deseado:

INSERT INTO orders (orderdate, customerid, netamount, tax, totalamount)

VALUES (’2004-01-31’, 6765, 359.03, 29.62, 388.65);

Y comprobamos que se haya insertado en la tabla orders 2004 01.

79

7.3.2. Utilizar reglas de particion

Existe otra manera de implementar la redireccion a la particion llevada a cabo mediante el trigger.Mediante una funcionalidad llamada rules (reglas) de PostgreSQL se puede substituir un comandopor otro que se desee.

Para implementar la regla, se elimina el trigger:

DROP TRIGGER insert_orders_trigger ON orders;

Y se crea la regla:

CREATE RULE orders_2004_01_insert AS

ON INSERT TO orders WHERE

( orderdate >= DATE ’2004-01-01’ AND orderdate < DATE ’2004-02-01’ )

DO INSTEAD


Y comprobamos si funciona insertando un registro.

La ventajas de las reglas es que son mas eficientes insertando grandes cantidades de registros almismo tiempo, pero los triggers son superiores al insertar registros de uno en uno. Y debido a quelas reglas son proporcionales al numero de particiones, el rendimiento puede disminuir a medida queaumentan las particiones.

En pocas palabras es mejor utilizar triggers para particionar tablas.

7.3.3. Trigger para los Updates

Cuando en la tabla maestra se actualizan registros, estos deben guardarse no en la tabla maestrasino en la particion correspondiente. Con este fin se instala el siguiente trigger:

CREATE OR REPLACE FUNCTION orders_2004_01_update_trigger()


BEGIN

IF ( NEW.orderdate != OLD.orderdate ) THEN

DELETE FROM orders_2004_01

WHERE OLD.orderid=orderid;

INSERT INTO orders values(NEW.*);

END IF;

RETURN NULL;

80

END;

$$

LANGUAGE plpgsql;

CREATE TRIGGER update_orders_2004_01

BEFORE UPDATE ON orders_2004_01

FOR EACH ROW

EXECUTE PROCEDURE orders_2004_01_update_trigger();

7.3.4. Migracion de la data

Luego de crear las particiones, los ındices y las funciones ahora se tiene toda la data en la tabla ordersy particiones vacias. Esta es la forma habitual como se llevarıa a cabo la particion de la data parano interrumpir el funcionamiento de la aplicacion. Otra opcion es hacer un backup de la tabla, crearla estructura de las particiones y recargar la data, lo que requeriria una ventana de mantenimiento.

Otro metodo es si se instala el trigger para los updates en la tabla padre, al actualizarse toda la tablase migrarıa la data. Este es el codigo necesario:

CREATE OR REPLACE FUNCTION orders_update_trigger()


BEGIN

DELETE FROM orders WHERE OLD.orderid=orderid;


RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE TRIGGER update_orders

BEFORE UPDATE ON orders

FOR EACH ROW

EXECUTE PROCEDURE orders_update_trigger();

Al llevar a cabo una migracion, la aproximacion mas prudente es incluir todo en un BEGIN/COMMIT,de tal manera que si algo sale mal, nada se lleva a cabo.

81

Para ejecutar la funcion se llama al trigger:

UPDATE orders SET orderid=orderid WHERE ORDERDATE < ’2004-03-01’;

Luego se hace un count de las particiones:

SELECT count(*) FROM orders_2004_01;

Se observa que las particiones tienen la data correspondiente pero que la tabla padre aun tiene todala data. Para limpiar la tabla padrea se utilizara CLUSTER para reconstruirla.

CLUSTER orders USING ix_order_custid;

Luego se eliminan el trigger y la funcion que realizaron la migracion para evitar futuras confuciones:

DROP TRIGGER update_orders ON orders;

DROP FUNCTION orders_update_trigger();

Se ejecuta ANALYZE para actualizar las estadısticas de la base de datos y se verifica que cons-traint exclusion este activa.

ANALYZE;

SHOW constraint_exclusion;

Constraint exclusion permite al planificador evitar incluir particiones en una consulta cuando estasno van a proveeder de registros relevantes.

Se puede volver a ejecutar una consulta con toda la tabla y verificar las estadısticas:

EXPLAIN ANALYZE SELECT * FROM orders;

QUERY PLAN

----------

Result (cost=0.00..1292.00 rows=12001 width=36) (actual

time=4.453..102.062 rows=12000 loops=1)

-> Append (cost=0.00..1292.00 rows=12001 width=36) (actual

time=4.445..62.258 rows=12000 loops=1)



-> Seq Scan on orders_2004_01 orders (cost=0.00..77.00

82

rows=1000 width=36) (actual time=0.287..1.971 rows=1000 loops=1)

-> Seq Scan on orders_2004_02 orders (cost=0.00..77.00

rows=1000 width=36) (actual time=0.267..2.045 rows=1000 loops=1)

Para probar el valor de las particiones, se puede probar con una consulta que solo consulte un dıapuntual:

EXPLAIN ANALYZE SELECT * FROM orders WHERE orderdate=’2004-01-16’;

QUERY PLAN

----------

Result (cost=0.00..471.50 rows=36 width=36) (actual time=1.437..2.141

rows=35 loops=1)

-> Append (cost=0.00..471.50 rows=36 width=36) (actual

time=1.432..2.017 rows=35 loops=1)



Filter: (orderdate = ’2004-01-16’::date)

-> Seq Scan on orders_2004_01 orders (cost=0.00..71.50 rows=35

width=36) (actual time=0.238..0.718 rows=35 loops=1)

Filter: (orderdate = ’2004-01-16’::date)


En lugar de recorrer todas las particiones, solo recorre aquella correspondiente a la fecha solicitada.

7.3.5. Crear nuevas particiones

Cuando sea necesario crear nuevas particiones se debe modificar el trigger y la funcion relacionadade tal manera que continue funcionando sin novedad el particionado.

7.3.6. Ventajas de las particiones

Existen diversas ventajas en usar particiones para una tabla:

Las busquedas son mas veloces.

Es mas sencillo el mantenimiento de data historica. Si se desea eliminar un rango de fechas delservidor de produccion, se hace un backup y se eliminan las particiones con la data antigua.

Al eliminar data, esta no queda ocupando espacio, es eliminada junto con la particion borrada

83

7.3.7. Errores al particionar

Existen algunos errores comunes al particionar una tabla:

No tener constraint exclusion en ON.

No agregar todas los constrains e ındices necesarios a cada particion.

No asignar los permisos necesarios a las particiones.

No utlizar el campo por el que se particiono al hacer consultas.

No escribir robustos el trigger o la funcion.

84

Capıtulo 8

Pooling de conexiones

Cuando se trabaja con PostgreSQL, y cualquier gestor de bases de datos, una de sus limitaciones esel sobrecosto que se paga al ejecutar incluso la mas pequena consulta. El sobrecosto se acepta sinproblemas si se esta llevando a cabo un complejo JOIN, pero si solo se efectua una lectura simple, elsobrecosto de abrir una conexion a la base de datos y esperar a que se ejecute la consulta puede sermuy alto.

8.1. El pool de conexiones

La razon principal para utilizar un pool de conexiones es que se deben tener suficientes conexionespara utilizar todos los recursos disponibles pero no mas que eso. La cantidad de conexiones dependede los nucleos en el sistema, cuanto de la base de datos esta en memoria y la velocidad de los discos.Una vez que el servidor ya esta activo continuamente, toda conexion nueva solo degrada su eficiencia.

Existen algunas reglas practicas para decidir si se tienen muchas conexiones. En una instalacion de*nix, el punto en que agregar nuevas conexiones se vuelve oneroso esta entre las 500 y 1000 activas.En general tener menos conexiones abiertas es mejor, pero no se recomienda utilizar un pool deconexiones hasta que el servidor este sobrecargado de trabajo. [5]

En los sistemas Windows, el lımite es menor. Al ejecutar PostgreSQL como servicio, se le asignan512KB para trabajar, por lo que si cada conexion toma 3.5KB de espacio no se pueden tener mas de125. Para mas informacion al respecto se puede ver en:

http://wiki.postgresql.org/wiki/Running %26 Installing PostgreSQL On Native Windows.

Realizar pooling de conexiones va a ayudar si se tienen cientos o mas conexiones y los procesadoresestan siendo utilizados en un elevado porcentaje.

8.2. PgBouncer

Originado como parte de Skype, PgBouncer es el software para pooling con el mayor rendimientodisponible. Se ejecuta como un solo proceso, no abriendo un proceso por cada conexion.

85

Al monitorear las conexiones, despliega la informacion mediante una interfaz de base de datos a laque se le pueden aplicar consultas, sirviendo para proveer informacion y de consola de control. Alconectarse mediante psql al puerto donde se ejecuta PgBouncer, se puede utilizar el comando SHOWpara mostrar informacion sobre el estado interno del pool de conexiones.

PgBouncer se puede conectar con multiples bases de datos, ya sea en el mismo host o en diferentes,lo que permite una forma de particionar para escalar. Se mueve cada base de datos a su propio hosty se les une mediante PgBouncer como intermediario y la aplicacion no necesitara ser cambiada.

8.2.1. Instalacion de PgBouncer

Para instalar PgBouncer en Ubuntu, ejecutar:

$ sudo apt-get install pgbouncer

8.2.2. Configurar PgBouncer

Para conectarse a una base de datos a traves de PgBouncer se necesitan dos archivos de configuracionubicados en /etc/pgbouncer, uno donde se establecen los parametros de configuracion y conexion yotro donde se definen los usuarios de la base de datos que podran utilizar PgBouncer. El primero espgbouncer.ini y el segundo userlist.txt :

En pgbouncer.ini se escribira la siguiente configuracion:

[databases]

template1 = host=127.0.0.1 port=5432 dbname=template1

Bd_prueba = host=127.0.0.1 port=5432 dbname=Bd_prueba

[pgbouncer]

listen_port = 6543

listen_addr = 127.0.0.1

auth_type = md5

auth_file = userlist.txt

logfile = pgbouncer.log

pidfile = pgbouncer.pid

admin_users = user

En pgbouncer.ini “user” es el usuario que va administrar pgbouncer. Se agregan ademas los usuariosde la base de datos. Para agregar mas bases de datos solo se deben de agregar en la seccion [databases].

Y en userlist.txt :

86

"user" "password"

Para iniciar PgBouncer ejecutar el comando:

pgbouncer -d pgbouncer.ini

Para conectarse a una base de datos a travez de PgBouncer escribir:

psql -p 6543 -h localhost -U user-base-datos template1

Para conectarse a PgBouncer escribir:

psql -p 6543 -h localhost -U user pgbouncer

Al conectarse a PgBouncer, mediante SHOW HELP se pueden mostrar los comandos existentes:

SHOW HELP;

NOTICE: Console usage

DETALLE:

SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION

SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM

SHOW DNS_HOSTS|DNS_ZONES

SET key = arg

RELOAD

PAUSE [<db>]

RESUME [<db>]

KILL <db>

SUSPEND

SHUTDOWN

SHOW

Si se hacen cambios en pgbouncer.ini y se desea recargar el archivo de configuracion:

RELOAD;

87

8.3. PgPool II

Es el mas antiguo de los paquetes utilizados para pooling de conexiones con PostgreSQL que aunsigue en desarrollo. Su funcion principal no es solo el pooling de conexiones, ademas provee balan-ceo de carga y capacidades de replicacion. Ademas soporta algunas configuraciones para consultasen paralelo, donde las consultas son partidas en pedazos y repartidas entre los nodos donde cadauna tiene una copia de la informacion solicitada. El “pool” en pgpool es sobre todo para gestionarmultiples servidores, y pgpool sirve como proxy entre el cliente y cierto numero de bases de datos.

PgPool tiene algunas limitaciones en el pooling de conexiones. Cada conexion es establecida en supropio proceso como en el servidor. El sobrecosto de memoria de esa aproximacion, con cada procesoutilizando una porcion de memoria, puede ser significativa. Ademas, en pgpool una vez se sobrepasa lacantidad de conexiones que maneja el software, las adicionales son encoladas en el sistema operativo,con cada conexion esperando para que su conexion de red sea aceptada.

8.3.1. Instalar PgPool II

Para instalar PgPool II en Ubuntu ejecutar:

sudo apt-get install pgpool2

8.3.2. Configuracion de PgPool II

Para configurar el origen de conexiones a PgPool, se abre el archivo pgpool.conf y se da el valor ’*’al parametro listen adresses.

listen adresses = ’*’

Para configurar los usuarios que pueden acceder a la interfaz de administracion se configura el archivopcp.conf agregando el usuario y su password en md5.

Para generar un password en md5 utilizamos el comando pg md5 y el password que deseamos cifrar:

pg md5 123456

El resultado lo incluimos en pcp.conf :

admin:e10adc3949ba59abbe56e057f20f883e

8.3.3. Preparar los nodos de bases de datos

Se deben establecer los servidores PostgreSQL para PgPool. Estos servidores pueden estar en el mis-mo host donde se encuentra PgPool o en otro host.

En el ejemplo el servidor estara presente en el mismo host que PgPool. Para configurar se accede alarchivo pgpool.conf y se escriben los siguientes parametros:

88

backend hostname0 = ’localhost’backend port0 = 5432backend weight0 = 1

El parametro backend weight0 indica que se esta ejecutando un solo servidor sin balanceo de carga.

Si se desean mas servidores, se agrega mas parametros cambiando el numero para reflejar el numerodel servidor.

8.3.4. Activar PgPool II

Para activar pgpool-II, se utiliza el comando:

pgpool -n -d > /tmp/pgpool.log 2>&1 &

Esta instruccion genera un log en /tmp/pgpool.log y se ejecuta en segundo plano.

Para terminar el proceso se ejecuta:

pgpool stop

Si hay clientes conectados y se desea desactivar el servicio a la fuerza:

pgpool -m fast stop

8.3.5. Conectarse a PgPool II

El puerto por defecto figura en el archivo pgpool.conf, en el caso del presente ejemplo es 5433.

Antes de ejecutarlo se debe cerrar cualquier programa que este editando un archivo de configuracionde PgPool. Para saber si PgPool esta activo y aceptando conexiones ejecutar:

ps -ef | grep pgpool

root 6586 3013 0 20:35 pts/1 00:00:00 sudo pgpool -n -droot 6587 6586 0 20:35 pts/1 00:00:00 pgpool -n -droot 6588 6587 0 20:35 pts/1 00:00:00 pgpool: wait for connection requestroot 6589 6587 0 20:35 pts/1 00:00:00 pgpool: wait for connection requestroot 6590 6587 0 20:35 pts/1 00:00:00 pgpool: wait for connection requestroot 6591 6587 0 20:35 pts/1 00:00:00 pgpool: wait for connection requestroot 6592 6587 0 20:35 pts/1 00:00:00 pgpool: wait for connection requestroot 6593 6587 0 20:35 pts/1 00:00:00 pgpool: wait for connection requestroot 6594 6587 0 20:35 pts/1 00:00:00 pgpool: wait for connection requestroot 6595 6587 0 20:35 pts/1 00:00:00 pgpool: wait for connection request

Si todo esta conforme se ejecuta psql para conectarse a PgPool:

89

psql -p 5433 -h localhost -U pedro -d Bd prueba

Una vez en la base de datos, ademas de los comandos SQL habituales, se pueden ver datos propiosde PgPool como:

pool status, to get the configurationpool nodes, to get the nodes informationpool processes, to get information on pgPool-II processespool pools, to get information on pgPool-II poolspool version, to get the pgPool-II release version

90

Capıtulo 9

Lenguajes Procedurales

Como en otras bases de datos, se pueden juntar expresiones SQL y volverlas una unidad. tiene di-ferentes nombres segun el motor utilizado: procedimientos almacenados, modulos, macros, etc. EnPostgreSQL se llaman funciones y ademas de permitir juntar varias expresiones SQL, permiten tam-bien utilizar logica para dirigir el flujo del programa.

En PostgreSQL existen diversos lenguajes que se pueden utilizar para escribir funciones y que confrecuencia vienen ya empaquetados: SQL, C, PL/pgSQL, PL/Perl, PL/Python. Se pueden instalarademas otrs lenguajes como R, Java, sh e incluso algunos experimentales como Scheme. Para veruna lista de los lenguajes disponibles acceder a:

http://www.postgresql.org/docs/current/interactive/external-pl.html

9.1. Estructura de una funcion

Sin importar que lenguaje se escoge para escribir una funcion, todas comparten una misma estructura:

CREATE OR REPLACE FUNCTION func_name(

arg1 arg1_datatype)

RETURNS some_type | setof sometype | TABLE (..) AS

$$

BODY of function

$$

LANGUAGE language_of_function

Para listar la lista de lenguajes en la base de datos se ejecuta:

SELECT lanname FROM pg_language;

91

La definicion de una funcion puede incluir atributos adicionales para optimizar la ejecucion y mejorarla seguridad:

LANGUAGE: Debe ser uno instalado en la base de datos.

VOLATILITY: Por defecto es VOLATILE si no es especificado. Puede ser establecido comoSTABLE, VOLATILE o IMMUTABLE. Estas configuraciones le dan una idea al planificadorsobre si los resultados de la funcion pueden ser colocados en la cache. STABLE significa quela funcion va a retornar el mismo valor para la misma entrada y la misma consulta. VOLA-TILE significa que la funcion puede retornar un valor diferente con cada llamada del sistema.INMUTABLE significa que dada una entrada, la salida siempre va a ser la misma.

STRICT: Una funcion es asumida como no estricta a menos que tenga el atributo STRICT.Una funcion STRICT siempre retorna NULL si alguna entrada es NULL.

COST: Es una medida relativa de la intensidad de computo. Las funciones SQL y PL/PgSQLtienen un valor por defecto de 100 y las de C de 1. Amayor el valor, se asume que la funcion esmas costosa.

ROWS: Es un estimado de cuantos registros va a devolver la funcion. El formato es ROWS100.

SECURITY DEFINER: Clausula opcional que implica que la funcion se ejecuta en el contextodel dueno dela funcion. Si no se coloca, la funcion se ejecuta en el contexto del usuario actual.

9.2. Funciones SQL

Escribir funciones en SQL es sencillo. Se agrega la estructura y se tiene la funcion. Debido a que noes un lenguaje propiamente dicho, en SQL no hay estructuras de control. No puede haber mas deuna sentencia SQL (se pueden utilizar sub consultas).

Ejemplo:

CREATE OR REPLACE FUNCTION ins_logs(param_user_name varchar, param_description text)

RETURNS integer AS

$$ INSERT INTO logs(user_name, description) VALUES($1, $2)

RETURNING log_id; $$

LANGUAGE ’sql’ VOLATILE;

Para llamar a la funcion se ejecuta:

SELECT ins_logs(’lhsu’, ’this is a test’) As new_id;

Para retornar conjuntos de registros existes tres alternativas: RETURNS TABLE, usar parametrosOUT, o retornar un tipo de datos compuesto. [3]

92

Ejemplo de RETURNS TABLE:

CREATE FUNCTION sel_logs_rt(param_user_name varchar)

RETURNS TABLE (log_id int, user_name varchar(50), description text, log_ts timestamptz) AS

$$

SELECT log_id, user_name, description, log_ts FROM logs WHERE user_name = $1;

$$

LANGUAGE ’sql’ STABLE;

Utilizando parametros OUT:

CREATE FUNCTION sel_logs_out(param_user_name varchar, OUT log_id int

, OUT user_name varchar, OUT description text, OUT log_ts timestamptz)

RETURNS SETOF record AS

$$

SELECT * FROM logs WHERE user_name = $1;

$$


Usando un tipo de dato compuesto:

CREATE FUNCTION sel_logs_so(param_user_name varchar)

RETURNS SETOF logs AS

$$

SELECT * FROM logs WHERE user_name = $1;

$$


Todas las funciones pueden ser llamadas usando:

SELECT * FROM sel_logs_rt(’lhsu’);

9.3. Funciones PL/pgSQL

Cuando las necesidades escapan de los lımites del SQL puro, la opcion mas comun es PL/pgSQL. Sediferencia de SQL en que se pueden declarar variables utilizando DECLARE, existen instrucciones

93

de control de flujo y el cuerpo de la funcion debe de estar entre BEGIN y END.

Ejemplo:

CREATE FUNCTION sel_logs_rt(param_user_name varchar)

RETURNS TABLE (log_id int, user_name varchar(50), description text,

log_ts timestamptz) AS

$$

BEGIN

RETURN QUERY

SELECT log_id, user_name, description, log_ts

FROM logs WHERE user_name = param_user_name;

END;

$$

LANGUAGE ’plpgsql’ STABLE;

9.4. Funciones en PL/Python

Python es un lenguaje popular con gran cantidad de librerıas. En PostgreSQL se pueden escribirfunciones utilizando Python. Estan soportadas tanto Python 2 como Python 3.

Para utilizar Python, se debe instalar el paquete de software que contiene la extension. en Ubuntu:

sudo apt-get install postgresql-plpython-9.2

Luego se ingresa en la base de datos y se instala la extension:

CREATE EXTENSION plpython2u;

9.4.1. Funcion python basica

PostgreSQL convierte automaticamente los tipos de PostgreSQL a tipos de dato Python y viceversa.Desde Pl/Pyton se pueden retornar arreglos e incluso tipos de datos compuestos. Se puede utilizarpython para escribir Triggers y crear funciones de agregacion.

Pl/Python es un lenguaje “untrusted” debido a que puede interactuar con el sistema operativo y porlo tanto una funcion solo puede ser creada por un superusuario. Un usuario puede escribir funcionesen PL/Python si es que al crearse se le dio el atributo SECURITY DEFINER. Los lenguajes “un-trusted” se reconocen debido a que llevan una “u” al final de su nombre.

94

Python permite llevar a cabo tareas que no son factible en SQL o PL/pgSQL. En el siguiente ejemplose muestra como hacer una funcion que haga una busqueda de texto en la web de documentacion dePostgreSQL.

CREATE OR REPLACE FUNCTION postgresql_help_search(param_search text)

RETURNS text AS

$$

import urllib, re

response = urllib.urlopen \

(’http://www.postgresql.org/search/?u=%2Fdocs%2Fcurrent%2F&q=’+param_search)

raw_html = response.read()

result = raw_html[raw_html.find(""):raw_html.find("") - 1]

result = re.sub(’<[^<]+?>’, ’’, result).strip()

return result

$$

LANGUAGE plpython2u SECURITY DEFINER STABLE;

Lo que hace la funcion es:

1. Importar las librerıas a utilizar.

2. Concatenar el termino de busqueda con la direccion web.

3. Leer la respuesta y guardarla en una variable llamada raw html.

4. Guardar la parte de raw html que empieza con <!– docbot goes here –> y termina antes de<!– pgContentWrap –>.

5. Elimina los tags HTML y los espacios en blanco al inicio y final y guarda en result.

6. Retorna el resultado final result.

Llamar a una funcion Python no es diferente que llamar a funciones en otros lenguajes:

SELECT search_term, left(postgresql_help_search(search_term), 125)

As result FROM (VALUES

(’regexp_match’), (’pg_trgm’), (’tsvector’)) As X(search_term);

Otro ejemplo permitira interactuar con el sistema operativo:

95

CREATE OR REPLACE FUNCTION list_incoming_files()

RETURNS SETOF text AS

$$

import os

return os.listdir(’/tmp’)

$$

LANGUAGE ’plpython2u’ VOLATILE SECURITY DEFINER;

Para ejecutarlo:

SELECT * FROM list_incoming_files();

Ejemplo de acceso a data desde plpython mediante la librerıa plpy.

CREATE FUNCTION try_adding_joe() RETURNS text AS $$

try:

plpy.execute("INSERT INTO users(username) VALUES (’joe’)")

except plpy.SPIError:

return "something went wrong"

else:

return "Joe added"

$$ LANGUAGE plpythonu;

CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$

odd = 0

cursor = plpy.cursor("SELECT num FROM largetable")

while True:

rows = cursor.fetch(batch_size)

if not rows:

break

for row in rows:

if row[’num’] % 2:

odd += 1

return odd

96

$$ LANGUAGE plpythonu;

9.4.2. Aspectos basicos de Python

Python es un lenguaje de programacion interpretado, dinamico y multiparadigma (POO, estructura-do). Se caracteriza por tener una baja curva de aprendizaje, ser multiuso y permitir una programacionordenada y elegante.

Su vision esta expresa en el Zen de Python:

Bello es mejor que feo.Explıcito es mejor que implıcito.Simple es mejor que complejo.

Python se ha utilizado para todo tipo de desarrollos incluyendo Web, aplicaciones de escritorio, sis-temas de informacion, utilitarios, etc.

9.4.3. Ejemplo de Python

import smtplib

from email.MIMEText import MIMEText

fromaddr = ’[email protected]’

toaddrs = ’[email protected]’

msg = ’Hola, este es un mensaje de prueba’

message = MIMEText(msg)

message[’From’] = ’Example <’ + fromaddr + ’>’

message[’To’] = toaddrs

message[’MIME-Version’] = ’1.0’

message[’Content-type’] = ’text/HTML’

message[’Subject’] = ’Asunto’

# Credentials (if needed)

usuario = ’usuario’

password = ’password’

97

# The actual mail send

server = smtplib.SMTP(’smtp.gmail.com:587’)

server.starttls()

server.login(usuario,password)

server.sendmail(fromaddr, toaddrs, message.as_string())

server.quit()

Para empezar a experimentar con Python se puede utilizar el interprete interactivo desde la consola:

python

9.4.4. Tipos basicos

Los tipos basicos son en esencia tres: numeros, cadenas y valores booleanos.

Numeros: 5 (entero), 4.7 (flotante), 4 + 6j (complejo).

Cadenas: ”Gobierno regional”.

Booleanos: True y False

Los comentarios se escriben utilizando el sımbolo #:

\# Este es un comentario

a = "Esta es una linea de programa"

Las variables se asignan de forma dinamica y dependen del tipo de dato que se les asigna, por ejemplo,una variable puede ser entera, luego asignarsele un valor de coma flotante y por ultimo una cadenay es valido:

>>> a = 5

>>> a

5

>>> a = 6.4

>>> a

6.4

>>> a = "Chiclayo"

>>> a

98

’Chiclayo’

>>>

Cadenas

Existen tres tipo de cadenas: Las cadenas normales, las cadenas unicode y las cadenas “crudas” (rawen ingles).

>>> a = “n”>>> print “a”n>>> b =“n”>>> print bn>>> au’\xf1’>>> b’\xc3\xb1’>>> c = r“\n”>>> c’\\n’>>> print c\n>>>

9.4.5. Operadores

Operador Descripcion Ejemplo

+ Suma r = 3 + 2 # r es 5

- Resta r = 4 - 7 # r es -3

- Negacion r = -7 # r es -7

* Multiplicacion r = 2 * 6 # r es 12

** Exponente r = 2 ** 6 # r es 64

/ Division r = 3.5 / 2 # r es 1.75

// Division entera r = 3.5 // 2 # r es 1.0

% Modulo r = 7

Si en una operacion utilizamos un numero flotante y un entero el resultado sera flotante.

99

9.4.6. Colecciones

Los tipos de colecciones de datos mas utilizados en Python son: listas, tuplas y diccionarios.

Listas

Las listas son el caballo de batalla de Python. Son colecciones de datoa que pueden tener cualquiertipo de dato: numeros, cadenas, otras listas, objetos, etc.

Para crear una lista se agrupan los objetos en corchetes separados por comas:

>>> l = [22, True, "una lista", [1, 2]]

>>> l[2]

’una lista’

>>> l[3][0]

1

>>> l[1] = 15

>>> l

[22, 15, ’una lista’, [1, 2]]

>>>

>>> l[0:2]

[22, 15]

>>> l[:2]

[22, 15]

>>> l.append("mee")

>>> l

[22, 15, ’una lista’, [1, 2], ’mee’]

Tuplas

Las tuplas se parecen a las listas, excepto por dos puntos: Se definen de forma diferente y son inmu-tables.

>>> tupla = (1,5,"Hola", True, "Hotel")

>>> tupla[0]

1

>>> tupla[0:2]

100

(1, 5)

>>> t = (1)

>>> type(t)

<type ’int’>

>>> t = (1,)

>>> type(t)

<type ’tuple’>

A cambio de no ser modificables, las tuplas son mas ligeras en memoria.

Diccionarios

Son colecciones que relacionan una llave y un valor. El primer valor es la clave y el segundo el valorasociado a la clave. Como clave se puede utilizar cualquier valor inmutable: numeros, cadenas, boo-leanos, tuplas. A un diccionario se accede solo por la llave, ya que no tienen orden interno.

>>> d = {"Don Corleone": "Marlon Brando", "Michel Corleone": "Al Pacino"}

>>> d

{’Michel Corleone’: ’Al Pacino’, ’Don Corleone’: ’Marlon Brando’}

>>> d["Don Corleone"]

’Marlon Brando’

9.4.7. Control de flujo

El control de flujo son los condicionales y bucles:

x = 10

y = 50

if x > 10 and y > 60:

print "x mayor que 10 e y mayor que 60"

elif x > 5 and y < 100:

print "x mayor que 5 e y menor que 100"

else:

print "ninguna de las anteriores"

101

En Python no existe el switch.

i = 0

while i < 20:

print str(i) + " es menos de 20"

i = i + 1

En Python, el bucle for ... in se utiliza como una forma generica de iterar sobre una secuencia.

x = 5

for j in range(x):

print "a " + str(j) + " le faltan " + str(x - j) + " para alcanzar a " + str(x)

l = [4, "Segundo", "Chiclayo", False, 6.8]

for item in l:

print item

9.4.8. Funciones

Una funcion es un fragmento de codigo con un nombre asociado que realiza una serie de tareas ydevuelve un valor.

def primera_funcion(param1, param2):

suma = param1 + param2

return suma

print primera_funcion(5,4)

print "\n"

print primera_funcion("Ho","la")

def varios(param1, param2, *otros):

"""Funcion con numero variable de parametros:W

102

"""

print param1

print param2

for val in otros:

print val

varios(1,2)

varios(1,2,3,4)

varios(1,2,3,4,5)

9.4.9. Funciones Trigger

PostgreSQL ofrece Triggers a nivel de registro y de tablas y vistas. PostgreSQL ofrece funcionesespecializadas en gestionar los triggers que actuan como cualquier otra funcion y tienen la mismaestructura basica. Donde difieren es en el parametro de entrada y en la salida.

Una funcion trigger nunca recibe un input aunque internamente tiene acceso a la data del trigger ypuede modificarla. Su salida siempre es un tipo llamado trigger. Cada trigger debe tener asociadaunafuncion trigger y para tener multiples triggers se deben crear multiples funciones sobre el mismoevento las cuales se ejecutaran en orden alfabetico.

Se puede usar cualquier lenguaje menos SQL para escribir triggers. El mas utilizado es PL/pgSQL.Se siguen dos pasos: en primer lugar escribir la funcion y luego el trigger correspondiente.

Por ejemplo:

CREATE OR REPLACE FUNCTION orders_2004_01_update_trigger()


BEGIN

IF ( NEW.orderdate != OLD.orderdate ) THEN

DELETE FROM orders_2004_01

WHERE OLD.orderid=orderid;


END IF;

RETURN NULL;

END;

$$

103

LANGUAGE plpgsql;

CREATE TRIGGER update_orders_2004_01

BEFORE UPDATE ON orders_2004_01

FOR EACH ROW

EXECUTE PROCEDURE orders_2004_01_update_trigger();

Lo primero que se hace es definir la funcion trigger que elimina un valor antiguo e inserta elvalor nuevo del registro.

El trigger se ejecutara antes de que se actualize un registro de la tabla orders 2004 01.

104

Capıtulo 10

Extensiones y contrib

Las extensiones y los modulos contribs son Add-ons que se pueden instalar en una base de datospara extender su funcionalidad mas alla de lo que ofrece PostgreSQL.

Antes de PostgreSQL 9.1 los add-ons se llamaban contribs, a partir de esa version, los add-ons son ins-talados con facilidad mediante el nuevo modelo de extensiones, termino que ha reemplazado a contrib.

Las extensiones son instaladas por separado en cada base de datos. Se puede tener una base de datoscon una extension y las demas no tenerla. Si se desea que todas las bases de datos tengan un esquema,este se debe instalar en la base de datos template1 para que al crearse una nueva, tenga a su vez laextension deseada.

Para ver las extensiones instaladas se ejecuta:

SELECT * FROM pg available extensions;

Para tener los detalles de una extension en particular se utiliza el siguiente comando:

\dx+ plpgsql

10.1. Instalar Extensiones

Para instalar una extension se deben de colocar sus scripts en share/extension y sus binarios en liby bin. En el caso de las extensiones mas comunes, estas ya se encuentran precompiladas y solo faltainstalarlas.

La forma moderna y sencilla de instalar una extension es:

CREATE EXTENSION fuzzystrmatch;

Si se desea instalar la extension en un esquema particular, primero se crea el esquema y luego seinstala la extension:

CREATE EXTENSION fuzzystrmatch SCHEMA my extensions;

105

10.2. Extensiones Comunes

Diversas extensiones vienen empaquetadas con PostgreSQL pero no son instaladas por defecto. Al-gunas de las mas populares son:

Postgis: Una extension que coloca a PostgreSQL entre las principales bases de datos geografi-cas. Esta compuesto de mas de 800 funciones, tipos e ındices espaciales.

fuzzystrmatch: Es una extension ligera para trabajar de forma difusa con cadenas.

hstore: Es una extension que agrega almacenamiento por llave y valor (tipo diccionario) paraguardar data pseudo normalizada. Util para servir de intermedio entre relacional y NoSQL.

pg trgm: Una extension utilizada para manejar de forma difusa cadenas de texto.

dblink: Modulo que permite hacer consultas a otras bases de datos. Es el unico mecanismopara trabajar entre bases de datos con PostgreSQL.

pgcrypto: Provee herramientas de cifrado incluyendo PGP.

106

Fuentes de Informacion

[1] CELKO, JOE. SQL FOR SMARTIES: ADVANCED SQL PROGRAMMING THIRD EDITION.Morgan Kaufmann, 2005.

[2] Group, The PostgreSQL Global Development. PostgreSQL 9.2.3 Documentation. PostgreSQL9.2.3 Documentation, 2013.

[3] Obe, Regina y Hsu, Leo. PostgreSQL: Up and Running. O’Reilly, 2012.

[4] Quinones, Ernesto. Postgresql como funciona una base de datos por dentro.

[5] Smith, Gregory. PostgreSQL 9.0 High Performance. Packt Publishing, Birmingham – UK., 2010.

107

Documents

Clases Postgresql