1 Capacitación Nivel 1 Día 3PostgreSQL Capacitación Nivel 1 Día 3

2 Agenda Estructura del PGDATA. Configuración Transacciones y bloqueos.Control de concurrencia. Catalogo. Estadísticas. Consultas y lenguaje SQL para DBA. Entendiendo el Explain. Administración de Índices. Administración de Large Objects. Objetos del gestor.

3 Estructura del PGDATA

4 Foto del PGDATA

5 Archivos y DirectoriosArchivos de configuración PG_VERSION Otros Ficheros: postmaster.pid: contiene el numero del PID del servidor que pega al cluster. postmaster.opts: opciones con las cuales arranca el servidor.

6 Archivos y Directorios (2)Base Bases de datos y plantillas (Template0 y 1). Dentro se encuentran las tablas e indices con sus correspondientes OIDs. Global Tablas e índices comunes a todas las bases. Catálogo compartido: pg_shadow (usuarios), pg_database... pgstat.stat (usado por el monitor de estadísticas) pg_control: arch. Con param del Cluster. pg_log Generalmente es esta carpeta la que contiene los logs del servidor.

7 Archivos y Directorios (3)pg_xlog (WAL): Diarios de escritura adelantada. Utilizada para recuperaciones. Conjunto de segmentos de un tamaño de 16 MB y divididos en páginas de 8KB. Se van creando de acuerdo a las necesidades. pg_clog Ficheros de confirmación Guarda los estados de las transacciones. pg_multixact Utilizado para estados multitransaccionales, bloqueos compartidos de filas.

8 Archivos y Directorios (4)pg_twophase Ficheros para el control de transacciones preparadas. pg_subtrans Para realizar savepoints dentro de transacciones. pg_tblspc Información de los tablespaces. *nix contienen los links a los directorios.

9 Configuración

10 postgresql.conf General shared_buffers max_connections temp_bufferswork_mem max_fsm_pages y max_fsm_relations WAL Fsync wal_buffers archive_mode archive_command

11 postgresql.conf (2) Query TuningPlanner method (peuden ser seteados en caliente) Constantes de costo (para evaluar el Qpath) Geqo Loggin (opciones de log) Runtime statistics track_activities, log_parser_stats, log_planner_stats, log_statement_stats, etc.

12 postgresql.conf (3) Autovacuum (on -off)Valores por defecto de conección cliente search_path = '”$user”, schema' Bloqueos deadlock_timeout max_locks_per_transaction tener en cuenta que cada bloqueo consume ~270 bytes del shared_buffer por lo que existen max_locks_per_transaction * (max_connections + max_prepared_transactions)

13 pg_hba.conf Database puede ser:all, sameuser, samerole, un nombre de una base o un listado separado por coma. Usuario puede ser: All, un nombre, un nombre de grupo (+ como prefijo) o unlistado separado por coma. Puede incluir el nomre de un archivo para un listado. CIDR-Address puede ser: Expresión que haga concordar con el host solicitante. Esta compuesta por una IP + CIDR (numero entre 0 y 32 para Ipv4 y 0 a 128 para Ipv6) que especifica el numero significativo de la máscara.

14 pg_hba.conf (2) METHOD (método) puede ser:Trust (confiable), reject (rechazar), md5 (checksum), crypt, password, gss, sspi, krb5 (kerberos), ident (utiliza el archivo ident), pam, ldap. Password envia en texto plano, por lo que no se recomienda. Md5 envia los passwords encriptados. Option es el mapa de identificación o el nombre del servicio PAM, dependiendo el método. Requiere reload para los cambios.

15 Todos en pg_hba.conf Desde afuera con IPv4:Host all all /0 trust Desde afuera con Ipv6: Host all all ::1/128 trust Desde localhost: Host all all /32 trust

16 Recomendación Tener en cuenta que:Generalmente en aplicaciones web, utilizamos 1 servidor o un cluster que se conecta a la base. Conviene especificar esos servidores uno por uno en vez de utilizar una expresión. Preferiblemente, asignar conexión usuario- base, evitando 'all'. De esta manera podremos tener superusuarios, sin acceso a otras bases para visión de datos.

17 pg_ident.conf Compuesto de MAPNAME, IDENT- USERNAME, PG-USERNAME.MAPNAME es el nombre utilizado en el pg_hba.conf IDENT-USERNAME el nombre del usuario. PG-USERNAME es el usuario en la base de datos. Requiere un reload para los cambios.

18 Transacciones y bloqueos

19 Que debe prevenirse: Lectura suciaLa transacción lee un dato que aún no fue comprometido por otra transacción. Lectura no repetida Una transacción relee un datos y encuentra que este fue modificado por otra transacción (y que fue comprometida desde la lectura inicial). Lectura fantasma Una transacción reejecuta una consulta retornando un set de registros que satisfacen una condición y encuentran que ese set ha sido modificado por otra reciente transacción.

