unico tipos reconstruir mostrar indice enlaces eliminar crear consultas con como archivo mysql indexing production alter-table table-locking

tipos - Crear un índice en una enorme tabla de producción de MySQL sin bloqueo de tabla



reconstruir indices mysql (4)

Necesito crear un índice en una tabla ~ ~ 5M filas MySQL. Es una tabla de producción, y me temo que un bloque completo de todo si ejecuto una declaración CREATE INDEX ...

¿Hay alguna forma de crear ese índice sin bloquear inserciones y selecciones?

¡Solo me estoy preguntando si no debo parar, crear un índice y reiniciar mi sistema!

[2017] Actualización: MySQL 5.6 tiene soporte para actualizaciones de índices en línea

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

En MySQL 5.6 y posteriores, la tabla permanece disponible para operaciones de lectura y escritura mientras se crea o elimina el índice. La instrucción CREATE INDEX o DROP INDEX solo finaliza después de que se completen todas las transacciones que acceden a la tabla, de modo que el estado inicial del índice refleje los contenidos más recientes de la tabla. Anteriormente, la modificación de la tabla mientras se creaba o descartaba un índice generalmente producía un interbloqueo que cancelaba la instrucción INSERT, UPDATE o DELETE en la tabla.

[2015] Actualización de bloques de indices de tabla escribe en MySQL 5.5

De la respuesta anterior:

"Si usa una versión superior a 5.1, los índices se crean mientras la base de datos está en línea. Así que no se preocupe, no interrumpirá el uso del sistema de producción".

Esto es **** FALSO **** (al menos para las tablas MyISAM / InnoDB, que es lo que usa el 99.999% de las personas que hay. Clustered Edition es diferente).

Al hacer operaciones de ACTUALIZACIÓN en una tabla se BLOQUEARÁ mientras se está creando el índice. MySQL es realmente estúpido con esto (y algunas otras cosas).

Script de prueba:

