32 votos

MySQL InnoDB - innodb_file_per_table cons?

Por defecto, MySQL InnoDB almacena todas las tablas de todas las bases de datos en un archivo global. Puede cambiar esto estableciendo innodb_file_per_table en el config, que luego crea un archivo de datos para cada tabla.

Me pregunto por qué innodb_file_per_table no está activado por defecto. ¿Hay alguna desventaja en su uso?

12voto

alvosu Puntos 5860

Ver bug .

¿Hay desventajas en su uso?

  • más archivos abiertos
  • abierto/abierto por encima de la cabeza
  • El archivo .ibd no se encoge (véase 1 , 2 )

Siempre uso innodb_file_per_table en las grandes bases de datos.

11voto

Alex Puntos 5342

innodb_file_per_table está activado por defecto en MariaDB.

2voto

Stephen Myall Puntos 252

La razón por la que opté por no usar innodb_file_per_table es porque cada tabla se pone en su propio archivo, lo que significa que cada tabla tiene sus propios gastos generales (firmas de archivos, etc.), lo que causa el tamaño total y global de la MySQL que sea más grande que si se usara un espacio de tablas compartido. Además, hay más espacio desperdiciado debido a la falta de clústeres cuando se tienen varios archivos pequeños en lugar de uno solo grande.

Concedido, los gastos generales adicionales no son una masivo cantidad en el gran esquema de las cosas, especialmente si estás usando un disco grande o tienes una base de datos gigante, pero para mí (y probablemente para muchos "usuarios domésticos"), todo sumaba y seguía siendo demasiado para el pequeño disco con grandes clusters donde guardaba mi tienda de MySQL.

Por ejemplo, mi almacén de bases de datos con mis bases de datos de WordPress y algunas otras pequeñas bases de datos (phpBB, dev, algunas pruebas de AMP, etc.), al convertir a por-tabla lo cambiaron de 32MB a 50MB, y eso ni siquiera incluye el ibdata1 que todavía requiere a mínimo de 10MB para un total de al menos 60MB.

Como dije, puede que esto no sea demasiado problema para algunas personas, especialmente para las empresas, pero si usted es un usuario doméstico que sólo aloja su sitio web, blog, etc., entonces puede ser un factor en cosas como la elección de un proveedor de alojamiento porque muchos anfitriones limitan el tamaño de su base de datos además del uso total del disco.

1voto

Alex Zheng Puntos 26

con innodb_file_per_table=1, la tabla de caída puede ser más lenta ver Aquí

0voto

RolandoMySQLDBA Puntos 10660

Tengo la respuesta completa para esto.

Una vez innodb_file_per_table y las nuevas mesas InnoDB pueden ser encogidas usando ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; Esto se encogerá de nuevo .ibd archivos GARANTIZADOS.

Si corres ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; en una tabla InnoDB creada antes de usar innodb_file_per_table, sacará los datos e índices de esa tabla del archivo ibdata1 y la almacenará en un .ibd Esto dejará una paloma entera permanente en el ibdata1 que nunca podrá ser reutilizada.

El ibdata1 El archivo normalmente alberga cuatro tipos de información

  • Datos de la tabla
  • Índices de la tabla
  • MVCC (Multiversioning Concurrency Control) Datos
    • Segmentos de retroceso
    • Deshacer el espacio
  • Metadatos de la tabla (Diccionario de datos)
  • buffer de escritura doble (escritura en segundo plano para evitar la dependencia del caché del sistema operativo)
  • Insert buffer (gestión de los cambios en los índices secundarios no únicos)
  • Vea el Pictorial Representation of ibdata1

Esta es la forma garantizada de reducir el archivo ibdata1 casi para siempre...

PASO 01) MySQLVerter todas las bases de datos en un archivo de texto SQL (llamarlo SQLData.sql)

PASO 02) Suelte todas las bases de datos (excepto mysql, information_schema y performance_schema schemes)

PASO 03) Apagado mysql

PASO 04) Añade las siguientes líneas a /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend

Sidenote: Cualquiera que sea tu set para innodb_buffer_pool_size, asegúrate de que innodb_log_file_size sea el 25% de innodb_buffer_pool_size.

  • PASO 05) Borrar ibdata1, ib_logfile0 y ib_logfile1 ( ver actualización abajo antes de borrar! )

En este punto, sólo debería haber el esquema de mysql en /var/lib/mysql

  • PASO 06) Reiniciar mysql

Esto recreará ibdata1 a 10MB (no configure la opción) , ib_logfile0 e ib_logfile1 a 1G cada uno

  • PASO 07) Recargar SQLData.sql en mysql

ibdata1 crecerá pero sólo contendrá metadatos de tablas y datos de MVCC intermitentes.

Cada tabla InnoDB existirá fuera de ibdata1

Supongamos que tienes una mesa InnoDB llamada mydb.mytable. Si entras en /var/lib/mysql/mydb verán dos archivos que representan la tabla

  • mytable.frm (Cabecera del motor de almacenamiento)
  • mytable.ibd (Inicio de datos de tablas e índices de tablas para mydb.mytable )

ibdata1 nunca más contendrá datos e índices de InnoDB.

Con el innodb_file_per_table opción en /etc/my.cnf puedes correr OPTIMIZE TABLE mydb.mytable O ALTER TABLE mydb.mytable ENGINE=InnoDB; y el archivo /var/lib/mysql/mydb/mytable.ibd en realidad se encogerá.

He hecho esto numerosas veces en mi carrera como DBA de MySQL sin ningún problema después. De hecho, la primera vez que hice esto, colapsé un archivo ibdata1 de 50GB en 50MB.

Inténtalo. Si tienes más preguntas sobre esto, envíame un correo electrónico. Confía en mí. Esto funcionará a corto plazo y a largo plazo.

ACTUALIZACIÓN 2013-07-02 15:08 EDT

Hay una advertencia que tengo en este sentido que he actualizado en otros mensajes míos pero me he perdido esto: Estoy actualizando mi respuesta un poco más con innodb_fast_shutdown porque solía reiniciar mysql y detener a mysql para hacer esto. Ahora, este paso es vital porque cada transacción no comprometida puede tener otras partes móviles dentro y fuera de los Registros de Transacciones InnoDB ( Véase Infraestructura del InnoDB ).

Tenga en cuenta que el ajuste innodb_fast_shutdown a 2 limpiaría los troncos también, pero todavía existen más partes móviles y se mete en Recuperación de Choques durante el inicio de mysqld. El ajuste de 0 es el mejor.

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by: