PDF de programación - Optimización del rendimiento con MySQL

Optimización del rendimiento con MySQL ( deCharlas Mayo 2012)

Vicente Víctor Jiménez Cerrada <[email protected]>

@capitangolo

Bienvenidos a "Optimización del rendimiento con MySQL".
Soy Víctor Jiménez y seré vuestro ponente para hoy.

Trabajo en Warp Networks S.L. http://www.warp.es
Donde nos dedicamos a varias cosas, entre ellas, formación y consultoría
MySQL

Trabajo en Warp Networks S.L. http://www.warp.es
Donde nos dedicamos a varias cosas, entre ellas, formación y consultoría
MySQL

Agenda

5"

20"

30"

40"

…

1- Introducción. ¿Por qué optimizar?

2- Arquitectura de MySQL

3- Optimización de consultas

4- Ejercicios

5- Ruegos y Preguntas

1 - Introducción ¿Por Qué
optimizar?

2- Arquitectura de MySQL
3- Optimización de consultas
4- Ejercicios
5- Ruegos y Preguntas

1 - Introducción ¿Por Qué optimizar?

Desarrollamos nuestras apps web sin importarnos mucho el rendimiento.
Cuando nuestra web tiene éxito y se convierte en la gallina de los huevos
de oro, tenemos más solicitudes, y si no tenemos cuidado…

1 - Introducción ¿Por Qué optimizar?

… nuestro servidor se cuelga.
A esto se le llama morir de éxito.

1 - Introducción ¿Por Qué optimizar?

Pasos para no morir de éxito

Optimizar la aplicación
Optimizar la base de datos
Escalar

1 - Introducción ¿Por Qué optimizar?

Optimización

1 - Introducción ¿Por Qué optimizar?

Optimización

Hacer más con lo mismo

1 - Introducción ¿Por Qué optimizar?

Optimización

Hacer más con lo mismo
Objetivo: Aumentar Consultas / segundo

1 - Introducción ¿Por Qué optimizar?

Optimización

Hacer más con lo mismo
Objetivo: Aumentar Consultas / segundo
¿Cómo?: Menor tiempo de ejecución

1 - Introducción ¿Por Qué optimizar?

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

Tenemos una consulta que tarda 2,5 segundos en ejecutarse si se ejecuta
sola.
Pero lo normal es que esa consulta se ejecute en varios procesos en
paralelo.

1 - Introducción ¿Por Qué optimizar?

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

Tenemos una consulta que tarda 2,5 segundos en ejecutarse si se ejecuta
sola.
Pero lo normal es que esa consulta se ejecute en varios procesos en
paralelo.

1 - Introducción ¿Por Qué optimizar?

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

Dado que la máquina tiene más recursos ocupados, es posible que tarde
más en ejecutarse.
En este caso, se ha ejecutado la consulta cinco veces en paralelo, cada
una un segundo más tarde que la anterior.

1 - Introducción ¿Por Qué optimizar?

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

Dado que la máquina tiene más recursos ocupados, es posible que tarde
más en ejecutarse.
En este caso, se ha ejecutado la consulta cinco veces en paralelo, cada
una un segundo más tarde que la anterior.

1 - Introducción ¿Por Qué optimizar?

C = 5

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

En un momento se están ejecutando 5 consultas a la vez.
El servidor necesita poder soportar estos picos.

1 - Introducción ¿Por Qué optimizar?

C max = 1

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

Si optimizamos esas consultas para que tarden medio segundo...

1 - Introducción ¿Por Qué optimizar?

C max = 1

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

... sólo se ejecuta una consulta cada vez.
Reduciendo el nivel de concurrencia que tiene que soportar el servidor.

1 - Introducción ¿Por Qué optimizar?

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

Dejando muchos más recursos libres.

1 - Introducción ¿Por Qué optimizar?

Más información

http://www.slideshare.net/capitangolo/no-mueras-de-exito

Dejando muchos más recursos libres.

