Schijfruimtegebruik berekenen per MySQL DB
Geplaatst op februari 9, 2021 door adminIk gebruik momenteel information_schema.TABLES om het totale schijfruimtegebruik te berekenen, gegroepeerd op databasenaam, maar het draait verschrikkelijk langzaam. Op servers met honderden databases kan het minuten duren om te berekenen.
Wat is de snelste methode om het schijfruimtegebruik per database te berekenen? Moet ik gewoon naar het bestandssysteem kijken? Is er een methode om het information_schema te versnellen?
Answer
Er zijn 3 scenarios.
- Als je MyISAM gebruikt, is het het gemakkelijkst om gewoon naar het bestandssysteem te kijken en
du -sh /var/lib/mysql/database
te gebruiken. - Als je dat bent als je InnoDB gebruikt met innodb_file_per_table ingesteld, kun je een geschat antwoord krijgen met
du -sh
. Het is bij benadering omdat er nog wat gegevens zijn opgeslagen in het ibdata1-bestand, dus je zult een beetje aan de lage kant zijn. Deze techniek werkt ook met gemengde MyISAM / InnoDB (innodb_file_per_table
) databases. - Als u InnoDB gebruikt zonder
innodb_file_per_table set
, dan moet u naar INFORMATION_SCHEMA kijken.
In elk van de bovenstaande gevallen kunt u de volgende zoekopdracht uitvoeren om de informatie te krijgen waarnaar u op zoek bent.
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)
Als je een zeer groot aantal tabellen hebt, kan het traag zijn, zoals je al hebt ontdekt.
Opmerkingen
- Ik zag ergens anders dat optie 3 geen ‘ geen rekening houdt met VARCHAR-formaten.
Answer
U kunt dit commando gebruiken om informatie in GB op te halen:
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)
Het antwoord van Aaron Brown aangepast om de grootte in GB op te geven. Zie Aaron Brown “s antwoord voor meer details.
OF om gratis / terug te vorderen ruimte op te nemen, gebruik:
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 | +--------------------+---------------+------------------------------+
Ruimte kan worden teruggevorderd met het OPTIMALISEREN TABEL commando voor InnoDB, MyISAM, en ARCHIVE tabellen.
Zie ook Hoe de ware grootte van MySQL-database te krijgen? voor meer details.
Antwoord
Om mij te laten zien waar de schijfruimte wordt gebruikt (ongeacht of het zich in een mysql-tabel bevindt of niet), gebruik ik mijn vertrouwde “du” -commando. Hier is een voorbeeld van hoe ik ontdek waar alle ruimte wordt opgegeten.
$ 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
Je kunt zien dat het grootste deel van de ruimte wordt gebruikt door deze map. / mysql
Die map bevat gegevenstabellen. Om te zien welke tabellen alle ruimte innemen, kunt u op deze manier te werk gaan met de optie “menselijk” of “-h”. Ik vind het leuk om te doen schijfruimtebeheer op deze manier omdat je soms niet eens kunt inloggen op mysql omdat je het wachtwoord of de gebruiker niet weet.
$ 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
Je kunt zien dat alle ruimte wordt ingenomen door een paar tabellen met veel GiGs aan gegevens. Ik hoop dat dit helpt.
Answer
Om informatie te krijgen over de naam van de tabel en het aantal records dat deze heeft, kan de onderstaande zoekopdracht worden gebruikt,
SELECT * FROM information_schema.TABLES ;
Om informatie te krijgen over databases op de servers met hun respectievelijke grootte, kan de onderstaande zoekopdracht worden gebruikt,
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;
Answer
Ik zou zoeken naar de grootte van het bestand in uw gegevensdictionnary. Het is onmiddellijk en nauwkeurig.
Waarschuwing : volgens de opslag-engine worden indexen opgeslagen in het hoofdbestand of in een ander bestand, vergeet ze niet op te sommen als nodig.
Reacties
- Ja, maar waar is dat?
Antwoord
Ik weet dat dit oud is, maar iemand kan dit relevant vinden.
I n MySQL die ik gebruik:
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;
Aangezien mijn database InnoDB is, is dit slechts een schatting.
Ik vergelijk deze uitvoer met:
du -sch /location/of_Mysql/* | sort -hr | head -n20
Ik hoop dat dit je helpt
Antwoord
Beste (na het doen van apt-get install ncdu
):
cd "/var/lib/mysql" && ncdu
om de volledige grootte van Mysql-databases in je VPS te krijgen.
Answer
Ervan uitgaande dat uw MySQL-host op Linux draait.
U kunt de onderstaande vraag uitvoeren om vind het pad waar MySQL-gegevens zijn opgeslagen
select @@datadir;
Zodra u weet waar de gegevens is opgeslagen, kunt u het schijfgebruik controleren met het du
commando.
Bijvoorbeeld op Ubuntu sudo du -h /var/lib/mysql
.
Je zou zoiets krijgen
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
Geef een reactie