BASES DE DA DATOS TOS II (DISTRIBUIDAS) Facultad Facult ad de Ingeniería y Ciencias Ingeniería en Sistema e Informátic Informáticaa - Electrónica y Redes
Afinación de Desempeño de una BDD y Optimización de Consultas Ing. Jonathan Carrillo Quito, Septiembre 2017
Contenido: 1. Introducción 2. Técnicas para optimización de bases de datos A. B. C. D.
Requisitos mínimos Ajuste del sistema Ajuste de de la la ba base de de da datos Ajuste de de Ap Aplicación
3. Rendimiento de Aplicaciones 4. Ajuste de la base de datos Índice clustered Índice unclustered o non-clustered
5. Gestión del Rendimien Rendimiento to Carga de trabajo (workload) Capacidad del Servidor Recursos adicionales de hardwar hardware e y software Optimización/Afinamiento Contención
6. Conclusión
Afinación de Desempeño de una BDD 1. Introducción •
•
El rendimiento depende del tamaño y número de registros en los archivos, es por aquello que es indispensable realizar un análisis de consultas y transacciones que se espera ejecutar. Este análisis determina las estructuras de acceso (índices) y las estructuras de almacenamiento (organización de archivos*) de la Base de Datos, en función del tipo de operación y los atributos que son tratados en el motor de la Base de Datos.
*archivo/fichero: es una agrupación de datos como una unidad de almacenamiento almacenam iento y organizado de forma estructurada .
Afinación de Desempeño de una BDD 1. Introducción •
Para tal efecto existen técnicas que apoyan a cumplir con ,os objetivos del diseño físico de bases de datos, entre ellos: A. Requerimientos mínimos B. Ajuste del Sistema C. Ajuste de la Base de Datos D. Ajuste de las aplicaciones
Afinación de Desempeño de una BDD 2. Técnicas para optimización de bases de datos
A. Requerimientos mínimos. •
Entre los principales: > Hardware: » Memoria Física (RAM) » Memoria Virtual » Procesador » Espacio en disco » Adaptación de Vídeo
Afinación de Desempeño de una BDD 2. Técnicas para optimización de bases de datos
B. 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 2. Técnicas para optimización de bases de datos
C. 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 2. Técnicas para optimización de bases de datos
C. 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 2. Técnicas para optimización de bases de datos
D. 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 2. Técnicas para optimización de bases de datos
D. 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 de la 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 3. 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 3. 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 3. 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 4. 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 4. 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 4. 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 4. 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 4. 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 4. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
Índices:
En el índice clustered (agrupado), 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 4. 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 4. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
Índices:
En un índice non-clustered (no agrupado) 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ícito será non-clustered
Afinación de Desempeño de una BDD 4. 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 4. Ajuste de la base de datos
Selección de la organización de la estructura de acceso (índices) •
•
Cuando se crea un índice tipo Clustered en alguna columna de las tablas de la base de datos, la tabla se ordenará físicamente por el índice solicitado. Cuando se crea un índice tipo Nonclustered a diferencia del anterior, no modifican el orden físico de los registros en la tabla original, estos índices guardan en otra estructura una copia de los valores involucrados en la clave y un puntero al registro original de la tabla.
Afinación de Desempeño de una BDD 4. 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…. index » Create TIPODEINDICE NOMBREINDICE on TABLA(columna/s)); > Para eliminar un índice debemos emplear la sentencia DROP INDEX. » Drop index NOMBREINDICE ON TABLA; > 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 4. 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 4. 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?: > Crear índices para los atributos o conjunto de atributos que sean clave candidata, es decir, atributo/s clave primaria que tengan asignada la restricción de unicidad > Se debe crear el menor número de índices posible para las tablas que se actualicen con frecuencia, porque la actualización de los índices ralentiza las operaciones de actualización.
Afinación de Desempeño de una BDD 4. 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.
Afinación de Desempeño de una BDD 5. 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 5. 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 5. 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 5. 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 5. 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 5. 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 5. 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 5. 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 5. 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. Exists: cierto si el conjunto resultante de la subconsulta no está vacío. NOT EXISTS: cierto si vacío)
> Ordenamientos innecesarios (group by, union)
Afinación de Desempeño de una BDD 5. 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.
