Pourquoi utiliser innodb_file_per

Je ne pense pas que ce soit une question de performance mais de management.

Avec un fichier séparé par table, vous pouvez par exemple stocker différentes bases de données dans différents périphériques de stockage.

Vous pouvez traiter le cas de très grandes bases de données dans des systèmes de fichiers qui ne peuvent pas gérer de gros fichiers (au moins reporter le problème jusqu'à ce qu'une table atteigne la limite de taille de fichier).

Vous n'avez pas de croissance incontrôlée de l'espace de table. Si vous avez de grosses tables que vous supprimez, le ibdata le fichier reste petit.

Un aspect qui peut avoir un certain effet sur les performances est la fragmentation des données de table et des index, qui sera limitée par table. Mais cela doit être testé pour être confirmé.

Pourquoi utiliser innodb_file_per_table ?

Parce qu'il est plus facile à gérer individuellement car cela peut être fait au niveau du fichier. Cela signifie que même si le serveur est en panne, vous pouvez toujours copier des données en copiant les fichiers de table alors que l'utilisation d'un espace de table partagé signifie soit copier tout qui peuvent être inutilement massifs, ou trouver un moyen de faire fonctionner le serveur pour extraire des données (vous ne voulez vraiment pas extraire manuellement les données avec un éditeur hexadécimal).

Quelqu'un a averti que vous ne pouvez pas simplement copier et coller .ibd fichiers d'un serveur à un autre. Cela peut être vrai, mais cela ne devrait pas s'appliquer aux sauvegardes sur le même serveur (j'utilise le terme sauvegarde ici au sens traditionnel de faire une copie; c'est-à-dire, ne changeant pas radicalement le tout). De plus, ibdata1 est automatiquement recréé au démarrage (comme on le voit dans le delete ibdata1 étape de la plupart des guides de "conversion en fichier par table"). En tant que tel, vous ne faites pas besoin de copier ibdata1 en plus de votre .ibd fichiers (et leur .frm correspondant , etc.).

Si vous essayez de récupérer une table perdue, il devrait suffire de copier son .ibd et .frm fichier, ainsi que information_schema (ce qui est beaucoup inférieur à ibdata1 ). De cette façon, vous pouvez les mettre dans un serveur factice et extraire votre table sans avoir à copier l'intégralité de la chose massive.

Cependant, l'affirmation d'une meilleure performance est discutable. … Avec innodb_file_per_table, plus d'opérations d'E/S de disque sont nécessaires; et ceci est important dans les contraintes JOIN et FOREIGN KEY compliquées.

Sans surprise, les performances dépendront entièrement de la ou des bases de données spécifiques utilisées. Une personne aura des résultats (même très) différents d'une autre.

Il est vrai qu'il y aura plus d'opérations d'E/S disque avec le fichier par table, mais seulement légèrement Suite. Réfléchissez au fonctionnement du système.

Pour une base de données monolithique :
1. Le serveur est démarré
2. ibdata1 est ouvert
3. L'en-tête et les métadonnées sont lues
4. Les structures et les métadonnées sont mises en cache en mémoire
5. Les requêtes se produisent
  1. Le serveur accède au disque et lit les données du ibdata1 déjà ouvert
  2. Le serveur peut mettre en cache les données en mémoire
Pour une base de données par table :
1. Le serveur est démarré
2. ibdata1 est ouvert
3. L'en-tête et les métadonnées sont lus
4. Chaque individu .ibd le fichier est ouvert
5. L'en-tête et les métadonnées sont lus à partir de chaque .ibd fichier
6. Les structures et les métadonnées sont mises en cache en mémoire
7. Les requêtes se produisent
  1. Le serveur accède au disque et lit les données du .ibd déjà ouvert fichier
  2. Le serveur peut mettre en cache les données en mémoire

Vous remarquerez que lorsque le serveur est en cours d'exécution, vous ne pouvez pas déplacer les fichiers de données car le serveur a des descripteurs ouverts pour eux. En effet, lorsqu'il démarre, il les ouvre et les laisse ouverts. Il ne les ouvre et ne les ferme pas pour chaque requête individuelle.

