MySQL – Passer à un fichier par table
Ce tutoriel explique comment passer la structure physique votre base de donnée MySQL de 1 seul gros fichier , à un fichier par table.
Gardez à l’esprit que le fichier le plus sollicité dans l’infrastructure InnoDB est /var/lib/mysql/ibdata1
Ce fichier héberge normalement beaucoup de classes d’information ( lorsque innodb_file_per_table est à 0 ) :
- Données des tables
- Indexes des tables
- MVCC (Multiversioning Concurrency Control) Data
- Rollbacks Segments
- Undo Tablespace
- Métadonnées des tables
- Voir la Représentation visuelle
Beaucoup de gens créent de multiples fichiers ibdata, espérant une meilleure gestion de l’espace disque et de meilleures performances. Ce n’est d’aucune aide.
Malheureusement, OPTIMIZE TABLE lorsqu’une table InnoDB est stockée dans ibdata1 provoque 2 choses :
- Makes the table’s data and indexes contiguous inside ibdata1
- Cela fait grossir ibdata1 car les données contigües sont ajoutées à ibdata1.
Vous pouvez segregate Table Data et Table Indexes depuis ibdata1 et les gérer indépendamment en utilisant innodb_file_per_table. Pour réduire ibdata1 une fois pour toutes vous devez faire les choses suivantes :
Etape 1 : Faites un MySQLDump de toutes les bases de données dans un fichier texte SQL SQL (appelez-le SQLData.sql) ( Plus de détails ici )
Etape 2 : Droppez (supprimez) toutes les bases de données, sauf :
mysql, performance_schema, et information_schema
Etape 3 : Arrêtez MySQL
Etape 4 : Ajoutez les lignes suivantes à /etc/my.cnf
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=256M
innodb_buffer_pool_size=1G
Note : Quelque-soit la valeur que vous indiquiez pour innodb_buffer_pool_size, assurez-vous que innodb_log_file_size est 25% de innodb_buffer_pool_size.
Etape 5 : Dans /var/lib/mysql, supprimez ibdata1, ib_logfile0 et ib_logfile1
A ce point, il ne doit y avoir que le schéma mysql dans /var/lib/mysql
Etape 6 : Redémarrez MySQL
Cela va recréer ibdata1 à 10MB, ib_logfile0 et ib_logfile1 à 1G chacun.
Etape 7 : Remplacez le moteur de stockage MyISAM par INNODB dans le fichier dump :
sed -i ‘s/MyISAM/INNODB/g’ SQLData.sql
Etape 8 : Rechargez votre fichier SQLData.sql dans MySQL
mysql -uroot -pmot_de_passe nom_base_de_données < SQLData.sql
ibdata1 va grossir mais contiendra seulement les métadonnées des tables.
Chaque table InnoDB va exister en dehors de ibdata1.
Supposez que vous ayez une table InnoDB nommée mydb.mytable. Si vous allez dans /var/lib/mysql/mydb, vous verrez 2 fichiers représentant cette table :
- mytable.frm (Entête du moteur de stockage)
- mytable.ibd (Réceptacle des données de table et indexes de table pour mydb.mytable)
ibdata1 ne contiendra plus jamais les données et les indexes InnoDB.
Avec l’option innodb_file_per_table dans /etc/my.cnf, vous pouvez lancer OPTIMIZE TABLE mydb.mytable et le fichier /var/lib/mysql/mydb/mytable.ibd va se réduire.
J’ai fait ceci de nombreuses fois dans ma carrière de DBA MySQL. (Note de la source)
En fait, la première fois que j’ai fait ceci, j’ai réduit un fichier ibdata1 de 50GB à 500MB.
Faites un essai.
Si vous voulez voir combien actuellement de données sont stockées en MyISAM et InnoDB, vous pouvez lancer cette requête :
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;
Le résultat ressemblera à ça :
+----------------+----------------------+----------------------+----------------------+ | Storage Engine | Data Size | Index Size | Table Size | +----------------+----------------------+----------------------+----------------------+ | InnoDB | 3.537 GB | 0.001 GB | 3.538 GB | | Total | 3.537 GB | 0.001 GB | 3.538 GB | +----------------+----------------------+----------------------+----------------------+ 2 rows in set (0.61 sec) mysql>
