Claudio Nanni wrote:
A very first thing you should do is to evaluate the storage needed by a
single row.
This can be done by summing up all columns data types length in bytes and
index lenght as well.
This should be quite accurate.
Just a tip to start:
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
2009/9/30 John Dove <mysqlsee...@live.com>
Hi!
I need to figure out how much disk space an arbitrary number of rows in a
table take up. For example
SELECT DISK_SPACE(*) FROM ORDERS WHERE CREATED_AT BETWEEN x AND y
I know i could do something like a mysqldump with conditions and analyze
the output file but i'd like to avoid outputting anything to the filesystem
as it's slow.
Two more things to remember are:
The common InnoDB tablespace contains both data rows and indexes and
other metatdata and transaction control elements. The tablespace is
allocated in pages (16k per page) so one new row could take up 16k of
new disk but the next 8 rows might not take up any as they all may fit
on the page opened by the first new row. And, the tablespace will grow
in increments of MB, as necessary. Check your SHOW GLOBAL VARIABLES for
your current value. Tablespaces never collapse as you empty them of
data. They will only grow to enclose any new data you need them to hold.
Second, you can export the data and indexes for InnoDB tables to their
own private tablespace if you use the option --innodb-file-per-table.
However the common tablespace will still exist and is required in order
to contain the metadata and other InnoDB elements.
To see how much of your tablespace is available for new data, check the
SHOW TABLE STATUS report.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
