Calcolo dellutilizzo dello spazio su disco per database MySQL
Su Febbraio 9, 2021 da adminAttualmente sto utilizzando information_schema.TABLES per calcolare lutilizzo totale dello spazio su disco raggruppato per nome del database, ma funziona in modo terribile lentamente. Su server con centinaia di database, il calcolo può richiedere minuti.
Qual è il metodo più rapido per calcolare lutilizzo dello spazio su disco in base al database? Dovrei solo guardare il filesystem? Esiste un metodo per accelerare information_schema?
Answer
Ci sono 3 scenari.
- Se stai usando MyISAM, è più semplice guardare il filesystem e usare
du -sh /var/lib/mysql/database
. - Se lo sei utilizzando InnoDB con innodb_file_per_table impostato, puoi ottenere una risposta approssimativa utilizzando
du -sh
. È approssimativo perché ci sono ancora alcuni dati memorizzati nel file ibdata1, quindi sarai un po basso. Questa tecnica funziona anche con database misti MyISAM / InnoDB (innodb_file_per_table
). - Se utilizzi InnoDB senza
innodb_file_per_table set
, quindi dovrai esaminare INFORMATION_SCHEMA.
In uno qualsiasi dei casi precedenti, puoi eseguire la seguente query per ottenere le informazioni che stai cercando.
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)
Se hai un numero molto elevato di tabelle, può essere lento, come hai già scoperto.
Commenti
- Ho visto da qualche altra parte che lopzione 3 ‘ non tiene conto delle dimensioni VARCHAR.
Risposta
Puoi utilizzare questo comando per ottenere informazioni in 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)
Adattato la risposta di Aaron Brown per fornire le dimensioni in GB. Vedi Risposta di Aaron Brown per maggiori dettagli.
OPPURE per includere spazio libero / recuperabile, utilizza:
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 | +--------------------+---------------+------------------------------+
Lo spazio può essere recuperato utilizzando il comando OPTIMIZE TABLE per le tabelle InnoDB, MyISAM e ARCHIVE.
Vedi anche Come ottenere la dimensione reale del database MySQL? per maggiori dettagli.
Risposta
Per poter vedere dove viene utilizzato lo spazio su disco (indipendentemente dal fatto che “sia in una tabella mysql o meno), utilizzo il mio fidato comando” du “. Ecco un esempio di come ho scoperto da dove viene consumato tutto lo spazio.
$ 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
Puoi vedere che la maggior parte dello spazio è utilizzata da questa cartella. / mysql
Quella cartella contiene le tabelle di dati. Per vedere quali tabelle stanno occupando tutto lo spazio puoi procedere in questo modo usando lopzione “human” o “-h”. Mi piace fare gestione dello spazio su disco in questo modo perché a volte non puoi nemmeno accedere a mysql perché non conosci la password o lutente.
$ 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
Puoi vedere che tutto lo spazio è occupato da poche tabelle che contengono molti GiG “di dati. Spero che questo aiuti.
Risposta
Per ottenere informazioni sul nome della tabella e sul numero di record che ha, è possibile utilizzare la query seguente,
SELECT * FROM information_schema.TABLES ;
Per ottenere informazioni sui database sui server con le rispettive dimensioni, è possibile utilizzare la query seguente,
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;
Risposta
Cercherò la dimensione del file nel dizionario dei dati. È istantanea e precisa.
Avviso : secondo il motore di archiviazione, gli indici sono memorizzati nel file principale o in un altro file non dimenticare di sommarli se necessario.
Commenti
- Sì, ma dovè?
Risposta
So che è vecchio ma qualcuno potrebbe trovarlo pertinente.
I n MySQL che utilizzo:
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;
Poiché il mio DB è InnoDB, questa è solo una stima.
Confronto questo output con:
du -sch /location/of_Mysql/* | sort -hr | head -n20
Spero che questo ti aiuti
Rispondi
Migliore (dopo aver eseguito apt-get install ncdu
):
cd "/var/lib/mysql" && ncdu
per ottenere tutte le dimensioni totali dei database Mysql nel tuo VPS.
Risposta
Supponendo che il tuo host MySQL sia in esecuzione su Linux.
Puoi eseguire la query seguente per trova il percorso in cui sono archiviati i dati MySQL
select @@datadir;
Una volta che sai dove si trovano i dati è memorizzato, puoi controllare lutilizzo del disco con il comando du
.
Ad esempio su Ubuntu sudo du -h /var/lib/mysql
.
Otterrai qualcosa di simile
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
Fonte: Controlla lo spazio di archiviazione utilizzato da MySQL su Linux
Lascia un commento