20 Niveles transaccionales de SQLIsolation Level Dirty Read Nonrepeatable Read Phantom Read R. uncommitted Possible Possible Possible Read committed Not possible Possible Possible Repeatable read Not possible Not possible Possible Serializable Not possible Not possible Not possible En Postgres, se puede llamar a cualquiera de estos, pero solo dos tienen efecto: Read commited y Serializable. Por cuestiones de costo, postgresql no soporta bloqueos en predicado para el modo serializable.

21 Update perdido

22 Bloqueos explícitos: LOCK (Nivel de tabla)descripción: Bloquea una tabla Sintaxis: LOCK [ TABLE ] name [, ...] [ IN lockmode MODE ] [ NOWAIT ] Modo de bloqueo peude ser uno de los siguientes: ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

23 Modos de bloqueo Es el que bloquea obsolutamente todos los modos de acceso.

24 Bloqueos explícitos: Nivel de tuplaAutomáticamente se bloquea ante cualquier modificación. No afecta a las consultas, solo a las actualizaciones. SELECT … FOR SHARE [OF tabla NOWAIT] SELECT … FOR UPDATE [OF tabla NOWAIT]

25 Deadlocks Bloqueo mutuo entre dos transacciones.Resultan a partir del bloqueo a nivel de tupla. La mejor defensa contra este tipo de bloqueos es generalmente evitarlos cerciorando que todas las aplicaciones udquieran los bloqueos de varios objetos en orden consistente. UPDATE accounts SET balance = balance WHERE acctnum = 11111; WHERE acctnum = 22222; UPDATE accounts SET balance = balance WHERE acctnum = 22222; WHERE acctnum = 11111;

26 Advisory Locks Duran hasta finalizar la sesión o ser liberados explícitamente. Pueden ser vistas en la vista de sistema pg_locks. Se configura a traves de max_locks_per_transaction y max_connections SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok SELECT pg_advisory_lock(id) FROM foo WHERE id > LIMIT 100; -- danger! SELECT pg_advisory_lock(q.id) FROM ( SELECT id FROM foo WHERE id > LIMIT 100; ) q; -- ok

27 Bloqueos e índices Los índices B-tree ofrecen la mejor performance para aplicaciones concurrentes Tienen mayores prestaciones que los índices por hash. Para datos NO escalares, es preferible utilizar GiST o GIN.

28 Catalogo y estadísticas.

29 System tables

30 System tables pg_catalogTodas comienzan con pg_ pg_aggregate pg_index pg_shdepend pg_stat_user_indexes pg_am pg_indexes pg_shdescription pg_stat_user_tables pg_amop pg_inherits pg_stat_activity pg_tables pg_amproc pg_language pg_stat_all_indexes pg_tablespace pg_attrdef pg_largeobject pg_stat_all_tables pg_temp_1. pg_attribute pg_listener pg_stat_bgwriter pg_timezone_abbrevs pg_authid pg_locks pg_stat_database pg_timezone_names pg_auth_members pg_namespace pg_statio_all_indexes pg_toast. pg_autovacuum pg_opclass pg_statio_all_sequences pg_toast_temp_1. pg_cast pg_operator pg_statio_all_tables pg_trigger pg_catalog pg_opfamily pg_statio_sys_indexes pg_ts_config pg_class pg_pltemplate pg_statio_sys_sequences pg_ts_config_map pg_constraint pg_prepared_statements pg_statio_sys_tables pg_ts_dict pg_conversion pg_prepared_xacts pg_statio_user_indexes pg_ts_parser pg_cursors pg_proc pg_statio_user_sequences pg_ts_template pg_database pg_rewrite pg_statio_user_tables pg_type pg_depend pg_roles pg_statistic pg_user pg_description pg_rules pg_stats pg_views pg_enum pg_settings pg_stat_sys_indexes pg_group pg_shadow pg_stat_sys_tables

31 Information Schema Se incluye para compatibilidad con el standart.

32 Explain es tu amigo Permite ir más allá de ejecutar consultas.Ayuda a comprender que es lo que puede estar fallando del servidor. No siempre la consulta más elegante es la más performante :)

33 EXPLAIN - Resume Este comando permite visualizar y entender cual es la forma en que el optimizador analiza y devuelve los resultados. Es de gran ayuda cuando queremos ver posibles defectos en la arquitectura de nuestra base de datos.

34 Sintáxis # EXPLAIN [ANALYZE] [VERBOSE] Insert Updateselect

35 Conceptos básicos Coste OperadorTiempo (no vamos a entrar en explicaciones filosóficas de que es esto :) Tamaño de datos (esto podría tener una explicación más extensa , pero es entendible de solo pensar en ella)

