¿ALTERAR la MESA sin bloquear la mesa?

Percona crea una herramienta llamada pt-online-schema-change que permite hacer esto.

Esencialmente hace una copia de la tabla y modifica la nueva tabla. Para mantener la nueva tabla sincronizada con la original, utiliza disparadores para actualizar. Esto permite acceder a la tabla original mientras se prepara la nueva tabla en segundo plano.

Esto es similar al método Dems sugerido anteriormente, pero esto lo hace de manera automatizada.

Algunas de sus herramientas tienen un aprendizaje curva, es decir, la conexión a la base de datos, pero una vez que tiene que hacia abajo, son grandes herramientas para tener.

Ex:

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=db,t=numbers_are_friends

Consulte la herramienta de cambio de esquema en línea de Facebook.

Http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

No para los débiles de corazón, sino que hará el trabajo.

Recomiendo Postgres si esa es una opción. Con postgres esencialmente no hay tiempo de inactividad con los siguientes procedimientos:

• ALTER TABLE ADD COLUMN (si la columna puede ser NULA)
• ALTER TABLE DROP COLUMN
• CREAR ÍNDICE (debe usar CREAR ÍNDICE SIMULTÁNEAMENTE)
• ÍNDICE DE CAÍDA

Otra gran característica es que la mayoría de las sentencias DDL son transaccionales, por lo que podría hacer una migración completa dentro de una transacción SQL, y si algo si sale mal, todo retrocede.

Escribí esto hace un poco, tal vez pueda arrojar algo más de luz sobre los otros méritos.

Esta pregunta de 2009. Ahora MySQL ofrece una solución:

En línea DDL

Una característica que mejora el rendimiento, la concurrencia y la disponibilidad de tablas InnoDB durante las operaciones DDL (principalmente ALTER TABLE). Ver Sección 14.11, "InnoDB y DDL en línea" para más detalles.

Los detalles varían según el tipo de operación. En algunos casos, la tabla se puede modificar simultáneamente mientras la TABLA ALTER está en progreso. La operación podría ser capaz para ser realizado sin hacer un copia de tabla, o usando un tipo especialmente optimizado de copia de tabla. Espacio el uso es controlado por el innodb_online_alter_log_max_size opción de configuración.

Le permite ajustar el equilibrio entre rendimiento y concurrencia durante la operación DDL, eligiendo si desea bloquear el acceso a la tabla por completo (cláusula LOCK=EXCLUSIVE), permitir consultas pero no DML (cláusula LOCK=SHARED), o permitir consultas completas y acceso DML a la tabla (cláusula LOCK=NONE). Cuando omite la cláusula LOCK o especifica LOCK=DEFAULT, MySQL permite tanta concurrencia como sea posible dependiendo del tipo de operación.

Realizar cambios en el lugar donde sea posible, en lugar de crear una nueva copia de la tabla, evita aumentos temporales en el uso de espacio en disco y la sobrecarga de E/S asociada con la copia de la tabla y la reconstrucción de índices secundarios.

Ver MySQL 5.6 Manual de referencia -> InnoDB y DDL en línea para más información.

Parece que en línea DDL también disponible en MariaDB

Alternativamente, puede usar ALTER ONLINE TABLE para asegurarse de que su ALTER La TABLA no bloquea las operaciones simultáneas (no acepta bloqueos). Es equivalente a BLOQUEO = NINGUNO.

MariaDB KB acerca de ALTER TABLE

Ya que preguntó sobre otras bases de datos, aquí hay información sobre Oracle.

Agregar una columna NULA a una tabla Oracle es una operación muy rápida, ya que solo actualiza el diccionario de datos. Esto mantiene un bloqueo exclusivo en la mesa durante un período de tiempo muy corto. Sin embargo, invalidará cualquier procedimiento almacenado de depedant, vistas, disparadores, etc. Estos se recompilarán automáticamente.

Desde allí, si es necesario, puede crear un índice utilizando la cláusula ONLINE. Una vez más, sólo bloqueos de diccionario de datos muy cortos. Leerá toda la tabla buscando cosas para indexar, pero no bloquea a nadie mientras hace esto.

Si necesita agregar una clave externa, puede hacer esto y hacer que Oracle confíe en que los datos son correctos. De lo contrario, necesita leer toda la tabla y validar todos los valores que pueden ser lentos (cree su índice primero).

Si necesita poner un valor predeterminado o calculado en cada fila de la nueva columna, deberá ejecutar una actualización masiva o tal vez un pequeño programa de utilidad que rellena los nuevos datos. Esto puede ser lento, especialmente si las filas se hacen mucho más grandes y ya no caben en sus bloques. El bloqueo se puede gestionar durante este proceso. Dado que el antiguo versino de su aplicación, que todavía se está ejecutando, no conoce esta columna, es posible que necesite un disparador furtivo o especificar un valor predeterminado.

