Reporting on disk space usage for PostgreSQL and MySQL databases
I was recently tasked with finding out how much each disk space each of our PostgreSQL and MySQL databases was taking up.
The job was considerably easier for PostgreSQL, which ships with tools in contrib/dbsize to do just this. After following the simple instructions there, all that was necessary for easy reporting was to create a simple view. Here’s the one I used:
SELECT pg_database.datname,
pg_shadow.usename AS “owner”,
database_size(pg_database.datname) AS size,
pg_size_pretty(database_size(pg_database.datname)) AS pretty_size
FROM pg_database
JOIN pg_shadow ON pg_database.datdba = pg_shadow.usesysid;
With MySQL I found it necessary to roll my own script. I’ve published mysql_dbsize.pl to save someone else the trouble.
It’s a Perl script you’ll need to edit to change your mysql hostname, and perhaps other bits of the connection string. It requires the Number::Format and Perl6::Form modules to work, along with DBD::mysql, as you might expect.
It will produce a nice table format including the raw number of bytes as well as a formatted version for human consumption like 3.43 G.
I hope it’s helpful!
One Response to “Reporting on disk space usage for PostgreSQL and MySQL databases”
Leave a Reply
The opinions expressed by individuals posting in the Summersault Weblog are not necessarily those of Summersault, LLC. While we try to insure the quality and accuracy of the information presented here, we make no guarantees about its suitability for any particular purpose.
March 15th, 2006 at 3:53 pm
Since the release of PostgreSQL 8.1, these functions are now built directly to PostgreSQL, although they have different names. See the documentation for details.
The query above would now be written as:
SELECT pg_database.datname,
pg_shadow.usename AS owner,
pg_database_size(pg_database.datname) AS size,
pg_size_pretty(pg_database_size(pg_database.datname)) AS pretty_size
FROM pg_database
JOIN pg_shadow ON pg_database.datdba = pg_shadow.usesysid;
To get the size of a single table use:
SELECT pg_size_pretty( pg_total_relation_size(’my_table_name_here’) );