36 Cost Cost( 1ra fila … ultima) de acuerdo a valores de coste explícitos en el postgresql.conf

37 Operadores Seq Scan Index scan Sort (*work_mem) Unique Limit AggregateAppend Result Nested Loop Merge Join Hash and Hash Join Group Subquery Scan and Subplan Tid Scan (raro) Materialize

38 EXPLAIN vs EXPLAIN ANALYZE

39 + operadores select * from accounts a, history h where a.aid = h.aid;

40 Manipulando el optimizadorDesactivamos la búsqueda por escaneo de índices?

41 Estadísticas El catálogo contiene las estadísticas de acceso , campos frecuentes y demás de todos los objetos del CLUSTER.

42 Ejemplo de uso de estadísticasTener en cuenta de tener las estadísticas bien actualizadas (ANALYZE y VACUUM).

43 Frecuencia de valores A mayor frecuencia de aparición,favorecerá que el optimizador opte por el seqscan Por sobre otros operadores :O

44 Cantidad de valores distintos

45 Tunning estadísticas ALTER TABLE chares ALTER COLUMN texto SET STATISTICS 10; Permite mayor recolección de estadísticas para una determinada columna. De esta forma, campos númericos pueden ser medidos de manera más detallada.

46 I/O por objeto pg_statio_all_indexes pg_statio_sys_indexespg_statio_user_indexes pg_statio_all_sequences pg_statio_sys_sequences pg_statio_user_sequences pg_statio_all_tables pg_statio_sys_tables pg_statio_user_tables

47 Administración de Índices

48 Índices tipos Comunes Por expresion o funcionalesBtree (árbol balanceado) Gin (mejor para texto, ex R- tree) GIST (datos complejos) Hash (con algunos problemas aún | recomendado para columnas UNIQUE) Comunes Por expresion o funcionales CREATE INDEX ix_accounts1 ON accounts USING hash (aid) tablespace ts_indices;

49 Índices funcionales CREATE INDEX ix_chares_func ON chares (hashtext(texto));

50 Incidencia de NULLs en Índicesparapruebas=# update chares set texto = NULL where texto ~ '^1[1|2|3]';

51 Performance Tablespaces dedicados a objetos.RAID si o si en servidores. Economía? RAID0 con PITR. Bases chicas, normalizar en buen nivel y redundar información con vistas materializadas. Después de grandes cambios en los datos, correr VACCUM y ANALYZE. CREATE TABLE AS o CLUSTER permitirá que las lecturas en los índices más rápida.

52 Performance (2) Influencie el optimizador para detectar posibles discrepancias en el resultado a través de las variables CPU_INDEX_TUPLE, CPU_OPERATOR_COST, CPU_TUPLE_COST, EFFECTIVE_CACHE_SIZE, RANDOM_PAGE_COST. Minimice la carga de red y de las máquinas locales haciendo la mayor parte del trabajo en el servidor. Esto incluye los procedimientos, funciones, disparadores.

53 Performance (3) Prefiera utilizar multinucleos para Postgres, el sistema de forks permite una distribución en los recursos. Si utiliza 'large objects' realice seguido vacuum_lo (incluido en los contrib). Trate de utilizar FreeBSD para sistemas medianos y Solaris para sistemas de buenos recursos. PL/Perl es un exelente lenguaje para realizar tareas complejas y eficientes.

54 PGBENCH

55 PyTest (por Mariano Reingart y ecFranco)import psycopg2 import MySQLdb from threading import Thread from time import time import sys class BenchmarkInsert(Thread): "Hilo para timing de INSERT" def __init__(self, connect, nro): Thread.__init__(self) self.nro = nro # nro de thread self.connect = connect # funcion de conexión (mysql o pg) def run(self): cn = self.connect() cur = cn.cursor() for x in range(10000): cur.execute("INSERT INTO prueba (texto, entero, flotante) VALUES (%s,%s,%s)", ("hola %s" % x, int(x), float(x/100.0))) cn.commit() cn.close() class BenchmarkSelect(Thread): "Hilo para timing de SELECT" cur.execute("SELECT * FROM prueba") for row in cur: #print self.nro, row pass

56 PyTest (2) class BenchmarkSelectJoin(Thread):"Hilo para timing de SELECT" def __init__(self, connect, nro): Thread.__init__(self) self.nro = nro # nro de thread self.connect = connect # funcion de conexión (mysql o pg) def run(self): cn = self.connect() cur = cn.cursor() for x in xrange(10000): cur.execute("SELECT * FROM prueba as p1 INNER JOIN prueba as p2 WHERE p1.id=p2.flotante WHERE p1.text ", ("hola %s" % x,)) for row in cur: #print self.nro, row pass cn.close() def bench(cant,connect, benchmark): "Funcion de benchmark, recibe cantidad de threads a crear, función de conexion y clase benchmark" threads = [] for i in range(cant): thread = benchmark(connect, i) # creo el thread de test threads.append(thread) thread.start() #print "Threads startedok" for thread in threads: # wait until it terminates thread.join() #print "Threads killed ok"

