Obliczanie wykorzystania miejsca na dysku według bazy danych MySQL
On 9 lutego, 2021 by adminObecnie używam information_schema.TABLES do obliczenia całkowitego wykorzystania miejsca na dysku pogrupowanego według nazwy bazy danych, ale działa strasznie powoli. Na serwerach z setkami baz danych obliczenie może zająć kilka minut.
Jaka jest najszybsza metoda obliczenia wykorzystania miejsca na dysku przez bazę danych? Czy powinienem po prostu spojrzeć na system plików? Czy istnieje metoda przyspieszenia schematu informacyjnego?
Odpowiedź
Istnieją 3 scenariusze.
- Jeśli używasz MyISAM, najłatwiej jest po prostu spojrzeć na system plików i użyć
du -sh /var/lib/mysql/database
. - Jeśli jesteś używając InnoDB z ustawionym innodb_file_per_table , możesz uzyskać przybliżoną odpowiedź za pomocą
du -sh
. Jest to przybliżone, ponieważ nadal istnieją pewne dane przechowywane w pliku ibdata1, więc będziesz trochę po niskiej stronie. Ta technika działa również z mieszanymi bazami danych MyISAM / InnoDB (innodb_file_per_table
). - Jeśli używasz InnoDB bez
innodb_file_per_table set
, musisz spojrzeć na INFORMACJE_SCHEMA.
W każdym z powyższych przypadków możesz uruchomić następujące zapytanie, aby uzyskać informacje, których szukasz.
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)
Jeśli masz bardzo dużą liczbę tabel, może to działać wolno, jak już odkryłeś.
Komentarze
- Widziałem gdzieś indziej, że opcja 3 nie ' nie uwzględnia rozmiarów VARCHAR.
Odpowiedź
Możesz użyć tego polecenia, aby uzyskać informacje w 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)
Dostosowano odpowiedź z Aarona Browna , aby podać rozmiar w GB. Zobacz odpowiedź Aarona Browna , aby uzyskać więcej informacji.
LUB aby uwzględnić wolne / odzyskane miejsce, użyj:
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 | +--------------------+---------------+------------------------------+
Miejsce można odzyskać za pomocą polecenia OPTIMIZE TABLE dla tabel InnoDB, MyISAM i ARCHIVE.
Zobacz także Jak uzyskać prawdziwy rozmiar bazy danych MySQL? , aby uzyskać więcej informacji.
Odpowiedź
Aby zobaczyć, gdzie miejsce na dysku jest zajęte (niezależnie od tego, czy znajduje się ono w tabeli mysql, czy nie), używam mojego zaufanego polecenia „du”. Oto przykład, jak znalazłem, skąd jest zajęta cała przestrzeń.
$ 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
Możesz zobaczyć, że większość miejsca jest zajęta przez ten folder. / mysql
Ten folder zawiera tabele danych. Aby zobaczyć, które tabele zajmują całe miejsce, możesz postępować w ten sposób, używając opcji „human” lub „-h”. Lubię to robić zarządzanie przestrzenią dyskową w ten sposób, ponieważ czasami nie możesz nawet zalogować się do mysql, ponieważ nie znasz hasła ani użytkownika.
$ 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
Widać, że cała przestrzeń jest zajęta przez kilka tabel zawierających wiele danych GiG. Mam nadzieję, że to pomoże.
Odpowiedź
Aby uzyskać informacje o nazwie tabeli i liczbie posiadanych rekordów, można użyć poniższego zapytania:
SELECT * FROM information_schema.TABLES ;
Aby uzyskać informacje o bazach danych na serwerach o odpowiednim rozmiarze, można użyć poniższego zapytania:
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;
Odpowiedź
Szukałbym rozmiaru pliku w Twoim słowniku danych. Jest natychmiastowy i dokładny.
Ostrzeżenie : Zgodnie z mechanizmem przechowywania indeksy są przechowywane w pliku głównym lub w innym pliku, nie zapomnij ich podsumować, jeśli potrzebne.
Komentarze
- Tak, ale gdzie to jest?
Odpowiedź
Wiem, że to jest stare, ale ktoś może uznać to za istotne.
Ja n MySQL, którego używam:
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;
Ponieważ moja baza danych to InnoDB, to jest tylko szacunek.
Porównuję to wyjście z:
du -sch /location/of_Mysql/* | sort -hr | head -n20
Mam nadzieję, że to ci pomoże
Odpowiedź
Najlepsze (po wykonaniu apt-get install ncdu
):
cd "/var/lib/mysql" && ncdu
, aby uzyskać całkowity rozmiar baz danych MySQL w Twoim VPS.
Odpowiedź
Zakładając, że Twój host MySQL działa w systemie Linux.
Możesz wykonać poniższe zapytanie, aby znajdź ścieżkę do przechowywania danych MySQL
select @@datadir;
Gdy już wiesz, gdzie dane jest przechowywany, możesz sprawdzić użycie dysku za pomocą polecenia du
.
Na przykład na Ubuntu sudo du -h /var/lib/mysql
.
Otrzymałbyś coś takiego
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
Dodaj komentarz