List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:October 2 2009 4:08pm
Subject:Re: disk space on arbitrary rows
View as plain text  
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 <mysqlseeker@stripped>
> 
>>
>>
>>
>>
>>
>> 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


Thread
disk space on arbitrary rowsJohn Dove30 Sep
  • Re: disk space on arbitrary rowsClaudio Nanni30 Sep
    • Re: disk space on arbitrary rowsShawn Green2 Oct