disk space on arbitrary rows

--_9f984562-bff3-43cb-9b3e-a1e2b19bb308_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable







Hi!

I need to figure out how much disk space an arbitrary number of rows in a t=
able 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 th=
e output file but i'd like to avoid outputting anything to the filesystem a=
s it's slow.

My underlying db type is InnoDB on Server version: 5.0.75-0ubuntu10.2

Any help would be much appreciated.

-John
=0A=
____________________________________________________________ _____=0A=
Hotmail=AE has ever-growing storage! Don=92t worry about storage limits.=0A=
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=3DTXT_T AGLM_WL_HM_Tuto=
rial_Storage_062009=

--_9f984562-bff3-43cb-9b3e-a1e2b19bb308_--

Re: disk space on arbitrary rows

--00c09ffb56b96d093f0474ccebb3
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

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

>
>
>
>
>
>
> 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 filesyst=
em
> as it's slow.
>
> My underlying db type is InnoDB on Server version: 5.0.75-0ubuntu10.2
>
> Any help would be much appreciated.
>
> -John
>
> ____________________________________________________________ _____
> Hotmail=AE has ever-growing storage! Don=92t worry about storage limits.
>
> http://windowslive.com/Tutorial/Hotmail/Storage?ocid=3DTXT_T AGLM_WL_HM_Tu=
torial_Storage_062009




--=20
Claudio

--00c09ffb56b96d093f0474ccebb3--

Re: disk space on arbitrary rows

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
>
>>
>>
>>
>>
>>
>> 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=gcdmg-mysql-2@m.gmane.org