mysql en la vida real

MySQL en la vida realMySQL en la vida real

Muchos consejos obvios, pero útiles...Muchos consejos obvios, pero útiles...

Davide FerrariSystem Administrator

30 Octubre 2009 PHP Conference Barcelona 2

MySQL hoy en día

Desarrollado por Sun MicrosystemVersión estable 5.1.x

Ya está en beta la 5.4 y en alfa la 6.0

Es la M de LAMPYa es un proyecto maduro y algunos de los mitos de las versiones 3.23/4.0 hay que dejarlos atrásVasto ecosistema opensourceHay un fork: Drizzle


Resumen de las “nuevas” versiones

MySQL 5.1Stable después de una laaaaaarga gestaciónAñade arquitectura a plugins, particiones, nueva replicación y mucho másVivió el cambio MySQL AB/Sun Microsystem

MySQL 5.4Originalmente no planeadaOrientada a las mejoras en escalabilidad vertical y rendimiento100% made in Sun y la ultima así


Resumen de lo que vamos a ver hoy

ReplicaciónMaster-slave(s)Master-masterOtras formas

DRBD (Distributed Replicated Block Device) y HeartbeatConsejos e ideas


La replicación en MySQL

Qué és la replicación?


La replicación en MySQL

Es hacer que todos los datos importantes en un servidor MySQL estén también en otro (o más) y se

vayan actualizando de forma automática.


La replicación en MySQL: más en detalle

Ayuda a escalar en lectura



Ayuda a escalar en lecturaNo ayuda a escalar en escritura. Olvidadlo.



Ayuda a escalar en lecturaNo ayuda a escalar en escritura. Olvidadlo.Puede ayudar a mejorar la disponibilidad



Ayuda a escalar en lecturaNo ayuda a escalar en escritura. Olvidadlo.Puede ayudar a mejorar la disponibilidadPuede dar dolores de cabeza




Cuando se rompe (y lo notas)




Cuando se rompe (y lo notas)Cuando se corrompe (y no lo notas)



Hasta la 5.0, estaba basada en sentencias planas (SBL – Statements Based Replic.).

Falla en algunos casos como por ejemplo UUID(), UPDATE|DELETE … LIMIT sin ORDER BY etc

>= 5.1, soporta una replicación basada en rows (RBR – Row Based Replic.) o sea transmitiendo el valor y no la sentencia SQL

Genera un binlog mucho más grandeProtege de los fallos de la SBR

También hay una modalidad mixta, que se basa en SBR pero utiliza RBR en casos concretos



La replicación es un proceso asincrono



La replicación es un proceso asincronoLa replicación es un proceso mono-thread, se pierde el paralelismo de las peticiones y, por lo tanto, es generalmente más lento que los updates directos en el master



La replicación un proceso asincronoLa replicación es un proceso mono-thread, por lo tanto se pierde el paralelismo de las peticiones y, por lo tanto, es generalmente más lento que los updates directos en el masterBueno no, en realidad hay dos threads en el slave. I/O thread y SQL thread. Uno lee el binlog remoto y lo copia en el relay log locale y el otro ejecuta el relay log


Master - Slave(s)

Sin dudas és la topología de replicación más común


Master - Slave(s)

Sin dudas és la topología de replicación más comúnHay un master donde van las escrituras...


Master - Slave(s)


… y algunas lecturas


Master - Slave(s)


… y algunas lecturasCuidado con las SELECTs en transacciones (es ACID)


Master - Slave(s)



Hay N slaves donde van las lecturas...


Master - Slave(s)



Hay N slaves donde van las lecturas...… y escrituras. Si no, como se replican los datos? :)


Master - Slave(s)

Como distribuir el hardware entre master/slaves?


Master - slave(s)

Aparantemente, el master debe tener más hardware


Master - slave(s)

Aparantemente, el master debe tener más hardwareRecibe todas las escrituras...


Master - slave(s)

pero...


Master - slave(s)

En un contexto web, es mucho más probable que haya muchísimas más lecturas


Master - slave(s)

En un contexto web, es mucho más probable que haya muchísimas más lecturasAdemás, los esclavos hacen las mismas escrituras que el master


Master - slave(s)

Más recursos a los esclavos!


Master-Slave(s): consejos varios

En los slaves poner siempre el parametro read-only en my.cnf. Ahorra muchos problemas.Evitar de mezclar versiones distintas entre master y slaves. Si hay que mezclar, el slave que sea más reciente que el masterSi puedes tener 2 o más slaves, que 2 sean gemelos a nivel HW para poder hacer pruebas de tuning.Cada server obligatoriamente con su propio server ID. Falla todo si falta!!!


Master-Master

Qué es el master-master?


Master-Master

És un tipo de replicación entre 2 server donde ambos son master y slave.

Puede ser activo-pasivo (recomendado) o bien activo-activo.


Master-Master: activo/activo

No sirve para escalar en escrituraComo mucho, se gana un poco más de paralelismo

Si pasa algo con la replicación, es aún peor que un master-slave normalPuede ayudar a montar un site geograficamente distribuido


Master-Master: activo/pasivo

No ayuda a escalar en escritura (ooooooh)

Es una muy buena solución para la alta disponibilidadNo sólo en caso de crash físico si no también de operaciones lógicas como una ALTER gordaMMM (Multi Master replication Manager) puede ayudar en la automatización del proceso.


Master-Master: parametros

En configuración A/A es fundamental setear los auto_increment_increment = 2 auto_increment_offset = N

Tanto en A/A como en A/P log-slave-updates

Aquí también no olvidarse de los server ID.


DRBD

