Documentation Home
MySQL Utilities 1.6 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 288.2Kb
HTML Download (TGZ) - 208.9Kb
HTML Download (Zip) - 221.6Kb


MySQL Utilities 1.6 Manual  /  ...  /  How do you know how much space your data uses?

Beta Draft: 2016-08-26

3.3.1 How do you know how much space your data uses?

When preparing to create a backup or when performing maintenance on a server, it is often the case we need to know how much space is used by our data and the logs the server maintains. Fortunately, there is a utility for that.

Objectives

Show the disk space used by the databases and all logs using the mysqldiskusage utility.

Example Execution

shell> sudo env PYTHONPATH=$PYTHONPATH mysqldiskusage \
--server=root:root@localhost --all
# Source on localhost: ... connected.
# Database totals:
+-----------------+--------------+
| db_name         |       total  |
+-----------------+--------------+
| oltp2           | 829,669      |
| bvm             | 15,129       |
| db1             | 9,895        |
| db2             | 11,035       |
| employees       | 206,117,692  |
| griots          | 14,415       |
| mysql           | 995,722      |
| oltp1           | 177,393      |
| room_temp       | 9,847        |
| sakila          | 791,727      |
| test            | 647,911      |
| test_arduino    | 9,999        |
| welford_kindle  | 72,032       |
| world           | 472,785      |
| world_innodb    | 829,669      |
+-----------------+--------------+

Total database disk usage = 210,175,251 bytes or 200.44 MB

# Log information.
+--------------------+--------------+
| log_name           |        size  |
+--------------------+--------------+
| host123.log        | 957,282,265  |
| host123-slow.log   |     123,647  |
| host123.local.err  | 321,772,803  |
+--------------------+--------------+

Total size of logs = 1,279,178,715 bytes or 1.19 GB

# Binary log information:
Current binary log file = my_log.000287
+----------------+---------+
| log_file       | size    |
+----------------+---------+
| my_log.000285  | 252208  |
| my_log.000286  | 256     |
| my_log.000287  | 3063    |
| my_log.index   | 48      |
+----------------+---------+

Total size of binary logs = 255,575 bytes or 249.58 KB

# Server is not an active slave - no relay log information.
# InnoDB tablespace information:
+--------------+--------------+
| innodb_file  |        size  |
+--------------+--------------+
| ib_logfile0  |   5,242,880  |
| ib_logfile1  |   5,242,880  |
| ibdata1      | 815,792,128  |
| ibdata2      |  52,428,800  |
+--------------+--------------+

Total size of InnoDB files = 889,192,448 bytes or 848.00 MB

InnoDB freespace = 635,437,056 bytes or 606.00 MB

Discussion

To see all of the logs, we use the --all option which shows all logs and the InnoDB disk usage.

Notice we used elevated privileges to allow for reading of all of the files and databases in the data directory. In this case, the data directory is owned by the mysql user and a normal user account does not have read access.

The --all option instructs the utility to list all databases even if they contain no data.

Permissions Required

The user must have permissions to read the data directory or use an administrator or super user (sudo) account as shown in the example.

Tips and Tricks

You can run mysqldiskusage without privileges to read the data directory but in this case you will see an estimate of the disk usage rather than actual bytes used. You may also not be able to see a list of the logs if you run the utility remotely.


User Comments
Sign Up Login You must be logged in to post a comment.