1- Introducción. ¿Por qué optimizar?

2 - Arquitectura MySQL

3- Optimización de consultas
4- Ejercicios
5- Ruegos y Preguntas

2 - Arquitectura MySQL

mysqld
mysqld-nt

mysql
Workbench
PHP My Admin
…

MySQL tiene una arquitectura cliente servidor.
Aunque hay algunos programas 'ninjas' que acceden directamente a los
datos.

2 - Arquitectura MySQL

mysqld
mysqld-nt

myisamchk
myisampack

mysql
Workbench
PHP My Admin
…

MySQL tiene una arquitectura cliente servidor.
Aunque hay algunos programas 'ninjas' que acceden directamente a los
datos.

2 - Arquitectura MySQL

/usr/local/mysql/data

test

table.frm

world

City.frm

Country.frm

CountryLanguage.frm

Hostname.pid

Hostname.err

En disco guarda tablas, logs y archivos de estado.

2 - Arquitectura MySQL

Uso de Memoria

Thread Cache
Buffers y Cachés
Tablas en memoria
Tablas temporales
Buffers de cliente

2 - Arquitectura MySQL

Uso de Memoria

Por Instancia

Reservado en el arranque del servidor
Compartido para todos los usuarios
Query Cache
Key Cache
InnoDB Buffer Pool

Por Sesión

Reservado por cada conexión
Principalmente para gestionar los resultados
sort_buffer
join_buffer
read_buffer

Hay que tener cuidado al configurar las variables de sesión.
20MB de sort_buffer x 100 conexiones = 2GB de memoria

2 - Arquitectura MySQL

API C

Query

Cache

Intérprete

Optimizador

Executador

Motores

MyISAM InnoDB

Memory CSV

Subsistemas

Funciones base

Hilos

Buffers y cachés

Red

Logs

Acceso y Permisos

2 - Arquitectura MySQL

Motores de Almacenamiento

Gestionan la persistencia y recuperación de los datos
Configuración a nivel de Tabla
Oficiales:

MyISAM Motor por defecto en MySQL 5.0
InnoDB Motor por defecto en MySQL 5.5
Memory
Archive
Blackhole
CSV
…

De terceros:

solidDB Nitro
InfoBrigth PBXT

2 - Arquitectura MySQL
2.1 - MyISAM

2 - Arquitectura MySQL » MyISAM

Características de MyISAM (I)

/usr/local/mysql

world

City.frm

City.MYD

City.MYI

MyISAM guarda la información en dos archivos
.MYD(ata) y .MYI(ndex)

2 - Arquitectura MySQL » MyISAM

Características de MyISAM (II)

No soporta transacciones
Bloqueos a nivel de tabla
Para un backup binario, copiar:

.frm
.MYD
.MYI

Un backup binario es portable

2 - Arquitectura MySQL » MyISAM

Características de MyISAM (III)

Compresión de índices

prefijos en índices de tipo texto

Fulltext

ALTER TABLE table ADD FULLTEXT(column1, column2)
SELECT […] WHERE MATCH (column1, column2) AGAINST ('TEXT');

Concurrent inserts

concurrent_insert = 0 | 1 | 2

R-Tree index

Datos Geoposicionados (GIS)

2 - Arquitectura MySQL » MyISAM

Bloqueos MyISAM (I)

Bloqueo a nivel de tabla
Problemático cuando hay concurrencia

2 - Arquitectura MySQL » MyISAM

Bloqueos MyISAM (II) - Consultas lentas

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

Los select obtienen bloqueo de lectura compartido.
El insert solicita un bloqueo exclusivo de escritura.
Todos los demás selects posteriores esperan a que el insert libere el
bloqueo.
Hasta que el primer select termina, no se terminan de ejecutar las demás
consultas.
¡¡¡LLegamos a tener concurrencia 7!!!

2 - Arquitectura MySQL » MyISAM

Bloqueos MyISAM (II) - Consultas lentas