DRBD® es una solución 100% opensource de LinBit para la alta disponibilidad de un block device


DRBD más en detalle

Disponible para todas las distro LinuxPermite replicar bit a bit un disco en 2 servidoresTransmite solo lo que escribesEstá pensado para soluciones activo/pasivoPuede utilizar hardware totalmente estandar (se aconseja por lo menos una Ethernet Gbit dedicada)


DRBD y MySQL

MySQL en activo-pasivo con shared diskNO es un master-master. No hay replicación de Mysql, sólo la de DRBD a nivel de bloque2 servidores identicos a nivel de HWMismo my.cnf en ambos


DRBD y MySQL

Ok, ya tengo los datos en dos servidores, así que si uno muere tengo otro.

Pero... ¿como me entero?


DRBD y MySQL

Ok, ya tengo los datos en dos servidores, así que si uno muere tengo otro.

Pero... ¿como me entero?

Usando Heartbeat

Replicación DRBD

MySQL

sda1

IP real IP virtual

Heart Beat MySQL

sda1

IP real

Red internaCliente

Cliente

Cliente

Cliente

Activo Pasivo

Servidor A Servidor B


MySQL, Heartbeat y DRBD

Y que pasa si Servidor A cae?

MySQL

sda1

IP real

MySQL

sda1

IP real

Red internaCliente

Cliente

Cliente

Cliente

Muerto Activo

Servidor A Servidor B

IP virtual

DOWN



Cuando Servidor B se levanta, la base de datos en disco está en estado crashed



Cuando Servidor B se levanta, la base de datos en disco está en estado crashedPara InnoDB (u otros engines ACID) no es un problema crítico




Aunque si la BD es grande, puede tardar tiempo en recuperarse




Aunque si la BD es grande, puede tardar tiempo en recuperarse

Las tablas MyISAM pueden corromperse de forma más o menos grave (sí, podrías perderlo todo)


Recordad!

¡¡ BACKUP !!(sólo hay dos clases de administradores de sistemas: los que han perdido

datos y los que aún no lo han hecho)


Backup – Algunas opciones

mysqldump (el de toda la vida)mk-parallel-dumpInnoDB Hot Backup

De pago, de innodb.com (autores de InnoDB)Hace backup de InnoDB sin necesidad de parar MySQL

xtrabackupOpensource, de PerconaTambién hace hot backup, pero está más verde

LVM + cold backup (mylvmbackup)Cold backup de un slave + copia de binlog


innodb_file_per_table

innodb_file_per_table Sirve para separar cada tabla InnoDB en un archivo dedicado

Permite recuperar espacio a nivel de SO si se borran tuplasUn simple ls y ya sabes cual es la tabla gordaOcupa más file descriptorsHay que controlar el espacio que queda libre en disco


innodb_flush_method

innodb_flush_method Determina como MySQL abre en lectura y escritura los archivos de datos (tablespace) y los transaction logs a nivel de SO. En *nix puede tener como valor:

fdatasync (default) utiliza fsync() para los logs y los datafilesO_DSYNC utiliza O_SYNC para abrir y hacer flush de los logs y fsync() para hacer flush de los datafilesO_DIRECT utiliza O_DIRECT para abrir los datafiles y fsync() para el flush de logs y datos


fdatasync O_DIRECT O_DSYNC

Tablespacefsync()

open() con O_DIRECT,

fsync()fsync()

Transaction log

fsync() fsync()open() con O_SYNC

innodb_flush_method


innodb_flush_method

WTF?O_SYNC y O_DIRECT son parametros opcionales de open()

O_SYNC obliga a sincronizar los datos a disco cada vez que se quiere escribir algo en un archivo así abierto, bloqueando el retorno de la syscall. Las escrituras pero sì pasan por la cache del SO.O_DIRECT el kernel pone directamente en DMA la aplicación en userspace, deshabilitando al 100% el I/O buffer del SO. Se evita así el doble buffer (de SO e InnoDB en lectura, de SO y hardware en escritura)

fsync(): si no hay O_SYNC se utiliza siempre, aunque exista fdatasync()


Buffer OS Buffer HW Media (disco)

fsync()Si Si Si

fsync() con O_DIRECT

No Si Si

O_SYNCSi, locking

Puede ser que no

Si

innodb_flush_method


Transaction log

innodb_flush_log_at_trx_commit0 write y flush cada segundo, no cada transacción1 (default) write y flush del log de la memoria al disco cada transacción. Más seguro.2 write cada trx, flush cada segundo


maatkit

Es un set de herramientas que facilitan la vida del administrador de MySQLmk-table-checksum permite comprobar que dos tablas replicadas sean realmente identicasmk-slave-delay permite mantener artificialmente un slave atrás en el tiempo. Util para recuperar datos despues de errores humanosmk-parallel-(dump|restore)mk-visual-explainetc


innotop

Innotop es una herramienta parecida a top de Unix que permite visualizar datos de InnoDB en tiempo real

Queries que se están ejecutando y sus detallesExplain directo de una kk-query que está tardandoEstadisticas de buffer, flushs, writes, prune, consumo de memoria en tiempo real

Fundamentalmente es un front-end a mysqladmin proc, mysqladmin ext, y suerte que existe


Links

<--- Google's that wayHigh Performance MySQL 2nd Edition (la biblia de MySQL)http://planet.mysql.com


Contactos

Davide FerrariMail: [email protected]: http://www.linkedin.com/in/davideferrariBlog: http://anothersysadmin.wordpress.com

¿Preguntas?

mailto:[email protected]

http://www.linkedin.com/in/davideferrari

http://anothersysadmin.wordpress.com/

mysql en la vida real

Technology