主页 / dba / 问题 / 14337
Accepted
GoldenNewby
Asked: 2012-03-04 17:35:26 +0800 CST

计算每个 MySQL DB 的磁盘空间使用情况

  • 772

我目前正在使用 information_schema.TABLES 来计算按数据库名称分组的总磁盘空间使用量，但它的运行速度非常慢。在拥有数百个数据库的服务器上，计算可能需要几分钟时间。

按数据库计算磁盘空间使用量的最快方法是什么？我应该只看文件系统吗？有没有加快信息模式的方法？

mysql

8 个回答

  1. Best Answer

    有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 innodb_file_per_table set，则需要查看 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)

    如果您有大量的表，它可能会很慢，正如您已经发现的那样。

    • 64

  2. 您可以使用此命令以 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 |
+--------------------+---------------+------------------------------+

    对于 InnoDB、MyISAM 和 ARCHIVE 表，可以使用OPTIMIZE TABLE 命令回收空间。

    另请参阅如何获取 MySQL 数据库的真实大小？ 更多细节。

    • 13

  3. 为了让我查看磁盘空间在哪里被用完（不管它是否在 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

    您可以看到所有空间都被几个包含许多 GiG 数据的表占用。希望这可以帮助。

    • 3

  4. 要获取有关表名和记录数的信息，可以使用以下查询，

    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;
    • 1

  5. 我知道这很旧，但有人可能会觉得这很相关。

    在 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

    希望这可以帮助你

    • 1

  6. 我会在您的数据字典中查找文件的大小。它的瞬时和准确。

    警告：根据存储引擎，索引存储在主文件或另一个文件中，如果需要，不要忘记汇总它们。

    • 0

  7. 最好的（做完之后apt-get install ncdu）：

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

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

    • 0

  8. 假设您的 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

    来源：检查 MySQL 在 linux 上使用的存储

    • 0

相关问题