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

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

• Datos de la tabla
• Índices de tabla
• MVCC (Multiversioning Concurrency Control) Data
  • Segmentos de reversión
  • Deshacer Espacio
• Metadatos de la tabla (Datos Diccionario)
• Buffer de doble escritura (escritura en segundo plano para evitar la dependencia del almacenamiento en caché del sistema operativo)
• Insertar Búfer (gestión de cambios en índices secundarios no únicos)
• Ver el Pictorial Representation of ibdata1

Arquitectura InnoDB

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

Puedo ejecutar OPTIMIZE TABLE ?

Desafortunadamente, corriendo OPTIMIZE TABLE contra una tabla InnoDB almacenada en el archivo de espacio de tabla compartido ibdata1 hace dos cosas:

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

Sin embargo, puede separar los Datos de la Tabla y los Índices de la Tabla de ibdata1 y administrarlos de forma independiente.

Puedo ejecutar OPTIMIZE TABLE con innodb_file_per_table ?

Supongamos que se va a añadir innodb_file_per_table a /etc/my.cnf (my.ini). ¿Puedes correr?OPTIMIZE TABLE ¿en todas las mesas de InnoDB?

Buenas Noticias : Cuando se ejecuta OPTIMIZE TABLE con innodb_file_per_table habilitado, esto producirá un .ibd archivo de la tabla. Por ejemplo, si tiene la tabla mydb.mytable con un datadir de /var/lib/mysql, producirá el siguiente:

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

El .ibd contendrá las Páginas de Datos y las Páginas de Índice para esa tabla. Gran.

Malas Noticias : Todo lo que has hecho es extraer las Páginas de Datos y las Páginas de Índice de mydb.mytable de vivir en ibdata. La entrada del diccionario de datos para cada tabla, incluyendo mydb.mytable, aún permanece en el diccionario de datos (Ver la Representación pictórica de ibdata1). NO PUEDE SIMPLEMENTE ELIMINAR ibdata1 EN ESTE PUNTO !!! Tenga en cuenta que ibdata1 no se ha reducido en absoluto.

InnoDB Infrastructure Cleanup

Para reducir ibdata1 de una vez por todas debes hacer lo siguiente:

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

2. Eliminar todas las bases de datos (excepto mysql y information_schema) ADVERTENCIA: Como precaución, ejecute este script para asegurarse de que tiene todas las concesiones de usuario en lugar:

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

3. Inicie sesión en mysql y ejecute SET GLOBAL innodb_fast_shutdown = 0; (Esto eliminará completamente todos los cambios transaccionales restantes de ib_logfile0 y ib_logfile1)

4. Shutdown MySQL

5. Agregue las siguientes líneas 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 lateral: Cualquiera que sea su conjunto para innodb_buffer_pool_size, asegúrese de que innodb_log_file_size es el 25% de innodb_buffer_pool_size.

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

6. Delete ibdata* and ib_logfile*, Opcionalmente, usted puede eliminar todas las carpetas en /var/lib/mysql, excepto /var/lib/mysql/mysql.

7. Inicie MySQL (Esto recreará ibdata1 [10MB por defecto] y ib_logfile0 y ib_logfile1 a 1G cada uno).

8. Importar SQLData.sql

Ahora, ibdata1 seguirá creciendo pero solo contendrá metadatos de tabla porque cada tabla InnoDB existirá fuera de ibdata1. ibdata1 ya no contendrá datos InnoDB e índices para otras tablas.

Por ejemplo, supongamos que tiene una tabla InnoDB llamada mydb.mytable. Si miras en /var/lib/mysql/mydb, verá dos archivos que representan la tabla:

• mytable.frm (Cabecera del motor de almacenamiento)
• mytable.ibd (Tabla de datos e índices)

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

He hecho esto muchas veces en mi carrera como DBA MySQL. De hecho, la primera vez que hice esto, me encogí un 50GB ibdata1 archivo hasta solo 500MB!

Dale una oportunidad. Si tiene más preguntas sobre esto, solo pregunta. Confía en mí; esto funcionará a corto plazo, así como a largo plazo.

ADVERTENCIA

En el paso 6, si mysql no puede reiniciarse debido a que el esquema mysql comienza eliminado, mire hacia atrás en el Paso 2. Usted hizo la copia física del esquema mysql. Puede restaurarlo de la siguiente manera:

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

Vuelva al paso 6 y continúe

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

Con respecto a la fijación de innodb_log_file_size al 25% de innodb_buffer_pool_size en el paso 5, esa regla general es bastante vieja escuela.

De vuelta en July 03, 2006, Percona tenía un buen artículo por qué elegir un innodb_log_file_size apropiado. Más tarde, en Nov 21, 2008, Percona siguió con otro artículo sobre cómo calcular el tamaño adecuado basado en la carga de trabajo máxima manteniendo el valor de una hora de cambios.

Desde entonces he escrito publicaciones en el DBA StackExchange sobre el cálculo del tamaño del registro y dónde hice referencia a esos dos artículos de Percona.

• Aug 27, 2012 : Ajuste adecuado para la tabla InnoDB de 30 GB en el servidor con 48 GB de RAM
• Jan 17, 2013 : MySQL 5.5-Innodb-innodb_log_file_size mayor que 4GB combinado?

Personalmente, todavía iría con la regla del 25% para una configuración inicial. Luego, a medida que la carga de trabajo se puede determinar con más precisión a lo largo del tiempo en la producción, podría cambiar el tamaño de los registros durante un ciclo de mantenimiento en apenas minuto.