SELECT x FROM tabla …

SELECT x FROM tabla

INSERT INTO tabla

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

Los select obtienen bloqueo de lectura compartido.
El insert solicita un bloqueo exclusivo de escritura.
Todos los demás selects posteriores esperan a que el insert libere el
bloqueo.
Hasta que el primer select termina, no se terminan de ejecutar las demás
consultas.
¡¡¡LLegamos a tener concurrencia 7!!!

2 - Arquitectura MySQL » MyISAM

Bloqueos MyISAM (II) - Consultas lentas

SELECT x FROM tabla …

SELECT x FROM tabla

INSERT INTO tabla

SELECT x FROM tabla …

SELECT x FROM tabla …

SELECT x FROM tabla …

SELECT x FROM …

SELECT …

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

Los select obtienen bloqueo de lectura compartido.
El insert solicita un bloqueo exclusivo de escritura.
Todos los demás selects posteriores esperan a que el insert libere el
bloqueo.
Hasta que el primer select termina, no se terminan de ejecutar las demás
consultas.
¡¡¡LLegamos a tener concurrencia 7!!!

2 - Arquitectura MySQL » MyISAM

Bloqueos MyISAM (II) - Consultas lentas

SELECT x FROM tabla …

SELECT x FROM tabla

INSERT INTO tabla

SELECT x FROM tabla …

SELECT x FROM tabla …

SELECT x FROM tabla …

SELECT x FROM …

SELECT …

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

Los select obtienen bloqueo de lectura compartido.
El insert solicita un bloqueo exclusivo de escritura.
Todos los demás selects posteriores esperan a que el insert libere el
bloqueo.
Hasta que el primer select termina, no se terminan de ejecutar las demás
consultas.
¡¡¡LLegamos a tener concurrencia 7!!!

2 - Arquitectura MySQL » MyISAM

Bloqueos MyISAM (II) - Consultas lentas

SELECT …

SELECT …

INSERT…

SELECT …

SELECT …

SELECT …

SELECT …

SELECT …

0s

1s

2s

3s

4s

5s

6s

7s

8s

9s

Si optimizamos el primer select, los bloqueos bajan exponencialmente.
Concurrencia 4

2 - Arquitectura MySQL » MyISAM

Key Cache

Caché para índices MyISAM

key_buffer_size > 0

En MyISAM existe una caché de claves

2 - Arquitectura MySQL
2.2 - InnoDB

2 - Arquitectura MySQL » InnoDB

Características de InnoDB (I): Almacenamiento

/usr/local/mysql

test

table.frm

ibdata1

ib_logfile0

ib_logfile1

InnoDB guarda toda la información de todas las tablas en el tablespace.
Archivo ibdata

2 - Arquitectura MySQL » InnoDB

Características de InnoDB (II): Files per table

innodb_file_per_table

/usr/local/mysql

test

table.frm

table.ibd

ibdata1

ib_logfile0

ib_logfile1

Si configuramos innodb_file_per_table tenemos un sub-espacio de tabla
por cada tabla
Archivo .ibd
El espacio de tabla sigue conteniendo información de cada tabla, es
necesario.

2 - Arquitectura MySQL » InnoDB

Características de InnoDB (III)

Transacciones full ACID
Bloqueo a nivel de fila

El bloqueo se realiza en la PK
Buscando rangos, se bloquean también los huecos

Bloqueo a nivel de tabla
Cachea tanto índices como datos

2 - Arquitectura MySQL » InnoDB

InnoDB Buffers (I)

Buffer Pool

Caché de datos e índices

Log Buffer

Log de transacciones

logfiles

Log de transacciones
Redo log

MYSQL SERVER

LOG BUFFER

BUFFER
POOL

ibdata

Diccionario de datos
Undo log

commit
& checkpoints

checkpoints

ib_logfiles

ibdata

2 - Arquitectura MySQL » InnoDB

InnoDB Buffers (II): Configuración

innodb_flush_lo