133 votos

¿HOWTO: Limpiar un motor de almacenamiento InnoDB de mysql?

¿Es posible limpiar un motor de almacenamiento innodb de mysql, así que no está almacenando datos de tablas borradas?

¿O tengo que reconstruir una nueva base de datos cada vez?

351voto

RolandoMySQLDBA Puntos 19439

Aquí está una respuesta más completa con respecto a InnoDB. Es un poco de un largo proceso, pero puede ser vale la pena el esfuerzo.

Tenga en cuenta que /var/lib/mysql/ibdata1 es la más visitada en el archivo de InnoDB infraestructura. Normalmente las casas de los seis tipos de información:

  • Tabla De Datos
  • Los Índices De Tabla
  • MVCC (Multiversioning Control de Concurrencia)Datos
    • Los Segmentos De Rollback
    • Deshacer El Espacio
  • Tabla De Metadatos (Diccionario De Datos)
  • Doble Búfer de Escritura (fondo de escritura para prevenir la dependencia en el sistema operativo en la memoria caché)
  • Insertar Buffer (la gestión de los cambios para no único índices secundarios)
  • Ver el Pictorial Representation of ibdata1

InnoDB Arquitectura

InnoDB Architecture

Muchas personas crean múltiples ibdata ficheros con la esperanza de un mejor espacio en disco de la gestión y el rendimiento, sin embargo, que la creencia es errónea.

¿Puedo ejecutar OPTIMIZE TABLE ?

Por desgracia, la ejecución OPTIMIZE TABLE contra una tabla InnoDB almacena en la tabla compartida de espacio de archivo ibdata1 hace dos cosas:

  • Hace que los datos de la tabla y los índices contiguos dentro de ibdata1
  • Hace ibdata1 de crecer porque la contiguos de datos y las páginas de índice se anexa a ibdata1

Sin embargo, puede segregar los Datos de la Tabla y los Índices de Tabla de ibdata1 y gestionar de forma autónoma.

¿Puedo ejecutar OPTIMIZE TABLE con innodb_file_per_table ?

Suponga que usted se agregue innodb_file_per_table a /etc/my.cnf (my.ini). Puede, a continuación, sólo tiene que ejecutar OPTIMIZE TABLE en todas las Tablas InnoDB?

Buenas Noticias : Al ejecutar OPTIMIZE TABLE con innodb_file_per_table habilitado, esto producirá un .ibd de archivo de la tabla. Por ejemplo, si usted tiene la tabla mydb.mytable conuna datadir de /var/lib/mysql, se producirán los siguientes:

  • /var/lib/mysql/mydb/mytable.frm
  • /var/lib/mysql/mydb/mytable.ibd

La .ibd contendrá los Datos de Páginas y Páginas de Índice para la tabla. Gran.

Malas Noticias : Todo lo que han hecho es extraer los Datos de Páginas y Páginas de Índice de mydb.mytable viven en ibdata. El diccionario de datos de entrada para cada tabla, incluyendo mydb.mytable, aún se mantiene en el diccionario de datos (Ver la Representación Pictórica de ibdata1). USTED NO PUEDE SIMPLEMENTE ELIMINAR ibdata1 EN ESTE MOMENTO !!! Por favor, tenga en cuenta que ibdata1 no ha disminuido en absoluto.

InnoDB Infraestructura De Limpieza

Para reducir ibdata1 una vez por todas debe hacer lo siguiente:

  1. Dump (por ejemplo, mysqldump) todas las bases de datos en .sql archivo de texto (SQLData.sql se utiliza a continuación)

  2. Caída de todas las bases de datos (excepto mysql y information_schema) ADVERTENCIA : Como medida de precaución, por favor ejecute este script para hacer absolutamente seguro de que tiene todo usuario de subvenciones en el lugar:

    mkdir /var/lib/mysql_grants
    cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
    chown -R mysql:mysql /var/lib/mysql_grants
    
  3. Inicio de sesión de mysql y ejecutar SET GLOBAL innodb_fast_shutdown = 0; (Esto va a vaciar por completo el resto de cambios transaccionales desde ib_logfile0 y ib_logfile1)

  4. Cierre De MySQL

  5. Agregue las líneas siguientes a /etc/my.cnf (o my.ini en Windows)

    [mysqld]
    innodb_file_per_table
    innodb_flush_method=O_DIRECT
    innodb_log_file_size=1G
    innodb_buffer_pool_size=4G
    

    (Nota al margen: para Cualquier conjunto de innodb_buffer_pool_size, asegúrese innodb_log_file_size 25% innodb_buffer_pool_size.

    También: innodb_flush_method=O_DIRECT no está disponible en Windows)

  6. Borrar ibdata* y ib_logfile*, Opcionalmente, usted puede quitar todas las carpetas /var/lib/mysql, excepto /var/lib/mysql/mysql.

  7. Iniciar MySQL (Esto va a recrear ibdata1 [10 MB por defecto] y ib_logfile0 y ib_logfile1 1 g cada uno).

  8. De importación SQLData.sql