( for n in {1..50}; do #(time mysql -uroot -e ''select * from website_development.users where id = 41225/G''>/dev/null) 2>&1 | grep real; (time mysql -uroot -e ''update website_development.users set bio="" where id = 41225/G''>/dev/null) 2>&1 | grep real; done ) | cat -n & PID=$! sleep 0.05 echo "Index Update - START" mysql -uroot website_development -e ''alter table users add index ddopsonfu (last_name, email, first_name, confirmation_token, current_sign_in_ip);'' echo "Index Update - FINISH" sleep 0.05 kill $PID time mysql -uroot website_development -e ''drop index ddopsonfu on users;''

Mi servidor (InnoDB):

Server version: 5.5.25a Source distribution

Salida (observe cómo la 6ª operación bloquea los ~ 400 ms que tarda en finalizar la actualización del índice):

1 real 0m0.009s 2 real 0m0.009s 3 real 0m0.009s 4 real 0m0.012s 5 real 0m0.009s Index Update - START Index Update - FINISH 6 real 0m0.388s 7 real 0m0.009s 8 real 0m0.009s 9 real 0m0.009s 10 real 0m0.009s 11 real 0m0.009s

Vs operaciones de lectura que no bloquean (intercambia el comentario de línea en el script):

1 real 0m0.010s 2 real 0m0.009s 3 real 0m0.009s 4 real 0m0.010s 5 real 0m0.009s Index Update - START 6 real 0m0.010s 7 real 0m0.010s 8 real 0m0.011s 9 real 0m0.010s ... 41 real 0m0.009s 42 real 0m0.010s 43 real 0m0.009s Index Update - FINISH 44 real 0m0.012s 45 real 0m0.009s 46 real 0m0.009s 47 real 0m0.010s 48 real 0m0.009s

Actualizando el esquema de MySQL sin tiempo de inactividad

Hasta ahora, solo hay un método que conozco para actualizar un esquema MySql y no sufrir un corte de disponibilidad. Maestros circulares:

  • El maestro A tiene su base de datos MySQL ejecutándose en él
  • Ponga en funcionamiento el Master B y haga que replique las escrituras del Maestro A (B es un esclavo de A)
  • Realice la actualización del esquema en Master B. Se retrasará durante la actualización
  • Deja que Master B se ponga al día. Invariante: su cambio de esquema DEBE ser capaz de procesar comandos replicados desde un esquema de bajada. Los cambios de indexación califican. Las adiciones simples a las columnas generalmente califican. ¿Quitando una columna? Probablemente no.
  • ATOMICAMENTE intercambie todos los clientes del Máster A al Máster B. Si quiere estar a salvo (confíe en mí, si lo hace), debe asegurarse de que la última escritura en A se repita en B ANTES de que B tome su primera escritura. Si permite escrituras concurrentes en más de 2 maestros, ... será mejor que comprenda la replicación de MySQL a un nivel PROFUNDO o se dirigirá a un mundo de dolor. Dolor extremo. ¿Tienes una columna que es AUTOINCREMENTO? estás jodido (a menos que uses números pares en un maestro y probabilidades en el otro). NO confíe en la replicación de MySQL para "hacer lo correcto". NO es inteligente y no lo salvará. Es ligeramente menos seguro que copiar registros de transacciones binarias desde la línea de comandos y reproducirlos a mano. Aún así, desconectar todos los clientes del antiguo maestro y pasarlos al nuevo maestro se puede hacer en cuestión de segundos, mucho más rápido que esperar una actualización de esquema de varias horas.
  • Ahora el Maestro B es tu nuevo maestro. Tienes el nuevo esquema. La vida es buena. Toma una cerveza; lo peor ha terminado.
  • Repita el proceso con el Maestro A, actualice su esquema para que se convierta en su nuevo maestro secundario, listo para asumir el control en caso de que su maestro principal (el maestro B ahora) pierda potencia o simplemente se levante y muera.

Una manera fácil de actualizar el esquema esto no es. Funcionable en un entorno de producción serio; sí lo es. Por favor, por favor, si hay una forma más fácil de agregar un índice a una tabla MySQL sin bloquear las escrituras, háganmelo saber.

Google me lleva a este artículo que describe una técnica similar. Aún mejor, aconsejan beber en el mismo punto del procedimiento (¡tenga en cuenta que escribí mi respuesta antes de leer el artículo)!

Percona''s pt-online-schema-change

El artículo que he vinculado anteriormente habla de una herramienta, pt-online-schema-change , que funciona de la siguiente manera:

  • Crea una nueva tabla con la misma estructura que el original.
  • Actualizar el esquema en la nueva tabla.
  • Agregue un desencadenador en la tabla original para que los cambios estén sincronizados con la copia
  • Copie las filas en lotes desde la tabla original.
  • Mueva la tabla original fuera del camino y reemplácela por una nueva.
  • Suelta la mesa vieja.

Nunca he probado la herramienta yo mismo. YMMV

RDS

Actualmente estoy usando MySQL a través del RDS de Amazon . Es un servicio realmente ingenioso que envuelve y administra MySQL, permitiéndole agregar nuevas réplicas de lectura con un solo botón y actualizar de manera transparente la base de datos a través de los SKU de hardware. Es realmente conveniente. No obtienes acceso SUPER a la base de datos, por lo que no puedes atornillar con la replicación directamente (¿es esto una bendición o una maldición?). Sin embargo, puede usar la promoción de lectura de réplica para hacer que sus cambios de esquema en un esclavo de solo lectura, luego promover ese esclavo para convertirse en su nuevo maestro. Exactamente el mismo truco que describí anteriormente, simplemente mucho más fácil de ejecutar. Todavía no hacen mucho para ayudarte con el corte. Debes reconfigurar y reiniciar tu aplicación.

Actualización de MySQL 5.6 (feb 2013): ahora puede realizar operaciones de lectura y escritura mientras se crea un índice incluso con tablas InnoDB - http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

En MySQL 5.6 y posteriores, la tabla permanece disponible para operaciones de lectura y escritura mientras se crea o elimina el índice. La instrucción CREATE INDEX o DROP INDEX solo finaliza después de que se completen todas las transacciones que acceden a la tabla, de modo que el estado inicial del índice refleje los contenidos más recientes de la tabla. Anteriormente, la modificación de la tabla mientras se creaba o descartaba un índice generalmente producía un interbloqueo que cancelaba la instrucción INSERT, UPDATE o DELETE en la tabla.

y:

En MySQL 5.6, esta característica se vuelve más general: puede leer y escribir en tablas mientras se crea un índice, y se pueden realizar muchos más tipos de operaciones ALTER TABLE sin copiar la tabla, sin bloquear operaciones DML, o ambas. Por lo tanto, en MySQL 5.6 y versiones posteriores, generalmente nos referimos a este conjunto de funciones como DDL en línea en lugar de Fast Index Creation.

de http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_fast_index_creation

Como se describe en este blog , el mecanismo InnoDB ALTER TABLE ha sido completamente rediseñado para MySQL 5.6.

(Para una visión general exclusiva de este tema, la documentación de MySQL puede proporcionar una lectura de la tarde).

Para agregar un índice a una tabla sin un bloqueo resultante de UPDATE / INSERT , se puede usar el siguiente formato de declaración:

ALTER TABLE my_table ADD INDEX my_table__idx (my_column), ALGORITHM=INPLACE, LOCK=NONE;

pt-online-schema-change es el camino a seguir si realmente quiere asegurarse de que la migración no derribará el sitio.

Como escribí en el comentario anterior, tengo varias experiencias con pt-online-schema-change en la producción. Tenemos nuestra tabla principal de más de 20 millones de registros y un maestro -> 2 esclavos de replicación de solo lectura. He hecho al menos una docena de migraciones con pt-online-schema-change desde agregar una nueva columna, cambiar el juego de caracteres, hasta agregar varios índices. También atendemos toneladas de tráfico durante el tiempo de migración y no hemos tenido ningún inconveniente. Por supuesto, tendría que probar todos los scripts muy a fondo antes de ejecutar en producción.

Traté de combinar los cambios en 1 script para que pt-online-schema-change solo tenga que copiar los datos una vez. Y tenga mucho cuidado al cambiar el nombre de la columna, ya que perderá sus datos. Sin embargo, agregar un índice debería estar bien.