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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
