Transcript MySQL - DBA.MX

Tunning para
MySql
Pasos para el ambiente de
performance test
•
•
•
•
Base de datos MySql 5.4 o superior
2gb ram
Acceso cuenta root
Tablas para realizar pruebas de estrés
Nociones básicas de tunning
• ¿Quién realiza el tuneo de la base de datos?
• Todas las personas involucradas con el
Software de Mysql, deben de estar envueltas
en el proceso de tunning.
Diseñadores de
la aplicación
Desarrolladores
de la aplicación
Administradores
de bases de
datos
Administradores
de sistemas
¿Qué es lo que se tunea?
• El diseño cuidadoso de los sistemas y
aplicaciones es esencial para el performance
optimo de cualquier base de datos.
• En muchos casos la ganancia se puede lograr
haciendo un tunning de la aplicación, cuando
se debe de considerar el tuneo es cuando la
aplicación se encuentra en su ultima fase.
El diseño de
la aplicación
El desarrollo
de la
aplicación
Estructura de
la Base de
datos
El Hardware
Fases del tunning
Diseño de la
aplicación
Desarrollo de la
aplicación
Configuración de la
base de datos
Resolución de problemas
Mantenimiento de
la aplicación y
crecimiento
Test Plan
• El test plan es necesario para el ciclo de
testing, aunque parezca distractor, ya que le
da un sentido de dirección
• Evita el típico trabajo duplicado en grupos de
varias personas
• Es la base para el reporte final del análisis
• *ver testplan.txt
Fuentes de los problemas
•
•
•
•
Mala elección de Indexes
Diseño del esquema insuficiente
Malas practicas de programación SQL
Las variables de servidor no tuneadas
apropiadamente
• Cuellos de botella de hardware o red
Preparación del test plan
• Quitar el cache de resultados al ejecutar las
pruebas de benchmark (seteando =0 el cache)
• Utilizar lo mas posible el comando explain
• Utilizar la herramienta mytop
Cambiar una variable a la vez
• Muchas veces se intenta modificar muchas
variables de los parámetros de sistema, pero
no siempre es la forma mas eficiente.
• Puede beneficiar y perjudicar al mismo tiempo
• Se obtiene mejor experiencia de testing,
cambiando una variable a la vez, ejecutar el
test y moverse al siguiente candidato
Herramienta MysqlAdmin
•
•
•
•
•
•
•
•
•
•
•
extended-status Show system status
flush-hosts Flush all cached hosts
flush-logs Flush all logs
flush-status Clear status variables
flush-tables Flush all tables
flush-threads Flush the thread cache
flush-privileges Reload grant tables (same as reload)
kill id,id,... Kill mysql threads
processlist Show list of active threads in server
refresh Flush all tables and close and open logfiles
variables Prints variables available
Comando ShowEngine
• Con este comando nos muestra los engines
que están disponibles para MySql y si se
encuentra soportado.
• Seleccionar MyIsam sobre INNODB se debe
seguir las recomendaciones de la aplicación,
ya que cuentan con diferentes ventajas y
desventajas.
INNODB
• Soporte de transacciones
• Bloqueo de registros
• Nos permite tener las características ACID (Atomicity,
Consistency, Isolation and Durability: Atomicidad,
Consistencia, Aislamiento y Durabilidad en español),
garantizando la integridad de nuestras tablas.
• Es probable que si nuestra aplicación hace un uso
elevado de INSERT y UPDATE notemos un aumento de
rendimiento con respecto a MyISAM.
• Recomendado para alto volumen de datos como un
datawarehouse
MyISAM
• MyISAM
• Mayor velocidad en general a la hora de recuperar
datos.
• Recomendable para aplicaciones en las que dominan
las sentencias SELECT ante los INSERT / UPDATE.
• Ausencia de características de atomicidad ya que no
tiene que hacer comprobaciones de la integridad
referencial, ni bloquear las tablas para realizar las
operaciones, esto nos lleva como los anteriores
puntos a una mayor velocidad.
Innodb storage
•
•
•
InnoDB se recupera de un problema volviendo a ejecutar sus logs, mientras
que MyISAM necesita repasar todos los índices y tablas que hayan sido
actualizados y reconstruirlos si esos cambios no han sido escritos en disco. El
primer proceso requiere más o menos el mismo tiempo siempre, mientras que
el segundo aumenta con el tamaño de la base de datos.
InnoDB almacena físicamente los registros en el orden de la clave primaria,
mientras que MyISAM los guarda en el orden en que fueron añadidos. Cuando
la clave primaria se escoge de acuerdo con las necesidades de las consultas
más habituales esto puede suponer una mejora sustancial del rendimiento.
Por otro lado, si los datos se insertan en un orden que difiera sustancialmente
del orden de la clave primaria, se obliga a InnoDB a reordenar mucho los datos
para mantenerlos en el orden adecuado.
InnoDB no dispone de la compresión de datos de la que disfruta MyISAM, de
modo que tanto el espacio en disco como la caché en la memoria RAM
pueden ser más grandes. Este problema se ha reducido en MySQL 5.0,
reduciéndolo en aproximadamente un 20%.
Limitaciones de MyISAM
•
•
•
•
•
•
Limitaciones de MyISAM
No tiene llaves foraneas ni deletes ni updates en cascada
No tiene habilidades de rollback
No cumple con los estandares de ACID
Limite de rows de 4,284,867,296
Maximo 64 indices por row
• MyISAM usa bloqueo a nivel tabla, inconveniente para
inserts/deletes y updates muy altos
• La velocidad de lectura es superior a INNODB
Limitaciones de INNODB
• No tiene índices de texto completo
• No se puede comprimir
• Las tablas ocupan mas espacio
Cuando usar cada Engine
• ¿Cuándo usar MyISAM?
• El engine de MyISAM fue hecho para cuando la
base de datos recibe muchos mas querys que
updates, ya que las operaciones de lectura son
mucho mas rápidas
• MyISAM se recomienda usar cuando los
insert/update tengan un ratio del 15%
• ¿Cuándo usar INNODB?
• INNODB usa bloqueos a nivel ROW, tiene
capacidades de commit y recovery, es tolerante a
fallas
Convertir tipos de Engines
• Para crear una tabla con un engine predefinido
• Create table Test (rid INT) ENGINE=INNODB;
• Para definir el default por sesión:
• Set Storage_engine=INNODB
• Convertir tablas de un engine a otro
• Alter table test engine=INNODB
Consideraciones de diseño de INNODB
Emula la arquitectura de Oracle
Sistemas unicos
Buffering de inserts
Index hash
DataDictionary Interno
Undo
Insert Buffer
MySQL Replication info
Limites de tamaño con InnoDB
•
•
•
•
•
Tamaño máximo de una tabla: 32GB
Columnas por tabla: 1000
Tamaño máximo de la llave 3500 caracteres
Tamaño máximo del tablespace 64TB
Numero máximo de transacciones
concurrentes: 1023
InnoDB Monitor
• El monitor de InnoDB provee información
sobre el estado interno de INNODB, esta es
información valiosa para realizar el tunning
• Para iniciar el monitor se crea una tabla con
un nombre especial, que hace que el InnoDB
escriba el output del monitor periódicamente,
además el Monitor de InnoB es accesible
sobre demanda ejecutando:
• Show engine innodb status;
Lo que muestra el monitor de Innodb
•
•
•
•
•
•
•
El estándar que muestra el monitor de innodb es:
Las tablas y registros bloqueados en cada sesión activa.
Los Locks que esperan una transacción
Los semáforos que esperan una transacción
Transacciones de I/O pendientes
Estadísticas del Buffer pool
Buffer del insert y purge del thread de Innodb
• La tabla que hay que crear para el monitor es:
• Innodb_monitor
• Create table innodb_monitor (a int) engine=innodb;
Monitor de InnoDB
• El monitor de locks
• Es igual al monitor estándar, pero muestra información mas
completa sobre los bloqueos de las tablas
• La tabla para el lock monitor es inno_lock_monitor
• Semaphores
• Muestra las estadísticas de los semáforos que administran
los bloqueos en las tablas, un alto índice de estos
semáforos se puede deber a un alto paralelismo de los
querys, para resolver este problema se puede ajustar la
variable de sistema innodb_thread_concurrency,
definiendo un valor mas pequeño al default
Monitor de Innodb de tablespace
• Monitor que verifica los tablespaces
• Para activarlo es creando la tabla
• Innodb_file_per_table
Comando Show Variables
• Para buscar variables especificas es con ‘Show
variables like ‘%cache%’;’
• Para ver las estadísticas de las variables
definidas es con el comando ‘Show status;’
• Para ver el reporte de las conexiones y los
usuarios es con el comando ‘show processlist’
• ‘Show index’ para ver los índices creados
• Show innodb status – nos muestra el status
del engine innodb
MySQL Server Tunning
• Parámetros básicos de tunning:
• Max_connections – El numero máximo de
conexiones al servidor
• Key_buffer_size – proporcionalmente para el
tamaño de los índices de la base de datos, se
recomienda 256mb en una base estándar.
• Thread_cache_size – el cache para los threads
• Table_cache_size – se define dependiendo del
status de ‘opened_tables’
Scripts de autotunning de MySql
• Mysqltuner.pl – script de phyton que compara
las variables y las tablas de monitoreo
InnoDB Lock Modes
• InnoDB implementa bloqueos a nivel campo, existen dos
tipos de bloqueos:
• Un bloqueo Shared, que permite la lectura del campo
• Un bloqueo Exclusive, que permite la transacción para
actualizar o eliminar el campo.
• Por ej. Si las transacciones T1 mantienen un bloqueo en la
columna X, si se quiere lanzar una segunda transaccion T2
ocurre lo siguiente:
• El request de T2 obtiene ademas un bloqueo para la
columna X y tanto T1 como T2 lockean el campo.
• Adicionalmente InnoDB soporta locking granular, que
permite la coexistencia de bloqueos de campos y bloqueos
de toda la tabla.
• Los shared locks se hacen intencionalmente
con el comand
• Select … lock in share mode
• Las compatibilidades
son
X
IXentre los
S bloqueos
IS
X
Conflicto
Conflicto
Conflicto
Conflicto
las siguientes:
IX
Conflicto
Compatible
Conflicto
Compatible
S
Conflicto
Conflicto
Compatible
Compatible
IS
Conflicto
Compatible
Compatible
Compatible
• Desarrollo de la aplicación
• Optimización del SQL
Optimización de índices
• Los índices mal hechos son iguales a no tener ningún
índice y son una forma de bajar el performance
• La buena selectividad de los campos de índices
• Los índices de múltiples columnas, el orden de los
campos es muy importante
• Tener demasiados índices también quita performance
• mientras mas datos de un índice quepan en un solo
bloque de memoria, mas rápido será el query
Sintaxis creación de índices
• create index Test on Tabla_prueba col_name (columna1,
columna2) using BTREE;
• CREATE TABLE lookup (id INT) ENGINE = MEMORY;
• CREATE INDEX id_index ON lookup (id) USING hash order by
;
• Los ordenes de las columnas son importantes, tambien se
puede declarar si se acomodaran en orden ascendente o
decendente.
• El tipo de indice BTREE se usa para la mayoria de los casos,
solo en caso de las tablas con engine en Memory se
recomienda el uso de hash
Recomendaciones de índices
• Minimizar el tamaño de la llave primaria que
es usada para referenciar otras tablas
• Usar columnas con auto_increment puede ser
mas optimo
Uso del Query Execution plan
•
•
•
•
•
•
•
Para ver el QEP, se utiliza el comando explain
El explain te dice:
en que orden son leídas las tablas
que tipos de operaciones de lectura son hechas
que índices han sido utilizado
Como las tablas se referencian entre si
cuantos campos el optimizador estima obtener
de cada tabla
Optimización de los índices
• Un índice de toda la columna no siempre es necesario
• Los índices compuestos nos sirven para buscar en las primeras
columnas del índice
Logueando los querys lentos
• El slow query log
•
-los querys que toman mucho tiempo son
•
long_query_time
•
- Tamben se pueden loguear los querys que no
usan indices con
•
--log-queries-not-using-indexes
•
-Para tener un log de los comandos administrativos
usar
•
-log-slow-admin-statements
•
-para analizar el contenido del slow log se usa
•
mysqldumpslow