Quantcast

Reclame unused space, but how exactly?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Reclame unused space, but how exactly?

Karl Weber-2
Hi,

just to make sure, that I understand what is going on:

Suppose I have some table with a variable length column type, e.g. a
varchar(10000). The data in this column are large, say, on average of about
9000 characters.

Then I replace the data and they are on average only 10 characters long.

Suppose, I have a huge number of rows, so that the following makes sence.

When I understand the derby documentation, the space -- 8990 characters on
average in each row -- is unused and completely wasted. Derby does not
automatically frees this space.

Is this correct so far? Or does it depend? If yes, on what?

I want to make the unused space availabe to derby for the insertion of new
rows. It should be optinal to give unused space back to the OS. In order to do
so, I have to use some system utility.

Correct?

The only utility I found is SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. But is
this correct?

I do not have deleted rows, to I do not need PURGE_ROWS.

I do not need to move entire rows, so I do not need DEFRAGMENT_ROWS.

So the utility will not help???

Since SYSCS_UTIL.SYSCS_COMPRESS_TABLE will only give unused space back to the
OS, this is not helpfull either. Or is it?

Where is my misunderstanding, i.e. what do I have to do, in order do reclame
the space?

Or does derby somehow optimize disk usage in the described case automatically?

Thanks in advance
Karl
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Reclame unused space, but how exactly?

Bryan Pendleton-3
> I want to make the unused space availabe to derby for the insertion of new
> rows. It should be optinal to give unused space back to the OS. In order to do
> so, I have to use some system utility.

I'm not totally sure whether the 5th word in the above line is "optional"
or "optimal".

To return unused space to the OS, you have to make a special call;
simply deleting your rows from the table with DELETE FROM is not enough,
nor is updating your rows to set every column to the shortest-possible
value.

> The only utility I found is SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. But is
> this correct?

It is correct that the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure
will return space to the operating system if there is space available.

There is also the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure.

And there is the DROP TABLE statement.

And the TRUNCATE TABLE statement.

> Since SYSCS_UTIL.SYSCS_COMPRESS_TABLE will only give unused space back to the
> OS, this is not helpfull either. Or is it?

I don't understand your question: I thought that you **wanted** to "give unused
space back to the OS".

> Where is my misunderstanding, i.e. what do I have to do, in order do reclame
> the space?
>
> Or does derby somehow optimize disk usage in the described case automatically?

Derby does many such optimizations. You might want to start by reading
the archives of derby-user from May-June, 2005, specifically starting here:

http://mail-archives.apache.org/mod_mbox/db-derby-user/200505.mbox/%3Cloom.20050531T105713-104@...%3E

Maybe you could run some experiments, to try different approaches,
and report back to the list about what experiments you have run,
and what you have found, and the list could help you understand
the behaviors you are seeing.

Note that if you actually have "a huge number of rows", where
the data you store in each row is "large, say, on average of about
9000 characters" and then you "replace the data and they are
on average only 10 characters long", you have created a somewhat
mysterious program, to me.

Will you ever make those values longer again? Or will they permanently
be only 10 characters long? Why did you need the 9000 byte values in
the first place? Does this happen with every row in your table, or
is it more of a mixture of some rows growing and some rows shrinking?

The wonderful thing about a database like Derby is that you have many
choices in how you build your application, and Derby will work very
hard to try to accommodate whatever choices you make.

The challenging thing about a database like Derby is that you have many
choices in how you build your application, and Derby will work very
hard to try to accommodate whatever choices you make.

thanks,

bryan


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Reclaim unused space, but how exactly?

Karl Weber-2
Hi Bryan,

thank you for your answer. My problem is the following: You may think about a
table with a column (among others) that contains a large amount of
unstructured data. These data will to a large extent (but perhaps not
completely) be replaced by structured data (in some user defined data type) in
another column. This will reduce the space requirements considerably for each
particular row. This conversion of the data will be done only once and also
require the addition of one new column to the table in question. After this
conversion, the newly added rows will already have the data structured.

So my question is about what to do after this conversion in order to have no
disk space wasted.

Am Sonntag, 6. März 2016, 08:53:10 schrieb Bryan Pendleton:
> > I want to make the unused space availabe to derby for the insertion of new
> > rows. It should be optinal to give unused space back to the OS. In order
> > to do so, I have to use some system utility.
>
> I'm not totally sure whether the 5th word in the above line is "optional"
> or "optimal".
>

