Perché usare innodb_file_per_table?

Non credo sia una questione di performance ma di gestione.

Con file separati per tabella, ad esempio, è possibile archiviare diversi database in diversi dispositivi di archiviazione.

È possibile gestire il caso di database molto grandi in file system che non sono in grado di gestire file di grandi dimensioni (almeno rimandare il problema fino a quando una tabella raggiunge il limite di dimensione del file).

Non hai una crescita incontrollata del tablespace. Se hai dei tavoli grandi che elimini, il file ibdata file rimane piccolo.

Un aspetto che potrebbe avere qualche effetto sulle prestazioni è la frammentazione dei dati e degli indici delle tabelle, che saranno limitati per tabella. Ma questo necessita di test per essere confermato.

Perché usare innodb_file_per_table?

Perché è più facile da gestire individualmente poiché può essere fatto a livello di file. Ciò significa che anche se il server è inattivo, puoi comunque copiare i dati copiando i file della tabella mentre l'utilizzo di un tablespace condiviso significa copiare tutto che può essere inutilmente enorme, o trovare un modo per far funzionare il server per estrarre i dati (davvero non vuoi estrarre manualmente i dati con un editor esadecimale).

Qualcuno ha avvertito che non puoi semplicemente copiare e incollare .ibd file da un server all'altro. Questo può essere vero, ma non dovrebbe applicarsi ai backup sullo stesso server (sto usando il termine backup qui nel senso tradizionale di fare una copia; cioè, non cambiare drasticamente il tutto). Inoltre, ibdata1 viene ricreato automaticamente all'avvio (come visto nel file delete ibdata1 passaggio della maggior parte delle guide "conversione in file per tabella"). In quanto tale, non devi copiare ibdata1 oltre al tuo .ibd file (e il loro corrispondente .frm , ecc.).

Se si tenta di recuperare una tabella persa, dovrebbe essere sufficiente copiarne il .ibd e .frm file, così come information_schema (che è molto minore di ibdata1 ). In questo modo, puoi metterli in un server fittizio ed estrarre la tua tabella senza dover copiare l'intera cosa enorme.

Tuttavia, l'affermazione di prestazioni migliori è discutibile. … Con innodb_file_per_table, sono necessarie più operazioni di I/O su disco; e questo è significativo nei JOIN complicati e nei vincoli FOREIGN KEY.

Non sorprende che le prestazioni dipenderanno interamente dai database specifici in uso. Una persona avrà risultati (anche molto) diversi da un'altra.

È vero che ci saranno più operazioni di I/O su disco con file per tabella, ma solo di poco di più. Pensa a come funziona il sistema.

• Per un database monolitico:

  1. Il server è avviato
  2. ibdata1 è aperto
  3. Intestazione e metadati vengono letti
  4. Le strutture e i meta-dati vengono memorizzati nella cache
  5. Le query capitano
     1. Il server accede al disco e legge i dati dal ibdata1 già aperto
     2. Il server potrebbe memorizzare nella cache i dati

• Per un database per tabella:

  1. Il server è avviato
  2. ibdata1 è aperto
  3. Intestazione e metadati vengono letti
  4. Ogni individuo .ibd il file è aperto
  5. Intestazione e metadati vengono letti da ogni .ibd file
  6. Le strutture e i meta-dati vengono memorizzati nella cache
  7. Le query capitano
     1. Il server accede al disco e legge i dati dal .ibd già aperto file
     2. Il server potrebbe memorizzare nella cache i dati

Noterai che quando il server è in esecuzione, non puoi spostare i file di dati perché il server ha handle aperti per essi. Questo perché quando si avvia li apre e li lascia aperti. Non li apre e non li chiude per ogni singola query.

Pertanto, all'inizio, quando il server si avvia, ci sono solo altre operazioni di I/O; non mentre è in esecuzione. Inoltre, mentre ogni singolo .ibd file ha il proprio sovraccarico separato (firme di file, strutture, ecc.), vengono memorizzati nella cache e non vengono riletti per ogni query. Inoltre, le stesse strutture vengono lette anche con un tablespace condiviso, quindi non c'è quasi nessuna memoria in più richiesta.

innodb_file_per_table ha un effetto su una migliore performance di mysql?

In realtà, semmai, le prestazioni potrebbero essere peggiori .

Quando si utilizza un tablespace condiviso, le operazioni di lettura e scrittura possono a volte/spesso essere combinate in modo che il server legga un campione di dati da più tabelle in una volta sola da ibdata .

Tuttavia, se i dati sono distribuiti su più file, devono eseguire un'operazione di I/O separata per ciascuno di essi individualmente.

Ovviamente anche questo dipende interamente dal database in questione; l'impatto sulle prestazioni nel mondo reale dipenderà dalle dimensioni, dalla frequenza delle query e dalla frammentazione interna del tablespace condiviso. Alcune persone potrebbero notare una grande differenza, mentre altre potrebbero non vedere alcun impatto.

Il tablespace è condiviso su un singolo ibdata; in che modo tablespace dedicati per tabelle separate possono risparmiare spazio su disco?

Non è così. Semmai, aumenta utilizzo del disco alcuni.

Non ho un database da 60 GB con cui testare, ma il mio database personale "irrisorio" che contiene la mia installazione di WordPress e alcune piccole tabelle per uso personale e test di sviluppo pesava circa 30 MB durante l'utilizzo di un tablespace condiviso. Dopo averlo convertito in file per tabella, si è gonfiato fino a ~ 85 MB. Anche eliminando tutto e reimportando, erano ancora>60 MB.

Questo aumento è dovuto a due fattori:

• Il minimo assoluto dimensione per ibdata1 è, per qualche motivo, 10 MB, anche se non hai altro che information_schema memorizzati in esso.

• Con un tablespace condiviso, solo ibdata1 ha un sovraccarico come firme di file, metadati, ecc., ma con per tabella, ogni singolo .ibd file ha tutto questo. Ciò significa che il totale (anche con un ipotetico <10MB ibdata1 ) sarebbe un po' più grande di almeno:

  GetTotalSizeofOverhead() * GetNumTables()
  

Ovviamente questi non saranno enormi aumenta (a meno che tu non stia utilizzando un host che limita le dimensioni del tuo database o memorizzandoli su un'unità flash, ecc.), ma sono comunque aumenti, e mentre cambiando (ogni ) table in file-per-table puoi ridurre ibdata1 fino a 10 MB, il totale complessivo sarà invariabilmente di più di quanto non fosse.

Questo è il motivo per cui utilizzo SEMPRE innodb_file_per_table:

Senza file per tabella, il file ibdata non si comprime, non si restringe o diminuisce mai nello spazio. Non quando elimini una riga, elimini una tabella o un database. 2 GB di dati possono diventare un file da 20 GB in pochissimo tempo se si dispone di un sistema di accodamento attivo.

Supponiamo che tu voglia fare un backup della tua attuale tabella da 1 GB prima di una modifica, quindi rilasciarlo in seguito. Sei bloccato con un GB di spazio ora inutilizzato nel tuo ibdata. Peccato.

Ci sono probabilmente infiniti esempi di casi in cui misure temporanee gonfiano il singolo file di dati, ma basti dire che, a mio parere, non c'è mai motivo di NON usare innodb_file_per_table

Inoltre, ecco un buon post da leggere:http://code.openark.org/blog/mysql/reasons-to-use-innodb_file_per_table