Thank you VERY much, Mark! Your reply is EXCELLENT and gives us all a lot of
very useful information.
This is the kind of information that should be in the MySQL manual. Paul
DuBois, if you're reading this, please consider adding all of Mark's
information to the manual!
I think this reply also points to a definite need within the MySQL
community, namely monitoring tools. After all, any decent administrator is
going to want to know the size of his databases at some point. I don't
follow the development of tools for MySQL but if there are no tools to
monitor database size, I would imagine there is a definite market for such
tools. After all, why should each of us independently re-invent the wheel?
This seems like an opportunity for an entrepreneurial type to make some
money serving a market. Or for people who have already developed monitoring
tools to contribute them freely to the MySQL community.
Thanks again, Mark! I know I will revisit your reply when I get around to
doing proper monitoring of my MySQL databases when they finally go into
----- Original Message -----
From: "Mark Leith" <mleith@stripped>
To: "Shivaji S" <s.shivaji@stripped>
Cc: "Rhino" <rhino1@stripped>; <danieldaveiga@stripped>;
Sent: Wednesday, May 03, 2006 9:20 AM
Subject: Re: How to find size of my database
> On Wed, 03 May 2006 Rhino wrote :
>>> Daniel de Veiga has already answered you on how to determine the size of
>>> your database by using the file system and simply looking at the size of
>>> the physical files in your database.
>>> Another possibility is that you could use the SHOW TABLE STATUS command
>>> in MySQL. If you go to your MySQL prompt and select a database, then use
>>> the SHOW TABLE STATUS command, like this:
>>> set tmp;
>>> show table status like '%';
>>> you'll find a column called Data_length which tells you "the length of
>>> the data file" for each table. If you simply add the size of each table
>>> in the database together, you should have the size of the whole
>>> Please note that I'm not sure how accurate my suggestion is; you might
>>> find that Daniel's approach gives you a better answer. I'm not sure if
>>> the Data_length column considers all the overhead that you might have
>>> with a table, such as index sizes, pointer sizes, unusable space, etc.
>>> Perhaps someone else can jump in with a better approach; I'm inclined to
>>> think it can't be quite as easy as I suggested.
> I'll jump in for you..
> Indeed, you are right that Data_length that does not cover space
> allocated but unused (i.e space freed up by DELETE or UPDATE statements
> that is not released back to the filesystem, before an OPTIMIZE TABLE for
> example). There are other columns within the output however - Data_free
> and Index_length.
> Therefore to get the total allocated space to a database:
> SUM(data_length) + SUM(index_length)
> Total of actual data:
> (SUM(data_length) - SUM(data_free)) + SUM(index_length)
> Allocated but unused:
>>> Also, even if the Data_length column gives an accurate answer for the
>>> size of a table, it is rather tedious to have to execute the SHOW TABLE
>>> STATUS command and then manually sum up the various sizes. I don't think
>>> you can simply execute an SQL query that does all the work for you,
>>> which is very unfortunate.
> Unfortunately within 4.0 there is no way to do this with a SQL query. You
> can do this on 5.0 however. For example:
> SELECT s.schema_name,
> /1024/1024,2),0.00),"Mb") total_size,
> CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,
> /((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used,
> COUNT(table_name) total_tables
> FROM INFORMATION_SCHEMA.SCHEMATA s
> LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema
> WHERE s.schema_name = "sakila"
> GROUP BY s.schema_name
> ORDER BY pct_used DESC\G
> *************************** 1. row ***************************
> schema_name: sakila
> total_size: 6.62Mb
> data_used: 6.62Mb
> data_free: 0.01Mb
> pct_used: 99.91
> total_tables: 22
> 1 row in set (0.08 sec)
>>> It is entirely possible that there is a MySQL command that gives you the
>>> actual size of each database directly, although I didn't find it when I
>>> searched the manual. Again, perhaps someone with more administrative
>>> experience with MySQL can suggest a better approach. If not, perhaps we
>>> need to make a feature request of the MySQL people :-) This would appear
>>> to be a very useful command to create if it doesn't already exist!
> I have a little administrative experience ;)
> I'm going to confuse the matter now, as the above reports freespace
> correctly for storage engines such as MyISAM, however, it does not report
> the freespace properly within Data_free column for InnoDB tables - the
> freespace is reported at the *tablespace* level, within the "Comment"
> column of SHOW TABLE STATUS and INFORMATION_SCHEMA.TABLES.
> There are a couple of other relatively undocumented features within InnoDB
> that allow you to dump this kind of information - called the "InnoDB
> If you create the following table for a short period of time, the internal
> contents of the InnoDB data dictionary will be dumped out to the error
> create table innodb_table_monitor (a int) engine = innodb;
> Then drop the table after a minute or so (otherwise it will dump
> continuously). This will dump a wealth of information on all of the tables
> within InnoDB (for all databases), here's an extract on one of our sakila
> TABLE: name sakila/rental, id 0 26, columns 11, indexes 5, appr.rows 16305
> COLUMNS: rental_id: DATA_INT len 4 prec 0; rental_date: DATA_INT len 8
> prec 0; inventory_id: DATA_INT len 3 prec 0; customer_id: DATA_INT len 2
> prec 0; return_date: DATA_INT len 8 prec 0; staff_id: DATA_INT len 1 prec
> 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6
> prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS
> prtype 8 len 7 prec 0;
> INDEX: name PRIMARY, id 0 44, fields 1/9, type 3
> root page 207, appr.key vals 16305, leaf pages 53, size pages 97
> FIELDS: rental_id DB_TRX_ID DB_ROLL_PTR rental_date inventory_id
> customer_id return_date staff_id last_update
> INDEX: name rental_date, id 0 45, fields 3/4, type 2
> root page 208, appr.key vals 17655, leaf pages 28, size pages 29
> FIELDS: rental_date inventory_id customer_id rental_id
> INDEX: name idx_fk_inventory_id, id 0 46, fields 1/2, type 0
> root page 210, appr.key vals 4467, leaf pages 16, size pages 17
> FIELDS: inventory_id rental_id
> INDEX: name idx_fk_customer_id, id 0 47, fields 1/2, type 0
> root page 211, appr.key vals 589, leaf pages 16, size pages 17
> FIELDS: customer_id rental_id
> INDEX: name idx_fk_staff_id, id 0 48, fields 1/2, type 0
> root page 212, appr.key vals 1, leaf pages 13, size pages 14
> FIELDS: staff_id rental_id
> FOREIGN KEY CONSTRAINT sakila/fk_rental_staff: sakila/rental ( staff_id )
> REFERENCES sakila/staff ( staff_id )
> FOREIGN KEY CONSTRAINT sakila/fk_rental_inventory: sakila/rental (
> inventory_id )
> REFERENCES sakila/inventory ( inventory_id )
> FOREIGN KEY CONSTRAINT sakila/fk_rental_customer: sakila/rental (
> customer_id )
> REFERENCES sakila/customer ( customer_id )
> FOREIGN KEY CONSTRAINT sakila/fk_payment_rental: sakila/payment (
> rental_id )
> REFERENCES sakila/rental ( rental_id )
> As you can see - bunches of information (including showing the internal
> columns used for transactions). However the things to note here are the
> "leaf pages" and "size pages" for each index. "leaf pages" is the number
> of pages that actually contain data (as indexes store their data in the
> leaf nodes), whilst "size pages" is the total number of pages that are
> allocated to the table.
> InnoDB uses "clustered indexes" - so the data for each row is actually
> stored within the PRIMARY KEY index for each table. Each "page" within
> InnoDB is 16kb in size. So as you can see above for "Index: name PRIMARY"
> we have "size_pages 97" (16,384 * 97 = 1,589,248 (1.5Mb)) allocated to the
> table within the InnoDB tablespace, with only "leaf pages 53" (16,384 *
> (97-53) = 720,896 (~700Kb)) actually being used.
> You can then perform the same kind of sum across the rest of the indexes
> within each table to get an approximation such as that given for
> Index_length within SHOW TABLE STATUS / INFORMATION_SCHEMA.TABLES as well.
> Of course, this is not an easy method to use, there is no simple command
> to quickly analyze this data (yet), it's mostly a manual process reading
> through the dump file - however shouldn't be that hard to script, either.
> Hope this helps!
> Best regards
> Mark Leith, Support Engineer
> MySQL AB, Worcester, England, www.mysql.com
> Are you MySQL certified? www.mysql.com/certification
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006