Ahora, ibdata1 todavía va a crecer, pero sólo contienen metadatos de la tabla, ya que cada tabla InnoDB va a existir fuera de ibdata1. ibdata1 no contendrán InnoDB datos y los índices para el resto de tablas.

Por ejemplo, supongamos que tiene una tabla InnoDB nombrado mydb.mytable. Si usted mira en /var/lib/mysql/mydb, podrás ver dos archivos que representan a la tabla:

  • mytable.frm (Motor De Almacenamiento De Encabezado)
  • mytable.ibd (Datos de la tabla y los Índices)

Con el innodb_file_per_table opción en /etc/my.cnf, puede ejecutar OPTIMIZE TABLE mydb.mytable y el archivo /var/lib/mysql/mydb/mytable.ibd se reducirá realmente.

He hecho esto muchas veces en mi carrera como MySQL DBA. De hecho, la primera vez que hice esto, yo se redujo un 50 ibdata1 archivo de abajo a sólo 500 MB!

Darle una oportunidad. Si usted tiene más preguntas sobre esto, sólo pregunte. Confía en mí; esto funcionará en el corto plazo, así como en el largo plazo.

ADVERTENCIA

En el Paso 6, si mysql no puede reiniciar debido a las mysql esquema de empezar a caer, mira de nuevo el Paso 2. Se hizo la copia física de la mysql esquema. Se puede restaurar como sigue:

mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_grants/* /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql/mysql

Vaya al Paso 6 y continuar

ACTUALIZACIÓN 2013-06-04 11:13 EDT

Con respecto a la configuración de innodb_log_file_size 25% de innodb_buffer_pool_size en el Paso 5, que la colcha de la regla es más bien de la vieja escuela.

De vuelta en July 03, 2006, Percona tenido un buen artículo ¿por qué elegir un buen innodb_log_file_size. Más tarde, en Nov 21, 2008, Percona seguido con otro artículo sobre cómo calcular el tamaño adecuado basado en el pico de carga de trabajo de mantenimiento de una hora de los cambios.

Desde entonces he escrito posts en el DBA StackExchange acerca de cómo calcular el tamaño del registro y en el que hace referencia a los dos Percona artículos.

Personalmente, me gustaría ir con el 25% de la regla para una configuración inicial. Entonces, como la carga de trabajo puede más exacto será determinado a través del tiempo en la producción, usted puede cambiar el tamaño de los registros durante un ciclo de mantenimiento en pocos minutos.

4voto

bigjeff Puntos 31

El motor InnoDB no almacena los datos eliminados. Como insertar y eliminar filas, sin utilizar el espacio asignado dentro de la InnoDB de almacenamiento de archivos. Con el tiempo, el espacio global no va a disminuir, pero con el tiempo el 'borrados y liberan espacio será automáticamente reutilizados por el servidor de DB.

Usted puede sincronizar y administrar el espacio utilizado por el motor a través de un manual de re-org de las tablas. Para ello, el volcado de los datos en las tablas afectadas usando mysqldump, la caída de las tablas, reiniciar el servicio de mysql, y luego volver a crear las tablas de los archivos de volcado.

2voto

mike Puntos 151

FYI, después de tanto rascarse cabeza que sólo he averiguado O_DIRECT no se puede utilizar en Windows.

"En Windows, el método de descarga es siempre async_unbuffered y no puede ser cambiado"

De: http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar _innodb_flush_method

0voto

MiSHuTka Puntos 368

Puede utilizar query OPTIMIZE TABLE periódicamente para optimizar su almacenamiento de información y acelerar las consultas SELECT un poco. Enlace a Manual de MySQL: http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X