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
database.
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:
SUM(data_free)
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,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))
/1024/1024,2),0.00),"Mb") total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb")
data_used,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.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
Monitors":
http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html
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
log:
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
tables:
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
--
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
