Beräkna diskutrymmesanvändning per MySQL DB
On februari 9, 2021 by adminJag använder för närvarande information_schema.TABLES för att beräkna den totala diskutrymmesanvändningen grupperad efter databasnamnet, men den körs fruktansvärt långsamt. På servrar med hundratals databaser kan det ta minuter att beräkna.
Vad är den snabbaste metoden för att beräkna diskutrymmesanvändning per databas? Ska jag bara titta på filsystemet? Finns det en metod för att påskynda informationsschemat?
Svar
Det finns 3 scenarier.
- Om du använder MyISAM är det enklast att bara titta på filsystemet och använda
du -sh /var/lib/mysql/database
. - Om du är med InnoDB med innodb_file_per_table inställd kan du få ett ungefärligt svar med
du -sh
. Det är ungefärligt eftersom det fortfarande finns vissa data lagrade i ibdata1-filen, så du kommer att vara lite på lågsidan. Denna teknik fungerar också med blandade MyISAM / InnoDB (innodb_file_per_table
) databaser. - Om du använder InnoDB utan
innodb_file_per_table set
måste du titta på INFORMATION_SCHEMA.
I något av fallen ovan kan du köra följande fråga för att få den information du letar efter.
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)
Om du har ett mycket stort antal tabeller kan det gå långsamt, som du redan har upptäckt.
Kommentarer
- Jag såg någon annanstans att alternativ 3 inte tar ’ t hänsyn till VARCHAR-storlekar.
Svar
Du kan använda det här kommandot för att få information i GB:
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)
Anpassade svaret från Aaron Brown för att ge storlek i GB. Se Aaron Browns svar för mer information.
ELLER för att inkludera ledigt / återvinningsbart utrymme, använd:
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 | +--------------------+---------------+------------------------------+
Utrymmet kan återvinnas med OPTIMIZE TABLE command för tabellerna InnoDB, MyISAM och ARCHIVE.
Se även Hur får man riktig storlek på MySQL-databas? för mer information.
Svar
För att jag ska kunna se var diskutrymme används (oavsett om det finns i en mysql-tabell eller inte) använder jag mitt pålitliga ”du” -kommando. Här är ett exempel på att jag hittar var allt utrymme äts upp.
$ 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
Du kan se att majoriteten av utrymmet används av den här mappen. / mysql
Den mappen innehåller datatabeller. För att se vilka tabeller som tar allt utrymme kan du fortsätta så här med alternativet ”human” eller ”-h”. Jag gillar att göra diskutrymmehantering på det här sättet eftersom du ibland inte ens kan logga in på mysql eftersom du inte vet lösenordet eller användaren.
$ 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
Du kan se att allt utrymme slås av några tabeller med många GiG-data. Hoppas det hjälper.
Svar
För att få information om namnet på tabellen och antalet poster den har kan nedanstående fråga användas,
SELECT * FROM information_schema.TABLES ;
För att få information om databaser på servrarna med respektive storlek kan nedanstående fråga användas,
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;
Svar
Jag skulle leta efter storleken på filen i din datadictionnaire. Den är omedelbar och korrekt.
Varning : Enligt lagringsmotorn lagras index i huvudfilen eller i en annan fil glöm inte att summera dem om behövs.
Kommentarer
- Ja, men var är det?
Svar
Jag vet att det här är gammalt men någon kanske tycker det är relevant.
I n MySQL använder jag:
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;
Eftersom min DB är InnoDB är detta bara en uppskattning.
Jag jämför denna utdata med:
du -sch /location/of_Mysql/* | sort -hr | head -n20
Hoppas det hjälper dig
Svar
Bäst (efter att ha gjort apt-get install ncdu
):
cd "/var/lib/mysql" && ncdu
för att få den totala storleken på Mysql-databaser i din VPS.
Svar
Förutsatt att din MySQL-värd körs på Linux.
Du kan utföra frågan nedan för att hitta sökvägen där MySQL-data lagras
select @@datadir;
När du vet var data lagras kan du kontrollera diskanvändningen med du
-kommandot.
Till exempel på Ubuntu sudo du -h /var/lib/mysql
.
Du skulle få något liknande detta
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
Källa: Kontrollera lagringsutrymmet som används av MySQL på Linux
Lämna ett svar