Desde allí, puede hacer un switcharoo en sus servidores de aplicaciones a la nueva versión del código y seguirá funcionando. Gota tu gatillo astuto.

Alternativamente, puede usar DBMS_REDEFINITION que es una caja negra diseñada para hacer este tipo de cosas.

Todo esto es tan molesto para probar, etc. que solo tenemos un apagón temprano el domingo por la mañana cada vez que lanzamos una versión importante.

Si no puede permitirse el tiempo de inactividad de su base de datos al realizar actualizaciones de aplicaciones, debe considerar mantener un clúster de dos nodos para una alta disponibilidad. Con una configuración de replicación simple, podría hacer cambios estructurales casi completamente en línea como el que sugiere:

• espere a que todos los cambios se replicen en un esclavo pasivo
• cambiar el esclavo pasivo para ser el maestro activo
• hacer los cambios estructurales al viejo maestro
• replicar cambios desde el nuevo maestro al viejo maestro
• haga el intercambio maestro de nuevo y la implementación de la nueva aplicación simultáneamente

No siempre es fácil, pero funciona, por lo general con 0 tiempo de inactividad! El segundo nodo no tiene que ser solo pasivo, se puede usar para pruebas, hacer estadísticas o como un nodo de reserva. Si no tiene infraestructura, la replicación se puede configurar dentro de una sola máquina (con dos instancias de MySQL).

No. Si está utilizando tablas MyISAM, a mi mejor entender, solo hacen bloqueos de tabla-no hay bloqueos de registro, solo tratan de mantener todo hiperrápido a través de la simplicidad. (Otras tablas MySQL operan de manera diferente.) En cualquier caso, puede copiar la tabla a otra tabla, alterarla y luego cambiarla, actualizándola por diferencias.

Esta es una alteración tan masiva que dudo que cualquier DBMS lo apoye. Se considera un beneficio poder hacerlo con los datos de la tabla en el primer lugar.

Solución Temporal...

Otra solución podría ser, agregar otra tabla con la clave primaria de la tabla original, junto con su nueva columna.

Rellene su clave principal en la nueva tabla y rellene los valores para la nueva columna en su nueva tabla, y modifique su consulta para unirse a esta tabla para seleccionar operaciones y también necesita insertar, actualizar por separado para este valor de columna.

Cuando pueda obtener tiempo de inactividad, puede alterar la tabla original, modificar sus consultas DML y deja caer tu nueva tabla creada anteriormente

De lo contrario, puede optar por clustering method, replication, pt-online-schema tool de percona

Usando el complemento Innodb, las instrucciones ALTER TABLE que solo agregan o eliminan índices secundarios se pueden hacer "rápidamente", es decir, sin reconstruir la tabla.

En términos generales, sin embargo, en MySQL, cualquier ALTER TABLE implica reconstruir toda la tabla, lo que puede llevar mucho tiempo (es decir, si la tabla tiene una cantidad útil de datos).

Realmente necesita diseñar su aplicación para que las sentencias ALTER TABLE no tengan que hacerse regularmente; ciertamente no desea ningún ALTER TABLA realizada durante la ejecución normal de la aplicación a menos que esté preparado para esperar o que está alterando pequeñas tablas.

Recomendaría uno de dos enfoques:

1. Diseñe las tablas de su base de datos teniendo en cuenta los posibles cambios. Por ejemplo, he trabajado con Sistemas de Gestión de Contenido, que cambian los campos de datos en el contenido regularmente. En lugar de construir la estructura física de la base de datos para que coincida con los requisitos iniciales del campo CMS, es mucho mejor construir una estructura flexible. En este caso, usar un campo de texto blob (varchar (max) por ejemplo) para contener datos XML flexibles. Esto hace cambios estructurales muy menos frecuentes. Los cambios estructurales pueden ser costosos, por lo que hay un beneficio para costar aquí también.

2. Tenga tiempo de mantenimiento del sistema. O bien el sistema se desconecta durante los cambios (mensuales, etc.), y los cambios se programan durante la hora menos transitada del día (3-5am, por ejemplo). Los cambios se realizan antes del lanzamiento de la producción, por lo que tendrá una buena estimación de tiempo de inactividad de la ventana fija.

2a. Tener servidores redundantes, para que cuando el sistema tiene tiempo de inactividad, todo el sitio no se caiga. Esto le permitiría "rodar" sus actualizaciones de una manera escalonada, sin tener todo el sitio abajo.