The word is optional. The point is, that the number of rows will further
increase, so there seems to be no point in giving the space back to the OS
after the conversion, derby will need this space later anyway. I just want to
make sure that disk space will be used efficiently after the conversion.

With kind regards
Karl

Me
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Reclame unused space, but how exactly?

Me
In reply to this post by Bryan Pendleton-3
I wonder if you realize the only way Derby (and other darabase systems) gives space back to the OS is when it's freed up a page. Typically data is not restructured to that extent on an update. So if a page initially had 3 rows because each row had lots of data in one column, after update, the page will still have 3 rows, even if those rows are now a fraction of the size. It wain't try to consolidate across pages. It might use empty space - if any - to insert new rows, but will hold on to the page it now has under its control. Note also that depending on the datatype the space in the 'emptied' column may still be reserved, or padded with blanks, not really empty after all.
 
It's possible compress actually compresses the data on the pages, probably depends on the datatype.

But reorganizing means doing it for all the rows in a table, so it takes some time.

If in your experiments you find compress doesn't get you enough space back you could think of having two tables, one for the initial data, one for the modified one.

Hth
Myrna


On Mar 6, 2016, at 8:53 AM, Bryan Pendleton <[hidden email]> wrote:

>> I want to make the unused space availabe to derby for the insertion of new
>> rows. It should be optinal to give unused space back to the OS. In order to do
>> so, I have to use some system utility.
>
> I'm not totally sure whether the 5th word in the above line is "optional"
> or "optimal".
>
> To return unused space to the OS, you have to make a special call;
> simply deleting your rows from the table with DELETE FROM is not enough,
> nor is updating your rows to set every column to the shortest-possible
> value.
>
>> The only utility I found is SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. But is
>> this correct?
>
> It is correct that the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure
> will return space to the operating system if there is space available.
>
> There is also the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure.
>
> And there is the DROP TABLE statement.
>
> And the TRUNCATE TABLE statement.
>
>> Since SYSCS_UTIL.SYSCS_COMPRESS_TABLE will only give unused space back to the
>> OS, this is not helpfull either. Or is it?
>
> I don't understand your question: I thought that you **wanted** to "give unused
> space back to the OS".
>
>> Where is my misunderstanding, i.e. what do I have to do, in order do reclame
>> the space?
>>
>> Or does derby somehow optimize disk usage in the described case automatically?
>
> Derby does many such optimizations. You might want to start by reading
> the archives of derby-user from May-June, 2005, specifically starting here:
>
> http://mail-archives.apache.org/mod_mbox/db-derby-user/200505.mbox/%3Cloom.20050531T105713-104@...%3E
>
> Maybe you could run some experiments, to try different approaches,
> and report back to the list about what experiments you have run,
> and what you have found, and the list could help you understand
> the behaviors you are seeing.
>
> Note that if you actually have "a huge number of rows", where
> the data you store in each row is "large, say, on average of about
> 9000 characters" and then you "replace the data and they are
> on average only 10 characters long", you have created a somewhat
> mysterious program, to me.
>
> Will you ever make those values longer again? Or will they permanently
> be only 10 characters long? Why did you need the 9000 byte values in
> the first place? Does this happen with every row in your table, or
> is it more of a mixture of some rows growing and some rows shrinking?
>
> The wonderful thing about a database like Derby is that you have many
> choices in how you build your application, and Derby will work very
> hard to try to accommodate whatever choices you make.
>
> The challenging thing about a database like Derby is that you have many
> choices in how you build your application, and Derby will work very
> hard to try to accommodate whatever choices you make.
>
> thanks,
>
> bryan
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Reclaim unused space, but how exactly?

Bryan Pendleton-3
In reply to this post by Karl Weber-2
> unstructured data. These data will to a large extent (but perhaps not
> completely) be replaced by structured data (in some user defined data type) in
> another column. This will reduce the space requirements considerably for each
> particular row.

Hi Karl, thanks for the follow-up.

As Myrna suggested, I wonder if it might be worth using two separate tables here.

First, insert the unstructured data into the first table.

Later, when the conversion to the structured form occurs, insert the
row (in its structured form) into the second table, and simultaneously
delete the row from the first table.

Over time, I'd expect Derby to successfully re-use the space in the first
table, and I'd expect Derby to successfully store the rows in the
second table in a compact way.

bryan


Loading...