En tant que tel, il n'y a que quelques opérations d'E/S supplémentaires au début, lorsque le serveur démarre; pas pendant qu'il tourne. De plus, alors que chaque .ibd individuel Le fichier a sa propre surcharge séparée (signatures de fichier, structures, etc.), ils sont mis en cache en mémoire et ne sont pas relus pour chaque requête. De plus, les mêmes structures sont lues même avec un table-space partagé, donc il n'y a pratiquement pas (voire pas du tout) de mémoire supplémentaire requise.

innodb_file_per_table a-t-il un effet sur une meilleure performance de mysql ?

En fait, si quoi que ce soit, les performances peuvent en fait être pires .

Lors de l'utilisation d'un table-space partagé, les opérations de lecture et d'écriture peuvent parfois/souvent être combinées afin que le serveur lise un échantillon de données de plusieurs tables en une seule fois à partir de ibdata .

Cependant, si les données sont réparties sur plusieurs fichiers, elles doivent effectuer une opération d'E/S distincte pour chacun individuellement.

Bien sûr, cela dépend à nouveau entièrement de la base de données en question ; l'impact réel sur les performances dépendrait de la taille, de la fréquence des requêtes et de la fragmentation interne de l'espace table partagé. Certaines personnes peuvent remarquer une grande différence tandis que d'autres peuvent ne pas voir d'impact du tout.

Le tablespace est partagé sur un seul ibdata ; comment les tablespaces dédiés pour des tables séparées peuvent économiser de l'espace disque ?

Ce ne est pas. Si quoi que ce soit, il augmente utilisation du disque certains.

Je n'ai pas de base de données de 60 Go pour tester, mais ma base de données personnelle « dérisoire » qui contient mon installation WordPress et quelques petites tables pour un usage personnel et des tests de développement pesait environ 30 Mo tout en utilisant un espace de table partagé. Après l'avoir converti en fichier par table, il a gonflé à environ 85 Mo. Même en supprimant tout et en réimportant, c'était toujours> 60 Mo.

Cette augmentation est due à deux facteurs :

Le minimum absolu taille pour ibdata1 est—pour une raison quelconque—10 Mo, même si vous n'avez que information_schema stocké dedans.
Avec un table-space partagé, seulement ibdata1 a une surcharge comme les signatures de fichiers, les métadonnées, etc., mais avec par table, chaque individu .ibd le fichier contient tout cela. Cela signifie que le total (même avec un hypothétique <10 Mo ibdata1 ) serait un peu plus grand d'au moins :
```
GetTotalSizeofOverhead() * GetNumTables()
```

Évidemment, ceux-ci ne seront pas énormes augmente (sauf si vous utilisez un hôte qui limite la taille de votre base de données ou les stocke sur un lecteur flash, etc.), mais ils augmentent néanmoins, et en changeant (chaque ) table en fichier par table, vous pouvez réduire ibdata1 jusqu'à 10 Mo, le total global sera invariablement plus qu'il ne l'était.

C'est la raison pour laquelle j'utilise TOUJOURS innodb_file_per_table :

Sans fichier par table, le fichier ibdata ne se comprime jamais, ne rétrécit ou ne diminue jamais dans l'espace. Pas lorsque vous supprimez une ligne, supprimez une table ou une base de données. 2 Go de données peuvent devenir un fichier de 20 Go en un rien de temps si vous disposez d'un système de file d'attente actif.

Supposons que vous souhaitiez effectuer une sauvegarde de votre table actuelle de 1 Go avant une modification, puis la supprimer par la suite. Vous êtes coincé avec un Go d'espace maintenant inutilisé dans votre ibdata. Dommage.

Il existe probablement d'innombrables exemples de cas où des mesures temporaires gonflent le fichier de données unique, mais il suffit de dire qu'à mon avis, il n'y a jamais de raison de ne PAS utiliser innodb_file_per_table

Aussi, voici un bon article à lire :http://code.openark.org/blog/mysql/reasons-to-use-innodb_file_per_table