No creo que sea una cuestión de rendimiento sino de gestión.
Con un archivo separado por tabla, puede almacenar diferentes bases de datos en diferentes dispositivos de almacenamiento, por ejemplo.
Puede lidiar con el caso de bases de datos muy grandes en sistemas de archivos que no pueden manejar archivos grandes (al menos posponga el problema hasta que una tabla alcance el límite de tamaño de archivo).
No tiene un crecimiento de espacio de tablas descontrolado. Si tiene algunas mesas grandes que descarta, el ibdata el archivo permanece pequeño.
Un aspecto que puede tener algún efecto en el rendimiento es la fragmentación de los índices y los datos de las tablas, que estarán limitados por tabla. Pero eso necesita pruebas para ser confirmado.
¿Por qué usar innodb_file_per_table?
Porque es más fácil de administrar individualmente ya que se puede hacer a nivel de archivo. Esto significa que incluso si el servidor está inactivo, aún puede copiar datos copiando los archivos de la tabla, mientras que usar un espacio de tabla compartido significa copiar todo que puede ser innecesariamente masivo, o encontrar alguna manera de hacer que el servidor se ejecute para extraer datos (realmente no desea extraer manualmente los datos con un editor hexadecimal).
Alguien advirtió que no puedes simplemente copiar y pegar .ibd archivos de un servidor a otro. Esto puede ser cierto, pero no debería aplicarse a las copias de seguridad en el mismo servidor (estoy usando el término copia de seguridad aquí en el sentido tradicional de hacer una copia; es decir, no cambiar drásticamente todo). Además, ibdata1 se vuelve a crear automáticamente en el inicio (como se ve en el delete ibdata1 paso de la mayoría de las guías de "conversión a archivo por tabla"). Como tal, no necesito copiar ibdata1 además de tu .ibd archivos (y sus correspondientes .frm , etc. archivos).
Si intenta recuperar una tabla perdida, debería ser suficiente copiar su .ibd y .frm archivo, así como information_schema (que es mucho menor que ibdata1 ). De esa manera, puede colocarlos en un servidor ficticio y extraer su tabla sin tener que copiar todo el contenido masivo.
Sin embargo, la afirmación de un mejor rendimiento es cuestionable. … Con innodb_file_per_table, se necesitan más operaciones de E/S de disco; y esto es significativo en JOINs complicados y restricciones FOREIGN KEY.
No es sorprendente que el rendimiento dependa completamente de las bases de datos específicas en uso. Una persona tendrá resultados (incluso muy) diferentes a los de otra.
Es cierto que habrá más operaciones de E/S de disco con archivo por tabla, pero solo ligeramente más. Piensa en cómo funciona el sistema.
-
Para una base de datos monolítica:
- El servidor está iniciado
ibdata1está abierto- Se leen el encabezado y los metadatos
- Las estructuras y los metadatos se almacenan en caché en la memoria
- Se realizan consultas
- El servidor accede al disco y lee los datos del
ibdata1ya abierto - El servidor puede almacenar en caché los datos en la memoria
- El servidor accede al disco y lee los datos del
-
Para una base de datos por tabla:
- El servidor está iniciado
ibdata1está abierto- Se leen el encabezado y los metadatos
- Cada individuo
.ibdse abre el archivo - El encabezado y los metadatos se leen de cada
.ibdarchivo - Las estructuras y los metadatos se almacenan en caché en la memoria
- Se realizan consultas
- El servidor accede al disco y lee los datos del
.ibdya abierto archivo - El servidor puede almacenar en caché los datos en la memoria
- El servidor accede al disco y lee los datos del
Notará que cuando el servidor se está ejecutando, no puede mover los archivos de datos porque el servidor tiene identificadores abiertos para ellos. Esto se debe a que cuando arranca, los abre y los deja abiertos. No los abre y cierra para cada consulta individual.
Como tal, solo hay algunas operaciones de E/S más al principio, cuando se inicia el servidor; no mientras se está ejecutando. Además, mientras cada individuo .ibd El archivo tiene su propia sobrecarga separada (firmas de archivos, estructuras, etc.), se almacenan en caché en la memoria y no se vuelven a leer para cada consulta. Además, las mismas estructuras se leen incluso con un espacio de tabla compartido, por lo que apenas se requiere más memoria (si es que se requiere alguna).
¿Innodb_file_per_table tiene un efecto en un mejor rendimiento de mysql?
En realidad, en todo caso, el rendimiento puede ser peor .
Cuando se usa un espacio de tabla compartido, las operaciones de lectura y escritura a veces/a menudo se pueden combinar para que el servidor lea una muestra de datos de varias tablas de una sola vez desde ibdata .
Sin embargo, si los datos se distribuyen entre varios archivos, debe realizar una operación de E/S separada para cada uno individualmente.
Por supuesto, esto nuevamente depende completamente de la base de datos en cuestión; el impacto en el rendimiento del mundo real dependería del tamaño, la frecuencia de las consultas y la fragmentación interna del espacio de tabla compartido. Algunas personas pueden notar una gran diferencia, mientras que otras pueden no ver ningún impacto.
Tablespace se comparte en un solo ibdata; ¿Cómo los tablespaces dedicados para tablas separadas pueden ahorrar espacio en disco?
No es asi. En todo caso, aumenta uso de disco algo.
No tengo una base de datos de 60 GB para probar, pero mi base de datos personal "insignificante" que contiene mi instalación de WordPress y algunas tablas pequeñas para uso personal y pruebas de desarrollo pesaban ~ 30 MB mientras usaba un espacio de tabla compartido. Después de convertirlo a archivo por tabla, aumentó a ~ 85 MB. Incluso dejando todo y volviendo a importar, seguía siendo>60 MB.
Este aumento se debe a dos factores:
-
El mínimo absoluto tamaño para
ibdata1es, por alguna razón, 10 MB, incluso si no tiene nada más queinformation_schemaalmacenado en él. -
Con un espacio de tabla compartido, solo
ibdata1tiene gastos generales como firmas de archivos, metadatos, etc., pero por tabla, cada.ibdindividual archivo tiene todo eso. Esto significa que el total (incluso con un hipotético <10 MBibdata1) sería algo mayor por al menos:GetTotalSizeofOverhead() * GetNumTables()
Obviamente estos no van a ser enormes aumenta (a menos que esté utilizando un host que limite el tamaño de su base de datos o los almacene en una unidad flash, etc.), pero de todos modos aumentan, y mientras cambia ( cada ) tabla a archivo por tabla, puede reducir ibdata1 hasta 10 MB, el total general será invariablemente más de lo que era.
Esta es mi razón para usar SIEMPRE innodb_file_per_table:
Sin archivo por tabla, el archivo ibdata nunca se comprime, encoge o disminuye en el espacio. No cuando elimina una fila, suelta una tabla o una base de datos. 2 GB de datos pueden convertirse en un archivo de 20 GB en poco tiempo si tiene un sistema de cola activo.
Supongamos que desea hacer una copia de seguridad de su tabla actual de 1 GB antes de modificarla y luego suéltela. Está atascado con un GB de espacio ahora sin usar en su ibdata. Qué fastidio.
Probablemente haya un sinfín de ejemplos de instancias en las que las medidas temporales inflan el archivo de datos único, pero baste decir que, en mi opinión, nunca hay una razón para NO usar innodb_file_per_table
Además, aquí hay una buena publicación para leer:http://code.openark.org/blog/mysql/reasons-to-use-innodb_file_per_table