57 PyTest (3) myconnect = lambda: MySQLdb.connect(db="benchmark", user="root", passwd="m" ,host="localhost") pgconnect = lambda: psycopg2.connect(database="benchmark", user="postgres", password="m", host="localhost") tests = ( ('mysql-select', BenchmarkSelect, myconnect), ('pgsql-select', BenchmarkSelect, pgconnect), # ('mysql-insert', BenchmarkInsert, myconnect), # ('pgsql-insert', BenchmarkInsert, pgconnect), ) for db, benchmark, connect in tests: print "populando para benchmark %s" % db cn = connect() cur = cn.cursor() cur.execute("DELETE FROM prueba") for x in range(1000): cur.execute("INSERT INTO prueba (texto, entero, flotante) VALUES (%s,%s,%s)", ("hola %s" % x, int(x), float(x))) print "ejecutando benchmark %s" % db cn.commit() cn.close() out = open("%s.csv" % db,"w") for i in range(1,int(sys.argv[1])): t0 = time() bench(i, connect, benchmark) t1 = time() print "%s,%d,%f" % (db, i, t1-t0) out.write("%s,%d,%f\n" % (db, i, t1-t0)) #create table prueba (id serial primary key, texto text, flotante float, entero integer, fecha timestamp DEFAULT now()); #create table prueba (id integer auto_increment primary key, # texto text, flotante float, entero integer, fecha timestamp DEFAULT now()) engine=innodb; #create index prueba_float on prueba(flotante); #create index prueba_text on prueba(texto);

58 Administración de Large Objects

59 Funciones para LO List of functionsSchema | Name | Result data type | Argument data types pg_catalog | lo_close | integer | integer pg_catalog | lo_creat | oid | integer pg_catalog | lo_create | oid | oid pg_catalog | lo_export | integer | oid, text pg_catalog | lo_import | oid | text pg_catalog | lo_lseek | integer | integer, integer, integer pg_catalog | lo_open | integer | oid, integer pg_catalog | lo_tell | integer | integer pg_catalog | lo_truncate | integer | integer, integer pg_catalog | lo_unlink | integer | oid

60 Vacuum_lo Existe un contrib para realizar vaciamiento de LO huérfanos.vacuumlo -Uuser base

61 En detalle -- Tabla de imágenes CREATE TABLE misimagenes (title TEXT NOT NULL primary key, picture OID); -- Imagen de rosas rojas está en el servidor. Cargar y exportar una copia al servidor. INSERT INTO misimagenes (title, picture) VALUES ('Rosas Rojas', lo_import('/tmp/rosas_rojas.jpg')); SELECT lo_export(picture, '/tmp/rosas_rojas_copia.jpg') FROM misimagenes WHERE title = 'Rosas Rojas'; -- Imagen de rosas blancas está en el cliente -- Cargar y exportar una copia al cliente. \lo_import '/tmp/rosas_blancas.jpg' INSERT INTO misimagenes (title, picture) VALUES ('Rosas Blancas', :LASTOID); SELECT * from misimagenes; title | picture -- Red Roses | -- White Roses | -- (2 rows) \lo_export '/tmp/rosas_blancas_copia.jpg'

62 En detalle ++ CREATE RULE droppicture AS ON DELETE TO misimagenesDO SELECT lo_unlink( OLD.picture ); CREATE RULE reppicture AS ON UPDATE TO misimagenes DO SELECT lo_unlink( OLD.picture ) where OLD.picture <> NEW.picture; Como listo los objetos? lo_list desde el psql! CREATE TABLE image ( name text, raster oid ); SELECT lo_creat(-1); returns OID of new, empty large object SELECT lo_create(43213); -- attempts to create large object with OID 43213 SELECT lo_unlink(173454); -- deletes large object with OID INSERT INTO image (name, raster) VALUES ('beautiful image', lo_import('/etc/motd')); SELECT lo_export(image.raster, '/tmp/motd') FROM image WHERE name = 'beautiful image';

63 bytea CREATE TABLE connectby_bytea(keyid bytea, parent_keyid bytea, pos int); copy connectby_bytea from stdin; row\\001 \N 0 row\\002 row\\001 0 row\\003 row\\001 0 row\\004 row\\002 1 row\\005 row\\002 0 row\\006 row\\004 0 row\\007 row\\003 0 row\\010 row\\006 0 row\\011 row\\005 0 \. Create table foo (d bytea); Insert into foo('asdfasfasdf23523”#$&%/$%#$%!#/&%UYT//'); BLOB'S ---> USE TOAST!!!!

64 Objetos del motor

65 Links explain.depesz.com

66 Gracias!