Puede replicación MySQL ponerse al día

Original post: http://anothermysqldba.blogspot.com/2014/01/can-mysql-replication-catch-up.html

Así que la replicación se ha mejorado recientemente en MySQL 5.6. Sin embargo, la gente sigue utilizando 5.1 y 5.5 por lo que algunas de estas mejoras tendrán que esperar para golpear el mundo real.

Recientemente ayudé a paso en esta dirección con una solución de replicación de geo-localizada. Una parte del país tenía un servidor MySQL 5.1 y la otra parte del país tuvo un nuevo servidor MySQL 5.6 instalado.

Después de lidiar con los problemas de obtener la copia de seguridad inicial de los datos desde el primario al servidor secundario (tardó varias horas para decir lo menos), tuve que decidir podría replicación ponerse al día y mantener el ritmo. El servidor principal tenía algunos grandes consultas y optimización es siempre un buen lugar para empezar. Tuve que hacer el servidor secundario tirando y aplicar lo más rápido que pude primero sin embargo.

Así que aquí hay algunas cosas que debe revisar y tener en cuenta cuando se trata de la réplica. He añadido algunos enlaces de abajo que ayudan a apoyar mis pensamientos mientras trabajaba en esto.

La replicación puede ser muy de E / S pesada. Dependiendo de su aplicación. Un sitio blog no tiene que muchas escrituras por lo que la replicación de E / S es la luz, pero un servidor primario en gran medida por escrito y actualizado va a conducir a un servidor de replicación escribir mucho relay_logs y binary_logs si están habilitadas. Los logs binarios pueden ser activados en la secundaria que le permite ejecutar copias de seguridad o usted puede ser que este servidor sea un primario a otros.

Dividí los registros en una partición de datos diferente del directorio de datos.
Esto se establece en el archivo my.cnf - relay-log

El buffer de InnoDB ya se estableció en un valor de más de 10 GB. Esto fue suficiente para este servidor.
El camarero fue más de 90.000 segundos por detrás todavía.

Así que empecé a hacer algunos ajustes en el servidor y terminamos con estos ajustes en el final. Concedido cada servidor es diferente.

mysql> select @ @ sync_relay_log_info \ G
*************************** 1. fila ***************************
@ @ Sync_relay_log_info: 0
1 row in set (0.08 sec)

mysql> select @ @ innodb_flush_log_at_trx_commit \ G
*************************** 1. fila ***************************
@ @ Innodb_flush_log_at_trx_commit: 2
1 row in set (0.00 sec)

mysql> select @ @ log_slave_updates \ G
*************************** 1. fila ***************************
@ @ Log_slave_updates: 0

mysql> select @ @ sync_binlog \ G
*************************** 1. fila ***************************
@ @ Sync_binlog: 0
1 row in set (0.00 sec)

mysql> select @ @ max_relay_log_size \ G
*************************** 1. fila ***************************
@ @ Max_relay_log_size: 268435456

Me volví el registro binario fuera como yo supervisé diferentes configuraciones y opciones para ayudar a la replicación de ponerse al día. Me tomó un tiempo. Algunos de los ajustes que ves arriba puede o no se han aplicado a medida que trabajaba dentro de este marco de tiempo. Sin embargo, no ponerse al día en 0 segundos atrás. Ahora usted puede notar que muchos de estos ajustes anteriores se relacionan en y alrededor del registro binario. Así que me encontré una pequeña prueba. Por lo tanto, he reiniciado y permitió a los registros de basura. Me registré en el servidor más tarde y encontró que 10,000 + segundos atrás. Así que una vez más, reinicié y desactivado los registros de basura. Se alcanzó (0 segundos detrás) con el servidor principal en menos de 15 minutos. Solía ​​Aurimas ' herramienta mientras veía a ponerse al día también. Si aún no lo use antes, es una herramienta muy agradable y práctico.

Lo que todo esto significa es que el servidor principal debe ser conforme a ACID. Con esta configuración también está en función del sistema operativo para el caché y limpiar. Este es el servidor se puede utilizar como un servidor de lectura principalmente para alimentar a la información a los demás. También quiere decir que sí, la replicación geo-localizada puede mantenerse al día con un servidor primario.

¿Qué pasa si usted necesita parar el esclavo, se sigue ponerse al día rápidamente?

¿Cómo y por qué te detienes el esclavo es mi primera respuesta. Usted debe entrar en el hábito de usar SLAVE STOP SQL_THREAD , en lugar de SLAVE STOP ; Esto permite que los logs retardados continúen para recopilar datos y no aplicarlo a su servidor principal. Así que si usted puede tomar ventaja de que va a ayudar a reducir el tiempo que toma para que usted pueda rellenar los logs retardados después.

