Si je suis une table innodb que cette requête exécute beaucoup (une fois par seconde pendant un certain temps avec une petite quantité de données ajoutée) et que innodb_files_per_table = 0 est-ce que cela peut causer un problème de stockage? Le blob lui-même pourrait faire 2-3 Mo mais j'ai perdu 30 Go d'utilisation de données et je me demande si ce code est à blâmer d'une manière ou d'une autre ou si je devrais chercher ailleurs.
SQL
mysql> show create table phppos_app_files; +------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | phppos_app_files | CREATE TABLE `phppos_app_files` ( `file_id` int(10) NOT NULL AUTO_INCREMENT, `file_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `file_data` longblob NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `expires` timestamp NULL DEFAULT NULL, PRIMARY KEY (`file_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7577 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
3 Réponses :
vous pouvez utiliser la requête ci-dessous pour vérifier la taille de vos tables. De cette façon, vous pouvez voir quelle table gaspille votre stockage.
SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC
en effet, la quantité totale d'espace utilisée reste la même.
Si vous utilisez innodb_files_per_table = 0 vous n'en aurez qu'un tablespace et comme vos données changent rapidement, il est possible que votre tablespace soit fragmenté dans le temps.
Si vous décidez à l'avenir de supprimer ou de tronquer votre table, la taille de votre tablespace restera la même (ne rétrécit pas) par conséquent, vous ne pouvez pas récupérer l'espace de l'opération de dépôt.
Cela montre ce à quoi je m'attendrais en termes de tailles de données; mais je suppose que je suis préoccupé si la requête ci-dessus peut remplir de l'espace libre puisque j'utilise innodb_file_per_table de 0
27930.00MB est mon espace libre trouvé à partir de mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT (ROUND (data_length / (1024 * 1024), 2), 'MB') DATA_Space, -> CONCAT (ROUND (data_free / (1024 * 1024)) , 2), 'MB') FREE_Space, create_time, table_rows, engine, create_options -> from information_schema.TABLES -> where TABLE_SCHEMA NOT IN ('information_schema', 'mysql') et Data_free> 0 -> ORDER BY FLOOR (FREE_Space) DESC, FLOOR (DATA_Space) DESC, TABLE_SCHEMA, TABLE_NAME ->;
Je me demande simplement si cette requête peut contribuer à un excès d'espace libre ou si je devrais chercher ailleurs
Donc, si je mets à jour beaucoup, cela peut entraîner une forte fragmentation et des problèmes de stockage possibles? Si j'active le fichier par table, est-ce que je n'aurais pas ce problème?
si vous activez, vous voulez avoir ce problème sur vos autres tables. Si vous le laissez désactivé, vous stockerez toutes vos tables dans un espace de table et lorsque votre espace de table sera fragmenté, cela affectera toutes vos tables.
Je ne comprends pas la dernière déclaration; y a-t-il une faute de frappe?
si vous activez, vous n'aurez pas de problème de fragmentation sur d'autres tables. Si vous le laissez désactivé, vous stockerez toutes vos tables dans un espace de table et lorsque votre espace de table sera fragmenté, cela affectera toutes vos tables.
Donc, votre recommandation serait de ne pas mettre à jour beaucoup d'objets blob ou d'utiliser un fichier par table de 1?
Comment mysql a-t-il décidé quand commencer à manger de l'espace? Il semblait que cela se soit produit des heures après les mises à jour des blob
En ce qui concerne le stockage et l'accès aux BLOB, les mises à jour fréquentes de petits blocs de données peuvent entraîner une perte d'espace en raison de la fragmentation. Il y a des optimisations possibles à considérer:
innodb_file_per_table désactivée, cela ne réduira pas le tablespace. Cependant, il récupérera l'espace perdu en raison de la fragmentation et le marquera comme disponible dans le tablespace: il réutilisera donc toutes les étendues vides avant de faire croître à nouveau le tablespace. Cela pourrait-il expliquer 30 Go d'espace libre perdu?
@ChrisMuench: la fragmentation expliquerait cela. Vous voulez maintenant OPTIMISER TABLE et vérifier sa taille par la suite ...
L'optimisation de la table a-t-elle un effet sur la taille de stockage avec le fichier Innodb par table 0. J'ai de nombreuses bases de données
Oui, l'optimisation de la table fonctionnera même si innodb_file_per_table est défini sur 0
Va-t-il récupérer de l'espace?
@ChrisMuench ... oui c'est ce que fait OPTIMIZE TABLE ... Avez-vous lu le document que j'ai lié? Il explique le but de cette commande
OPTIMIZE TABLES défragmente, mais il ne peut pas réduire un tablespace, sauf si vous utilisez innodb_file_per_table = 1 . Un tablespace multi-table ne diminuera jamais. Il ne peut marquer que les étendues vides comme prêtes à être réutilisées pour les données futures. Ainsi, il réutilisera toutes les étendues vides avant de développer à nouveau le tablespace.
@BillKarwin merci d'avoir signalé cela ... j'ai mis à jour ma réponse
@ChrisMuench: j'ai mis à jour ma réponse avec plus de détails sur ANALYZE TABLE
@GMB - Je ne vois aucune mention de ANALYZE .
@RickJames: désolé, je voulais dire OPTIMISER
Si vous effectuez des mises à jour fréquentes de gros objets blob, cela pourrait en effet utiliser temporairement beaucoup de stockage et laisser votre tablespace fragmenté.
Une mise à jour copie les pages de données d'origine dans le "journal d'annulation" qui n'est qu'une zone de pages dans le tablespace. InnoDB conserve ces pages copiées au cas où vous annuleriez votre transaction et que vous deviez restaurer les données d'origine. Après avoir validé votre mise à jour, la copie dans le journal d'annulation n'est plus nécessaire et InnoDB nettoiera progressivement. Mais si vos mises à jour sont fréquentes, comme vous le dites, vous pourriez accumuler des déchets dans le journal des annulations plus rapidement qu'InnoDB ne peut les nettoyer.
Dans tous les cas, étendre le stockage pour plus de contenu du journal d'annulation augmentera l'espace de table , et même après le nettoyage d'InnoDB, l'espace de table ne sera pas réduit. Il restera toujours la taille de sa limite supérieure.
Pour atténuer cela, MySQL 5.6 a introduit une option permettant au journal d'annulation d'être stocké en dehors de l'espace de table du système, et MySQL 5.7 a introduit une fonctionnalité pour tronquer les journaux d'annulation après leur nettoyage. Lisez https://dev.mysql.com/doc /refman/5.7/en/innodb-undo-tablespaces.html pour plus d'informations sur ces fonctionnalités.
Si vous êtes intéressé par la disposition interne des fichiers InnoDB, je vous recommande tester les outils open-source Jeremy Cole a écrit: https://github.com/jeremycole/innodb_ruby/wiki
Vous pouvez vider divers résumés de ce qui se trouve dans votre tablespace ibdata1. Une grande partie du fichier peut être inutilisée, mais prend encore de la place.
Voici un exemple de mon sandbox MySQL local. J'ai juste une table avec innondb_file_per_table = 0 (toutes les autres tables sont en dehors du tablespace central). J'ai exécuté cette commande:
innodb_space -s /usr/local/var/mysql/ibdata1 -T mydatabase/mytable space-extents-illustrate
Voici l'illustration qu'elle a créée. La légende (non incluse) montrait que les données et les index de ma table se situaient dans les pages comprises entre 3008 et 3392. Les autres éléments étaient principalement des structures de données système InnoDB (noir), des tables système, des tampons d'insertion (jaune foncé) et inoccupés pages (gris).
sans en savoir plus sur ce que vous stockez dans file_data, nous ne pouvons pas identifier le problème.
c'est 2-3 Mo de données de journal simples
pouvez-vous vérifier ma réponse avant d'aller plus loin, car nous devons d'abord nous assurer que c'est le tableau qui pose problème
Combien de fois avez-vous ajouté une petite chaîne à cet objet blob avant la disparition de 27930,00 Mo? En d'autres termes, je cherche un cas de test reproductible .
C'est vraiment difficile à dire. J'ai de nombreuses bases de données et je dirais que 4 ou 5 d'entre elles ont eu une certaine activité avec ce type de journalisation. Mais ensuite, j'ai pensé que les sessions sont également stockées sous forme d'objet et sont mises à jour fréquemment et n'ont jamais eu de problème, lorsque ce problème de stockage s'est produit, c'était le principal changement de backend effectué récemment. Je peux me tromper mais je n'ai rien d'autre à signaler. Y a-t-il d'autres raisons pour lesquelles MySQL ne réclame que beaucoup d'espace? Y a-t-il des commandes que je peux exécuter pour vous montrer plus de données?
Le seul journal que j'ai vérifié avec 3 Mo après 10 minutes pour le créer
A noter également que ma réplique interrégionale n'a jamais eu de baisse d'espace de stockage
Pourriez-vous publier les résultats de SELECT file_id, file_data FROM phppos_app_files WHERE file_id = 10; afin que nous puissions voir votre contenu actuel pour celui-ci file_id? Merci
dropbox.com/s/h5eaco230zcrop0/file_id_10.txt?dl=0 < / a>
@ChrisMuench - Quelle est la valeur de
binlog_format?À 3 Mo / s, 3 heures grignotent 30 Go. Je ne sais toujours pas combien de données (3 Mo?) Sont ajoutées à quelle fréquence (1 / sec? 1/10 minutes?) À combien de tables (1? 1000?).
3 Mo de fichier total en 3 heures (j'ai mal lu le journal). Nous l'ajoutons ligne par ligne, par exemple 26/12/2018 10:40:02: ******* Exception: 'Erreur: []' comme indiqué dans le fichier ci-dessus
Pourriez-vous publier les résultats de SELECT * FROM phppos_app_files WHERE file_id = 7570; afin que nous puissions voir le contenu de votre ligne actuelle pour ce file_id? Merci
dropbox.com/s/h5eaco230zcrop0/file_id_10.txt?dl=0 < / a>
Le lien ci-dessus est le journal binaire pendant 3 heures. J'aimerais les données d'une ligne du tableau, s'il vous plaît.
C'est une ligne; ce n'est qu'une grosse corde. C'est le blob file_data pour cette ligne
@ChrisMuench Comme il s'agit des données pour un file_id et que l'objet blob est passé à 3 Mo en 3 heures, en moins de 2 heures, vous dépasserez le maximum de LONGBLOB et il n'y a aucune disposition dans la mise à jour pour traiter l'erreur attendue. (LONGBLOB max est de 4 Mo). Cette méthode de journalisation en direct pour vos 7000+ ID de fichier sera très consommatrice pour vos besoins de stockage et, telle quelle, utilisera ~ 8000 * 4 Mo = 32 Go d'espace et sera obsolète en quelques heures par file_id actif. Un autre effet secondaire négatif est la surcharge qu'INNODB doit gérer pour stocker ces données «hors ligne».
Le long blob peut contenir 4 Go; il ne semble pas possible qu’un fichier de 3 Mo entraîne une consommation d’espace de 32 Go