Calculando o uso de espaço em disco por banco de dados MySQL
On Fevereiro 9, 2021 by adminAtualmente, estou usando information_schema.TABLES para calcular o uso total de espaço em disco agrupado pelo nome do banco de dados, mas ele está funcionando mal devagar. Em servidores com centenas de bancos de dados, pode levar minutos para calcular.
Qual é o método mais rápido de calcular o uso de espaço em disco por banco de dados? Devo apenas olhar para o sistema de arquivos? Existe um método para acelerar information_schema?
Resposta
Existem 3 cenários.
- Se você estiver usando MyISAM, é mais fácil apenas olhar para o sistema de arquivos e usar
du -sh /var/lib/mysql/database
. - Se estiver usando InnoDB com innodb_file_per_table definido, então você pode obter uma resposta aproximada usando
du -sh
. É aproximado porque ainda há alguns dados armazenados no arquivo ibdata1, então você ficará um pouco para baixo. Esta técnica também funciona com bancos de dados MyISAM / InnoDB (innodb_file_per_table
) mistos. - Se você estiver usando InnoDB sem
innodb_file_per_table set
, então você precisará olhar em INFORMATION_SCHEMA.
Em qualquer um dos casos acima, você pode executar a seguinte consulta para obter as informações que está procurando.
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 você tiver um grande número de tabelas, pode ser lento, como você já descobriu.
Comentários
- Vi em outro lugar que a opção 3 não ‘ leva em consideração os tamanhos VARCHAR.
Resposta
Você pode usar este comando para obter informações em 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)
Adaptou a resposta de Aaron Brown para fornecer o tamanho em GB. Veja a resposta de Aaron Brown para mais detalhes.
OU para incluir espaço livre / recuperável, use:
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 | +--------------------+---------------+------------------------------+
O espaço pode ser recuperado usando o comando OPTIMIZE TABLE para tabelas InnoDB, MyISAM e ARCHIVE.
Consulte também Como obter o tamanho real do banco de dados MySQL? para obter mais detalhes.
Resposta
Para que eu possa ver onde o espaço em disco está sendo usado (independentemente se está em uma tabela mysql ou não), eu uso meu confiável comando “du”. Aqui está um exemplo de como eu descobri de onde todo o espaço está sendo consumido.
$ 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
Você pode ver que a maior parte do espaço está sendo usado por esta pasta. / mysql
Essa pasta contém tabelas de dados. Para ver quais tabelas estão ocupando todo o espaço, você pode proceder assim usando a opção “humana” ou “-h”. Eu gosto de fazer gerenciamento de espaço em disco desta forma porque às vezes você não consegue nem mesmo entrar no mysql porque você não sabe a senha ou o usuário.
$ 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
Você pode ver que todo o espaço está sendo ocupado por algumas tabelas contendo muitos GiG “s de dados. Espero que isso ajude.
Resposta
Para obter informações sobre o nome da tabela e o número de registros que ela possui, a consulta abaixo pode ser usada,
SELECT * FROM information_schema.TABLES ;
Para obter informações sobre os bancos de dados nos servidores com seus respectivos tamanhos, pode-se usar a consulta abaixo,
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;
Resposta
Eu procuraria o tamanho do arquivo no seu dicionário de dados. É instantâneo e preciso.
Aviso : De acordo com o mecanismo de armazenamento, os índices são armazenados no arquivo principal ou em outro arquivo, não se esqueça de resumi-los se necessário.
Comentários
- Sim, mas onde fica isso?
Resposta
Eu sei que isso é antigo, mas alguém pode achar isso relevante.
Eu n MySQL que eu uso:
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;
Como meu banco de dados é InnoDB, isso é apenas uma estimativa.
Eu comparo esta saída com:
du -sch /location/of_Mysql/* | sort -hr | head -n20
Espero que isso ajude você
Resposta
Melhor (depois de fazer apt-get install ncdu
):
cd "/var/lib/mysql" && ncdu
para obter todo o tamanho total dos bancos de dados Mysql em seu VPS.
Resposta
Supondo que seu host MySQL esteja sendo executado no Linux.
Você pode executar a consulta abaixo para encontre o caminho onde os dados do MySQL estão armazenados
select @@datadir;
Assim que você souber onde estão os dados está armazenado, você pode verificar o uso do disco com o comando du
.
Por exemplo, no Ubuntu sudo du -h /var/lib/mysql
.
Você obteria algo assim
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
Deixe uma resposta