Algunos de lectura adicional para usted:

• http://www.mysqlperformanceblog.com/2011/07/29/reasons-for-mysql-replication-lag/
• http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/
• http://www.mysqlperformanceblog.com/2012/08/29/heres-a-quick-way-to-foresee-if-replication-slave-is-ever-going-to-catch-up-and-when/

Cómo ajustar el servidor MySQL

Original post: http://anothermysqldba.blogspot.com/2013/05/how-to-tune-mysql-server.html

Esta entrada de blog es parte de la serie de blog

• MySQL Server Sintonía pregunta
• Cómo identificar MySQL consultas lentas y preocupaciones relacionados
• Cómo ajustar el servidor MySQL

Para continuar ahora con el propio servidor:

Bueno, además de simplemente decir hacer lo que dice Pedro , le revise ejemplos de cómo se pueden resolver por sí mismos.

Para empezar, se puede comparar el archivo my.cnf contra el Q & A versión que está disponible de forma gratuita a través de Percona. Es ésta una solución ideal? No, pero se le permitirá tomar una nueva mirada a su archivo de configuración después de que responde a todas sus preguntas, a través de su asistente de configuración.

• https://tools.percona.com/wizard

innodb_buffer_pool_size -

select @@innodb_buffer_pool_size;

Ajuste de la innodb_buffer_pool_size es, con mucho, uno de los lugares más importantes para un MySQL InnoDB database.Some buena lectura sobre este tema es el siguiente:

• http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
• http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

Es necesario tener en cuenta que no existe una "talla única consulta" para el ajuste del innodb_buffer_pool_size. Tenemos algunas pautas y sugerencias que han surgido en los últimos años (ver a continuación) comunes y también tenemos varias teorías diferentes acerca de las mejores opciones.

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS
FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;


SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B; 

Es seguro decir que si usted tiene la serie 8M defecto como la variable innodb_buffer_pool_size entonces este debe ser uno de los primeros parámetros que ajustar.

Básicamente, permitir que esto tiene toda la memoria disponible, puede darse el lujo de dar. Esto por supuesto tiene en cuenta varios factores, como lo que más necesita asignar memoria en el servidor? Ten en cuenta que el disco I / O es importante para el rendimiento y la más memoria permite que esta variable tiene menos disco I / O se debería exigir para el acceso a la tabla.

Consideremos también esto ....
Como una recomendación general es establecer the innodb_log_file_size a 25% del tamaño del buffer que debemos evaluar lo que tenemos para el innodb_log_file_size per conceptos del barón y luego mirar para ver cómo se relaciona con el innodb_buffer_pool_size.

Por ejemplo, siguiendo la lógica de Del Barón mensaje:

> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 3982683217
1 row in set (0.01 sec)

1 row in set (59.99 sec)

Log sequence number 3991367755
1 row in set (0.01 sec)

> SET @sequence1=3982683217;
Query OK, 0 rows affected (0.00 sec)
> SET @sequence2=3991367755;
Query OK, 0 rows affected (0.00 sec)
> select (@sequence2 - @sequence1) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 8.28222084 |
+------------+
1 row in set (0.00 sec)
> select ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 as MB_per_hour ;
+--------------+
| MB_per_hour |
+--------------+
| 496.93325043 |
+--------------+
1 row in set (0.00 sec)
> select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 as estimated_buffer_pool ;
+-----------------------+
| estimated_buffer_pool |
+-----------------------+
| 1987.73300171 |
+-----------------------+
1 row in set (0.00 sec)
> select ( ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 ) / 1024 as estimated_buffer_pool_GB ;
+--------------------------+
| estimated_buffer_pool_GB |
+--------------------------+
| 1.941145509481 |
+--------------------------+
1 row in set (0.00 sec)


Ahora, cuando se comparan estos resultados con las "directrices"

