logo标签列表

计算每个MySQL数据库的磁盘空间使用量

mysql
我目前正在使用information_schema.TABLES来计算按数据库名称分组的总磁盘空间使用情况，但速度非常慢。在有数百个数据库的服务器上，计算可能需要几分钟。 有什么最快的方法可以计算每个数据库的磁盘空间使用情况吗？我应该只看文件系统吗？有没有加快information_schema速度的方法？
8个回答

有3种情况。

  1. 如果您正在使用MyISAM，最简单的方法是查看文件系统并使用du -sh /var/lib/mysql/database。
  2. 如果您正在使用启用了innodb_file_per_table的InnoDB，则可以使用du -sh来获得一个近似的答案。这是近似值，因为仍然有一些数据存储在ibdata1文件中，所以结果可能偏低。这种技术也适用于混合MyISAM/InnoDB (innodb_file_per_table)数据库。
  3. 如果您正在使用未启用innodb_file_per_table set的InnoDB，则需要查看INFORMATION_SCHEMA。

在上述任何情况下，您可以运行以下查询来获取您要查找的信息。

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)
如果您有很多表格，那么速度可能会很慢，正如您已经发现的那样。
我在其他地方看到，选项3没有考虑VARCHAR的大小。 - Joe
选择 table_name, (data_length+index_length)/1024/1024 AS size from information_schema.tables where table_schema='schema_name' order by size; - krishnakumarp
这是不正确的。对于InnoDB，你还有data_free列，即使那也不准确。 - dresende
你可以使用这个命令来获取以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;
+-------------------------------------+-----------------+
| 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)

根据Aaron Brown的答案进行了修改，以提供以GB为单位的大小。更多详细信息请参见Aaron Brown的答案。

要包括可用/可回收空间，请使用以下方法：

mysql> SELECT table_schema "DB name",
sum( data_length + index_length ) / 1024 / 1024 "DB 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 MB | free/reclaimable space in MB |
+--------------------+---------------+------------------------------+
| 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 |
+--------------------+---------------+------------------------------+
可以使用OPTIMIZE TABLE命令来回收InnoDB、MyISAM和ARCHIVE表的空间。 更多详细信息，请参阅如何获取MySQL数据库的真实大小？。
为了查看磁盘空间的使用情况（无论是在mysql表中还是其他地方），我使用可靠的"du"命令。以下是我找到空间被占用的示例。 
$ 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

你可以看到大部分空间都被这个文件夹占用了。 /mysql

这个文件夹保存着数据表。为了查看哪些表占用了所有的空间，你可以按照以下步骤使用“human”或“-h”选项。我喜欢以这种方式进行磁盘空间管理，因为有时候你甚至无法登录mysql，因为你不知道密码或用户名。

$ 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
你可以看到所有的空间都被几个包含大量数据的表占用了。希望这对你有所帮助。

我知道这个可能有点老了，但是可能有人会觉得这个还是有用的。

在MySQL中，我使用：

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;

由于我的数据库是InnoDB，这只是一个估计。

我将此输出与以下进行比较：

du -sch /location/of_Mysql/* | sort -hr | head -n20
希望这对你有所帮助
获取表名和记录数的信息，可以使用以下查询语句： 
SELECT * 
FROM information_schema.TABLES ;
获取有关服务器上数据库及其相应大小的信息，可以使用以下查询语句： 
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;

最佳（在执行 apt-get install ncdu 之后）：

cd "/var/lib/mysql" && ncdu

获取您的VPS中所有Mysql数据库的总大小。

我会在你的数据字典中查找文件的大小。这是即时且准确的。 警告：根据存储引擎，索引可能存储在主文件中或另一个文件中，请不要忘记将它们加起来（如果需要的话）。
2是的，但那在哪里呢？ - Magne

假设您的MySQL主机正在运行Linux。

您可以执行下面的查询来查找存储MySQL数据的路径

select @@datadir;

一旦你知道数据存储在哪里，你可以使用du命令来检查磁盘使用情况。

例如，在Ubuntu上使用sudo du -h /var/lib/mysql。

你会得到类似下面的结果

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

源码：查看Linux上MySQL使用的存储空间