BASES DE DA DATOS TOS II (DISTRIBUIDAS) Facultad Fa cultad de Ingeniería y Ciencias Ingeniería en Sistema e Informátic Informática a - Electrónica y Redes
Afinación de Desempeño de una BDD y Optimización de Consultas Ing. Jonathan Carrillo Quito, Mayo 2015
Contenido: 1. Introducción 2. Gestión del Rendimiento Carga de trabajo (workload) Capacidad del Servidor Recursos adicionales de hardware y software Optimización/Afinamiento Contención
3. Técnicas para optimización de bases de datos Ajuste del sistema Ajuste de la base de datos Ajuste de Aplicación
4. Rendimiento de Aplicaciones 5. Ajuste de la base de datos Indice cluster clustered ed Indice uncluster unclustered ed
Afinación de Desempeño de una BDD 1. Introducción
INDEPENDIENTE DEL DBMS
DEPENDE DEL DBMS
Afinación de Desempeño de una BDD 1. Introducción
> Diseño Físico • Gestionar los metadatos (el diccionario de datos) • Optimizar tiempos de respuesta. • Afinación de Desempeño de una BDD y de fiabilidad (recuperabilidad, tolerancia a fallos) • Optimización mediante RAID • Minimizar espacio de almacenamiento para los archivos físicos de la Base de Datos. • Optimizar rendimiento de transacciones (throughput) • Proporcionar procedimientos óptimos de recuperación e integridad de la BD. • Asegurarse que los requisitos y criterios de seguridad y confidencialidad se cumplen.
Afinación de Desempeño de una BDD 1. Introducción
> Diseño Físico Estructura interna (Esquema Interno
Afinación de Desempeño de una BDD 1. Introducción
> Diseño Físico
Afinación de Desempeño de una BDD 2. Gestión del Rendimiento •
•
•
•
Una de las tares principales del DBA es el monitoreo de rendimiento y afinamiento de la base de datos. Sin embargo, en la mayoría de las ocasiones la gestión de rendimiento es reactiva. Los problemas de rendimiento pueden ser causados por cualquier componente de las TIC: servidores, red, SO, concurrencia de clientes o base de datos. Poseer una sólida comprensión de la infraestructura de TI permite a los DBA’s responder eficazmente cuando surgen problemas de rendimiento.
Afinación de Desempeño de una BDD 2. Gestión del Rendimiento •
•
•
Gestión: es la acción de administrar, gobernar, dirigir, monitorear y concretar objetivos Rendimiento: es la celeridad con la que la DBMS atiende la demanda de los usuarios. Cinco factores que influyen en el rendimiento de base de datos: 1. Carga de trabajo (workload). 2. Capacidad del servidor 3. Recursos adicionales de hardware y software. 4. Optimización y 5. Contención.
Afinación de Desempeño de una BDD 2. Gestión del Rendimiento
Factores que influyen en el rendimiento de BDD: 1. Carga de trabajo (workload) •
•
Es el conjunto de todas las peticiones que el sistema recibe de su entorno durante un periodo de tiempo dado. Estas peticiones pueden ser una combinación de transacciones en línea, tareas en batch, consultas ad hoc, tareas de análisis de datos, comandos del sistema ejecutados, entre otras
Afinación de Desempeño de una BDD 2. Gestión del Rendimiento
Factores que influyen en el rendimiento de BDD: 2. Capacidad del Servidor •
Se pueden considerar: •
La velocidad de entrada-salida
•
Velocidad del CPU
•
Velocidad de Memoria
•
Capacidad de trabajo en paralelo
•
Eficiencia del sistema operativo
•
Eficiencia del DBMS.
Afinación de Desempeño de una BDD 2. Gestión del Rendimiento
Factores que influyen en el rendimiento de BDD: 3. Recursos adicionales de hardware y software •
Gestor de Transacciones
•
Gestor de Concurrencia
•
Gestor de Almacenamiento
•
Dispositivos de caché
•
Sistemas RAID de almacenamiento
•
Configuración de Red/Comunicaciones
•
Entre otros
Afinación de Desempeño de una BDD 2. Gestión del Rendimiento
Factores que influyen en el rendimiento de BDD: 4. Optimización/Afinamiento •
Es la capacidad que tienen las bases de datos relacionales de realizar acciones correctivas a las consultas internamente para hacer mas eficiente el camino de acceso a los datos.
5. Contención •
Es la condición en la que dos o mas componentes de la carga de trabajo intentan utilizar un mismo recurso de una manera conflictiva. Mientras la contención aumenta, la capacidad de procesar datos disminuye.
Afinación de Desempeño de una BDD 2. Gestión del Rendimiento •
Por lo anterior, Gestión del rendimiento de base de datos se puede definir como: > La Optimización y afinamiento del uso de recursos para incrementar la capacidad de procesamiento y disminuir la contención, permitiendo que sea procesada la mayor carga de trabajo. > Afinación de Desempeño de una BDD es un proceso proactivo encaminado a detectar posibles cuellos de botella mediante monitoreo del DBMS así como lograr que los tiempos de ejecución de los distintos procesos de un sistema disminuyan, haciendo uso del menor número de recursos posibles.
Afinación de Desempeño de una BDD 2. Gestión del Rendimiento •
Consideraciones del rendimiento de las BDD: > Del 75% al 80% de todos los problemas de rendimiento de una base de datos son causadas por código pobre SQL > Aunque un sistema esté en un inicio muy eficiente, puede degradarse por varias razones como: crecimiento de la Base de Datos, usuarios adicionales, usuarios concurrentes, cambios en el negocio, etc.
Afinación de Desempeño de una BDD 2. Gestión del Rendimiento •
Problemas causados por un pobre código SQL: > Búsquedas secuenciales en las tablas. > Junturas en las aplicaciones en vez de junturas en SQL. > Métodos de junturas no eficientes. > Código SQL eficiente dentro de código de aplicaciones ineficiente (bucles). > Formulación de subconsultas ineficientes (exists) > Ordenamientos innecesarios (group by, union)
Afinación de Desempeño de una BDD 2. Gestión del Rendimiento •
Otros factores que afectan al rendimiento son: > La asignación de memoria (buffer / caché para datos, SQL, autorización). > Opciones de registro de logs (caché, el tamaño del registro, segmentos de rollback ). > Eficiencia de E/S (separación de las tablas e índices en el disco, el tamaño de la base de datos, archivos fragmentados y extendidos). > La carga de trabajo de la aplicación y de la base de datos en el servidor. > Definiciones de esquemas de bases de datos.
Afinación de Desempeño de una BDD 3. Técnicas para optimización de bases de datos
A. Ajuste del sistema. •
El software de otros sistemas con los que interactúa el DBMS incluye: el sistema operativo, software de red, sistemas de colas de mensajes, middleware, procesadores de transacciones , etc.
•
Por tal motivo es necesario no solo realizar un análisis del comportamiento del DBMS, sino una evaluación integral de los sistemas y de esta manera ajustar y afinar todos los componentes y asegurar un rendimiento óptimo.
Afinación de Desempeño de una BDD 3. Técnicas para optimización de bases de datos
B. Ajuste de la base de datos •
•
El rendimiento puede verse afectado por el diseño físico de la base de datos, incluyendo la normalización, almacenamiento en disco, el número de tablas, diseño de índices, el uso de DDL, seguridad y sus parámetros asociados. La ubicación física de los archivos de base de datos en sistemas de disco afecta al rendimiento de las aplicaciones de acceso a los datos, por lo que se debe tomar en cuenta que los archivos de datos crecen y degradan el rendimiento .
Afinación de Desempeño de una BDD 3. Técnicas para optimización de bases de datos
B. Ajuste de la base de datos •
Así mismo para obtener alto rendimiento de las BDD es fundamental tomar en cuenta los siguientes parámetros: > Seleccionar el tipo de base de datos apropiado > Diseñar correctamente la estructura de la base de base de datos > Optimizar las consultas SQL > No almacenar objetos binarios de gran tamaño
Afinación de Desempeño de una BDD 3. Técnicas para optimización de bases de datos
C. Ajuste de Aplicación •
•
El 75% de los problemas de rendimiento son causados por códigos de aplicaciones no apropiados tanto en SQL como en lenguajes de aplicación como: Java, C#, C ++, Visual Basic, Fox Pro, etc. En las aplicaciones se debe tomar en cuenta: > Las cadenas de conexión > Abrir/Cerrar conexiones en los momentos apropiados > No almacenar conexiones y recordsets en objetos Session o Application
Afinación de Desempeño de una BDD 3. Técnicas para optimización de bases de datos
C. Ajuste de Aplicación > Extraer de la base de datos sólo la información necesaria > Utilizar cursores y modos de bloqueo apropiados > Utilizar variables objeto > Aumentar el tamaño del caché. Por ejemplo en la propiedad CacheSize del objeto RecordSet () > Una conexión por página (Evitar conexiones a una misma base de datos más de una vez en una misma página)
Afinación de Desempeño de una BDD 4. Rendimiento de Aplicaciones •
Para asegurar la disponibilidad de la aplicación y el logro de los acuerdos de nivel de servicio (SLA) con todas las partes relevantes, el personal del departamento de TI deben estar al tanto de quién utiliza la aplicación, cuándo acceden a ella, dónde se encuentran, qué hacen y mucho más.
•
La gestión deficiente del rendimiento de la aplicación produce efectos negativos que se pueden sentir en toda la empresa: desde productividad reducida de los empleados e ineficacia del departamento de TI a la insatisfacción del cliente y pérdida de negocio.
Afinación de Desempeño de una BDD 4. Rendimiento de Aplicaciones
•
•
El rendimiento es conocido también en la ingeniería y arquitectura de servidores como «Productividad»
Rendimiento (Productividad): Cantidad de Trabajo realizado por el sistema en una unidad de Tiempo. Está relacionado con el costo / beneficio de los sistemas.
Afinación de Desempeño de una BDD 4. Rendimiento de Aplicaciones •
•
•
El rendimiento debe aplicarse a cada uno de los componentes del sistema, es decir, productividad de: CPU, canales de E/S, memoria, etc. La productividad cuantitativamente se la representa mediante la siguiente ecuación:
Para tal efecto es necesaria la caracterización de la carga: Configuración del sistema, multiprogramación permitido por el HW, configuración del sistema HW & SW, algoritmos de administración y asignación de recursos, velocidad de los componentes HW & SW.
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
Índices: > El índice de una base de datos es una estructura de datos que mejora la velocidad de las operaciones, permitiendo un rápido acceso a los registros de una tabla o vista. > Los índices son construidos sobre árboles B, B+, B* o sobre una mezcla de ellos, funciones de cálculo u otros métodos. La B viene de balanceados
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
Índices: > Es posible agregar, modificar y quitar índices sin que esto afecte al esquema de la base de datos o al diseño de la aplicación. > La característica fundamental es la predominancia del tiempo de las operaciones de I/O de disco en el tiempo de ejecución total, es decir , busca minimizar el número de operaciones de R/W de bloques de datos del disco duro o soporte físico.
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
Índices: > Varios DBMS crean automáticamente índices cuando se crea una restricción "primary key" o "unique" en una tabla. > En general, los índices facilitan la recuperación de datos, permitiendo el acceso directo y acelerando las búsquedas, consultas y otras operaciones que optimizan el rendimiento general.
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) • Índices: > Algunos tipos de índices: » Bitmap Indexes » Reverse Key Indexes » Partitioned Indexes » Ordered Indexes » Clustered Indexes » Non-clustered Indexes » XML Indexes
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
Índices:
Estructura interna de un índice
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
Índices:
En el índice clustered, la leaf level es la pagina de datos. En otras palabras, es similar a una guía telefónica, los registros con el mismo valor de campo se agrupan juntos. Un índice clustered determina la secuencia de almacenamiento de los registros en una tabla. Se utilizan para campos por los que se realizan búsquedas con frecuencia o se accede siguiendo un orden
Índice Clustered
Una tabla sólo puede tener UN índice clustered. El tamaño medio de un índice clustered es aproximadamente el 5% del tamaño de la tabla.
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
Índices:
Índice Clustered, Mullins C. S.
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
Índices:
En un índice non-clustered la clave por la que buscamos tiene un puntero a la página de datos donde se encuentra el registro. Es como el índice de un libro, los datos se almacenan en un lugar diferente al del índice, los punteros indican el lugar de almacenamiento de los elementos indizados en la tabla. Un índice non-clustered se emplea cuando se realizan distintos tipos de búsquedas frecuentemente, con campos en los que los datos
son únicos. Una tabla puede tener hasta 249 índices non-clustered.
Índice Non-Clustered
Si no se especifica un tipo de índice, de modo implícitamente será non-clustered
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
Índices:
Índice Unclustered, Mullins C. S.
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
Sintaxis Índices: > Para crear Índices: CREATE INDEX…. > Para eliminar un índice debemos emplear la sentencia DROP INDEX. > Para modificar un índice existente: ALTER INDEX Nota: Se debe tomar en cuenta la definición de las anteriores estructuras en función del DBMS que se esté administrando
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
¿Dónde se encuentran los Índices en el DBMS?: > Los índices son objetos de la BDD, es decir, es una estructura lógica que se almacena en los archivos de datos. > Por lo tanto, los índices se encuentran en el módulo denominado “Datos Almacenados” del DBMS.
Afinación de Desempeño de una BDD 5. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
¿Qué columnas son candidatas a tener índices?: > De uso general en el WHERE o HAVING de las consultas > Columnas en donde las consultas utilizan frecuentemente la cláusula GROUP BY. > Columnas que se utilizan para ordenar los resultados en una cláusula ORDER BY. > Columnas para referenciar a otra tabla como clave externa (Foreing key). > Columnas que especifican condiciones JOIN.