SELECT CONCAT(ROUND(KBS/POWER(1024,
-> IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
-> SUBSTR(' KMG',IF(PowerOf1024<0,0,
-> IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
-> FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
-> WHERE engine='InnoDB') A,
-> (SELECT 3 PowerOf1024) B;
+-------------------------------------+
| recommended_innodb_buffer_pool_size |
+-------------------------------------+
| 1G |
+-------------------------------------+ 

Usted obtiene un resultado diferente. Mientras que el cheque de Baron depende del período de tiempo que se ejecuta dentro (que es por eso que usted debe comprobar esto durante picos de tráfico) se le puede dar una visión más realista de su tráfico y uso. Me gustaría ver en la creación del innodb_buffer_pool_size a 2G no 1G en este sencillo ejemplo.

SET @ Secuencia1 = 3982683217;
SET @ secuencia2 = 3991367755;
seleccionar (@ secuencia2 - @ Secuencia1) / 1024/1024 como MB_per_min;
seleccionar ((@ secuencia2 - @ Secuencia1) / 1024/1024) * 60 como MB_per_hour;
seleccionar (((@ secuencia2 - @ Secuencia1) / 1024/1024) * 60) * 4 como estimated_buffer_pool;
seleccionar ((((@ secuencia2 - @ Secuencia1) / 1024/1024) * 60) * 4) / 1024 como estimated_buffer_pool_GB;



innodb_log_file_size -

select @@innodb_log_file_size;

He descubierto que me gusta conceptos del barón sobre cómo configurar el innodb_log_file_size. Recuerde que debe ejecutar sus sugerencias durante las horas punta para obtener lecturas reales. Cuanto mayor sea el tamaño de estos archivos, el mejor rendimiento con grandes conjuntos de datos, pero nada es gratis, aumentará los tiempos de recuperación. El aumento de los tiempos de recuperación no puede sonar como una gran preocupación para algunos, hasta que sea una base de datos depende de los ingresos que usted está esperando y el proceso de observación de siempre. Una recomendación general es situarlo en el 25% del tamaño del buffer.

select ( @@innodb_buffer_pool_size * .25 )

• http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
• http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely /

innodb_log_buffer_size -

select @@innodb_log_buffer_size;

Recuerdo que una vez tuve este conjunto en un servidor para innodb_log_buffer_size = 128M.¿Fue una buena elección? No significa que quería memoria cuando probablemente no tenía que hacerlo. Era una base de datos muy pesado escribir, pero esta opción es muy probable que alto. Centrarse en los valores predeterminados primero y luego duplicarlo (8 MB - 16 MB) max.


innodb_additional_mem_pool_size -

select @@innodb_additional_mem_pool_size;

Pedro se dirige a esta configuración en su blog . Usted puede tratar y evaluar las opciones de configuración después de que otros se han tratado en mi opinión.


innodb_flush_log_at_trx_commit -

select @@innodb_flush_log_at_trx_commit;

> Set GLOBAL innodb_flush_log_at_trx_commit = 2;
Pedro se dirige a esta configuración en su blog también. Se trata de una mejora en el rendimiento de pruebas variables y el valor comúnmente pasado por alto por el ajuste 0-2.Tenga en cuenta los riesgos.


thread_cache -

select @@thread_cache_size;
+---------------------+
| @@thread_cache_size |
+---------------------+
| 50 |
+---------------------+

• http://dev.mysql.com/doc/refman/5.5/en/connection-threads.html

>show status like 'threads_created';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 4 |
+-----------------+-------+
1 row in set (0.00 sec)

> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 962 |
+---------------+-------+
1 row in set (0.00 sec)

> SELECT 100 - ((4 / 962) * 100);
+-------------------------+
| 100 - ((4 / 962) * 100) |
+-------------------------+
| 99.5842 |
+-------------------------+


Mantenga un ojo en la misma situación: mostrar el estado como "Threads_created ';
Si sube, entonces tengo configurado demasiado bajo y tiene que ser levantado.


query_cache_size -

select ((@@query_cache_size / 1024) / 1024);

Los valores permitidos son múltiplos de 1.024.

• http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html # sysvar_query_cache_size

Preste atención a este ajuste, el más leído depende que la aplicación se vuelve. Yo solía correr con los ajustes key_buffer = 16M y yo podría y probablemente debería haber doblado eso.


key_buffer_size -

select @@key_buffer_size;

Con el cambio a MySQL InnoDB como el motor de almacenamiento por defecto que es probable un cierto pudimos ver cada vez menos las tablas creadas como MyISAM. Eso no es un hecho sólo una opinión. El key_buffer_size será muy importante para usted si utilizas tablas MyISAM.

Revisión de Shlomi Noaj mensaje para ayudarle a encontrar lo que fácilmente las tablas que tipo de motor.
"Ver al tamaño de la tabla (casi exactamente como se presenta en INFORMATION_SCHEMA)"- Shlomi Noaj.

> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 15728640 |
+-----------------+----------+ 

table_cache -

show variables like '%table%cache%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_definition_cache | 4096 |
| table_open_cache | 4096 |
| table_open_cache_instances | 1 |
+----------------------------+-------+ 

Según el manual es una buena fórmula para ayudar a determinar latable_definition_cache tamaño.

SELECT 400 + (@@table_open_cache / 2);

> SHOW status like '%Opened_tables%'; 

table_open_cache_instances
Un valor de 8 o 16 se recomienda en sistemas que utilizan habitualmente 16 o más núcleos, el valor predeterminado es 1.


Esperemos que esto expone las variables y los ajustes que se deben revisar para ayudarle a obtener lo mejor de su base de datos MySQL.