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.
You can query the size of table and index data using Information Schema or
SHOW TABLE STATUS. The columns
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.
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.
In MySQL 5.6, table and index data are stored in individual files in the appropriate directory. So, in my
/var/lib/mysql/world_innodb folder, I've got
CountryLanguage.ibd. To check the size of the database, I can simply read the size of that folder, which gives me the approximate size of the table and index data, along with any extra space allocated. The extra space comes from the fact that the file size is typically bigger than the amount of data stored within it, because InnoDB increases the file size by 64MB (8MB in 5.5) every time it fills up. You can change this value by setting the
In MySQL 5.1 and 5.5, the default InnoDB configuration stores all InnoDB information in a single tablespace with a filename of (typically)
/var/lib/mysql/ibdata1. By default, this file contains all table data and index information for all InnoDB tables in all databases, which means you cannot simply calculate the space taken on disk by the data of any single database. Although all InnoDB data defaults to a single tablespace file, you can change this by setting
innodb_file_per_table, which is enabled by default in 5.6. By doing so, you store table and index data for each database in a file named for the table, in the appropriate database directory.
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.
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
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:
mysqldatabase: 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
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:
db.optfile takes up 65 bytes (for my
.frmfiles) are about 8KB each
.frmfiles) 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
There is also row data in
mysql relevant to the
mysql.proctable contains 6 rows for
sakila; with an average row length of 1116 bytes—from
SHOW TABLE STATUS LIKE 'sakila';—that gives approximately 6KB.
mysql.dbtable on my system has two rows on my system (I've set up a couple of users), at ~440 bytes each
mysql.tables_privtable has one row at 851 bytes (unrepresentative, but included for detail)
mysql.procs_privrows relevant to
sakilaat this time (again, unrepresentative, but included for detail).
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?