From: Shawn Green (Shawn.GreenSun.COM)
Date: Fri Oct 02 2009 - 11:08:22 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <mysqlseekerlive.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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]