Comment faire : Nettoyer un moteur de stockage mysql InnoDB ?

Question

Est-il possible de nettoyer un moteur de stockage mysql innodb afin qu'il ne stocke pas les données des tables supprimées ?

Ou dois-je reconstruire une nouvelle base de données à chaque fois ?

Answer 1

Voici une réponse plus complète en ce qui concerne InnoDB. C'est un processus un peu long, mais qui peut en valoir la peine.

Gardez à l'esprit que /var/lib/mysql/ibdata1 est le fichier le plus utilisé dans l'infrastructure InnoDB. Il abrite normalement six types d'informations :

• Données du tableau
• Index des tables
• MVCC (Multiversioning Concurrency Control) Données
  • Segments de retour en arrière
  • Annuler l'espace
• Métadonnées de la table (dictionnaire de données)
• Double tampon d'écriture (écriture en arrière-plan pour éviter de dépendre du cache du système d'exploitation)
• Insert Buffer (gestion des modifications apportées aux index secondaires non uniques)
• Voir le Pictorial Representation of ibdata1

Architecture InnoDB

De nombreuses personnes créent plusieurs ibdata en espérant une meilleure gestion de l'espace disque et de meilleures performances, mais cette croyance est erronée.

Je peux courir OPTIMIZE TABLE ?

Malheureusement, l'exécution OPTIMIZE TABLE contre une table InnoDB stockée dans le fichier de tablespace partagé ibdata1 fait deux choses :

• Rend les données et les index de la table contigus à l'intérieur de la table. ibdata1
• Cela donne ibdata1 grandissent parce que les pages contiguës de données et d'index sont en annexe à ibdata1

Vous pouvez cependant séparer les données de la table et les index de la table des données de la table. ibdata1 et les gérer de manière indépendante.

Je peux courir OPTIMIZE TABLE avec innodb_file_per_table ?

Supposons que vous deviez ajouter innodb_file_per_table à /etc/my.cnf (my.ini) . Pouvez-vous alors simplement exécuter OPTIMIZE TABLE sur toutes les tables InnoDB ?

Bonne nouvelle : Quand vous exécutez OPTIMIZE TABLE avec innodb_file_per_table activé, cela produira un .ibd pour cette table. Par exemple, si vous avez la table mydb.mytable avec un répertoire de données de /var/lib/mysql il produira ce qui suit :

• /var/lib/mysql/mydb/mytable.frm
• /var/lib/mysql/mydb/mytable.ibd

Le site .ibd contiendra les pages de données et les pages d'index pour cette table. Super.

Mauvaises nouvelles : Tout ce que vous avez fait, c'est d'extraire les pages de données et les pages d'index du site. mydb.mytable de vivre dans ibdata . L'entrée du dictionnaire de données pour chaque table, y compris mydb.mytable reste toujours dans le dictionnaire de données (voir l'article de l'article de la loi sur la protection des données). Représentation graphique de ibdata1 ). VOUS NE POUVEZ PAS SIMPLEMENT SUPPRIMER ibdata1 À CE STADE ! !! Veuillez noter que ibdata1 n'a pas du tout diminué.

Nettoyage de l'infrastructure InnoDB

Pour rétrécir ibdata1 une fois pour toutes, vous devez faire ce qui suit :

1. Dump (par exemple, avec mysqldump ) toutes les bases de données dans un .sql fichier texte ( SQLData.sql est utilisé ci-dessous)

