Post on 28-Jun-2015
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
30 Octubre 2009 PHP Conference Barcelona 3
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í
30 Octubre 2009 PHP Conference Barcelona 4
Resumen de lo que vamos a ver hoy
ReplicaciónMaster-slave(s)Master-masterOtras formas
DRBD (Distributed Replicated Block Device) y HeartbeatConsejos e ideas
30 Octubre 2009 PHP Conference Barcelona 5
La replicación en MySQL
Qué és la replicación?
30 Octubre 2009 PHP Conference Barcelona 6
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.
30 Octubre 2009 PHP Conference Barcelona 7
La replicación en MySQL: más en detalle
Ayuda a escalar en lectura
30 Octubre 2009 PHP Conference Barcelona 8
La replicación en MySQL: más en detalle
Ayuda a escalar en lecturaNo ayuda a escalar en escritura. Olvidadlo.
30 Octubre 2009 PHP Conference Barcelona 9
La replicación en MySQL: más en detalle
Ayuda a escalar en lecturaNo ayuda a escalar en escritura. Olvidadlo.Puede ayudar a mejorar la disponibilidad
30 Octubre 2009 PHP Conference Barcelona 10
La replicación en MySQL: más en detalle
Ayuda a escalar en lecturaNo ayuda a escalar en escritura. Olvidadlo.Puede ayudar a mejorar la disponibilidadPuede dar dolores de cabeza
30 Octubre 2009 PHP Conference Barcelona 11
La replicación en MySQL: más en detalle
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)
30 Octubre 2009 PHP Conference Barcelona 12
La replicación en MySQL: más en detalle
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 corrompe (y no lo notas)
30 Octubre 2009 PHP Conference Barcelona 13
La replicación en MySQL: más en detalle
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
30 Octubre 2009 PHP Conference Barcelona 14
La replicación en MySQL: más en detalle
La replicación es un proceso asincrono
30 Octubre 2009 PHP Conference Barcelona 15
La replicación en MySQL: más en detalle
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
30 Octubre 2009 PHP Conference Barcelona 16
La replicación en MySQL: más en detalle
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
30 Octubre 2009 PHP Conference Barcelona 17
Master - Slave(s)
Sin dudas és la topología de replicación más común
30 Octubre 2009 PHP Conference Barcelona 18
Master - Slave(s)
Sin dudas és la topología de replicación más comúnHay un master donde van las escrituras...
30 Octubre 2009 PHP Conference Barcelona 19
Master - Slave(s)
Sin dudas és la topología de replicación más comúnHay un master donde van las escrituras...
… y algunas lecturas
30 Octubre 2009 PHP Conference Barcelona 20
Master - Slave(s)
Sin dudas és la topología de replicación más comúnHay un master donde van las escrituras...
… y algunas lecturasCuidado con las SELECTs en transacciones (es ACID)
30 Octubre 2009 PHP Conference Barcelona 21
Master - Slave(s)
Sin dudas és la topología de replicación más comúnHay un master donde van las escrituras...
… y algunas lecturasCuidado con las SELECTs en transacciones (es ACID)
Hay N slaves donde van las lecturas...
30 Octubre 2009 PHP Conference Barcelona 22
Master - Slave(s)
Sin dudas és la topología de replicación más comúnHay un master donde van las escrituras...
… y algunas lecturasCuidado con las SELECTs en transacciones (es ACID)
Hay N slaves donde van las lecturas...… y escrituras. Si no, como se replican los datos? :)
30 Octubre 2009 PHP Conference Barcelona 23
Master - Slave(s)
Como distribuir el hardware entre master/slaves?
30 Octubre 2009 PHP Conference Barcelona 24
Master - slave(s)
Aparantemente, el master debe tener más hardware
30 Octubre 2009 PHP Conference Barcelona 25
Master - slave(s)
Aparantemente, el master debe tener más hardwareRecibe todas las escrituras...
30 Octubre 2009 PHP Conference Barcelona 26
Master - slave(s)
pero...
30 Octubre 2009 PHP Conference Barcelona 27
Master - slave(s)
En un contexto web, es mucho más probable que haya muchísimas más lecturas
30 Octubre 2009 PHP Conference Barcelona 28
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
30 Octubre 2009 PHP Conference Barcelona 29
Master - slave(s)
Más recursos a los esclavos!
30 Octubre 2009 PHP Conference Barcelona 30
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!!!
30 Octubre 2009 PHP Conference Barcelona 31
Master-Master
Qué es el master-master?
30 Octubre 2009 PHP Conference Barcelona 32
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.
30 Octubre 2009 PHP Conference Barcelona 33
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
30 Octubre 2009 PHP Conference Barcelona 34
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.
30 Octubre 2009 PHP Conference Barcelona 35
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.
30 Octubre 2009 PHP Conference Barcelona 36
DRBD
DRBD® es una solución 100% opensource de LinBit para la alta disponibilidad de un block device
30 Octubre 2009 PHP Conference Barcelona 37
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)
30 Octubre 2009 PHP Conference Barcelona 38
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
30 Octubre 2009 PHP Conference Barcelona 39
DRBD y MySQL
Ok, ya tengo los datos en dos servidores, así que si uno muere tengo otro.
Pero... ¿como me entero?
30 Octubre 2009 PHP Conference Barcelona 40
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
30 Octubre 2009 PHP Conference Barcelona 42
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
30 Octubre 2009 PHP Conference Barcelona 44
MySQL, Heartbeat y DRBD
Cuando Servidor B se levanta, la base de datos en disco está en estado crashed
30 Octubre 2009 PHP Conference Barcelona 45
MySQL, Heartbeat y DRBD
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
30 Octubre 2009 PHP Conference Barcelona 46
MySQL, Heartbeat y DRBD
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
30 Octubre 2009 PHP Conference Barcelona 47
MySQL, Heartbeat y DRBD
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
Las tablas MyISAM pueden corromperse de forma más o menos grave (sí, podrías perderlo todo)
30 Octubre 2009 PHP Conference Barcelona 48
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)
30 Octubre 2009 PHP Conference Barcelona 49
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
30 Octubre 2009 PHP Conference Barcelona 50
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
30 Octubre 2009 PHP Conference Barcelona 51
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
30 Octubre 2009 PHP Conference Barcelona 52
fdatasync O_DIRECT O_DSYNC
Tablespacefsync()
open() con O_DIRECT,
fsync()fsync()
Transaction log
fsync() fsync()open() con O_SYNC
innodb_flush_method
30 Octubre 2009 PHP Conference Barcelona 53
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()
30 Octubre 2009 PHP Conference Barcelona 54
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
30 Octubre 2009 PHP Conference Barcelona 55
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
30 Octubre 2009 PHP Conference Barcelona 56
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
30 Octubre 2009 PHP Conference Barcelona 57
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
30 Octubre 2009 PHP Conference Barcelona 58
Links
<--- Google's that wayHigh Performance MySQL 2nd Edition (la biblia de MySQL)http://planet.mysql.com
30 Octubre 2009 PHP Conference Barcelona 59
Contactos
Davide FerrariMail: vide80@gmail.comLinkedIn: http://www.linkedin.com/in/davideferrariBlog: http://anothersysadmin.wordpress.com
¿Preguntas?