By Jeremy Smyth on May 30, 2013
I got a question a while ago that I thought was quite simple, but turned into an interesting discussion: How much hard disk space does a database take up?
As it happens, there's a simple answer and a much, much more involved (yet ill-defined) answer, and which one you choose depends on what you think goes into a database and how very, very pedantic you are.
The Simple Answer
You can query the size of table and index data using Information Schema or SHOW TABLE STATUS. The columns DATA_LENGTH and INDEX_LENGTH contain the number of bytes for the table and index data respectively. You can construct simple queries based on that data, or do a quick search on the web for more interesting queries (For example, Peter Zaitsev of Percona wrote a post on Researching your MySQL Table Sizes). Adding up the totals for all tables in a database gives you a figure that shows how big the data rows and index contents for the database are.
This really is where you should stop.
Let's Get Slightly Pedantic: Taking the File System's Perspective
In MySQL, databases are little more than file folders, so they don't consume space themselves. The space is consumed by the data within, specifically tables and indexes: Views, stored routines, triggers are little more than command text and so take a trivial amount of space, so for now we can ignore them.
Let's Get Really Pedantic: Going Beyond the File System
The simple answer at the top really is quite simple: It only considers data, so if there is no data, there are no rows taking up space. This means that regardless of how many functions, procedures, triggers, views, tables, permissions, or anything else you've configured in your (otherwise empty) database, MySQL considers that it is empty, and therefore takes up 0 space. Of course, if you're quite pedantic about it, you'll know better.
What Else is in a Database?
MySQL does not allocate space per database, but per table and any indexes associated with that table. If every table in a database contain no data, then MySQL considers that the database contains no data. Queries on DATA_LENGTH and INDEX_LENGTH show information about data (as opposed to metadata), so for the purpose of that query, a database with no data takes up 0 space. This might be a quite simplistic way of looking at it, so let's consider the difference between this viewpoint—a database with no data takes up no space—and another—a database with objects, even empty ones, has to exist, and that must take up some space.
As well as table data and indexes, the other information MySQL maintains about a database is its metadata. Some of this metadata is contained within:
- The file system: as a database directory as db.opt, .frm, and .trg files
- The mysql database: stored routines, events, privileges, and other database metadata; these are stored in MyISAM tables, each of which contains information for all databases on the server
Metadata on the File System
As an administrator, I can see that the structure of my sakila database takes up space in the form of the following file system artefacts:
- The database directory inode takes up 4KB on my machine.
- The db.opt file takes up 65 bytes (for my sakila database)
- Triggers take just over 1KB each (TRG and TRN files)
- Table metadata (.frm files) are about 8KB each
- View metadata (.frm files) depend on the complexity of the view, but let's say 1KB each
On my system, these add up to 336KB for my slightly-extended copy of the sakila database.
Metadata in the mysql Database
There is also row data in mysql relevant to the sakila database:
- The mysql.proc table contains 6 rows for sakila; with an average row length of 1116 bytes—from SHOW TABLE STATUS LIKE 'sakila';—that gives approximately 6KB.
- The mysql.db table on my system has two rows on my system (I've set up a couple of users), at ~440 bytes each
- The mysql.tables_priv table has one row at 851 bytes (unrepresentative, but included for detail)
- I have no mysql.event, mysql.columns_priv, or mysql.procs_priv rows relevant to sakila at this time (again, unrepresentative, but included for detail).
Have We Considered Everything Yet?
This isn't even the whole story; I haven't included mysql.user records for users who only use this database, or log file entries relevant to the sakila database. You can take this as a sign that what constitutes a database is actually quite a fuzzy thing.
So, taking all of that together, you might consider that the metadata for my sakila database takes up ~344KB, but it should be apparent that it is quite a difficult task to define what is and isn't sakila metadata, and there is certainly no built-in way to arrive at this figure in a comprehensive and consistent way that would satisfy the most pedantic administrator.
Best to just consider the data, right?