2. Abandonnez toutes les bases de données (à l'exception de mysql et information_schema ) CAVEAT : Par précaution, veuillez exécuter ce script pour vous assurer absolument que vous avez toutes les autorisations d'utilisateur en place :

   mkdir /var/lib/mysql_grants
   cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
   chown -R mysql:mysql /var/lib/mysql_grants

3. Connectez-vous à mysql et exécutez SET GLOBAL innodb_fast_shutdown = 0; (Ceci effacera complètement toutes les modifications transactionnelles restantes de l'application ib_logfile0 et ib_logfile1 )

4. Arrêter MySQL

5. Ajoutez les lignes suivantes à /etc/my.cnf (ou my.ini sur Windows)

   [mysqld]
   innodb_file_per_table
   innodb_flush_method=O_DIRECT
   innodb_log_file_size=1G
   innodb_buffer_pool_size=4G

   (Sidenote : Quel que soit votre jeu pour innodb_buffer_pool_size assurez-vous que innodb_log_file_size est de 25% de innodb_buffer_pool_size .

   Aussi : innodb_flush_method=O_DIRECT n'est pas disponible sous Windows)

6. Supprimer ibdata* et ib_logfile* Si vous le souhaitez, vous pouvez supprimer tous les dossiers de la section /var/lib/mysql sauf /var/lib/mysql/mysql .

7. Démarrez MySQL (Cela recréera ibdata1 [10 Mo par défaut] et ib_logfile0 et ib_logfile1 à 1G chacun).

8. Importation SQLData.sql

Maintenant, ibdata1 s'agrandira toujours mais ne contiendra que les métadonnées des tables car chaque table InnoDB existera en dehors de la section ibdata1 . ibdata1 ne contiendra plus les données InnoDB et les index des autres tables.

Par exemple, supposons que vous ayez une table InnoDB nommée mydb.mytable . Si vous regardez dans /var/lib/mysql/mydb vous verrez deux fichiers représentant la table :

• mytable.frm (En-tête du moteur de stockage)
• mytable.ibd (Données de la table et index)

Avec le innodb_file_per_table option dans /etc/my.cnf vous pouvez exécuter OPTIMIZE TABLE mydb.mytable et le fichier /var/lib/mysql/mydb/mytable.ibd va en fait se réduire.

Je l'ai fait de nombreuses fois au cours de ma carrière de DBA MySQL. En fait, la première fois que j'ai fait cela, j'ai rétréci une 50GB ibdata1 à seulement 500 Mo !

Essayez-le. Si vous avez d'autres questions à ce sujet, n'hésitez pas à les poser. Faites-moi confiance, cela fonctionnera aussi bien à court terme qu'à long terme.

CAVEAT

À l'étape 6, si mysql ne peut pas redémarrer en raison de l'échec de l'opération mysql Si le schéma commence à tomber, reportez-vous à l'étape 2. Vous avez fait la copie physique du mysql schéma. Vous pouvez le restaurer comme suit :

mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_grants/* /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql/mysql

Retournez à l'étape 6 et continuez

MISE À JOUR 2013-06-04 11:13 EDT

En ce qui concerne la fixation innodb_log_file_size à 25 % de innodb_buffer_pool_size à l'étape 5, cette règle de couverture est plutôt de la vieille école.

Retour sur July 03, 2006 Percona a publié un article intéressant Pourquoi choisir une taille de fichier innodb_log_file_size appropriée ? . Plus tard, le Nov 21, 2008 Percona a poursuivi avec un autre article sur comment calculer la taille appropriée sur la base de la charge de travail maximale en conservant une heure de changements. .

J'ai depuis écrit des articles dans le DBA StackExchange sur le calcul de la taille du journal et j'y ai fait référence à ces deux articles de Percona.

• Aug 27, 2012 : Réglage correct d'une table InnoDB de 30 Go sur un serveur avec 48 Go de RAM
• Jan 17, 2013 : MySQL 5.5 - Innodb - innodb_log_file_size supérieure à 4GB combinés ?

Personnellement, je continuerais à appliquer la règle des 25% pour une configuration initiale. Ensuite, comme la charge de travail peut être déterminée plus précisément au fil du temps en production, vous pourriez redimensionner les journaux pendant un cycle de maintenance en quelques minutes seulement.

Answer 2

Le moteur InnoDB ne stocke pas les données supprimées. Au fur et à mesure que vous insérez et supprimez des lignes, de l'espace inutilisé reste alloué dans les fichiers de stockage InnoDB. Au fil du temps, l'espace total ne diminuera pas, mais l'espace "supprimé et libéré" sera automatiquement réutilisé par le serveur de base de données.

Vous pouvez affiner et gérer l'espace utilisé par le moteur en réorganisant manuellement les tables. Pour ce faire, videz les données des tables concernées à l'aide de mysqldump, supprimez les tables, redémarrez le service mysql, puis recréez les tables à partir des fichiers de vidage.

Answer 3

Pour info, après m'être beaucoup creusé la tête, je viens de découvrir que O_DIRECT ne peut pas être utilisé sous Windows.

"Sous Windows, la méthode de vidage est toujours async_unbuffered et ne peut être modifiée"

De : http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_method

Answer 4

Je suis ce guide pour une réinitialisation complète (comme Root) :

mysqldump --all-databases --single-transaction | gzip -c > /tmp/mysql.all.sql.gz
service mysql stop
mv /var/lib/mysql /var/lib/mysql.old; mkdir -m700 /var/lib/mysql; chown mysql:mysql /var/lib/mysql

mysql_install_db                # mysql 5.5
mysqld --initialize-insecure    # mysql 5.7

service mysql start
zcat /tmp/mysql.all.sql.gz | mysql
service mysql restart

Answer 5

Vous pouvez utiliser la requête OPTIMIZE TABLE périodiquement pour optimiser votre stockage et accélérer un peu les requêtes SELECT. Lien vers le manuel MySQL : http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html