Las opciones 2 y 2a pueden no ser viables; tienden a ser solo para sitios/operaciones más grandes. Sin embargo, son opciones válidas, y he utilizado personalmente todas las opciones presentadas aquí.

Si alguien sigue leyendo esto o viene aquí, este es el gran beneficio de usar un sistema de base de datos NoSQL como mongodb. Tuve el mismo problema al alterar la tabla para agregar columnas para características adicionales o índices en una tabla grande con millones de filas y escrituras altas. Terminaría bloqueándose durante mucho tiempo, por lo que hacer esto en la base de datos en VIVO frustraría a nuestros usuarios. En mesas pequeñas puedes salirte con la tuya.

Odio el hecho de que tenemos que "diseñar nuestras mesas para evitar alterarlas". No creo que eso funcione en el mundo de los sitios web de hoy. No puedes predecir cómo las personas usarán tu software, por eso cambias rápidamente las cosas según los comentarios de los usuarios. Con mongodb, puede agregar "columnas" a voluntad sin tiempo de inactividad. Realmente ni siquiera los agregas, solo insertas datos con nuevas columnas y lo hace automáticamente.

Vale la pena echarle un vistazo: www.mongodb.com

En general, la respuesta va a ser "No". Estás cambiando la estructura de la tabla que potencialmente requerirá muchas actualizaciones " y definitivamente estoy de acuerdo con eso. Si espera hacer esto a menudo, entonces ofreceré una alternativa a las columnas "ficticias": use VIEWs en lugar de tablas para los datos de SELECTing. IIRC, cambiar la definición de una vista es relativamente ligero y la indirección a través de una vista se realiza cuando se compila el plan de consulta. El gasto es que tendrías que añadir la columna a una nueva tabla y hacer la vista JOIN en la columna.

Por supuesto, esto solo funciona si puede usar claves foráneas para realizar la cascada de eliminaciones y demás. La otra ventaja es que puede crear una nueva tabla que contenga una combinación de los datos y apuntar la vista a ella sin molestar el uso del cliente.

Solo un pensamiento.

La diferencia entre Postgres y MySQL en este sentido es que en Postgres no recrea una tabla, sino que modifica el diccionario de datos que es similar a Oracle. Por lo tanto, la operación es rápida, mientras que todavía requiere asignar un bloqueo de tabla DDL exclusivo por un tiempo muy corto como se indicó anteriormente por otros.

En MySQL, la operación copiará los datos a una nueva tabla mientras bloquea las transacciones, lo que ha sido un problema principal para los DBA de MySQL antes de la versión 5.6.

La buena noticia es que desde la publicación de MySQL 5.6 la restricción ha sido en su mayoría levantada y ahora puede disfrutar del verdadero poder de la base de datos MYSQL.

Como SeanDowney ha mencionado, pt-online-schema-change es una de las mejores herramientas para hacer lo que has descrito en la pregunta aquí. Recientemente hice muchos cambios de esquema en una base de datos en vivo y salió bastante bien. Puedes leer más al respecto en mi entrada de blog aquí: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/.

Definitivamente deberías probar pt-online-schema-change. He estado usando esta herramienta para hacer migraciones en AWS RDS con varios esclavos y ha funcionado muy bien para mí. Escribí una entrada de blog elaborada sobre cómo hacer eso que podría ser útil para usted.

Blog: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona /

Las columnas ficticias son una buena idea si puede predecir su tipo (y hacerlas nullables). Compruebe cómo su motor de almacenamiento maneja los nulls.

MyISAM bloqueará todo si incluso menciona un nombre de mesa de pasada, por teléfono, en el aeropuerto. Simplemente hace eso...

Dicho esto, los bloqueos no son realmente un gran problema; siempre y cuando no esté tratando de agregar un valor predeterminado para la nueva columna a cada fila, pero deje que se siente como null, y su motor de almacenamiento es lo suficientemente inteligente como para no ir al escribirlo, debería estar de acuerdo con un bloqueo que solo se mantenga el tiempo suficiente para actualizar los metadatos. Si intentas escribir un nuevo valor, bueno, estás frito.

TokuDB puede agregar/soltar columnas y agregar índices "calientes", la tabla está completamente disponible durante todo el proceso. Está disponible a través de www.tokutek.com

En realidad no.

Está alterando la estructura subyacente de la tabla, después de todo, y eso es un poco de información que es bastante importante para el sistema subyacente. También está (probablemente) moviendo gran parte de los datos en el disco.

Si planea hacer esto mucho, es mejor simplemente rellenar la tabla con columnas "ficticias" que están disponibles para uso futuro.