Calcul de lutilisation de lespace disque par base de données MySQL
On février 9, 2021 by adminJutilise actuellement information_schema.TABLES pour calculer lutilisation totale de lespace disque groupé par le nom de la base de données, mais il fonctionne terriblement tout doucement. Sur les serveurs avec des centaines de bases de données, le calcul peut prendre quelques minutes.
Quelle est la méthode la plus rapide pour calculer lutilisation de lespace disque par base de données? Dois-je simplement regarder le système de fichiers? Existe-t-il une méthode pour accélérer information_schema?
Réponse
Il existe 3 scénarios.
- Si vous utilisez MyISAM, il est plus simple de simplement regarder le système de fichiers et dutiliser
du -sh /var/lib/mysql/database
. - Si vous êtes en utilisant InnoDB avec innodb_file_per_table défini, vous pouvez alors obtenir une réponse approximative en utilisant
du -sh
. Cest approximatif car il y a encore des données stockées dans le fichier ibdata1, donc vous serez un peu sur le côté bas. Cette technique fonctionne également avec les bases de données mixtes MyISAM / InnoDB (innodb_file_per_table
). - Si vous utilisez InnoDB sans
innodb_file_per_table set
, alors vous devrez regarder INFORMATION_SCHEMA.
Dans tous les cas ci-dessus, vous pouvez exécuter la requête suivante pour obtenir les informations que vous recherchez.
mysql> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1; +--------------------+-----------------+ | table_schema | MB | +--------------------+-----------------+ | prod | 298025.72448921 | | information_schema | 0.00781248 | | maatkit | 70.77330779 | | mysql | 0.66873168 | | test | 4752.31449127 | +--------------------+-----------------+ 5 rows in set (0.01 sec)
Si vous avez un très grand nombre de tables, cela peut être lent, comme vous lavez déjà découvert.
Commentaires
- Jai vu ailleurs que loption 3 ne ‘ ne prend pas en compte les tailles VARCHAR.
Réponse
Vous pouvez utiliser cette commande pour obtenir des informations en Go:
mysql> select table_schema "DB name (table_schema)", sum((data_length+index_length)/1024/1024/1024) AS "DB size in GB" from information_schema.tables group by table_schema; +-------------------------------------+-----------------+ | table_schema DB name (table_schema) | DB size in GB | +-------------------------------------+-----------------+ | prod | 29.72448921 | | information_schema | 0.00781248 | | miscDB | 0.77330779 | | mysql | 0.66873168 | | test | 47.31449127 | +-------------------------------------+-----------------+ 5 rows in set (0.01 sec)
A adapté la réponse de Aaron Brown pour indiquer la taille en Go. Voir la réponse dAaron Brown pour plus de détails.
OU pour inclure de lespace libre / récupérable, utilisez:
mysql> SELECT table_schema "database name", sum( data_length + index_length ) / 1024 / 1024 "database size in MB", sum( data_free )/ 1024 / 1024 "free reclaimable space in MB" FROM information_schema.TABLES GROUP BY table_schema; +--------------------+---------------+------------------------------+ | DB name | DB size in GB | free/reclaimable space in GB | +--------------------+---------------+------------------------------+ | prod | 1.26 | 0.03 | | information_schema | 38.77 | 3.75 | | miscDB | 0.00 | 0.00 | | mysql | 0.00 | 0.00 | | test | 0.00 | 0.00 | +--------------------+---------------+------------------------------+
Lespace peut être récupéré à laide de la commande OPTIMIZE TABLE pour les tables InnoDB, MyISAM et ARCHIVE.
Voir aussi Comment obtenir la taille réelle de la base de données MySQL? pour plus de détails.
Réponse
Pour que je puisse voir où lespace disque est utilisé (quil soit dans une table mysql ou non), jutilise ma fidèle commande « du ». Voici un exemple de ma recherche d’où tout l’espace est mangé.
$ sudo du -cks /* | sort -rn 954881224 total 945218092 /mysql 5299904 /usr 1781376 /opt 1166488 /var 671628 /home 343332 /run 213400 /root 93476 /lib 30784 /boot 20652 /etc 15940 /bin 13708 /sbin 12388 /tmp 24 /mnt 16 /lost+found 4 /srv 4 /snap 4 /media 4 /lib64 0 /vmlinuz 0 /sys 0 /proc 0 /initrd.img 0 /dev
Vous pouvez voir que la majorité de l’espace est utilisée par ce dossier. / mysql
Ce dossier contient des tables de données. Pour voir quelles tables occupent tout lespace, vous pouvez procéder comme ceci en utilisant loption « human » ou « -h ». Jaime faire gestion de lespace disque de cette façon parce que parfois vous ne pouvez même pas vous connecter à mysql parce que vous ne connaissez pas le mot de passe ou lutilisateur.
$ sudo du -chs /mysql/* 2.3M /mysql/blacklist 18M /mysql/clientservices 2.5G /mysql/data 4.0K /mysql/doubleverify 137G /mysql/ias 4.0K /mysql/IAS 2.2G /mysql/innodb 16K /mysql/lost+found 4.0K /mysql/ml_centroids 16G /mysql/moat 4.0K /mysql/test 4.0K /mysql/tmp 4.0K /mysql/var 282G /mysql/verticaAdFees 4.0K /mysql/verticaViewability 247G /mysql/Whiteops 217G /mysql/Whiteops_TLX 902G total
Vous pouvez voir que tout lespace est monopolisé par quelques tables contenant de nombreuses GiG de données. Jespère que cela vous aidera.
Réponse
Pour obtenir des informations sur le nom de la table et le nombre denregistrements quelle contient, la requête ci-dessous peut être utilisée,
SELECT * FROM information_schema.TABLES ;
Pour obtenir des informations sur les bases de données sur les serveurs avec leur taille respective, la requête ci-dessous peut être utilisée,
SELECT TABLE_SCHEMA AS `Database`, SUM((data_length + index_length) / (1024 * 1024)) AS `Database_Size` FROM information_schema.TABLES GROUP BY table_schema ORDER BY `Database_Size` DESC;
Réponse
Je chercherais la taille du fichier dans votre dictionnaire de données. Il est instantané et précis.
Attention : Selon le moteur de stockage, les index sont stockés dans le fichier principal ou dans un autre fichier, noubliez pas de les résumer si nécessaire.
Commentaires
- Ouais, mais où est-ce?
Réponse
Je sais que cest vieux mais quelquun peut trouver cela pertinent.
I n MySQL que jutilise:
SELECT concat(table_schema) "Database Name", concat(round(SUM(data_length/power(1024,3)),2),"G") DATA, concat(round(SUM(index_length/power(1024,3)),2),"G") "INDEX", concat(round(SUM(data_free/power(1024,3)),2),"G") "DATA FREE", concat(round(sum(data_free)/(SUM(data_length+index_length))*100,2)) "% FRAGMENTED", concat(round(SUM(data_length+index_length)/power(1024,3),2),"G") TOTAL FROM information_schema.TABLES WHERE table_schema NOT IN ("mysql","information_schema","performance_schema") GROUP BY table_schema;
Puisque ma base de données est InnoDB, il ne sagit que dune estimation.
Je compare cette sortie à:
du -sch /location/of_Mysql/* | sort -hr | head -n20
Jespère que cela vous aidera
Réponse
Meilleur (après avoir fait apt-get install ncdu
):
cd "/var/lib/mysql" && ncdu
pour obtenir toute la taille totale des bases de données Mysql dans votre VPS.
Réponse
En supposant que votre hôte MySQL fonctionne sous Linux.
Vous pouvez exécuter la requête ci-dessous pour trouver le chemin où les données MySQL sont stockées
select @@datadir;
Une fois que vous savez où les données est stocké, vous pouvez vérifier lutilisation du disque avec la commande du
.
Par exemple sur Ubuntu sudo du -h /var/lib/mysql
.
Vous obtiendrez quelque chose comme ça
980K /var/lib/mysql/db_one 1.1M /var/lib/mysql/mysql 8.0K /var/lib/mysql/someother_db 1.2M /var/lib/mysql/another_db 212K /var/lib/mysql/performance_schema 1.7G /var/lib/mysql
Laisser un commentaire